A1 XV fx Function Library Budget Assumptions for Santa Fe Division 1 Budget Assumptions for Santa Fe Division A 2 Projected/Budgeted sales in unit for the fourth Quarter of 20Y3 3 The percentage of sales increase from one quarter to next quarter 4 Desired Ending Inventory is 20% of the budgeted sales units of the same quarter 5 plus 3,000 units 6 Budgeted Ending inventory units for the fourth quarter of 20Y3 7 8 9 10 Sales unit 11 Answer check (make sure to use roundup formula) 14 15 16 17 Budgeted Sales unit 18 Plus Desired Ending Inventory 19 Equal to Total need 20 Less Beginning inventory 21 Equal to Budgeted Purchase 22 Answer check 25 (Make sure to use roundup formula to compute desired ending inventory) 26 27 28 29 30 31 Manager Create from Selection Remove Arrows Defined Names Evaluate Formula Formula Auditing Window BB Calculate Shee Options Calculation B C D E F G H 0 Siddique, Salina: Do not hard code. Link the appropriate cell in Project Description worksheet to get the data. Siddique, Salina: Complete Cell B3 (as 15%), B4 (20%) and B5 (3,000) from the data provided in the Part B of the Directi box in Project Description worksheet. These three are the only cells you are supposed to hard code. Siddique, Salina: Refer to the Desired ending inventory policy provided in the Part B in the Directions box in Project Description worksheet to calculate this amount. Sales Budget for the 2014 1st quarter 2nd quarter 3rd quarter 4th quarter Total sale units Purchase Budget for the 2014 1st quarter 2nd quarter 3rd quarter 4th quarter 20Y4 budgeted units Product: Complan Revenue per unit Total Variable Costs of Goods Sold Total Fixed Costs of Goods Sold B D ASSUMPTIONS Denver Santa Fe $30 $900,000 $30 $2,520,000 $1,650,000 $0 $150,000 $200,000 $320,000 $300,000 2 Total Variable Selling and Administration Expenses Total Fixed Selling and Administration Expenses Quarterly volume of units sold in the fourth quarter 20Y3 200,000 200,000 Projected Contribution Margin based Income Statement For Fourth quarter 20Y2 Sales Total Variable costs Contribution margin Denver $ Santa Fe $ An $6,000,000 $6,000,000 Co $1,050,000 $2,720,000 Co $4,950,000 $3,280,000 Co $1,970,000 $300,000 Cor $2,980,000 $2,980,000 Cor Total Fixed costs Net income 3 Calculate the followings: Variable Cost per unit Contribution Margin per unit Break Even units (use Roundup formula) Break Even sales (Break even units x Selling price per unit) Margin of Safety percentage Operating Leverage Denver Santa Fe $5.25 $24.75 79,596.00 $2,387,880 60.20% 1.66 Project Description Part A Data File CVP Part B Data File Budget Grading Criteria + $13.60 Cor $16.40 Corr 18,293.00 Corr $548,790 Corr 90.85% Corr 1.10 Corr Accessibility Investicate