Excel Question

The purpose of this assignment is for you to understand how to export reports from an ERP system and then use Excel to create formula-based templates & pivot charts that allow accountants and managers to analyze the data more easily. After completing this assignment, you should feel comfortable exporting data from an ERP system and using Excel to analyze data further. This assignment will be graded on your ability to export the correct reports and create the templates and pivot charts. Please refer to the recorded for insights on the formulas and templates.

Part 1

1. Create a single excel file that includes an exported sales and production report from your ERPSim game. Each report should have their own tab and be labeled Sales Report and Production Report.
2. Create a tab that will act as a template to analyze the sales report. The tab should include the following details and measures.
   1. Item #: (this can be hard coded for the products in the game) For example AA-F01.
   2. Product (material description): This should be formula-based (xlookup or vlookup).
   3. Total sales orders for each product: Should be formula-based.
   4. Total sales for each product: Should be formula-based.
   5. Total quantity sold for each product: Should be formula-based.
   6. Average sale per order: Should be formula-based.
   7. Average quantity sold per order: Should be formula-based.
   8. Weighted Average Selling Price (WASP): Should be formula-based.
   9. % of total sales by product: Should be formula-based.
3. Create a tab that will act as a template to analyze sales further for a specific region (No, So, or We). This tab should include the following details and measures.
   1. Item #: (this can be hard coded for the products in the game) For example AA-F01.
   2. Product (material description): This should be formula-based (xlookup or vlookup).
   3. Total sales orders for each product: Should be formula-based.
   4. Total sales for each product: Should be formula-based.
   5. Total quantity sold for each product: Should be formula-based.
   6. Average sale per order: Should be formula-based.
   7. Average quantity sold per order: Should be formula-based.
   8. Weighted Average Selling Price (WASP): Should be formula-based.
   9. % of total sales by product: Should be formula-based.
4. Create a tab that will act as a template to analyze the relationship between sales and production. This tab should include the following details and measures.
   1. Item #: (this can be hard coded for the products in the game) For example AA-F01.
   2. Product (material description): This should be formula-based (xlookup or vlookup).
   3. Total quantity sold for each product: Should be formula-based.
   4. Total quantity produced for each product: Should be formula-based.
   5. % of inventory sold: Should be formula-based.
   6. Remaining inventory on hand: Should be formula-based.

Part 2

Create two pivot charts using the exported sales report from your ERPSim game. 1) Create a pivot bar chart to visualize the top 3 sellers in the North Area, 2) Create a pivot column chart to visualize the bottom 3 sellers in DC12.