Scenario: Styles Wing Bicycle Company manufactures and sells 2 sizes of bicycles, 21.5 inch and 22.5 inch. Sales Their sales team has committed to the following projected monthly unit sales for the next year. Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov 21.5 Bike 368 472 633 616 443 345 322 317 317 317 328 22.5 Bike 320 410 550 535 385 300 280 275 275 275 285 Dec 420 365 The selling price for the 21.5 inch bike is $455. For the 22.5 inch bike it is $490. The desired ending inventory for each month is 15% of the following month's sales for the 21.5 inch bike and 12% for the 22.5 inch bike. The inventory at January 1 of the budget year is expected to be 55 units of the 21.5 inch bike and 38 units of the 22.5 inch bike. The projected sales for Jan of the year following the budget are 385 of the 21.5 inch bike and 350 for the 22.5 inch bike. Materials The total materials for the 21.5 inch bike and 22.5 inch bike are $164.45 and $186.30 respectively (Bill of Materials in workbook for information purposes). The production department would like to have an ending raw materials inventory of 20% of the next month's production requirements. Desired ending production inventory for December is $15,700 for 21.5 inch bike and $15,300 for 22.5 inch bike. Beginning raw materials inventory value for Jan of the budget year are $16,370 for the 21.5 inch bike and $18,597 for the 22.5 inch bike. Direct Labor Direct labor is 3.9 hours at $15.25 per hour for the 21.5 inch bike and 4.4 hours at $15.25 per hour for the 22.5 inch bike (Process sheet in workbook for information purposes). Manufacturing Overhead Manufacturing Overhead is based on direct labor hours at a rate of $22.25 per DLH for each of the bikes. Budgeted monthly fixed overhead is as follows: Salaries 5,000 Utilities 2,200 Depreciation 7,200 Maintenance 2,800 Insurance 2,000 Property Taxes 1,200 Sales, General & Administrative Sales, General and Administrative (SG&A) expenses are based on unit sales for variable expenses at $11.20 per unit. Fixed SG&A expenses are $18,000 per month. Part of these SG&A expenses are non cash and amount to $7,800 per month Cash Cash is expected to be collected at 55% of the prior month sales and 45% of the sales from 2 months prior. Nov/Dec sales for the prior year are $422,000 and $475,000 respectively, Monthly cash disbursements for materials are 50% of current month's purchases and 50% of last month's purchases. Trade accounts payable should be based on materials purchases and the balance at Dec 31 of the year prior to the budget year is estimated to be $42,000 Assume all other expenses are paid in the month incurred. The beginning cash balance for Jan of the budget year is estimated to be $ 39,979 The company has estimated balance sheet amounts as of Jan 1 of $225,000 for Land, $1,280,000 for Property, Plant & Equipment, $2,125,000 for Common Stock and Retained Earnings of $510,915. O D E F G H I J K L M N I have provided the following check figures to keep you on track - if you get to the end of that particular report and you have an incorrect December balance then other reports are going to be wrong because they work! together. Do not just put a check figure down as your answer. If the individual budget numbers do not support your answer then it will be marked incorrect. UNIT SALES Total Sales at the end of December = $4,313,540 PRODUCTION BUDGet Total 21.5 bike units to produce = 4901 Total 22.5 bike units to produce = 4259 FIXED OVERHEAD Total fixed overhead for the year = $244,800 "CASH" SG & A EXPENSES Total "cash"SG & A Expenses for the year = $224,914 DIRECT MATERIALS Total materials to purchase for the year = $1,595454 MANUFACTURING OVERHEAD BUDGET Total cash disbutrsements for Mfg Overhead = $1,000,640 Ending cash balance as of Dec = $1,148,044 CASH BUDGET Somewhere along the line this will come in handy: remember Depreciation is an expense however it never involves cash - companies do not issue a check for Depreciation Expense. 5 Un. + 215 pire Process 22.5 Bike Process Number Cost 7.85 UnitOfMeasure Each Each Each Each Each Each Each Each Each Each Each Seat Post Saddle Seat Tube Down Tube Top Tube Rear Brake 95 Sprocket Cluster & Freewheel 3,65 QtyRead Item 1 Seat Post 1 Saddle 1 Seat Tube 1 Down Tube 1 Top Tube 2 Grips 1 Gear Shifters 1 Handlebar Stem 2 Headset 1 Head Tube 2 Front Brake 1 Forks 1 Quick Release 1 Front Wheel 1 Bottom Bracket 2 Pedal 2 Cranks 1 Chainings 1 Chain 1 Rear Mechanical 1 Rear Wheel 1 Sprocket Cluster 2 Rear Brake Total Cost Each Extended Cost 2.5 7.85 3.3 3.3 4.55 4.55 4.35 4.35 1.85 5.8 5.8 2.1 2.1 4.75 3.65 4.45 8.9 9.75 9.75 5.15 12.85 12.85 1.95 1.95 4.85 9.7 3.95 8.45 8.45 7.05 7.05 7.25 7.25 19.55 19.55 9.75 9.75 4.45 $ 164.45 Grips Gear Shifters Handlebar Stom Headte Head Tube -Front Brake Forks - Quick Release Hub Spoke Tyre 5.15 Rear Wheet Each Each Each Each Each Each Each Each Each Each Each Rear Mech Chain Front Mech Chainrings- Cranks Pedal Bottom Bracket Front Wheel 225 Bike Process Un... Check Fiqures Getting Started 22.5 Bike BOM21.5 Bike Process 21.5 Bike BOM Font Alignment Number yles Regd item 1 Seat Post Extended Cost Seat Post Saddle Seas Tube I-Down Tube Top Tube Rear Brake UnitOfMe asure Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Each Sprocket Cluster & Freewheel 1 Saddle 1 Seat Tube 1 Down Tube 1 Top Tube 2 Grips 1 Gear Shifters 1 Handlebar Stem 2 Headset 1 Head Tube 2 Front Brake 1 Forks 1 Quick Release 1 Front Wheel 1 Bottom Bracket 2 Pedal 2 Cranks 1 Chainings 1 Chain 1 Rear Mechanical 1 Rear Wheel 1 Sprocket Cluster 2 Rear Brake Total Cost 3,65 Cost 2.5 2.5 7.85 7.85 4.15 4.15 5.95 5.95 5.75 5.75 1.85 5.8 2.1 6.75 13.5 3.65 4.45 8.9 9.75 9.75 5.15 5.15 18.85 18.85 1.95 1.95 4.85 9.7 3.95 7.9 8.45 8.45 10.05 10.05 7.25 7.25 21.75 21.75 12.75 4.45 8.9 S 186.30 Grips Gear Shifters Handlebar Stom Headset Head Tube -Front Brake Forks Quick Release Rear Wheel Rear Mech Chain Front Mech Chainrings Cranks Pedal Bottom Bracket Front Wheel Each Each Each Each 12.75 22.5 Process 21.5 Bike Process 21.5 Bike BOM 22.5 Bike BOM Getting Started Alignment Cel Style G H E Direct Labor Rate (Hr) I F Variabel OH Rate (DLHR) Time Per Pc (Hrs) K L M N o. 0.5 Sequence WorkArea Description 10 Cut Cut Tubing 20 Deburr Deburr Cut Tubing 30 Close Flaten tube ends 40 Weld Weld Tubing for Frame 50 Assembly Assembly 60 Inspect Inspection 70 Package Package Bike Total Time per Unit Seat Post Saddle 0.25 0.75 0.75 1.25 0.15 0.25 3.9 $ Sex Tube Down Tube Top Tube Rear Brake Sprocket Cluster & Freewheel 15.25 $ 22.25 Grips Gear Shifter Handlebar se Heads Head Tube -Front Brak -Forks Quick Rele Hub Spoke Rear Wheer Rear Mech Tyre Chain Front Mech Chainnings Cranks Pedal Bomom Bracket Front Wheel Un BOM 21.5 Wke Process 225 Bike Process K L M N Sequence WorkArea Description 10 Cut Cut Tubing 20 Deburr Deburr Cut Tubing 30 Close Flaten tube ends 40 Weld Weld Tubing for Frame 50 Assembly Assembly 60 Inspect Inspection 70 Package Package Bike Total Time per Unit EF IGH I Direct Variabel Time Per Labor OH Rate Pc (Hrs) Rate (Hr) (DLHR) 0.65 0.25 0.85 0.85 1.4 Rear Brake 0.15 0.25 Sprocket Cluster 4.4 S 15.25 $ 22.25 & Freewheel Seat Post Saddle Seat Tube Down Tube Top Tube Heade Grips Gear Shi Handhebe Head Tub - Front & Forks -Quick R Rear Wheel HD Spoke Rim Tyre Rear Mech Chain Front Mech Channings- Cranks Pedal Bottom Bracket Front Wheel nike Prest Home Insert Draw Page Layout Formulas Master Budget Project Template Rev 10.2019 - Excel View Help Books Search Data Review Calba - BILA A Condition ing Aliment Apr May un Aug Sep Oct Nov De Torel UNIT SALES 21.5 Bike 225 Bike Total Units Totals 882 $ 324,240 $475,660 S 0 0 0 0 0 0 0 0 0 0 - $ $ $ 730 $ 1570 79,900 $ $ $ $ $ - $ Information tells us that desired ending iventory is 15% of the following months sales for the 21.5 and 12 for the 22.5 ACTUAL DESIRED ENDING INVENTORY 21.5 BIKE ACTUAL DESIRED ENDING 13 INVENTORY 22.5 BIKE number. For example dan is 70.8 rounded to a whole number is 71 A company cannot have a partial bike therefore this section rounds any fraction of a bike to a w ROUNDED DESIRED ENDING INVENTORY 21.5 BIKE ROUNDED DESIRED ENDING INVENTORY 225 BE AutoSave an File Home e X Nolan ohon u Ceneral- Insert Draw Page Layout Formulas Calibri - 11 - - B TU A A Data 2 E Master Budget Project Template Rev 10.2019. Excel Review View Help QuickBooks Search = = General - B $ % 9 e Share Conditional Formatting Paste Formatas Table Clipboard Font F34 - X f Mar Apr May Jun Jan 368 320 Jul Aug Feb 472 410 Sep Oct Nov DW Total 840 730 2 PRODUCTION BUDGET 3 21.5 Bike Budgeted Sales 4 22.5 Bike Budgeted Sales $ 21.5 Bike Desired Ending Inventory 6 22.5 Bike Desired Ending Inventory 7 21.5 Bike Total Inventory Required 8 22.5 Bike Total Inventory Required 9 Less: 21.5 Bike Beginning inventory 10 Less: 22.5 Bike Beginning inventory 11 21.5 Bike Units to Produce 12 22 5 Bike Units to produce 1006 370 55 346 71 50 496 426 0 0 0 0 0 332 0 0 0 0 0 0 0 0 0 0 0 0 0 758 Notice the Sales and Desired Ending Inventory comes from the Unit Sales Tab The Beginning inventory comes from the instructions & information Sheet NOTE: the ending balene in one month becomes the beginning balance in the next month Ending inwentary Budget Direct Ma + 225 Bike Process Unit Sales Pr duction Budget Fived OH SG&A Paste $ % ) BU A A A . Font - -07-BR San Feb Mar Apr May kun sul Aug Sep Oct Nov Dec 2 MANUFACTURING O/h BUDGET 3 Direct Labor Hours Required 4 Variable Me Overhead Rate 5 Variable Mlg Overhead Costs 6 Fixed Mig Overhead Costs 7 Total Mig Overhead Costs 8 Less: Non-Cash Costs 9 Cash Disbursements For MIE OH S $ $ $ $ S S S ONMENT - Feb Mar Apr May sun rol Aug Sep Oct No . 39.979 451,150 491.129 $ - $ $ 2 Cash Budget 3 Beginning Balance 4 Add: Cash Collections 5 Total Cash Available 6 Less: Cash Disbursements 7 Materials 8 Direct Labor 9 Manufacturing Overhead 10 SG&A 11 Total Disbursements 12 Ending Cash Balance 103,110 45.116 79,024 17,906 245,156 $ 245,973 S $ 15 The Cash Disbursements comes from the individual budget such as Cash Dlabursements Materials toh, Direct Labor Budget Tubete Be sure to finish up the total column which represents the ending cash bolace for 12/11/ ONS Cash Budget Cost of Good Collections Cash Disbursements materiali