50.1K

Verified Solution

Link Copied!

1. 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.
   1. 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
  2. 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.
  3. 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

Answer & Explanation Solved by verified expert

See Answer