Question

	Baby Dolls	Teddy Bears	Toy Cars
Volume	200,000	125,000	225,000
Sales price	$3.50	$2.75	$3.15
Variable Costs	$2.05	$1.75	$2.45
Fixed Costs	$65,000	$125,000	$35,000

Target pretax income=0

Investment=$2 million

Capacity=1 million units

1.Create a spreadsheet that the managers can use for sensitivity analysis. Modify input data in the spreadsheet to answer the following parts of this problem. You might want to add cell references for percentage changes in prices, volumes, and costs.

Answer 1

	Baby Dolls	Teddy Bears	Toy Cars	Total
Volume	200,000	125,000	225,000	SUM(B2:D2)
Sales Mix	B2/$E$2	C2/$E$2	D2/$E$2	SUM(B3:D3)
Sales price	$ 3.50	$ 2.75	$ 3.15	SUM(B4:D4)
Variable Costs	$ 2.05	$ 1.75	$ 2.45	SUM(B5:D5)
Fixed Costs	$ 65,000	$ 125,000	$ 35,000	SUM(B6:D6)
Sales price	B4*B2	C4*C2	D4*D2	SUM(B8:D8)
Less: Variable Costs	B5*B2	C5*C2	D5*D2	SUM(B9:D9)
Contribution	B8-B9	C8-C9	D8-D9	SUM(B10:D10)
Less: Fixed Costs	B6	C6	D6	SUM(B11:D11)
Net Income	B10-B11	C10-C11	D10-D11	SUM(B12:D12)