MIS 303 Scenario Question Excel Worksheet

DescriptionExcel HW2 Instructions
MIS303 Fall 2022
(Individual Assignment 35 points)
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:
(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.
1.
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 ordertotals (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
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. (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)
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.

Purchase answer to see full
attachment

We offer the bestcustom writing paper services. We have done this question before, we can also do it for you.

Why Choose Us

  • 100% non-plagiarized Papers
  • 24/7 /365 Service Available
  • Affordable Prices
  • Any Paper, Urgency, and Subject
  • Will complete your papers in 6 hours
  • On-time Delivery
  • Money-back and Privacy guarantees
  • Unlimited Amendments upon request
  • Satisfaction guarantee

How it Works

  • Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
  • Fill in your paper’s requirements in the "PAPER DETAILS" section.
  • Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
  • Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
  • From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.