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 firms 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?
Collections Worksheet
NOV
DEC
JAN
FEB
MAR
APR
850,000 875,000 350,000 300,000 250,000 400,000
Sales
MAY
500,000
JUN
JUL
AUG
525,000 600,000 625,000
SEP
OCT
NOV
DEC
JAN
700,000 725,000 800,000 900,000 400,000
Collections
340000 350000 140000 120000 100000
Cash
249900 257250 102900
30-days
200000
210000 240000
250000
280000 290000 320000 360000 160000
88200
73500
117600
147000 154350
176400
183750 205800 213150 235200 264600
249900 257250 102900
88200
73500
117600 147000
154350
176400 183750 205800 213150 235200
340000 599900 647150 480150 291100
321700
391100
474600 541350
580750
640150 679550 738950 808350 659800
60-days
Total Inflows
160000
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
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
720000
Salaries
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,000
Interest
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,000
Other 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
Purchases
700000
140,000
Taxes
140,000
6,000
140,000
30,000
New Computer
Total Payments
140,000
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
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.
DEC
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 departments 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.
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)
$ (174,400)
$ 21,750
$ (118,850)
$ 40,550
$ 39,950
$ (110,650)
Borrowing
(67,900)
-
2,350
-
$ 64,950
Interest on short-term borrowing
Short-term borrowing repaid
Ending Cash Balance
Minimum Cash Balance
Cumulative Surplus or deficit
$ 306,150
-50,000
$ 256,150
$ 487,300
-50,000
$ 437,300
$ 379,400
-50,000
$ 329,400
$ 322,100
-50,000
$ 272,100
$ 254,200
-50,000
$ 204,200
$ 79,800
-50,000
$ 29,800
$ 82,150
-50,000
$ 32,150
$ 103,900
$ 50,000
-50,000
$ 53,900
$ 70,700
(166)
$ (40,117)
$ (24,833)
$ 50,000
$ 89,950
-50,000
$
(433)
-50,000
$
$ 50,000
-50,000
-50,000
$ 39,950
Cumulative Surplus or deficit
Beginning Short term borrowing
Change in short term debt
$ 64,950
$ 64,950
$ 24,833
$ (40,117)
$ (24,833)
$ 24,833
$ 64,950
Ending Short-term debt
