What formula would get the data from the projected data table to the assumptions table? also what are tbe correct formulas for the areas that dont say correct?

DIRECTIONS Part A.canal! 1. Complete the green cells in the Assumption box in the Part A Data File CVP worksheet by linking the appropriate cells in the Projected Data Table in the Project Description worksheet Do not hardcode that ons, or else, this spreadsheet cannot be used over and over projections changed 2. Prepare projected Contribution margin-based Income Statement for the fourth quarter of 2011 for Denverand El Paso divisions based on the projected data transferred in the Assumption box Une formula De Idade Check figure: Net Income should be $500,000 for both divisions. 3. Calculate the following for both divisions using appropriate formula: Variable costs per unit - Contribution margin per unit -Break-even units Use Roundup formula in Excel to round up to full unit. The general roundup formula for break-even will be ROUNDUP Total Fixed costs/contribution margin per unit) Check figures:Deriver: 3,334 units; El Paso: 770 units -Break-even sales - Margin of safety in percentage Operating leverage 4. Undertake a sensitivity analysis assessing the impact of operating leverage on net income based on the following two what-ifscenarias for both the divisions. {Use appropriate formula and format the cells as currency: a) Conservative scenario: What would be the projected net income if sales decrease by 40% in the fourth quarter? Check figure: for Denver division: $260,000 b) Optimistic scenarios What would be the projected net income if sales increase by 40% in the fourth quarter? Check figure: for El Paso division: $708,000 5. Assume that you are in the position of Cast Accountant in Resin Inc. and the management seeks an explanation from you regarding the disparity among the comparative profit data of the sensitivity analysis of the two divisions. How do you explain this difference? (Weite your answer in the Explanation box. B 1 2 IN 3 4 5 CVP Model and Budget The purpose of this project is twofold It will give you experience: 1. Undertaking profitability analysis and understanding the link between the cost structure and profitability of a company, 2. Preparing a sales budget and purchase budget for its merchandising division. Your goal will be to use Excel in such a way that any changes to the assumptions will correctly ripple through the entire profitability analysis and budget preparation. If executed properly, the company should be able to use this spreadsheet over and over, using different "what if' assumptions. 7 8 10 11 12 13 14 15 16 17 18 19 20 21 22 Description of the business: Realm Inc. is specialized in selling pet toys. Currently, they are launching a new item - strike and run" which throws ball every time a paddle in the toy is pressed. The company has two divisions - one at Denver and the other at El Paso. The Denver division manufactures the tay in house and the El Paso division import the tay from Mexico. The following projected data is provided for the Fourth quarter of 2011: MENNYN 23 24 25 Projected data Table: Dexter El Paso Quarterly volume of unito cold in the fourth quarter 20Y 20.000 20,000 Revenus per unit $35 $35 Total Varisble Costs of Goods Sold $60,000 $130,000 Total Fixed Costs of Goods Sold $70,000 $0 Total Variable Selling and Administration Expenses $40,000 $50,000 Total Fixed Selling and Administration Expenses $30,000 $20,000 26 27 D A B 1 2 Denver El Paso 3 4 5 1 ASSUMPTIONS Product: Strike and Run: Revenue per unit Total Variable costs of Goods Sold Total Fixed Costs of Goods Sold Total Variable Selling and Administration Expenses Total Fixed Selling and Administration Expenses 6 7 8 9 10 Quarterly volume of units sold in the fourth quarter 2011 A B D 2 Projected Contribution Margin based Income Statement For Fourth quarter 2011 Denver 11 12 13 14 15 16 17 18 19 20 21 22 Sales Total Variable costs Contribution margin Total Fixed costs Net income 50 SO $0 SO SO El Paso Answer check SO Correct So correct So correct SO Correct SO Correct 23 3 Variable Cost per unit Contribution Margin per unit Break Even units Break Even sales Margin of Safety percentage Operating Leverage #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 Sensitivity Analyis Conservative scenario: Sales decrease by 40% than the projected sales -40% Net Income under conservative scenario: NDIV/0! #DIV/0! Optimistic scenario: Sales increase by 40% than the projected sales 40% Net Income under optimistic scenario: 5. Explanation: NDIV/01 HOIV/0