MIS303 Fall 2022
Introduction and Objectives:
(Individual Assignment 35 points)
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.
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 (14 pts)
• 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).
• 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.
(3 points for each of 3 parts)
• The summary values must include count of orders, sum of units sold, and average
of order totals (formatted as currency). (2 points)
• Make sure your pivot table will show a balanced view with the best ratio of width
and length that you can find. (2 points)
• Add a title/caption at the top of your pivot table (A8) to describe the table content. (1
point)
• 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)
(2 points)
Which month has the lowest number of units sold? (Fill this answer in cell A4) (2
points)
Which region has the lowest sum of order total? (Fill this answer in cell A5)
(1 point)
E. Goal Seeking (5 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. (16 pts)
1. Name the following cells (2 points).
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
2. Create scenarios using the data and formulas given in the gray area. (4 points for each of
the 3 scenarios)
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.
3. Rename the new sheet as “G. Scenario Report”. Drag and place this worksheet after
F. Scenario Question.
4. Question: Which scenarios gives us the highest gross profit? Provide your answer in
the green shaded region on worksheet F. (2 points)
