Reference no: EM133583620

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 co- working is allowed.

Task:

Simply put, you must do the following:

A. Keep using the same Excel file you had for HW1.
B. Follow the instructions below and complete the data reporting tasks in worksheet tab D, and decision support in worksheet tab 2 and 3.

Detailed instructions for each section / worksheet tab are provided below.

1. Pivot Table

I. 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 (1 point).

II. Create a two-dimensional pivot table using the "Sales Month" and the "Region". Next, we want to perform the following operations:
Find the count of all orders by month and region. Find the sum of total units sold by month and region. Find the average of order totals by month and region.

III. The summary values must include count of orders, sum of units sold, and average of ordertotals (formatted as currency).

IV. Make sure your pivot table will show a balanced view with the best ratio of width and length that you can find.

V. Add a title/caption at the top of your pivot table (A8) to describe the table content.

VI. Next, we want to find the following from the pivot table:

Which month has the highest number of total orders? (Fill this answer in cell A3)
Which month has the lowest number of units sold? (Fill this answer in cell A4)
Which region has the lowest sum of order total? (Fill this answer in cell A5)

2. Goal Seeking

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.

3. Scenario Question worksheet.

I. Name the following cells.

Cells Suggested
Names Cells Suggested
Names Cells Suggested
Names
B7 ATargetUnitSold C7 APrice F11 TRevenue
B8 BTargetUnitSold C8 BPrice F12 TCOGS
B9 CTargetUnitSold C9 CPrice F13 TGrossProfit

II. 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 to $51 while keeping price for A and C same. Notice that the revenue of Product B and the Total Revenue goes up. Name it as "Product B Change".

The Second Scenario is to raise the price of Product C by $4.00 to $37 while keeping that for A and B same as in the beginning (so bring back B to $45 and A stays as it is at $55). Notice that the revenue of Product B and the Total Revenue goes up. Name it as "Product C Change".

The Third Scenario is to raise the price of both Product B and Product C by $3.00 (so for B it becomes $48 and for product C it becomes $36). Notice that the revenue of Product B and Product C and Total Revenue goes up. Name it as "Product B and C Change".

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

III. Rename the new sheet as "Scenario Report". Drag and place this worksheet after 3. Scenario Question.

IV. Question: Which scenarios gives us the highest gross profit? Provide your answer in the green shaded region on worksheet 3.