Question

LAST LONG. PROBLEM #3 Objective The objective of Problem 3 is to reinforce your understanding of the preparation of budgets. Operating and Financial Budgets. Lynn has become concerned about the cash needs for the business as it grows. You have been asked to prepare budgets related to the production and sales of water bottles for the first quarter of 2020: the three months on January, February and March and the quarter in total, Lynn would like to begin the process with a production budget and a related purchases budgets for aluminum. Lynn purchased enough lids at a special price at the end of November that he will have no need to purchase additional lids through the first quarter of 2020. Finally, you are to produce a cash budget for each month and the quarter. The following sales related information is provided January 9,000 Budgeted 2020 Sales: Water bottles February March 7,000 8,000 April 10,000 Additional information for your budget preparation is as follows: a) Because of increased competition the average sales price per water bottle in 2020 is expected to drop to $15.00 per bottle. b) Wonderful Water bottles has a policy that each month's ending inventory of finished goods (water bottles) should be 20% of the following month's sales in units. Ending inventory on December 31, 2019 followed this pattern. c) Ending inventory of aluminum should be 40% of the next month's production needs in ounces. On average each water bottle will take 2 ounces of aluminum. The expected price for aluminum is $1.50 per ounce. Ending inventory on December 31, 2019 followed this pattern. Considering the April production schedule, the desired ending inventory of aluminum in March is 8,000 oz. d) Cash sales are 20% of total sales and are collected in the month of sale. The remaining sales are on account (credit) and are collected in the month following the month of sale. Accounts receivable on 12/31/19 is expected to be $140,000. e) Aluminum purchases are paid 30% in the month of purchase and 70% in the month following the month of purchase. Accounts Payable for purchases on 12/31/19 is expected to be $20,100. f) Budgeted labor costs for the plant for the three months of the first quarter are as follows: Budgeted 2020 Labor Costs January February $5,800 $5,400 $4,100 $4,100 Plant wages Plant salaries March $5,500 $4,100 These labor costs are paid 60% in in the month in which the work is performed and 40% in the following month. Amounts owed for salary and wages payable were expected to be $5,000 on 12/31/19. This amount will be paid in January. g) In addition to the wages and salaries above, Lynn will pay his sales representative solely on a commission of 4% of sales, paid in the month incurred. Administrative salaries of $4,000 are also paid in cash each month. h) Utilities for the plant will be $21,000, $18,000, and $20,000 for January, February and March respectively. These amounts are paid in the month incurred. Other costs paid in the month incurred include indirect materials purchases of $1,800 per month. i) Lynn plans to pay cash of $37,000 each month in the first quarter of 2020 for a series of advertisements in outdoor magazines. j) Lynn also plans to implement an ERP system for production scheduling in February of 2020. Installation of the system will cost $24,000, paid in February. The system will be depreciated over 5 years with no salvage value. A monthly fee for use of the system will be $2,000 to be paid each month beginning in February. k) Depreciation on the plant and equipment will be $38,000 per month. Depreciation on administrative equipment is $1,000 per month.

Answer 1

1 2 A B C D Requirement 3: Cash collection Budget Month 3 January February March Quarter 4 Budgeted Sales (in bottles) 9000 7000 8000 24000 5 Unit selling Price 15.00 $ 15.00 $ 15.00 $ 15.00 6 Sales Value $1,35,000.00 $1,05,000.00 $1,20,000.00 $ 3,60,000.00 7 Cash Collections: 8 Cash Collections in the month of sale $ 27,000.00 $ 21,000.00 $ 24,000.00 $ 72,000.00 9 Cash collected in the Month following the sale $1,40,000.00 $ 1,08,000.00 $ 84,000.00 $ 3,32,000.00 10 Total Cash Collection $1,67,000.00 $1,29,000.00 $1,08,000.00 $ 4,04,000.00 Requirement 5: Direct Labor Budget Month January February Total Budgeted Labor Costs (Plant Wages+ Plant Salaries) $ 9,900.00 $ 9,500.00 $ Cash Payment in the month of work performed $ 5,940.00 $ 5,700.00 $ Cash Payment in following the month of work performed $ 5,000.00 $ 3,960.00 $ Total Cash Payment for direct Labor Costs $ 10,940.00 $ 9,660.00 $ March Quarter 9,600.00 $ 29,000.00 5,760.00 $ 17,400.00 3,800.00 $ 12,760.00 9,560.00 $ 30,160.00 13 14 15 16 Budgeted Sales (in bottles) 17 Beginning FG Inventory 18 Desired ending FG Inventory 19 Production (in bottles) Requirement 1: Production Budget Month January February 9000 7000 1800 1400 1400 1600 8600 7200 March 8000 1600 2000 8400 Quarter 24000 1800 2000 24200 20 23 Quarter 24200 48400 19360 20480 49520 21 Requirement 2: Direct Material Budget 22 Month January February 24 Production (in bottles) 8600 7200 8400 25 Ounce for each bottle 26 Month's Production need 17200 14400 16800 27 Opening RM inventory 6880 5760 6720 28 Desired Ending RM Inventory 5760 6720 8000 29 Purchase of Aluminium (in Ounces) 16080 15360 18080 30 31 Requirement 4: Cash Payments for Direct Material Budget 32 Month January February March 34 Purchase of Aluminium (in Ounces) 16080 15360 18080 35 Price per Ounce of Aluminium $ 1.50 $ 1.50 $ 1.50 $ 36 Direct Material Cost $ 24,120.00 $ 23,040.00 $ 27,120.00 $ 37 Cash payment in the Month of purchase $ 7,236.00 $ 6,912.00 $ 8,136.00 $ 38 Payment in the Month following the purchase $ 20,100.00 $ 16,884.00 $ 16,128.00 $ 39 Cash disbursement $ 27,336.00 $ 23,796.00 $ 24,264.00 $ 33 Quarter 49520 1.50 74,280.00 22,284.00 53,112.00 75,396.00

! 2 March 8000 Quarter =SUM(B4:04) Requirement 5: Direct Labor Budget Month January February March Quarter Total Budgeted Labor Costs (Plant Wages+ Plant Salaries) =5800+4100 =5400+4100 =5500+4100 =SUM(H4:14) Cash Payment in the month of work performed =H4*60% =14*60% =J4*60% =SUM(H5:35) Cash Payment in following the month of work performed 5000 =H4*40% =14*40% =SUM(H6:J6) Total Cash Payment for direct Labor Costs =SUM(H5:H6) =SUM(15:16) =SUM(J5:36) =SUM(H7:17) 15 Requirement 3: Cash collection Budget Month 3 January February 4 Budgeted Sales (in bottles) 9000 7000 5 Unit selling Price 15 6 Sales Value =B4*B5 =C4*C5 7 Cash Collections: 8 Cash Collections in the month of sale =B6*20% =C6*20% 9 Cash collected in the Month following the sale 140000 =B6*80% 10 Total Cash Collection =SUM(B8:39) SUM(C8:09) 11 15 =D4*D5 =E4*E5 =D6*20% =C6*80% SUM(D8:09) =SUM(B8:08) =SUM(B9:09) -SUM(E8:E9) 12 13 14 15 16 Budgeted Sales (in bottles) 17 Beginning FG Inventory 18 Desired ending FG Inventory 19 Production (in bottles) Requirement 1: Production Budget Month January February =B4 =C4 =B16*20% =B18 =C16*20% =D16*20% =B16+B18-B17 =C16+C18-C17 March =D4 =C18 =10000*20% =D16+D18-D17 Quarter =SUM(B16:016) =B17 =D18 =E16+E18-E17 20 21 22 Requirement 2: Direct Material Budget Month January February =B19 =C19 March =D19 Quarter =SUM(B24:D24) 24 Production (in bottles) 25 Ounce for each bottle 26 Month's Production need 27 Opening RM inventory 28 Desired Ending RM Inventory 29 Purchase of Aluminium (in Ounces) =B24*B25 =B26*40% =C26*40% =B26+B28-B27 =C24*C25 =B28 =D26*40% =C26+C28-C27 =D24*D25 =C28 8000 =D26+D28-D27 =E24*E25 =SUM(B27:D27) =SUM(B28:D28) =SUM(B29:D29) 30 31 Requirement 4: Cash Payments for Direct Material Budget 32 Month 33 January February March Quarter 34 Purchase of Aluminium (in Ounces) =B29 =C29 =D29 =SUM(B34:D34) 35 Price per Ounce of Aluminium 1.5 1.5 1.5 1.5 36 Direct Material Cost =B34*B35 =C34*C35 =D34*D35 =E34*E35 37 Cash payment in the Month of purchase =B36*30% =C36*30% =D36*30% =SUM(B37:D37) 38 Payment in the Month following the purchase 20100 =B36*70% =C36*70% =SUM(B38:D38) 39 Cash disbursement =SUM(B37:B38) ESUM(C37:C38) =SUM(D37:D38) =SUM(B39:D39) An