Question

PROBLEM N3 Objective The objective of Problem 3 is to reinforce your understanding of the preparation of budgets ked to prepare some Operating and Financial Budgets. Lynn has become concerned about the cash needs for the business as it grow. 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 of January, February and March and the quarter in total. Lynn would like to begin the process with a production bude 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 te produce a cash budget for cach month and the quarter. The following sales-related information is provided: Budgeted 2020 Sales: Water bottles February March 7,000 8.000 April 10,000 January 9,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 40% of the following month's sales in units Ending inventory on December 31, 2017 followed this pattern. Ending inventory of aluminum should be 40% of the next month's production needs in ounces. On average. cach 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 matter. Considering the April production schedule, the desired ending inventory of aluminum in March is 8,000 oz. 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 15 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 S5.400 $4,100 $4,100 March $5,500 S4,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. 8) 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, 518,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. 1) 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. Depreciation on the plant and equipment will be $38,000 per month. Depreciation on administrative equipment is $1,000 per month.

Please prepare a Combined Cash Budget and Budgeted Income Statement using the information above - thank you so much! :) Will thumbs up!

Answer 1

Answer with working is given below

Sales Budget Sales Units Budgeted Selling price Total Budgeted Sales Cash Sales Sales on Credit January 9,000 $15.00 $135,000 $27,000 $108,000 February 7,000 $15.00 $105,000 $21,000 $84,000 March 8,000 $15.00 $120,000 $24,000 $96,000 Quarter 24,000 $15.00 $360,000 $72,000 $288,000 Total Cash receipts from Customers January February March Quarter Collection Current Month Cash Sales Collection of Receivables Total collection- AR $27,000 $140,000 $167,000 $21,000 $108,000 $129,000 $24,000 $84,000 $108,000 $72,000 $332,000 $404,000 1 Production Budget Budgeted Sales Add: Closing inventory - (20% of next month sales) Total Requirement Less: Opening Inventory Production Units Closing stock of month will be opening stock of next month | January 9,000 1,400 10,400 1,800 8,600 February 7,000 1,600 8,600 1,400 7,200 March 8,000 2,000 10,000 1,600 8 ,400 Quarter 24,000 2,000 26,000 1,800 24,200 2 Raw Material purchase budget Consumed for production- 2 ounce per output Add: Closing inventory - (40% of next month prod requirement) Total Needs Less: Opening Inventory Purchase Budget (Units) Purchase price per unit Purchase budget in value January 17,200 5,760 22,960 6,880 16,080 $1.50 $24,120 February 14,400 6,7201 21,120 5,760 15,360 $1.50 $23,040 March 16,800 8,000 24,800 6,720 18,080 $1.50 $27,120 Quarter 48,400 8,000 56,400 6,880 49,520 $1.50 $74,280 4 Expected Cash disbursements for Materials Purchases January February March Quarter $7,236 $16,884 $6,912 Cash payments for purchase January Purchase February Purchase March Purchase December Purchase Total purchase payment $16,128 $8,136 $24,120 $23,040 $8,136 $20,100 $75,396 $20,1001 $27,336 $23,796 $24,264 Direct Labor Cost Budget Plant Wages Plant Salaries Direct Labor Cost Budget January $5,800 $4,100 $9,900 February $5,400 $4,100 $9,500 March $5,500 $4,100 $9,600 Quarter $16,700 $12,300 $29,000 Cash payment for Direct Labor February March January $5,000 $5,940 December month January month February month March month Cash payment for Direct Labor $3,960 $5,700 Quarter $5,000 $9,900 $9,500 $5,760 $30,160 $3,800 $5,760 $9,560 $10,940 $9,660 Wonderful Water Bottles Combined Cash Budget For the Quarter Ended March 31, 2020 January $9,500 February $60,824 March $65,368 Quarter $9,500 $167,000 $176,500 $129,000 $189,824 $108,000 $173,368 $404,000 $413,500 $27,336 $10,940 $21,000 $1,800 $5,400 $4,000 $37,000 Beginning cash balance Add: Cash receipts Cash Receipts from Customers Total cash available Cash payments for Raw Materials Direct labor Utilities Indirect Materials Sales Commission Administrative salaries Advertisement Expenses ERP System installation Monthly Fee for ERP system Purchase of Equipment Tax payment Total cash payments Preliminary cash balance Financing Borrowing Repayment interest Total Financing Ending cash balance $23,796 $9,660 $18,000 $1,800 $4,200 $4,000 $37,000 $24,000 $2,000 $24,264 $9,560 $20,000 $1,800 $4,800 $4,000 $37,000 $75,396 $30,160 $59,000 $5,400 $14,400 $12,000 $111,000 $24,000 $4,000 $140,000 $8,200 $483,556 $70,056 $2,000 $140,000 $8,200 $115,676 $60,824 $124,456 $65,368 $243,424 -$70,056 $80,000 $80,000 $0 $60,824 $0 $65,368 $80,000 $9,944 $80,000 $9,944 $360,000 $184,080 $175,920 Wonderful Water Bottles Budgeted Income Statement For The Quarter Ended March 31st, 2020 Sales Less: Cost of Goods Sold Gross Margin Operating expenses Sales commission Administrative salary Advertisement Depreciation on ERP system Monthly fee on ERP System Depreciation on administrative equipment Total Operating expenses Income Before Taxes Less: Income Tax at 25% Net Income $14,400 $12,000 $111,000 $800 $4,000 $3,000 $145,200 $30,720 $7,680 $23,040 8 Balance Sheet Values as on 03/31/2020 Accounts receivable closing balance Opening Balance Add : Sales Less: Collection Closing Balance as on 3/31/2020 $140,000 $360,000 $404,000 $96,000 Accounts payable closing balance Opening Balance Add: Purchases Less : Payment Closing Balance as on 3/31/2020 $20,100 $74,280 $75,396 $18,984 Cash Closing balance as on 03/31/2020 $9,944