Its an excel case study assignment

SCM 5003 – Logistics Technology

Case Assignment

5% of Final Grade

Excel

The Megamart corporation has collected data on Barbecue (BBQ) sales from March 2019 to April 2021. Your boss has asked you to compile the data to make it useful.

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 1 sheet 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. In the POS Data worksheet. What are sales by week (in both units and dollars)? Name this Sheet “Question A”

   2. In the POS Data (2) worksheet. What are the sales per Item Number by week (in units)? Name this Sheet “Question B”

   3. In the POS Data (3) worksheet. What are sales by store, and for each store, sales for each Item (in units)? Name this Sheet “Question C”

2. In the POS Data (4) worksheet 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. Name this Sheet “Question 3”

1. Use the POS Data (5) worksheet to create a pivot table on a new worksheet. The pivot table should show each store’s sales ($) for each item, grouped by month and year. 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 copy of the Pivot table you made in question 4. Modify the pivot table to shows unit sales by month and year, for each Item Number. Name the Sheet Pivot 2. From the pivot table, create five Line Pivot Charts 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 unit 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, insert a text box in which you will 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