Excel

Use the Excel Megamart Case File to complete the following work. Treat this as a report you will be giving your boss.

1. Create 4 new copies of the POS 1sheet since you will be performing multiple tasks with the data. You should have 5 copies of POS1.

1. Use Subtotals to answer the following questions. 8 marks
   1. Use POS Data. What are sales by week (in units and dollars)? Name this Sheet "Question A"
   2. Use POS Data (2) What are the sales per Item Number by week? Name this Sheet "Question B"
   3. Use POS Data (3) What are sales by store, and for each store, sales for each Item? Name this Sheet "Question C"
   4. Use POS Data (4) to create a Table, and then use sorting and filtering to show Sales in dollars and units for store 697 in Chronological order for item number 1199.

1. Use Pivot Tables shows each store's sales ($) for each item, grouped by month and year. Do this on a new sheet from the data on POS Data (5). Name the new sheet Pivot 1. 2 marks.

1. Now, you want to use a new pivot table and pivot charts to help with Forecasting. Make a second copy of the Pivot table you made in question 3. Create a pivot table that shows unit sales by month and year, for each Item Number. Name the Sheet Pivot 2. From the pivot table, create five Line Pivot Chart on one new sheet, name the sheet Charts. Each chart should be for one item, and show Sales in units over time. Time should include both months and years. The fifth chart should show total sales over time. Make sure the charts are ready for a presentation. You will be editing the pivot table to get the chart the way you like it. (Hint, each time you change the pivot table, the chart will change, so once you get the chart the way you want it, copy it and paste it as a picture so it doesn't change). 3 Marks

1. On the Charts sheet, discuss the major issues concerning forecasting (at least 100 words). Your discussion should include types of forecast that should be used, should you forecast item sales or total sales, and any other issues you deem important. This last part will affect your marks significantly, so review your Supply and Demand notes if necessary. The shape of the lines on the charts should help you determine which forecasting techniques to use. 5 Marks

1. The Warehouse Manager needs to know what to stock and when. Click on Sheet 1, and use conditional functions (=sumif or =sumifs) to complete the two tables. Note, this should be done by creating one formula. Using Mixed cell references, you will be able to copy the one formula down and across the table. 2 Marks