Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Operations Management

Authors: William J Stevenson

12th edition

2900078024107, 78024102, 978-0078024108

More Books

Students also viewed these General Management questions

Question

What is one of the skills required for independent learning?Explain

Answered: 1 week ago

Question

=+(5.42) E[n \N2] = a, Var[n 'N.] - B - x2 +H-P n

Answered: 1 week ago