Eliza Corp has provided the following forecast information for its Dog Product for 2019 Table 1 Source data for the Dog Product for 2019 Selling price per unit $260 00 Variable cost of goods sold per unit $160 00 Fixed manufacturing costs $8,000,000 Fixed selling and admin costs $4,000,000 Units produced and sold 200,000 The company wishes you to use Excel to undertake cost volume profit analysis on the above product data You are required to construct suitable formulae to prepare a financial model that provides five types of information for 2019 The company wishes you to use Excel to undertake cost volume profit analysis on the above product data You are required to construct suitable formulae to prepare a financial model that provides five types of information for 2019 a contribution margin based income statement (i e variable costing) that clearly shows the sales revenue, variable costs, contribution margin, fixed costs and net income for the month A calculation of the monthly break even point in units A calculation of the monthly margin of safety percentage A calculation of the monthly operating leverage Sensitivity analysis assessing the impact of operating leverage on net income Analysis of the change in net income from the following three what if scenarios What is the total monthly net income if sales increase by 50 What is the total monthly net income if sales increase by 20 What is the total monthly net income if sales decrease by 50 You do not need to provide full revised income statements to answer these questions Use your knowledge of operating leverage to recalculate your existing net income figure for each month See the screenshots below for an illustrative example, that should help you construct the necessary formulas ( Note your figures will be different ) Your model should automatically recalculate this information if the user changes any of the source data variables (i e selling price, variable cost per unit, fixed costs and units sold) As a result, use direct cell references to the key source data rather than hard coding any of it in your formulae For example, entering a total sales formula of 20,000 20 will not allow the units sold or sales price per unit to be changed by the user within your spreadsheet Required 1 Prepare an Excel model that provides all of the requested information listed above

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 13, 2024

Eliza Corp has provided the following forecast information for its Dog Product for 2019: Table 1: Source data for the Dog Product for 2019 Selling

Eliza Corp has provided the following forecast information for its Dog Product for 2019:

Table 1: Source data for the Dog Product for 2019

Selling price per unit	$260.00
Variable cost of goods sold per unit	$160.00
Fixed manufacturing costs	$8,000,000
Fixed selling and admin costs	$4,000,000
Units produced and sold	200,000

The company wishes you to use Excel to undertake cost-volume-profit analysis on the above product data. You are required to construct suitable formulae to prepare a financial model that provides five types of information for 2019:

a contribution margin-based income statement (i.e. variable costing) that clearly shows the sales revenue, variable costs, contribution margin, fixed costs and net income for the month

A calculation of the monthly break-even point in units

A calculation of the monthly margin of safety percentage

A calculation of the monthly operating leverage

Sensitivity analysis assessing the impact of operating leverage on net income. Analysis of the change in net income from the following three what-if scenarios:
1. What is the total monthly net income if sales increase by 50%?
2. What is the total monthly net income if sales increase by 20%?
3. What is the total monthly net income if sales decrease by 50%?

You do not need to provide full revised income statements to answer these questions. Use your knowledge of operating leverage to recalculate your existing net income figure for each month. See the screenshots below for an illustrative example, that should help you construct the necessary formulas (Note: your figures will be different!).

Your model should automatically recalculate this information if the user changes any of the source data variables (i.e. selling price, variable cost per unit, fixed costs and units sold). As a result, use direct cell references to the key source data rather than hard coding any of it in your formulae. For example, entering a total sales formula of =20,000*20 will not allow the units sold or sales price per unit to be changed by the user within your spreadsheet.

Required:

1. Prepare an Excel model that provides all of the requested information listed above