Required:

1. Using the data from 2022 in Gaming Table Cost Data, create an Excel spreadsheet to provide a sensitivity analysis of the effect on operating profit of potential changes in demand for HFI Incorporated, ranging from a 20 percent decrease to a 20 percent increase. Use Contribution Income Statements and What-If Sensitivity Analysis as a guide. Assume that two-thirds of fixed costs are manufacturing related; the remaining one-third are selling-related. The variable manufacturing cost per unit is $30, while the variable selling cost per unit is $5. (Hint: Calculate the DOL for 2022 at a sales volume of 2,400 units.)

2. Using the spreadsheet you created, compute the new operating profit assuming a 10% decrease in demand.

Contribution Income Statements

	2022	2023
	Amount	Percent	Amount	Percent	Change	Notes
Sales	180,000	100	195,000	100	15,000
Variable Costs	84,000	46.67	91,000	46.67	7,000
Total Contribution Margin	96,000	53.33	104,000	53.33	8,000	53.33% is the contribution margin ratio
Fixed Costs	60,000		60,000		0
Operating Profit	36,000		44,000		8,000	$8,000=0.5333*$15,000

What-If Sensitivity Analysis

Units Sold	Variable Cost per Unit	Fixed Costs	Selling Price per Unit	Operating Profit
1,500	30	$69,000	75	7,500
1,500	35	$69,000	75	0
1,500	40	$69,000	75	-7,500
1,500	45	$69,000	75	-15,000

Gaming Table Cost Data

	Per Unit	2022	2023
Fixed costs (per year)		$69,000	$69,000
Selling price	$75
Variable cost	$35
Planned production		2,400 units	2,600 units
Planned sales volume		2,400 units	2,600 units