Contribution Income Statements Using Excel Sensitivity Analysis Goal Seek Input Data Per Unit 2010 2011 Fixed mfg costs $40,000 $40,000 Fixed selling costs $20,000 $20,000 Revenue $75 00 Variable mfg cost $30 00 Var Selling costs $5 00 Planned production 2,400 2,600 Planned sales 2,400 2,600 Requir...

Contribution Income Statements 1 A variety of possible spreadsheets could satisfy this requirement Once example is shown below The sensitivity analysi ...

Contribution Income Statements Using Excel; Sensitivity Analysis; Goal Seek Input Data: Per-Unit 2010 2011 Fixed mfg costs

Question:

Contribution Income Statements Using Excel; Sensitivity Analysis; Goal Seek

Input Data:	Per-Unit	2010	2011
Fixed mfg costs		$40,000	$40,000
Fixed selling costs		$20,000	$20,000
Revenue	$75.00
Variable mfg cost	$30.00
Var Selling costs	$5.00
Planned production		2,400	2,600
Planned sales		2,400	2,600

Required

1. Using the data in Exhibit 9.1, create an Excel spreadsheet to provide a sensitivity analysis of the effect on operating profit of potential changes in demand for HFI, Inc. Use Exhibits 9.2 and 9.6 as a guide.

2. Use the Goal Seek tool within Excel to determine which sales price would allow HFI to earn $100,000 operating profit, assuming that all the other cost information is the same as in Exhibit 9.1. Use Exhibit 9.5 as a guide.