Homework answers / question archive / Excel HW2 Instructions MIS303 Fall 2020 (Individual Assignment) Introduction and Objectives: You will keep working on the consulting project for this retail store

Excel HW2 Instructions MIS303 Fall 2020 (Individual Assignment) Introduction and Objectives: You will keep working on the consulting project for this retail store

Business

Share With

Excel HW2 Instructions MIS303 Fall 2020 (Individual Assignment) Introduction and Objectives: You will keep working on the consulting project for this retail store. In this HW, you will work more on the data reporting, and also decision supporting with Excel. You are to follow the instructions to complete and submit the assignment as individuals. No collaboration or coworking is allowed. Tasks: Simply put, you must do the following: 1. (2 pts) Keep using the same Excel file you had for HW1. Rename it as FirstName_LastName_ HW2.xlsx. For example, John_Doe_ HW2.xlsx. 2. Follow the instructions below and complete the data reporting tasks in worksheet tab D, and decision support in worksheet tab E and F. 3. Submit the Excel file back to the Blackboard Excel HW2 link. Detailed instructions for each section / worksheet tab are provided below. D. Pivot Table (18 pts) 1. Use all the sale records in the A. Sales Records worksheet (including the columns added in HW1) to create a pivot table. When “Choose where to place the pivot table”, select the option of existing worksheet. Use the Table / Range box below, browse to the worksheet D. Pivot Table, and select the cell A11 to place your pivot table. 2. Create a two-dimensional pivot table in your choice. Your goal will be to make some conclusions regarding factors such as products, salespeople, months, and/or regions. There is no need to include all these factors to your pivot table, and you can use 2-3 of them in your preference. You can use a filter if you like. DO NOT use more than two summary dimensions in the table or use more than one filter, or the pivot table results will be too complicate to view. 3. The summary values must include count of orders, sum of units sold, and average of subtotal (formatted as currency). Add more summarized values if you like. 4. Make sure your pivot table will show a balanced view with the best ratio of width and length that you can find. 5. Add a title/caption at the top of your pivot table (A8) to describe the table content. 6. What are your major findings from this pivot table? Enter 3 major findings from your pivot table to the Question and Answer area (green colored). Sample findings could be which product is best sold; which salesperson is the best sales; which month has the highest or lowest sales; which region has the best or lowest sales, etc. E. Goal Seeking (6 pts) Use this worksheet for the following Goal Seek analysis. The Target Units Sold for products A and C are given on your spreadsheet and they cannot be changed. Use Goal Seek feature to find out how many units of Product B you need to sell (B6) to reach the $150,000 Total Gross Profit goal. Copy and put your answer in H14 in this worksheet. Use Goal Seeking feature to find out how many units of Product B you need to sell to reach the $200,000 Total Gross Profit goal. Copy and put your answer in H15 in this worksheet. F. Scenario Question worksheet. (24 pts) 1. Name the following cells. Cells B7 B8 B9 Suggested Names ATargetUnitSold BTargetUnitSold CTargetUnitSold Cells C7 C8 C9 Suggested Cells Names APrice F11 BPrice F12 CPrice F13 Suggested Names TRevenue TCOGS TGrossProfit 2. Create scenarios using the data and formulas given in the gray area. Use the scenario manager to create the scenarios and the required scenario summary (in a separate worksheet). You will create 3 different scenarios (with unique and meaningful scenario names) by changing the product pricing mix in order to determine their impacts to Total Gross Profit. ? The First Scenario is to raise the price of Product B by $6.00. However, this would cause sales of Product B to fall by 600 units and sales of Product C to increase by 500 units ? The Second Scenario is to raise the price of Product C by $4.00. However, this would cause sales of Product C to fall by 350 units and sales of Product B to increase by 200 units ? The Third Scenario is to raise the price of both Product B and Product C by $3.00. This would cause sales for Products B and C to both decrease by 150 each Your goal is to create a Scenario summary report as a separate worksheet to compare the three scenarios. Make sure your summary show Revenue, COGS and GrossProfit for the comparison. 3. Rename the new sheet as “G. Scenario Report”. Drag and place this worksheet after F. Scenario Question. 4. Question: Which scenario will you take for 2020? Elaborate your product pricing mix of the selected scenario, and why do you choose this scenario? Submission When you are done with worksheet tabs D, E and F. Save the Excel file again, and close it as well as the Excel program on your computer. Go to the Blackboard, upload and submit the completed file back to the Excel HW2 link.