Get documents about "Excel Cash Budget
Description
Excel Cash Budget document sample
Document Sample
GEM2508
A Finance Survival Kit
GEM2508
Frederick H. Willeboordse
frederik@chaos.nus.edu.sg
1
GEM2508
Lecture 4
The Cash Budget
2
GEM2508
Today‟s Lecture
Excel
Understand the Cash Budget
Make a simple Cash Budget with
MS-Excel
3
GEM2508
Excel Basics – AND
The AND function is one
of the basics logical
functions. It returns
TRUE when all the
conditions are fulfilled
and FALSE otherwise.
One can check for as many
logical conditions as one wants
(well up to 30 or so … ).
4
GEM2508
Excel Basics – AND
One can also compare
words (these are often
called „strings‟ in
computer lingo.
However, watch out! In the
formula, you need to add
quotes to indicate to Excel
that you are dealing with a
word.
5
GEM2508
Excel Basics – OR
The OR function is one
of the basics logical
functions. It returns
TRUE when one the
conditions are fulfilled
and FALSE otherwise.
One can check for as many
logical conditions as one wants
(well up to 30 or so … ).
6
GEM2508
Excel Basics – SUMIF
The conditional sum is
very useful to extract
certain data.
One good example is that
of commissions given in
the Excel help system.
7
GEM2508
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.
8
GEM2508
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 be listed here, regardless of what they are for.
9
GEM2508
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!
10
GEM2508
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 ….
11
GEM2508
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 …
12
GEM2508
Cash Budget – Enter Sales
A B C D E F G H I J
2
3 Cash Budget April to July
4
5 Feb March April May June July
6
7 Sales 10000 10700 11449 12250 13108 14026
8 X
9 X etc
10 X etc
11 X =F7*1.07 (April sales + 7%)
Input 12 X =E7*1.07 (March sales + 7%)
13 X =D7*1.07 (Feb sales + 7%)
14 given initial sales (no formula)
We only need the initial sales in February. The rest is
calculated with a simple fromula
13
GEM2508
Enter Sales COD Receipts
A B C D E F G H I J
2
3 Cash Budget April to July
4
5 Feb March April May June July
6
7 Sales 10000 10700 11449 12250 13108 14026
8 Collections
9 Cash/Check 2000 2140 2290 2450 2622 2805
10 X
11 X etc
12 X etc
13 X =G7*0.2 (20% of Sales)
=F7*0.2 (20% of Sales)
This is completely derived with formulas!
14
GEM2508
Enter On-credit-sales Receipts
A B C D E F G H I J
2
3 Cash Budget April to July
4
5 Feb March April May June July
6
7 Sales 10000 10700 11449 12250 13108 14026
8 Collections
9 Cash/Check 2000 2140 2290 2450 2622 2805
10 One month 3000 3210 3435 3675 3932
11
12 X
13 X etc
X etc
X =F7*0.3 (30% of April Sales)
=E7*0.3 (30% of March Sales)
Again, this is completely derived with formulas!
15
GEM2508
Enter On-credit-sales Receipts
A B C D E F G H I J
2
3 Cash Budget April to July
4
5 Feb March April May June July
6
7 Sales 10000 10700 11449 12250 13108 14026
8 Collections
9 Cash/Check 2290 2450 2622 2805
10 One month 3210 3435 3675 3932
11 Two months 5000 5350 5725 6125
12
13 X
14 X etc
15 X etc
16 X =E7*0.5 (50% of April Sales)
=D7*0.5 (50% of Feb Sales)
Again, this is completely derived with formulas!
16
GEM2508
Enter Totals
A B C D E F G H I J
2
3 Cash Budget April to July
4
5 Feb March April May June July
6
7 Sales 10000 10700 11449 12250 13108 14026
8 Collections
9 Cash/Check 2290 2450 2622 2805
10 One month 3210 3435 3675 3932
11 Two months 5000 5350 5725 6125
12 Total collections 10500 11235 12021 12863
13 X
14 X etc
15 X etc
16 X =SUM(G9:G11)
=SUM(F9:F11)
Again, this is completely derived with formulas!
17
GEM2508
Enter Cash Outflow
A B C D E F G H I J
2
3 Cash Budget April to July
4
5 Feb March April May June July
6
7 Sales 10000 10700 11449 12250 13108 14026
8 Collections
9 Cash/Check 2,290 2,450 2,622 2,805
10 One month 3,210 3,435 3,675 3,932
11 Two months 5,000 5,350 5,725 6,125
12 Total collections 10,500 11,235 12,021 12,863
13 Purchases 6,125 =G7*0.5
14 Manpower/Fixed 2,290 =F7*0.2
15 Loan Repayment 2,000 Given
16 Total Payments 10,415 =SUM(F13:F15)
17 Net Cash Flow 85 =F12-F16
Mostly formulas, except for the loan repayments
18
GEM2508
Obtain Totals
A B C D E F G H I J
2
3 Cash Budget April to July
4
5 Feb March April May June July Aug
6
7 Sales 10000 10700 11449 12250 13108 14026 15007
8 Collections
9 Cash/Check 2,290 2,450 2,622 2,805
10 One month 3,210 3,435 3,675 3,932
11 Two months 5,000 5,350 5,725 6,125
12 Total collections 10,500 11,235 12,021 12,863
13 Purchases 6,125 6,554 7,013 7,504
14 Manpower/Fixed 2,290 2,450 2,622 2,805
15 Loan Repayment 2,000 2,000 2,000 2,000
16 Total Payments 10,415 11,004 11,634 12,309
17 Net Cash Flow 85 231 387 554
Copy and Paste the rest.
Great, but ugly … let‟s clean this up again.
19
GEM2508
Cash Budget
A B C D E F G H I J
2
3 Cash Budget April to July
4
5 Feb March April May June July Aug
6
7 Sales 10000 10700 11449 12250 13108 14026 15007
8
9 Cash/Check 2,290 2,450 2,622 2,805
10 One month 3,210 3,435 3,675 3,932
11 Two months 5,000 5,350 5,725 6,125
12 Total collections 10,500 11,235 12,021 12,863
How 13 Purchases 6,125 6,554 7,013 7,504
much do 14
15
Manpower/Fixed
Loan Repayment
2,290 2,450 2,622 2,805
2,000 2,000 2,000 2,000
we have? 16 Total Payments 10,415 11,004 11,634 12,309
17 Net Cash Flow 85 231 387 554
This is nice, but it does not really reflect the
cash position.
20
GEM2508
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
21
GEM2508
Ending Cash
A B C D E F G H I J
2
3 Cash Budget April to July
4
5 Feb April
March May June July Aug
6 Beginning Cash 20,000 20,085 =F20
7 Sales 10,000 10,700 11,449 12,250 13,108 14,026 15,007
8
9 Cash/Check 2,290 2,450 2,622 2,805
10 One month 3,210 3,435 3,675 3,932
11 Two months 5,000 5,350 5,725 6,125
12 Total collections 10,500 11,235 12,021 12,863
13 Purchases 6,125 6,554 7,013 7,504
14 Manpower/Fixed 2,290 2,450 2,622 2,805
15 Loan Repayment 2,000 2,000 2,000 2,000
16 Total Payments 10,415 11,004 11,634 12,309
17 Net Cash Flow 85 231 387 554
18 Unadjusted Cash Balance 20,085 =F6+F17
19 Borrowing 0 =IF(F18<0,-F18,0)
20 Ending Cash 20,085 =F18+F19
22
GEM2508
Ending Cash Time for a
nap!?
A B C D E F G H I J
2
3 Cash Budget April to July
4
5 Feb April
March May June July Aug
6 Beginning Cash 20,000 20,085 20,316 20,702
7 Sales 10,000 10,700 11,449 12,250 13,108 14,026 15,007
8
9 Cash/Check 2,290 2,450 2,622 2,805
10 One month 3,210 3,435 3,675 3,932
11 Two months 5,000 5,350 5,725 6,125
12 Total collections 10,500 11,235 12,021 12,863
13 Purchases 6,125 6,554 7,013 7,504
14 Manpower/Fixed 2,290 2,450 2,622 2,805
15 Loan Repayment 2,000 2,000 2,000 2,000
16 Total Payments 10,415 11,004 11,634 12,309
17 Net Cash Flow 85 231 387 554
18 Unadjusted Cash Balance 20,085 20,316 20,702 21,256
19 Borrowing 0 0 0 0
20 Ending Cash 20,085 20,316 20,702 21,256
23
GEM2508
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.
24
GEM2508
Scenarios
3 Cash Budget April to July
Inputs
4
5 Sales Growth 7% %COD 20% %Del 0% Loan rep 2,000
6 Initial Sales 10,000 % 1mo 30% % Inv 50%
7 Begiining Cash 20,000 % 2mo 50% % Man 20%
8
9 Feb March April May June July Aug
10 Beginning Cash 20,000 20,085 20,316 20,702
11 Sales 10,000 10,700 11,449 12,250 13,108 14,026 15,007
Excel does
12 Cash/Check 2,290 2,450 2,622 2,805
13 One month 3,210 3,435 3,675 3,932
the work
14 Two months 5,000 5,350 5,725 6,125
15 Total collections 10,500 11,235 12,021 12,863
16 Purchases 6,125 6,554 7,013 7,504
17 Manpower/Fixed 2,290 2,450 2,622 2,805
18 Loan Repayment 2,000 2,000 2,000 2,000
19 Total Payments 10,415 11,004 11,634 12,309
20 Net Cash Flow 85 231 387 554
21 Unadjusted Cash Balance 20,085 20,316 20,702 21,256
22 Borrowing 0 0 0 0
23 Ending Cash 20,085 20,316 20,702 21,256
25
GEM2508
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.
26
GEM2508
Cash Budget
3 Cash Budget April to July
4
5 Sales Growth 11% %COD 20% %Del 0% Loan rep 2,000
6 Initial Sales 10,000 % 1mo 30% % Inv 50%
7 Begining Cash 1,000 % 2mo 50% % Man 25%
8
9 Feb March April May June July Aug
10 Beginning Cash 1,000 0 0 0
11 Sales 10,000 11,100 12,321 13,676 15,181 16,851 18,704
12 Cash/Check 2,464 2,735 3,036 3,370
13 One month 3,330 3,696 4,103 4,554
14 Two months 5,000 5,550 6,161 6,838
15 Total collections 10,794 11,982 13,300 14,762
16 Purchases 6,838 7,590 8,425 9,352
17 Manpower/Fixed 3,080 3,419 3,795 4,213
18 Loan Repayment 2,000 2,000 2,000 2,000
19 Total Payments 11,918 13,009 14,220 15,565
20 Net Cash Flow -1,124 -1,028 -921 -802
21 Unadjusted Cash Balance -124 -1,028 -921 -802
22 Borrowing 124 1,028 921 802
23 Ending Cash 0 0 0 0
27
GEM2508
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.
28
GEM2508
Cash Budget
3
4 Sales Growth 11% %COD 20% %Del 0% Old Loan 2,000
5 Initial Sales 10,000 % 1mo 30% % Inv 50% Interest on 1%
6 Begining Cash 1,000 % 2mo 50% % Man 20% new Loan
7 Minimum Cash 1,000
8
9 Feb March April May June July Aug
10 Beginning Cash 1,000 1,000 1,000 1,000
11 Sales 10,000 11,100 12,321 13,676 15,181 16,851 18,704
12 Cash/Check 2,464 2,735 3,036 3,370
13 One month 3,330 3,696 4,103 4,554
14 Two months 5,000 5,550 6,161 6,838
15 Total collections 10,794 11,982 13,300 14,762
16 Purchases 6,838 7,590 8,425 9,352
17 Manpower/Fixed 2,464 2,735 3,036 3,370
18 Loan Repayments 2,000 2,005 2,009 2,010
19 Total Payments 11,302 12,331 13,470 14,732
20 Net Cash Flow -508 -349 -170 30
21 Unadjusted Cash Balance 492 651 830 1,030
22 Borrowing 508 349 170 0
23 Ending Cash 1,000 1,000 1,000 1,030
Total Loan 0 508 857 1,028 1,028
29
GEM2508
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
30
GEM2508
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
31
GEM2508
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!
32
GEM2508
What-if: Growth 38%
2 Cash Budget April to July -Golden Dragon Double Win International
3
4 Sales Growth 38% %COD 20% %Del 0% Old Loan 2,000
5 Initial Sales 10,000 % 1mo 30% % Inv 50% Interest on 1%
6 Begining Cash 10,010 % 2mo 50% % Man 25% new Loan
7 Minimum Cash 10,000
8
9 Feb March April May June July Aug
10 Beginning Cash 10,010 10,000 10,000 10,000
11 Sales 10,000 13,800 19,044 26,281 36,267 50,049 69,068
12 Cash/Check 3,809 5,256 7,253 10,010
13 One month 4,140 5,713 7,884 10,880
14 Two months 5,000 6,900 9,522 13,140
15 Total collections 12,949 17,869 24,660 34,030
16 Purchases 13,140 18,134 25,025 34,534
17 Manpower/Fixed 4,761 6,570 9,067 12,512
18 Loan Repayments 2,000 2,069 2,158 2,274
19 Total Payments 19,901 26,773 36,250 49,320
20 Net Cash Flow -6,953 -8,904 -11,590 -15,290
21 Unadjusted Cash Balance 3,057 1,096 -1,590 -5,290
22 Borrowing 6,943 8,904 11,590 15,290
23 Ending Cash 10,000 10,000 10,000 10,000
33
GEM2508
What-if: Growth 38%
4 Sales Growth 38% %COD 20% %Del 0% Old Loan 2,000
5 Initial Sales 10,000 % 1mo 30% % Inv 50% Interest on 1%
6 Begining Cash 10,000 % 2mo 50% % Man 25% new Loan
7 Minimum Cash 10,000
8
9 Aug Sep Oct Nov Dec Jan Feb
10 Beginning Cash 10,000 10,000 10,000 10,000
11 Sales 50,049 69,068 95,313 131,532 181,515 250,490 345,677
12 Cash/Check 19,063 26,306 36,303 50,098
New Borrowing
13 One month 20,720 28,594 39,460 54,454
Grows rapidly!
14
15
Two months
Total collections
25,025
64,807
34,534
89,434
47,657
123,419
65,766
170,319
16 Purchases 65,766 90,757 125,245 172,838
17 Manpower/Fixed 23,828 32,883 45,379 62,623
18 Loan Repayments 2,371 2,642 3,011 3,513
19 Total Payments 91,965 126,283 173,634 238,974
20 Net Cash Flow -27,158 -36,848 -50,215 -68,655
21 Unadjusted -17,158 -26,848 -40,215 -58,655
22 Borrowing 27,158 36,848 50,215 68,655
23 Ending Cash 10,000 10,000 10,000 10,000
Total New Borrowing 37052 64,210 101,058 151,273 219,928
34
GEM2508
What-if: Growth 38%
4 Sales Growth 38% %COD 20% %Del 0% Old Loan 2,000
5 Initial Sales 10,000 % 1mo 30% % Inv 50% Interest on 1%
6 Begining Cash 10,000 % 2mo 50% % Man 25% new Loan
7 Minimum Cash 10,000
8
But once it stops Jan
9 Feb Mar Apr May Jun Jul
10 Beginning Cash 10,000 44,627 126,846 209,066
growing, cash flow
11 Sales 250,490 345,676 345,676 345,676 345,676 345,676 345,676
12 Cash/Check 69,135 69,135 69,135 69,135
becomes positive
13 One month 103,703 103,703 103,703 103,703
14 Two months 125,245 172,838 172,838 172,838
rapidly.Total collections
15 298,083 345,676 345,676 345,676
16 Purchases 172,838 172,838 172,838 172,838
17 Manpower/Fixed 86,419 86,419 86,419 86,419
18 Loan Repayments 4,199 4,199 4,199 4,199
19 Total Payments 263,456 263,456 263,456 263,456
20 Net Cash Flow 34,627 82,220 82,220 82,220
21 Unadjusted 44,627 126,846 209,066 291,286
22 Borrowing 0 0 0 0
23 Ending Cash 44,627 126,846 209,066 291,286
Total New Borrowing 219,928 219,928 219,928 219,928 219,928
35
GEM2508
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
36
GEM2508
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.
37
Related docs
