How do i get the 'Projected data table' from the 'project description' sheet into the 'assumptions' data on the 'Part A_Data File CVP' sheet?

Also what formulas are needed for the ones i got wrong continuing on the 'Part A_Data File CVP' sheet? (ones wrong say #DIV/O!)

DIRECTIONS Put A. Cranes 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 Da not han conditirtha Ston boa, or else this spreadsheet cannot be used over and well 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 Usawal hot de 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 Boundup formula in Excel to round up to full unit. The general roundup formula for break-even will be: ROUNDUP Total Ented costs contribution mangin per unit, Check figures: Denver: 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 ancome based on the following two what-if scenarios for both the divisions. Use appropriate formula and format the cells as currency al 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 scenarier 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 Cost Accountant in Realin 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? Write your answer in the Explanation box.) A 1 DO 2 3 4 5 6 7 $ CVP Mode 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 property, the company should be able to use this spreadsheet over and over, using different what if assumptions. by WC Da 10 11 12 13 2 qu 8 $S$8 14 ch 15 16 17 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 toy in house and the El Paso division import the toy from Mexico. The following projected data is provided for the Fourth quarter of 2011: 19 20 21 bre 22 23 Projected data Table: Debrer El Paso Quarterly volume of unito cold in the fourth quarter 20Y| 20.000 20,000 Revenue per unit $35 335 Total Variable Costs of Goods Sold $60,000 $130.000 Total Fixed Costs of Goods Sold $70,000 30 Total Variable Selling and Administration Expenses $40,000 $50,000 Total Fixed Selling and Administration Expenses $30,000 $20,000 3.639 24 WWE fu: 25 26 27 28 24 30 31 32 33 34 35 36 37 30 34 40 41 b 500 g 5. Project Description Part A Data File CVP 1 2 Denver El Pato 3 4 5 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 Siddique, Salinas Update the motion table by linking data from the Projected Data Table in the Project Description"sheet Quarterly volume of units sold in the fourth quarter 2011 6 7 8 9 10 11 12 13 14 15 16 2 Projected Contribution Margin based Income Statement For Fourth quarter 2011 Denver $ So cil Part A Data Fie CVP Partoute udget Grading Crea o El Paso $ Answer check SO Correct cal.cat Sales Tatsach Project Descroton A B E 2 Projected Contribution Margin based Income Statement For Fourth quarter 2041 Denver S Sales SO Total Variable costs SO Contribution margin 50 Total Fixed costs SO Net income SO El Paso $ Answer check SO Correct SO Correct So correct SO Correct SO Correct 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 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/01 #DIV/0! #DIV/0! ADIV/0! #DIV/0! #DIV/OR 4 Sensitivity Analys Conservative scenario: Sales decrease by 40% than the projected sales -40% Net Income under conservative scenario: #DIV/0! #DIV/0! Optimistic scenario: Sales increase by 40% than the projected sales 40% Net Income under optimistic scenario: HOIV/0! #DIV/0! 5. Explanation: Project Description Part A Data File CVP Part B Data File Budget Grading Criteria