We learned how to use the Scenario Manager to view the results of a model you will build to determine the profitability based on the scenarios below.
You have been assigned to analyze the profitability of Elon Musk's autobiography. The following assumptions have been made:
Determine how the publisher's profit will vary with the Scenarios provided (Strong Sales, Break Even Sales, Weak Sales).
10 points
One-time royalty payment to Elon Musk	$9,000,000		$11,322,500	Hardcover	Hardcover	Paperback
Fixed cost of producing the hard cover version of the book	$1,500,000		Scenarios	Demand	Variable Cost	Variable Cost
Variable cost of producing EACH hard cover version of the book	$12.00		Strong Book Sales	1,800,000	$13.00	$9.50
Sale price of EACH hard cover book	$15.99		Break Even Book Sales*		$12.00	$7.50
Hard cover book demand	1,500,000		Weak Book Sales	1,200,000	$10.00	$6.50
Fixed cost of producing the paperback version of the book	$1,000,000
Variable cost of producing EACH paperback version of the book	$7.50		*Use Goal Seek to find the Break Even Hardcover Demand (profit 0) of the original model and manually enter this
Sale price of EACH paperback book	$11.99		number in the Hardcover Demand box above, these are the same variable costs that are in the original pro forma assumptions.
Paperback book demand will be 2.5x the hard cover book demand	3,750,000
Build a model below (Sales, Variable Costs, Fixed Costs, Profit) and run each Scenario
Prof Forma Model	$
Total Sales (demand * sales price)	$68,947,500
Total Variable Costs (demand * variable costs)	$46,125,000
Total Fixed Costs (there should be 3 fixed costs above)	$11,500,000
Profit (Sales - Variable Costs - Fixed Costs)	$11,322,500