Part B.

what formulas do i put in? especially the formulas for the two i can not hard code.

Part B. Preparation of sales and puchase budget: You are in the process of preparing budget for 20Y2 for El Puso Divinion. - Based on the forecasts of the Sales Department, the sales are expected to increase by 20 per cent in each quarter. - According to the company's policy, the desired ending inventory is expected to be 15 per cent of the budgeted sales of the current quarter. - Ending inventory at the end of the Fourth quarter of 20Y1 is 3,500 units. 1. Complete Budget assumption box in the Part B_Data file Budget. (See the comments.) 2. Prepare a Sales budget and Purchase budget for El Paso Division for 20Y2. (Use appropriate formula. Use Roundup function to calculate the number of units as a full unit in each row of the budget tables) Check figures: (Total budget sales units: 128,832 units) (Total budgeted purchase: 131,553 units) A D H 1 Budget Assumptions for El Paso Division Siddiq, Sala: 2 Projected/Budgeted sales in unit for the fourth Quarter of 2041 Do not hard code. In the appropriate cel Project Description worksheet to get the data 3. The percentage of sales increase from one quarter to next quarter 4 Desired Ending Inventory at the end of each quarter is 15% of the Saddique, Saint 5 Budgeted sales unit of the same quarter Complete all, and 36 from the data provided in the Part B in the Directions boxn 6 Budgeted Ending inventory units for the Fourth quarter of 2011 Project Description worksheet. These three are the only cel you are supposed to hard code 7 8 Sales Budget for the 2012 9 1st Quarter 2nd quarter Bed quarter 4th quarter Total sale units 10 Sales unit 11 Answer check 13 Purchase Budget for the 2012 14 1st quarter 2nd quarter 3rd quarter Ath quarter Total sales 15. Budpeted Sales unit 16 Plus Desired Ending Inventory 17 Equal to Total need 16 Less Beginning inventory 19 Equato Budgeted Purchase 21 xwerk Project Orion Part A Day Purt Data File Budget A B using different "what if" assumptions. 11 12 13 14 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. 15 16 17 18 19 20 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: Projected data Table: Denver 21 22 23 24 25 26 27 28 29 30 31 Quarterly volume of units sold in the fourth quarter 2011 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 El Paso 20,000 $35 $60,000 $70,000 $40,000 $30,000 20,000 $35 $130,000 $0 $50,000 $20,000 Project Description Part A Data File CVP Part B Data File Budget Grading Critena DAN A B 1 D E 2 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 Denver $35 $60,000 $70,000 $40,000 $30,000 El Paso $35 $130,000 SO $50,000 $20,000 9 10 11 Quarterly volume of units sold in the fourth quarter 2011 20,000 20,000 12 13 14 15 16 17 18 19 20 Sales Total Variable costs Contribution margin Total Fixed costs Net income Projected Contribution Margin based Income Statement For Fourth quarter 2011 Denver $ $700,000 $100,000 $600,000 $100,000 $500,000 El Paso $ Answer che $700,000 Correct $180,000 Correct $520,000 Correct $20,000 Correct $500,000 Correct 21 Project Description Part A Data File CVP Part B Data File Budget Ready Grading Criteria A B D 21 22 23 24 3 Variable Cost per unit Contribution Margin per unit Break Even units Break Even sales Margin of Safety percentage Operating Leverage 25 26 27 28 29 30 81 $5 $30 3,334 $116,666.67 83.33% 100000.00 $9 Correct $26 Correct 770 Correct $26,923 Check formula 96.15% Check formula 20000.00 Check formula 4 Sensitivity Analys Conservative scenario: Sales decrease by 40% than the projected sales 40% Net Income under conservative scenario: 32 33 -$140,000 $188,000 Check formula Optimistic scenario: Sales increase by 40% than the projected sales 40% 34 35 36 37 38 39 40 41 Net Income under optimistic scenario: 5. Explanation: $340,000 $278,000 Check formula Project Description Part A Data File CVP Part B Data File Budget Ready Grading Critere