Solution to Case 23

Cash Budgeting

Getting Our Act Together* *Note to instructors: Please note that taxes are $560,000 for the year (paid in Mar, Jun, Sep. and Dec.) Other expenses are $6,000 PER MONTH for the coming year 1. Even though sales have been increasing, why is Best Electronics in such a cash flow

crunch? Best Electronics’ cash inflows and outflows are not well balanced. Its sales are seasonal as well, with the highest sales occurring in the last quarter of the year. The firm’s payments for purchases are made in 30 days while the receipts (especially from wholesale orders) take between 30-60 days to come in, on average. The firm does not have a minimum cash balance policy and therefore in lean cash flow months its cash balance declines considerably.

2. What does the firm need to do as soon as possible?

The firm needs to prepare a detailed cash budget showing the monthly cash collections and disbursements and the resulting surpluses and shortfalls. This will help it to determine how much of a line of credit it needs to set up so as to avoid having to be faced with overdraft notices from its bank.

3. Prepare the collections worksheet. Which month has the greatest amount of cash inflows?

1

Collections Worksheet

NOV DEC JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC JAN

Sales 850,000 875,000 350,000 300,000 250,000 400,000 500,000 525,000 600,000 625,000 700,000 725,000 800,000 900,000 400,000

Collections

Cash 340000 350000 140000 120000 100000 160000 200000 210000 240000 250000 280000 290000 320000 360000 160000

30-days 249900 257250 102900 88200 73500 117600 147000 154350 176400 183750 205800 213150 235200 264600

60-days 249900 257250 102900 88200 73500 117600 147000 154350 176400 183750 205800 213150 235200

Total Inflows 340000 599900 647150 480150 291100 321700 391100 474600 541350 580750 640150 679550 738950 808350 659800

The greatest amount of cash inflows occurs in December ($808,350).

4. Prepare the disbursements worksheet. Which months seem to be hit by the highest amount of cash outflows? Why? Can this trend be changed?

Disbursements Worksheet

NOV DEC JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC

Purchases

700000 280000 240000 200000 320000 400000 420000 480000 500000 560000 580000 640000 720000 320000

Payments 700000 280000 240000 200000 320000 400000 420000 480000 500000 560000 580000 640000 720000Salaries 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000Interest 3,000 3,000 3,000 3,000 3,000 3,000 3,000 3,000 3,000 3,000 3,000 3,000 3,000Other expenses 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000 6,000Taxes 140,000 140,000 140,000 140,000New Computer 30,000 Total Payments 759000 339000 299000 399000 379000 459000 649000 539000 559000 759000 639000 699000 919000

June, September, October, November, and December are hit by relatively high cash outflows. This is because of higher payments for purchases resulting from higher forecasted sales during the last quarter of the year. Changing the ordering and payment schedules during the year can change this trend.

2

5. How should the depreciation expense be treated in the cash budget? Depreciation is not a cash outflow and should be ignored in the cash budget.

6. Which months seem to be particularly vulnerable to cash deficits? Which months have the greatest surpluses? March,

April, May, June, September, and December seem to be particularly vulnerable to cash deficits. January and February have the greatest surpluses.

7. If the cash balance outstanding is -$2,000, help Joe develop a cash budget for Best Electronics for the next twelve months. How can Mark use the cash budget to minimize cash shortages and plan for the future?

12 MONTH CASH BUDGET

JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC Total Inflows $ 647,150 $ 480,150 $ 291,100 $ 321,700 $ 391,100 $ 474,600 $ 541,350 $ 580,750 $ 640,150 $ 679,550 $ 738,950 $ 808,350

Total Payments $ 339,000 $ 299,000 $ 399,000 $ 379,000 $ 459,000 $ 649,000 $ 539,000 $ 559,000 $ 759,000 $ 639,000 $ 699,000 $ 919,000 Beginning Cash Balance $ (2,000) $ 306,150 $ 487,300 $ 379,400 $ 322,100 $ 254,200 $ 79,800 $ 82,150 $ 103,900 $ (14,950) $ 25,600 $ 65,550

Net Cash Flow $ 308,150 $ 181,150 $ (107,900) $ (57,300) $ (67,900) $ (174,400) $ 2,350 $ 21,750 $ (118,850) $ 40,550 $ 39,950 $ (110,650) Ending Cash Balance $ 306,150 $ 487,300 $ 379,400 $ 322,100 $ 254,200 $ 79,800 $ 82,150 $ 103,900 $ (14,950) $ 25,600 $ 65,550 $ (45,100)

The budget shows that September and December are going to particularly vulnerable months for the firm as far as cash shortages are concerned. The maximum shortfall seems to be around $45,000. Mark can use the cash budget to determine how much of the surplus cash should be invested and how much should be kept as a minimum reserve to prevent shortfalls.

3

8. Given that the monthly sales figures have been fluctuating so much what should Joe

do while preparing the cash budget? Can he take the sales figures provided by the finance department at face value? If so why? If not why? What other options does he have?

Joe should prepare various versions of the cash budget using alternative sales scenarios. For example, Best, Base, and Worst case scenarios can be analyzed by varying the sales figures. The finance department’s sales figures should not be taken at face value. They are probably too conservative. As stated earlier, alternative scenario analyses should be performed.

9. How can a minimum cash balance be built in? How much of a minimum cash balance

seems warranted? What can the company do with the excess cash that is generated in some months? By taking a look at the forecasted cash flows and providing enough of a reserve to cover the largest forecasted shortfall one can build in a minimum cash balance. The largest shortfall seems to be $45,100 (in December). So a minimum cash balance of about $50,000 seems to be warranted. If this cash balance is allocated at the start of the budget, it will help minimize the risk of future cash shortfalls. Excess cash can be invested in money market securities.

10. Rework the budget by using your suggested minimum cash balance and assume that short-term loans carry an interest rate of 8% per year.

Click here for spreadsheet calculations.

4

JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC Total Inflows $ 647,150 $ 480,150 $ 291,100 $ 321,700 $ 391,100 $ 474,600 $ 541,350 $ 580,750 $ 640,150 $ 679,550 $ 738,950 $ 808,350

Total Payments $ 339,000 $ 299,000 $ 399,000 $ 379,000 $ 459,000 $ 649,000 $ 539,000 $ 559,000 $ 759,000 $ 639,000 $ 699,000 $ 919,000 Beginning Cash Balance $ (2,000) $ 306,150 $ 487,300 $ 379,400 $ 322,100 $ 254,200 $ 79,800 $ 82,150 $ 103,900 $ 50,000 $ 50,000 $ 89,950

Net Cash Flow $ 308,150 $ 181,150 $ (107,900) $ (57,300) $ (67,900) $ (174,400) $ 2,350 $ 21,750 $ (118,850) $ 40,550 $ 39,950 $ (110,650) Borrowing $ - $ - $ - $ - $ - $ - $ 64,950 $ 70,700

Interest on short-term borrowing $ (433) $ (166) $ - Short-term borrowing repaid $ (40,117) $ (24,833)

Ending Cash Balance $ 306,150

$ 487,300 $ 379,400 $ 322,100 $ 254,200 $ 79,800 $ 82,150 $ 103,900 $ 50,000 $ 50,000 $ 89,950 $ 50,000 Minimum Cash Balance -50,000 -50,000 -50,000 -50,000 -50,000 -50,000 -50,000 -50,000 -50,000 -50,000 -50,000 -50,000

Cumulative Surplus or deficit $ 256,150 $ 437,300 $ 329,400 $ 272,100 $ 204,200 $ 29,800 $ 32,150 $ 53,900 $ - $ - $ 39,950 $ - Cumulative Surplus or deficit

Beginning Short term borrowing $ 64,950 $ 64,950 $ 24,833 $ - Change in short term debt $ - $ (40,117) $ (24,833) $ -

Ending Short-term debt $ 64,950 $ 24,833 $ - $ -

5