Question

preparing a master budget practice.

AaBbCcDdEe AaBbCcDdee Normal No Spacing Arial 15 AA Aa A ab A 9. . . 3 A 4+ BI Uabe XX? A. AA EES : . 5. Schedules to include: a. Sales Budget, with a Schedule of Expected Cash Collections b. Production Budget C. Ending Finished Goods Inventory Budget d. Direct Materials Budget, with a Schedule of Cash Disbursement e. Direct Labor Budget f. Manufacturing Overhead Budget g. Selling & Administrative Expense Budget h. Cash Budget i. Budgeted Income Statement

Answer 1

c. A C E F н K M (a) Sales Budget (e) Direct Labor Budget 1 2 Qtr Qtr 1 2 4 Year 2 3 4 Year 4 Budgeted Sales (in cases) 5 Unit selling Price Production (in cases) DL hours (0.50 hours) DL Cost ($15 per hour) 19400 26000 30000 28000 12000 96000 33000 29400 23200 105000 23.00 $ $ 23.00 $ 23.00 $ 23.00 $ 14700 11600 23.00 16500 9700 52500 $5,98,000.00 $6,90,000.00 $6,44,000.00 $2,76,000.00 $22,08,000.00 $2,47,500.00 $2,20,500.00 $1,74,000.00 $1,45,500.00 $7,87,500.00 6 Sales Value 7 Cash Collections: $4,66,440.00 $5,38,200.00 $5,02,320.00 $2,15,280.00 $17,22,240.00 8 Cash Collections in the month of sale $1,31,560.00 $1,51,800.00 $1,41,680.00 $ 4,25,040.00 $4,66,440.00 $6,69,760.00 $6,54,120.00 $3,56,960.00 $21,47,280.00 9 Cash collected in the quarter following the sale (f) Manufacturing Overhead Budget 10 Total Cash Collection Qtr 11 2. 3 4 Year 9700 16500 11600 12 DL Hours 14700 52500 $ 82,500.00 $ 73,500.00 $58,000.000 $ 48,500.00 $2,62,500.00 $ 24,000.00 $ 24,000.00 24,000.000 $ 24,000.00 96,000.00 $ 17,000.00 $ 17,000.00 $ 17,000.00 $ 17,000.00 $ 68,000.00 $1,23,500.00 $1,14,500.00 $ 99,000.00 $ 89,500.00 $4,26,500.00 Variable MOH ($5 per DLH) (b) Production Budget 13 Fixed MOH ($24,000 per Qtr) Non Cash MOH - Depreciation ($17,000 per qtr) 14 Qtr 4. 15 2 Year 16 Budgeted Sales (in cases) 17 Beginning FG Inventory 18 Desired ending FG Inventory 19 Production (in cases) 26000 Total Manufacturing Overhead 30000 28000 12000 96000 2000 3600 2000 9000 8400 9000 8400 3600 11000 11000 29400 19400 33000 23200 105000 20 O Ending FG Inventory Budget 21 22 Qtr 23 2 4 Year 24 Budgeted Sales (in cases) 25 Desired FG inventory 30 % of next Qtr's Sale 26000 30000 28000 12000 96000 8400 32000 9000 3600 11000 26 (d) Direct Material Budget 27 28 Qtr 29 1 4 Year 30 Beginning RM Inventory (in units) 31 Quantity of RM needed 32 Desired ending RM Inventory 44100 21000 34800 29100 21000 291000 1575000 495000 441000 348000 44100 34800 29100 23000 23000 33 RM Purchase 518100 431700 342300 284900 1577000 34 DM cost (0.25$ 35 Cash Payment: $1,29,525.00 $1,07,925.00 $ 85,575.00 71,225.00 $3,94,250.00 $28,495.50 $ 23,743.50 $ 18,826.50 $ 15,669.50 $ 36 Cash payment in the Qtr of purchase 86,735.00 $1,01,029.50 $84,181.50 $ 66,748.50 $ 2,51,959.50 $ 28,495.50 $1,24,773.00 $1,03,008.00 82,418.00 3,38,694.50 37 Payment in the qtr following the purchase 38 Cash disbursement

A В D E F G Н К M (a) Sales Budget (e) Direct Labor Budget 1 2 Qtr Qtr 3 2 3 4 Year 1 2 4 Year 4 Budgeted Sales (in cases) 5 Unit selling Price -SUM (B4:E4 ) -SUM(14:L4) =SUM(15:L5) -SUM (16: L6) 26000 Production (in cases) DL hours (0.50 hours) DL Cost ($15 per hour) =D19 -E19 30000 28000 12000 B19 -C19 E14*0.5 =J4*0.5 - K4* 0.5 EL4*0,5 23 23 23 23 23 E 15*15 6 Sales Value -B4*B5 D4*D5 =E4*E5 =F4*F5 J5*15 K5*15 L5*15 C4*C5 7 Cash Collections: =E6*78% =SUM(B8:E8) 8 Cash Collections in the month of sale B6*78% C6*78% D6*78% C6 22% -B6*22% =SUM(C9:E9) =SUM(F8:F9) 9 Cash collected in the quarter following the sale D6*22% (f) Manufacturing Overhead Budget SUM(B8:B9) 10 Total Cash Collection =SUM(C8:C9) SUM(D8:D9) =SUM(E8:E9) Qtr 11 1 4 Year =SUM(112:L12) -SUM(113:L13) =SUM(14:L14) =SUM(115:L15) -SUM(113:115)=SUM(J13:J15) SUM(K13: K15) -SUM(L13: L15) -SUM(M13: M15) 12 DL Hours 15 J5 K5 L5 Variable MOH ($5 per DLH) Fixed MOH ($24,000 per Qtr) (b) Production Budget E112*5 J12*5 K12*5 L12*5 13 14 Qtr 24000 24000 24000 24000 Non Cash MOH- Depreciation ($17,000 per qtr) 17000 17000 17000 15 1 4 Year 17000 16 Budgeted Sales (in cases) =SUM(B16:E16) Total Manufacturing Overhead B4 C4 -D4 -E4 17 Beginning FG Inventory 18 Desired ending FG Inventory 19 Production (in cases) 2000 B18 -C18 D18 B17 D16*30% C16*30% -E16*30% 11000 11000 =F16+F18 - F17 B16+B18 - B17 C16+C18- C17 =E16+E18 - E17 D16+D18-D17 20 O Ending FG Inventory Budget 21 22 Qtr 23 1 3 4 Year - SUM (B24: E24) 24 Budgeted Sales (in cases) 25 Desired FG inventory 30 % of next Qtr's Sale ЕB4 C4 D4 -E4 C24*30% =D24*30% E24*30% =SUM(B25:E25) 11000 26 (d) Direct Material Budget 27 28 Qtr 29 1 2 4 Year 30 Beginning RM Inventory (in units) 31 Quantity of RM needed 32 Desired ending RM Inventory %3DВ32 3С32 -D32 ЕВ30 21000 -SUM(B31: E31) -B19*15 -C19*15 =D19*15 -E19*15 C31 10 % -B31+ B32-B30 =E31*10% -D31*10% ЕЕ32 23000 33 RM Purchase -С31+с32-СЗ0 D31+D32-D30 -E31+E32-E30 =F31+F32-F30 34 DM cost (0.25$ =SUM(B34:E34) B33*0.25 -С33*0.25 =D33 0.25 E33*0.25 35 Cash Payment: B34 22% 36 Cash payment in the Qtr of purchase C34*22% D34*22% -E34*22% =SUM(B36:E36) -С34*78% 37 Payment in the qtr following the purchase =SUM(B37:E37) -SUM(B36:B37)=SUM(C36:C37) SUM(D36:D37) -SUM(E36:E37)=SUM(B38:E38) B34*78% D34*78% 38 Cash disbursement