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
Collections Worksheet NOV
Sales
DEC
JAN
F EB
MAR
APR
MAY
850, 850,00 000 0 875, 875,00 000 0 350, 350,00 000 0 300, 300,00 000 0 250, 250,00 000 0 400, 400,00 000 0
500,0 500,000 00
JUN
JUL
AUG
SEP
525, 525,00 000 0 600, 600,00 000 0 625, 625,00 000 0
OCT
NOV
DEC
JAN
700, 700,00 000 0 725, 725,00 000 0 800, 800,00 000 0 900, 900,00 000 0 400, 400,00 000 0
Collections Cash 30-days
3 4 000 0 35 00 00 1 4 00 00 12 0 00 0 10 0 0 00
16 00 00
2 0 0 00 0
2 10 00 0 24 0 0 00
2 5 00 00
2 80 00 0 2 9 00 0 0 3 2 000 0 3 6 00 00 16 00 0 0
24 99 00 2 5 72 50 10 2 900
8 8 200
7 35 00
1 1 760 0
147000 154350
1 7 640 0
1 83 75 0 2 0 5 80 0 2 1 315 0 2 3 52 00 2 6 46 0 0
2 4 99 00 25 7 25 0 10 2 9 00
8 8 2 00
7 35 00
117600 147000
1 54 54350
1 76 40 0 1 8 375 0 2 0 58 0 0 2 1 31 50 23 52 00
3 4 000 0 59 99 00 6 4 71 50 48 0 15 0 29 1 1 00
32 17 00
3 9 1 10 0
4 74 60 0 54 1 3 50
5 8 07 50
6 40 15 0 6 7 95 5 0 7 3 895 0 8 0 83 50 65 98 0 0
60-days
Total Inflows
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
700000
DEC
JAN
F EB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
2 8 00 00
24 00 00
2 00 00 0
32 0 0 0 0
4 0 00 0 0
4 2 00 0 0
48 0 000
5 0 00 00
5 6 0 000
5 8 0 000
64 0 000
7 2 000 0
32 0 0 00
Payments
7 0 00 00
28 00 00
2 40 00 0
20 0 0 0 0
3 20 0 0 0
4 0 00 0 0
42 0 000
4 8 00 00
5 0 0 000
5 6 0 000
58 0 000
6 4 000 0
72 0 0 00
Salaries
50,000
50,000
50,000
50,000
5 0 ,0 0 0
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
140,000
Taxes
1 4 0 ,0 0 0
6,000 140,000
30,000
New Computer Total Payments
140,000
7 5 90 00
33 90 00
2 99 00 0
39 9 0 0 0
3 79 0 0 0
4 5 90 0 0
64 9 000
5 3 90 00
5 5 9 000
7 5 9 000
63 9 000
6 9 900 0
91 9 0 00
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
F EB
Total Inflows
$ 647,150
$ 480,150
MAR
$ 291,100
APR
$ 321,700
MAY
$ 391,100
JUN
$ 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 Beginning Cash Balance Balance
$ (2,000) (2,000)
$ 306,150 306,150
$ 487,300 487,300
$ 379,400 379,400
$ 322,100 322,100
$ 254,200 254,200
$ 79,800 79,800
$ 82,150 82,150
$ 103,900 103,900
$ (14,950) (14,950)
$ 25,600 25,600
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 Ending Cash Balance Balance
$ 306,150 306,150
$ 487,300 487,300
$ 379,400 379,400
$ 322,100 322,100
$ 254,200 254,200
$ 82,150 82,150
$ 103,900 103,900
$ (14,950) (14,950)
$ 25,600 25,600
$ 65,550 65,550
$ (45,100 (45,100))
$
79,800 79,800
JUL
AUG
SEP
OCT
NOV
The budget shows that September Sep tember and December are going to particularly vulnerable months for the firm as far as cash c ash 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
DEC
$
65,550 65,550
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. 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. 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 $ 919,000
Total Payments
$ 339,000
$ 299,000
$ 399,000
$ 379,000
$
459,000
$ 649,000
$ 539,000
$ 559,000
$ 759,000
$ 639,000
$ 699,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
$
Net Cash Flow
$ 308,150
$ 181,150
$ (107,900)
$ (57,300)
$
(67,900)
$ (174,400)
$
$ 21,750
$ (118,850)
$ 40,550
$ 39,950
$ (110,650)
$
$
$
$
$
$
$
$
Borrowing
-
-
-
-
-
2,350 -
$
64,950
Interest on short-term borrowing Short-term borrowing repaid Ending Cash Balance Minimu m Cash Balance Cumulative Surplus or deficit
$ 306,150 - 5 0 ,0 0 0 $ 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 - 5 0 ,0 0 0
$
29,800
$ 82,150 -50,000 $ 32,150
$ 103,900
$
-50,000 $ 53,900
50,000
$
-
Beginning Short term borrowing
$
64,950
Change in short term debt
$
Ending Short-term debt
$
(166)
$ (40,117)
$ (24,833)
$ 50,000
$ 89,950
-50,000 $
(433)
-50,000 $
-
$ $
-50,000 $ 39,950
89,950 70,700 50,000 -50,000
$
-
Cumulative Surplus or deficit
5
64,950
$ 64,950
$ 24,833
$
-
$ (40,117)
$ (24,833)
$
-
$ 24,833
$
$
-
-
