I need help with the blanks and check formula on part B. I need the formulas for Part B_Data File Budget.

Part B. Preparation of sales and puchase budget: You are in the process of preparing budget for 20Y2 for El Paso Division. -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 2011 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 2012. (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) G H 1 Budget Assumptions for El Paso Division Sidd, Salina 2 Projected/Budgeted sales in unit for the Fourth Quarter of 2011 20,000 Do not hard code. Unk the appropriate od in Project Description worksheet to get the 3 The percentage of sales increase from one quarter to next quarter 20% 4 Desired Ending Inventory at the end of each quarter is 15% of the 15% Siddiq, Salina 5 Budgeted sales unit of the same quarter Completed 83, 84 and 86 from the data provided in the Parti in the Orections boxin 6 Budgeted Ending inventory units for the Fourth quarter of 2011 3,500 Project Descricon worksheet. These three are the only ones you are supposed to hard code 7 8 Sales Budget for the 2012 9 1st quarter 2nd quarter 3rd quarter 4th quarter Total sale units 10 Sales unit 24,000 28,000 32,000 36,000 120.000 11 Answer Correct Check formula check formula Check formulo Check formula 13 Purchase Budget for the 2012 14 Ist quarter 2nd quarter 3rd quarter Ath quarter Total sales 15 Budgeted Sales unit 24,000 32,000 36,000 92.000 16 Plus Desired Ending Inventory 3,600 17 Equal to Total need 18 Beginning Inventory 19 Equal to Budgeted Purchase 20 Answer onarte (vo Part Die Budget B A Projected data Table: D Denver El Paso 20,000 $35 Quarterly volume of units sold in the fourth quarter 20Y1 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 $60,000 $70,000 $40,000 $30,000 20,000 $35 $130,000 $0 $50,000 $20,000 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 b 5. 40 41 w me 42 ex Project Description Part A Data File CVP Part 8_Data File Budget Grading Criteria Read B D 2 3 4 5 6 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 SK Up inh Da De Denver $35 $60,000 $70,000 $40,000 $30,000 El Paso $35 $130,000 $0 $50,000 $20,000 7 8 9 10 11 Quarterly volume of units sold in the fourth quarter 2011 20,000 20,000 2 Projected Contribution Margin based Income Statement For Fourth quarter 2011 Denver 12 13 14 15 16 17 18 19 20 21 22 Sales Total Variable costs Contribution margin Total Fixed costs Net income $700,000 $100,000 $600,000 $100,000 $500,000 El Paso Answer check $700,000 Correct $180,000 Correo S520,000 Correct $20,000 Correct $500,000 Correct calle Variable Carunt Project Description Part A Data File CVP Part B Data File Budget ce Grading Criteria RESO A B 22 23 24 25 3 Variable Cost per unit Contribution Margin per unit Break Even units Break Even sales Margin of Safety percentage Operating Leverage $5 $30 3,334 $116,690.00 83.33% 1.20 D 59 Corred $26 Correct 770 Correct $26,950 Correct 96.15% Correct 1.04 Correct 26 27 28 29 30 31 32 33 34 35 4 Sensitivity Analyis Conservative scenario: Sales decrease by 40% than the projected sales -40% Net Income under conservative scenario: $260,000 $292,000 Correct Optimistic scenario: Sales increase by 40% than the projected sales 40% 36 Net Income under optimistic scenario: Project Description Part A Data File CVP Parti Oata File budget $740,000 5708,000 Correct Grading Critona