Please Help! Creating a operating budget section
TASK #1 OPERATING BUDGET, YEAR 5 Stair-Lifts Elevators Economy Premium Lite Comm Tota SALES BUDGET, YEAR 5 Sales Mix (from Challenge 4) 7296 7% 296 1996 1006 Budgeted Sales Volume (from C5, 12 mo goal; total is WA Units) Budgeted Sales Price Budgeted Sales Revenue, in S 132 2,238 1,575 S 307 3,275 $ 2,238 4,275 683.89 $275,000 $ cs-c7x C6(copy across to columns D through F). G9 = sum(C9F9) COST OF GOODS SOLD, YEAR 5 Budgeted Cost of Goods Sold G13 C13 G14 sum D14:F14) C15 C13+014 (copy across to columns D through F). G15 sum(C15:F15) (CK: $23,755,586) C16 C15/C7 (copy across to columns D through F) Elevators: $70,000 FC/month +VC (Sales$ x.45) Stair Lifts: $21,500 FC/month +VC (Sales$ x.45) 840,000 $59,000 $ 25,000$ 174,000 Total Cost of Goods Sold Budgeted Unit Cost By Product-Line PRODUCTION BUDGET, YEAR 5 Budgeted Sales Volume Add: Desired Units in Ending FG Inventory Total Units Needed Less: Units in Beginning FG Inventory Total Units To Be Produced Total Unit Budgeted Cost Budgeted Cost of Goods Manufactured C19 C7 (copy across to columns D through F) Ro w 20 is given (formula) C21 C19+C20 (copy across to columns D through F) Row 22 is given C23 C21C22 (copy across to columns D through F) C24 C16 (copy across to columns D through F) C25 C23 x C24 (copy across to columns D through F) G25 sum C25:F25) Ck: $24,644,976 (58) DIRECT MATERIALS BUDGET, YEAR 5 Budgeted Cost of Goods Manufactured % of Total Cost Related to DM-40% E and 51/55/70% SL Add: Desired Ending Raw Materials Inventory Total Raw Material Needed Less: Raw Mbterials in Beginning Balance Inventory Budgeted Purchases of Raw Materials C2B . C25 (cor, across to calam ns D thro-th Fl G28surm[C2B:F281 C29-C28 x 0.40, percentages "re given in 829; CK: G29"S11,50s.os C30-(C29y36S)20 days (copy across so columns D through F) CK: G30- 630,450 C31-C29 C30 copy across to coluD through F G is sotal ca2 dune C33-C31 C32 netl; copy acrass to colamns D thraugh F:G is toal Ck: 633 $11,879,458 (164,024) $ 17,363) $ 5,13 4 $ 46,773 : 256.7001 or you ise from balance shit trnes sai ming dart thi-ng. DIRECT LABOR BUDGET, YEAR 5 Budgeted Cost of Goods anufactured % of Total Cost Related to DL-50% E nd 38/22/21% SL Labor Cost Per Unit Labor Cost Per Hour: C36-C25 copy cross to columns D shrough F G is total C37 . C36x 0S0; percentages are even n B 37, as-S10.639,I34 C3a-C37/C23 [copy across to co umns D through F) C40 C3800 hoars; eopy ormat far D41, E42, F43;CK F43-$28.02 Elevators (400 hours each) Economy Stoir Lifts (10 hours ach) Premium Stair Lifts (12 hours each) Lie Commeial 15 hors each) MANUFACTURING OVERHEAD BUDGET, YEAR 5 Budgeted Cost of Goods Manufactured % of Total Cost Related to MOH : 10% E and 11/2313% SL C46- C25(copy acrois to colamns D throagh Fl:& is toal C47-C460.10 pereentages are given in B47 Ck: 647-$2,499,533 COST OF GOODS MANUFACTURED BUDGET, YEAR' 5 Direct laterials Used Direct Labor Manufturing Overhead CS0-C29(copy acrois to colamns D throgh Fl: G is toal CS1-C37 (copy acrois to colamns D throagh Fl: G is toaal CS2-C47 (copy acrois to colamns D throagh Fl: G is toaal CS3 sumCS0 C52) copy across to colamns D through F:Gis toal Ck: G53-$24,644,976 Total Cost of Goods Manufactured GS6-(0000 x12+7362 xG7 Use information given in column B for each row. d63- sum(G56:062) Ck: G63-$10,519,097 Sales Commissions (3681x) Solries&Woges Expense ($128,232 per month 801x Depreciation Expense(8,40 per month) Insurance Expene 5,00 per month 544x) Rent Expense 12,000 per month) Utilities Expense (2,000 per month 74x) Total SGE Expeases Kirifbte Fired s8,522,63;3 (8,522,633
