The Cash Budget

1
The Cash Budget

• Lecture 4
• This lecture is part of Chapter 2 Budgets,
  Running a Company

2
Todays Lecture

• Understand the Cash Budget
• Make a simple Cash Budget with MS-Excel

3
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.

4
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.

5
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 its really easy!

6
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
  months sales
• Loan repayments of 2000 per month
• Indeed, this seems to be pretty complicated.
• Fortunately .

7
Fortunately NOT

• Fortunately, its 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
8
Cash Budget Enter Sales
Input
We only need the initial sales in February. The
rest is calculated with a simple fromula
9
Enter COD Receipts
This is completely derived with formulas!
10
Enter Credit-Sales Receipts
Again, this is completely derived with formulas!
11
Enter Credit-Sales Receipts
Again, this is completely derived with formulas!
12
Enter Totals
Lucky we have a spreadsheet!
Again, this is completely derived with formulas!
13
Enter Cash Outflow
Mostly formulas, except for the loan repayments
14
Obtain Totals
Copy and Paste the rest. Great, but ugly lets
clean this up again.
15
Cash Budget
How much dowe have?
This is nice, but it does not really reflect the
cash position.
16
Ending Balance
The Ending Balance is an important indicator of
the companys 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
17
Ending Cash
18
Ending Cash
Time for a nap!?
19
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!Lets take out
all the numbers which were inserted manually.
20
Scenarios
Inputs
Excel does the work
21
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.
22
Cash Budget
23
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.
24
Cash Budget
25
A Better Cash Budget
How was this done? In E22, the formula used
was IF(E21ltC7,C7-E21,0) Then on a
auxiliary row, the total new loans are being kept
track off. The new formula for loan repayments
then becomes I4I5D25
26
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
  months sales
• Loan repayments of 2000 per month
• But we also

27
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!

28
What-if Growth 38
29
What-if Growth 38
New Borrowing Grows rapidly!
30
What-if Growth 38
But once it stopsgrowing, cash flowbecomes
positiverapidly.
31
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 asound analysis
32
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.