Question

Create a Spreadsheet for the following inputs, report, and statistics.

Part 1 Enter inputs and develop income statement report.

Create an input area on your spreadsheet. Enter the following:

Current Sales Volume (Units)	5,000
Variable Cost Per unit	$7.60
Sales Price Per Unit	$19.00
Fixed Costs	$300,000
Target Net Income Level	$120,000

When grading, these inputs will be changed by the instructor.
Your model should automatically change appropriately.

Develop a Spreadsheet model to report the following:

• Income Statement at Current Sales Level in a Contribution Margin Format
• Income Statement at break-even point in a Contribution Margin Format
• Income Statement at desired level of net income in a Contribution Margin Format.
• The Contribution Margin Income statement should include a per unit column and a percent column

The Income Statement Format should look like this:

	Per Unit	%	At Current Level	At Break even Level	At Target Net Income Level
Sales		100%
Variable Costs
Contribution Margin
Fixed Costs	Leave area blank
Net Income (loss)

• The only absolute in the Income Statement is the sales percent which is 100%
• In the Per Unit Column and in the Percent Column Leave the Fixed Costs Line and the Net Income (Loss) line blank.
• All other Data in the table is derived from formulas based on the inputs.
• When the instructor changes the inputs, the income statement report should automatically change.

Part 2 Add to your spreadsheet formulas for the following statistics. Each statistic should be clearly labeled and the answer should be based on formulas from the inputs or from the income statement. When the instructor changes the inputs, the statistics should automatically change.

• Margin of safety assuming the Company is operating at the Target net income level.
• Break Even Point in Units
• Degree of Operating Leverage Assuming the company is operating at the Target net income level.

Save, then upload your Excel file to the drop box.

Grading: You can expect the instructor to change any of the inputs When any input is changed, the solution area of the spreadsheet should change automatically and correctly.

• Appearance is important - format cells appropriately.
• Submit: Submit your assignment to the Excel Case 3 Drop-box. Your file name should be Excel Case 3 and your name (e.g. Excel Case 3 John Smith). The instructor will review your assignment and return it to you with comments and a initial grade.
• You may resubmit your case for a final grade.

Answer 1

INPUTS: Current Sales Volume (Units Variable Cost Per unit Sales Price Per Unit Fixed Costs Target Net Income Level 5,000 $8 $19 $300,000 $120,000 OUTPUTS Income Statement - Contribution Margin Format At current level $95,000 $38,000 $57,000 At BEP level $500,000 $200,000 $300,000 At Target NI level $700,000 $280,000 $420,000 Per unit % Sales Variable Costs Contribution Margin Fixed Costs Net Income (Loss) $19 $8 $11 100% 40% 60% Ref: workings] [Sales x 40%) $300,000 $300,000 $300,000 [Remained constant] $243,000 $0$120,000

Workings: Breakeven point, in dollars Fixed costs/ Contribution Margin ratio $300,000 Fixed costs Contribution margin ratio Breakeven point, in dollars 0.60 [$11/ $19] $500,000 [$300,000/0.60] Target sales, in dollars (Fixed costsTarget income) / Contribution Margin ratio. Fixed costs+Target income Contribution margin per unt Breakeven point, in dollars $420,000 [$300,000+$120,000] 0.60IS11/ $19] $700,000 [$420,000/0.60]