Question

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 some budgets related to the production and sales of water bottles for the first quarter of 2020: the three months of 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. 1) Budgeted labor costs for the plant for the three months of the first quarter are as follows: Plant wages Plant salaries Budgeted 2020 Labor Costs January February S5,800 $5,400 $4,100 $4,100 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 S5,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. nt 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 1) 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

Only first four requirements are solved.

Requirement 1 March 8000 Quarter 24000 Wonderful Water Bottles Production Budget-Water Bottles For the Quarter ended March 31, 2020 January February Budgeted unit sales 9000 7000 Add-Desired units of ending finished goods 1400 1600 inventory Total needs 10400 8600 Less- units of Beginning Finished goods inventory -1800 -1400 Required production in units 8600 7200 2000 2000 10000 -1600 8400 26000 -1800 24200 Requirement 2 March 8400 Quarter 24200 Wonderful Water Bottles Direct Materials Purchases Budget- Aluminum For the Quarter ended March 31, 2020 January February Budgeted unit to be produced 8600 7200 Budgeted ounces per unit Budgeted materials to be consumed 17200 14400 Add-Desired units of ending direct materials inventory 2880 3360 Total needs 20080 17760 Less- units of Beginning Direct Materials inventory -3440 -2880 Required direct materials to be purchased 16640 14880 16800 48400 8000 24800 -3360 21440 8000 56400 -3440 52960

Requirement 3 Wonderful Water Bottles Schedule of Expected Cash Collections For the Quarter ended March 31, 2020 January February 9000 7000 $ 15.00 $ 15.00 $ 1,35,000 $ 1,05,000 Budgeted unit sales Selling Price per unit Sales March 8000 15.00 1,20,000 Quarter 24000 15.00 3,60,000 $ $ $ $ $ $ 1,40,000 27,000 Beginning Accounts Receivable Jan month sales Feb month sales Mar month sales Total Cash Collections $ $ 1,08,000 21,000 $ $ 1,29,000 $ $ $ 84,000 $ 24,000 $ 1,08,000 $ 1,40,000 1,35,000 1,05,000 24,000 4,04,000 | $ 1,67,000 $ Requirement 4 Wonderful Water Bottles Schedule of Expected Cash Disbursements For the Quarter ended March 31, 2020 January February Required direct materials to be purchased 16640 14880 Cost of Direct Material per unit $ 1.50 $ 1.50 Required cost of Direct materials to be purchased $ 24,960 $ 22,320 March 21440 1.50 32,160 Quarter 52960 1.50 79,440 $ $ $ $ $ $ 20,100 7,488 Beginning Accounts Payable Jan month purchases Feb month purchases Mar month purchases Total Cash Disbursements $ $ 17,472 6,696 $ $ 24,168 $ $ $ 15,624 $ 9,648 $ 25,272 $ 20,100 24,960 22,320 9,648 77,028 $ 27,588 $