Problem 8-25 Cash Budget with Supporting Schedules: Changing Assumptions [LO8-2, LO8-4, LO8-8) 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 $ 440,000 S 970,000 308,000 679,000 132,890 291,000 June $420,000 294,000 126,800 July $ 320,000 224,000 96,888 Sales Cost of goods sold Gross margin Selling and administrative expenses: Selling expense Admibistrative expense Total selling and administrative expenses Net operating income 85,000 41,000 126,880 6,000 92,889 54,400 146,480 S 144,680 53,600 33,200 86,280 39,800 32,000 30,000 62,000 34,880 $ 5 $ "Includes $14,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 $150,000, and March's sales totaled $210,000 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 $150,000, and March's sales totaled $210,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 $89,600. 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 $61,600 f. Dividends of $22,000 will be declared and paid in April g. Land costing $30,000 will be purchased for cash in May. h. The cash balance at March 31 is $44,000, the company must maintain a cash balance of at least $40,000 at the end of each month 1. 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: a. Soles 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 the main section b. 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 $61,600 and accounts payable for inventory purchases at March 31 remains $89,600. Using the president's new assumptions in (a) above, prepare a schedule of expected and for the quarter in total. Quarter s 366,000 Cash sales Sales on account February March 24,000 Schedule of Expected Cash Collections April May June 88,000 $ 194,000 $ 84,000 EL 24,000 117 600 33,600 35,200 $ 246,400 3 70,400 77,600 543,200 33,600 264.800 S 551,600 S 731,200 April May 151,200 352,000 620,800 33,600 $ 1.547.600 June Total cash collections s Merchandise Purchases Budget April May June Budgeted cost of goods sold Add: Desired ending merchandise inventory Total needs Less: Beginning merchandise inventory 308,000 679,000 135,800 58,800 443,800 737,800 61,600 135,800 $ 382.200602,000 294,000 44,800 338,800 58,800 280,000 Required inventory purchases Last UISLUSENCILS TUI Trianuise pur clases TVE A Hay, diu JU GUI U qudith L Schedule of Expected Cash Disbursements for Merchandise Purchases April May June Beginning accounts payable $ 89,600 April purchases 191,100 191,100 "May purchases 301,000 X 301,000 X June purchases 140,000 Total cash disbursements $ 280,700 S 492,100 $ 441,000 Quarter S 89.600 382,200 602,000 140,000 $1,213,800 June Quarter 40,100 40,200 $ 120,300 40,100 40,200 120,300 Cash Budget For the Quarter Ended June 30 April Beginning cash balance $ 40,000 s Add collections from customers Total cash available 40,000 Less cash disbursements: Purchases for inventory Selling expenses Administrative expenses Land purchases Dividends paid Total cash disbursements Excess (deficiency) of cash available over disbursements 40,000 Financing Borrowings Repayment 40.100 40,200 120,300 Interest Total financing Ending cash balance 40.0005 40,100 40200 S 120,300