Question

Williams Company sells women’s hats for $12 each. Actual and budgeted sales in units for nine months are as follows:

January (actual) ………..          25,000 June (budget) ………….   50,000 February (actual) ………          26,000 July (budget) …………..   30,000 March (actual) …………          40,000 August (budget) ……….   28,000 April (budget) …………          65,000 September (budget) …… 25,000 May (budget) ………….        100,000

The company should have sufficient inventory on hand at the end of each month to supply 40% of the hats sold in the following month.

     Suppliers are paid $4.50 each for a hat. One half of a month’s purchases are paid for in the month of purchase; the other half is paid for in the following month. All sales are on credit, with no discount, and payable within 15 days. The company has found, however, that only 20% of a month’s sales are collected in the month of sale. An additional 70% is collected in the following month, and the remaining 10% is collected in the second month following sale.

      Monthly operating expenses for the company are given below: Expenses are paid in the month incurred.

Variable:     Sales commission……………..   4% of sales Fixed:     Advertising……………………      $190,000     Rent……………………………         22,000     Salaries………………………..        106,000     Utilities………………………..            9,000     Insurance………………………           5,000     Depreciation…………………..          15,000

     Additional information:

a. In April the company will pay $53,750 for restructuring costs.

b. Insurance is paid on an annual basis, in November of each year.

      c. The company plans to purchase investments for $161,250 cash in May.

             d. The company will purchase for cash $40,000 in new equipment during June.     

      e. The company declares dividends of $15,000 each quarter, payable in the first month of the following quarter.

     f. The company plan to collect $50,000 from common stock issued in May.

A listing of the company’s ledger accounts as of March 31, is given below:

Assets Cash……………………………………………        $74,000 Accounts Receivable ($31,200 February Sales;   $384,000 March sales) ………………………..       415,200 Inventory ………………………………………        104,000 Prepaid Insurance ……………………………..           15,800 Property and equipment (net) …………………         886,000 Total assets…………………………………….    $1,495,000                          Liabilities and Stockholders’ Equity Accounts payable………………………………       $112,500 Dividends payable……………………………..            15,000 Common Stock………………………………….        800,000 Retained earnings………………………………         567,500 Total liabilities and stockholders’ equity………    $1,495,000

   

The company maintains a minimum cash balance of $50,000. All borrowing is done at the beginning of a month; any repayments are made at the end of a month.

    The company has an agreement with a bank that allows the company to borrow in increments of $1,000 at the beginning of each month. The interest rate on these loans is 1.5% per month and for simplicity we will assume that interest is not compounded. At the end of the quarter, the company would pay the bank all of the accumulated interest on the loan and as much of the loan as possible (in increments of $1,000), while still retaining at least $50,000 in cash.

Required: A cash budget. Show the budget by month and for the quarter. Determine any borrowings that would be needed to maintain the minimum cash balance of $50,000.

Answer 1

Williams Company Sales Budget April May June Quarter Units sold 215000 65000 100000 50000 Selling price 12. 12 12 12 Total Sales 780000 1200000 600000 2580000 Sales Collections April May June Quarter February 31200 31200 March 48000 336000 384000 April 78000 780000 156000 546000 840000 1080000 May 240000 June 120000 120000 Total 834000 1038000 523200 2395200 Purchase Budget April May June Quarter Units sold 65000 100000 50000 215000 Ending Inventory 20000 12000 40000 12000 105000 120000 62000 227000 Opening Inventory -26000 -40000 -20000 -26000 Purchase units 79000 42000 201000 80000 Cost per hat 4.5 4.5 4.5 4.5 Total Purchases 355500 360000 189000 904500 Purchase Payments April May June Quarter March 112500 112500 April 177750 177750 355500 May 180000 180000 360000 94500 94500 June Total 357750 274500 922500 290250

Cash Budget For the Quarter ending June 30 April May June Quarter Opening Balance 74000 50000 50000 74000 Sales Collections 2395200 523200 834000 1038000 Common Stock 50000 50000 Cash Available 597200 934000 1088000 2519200 Purchase Payments 290250 274500 922500 357750 Sales commission 103200 31200 48000 24000 Advertising 190000 190000 190000 570000 Rent 2000 2000 22000 66000 Salaries 106000 106000 106000 318000 Utilities 9000 9000 9000 27000 Restructuring costs. 53750 53750 Investments 161250 161250 New Equipment Dividends 40000 40000 15000 15000 Total Payments 894000 665500 717200 2276700 Excess Cash Available -120000 40000 422500 242500 Financing Borrowings 170000 10000 180000 -180000 Repayments -180000 -7950 Interest -7950 Totals -187950 -7950 170000 10000 Cash Balance 50000 234550 234550 50000 170000 x 1.5% x 3 = 7650300 7950 10000 x1.5% x 2 300

А C D Е 1 Williams Company 2 3 Sales Budget May 4 April June Quarter 5 Units sold 100000 =SUM(B5:D5) 65000 50000 6 Selling price 12 12 12 12 7 Total Sales -B5*в6 C5*C6 -D5*D6 -E5*E6 9 Sales Collections April - 26000*12*O.1 May 10 June Quarter 11 February -SUM(B11:D11) -40000 12 0.1 87*0.7 12 March 40000*12*0.7 -SUM(B12:D12) 13 April -SUM(B13:D13) B7 0.2 -B7*0.1 14 May C7*0.2 ЕC7*0.7 =SUM(B14:D14) 15 June D7*0.2 -SUM(B15:D15) 16 Total SUM(B11:B15) -SUM(C11:C15) -SUM(D11:D15) SUM(B16:D16) 17 18 Purchase Budget April May 19 June Quarter -B5 20 Units sold -C5 -D5 -E5 21 Ending Inventory C20*0.4 -D20*0.4 -30000*0.4 -D21 -SUM(E20:E21) -SUM(B20:B21) =SUM(C20:C21) =SUM(D20:D21) 22 23 Opening Inventory -B20 0.4 -B21 -C21 - B23 24 Purchase units =SUM(B22:B23) =SUM(C22:C23) =SUM(D22:D23) -SUM(E22:E23) 25 Cost per hat 4.5 4.5 4.5 4.5 26 Total Purchases -B24*B25 C24*C25 -D24*D25 -E24*E25 27 28 Purchase Payments 29 April May June Quarter 30 March -SUM(B30:D30) 112500 31 April 32 May B26/2 B26/2 SUM(B31:D31) -SUM(B32:D32) =SUM(B33:D33) SUM(B34:D34) C26/2 C26/2 33 June D26/2 -SUM(B30:B33) 34 Total -SUM(C30:С33) -SUM(D30:D33) 35

А C E 36 Cash Budget 37 For the Quarter ending June 30 Аpril 38 May June Quarter 39 Opening Balance 74000 3В61 -С61 -B39 40 Sales Collections -B16 C16 -D16 -E16 -SUM(B41:D41) -SUM(E39:E41) 41 Common Stock 50000 42 Cash Available -SUM(B39:B41) -SUM(C39:C41) -SUM(D39:D41) 43 44 Purchase Payments ЕВ34 ЕС34 -D34 3Е34 -SUM(B45:D45 45 Sales commission -B7*0.04 C7*0.04 D7*0.04 46 Advertising -SUM(B46:D46) 190000 -B46 -C46 -B47 =SUM(B47:D47) 47 Rent 22000 -C47 48 Salaries SUM(B48:D48) 106000 -B48 -C48 49 Utilities -B49 =SUM(B49:D49) 9000 C49 -SUM(B50:D50) 50 Restructuring costs. 53750 -SUM(B51:D51) 51 Investments 161250 52 New Equipment =SUM(B52:D52) 40000 53 Dividends -SUM(B53:D53) 15000 54 Total Payments -SUM(C44:C53) -SUM(D44:D53) -D42 - D54 =SUM(B44:B53) -SUM(E44:E53) 55 Excess Cash Available -E42-E54 B42-B54 -C42-C54 56 Financing -SUM(B57:D57 -SUM(B58:D58 57 Borrowings =50000-B55 -50000-C55 - -B57-C57 58 Repayments |-7950 59 Interest -SUM(B59:D59) =SUM(B57:B59 60 Totals -SUM(C57:C59) =SUM(D57:D59) -SUM(E57:E59) 61 Cash Balance B55+B60 -С55+C60 -D55+D60 -E55+ E60 62 63 170000 x 1.5% x 3 7650+300 7950 64 10000 x1.5% x 2 300 65