Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Scenario Analysis: Go back to the sheet of Income Statement . Use the scenario manager to create three scenarios stated below and create a scenario
- Scenario Analysis: Go back to the sheet of Income Statement. Use the scenario manager to create three scenarios stated below and create a scenario summary, which will be a separate worksheet.
- You will create 3 different scenarios by changing the product pricing mix in order to determine their impacts to Net Profit.
- The First Scenario is to raise the price of Product B by $5.00. However, this would cause sales of Product B to fall by 800 units and sales of Product C to increase by 700 units. Title the scenario name as Product B Price Change
- 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 550 units and sales of Product B to increase by 400 units. Title the scenario name as Product C Price Change
- The Third Scenario is to raise the price of both Product B and Product C by $6.00. This would cause sales for Products B and C to both decrease by 350 units each. Title the scenario name as Product B and C Price Changes
- Create a Scenario summary report, which will become a new and separate worksheet. Make sure the Results Cells include Earnings Before Taxes and Net Profit. Rename this worksheet as Scenario Analysis, and move the sheet to the right of the sheet of Goal Seek.
- Write up a brief conclusion on your scenario analysis result in the sheet of Scenario Analysis, below the summary report. Which scenario will bring the company the optimum outcome of 2017 sales?
Revenue | Unit Sold | Unit Price | Unit Cost | Revenue | COGS | |
Product A | 6543 | $62.00 | $48.00 | |||
Product B | 6205 | $48.00 | $35.00 | |||
Product C | 6838 | $46.00 | $34.00 | |||
Product D | 6060 | $35.00 | $27.00 | |||
Total Revenue | ||||||
Total COGS | ||||||
Gross Profit | ||||||
Operating Expenses | ||||||
Salaries | ||||||
Advertising | ||||||
Miscellaneous | ||||||
Total Operating Expenses | ||||||
Earnings Before Taxes | ||||||
Taxes | ||||||
Net Profit |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started