Question

Garden Sales, Inc., sells garden supplies. Management is planning its cash needs for the second quarter. The company usually has to borrow money during this quarter to support peak sales of lawn care equipment, which occur during May. The following information has been assembled to assist in preparing a cash budget for the quarter: a. Budgeted monthly absorption costing income statements for April-July are: April May June July $ 550,000 $ 750,000 $ 450,000 $ 350,000 385,000 525,000 315,000 245,000 165,000 225,000 135, 000 105,000 Sales Cost of goods sold Gross margin Selling and administrative expenses: Selling expense Administrative expense Total selling and administrative expenses Net operating income 75,000 95,000 56,000 42,500 56,800 35,000 117,500 151,800 91,000 $ 47,500 $ 73,200 $ 44,000 35,000 33,000 68,000 $ 37,000 "Includes $17,000 of depreciation each month.

Answer 1

Solution Requirement 1: Schedule of expected cash disbursements for merchandise purchases Garden Sales, Inc. Schedule of Expected Cash Disbursements for Merchandise Purchases April May June Quarter Accounts Payable April purchases May purchases June purchases Total cash disbursements $101,500 $101,500 $206,500 $206,500 $413,000 $241,500 $483,000 $150,500 $150,500 $392,000 $1,148,000 $241,500 $0 $308,000 $448,000 Requirement 2: Cash Budget Garden Sales, Inc. Cash Budget For the Quarter Ended June 30 April $47,000 $311,200 May June Quarter Beginning cash balance Add: Collections from customers $47,000 $40,700 $40.900 $1,541,200 $580,000 $650,000 Total cash available Less: Cash disbursements: $690,900 $620,700 $358,200 $1,588,200 $448,000 $95,000 $39,800 $308,000 $75,000 $1,148,000 $226,000 Purchases for inventory $392,000 Selling expenses Administrative expenses Land purchases $56,000 $18,000 $25,500 $83,300 $33,000 $25,000 $1,515,300 $33,000 $0 $25,000 $433,500 ($75,300) Dividends paid $o $0 $615,800 $4,900 $466,000 $224,900 Total cash disbursements Excess (deficiency) of cash available over disbursements Financing: $72,900 $152,000 Borrowings $116,000 $36,000 $0 ($152,000) Repayments $0 ($152,000) $0 $0 $36,000 $40,900 ($4,200) ($4,200) Interest ($4,200) Total financing Ending cash balance $116,000 $40,700 ($156,200) $68,700 $68,700 WN-1.: Schedule of expected cash collections Garden Sales, Inc. Schedule of Expected Cash collections April May $150,000 June Quarter $350,000 Cash sales Sales on account: February sales $110,000 $90,000 $13,200 $13,200 $0 $18,000 $0 March sales $144,000 $0 $162,000 $44,000 $352,000 $60,000 April Sales $44,000 $440,000 May Sales $0 $480,000 $540,000 $36,000 $650,000 $36,000 $0 $311,200 $0 June Sales $580,000 Total cash collections $1,541,200 WN-2. Merchandise purchase budget Garden Sales, Inc. Merchandise Purchase Budget April $385,000 $105,000 $490,000 Quarter May $525,000 June Cost of Goods Sold $315,000 1,225,000 Add: Desired ending inventory Total needs Less: Likely beginning inventory Required purchases $63,000 $49,000 49,000 $1,274,000 77,000 $588,000 $364,000 $63,000 $301,000 $77,000 $105,000 $413,000 $483,000 $1,197,000

To see the formulas, please see the below images:

A D E F 1 Solution Requirement 1:Schedule of expected cash disbursements for merchandise purchases 4 Garden Sales, Inc. 6 Schedule of Expected Cash Disbursements for Merchandise Purchases 7 April May June Quarter 101500 |-С61/2 =SUM(C8:E8) -SUM(C9:E9) -SUM(C10:E10) -SUM(C11:E11) -SUM(F8:F11) 0 Accounts Payable 0 -C61/2 -D61/2 0 April purchases May purchases June purchases Total cash disbursements D61/2 -E61/2 -SUM(E8:E11) 10 0 11 0 =SUM(C8:C11) SUM(D8:D11) 12 13 14 Requirement 2:Cash Budget 15 16 Garden Sales, Inc. 17 Cash Budget 18 For the Quarter Ended June 30 19 April May June Quarter D36 -E50 |-SUM(E20:E21) Beginning cash balance Add: Collections from customers Total cash available Less: Cash disbursements: Purchases for inventory Selling expenses Administrative expenses Land purchases Dividends paid -С20 -SUM(C21:E21 |-SUM(F20:F21) 20 47000 -C36 -D50 -SUMID20:D21) 21 ЕC50 SUM(C20:C21) 22 23 |=SUM(C24:E24) |-SUMIC25:E25) |=SUM(C26:E26) -SUM(C27: E27 ) -SUM(C28:E28) |=SUM(F24:F28) =F22-F29 24 C12 -E12 56000 35000-17000 =D12 75000 42500-17000 25 95000 26 - 56800-17000 33000 27 0 28 0 25000 |=SUM(C24:C28) -C22-C29 SUM(E24:E28) -E22-E29 29 |=SUMD24:D28) Total cash disbursements Excess (deficiency) of cash available over disbursements Financing: 30 -D22 - D29 31 - 76000+40000 SUM(C32:E32) SUM(C33:E33) SUM(C34:E34) |-SUM(F32:F34) 32 36000 0 -с32-D32 |-(C32 1 % 3 )-( D32 1% 2) |-SUM(E32:E34) Borrowings Repayments 33 0 0 34 Interest 0 0 Total financing 35 -SUM(C32: C34) -SUM (D32 : D34 ) D30+D35 36 Ending cash balance =F30+ F35 %3Сзонс35 -Е30+ЕЗ5

A C E F 37 WN-1.:Schedule of expected cash collections 38 39 40 Garden Sales, Inc. 41 Schedule of Expected Cash collections 42 May - 750000 20 % April June Quarter -SUM(C43:E43 ) -550000 20 % 43 Cash sales 450000 20 % 44 Sales on account: - 165000 80 % 10 % | 0 =225000 80 % 80 % | - 225000*80 % * 10 % | 0 45 -SUM(C45:E45) -SUM(C46:E46) -SUM(C47:E47) -SUM(C48:E48) =SUM(C49:E49) -SUM(F43:F49) February sales 0 46 March sales =550000 80 % 10 % - 550000*80 % * 80 % | - 550000 80 % * 10 % =750000 80 % 10 %| 750000 *80 % *80 % 450000 80 % 10 % -sUM(E43:E49) April Sales May Sales 47 48 49 June Sales =SUM(C43:C49) -SUM(D43:D49) 50 Total cash collections 51 52 WN-2.: Merchandise purchase budget 53 54 Garden Sales, Inc. 55 Merchandise Purchase Budget 56 May 525000 -E57 20 % =D57+D58 D57 20 % D59-D60 April June Quarter -SUM(C57:E57) -E58 =F57+F58 C60 -F59-F60 57 315000 Cost of Goods Sold 385000 =D57 20 % -C57+C58 -C57 20 % Add: Desired ending inventory Total needs Less: Likely beginning inventory 61 58 -245000 20 % -E57+E58 59 60 -E57 20 % Required purchases C59-C60 -E59-E60 62