# Excel Cash Budget

Description

Excel Cash Budget document sample

Shared by:
Categories
-
Stats
views:
486
posted:
2/2/2011
language:
English
pages:
37
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