Information Systems Excel Assignment Part 1 - Amazon Simple ...
Information Systems MCGR 331 (Winter 2015)
Excel Assignment Part 1 – due Friday, Feb. 6 by 11:59pm The tasks in this assignment aim at developing a basic level of understanding on Excel and how you can use it to develop Decision Support System. This is an individual assignment. Compliance with all the instructions is required for full credit of 3%. Please include your student number, name and section on top of the document. There are a total of 2 tasks to complete within one saved workbook (file) which will contain two worksheets (Order and Cumulative).
Task 1: Create an Order Receipt Instructions 1) Name a worksheet in a workbook as “Order” 2) Type in all text and numbers shown in the spreadsheet below (Ensure that each cell has the right data, i.e., B4 should have value of “12” and A9 should have “Tax”).
MGCR 331 – Winter 2015 - Excel Assignment – Part 1
Page 1 of 4
3) Format all numbers with appropriate formatting (Notice the difference between tax rate formatting and discount formatting; Dollar and Euro symbols in some cells). 4) Center the spreadsheet heading “McGill BookStore” across the spreadsheet. 5) Format all text as displayed in the sample below, including the rotated text labels. 6) Create formulas to display a total for each item ordered, discounted total (in dollar and euro), subtotal, taxes and final total. Your final worksheet should look similar to the one shown below. Note: Formulas should be written such that changing the values in cell B18 (i.e., conversion rate) and B20 (i.e., tax rate) would update all the related calculations. Use currency signs as shown below. 7) Save the workbook. Name it as follows: “M331W15Sec___”. For example: If your name is “Jane Smith”, you are in section 3, and last 3 digits of your student ID is 354. Then your excel workbook should be named as: “M331W15Sec3_Smith_Jane_354” Please do not make any mistakes in naming your file to get full credit for the assignment. Your output should look like this:
MGCR 331 – Winter 2015 - Excel Assignment – Part 1
Page 2 of 4
Task 2: Create a Weekly Sales Chart Instructions 1) Name another worksheet in the same workbook as “Cumulative” 2) Type in all text and numbers shown in the spreadsheet below. (Ensure that each cell has the right data)
3) Format all numbers with appropriate formatting. 4) Center the spreadsheet heading “Weekly Sales Analysis” across the spreadsheet. 5) Format all text as displayed in the sample. 6) Create formulas to display a total for sales, average, standard deviation, maximum and minimum. Then write formula to calculate the sales share of each day. Sales share of a day is calculated by dividing the sales on that day divided by the total sales in the week. Then write another formula to calculate the cumulative sales (cumulative sales on a day is equal to sum of all the prior sales and the sale on that day) as well as cumulative percentage. Your final worksheet should look similar to the one shown below. 7) Save the workbook file which will now have 2 worksheets named Order and Cumulative. MGCR 331 – Winter 2015 - Excel Assignment – Part 1
Page 3 of 4
Recall that the name of your workbook is: “M331W15Sec___”.
NOTE: You should go over the Excel Tutorial posted on myCourses if you are not familiar with the Excel Basics. Read and practice Lesson 1 (Getting Started with Excel), 2 (Cell Basics), 3 (Modifying Columns, Rows, & Cells), 4 (Formatting Cells), 5 (Saving), 6 (Creating Simple Formulas), 7 (Worksheet Basics), 9 (Creating Complex Formulas), 10 (Working with Basic Functions), 11 (Sorting Data), 12 (Outlining Data), 13 (Filtering Data) Ensure that you understand Lesson 6, 7, 9 and 10 which are more relevant than others. Submit this workbook file on myCourses by Friday, February 6, 11:59pm.
MGCR 331 – Winter 2015 - Excel Assignment – Part 1
Page 4 of 4
Excel Assignment Part 1 – due Friday, Feb. 6 by 11:59pm The tasks in this assignment aim at developing a basic level of understanding on Excel and how you can use it to develop Decision Support System. This is an individual assignment. Compliance with all the instructions is required for full credit of 3%. Please include your student number, name and section on top of the document. There are a total of 2 tasks to complete within one saved workbook (file) which will contain two worksheets (Order and Cumulative).
Task 1: Create an Order Receipt Instructions 1) Name a worksheet in a workbook as “Order” 2) Type in all text and numbers shown in the spreadsheet below (Ensure that each cell has the right data, i.e., B4 should have value of “12” and A9 should have “Tax”).
MGCR 331 – Winter 2015 - Excel Assignment – Part 1
Page 1 of 4
3) Format all numbers with appropriate formatting (Notice the difference between tax rate formatting and discount formatting; Dollar and Euro symbols in some cells). 4) Center the spreadsheet heading “McGill BookStore” across the spreadsheet. 5) Format all text as displayed in the sample below, including the rotated text labels. 6) Create formulas to display a total for each item ordered, discounted total (in dollar and euro), subtotal, taxes and final total. Your final worksheet should look similar to the one shown below. Note: Formulas should be written such that changing the values in cell B18 (i.e., conversion rate) and B20 (i.e., tax rate) would update all the related calculations. Use currency signs as shown below. 7) Save the workbook. Name it as follows: “M331W15Sec___”. For example: If your name is “Jane Smith”, you are in section 3, and last 3 digits of your student ID is 354. Then your excel workbook should be named as: “M331W15Sec3_Smith_Jane_354” Please do not make any mistakes in naming your file to get full credit for the assignment. Your output should look like this:
MGCR 331 – Winter 2015 - Excel Assignment – Part 1
Page 2 of 4
Task 2: Create a Weekly Sales Chart Instructions 1) Name another worksheet in the same workbook as “Cumulative” 2) Type in all text and numbers shown in the spreadsheet below. (Ensure that each cell has the right data)
3) Format all numbers with appropriate formatting. 4) Center the spreadsheet heading “Weekly Sales Analysis” across the spreadsheet. 5) Format all text as displayed in the sample. 6) Create formulas to display a total for sales, average, standard deviation, maximum and minimum. Then write formula to calculate the sales share of each day. Sales share of a day is calculated by dividing the sales on that day divided by the total sales in the week. Then write another formula to calculate the cumulative sales (cumulative sales on a day is equal to sum of all the prior sales and the sale on that day) as well as cumulative percentage. Your final worksheet should look similar to the one shown below. 7) Save the workbook file which will now have 2 worksheets named Order and Cumulative. MGCR 331 – Winter 2015 - Excel Assignment – Part 1
Page 3 of 4
Recall that the name of your workbook is: “M331W15Sec___”.
NOTE: You should go over the Excel Tutorial posted on myCourses if you are not familiar with the Excel Basics. Read and practice Lesson 1 (Getting Started with Excel), 2 (Cell Basics), 3 (Modifying Columns, Rows, & Cells), 4 (Formatting Cells), 5 (Saving), 6 (Creating Simple Formulas), 7 (Worksheet Basics), 9 (Creating Complex Formulas), 10 (Working with Basic Functions), 11 (Sorting Data), 12 (Outlining Data), 13 (Filtering Data) Ensure that you understand Lesson 6, 7, 9 and 10 which are more relevant than others. Submit this workbook file on myCourses by Friday, February 6, 11:59pm.
MGCR 331 – Winter 2015 - Excel Assignment – Part 1
Page 4 of 4
