Question

Use EXCEL to prepare your solution. Complete the requirements using the class problem as a guide. Be sure to prepare tables and schedules in good form being careful to label all your work. Corning Incorporated sells its product for $24 per unit. Its actual and projected sales follow: Units Dollars January (actual) 18,500 $444,000 February (actual) 23,000 552,000 March (budgeted) 19,800 475,200 April (budgeted) 18,950 454,800 May (budgeted) 22,000 528,000 Here is added information about Corning’s operations: All sales are on credit. Recent experience show that 35% of sales are collected in the month of the sale, 45% in the month following the sale, 17% in the second month after the sale, and 3% prove to be uncollectible. The product’s purchase price is $15 per unit. All payments are payable within 21 days. Thus 30% of purchases in any given month are paid for in that month, with the remaining 70% paid for in the following month. The company has a policy to maintain an ending inventory of 20% of the next month’s projected sales plus a safety stock of 100 units. The January 31 and February 28 actual inventory levels are consistent with this policy. Selling and administrative expenses for the year are $2,456,000 and are paid evenly throughout the year in cash. The company’s minimum cash balance at month-end is $50,000. This minimum is maintained, if necessary, by borrowing cash from the bank. If the balance exceeds $50,000, the company repays as much of the loan balance as it can without going below the minimum. This loan carries an annual interest rate of 6% (0.5% per month). At February 28, the loan balance is $14,000, and the company’s cash balance is $50,000. Required: Part1. Prepare a table that shows cash collections from accounts receivable for March and April Part2. Prepare a table that shows the computation of ending inventory in units for January, February, March, and April. Part3. Prepare the merchandise purchases budget for February, March, and April. Part4. Prepare a table showing the computation of cash payments on merchandise purchases for March and April. Part5. Prepare a cash budget for March and April, including any loan activity and interest expense. Compute the loan balance at the end of each month. Part 1 Budgeted Collections of Accounts Receivable Part 1 March April Month of Sale (35%) 166,320 Month after Sale (45%) 248,400 2nd Month after Sale (17%) 75,480 Total 490,200 Part 2 Budgeted Ending Inventories (Units) January February March April Next month sales 23,000 19,800 Desired inventory % 20% 20% Budgeted inventory 4,600 3,960 Plus safety stock 100 100 Ending inventory 4,700 4,060 Part 3 Merchandise Purchases Budget February March April Budgeted ending inventory 4,060 Add budgeted sales 23,000 Required units of inventory 27,060 Deduct beginning inventory 4,700 Budgeted purchases (units) 22360 Budgeted cost per unit 15 Budgeted cost of purchases $335,400 Part 4 Cash Payments on Merchandise Purchases March April Month of Purchase(30%) Mo After Purchase (70%) 234,780 Total Part 5 Cash Budget for March and April March April Beginning cash balance $50,000 Plus cash receipts from customers 490,200 Total available cash 540,200 Cash disbursements Payments on purchases Selling and administrative expenses 204,667 Interest expense 70 Total disbursements Preliminary cash balance Additional loan from the bank Repayment of loan to the bank Ending cash balance Ending loan balance.

Answer 1