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 D) Lynn intends to purchase equipment costing $140.000 for a new line of water bottles in March. The equipment will be installed in March but use of the eauinment will not been until April, so no will be taken on the new equipment in the first quarter of the year. m) Wonderful Water bottles has a line of credit with a local bank and a policy that the ending cash balance each month must be at least 59,000. The cash balance on December 31, 2019 is expected to be $9,500. The company can borrow in increments of $1,000 at the beginning of the month. The firm pays accumula Interest of 1% per month at the end of each quarter and will use any excess cash above me balance to pay down on the line of credit balance at the end of the quarter n) The tax rate for Wonderful Water Bottles is 25% of income before tax (operating income less interest expense). An estimated tax payment of $8.200 will be made in January Required: Prepare your answers to the following requirements in Packet 2 (Hint: See the formats in proon 65B and adjust as needed.) 1. Prepare a production budget to calculate production in units for the months of January, February, and March and for the quarter in total. 2. Prepare a direct materials budget for ounces of aluminum for January, February, and March and for the quarter in total. Prepare a cash collections budget for the months of January, February, and March and for the quarter in total. 4. Prepare a cash payments for direct materials (aluminum) budget for the months of January February, and March and for the quarter in total. 5. Prepare cash payments for direct labor budget for the months of January, February, and March and for the quarter in total 6. Using the information from parts 3. through 5. and the additional information presented above, prepare a combined cash budget for the months of January, February, and March and for the quarter in total. Any cash flows that are not summarized in requirements 3. through 5. should be shown as separate line items in the budget. 7. Assume that the average cost of goods sold per unit during the first quarter of 2020, which includes direct materials, direct labor, and manufacturing overhead, is $7.67 per unit. Prepare a budgeted income statement for the first quarter of 2020. You do not need to do a separate schedule of cost of goods manufactured and sold. Round to the nearest dollar Calculate the following amounts that would appear on the 3/31/20 budgeted balance sheet: 8. Accounts Receivable Accounts Payable for direct materials (aluminum) Cash PROBLEM #3 Wonderful Water Bottles Production Budget - Water Bottles For the Quarter Ended March 31, 2020 January February March Quarter Wonderful Water Bottles Direct Materials Purchases Budget - Aluminum For the Quarter Ended March 31, 2020 January February March Quarter Wonderful Water Bottles Cash Collections Budget For the Quarter Ended March 31, 2020 January February March Quarter Wonderful Water Bottle Cash Payments for Direct Materials - Aluminum For the Quarter Ended March 31, 2020 January February March Quarter Wonderful Water Bottles Cash Payments for Labor Budget For the Quarter Ended March 31, 2020 January February March Quarter Wonderful Water Bottles Budgeted Income Statement For the Quarter Ended March 31, 2020 8. Balance sheet values 3/31/20 Accounts Receivable Accounts Payable for direct materials (aluminum) $_ Cash Wonderful Water Bottles Combined Cash Budget For the Quarter Ended March 31, 2020 January February March Quarter