AutoSave OFF AEGU ThreeBudgetsNEW Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments A v 11 A = = = General Calibri (Body) B IU fx A A der E Wrap Text Merge & Center Paste $ % -00 Conditional Format Formatting as Table Cell Styles 3X Delete Format Sort & Filter Find & Select Ideas X Sensitivity | K8 x L M N O P Q R S T U V June B E F G H 2 2 Three Budgets 3 Johnson Company is planning for the first 6 months of 2020. Johnson's estimated sales (in units) each month: January=100,000 4 February=120,000; March=130,000; April=120,000; May=115,000; June=110,000. 5 5 The product sells for a price of $15.00 per unit. Create a sales budget, cash collections budget and a production budget. 6 6 7 SALES BUDGET January February March April May June Total Sales (in units) 10 10 Price per Unit 11 11 Sales Revenue $0 $0 $0 $0 $0 $0 $0 SUMCI1H11) 12 12 13 13 The company's cash collections typically happen in a pattern of 60% of the sales collected in the month of sale 14 14 and the remaining 40% collected in the month following the sale. Note: December's sales were 90,000 units priced 15 15 at $15. Create a cash collections budget. 16 16 17 17 CASH COLLECTIONS BUDGET 18 18 January February March April May Total 19 19 Sales Revenue 20 20 60% of Current Month's Sales =SUM|C20:20) 21 21 40% of Last Month's Sales* =SUMC21:21) 22 22 Total Collections 120-121 23 23 40% of $1,350,000 in January 24 24 25 25 The Production Budget, unlike the ones shown above, is constructed in units, NOT DOLLARS. The objective is to determine how many units 26 26 to produce. You are already familiar with the basic inventory formula: BEGINNING INVENTORY UNITS PRODUCED - ENDING INVENTORY UNITS SOLD 27 27 But, if we are trying to figure out the UNITS PRODUCED (in this case UNITS TO PRODUCE), we need to switch the formula around. Like this: 28 28 UNITS TO PRODUCE UNITS SOLD+ENDING INVENTORY - BEGINNING INVENTORY 29 29 Using the sales budget, we already know the UNITS SOLD each month. We must derive the UNITS TO PRODUCE 30 30 31 31 For the production budget, Johnson wishes to end each month with enough inventory to cover 25% of next month's sales. Let us 32 32 also assume that July's sales are likely to be 90,000 units. Assume the beginning inventory in January was 25,000 units. 33 33 The format of the production budget is as follows. 34 34 35 35 PRODUCTION BUDGET 36 36 January February March April May June July 37 37 Sales (from Sales Budget) 38 38 +Desired Ending Inventory (25% next mo. sales) 39 39 Subtotal 40 40 - Beginning Inventory* 41 41 Units to Produce 0 0 0 0 0 42 42 Beginning inventory in January is December's Ending Inventory 43 43 NewBudg Sheet1 + Enter - - - + 100%