Projected Contribution Margin based Income Statement For Fourth quarter 20Y2 Denver Santa Fe $ Answer c Sales $6,000,000 $6,000,000 | Correct Total Variable costs $1,050,000 $2,720,000 Correct Contribution margin $4,950,000 $3,280,000 Correct Total Fixed costs $1,970,000 $300,000 Correct Net income $2,980,000 $2,980,000 Correct 3 Calculate the followings: Denver Santa Fe Variable Cost per unit $5.25 $13.60 Correct Contribution Margin per unit $24.75 $16.40 Correct Break Even units (use Roundup formula) 79,596.00 18,293.00 Correct Break Even sales (Break even units x Selling price per unit) $2,387,880 $548,790 Correct Margin of Safety percentage 60.20% 90.85% Correct Operating Leverage 1.66 1.10 Correct 4 Calculate the net income under the Conservative and Optimistic scenarios: Denver Santa Fe Sensitivity Analyis Conservative scenario: Sales decrease by 40% than the projected sales -40% -40% Net Income under conservative scenario: Optimistic scenario: Sales increase by 40% than the projected sales 40% 40% Net Income under optimistic scenario: 5 Write your answer to Question 5 in Explanation box below:DIRECTIONS: CVP Model and Budget The purpose of this project is twofold: Part A. CVP analysis: Par It will give you experience of: 1. Complete the green cells in the Assumption box in the Part A Data File CVP worksheet by linking You 1. Undertaking profitability analysis, and understanding the link between the cost structure and profitability the appropriate cells in the Projected Data Table in the Project Description worksheet. of a company. Do not hardcode in the Assumption box, or else, this spreadsheet cannot be used over and 2. Preparing a sales budget and purchase budget for its merchandising division. over if the projections are changed Your goal will be to use Excel in such a way that any changes to the assumptions will correctly 2. Prepare projected Contribution margin-based Income Statement for the fourth quarter of 20Y3 for of ripple through the entire profitability analysis and budget preparation. Denver and Santa Fe Divisions based on the projected data transferred in the Assumption box. If executed properly, the company should be able to use this spreadsheet over and over, using different "what if" assumptions. Use formula as needed. Do not hardcode. You 1 . inst Description of the business: 3. Calculate the following for both divisions using appropriate formula: Variable costs per unit 2. Us Fitness Fast Inc. is specialized in selling organic protein supplements. Currently, they are launching a buc new organic drink rich in electrolytes with minimal preservatives, named Complan. The company has - Contribution margin per unit two divisions - one at Denver, CO and the other at Santa Fe, NM. - Break-even units Ch The Denver division manufactures the supplement in-house and the Santa Fe division custom orders Use Roundup formula in Excel to round up to full unit. The general roundup formula for break- Tot and imports them from a manufacturer in Mexico. even will be: =ROUNDUP(Total Fixed costs/Contribution margin per unit,0). Tot The following projected data is provided for the fourth quarter of 20Y3: - Break-even sales in dollar - Margin of safety in percentage Operating leverage Projected data Table: Denver Santa Fe 4. Undertake a sensitivity analysis assessing the impact of operating leverage on net income based on the following two what-if scenarios for both the divisions. Sub Quarterly volume of units sold in the fourth quarter 20Y3 200,000 200,000 (Use appropriate formula and format the cells as 'currency"): - TH |Revenue per unit $30 $30 - S Total Variable Costs of Goods Sold $900,000 $2,520,000 a) Conservative scenario: - PI Total Fixed Costs of Goods Sold $1,650,000 $0 What would be the projected net income (loss) if sales decrease by 40% in the fourth quarter? Total Variable Selling and Administration Expenses $150,000 $200,000 b) Optimistic scenario: or Total Fixed Selling and Administration Expenses $320,000 $300,000 What would be the projected net income (loss) if sales increase by 40% in the fourth quarter? 5. Assume that you are in the position of Cost Accountant in Fitness Fast Inc. Management seeks clarification from you on the notable differences in breakeven units between the two divisions, despite both divisions reporting identical total sales and net income for the period. Additionally, They Sch are keen to understand the reasons behind the substantial disparities in the comparative data of the sensitivity analysis for the two divisions. I do Explain the reasons for such differences to management. (Write your answer in the Explanation box.)