Transcript The Profit and Loss Statement

The Cash Budget
Lecture 4
This lecture is part of Chapter 2:
Budgets, Running a Company
Today’s Lecture
Understand the Cash Budget
Make a simple Cash Budget with MS-Excel
What is a Cash Budget
A Cash Budget states all the cash inflows and
outflows for a certain period of time.
Sometimes the cash budget is also called
“Statement of budgeted cash receipts and
disbursements”.
A cash budget is not the same as an income
statement.
What is a Cash Budget
Main Differences with the Income Statement:
–
–
–
Depreciation is not included
Loans are included
Dividends are included
The Cash Budget is about “Cash”! All dollars in or out should
should be listed here, regardless of what they are for.
Main elements of a Cash Budget
The main elements of a cash budget are:
–
–
–
–
–
–
Cash collections from customers (IN)
Cash disbursements for purchases (OUT)
Cash disbursements for operating expenses (OUT)
Capital Expenditures (OUT)
Loans (IN)
Loan repayments (OUT)
As you can see everything is either IN or OUT.
In that sense it’s really easy!
Sounds complicated
Let us look at a company that expects the following:
–
–
–
–
–
–
–
Sales are expected to grow at 7% a month
20% of sales are COD (cash/check on delivery)
30% of sales are paid during the month following the sale
50% of sales are paid in the second month after the sale
Manpower and fixed costs are 20% of sales
Inventory purchases are 50% of the following month’s sales
Loan repayments of $2000 per month
Indeed, this seems to be pretty complicated.
Fortunately ….
Fortunately … NOT
Fortunately, it’s not as bad as it looks if we approach
the problem systematically by entering all the items
one by one into Excel.
Let us start with entering the sales
Like a vegetable stall, one by one …
Cash Budget – Enter Sales
A
Input
2
3
4
5
6
7
8
9
10
11
12
13
14
B
C
D
E
F
G
H
I
June
July
J
Cash Budget April to July
Feb
Sales
March
April
May
10000 10700 11449 12250 13108 14026
X
X etc
X
etc
X
=F7*1.07 (April sales + 7%)
X
=E7*1.07 (March sales + 7%)
X
=D7*1.07 (Feb sales + 7%)
given initial sales (no formula)
We only need the initial sales in February. The rest is
calculated with a simple fromula
Enter COD Receipts
A
2
3
4
5
6
7
8
9
10
11
12
13
B
C
D
E
F
G
H
I
June
July
Cash Budget April to July
Feb
Sales
Collections
Cash/Check
March
April
May
10000 10700 11449 12250 13108 14026
2000
2140
2290
2450
2622
2805
X
etc
X
X etc
X
=G7*0.2 (20% of Sales)
=F7*0.2 (20% of Sales)
This is completely derived with formulas!
J
Enter Credit-Sales Receipts
A
2
3
4
5
6
7
8
9
10
11
12
13
B
C
D
E
F
G
H
I
June
July
J
Cash Budget April to July
Feb
Sales
Collections
Cash/Check
One month
March
April
May
10000 10700 11449 12250 13108 14026
2000
2140
3000
2290
3210
2450
3435
2622
3675
2805
3932
X
etc
X
X
etc
X
=F7*0.3 (30% of April Sales)
=E7*0.3 (30% of March Sales)
Again, this is completely derived with formulas!
Enter Credit-Sales Receipts
A
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
B
C
D
E
F
G
H
I
June
July
J
Cash Budget April to July
Feb
Sales
Collections
Cash/Check
One month
Two months
March
April
May
10000 10700 11449 12250 13108 14026
2290
3210
5000
2450
3435
5350
2622
3675
5725
2805
3932
6125
X
etc
X
X
etc
X
=E7*0.5 (50% of April Sales)
=D7*0.5 (50% of Feb Sales)
Again, this is completely derived with formulas!
Enter Totals
A
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
B
C
D
E
F
G
H
I
June
July
J
Cash Budget April to July
Feb
March
April
May
Sales
10000 10700 11449 12250 13108 14026
Collections
Cash/Check
2290 2450 2622 2805
One month
3210 3435 3675 3932
Two months
5000 5350 5725 6125
Total collections
10500 11235 12021 12863
X
X etc
X
etc
X
=SUM(G9:G11)
=SUM(F9:F11)
Again, this is completely derived with formulas!
Enter Cash Outflow
A
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
B
C
D
E
F
G
H
I
June
July
J
Cash Budget April to July
Feb
March
April
May
10000 10700 11449 12250
Sales
Collections
2,290 2,450
Cash/Check
3,210 3,435
One month
5,000 5,350
Two months
10,500 11,235
Total collections
6,125 
Purchases
2,290 
Manpower/Fixed
2,000 
Loan Repayment
10,415 
Total Payments
85 
Net Cash Flow
13108 14026
2,622 2,805
3,675 3,932
5,725 6,125
12,021 12,863
=G7*0.5
=F7*0.2
Given
=SUM(F13:F15)
=F12-F16
Mostly formulas, except for the loan repayments
Obtain Totals
A
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
B
C
D
E
F
G
H
I
June
July
J
Cash Budget April to July
Feb
March
April
May
Aug
Sales
10000 10700 11449 12250 13108 14026 15007
Collections
Cash/Check
2,290 2,450 2,622 2,805
One month
3,210 3,435 3,675 3,932
Two months
5,000 5,350 5,725 6,125
Total collections
10,500 11,235 12,021 12,863
Purchases
6,125 6,554 7,013 7,504
Manpower/Fixed
2,290 2,450 2,622 2,805
Loan Repayment
2,000 2,000 2,000 2,000
Total Payments
10,415 11,004 11,634 12,309
Net Cash Flow
85
231
387
554
Copy and Paste the rest.
Great, but ugly … let’s clean this up again.
Cash Budget
A
How much do
we have?
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
B
C
D
E
F
G
H
I
June
July
J
Cash Budget April to July
Feb
Sales
March
April
May
Aug
10000 10700 11449 12250 13108 14026 15007
Cash/Check
One month
Two months
Total collections
Purchases
Manpower/Fixed
Loan Repayment
Total Payments
Net Cash Flow
2,290 2,450 2,622 2,805
3,210 3,435 3,675 3,932
5,000 5,350 5,725 6,125
10,500 11,235 12,021 12,863
6,125 6,554 7,013 7,504
2,290 2,450 2,622 2,805
2,000 2,000 2,000 2,000
10,415 11,004 11,634 12,309
85
231
387
554
This is nice, but it does not really reflect the cash
position.
Ending Balance
The Ending Balance is an important indicator of the
company’s cash position. It is calculated as:
+
+
=
+
=
Beginning Cash Balance
Total Collections (Total Cash IN)
Total Disbursements (Total Cash OUT)
Unadjusted Cash Balance
Current Borrowing
Ending Balance
Let us add these elements to our previous Cash Balance
Ending Cash
A
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
B
C
D
E
F
G
H
I
J
Cash Budget April to July
Feb
April
May
Beginning Cash
20,000 20,085
Sales
10,000 10,700 11,449 12,250
Cash/Check
One month
Two months
Total collections
Purchases
Manpower/Fixed
Loan Repayment
Total Payments
Net Cash Flow
Unadjusted Cash Balance
Borrowing
Ending Cash
March
June
July
Aug
 =F20
13,108
14,026 15,007
2,290 2,450
2,622
2,805
3,210 3,435
3,675
3,932
5,000 5,350
5,725
6,125
10,500 11,235
12,021
12,863
6,125 6,554
7,013
7,504
2,290 2,450
2,622
2,805
2,000 2,000
2,000
2,000
10,415 11,004
11,634
12,309
85
231
387
554
20,085
 =F6+F17
0
 =IF(F18<0,-F18,0)
20,085
 =F18+F19
Ending Cash
A
Time for a
nap!?
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
B
C
E
D
F
H
G
I
J
Cash Budget April to July
May
April
20,000 20,085
Beginning Cash
10,000 10,700 11,449 12,250
Sales
June
20,316
13,108
Aug
July
20,702
14,026 15,007
2,450
3,435
5,350
11,235
6,554
2,450
2,000
11,004
231
20,316
0
20,316
2,622
3,675
5,725
12,021
7,013
2,622
2,000
11,634
387
20,702
0
20,702
2,805
3,932
6,125
12,863
7,504
2,805
2,000
12,309
554
21,256
0
21,256
Feb
Cash/Check
One month
Two months
Total collections
Purchases
Manpower/Fixed
Loan Repayment
Total Payments
Net Cash Flow
Unadjusted Cash Balance
Borrowing
Ending Cash
March
2,290
3,210
5,000
10,500
6,125
2,290
2,000
10,415
85
20,085
0
20,085
Done?
Scenarios!!
Not quite!
This is nice, BUT, the assumptions are all fixed.
Since almost all of the calculations are done by
formulas, we can use this for investigating scenarios!
Let’s take out all the numbers which were inserted
manually.
Scenarios
Inputs
Excel does
the work
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Cash Budget April to July
Sales Growth
Initial Sales
Begiining Cash
7%
%COD 20%
10,000 % 1mo 30%
20,000 % 2mo 50%
Feb
March
April
Beginning Cash
20,000
Sales
10,000 10,700 11,449
Cash/Check
2,290
One month
3,210
Two months
5,000
Total collections
10,500
Purchases
6,125
Manpower/Fixed
2,290
Loan Repayment
2,000
Total Payments
10,415
Net Cash Flow
85
Unadjusted Cash Balance
20,085
Borrowing
0
Ending Cash
20,085
%Del 0%
% Inv 50%
% Man 20%
May
20,085
12,250
2,450
3,435
5,350
11,235
6,554
2,450
2,000
11,004
231
20,316
0
20,316
June
20,316
13,108
2,622
3,675
5,725
12,021
7,013
2,622
2,000
11,634
387
20,702
0
20,702
Loan rep
2,000
July
Aug
20,702
14,026 15,007
2,805
3,932
6,125
12,863
7,504
2,805
2,000
12,309
554
21,256
0
21,256
A Better Cash Budget
All the numbers in the Cash Budget thus only
depend on the assumptions in cells B5..I7.
Now we can easily see what happens if e.g. the
Growth increases to 11% and the manpower costs
to 25% but the beginning cash is reduced to 1000.
Cash Budget
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Cash Budget April to July
Sales Growth
Initial Sales
Begining Cash
11%
%COD 20%
10,000 % 1mo 30%
1,000 % 2mo 50%
Feb
March
April
Beginning Cash
1,000
Sales
10,000 11,100 12,321
Cash/Check
2,464
One month
3,330
Two months
5,000
Total collections
10,794
Purchases
6,838
Manpower/Fixed
3,080
Loan Repayment
2,000
Total Payments
11,918
Net Cash Flow
-1,124
Unadjusted Cash Balance
-124
Borrowing
124
Ending Cash
0
%Del 0%
% Inv 50%
% Man 25%
May
0
13,676
2,735
3,696
5,550
11,982
7,590
3,419
2,000
13,009
-1,028
-1,028
1,028
0
Loan rep
2,000
June
July
0
15,181
3,036
4,103
6,161
13,300
8,425
3,795
2,000
14,220
-921
-921
921
0
0
16,851 18,704
3,370
4,554
6,838
14,762
9,352
4,213
2,000
15,565
-802
-802
802
0
Aug
A Better Cash Budget
Great we have achieved a lot, but we can still
make this a bit better. The very least we would
need to pay interest on the new loans. Also, in
order to avoid bounced checks etc., we should
have a minimum sum in the bank.
Let us add these requirements.
Cash Budget
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sales Growth
Initial Sales
Begining Cash
Minimum Cash
11%
%COD 20%
10,000 % 1mo 30%
1,000 % 2mo 50%
1,000
March April
Beginning Cash
1,000
Sales
10,000 11,100 12,321
Cash/Check
2,464
One month
3,330
Two months
5,000
Total collections
10,794
Purchases
6,838
Manpower/Fixed
2,464
Loan Repayments
2,000
Total Payments
11,302
Net Cash Flow
-508
Unadjusted Cash Balance
492
Borrowing
508
Ending Cash
1,000
Total Loan
Feb
0
508
%Del 0%
% Inv 50%
% Man 20%
May
1,000
13,676
2,735
3,696
5,550
11,982
7,590
2,735
2,005
12,331
-349
651
349
1,000
June
1,000
15,181
3,036
4,103
6,161
13,300
8,425
3,036
2,009
13,470
-170
830
170
1,000
857
1,028
Old Loan 2,000
Interest on 1%
new Loan
July
Aug
1,000
16,851 18,704
3,370
4,554
6,838
14,762
9,352
3,370
2,010
14,732
30
1,030
0
1,030
1,028
A Better Cash Budget
How was this done?
In E22, the formula used was:
=IF(E21<$C$7,$C$7-E21,0)
Then on a auxiliary row, the total new loans are
being kept track off.
The new formula for loan repayments then
becomes:
=$I$4+$I$5*D25
Amazing!
We not only accomplished budgeting for all this:
–
–
–
–
–
–
–
Sales are expected to grow at 7% a month
20% of sales are COD (cash/check on delivery)
30% of sales are paid during the month following the sale
50% of sales are paid in the second month after the sale
Manpower and fixed costs are 20% of sales
Inventory purchases are 50% of the following month’s sales
Loan repayments of $2000 per month
But we also
Amazing!
..added:
–
–
–
–
What-IF Scenarios
Borrowing requirements
Maintenance of a minimum sum
Effects of interest of new loans on cash flow
Good use of simple tools can be powerful!
What-if: Growth 38%
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Cash Budget April to July -Golden Dragon Double Win International
Sales Growth
Initial Sales
Begining Cash
Minimum Cash
38%
%COD 20%
10,000 % 1mo 30%
10,010 % 2mo 50%
10,000
Feb
March April
Beginning Cash
10,010
Sales
10,000 13,800 19,044
Cash/Check
3,809
One month
4,140
Two months
5,000
Total collections
12,949
Purchases
13,140
Manpower/Fixed
4,761
Loan Repayments
2,000
Total Payments
19,901
Net Cash Flow
-6,953
Unadjusted Cash Balance
3,057
Borrowing
6,943
Ending Cash
10,000
%Del 0%
% Inv 50%
% Man 25%
May
10,000
26,281
5,256
5,713
6,900
17,869
18,134
6,570
2,069
26,773
-8,904
1,096
8,904
10,000
June
10,000
36,267
7,253
7,884
9,522
24,660
25,025
9,067
2,158
36,250
-11,590
-1,590
11,590
10,000
Old Loan 2,000
Interest on 1%
new Loan
July
Aug
10,000
50,049 69,068
10,010
10,880
13,140
34,030
34,534
12,512
2,274
49,320
-15,290
-5,290
15,290
10,000
What-if: Growth 38%
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
New Borrowing
Grows rapidly!
Sales Growth
Initial Sales
Begining Cash
Minimum Cash
%COD 20%
38%
10,000 % 1mo 30%
10,000 % 2mo 50%
10,000
Aug
Sep
Beginning Cash
50,049 69,068
Sales
Cash/Check
One month
Two months
Total collections
Purchases
Manpower/Fixed
Loan Repayments
Total Payments
Net Cash Flow
Unadjusted
Borrowing
Ending Cash
Total New Borrowing
37052
Oct
10,000
95,313
19,063
20,720
25,025
64,807
65,766
23,828
2,371
91,965
-27,158
-17,158
27,158
10,000
%Del
% Inv
% Man
Nov
10,000
131,532
26,306
28,594
34,534
89,434
90,757
32,883
2,642
126,283
-36,848
-26,848
36,848
10,000
0%
50%
25%
Dec
10,000
181,515
36,303
39,460
47,657
123,419
125,245
45,379
3,011
173,634
-50,215
-40,215
50,215
10,000
64,210 101,058 151,273
Old Loan 2,000
Interest on 1%
new Loan
Feb
Jan
10,000
250,490 345,677
50,098
54,454
65,766
170,319
172,838
62,623
3,513
238,974
-68,655
-58,655
68,655
10,000
219,928
What-if: Growth 38%
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sales Growth
Initial Sales
Begining Cash
Minimum Cash
%COD
38%
10,000 % 1mo
10,000 % 2mo
10,000
Feb
Jan
Beginning Cash
250,490 345,676
Sales
Cash/Check
One month
Two months
Total collections
Purchases
Manpower/Fixed
Loan Repayments
Total Payments
Net Cash Flow
Unadjusted
Borrowing
Ending Cash
But once it stops
growing, cash flow
becomes positive
rapidly.
Total New Borrowing
219,928
20%
30%
50%
Mar
10,000
345,676
69,135
103,703
125,245
298,083
172,838
86,419
4,199
263,456
34,627
44,627
0
44,627
%Del
% Inv
% Man
Apr
44,627
345,676
69,135
103,703
172,838
345,676
172,838
86,419
4,199
263,456
82,220
126,846
0
126,846
0%
50%
25%
May
126,846
345,676
69,135
103,703
172,838
345,676
172,838
86,419
4,199
263,456
82,220
209,066
0
209,066
219,928 219,928 219,928
Old Loan 2,000
Interest on 1%
new Loan
Jul
Jun
209,066
345,676 345,676
69,135
103,703
172,838
345,676
172,838
86,419
4,199
263,456
82,220
291,286
0
291,286
219,928
Amazing!
..indeed:
–
–
–
–
New cash requirements grow rapidly
If profitability growth cannot keep pace there will be trouble
Even though profitable, this company could go bust in a cash
crunch
On the other hand, if growth slows, profits may become large
Good use of simple tools can be powerful!
May depend on a
sound analysis
Key Points of the Day
The Cash Budget shows the cash requirements of a
company
Even though the task accomplished is rather complex, it is
surprisingly easy to do with Excel when doing things step
by step.