关键词 > Excel代写
Information systems theory Tutorial 2
发布时间：2023-07-18
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
Tutorial 2 (teaching week 3)
Objectives and organisation of tutorials:
• Students will complete excel activities (including watching LinkedIn Learning videos) and case study readings prior to the tutorial
• Students will work together to confirm their understanding of excel topics
• Tutors will lead discussions on theoretical topics using the case study readings
• Students will participate in quiz sessions to help reinforce learning. These quizzes are not assessed but the highest-ranked students will receive a prize at the end of the semester.
• Students are strongly encouraged to attend all tutorials and take comprehensive notes. In cases where a student cannot attend a particular tutorial, the student can complete the pre- tutorial activities on their own, watch the recordings, or follow up with the tutor during consultation sessions.
Information systems theory
1. Read the short article/case: “Offline, online and back: the evolution of the UK Grocery Market” on Page 135-137 of the course text. Answer the four questions:
1a. Analyze the cost leadership strategy of Tesco1 based on Porter’s Competitive Forces Model. Why would it be a smart move to close one of its online shopping sites?
1b. Do you think Aldi and Lidl’s strategy of venturing into e-commerce is a good idea?
1c. Comment on Tesco’s strategy of opening a discount chain. Do you think it could recover Tesco’s former market position? Explain your answer.
1d. How much potential do you see for virtual stores (like the ones Tesco introduced in South Korea) in the United Kingdom?
2. Explain how the value chain model can be used to identify opportunities for information systems.
3. Explain how a business can go about identifying the processes for change.
Excel: Working with Excel
Watch the following Excel videos (click on the links)
Instructions for logging in to LinkedIn Learning
• Go to www.linkedin.com/learning/
• Click Sign in
• Enter your UQ email address in this format: [email protected]
• Click Sign in with Single Sign-On
• You will be redirected to the UQ Authenticate page. Log in using your UQ username and password
• You can choose to connect your personal LinkedIn profile to your Learning account for additional features such as course recommendations for your skills and position and what’s trending on LinkedIn Learning. If you do link your LinkedIn profile, you will also be prompted to log in using your LinkedIn account each time, after you log in via UQ.
• The first time you access LinkedIn Learning you can select the "Continue without connecting my LinkedIn account" button to activate your LinkedIn Learning account without connecting to a LinkedIn account, if you prefer.
(For more info see https://web.library.uq.edu.au/library-services/training/linkedin-learning-online courses)
Files for this tutorial:
● 03 - Creating Formulas and Functions.xlsx
● 04 - Formatting.zip
● 05 - Layout.xlsx
● 06 - Printing.xlsx
The files are available on Blackboard (under Learning Resources > Week 3 > Tutorial Resources) or on the LinkedIn site.
Formulas in Excel (file “03 - Creating Formulas and Functions” spreadsheet) Creating simple formulas (worksheet: formulas)
In the work file “Creating Formulas and Functions” complete the following actions:
1. In cell B4, subtract “Overhead” from “Sales” . Note the formula bar content for each entry. Type the explicit formula firstly – then (as is done in the video) enter the formula via the ‘up arrow’ the appropriate number of times – then finally via typing and mouse.
2. In cell H2, use the built-in function SUM (we define a built-in function as a “shortcut to formulas”) to sum values in B2:G2.
3. In cells I2, calculate the average of the “Sales” figures in row 2.
Copying a formula into adjacent cells (worksheet: copy formulas)
1. Using mouse (and fill handle) copy the formula in B4 across to G4 – students should be able to explain what Excel is doing in adjusting the existing formula.
2. Similarly, copy formulas in H2 and I2 down across the appropriate cells.
Calculating year-to-date totals (worksheet: YTD and Pct increase)
1. Enter YTD Profits formula in B5, then the appropriate formula in cells C5:G5 (students should understand the logic of the YTD calculation)
Creating a percentage-change formula (worksheet: YTD and Pct increase)
1. Enter Pct Increase in cell C6. Note that parentheses must be used in this formula to ensure correctness. Note the need to match a left parenthesis with a right parenthesis. Note also the ‘hierarchy’ of operator priority (parenthesis, exponentiation, then multiplication AND division, and finally addition AND subtraction. Also ensure the formatted result is a percentage figure (not a decimal result). Copy the formula across to G6.
Working with relative, absolute, and mixed references (worksheet: Absolute)
1. Enter a formula to calculate the new salary (the existing salary increased by the percentage shown in H1). Note the logic of the formula, the need for the appropriate referencing or addressing, and finally – the need to verify your formula result (perhaps by one manual calculation). Addressing is very important in Excel.
Using SUM and AVERAGE (worksheet: SUM AVERAGE)
1. Calculate the SUM in G2 for all revenue figures via the AutoSum button. Repeat in B9 and H8.
2. Look to familiarize yourself with the AVERAGE and MAX functions. Tutors will explain the syntax and semantics (i.e. how the function is called and the full meaning of the call) of each function (AVERAGE and MAX).
Using other common functions (worksheet: Functions)
1. Note that there are over 450 functions ‘built-into’ Excel – we can loosely define a function as a “shortcut to a formula” .
2. In the file, use the RANK function in cell G2 – use the clicking of column F to nominate the range. Note well the syntax and semantics of RANK. Copy the function down. Change two values in F to be the same and note the results (i.e. how RANK now shows the rankings).
3. Complete the appropriate formulas in I2 and I3 to count the total number of cells and total number of ‘number’ cells in the range A1:F13 (using COUNTA and COUNT) – explain the difference in these two functions. Note well the syntax and semantics of each function.
4. Calculate the ‘median’ (or middle) value in the list of new salaries (column F) via the function MEDIAN, noting syntax and semantics. Similarly, the ‘2nd largest’ value – use LARGE with the appropriate argument setting (again note the syntax and semantics).
5. Calculate STDEV.S (standard deviation) in I6. Excel important points in file “04 - Formatting”
Students (in their own time) should work through all worksheets in the “04 - Formatting” workbook. Particularly important are the following:
1. The ‘best fit’ for column size (worksheet “Rows and Columns”) – select all cells, then double- click any column separator – this is very useful to visually format data correctly.
2. By default, text in cells is left aligned, whilst numbers are right aligned (worksheet “AlignWrapMerge” . Also note the different ways to wrap text, and also the merging of text. Finally, experiment with different rotations of text entries (either via the Alignment group or via the appropriate dialog box.
3. Note the approaches to border features (worksheet “Borders”) – these include different line styles, different colours. All available in the Font group of the Home tab.
4. Formatting numbers (worksheet “Numeric”) – this is most important for helping users appreciate the data in a cell.
5. Formatting numbers, dates and times (worksheet “Date-Time”). Note the difference in data formatting between the US (month/day/year) and Australia (day/month/year). Appreciate “short date” and “long date” formatting. Appreciate the usefulness of “Custom” date formatting.
6. Conditional formatting (worksheet “ConditionalFormatting”). Use conditional formatting to highlight values in column H that exceed $70000. Note that conditional formatting is dynamic to accommodate changes in the data. Note how we can easily clear conditional formatting rules, or change rules (via ‘Manage Rules’). Also note the use of ‘data bars’ and ‘icon sets’ in conditional formatting.
7. Creating and using tables (worksheet “Tables”). Tables can be useful for sorting and formatting data. Note the banded rows and the “Design” (or “Table Design”) tab in the Ribbon. Note the different ways to display via styles. Note how we can turn off and turn on the filter buttons. It is also very important to note the difference in formula syntax when we are using the table feature. Finally, on the Design tab, note the use of ‘Slicers’ (now available for Excel tables and Excel Pivot tables).
8. Shapes (worksheet “Shapes”). Can be useful in certain occasions – important to present data in its most visually useful format.
Excel important points in file “05 - Layout”
Students (in their own time) should work through all worksheets in the “05 - Layout” . Particularly important are the following:
1. Adding columns and rows is covered in the relevant video – work through these 4 capabilities (worksheet “Insert-Delete”). Also note how to “insert cells” and not whole rows or columns. Note how existing formulas are automatically adjusted.
2. Hiding and unhiding columns/rows (worksheet “Hide Unhide”) – work through these capabilities. Very useful for printing selective areas of the presented data.
3. Move, copy, inserting data (worksheet “Move Copy Insert”) – note the routine “copy/paste” or using the right mouse button to copy the data.
4. Finding and replacing (worksheet “Find Replace”) – under Home tab – Find and Replace. Note the significance of “Case” and “Match content exactly” and also format (e.g. yellow fill).
Excel important points in file “06 - Printing”
Students (in their own time) should work through all worksheets in the “06 - Printing” workbook. Particularly important are the following:
1. Printing (worksheet “PageLayoutSheet” – note the selective printing capability that is very useful – also “print titles” (rows to repeat at top).
2. Page break preview (“PageBreakPreviewSheet”) and Working with Page Setup and printing controls (“PageSetupSheet”) - more very useful printing tips.
