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
Sales $460,000 $990,000 $440,000 $340,000
Cost of goods sold 322,000 693,000 308,000 238,000
Gross margin 138,000 297,000 132,000 102,000
Selling and administrative expenses *
Selling expense 89,000 94,000 55,000 34,000
Administrative expense 42,000 56,000 34,400 32,000
Total selling and administrative expenses 131,000 150,000 89,400 66,000
Net operating income $7,000 $147,000 $42,600 36,000
* Includes $16,000 of depreciation each month.
b. Sales are 20% for cash and 80% on account.
c. Sales on account are collected over a three-month period with 10% collected in the month of sale: 70% collected in the first month following the month of sale, and the remaining 20% collected in the second month following the month of sale. February's sales totaled $160,000, and March's sales totaled $220,000
d. Inventory purchases are paid for within 15 days. Therefore, 50% of a month's inventory purchases are paid for in the month of purchase. The remaining 50% is paid in the following month. Accounts payable at March 31 for inventory purchases during March total $93,800.
e. Each month's ending inventory must equal 20% of the cost of the merchandise to be sold in the following month. The merchandise
inventory at March 31 is $64,400.
f. Dividends of $24,000 will be declared and paid in April.
g. Land costing $32,000 will be purchased for cash in May.
h. The cash balance at March 31 is $46,000; the company must maintain a cash balance of at least $40,000 at the end of each month.
i. The company has an agreement with a local bank that allows the company to borrow in increments of $1,000 at the beginning of each month, up to a total loan balance of $200,000. The interest rate on these loans is 1% per month and for simplicity, we will assume that interest is not compounded. The company would, as far as it is able, repay the loan plus accumulated interest at the end of the quarter.
The company's president is interested in knowing how reducing inventory levels and collecting accounts receivable sooner will impact the cash budget. He revises the cash collection and ending inventory assumptions as follows:
1. Sales continue to be 20% for cash and 80% on credit. However, credit sales from April, May, and June are collected over a three-month period with 25% collected in the month of sale, 65% collected in the month following sale, and 10% in the second month following sale. Credit sales from February and March are collected during the second quarter using the collection percentages specified in Problem 8-24.
2. The company maintains its ending inventory levels for April, May, and June at 15% of the cost of merchandise to be sold in the following month. The merchandise inventory at March 31 remains $84,000 and accounts payable for inventory purchases at March 31 remains $126,000.
Required:
1. Using the president's new assumptions in (1) above, prepare a schedule of expected cash collections for April, May, and June and for the quarter in total.
2. Using the president's new assumptions in (2) above, prepare the following for merchandise inventory:
a. A merchandise purchases budget for April, May, and June.
b. A schedule of expected cash disbursements for merchandise purchases for April, May, and June and for the quarter in total.
3. Using the president's new assumptions prepare a cash budget for April, May, and June, and for the quarter in total.
4. Prepare a brief memorandum for the president explaining how his revised assumptions affect the cash budget.

Answer 1

Answer:

Garden Sales, Inc.

                                                       April            May          June          Total

1. Cash Collections:

Cash sales (20%)                         $92,000   $198,000   $88,000  $378,000

Credit sales:

10% month of sale                         36,800       79,200     35,200       151,200

70% month following sale           123,200    257,600   554,400     935,200

20% second month following      25,600      35,200      73,600      134,400

Total cash collections               $277,600 $570,000  $751,200 $1,598,800

2. Merchandise Inventory:

a.    Purchases Budget                   April            May          June      

Cost of goods sold                      322,000    693,000   308,000

Ending inventory (15%)                 103,950      46,200     35,700

Goods available for sale             425,950    739,200   343,700  

Beginning inventory                      84,000     103,950    46,200

Purchases                                    341,950    635,250   297,500

b. Cash payment for purchases:

50% month of purchase              170,975     317,625    148,750      637,350

50% month following purchase 126,000      170,975    317,625      614,600

Total payment for purchases  $296,975  $488,600 $466,375 $1,251,950

3. Cash Budget

                                                       April            May          June         Total

Beginning cash balance           $46,000   $40,225     $40,425     $46,000

Total cash collections                311,200    652,800     727,600 $1,691,600

Cash available                        $357,200 $693,025   $768,025 $1,737,600

Payment for purchases          $296,975  $488,600 $466,375 $1,251,950

Other payments:

Dividends                                   24,000                                              24,000

Land purchase                                              32,000                           32,000

Selling & administrative exp.   115,000       134,000      73,400      322,400

Total cash payments            $435,975   $654,600  $539,775 $1,630,350

Cash Balance                           (78,775)       38,425    228,250     228,250

Minimum Cash balance         (40,000)      (40,000)  

Cash required                       $118,775         $1,575       0                      0

Cash borrowed                    $119,000        $2,000   (123,400)     (123,400)

Ending balance                       40,225        40,425    104,850       104,850

4. To: The President

From: FC

Subject: Revised Estimates and the Cash Budget

Date: April 26, 2021

The revised estimates will ensure that the company has the ability to pay off its borrowings in April and May by the end of the second quarter.

It should be maintained.

Regards,

Explanation:

a) Data and Calculations:

Budgeted monthly absorption costing income statements for April-July are:

                                                       April            May          June          July

Sales                                           $460,000 $990,000 $440,000 $340,000

Cost of goods sold                      322,000    693,000   308,000   238,000

Gross margin                                138,000    297,000    132,000    102,000

Selling and administrative expenses *

Selling expense                             89,000      94,000     55,000     34,000

Administrative expense                42,000      56,000     34,400     32,000

Total selling and administrative

expenses                                     131,000     150,000     89,400     66,000

Net operating income                  $7,000   $147,000   $42,600   $36,000

                                                       April            May          June          July

Sales                                           $460,000 $990,000 $440,000 $340,000

Credit sales (80%)                        368,000    792,000   352,000   272,000

Cash collections

Cash sales (20%)                         $92,000   $198,000   $88,000  $68,000

Credit sales:

10% month of sale                         36,800       79,200     35,200     27,200

70% month following sale           123,200    257,600   554,400   246,400

20% second month following      25,600      35,200      73,600    158,400

Total cash collections               $277,600 $570,000  $751,200 $500,000

                                                       April            May          June          July

Cost of goods sold                      322,000    693,000   308,000   238,000

Ending inventory (20%)                138,600       61,600     47,600

Goods available for sale             460,600    754,600   355,600  

Beginning inventory                      64,400     138,600      61,600     47,600

Purchases                                   396,200     616,000   294,000

Cash payment for purchases:

50% month of purchase             198,100     308,000    147,000

50% month following purchase 93,800       198,100   308,000

Total payment for purchases $291,800   $506,100 $455,000

Other payments:

Dividends                                   24,000

Land purchase                                              32,000

Selling & administrative exp.   115,000       134,000      73,400

Total cash payments           $430,800     $672,100 $528,400

Principal debt to bank at the end of the quarter =    $121,000

+ Interests: 1% of $119,000 = $1,190

1% of $121,000                         1,210

Total interest owed              $2,400                               2,400

Total debt to the bank at the end of the quarter = $123,400

Revised Estimates:

Credit sales (80%)                        368,000    792,000   352,000   272,000

Cash collections

Cash sales (20%)                         $92,000   $198,000   $88,000  $378,000

Credit sales:

25% month of sale                        92,000     198,000      88,000    378,000

65% month following sale            114,400    239,200     514,800    868,400

10% second month following        12,800        17,600      36,800       67,200

Total cash collections                $311,200  $652,800  $727,600 $1,691,600

                                                        April            May          June          July

Cost of goods sold                      322,000    693,000   308,000   238,000

Ending inventory (15%)                 103,950      46,200     35,700

Goods available for sale             425,950    739,200   343,700  

Beginning inventory                      84,000     103,950    46,200

Purchases                                    341,950    635,250   297,500

Cash payment for purchases:

50% month of purchase              170,975     317,625    148,750

50% month following purchase 126,000      170,975    317,625

Total payment for purchases  $296,975  $488,600 $466,375

Other payments:

Dividends                                   24,000

Land purchase                                              32,000

Selling & administrative exp.   115,000       134,000      73,400

Total cash payments           $435,975    $654,600  $539,775