Chapter 3 Agenda - Cash Budgeting
More than anything else, the ability to create a MODEL of a company’s cash budget
to facilitate updating and what-if analysis is what made spreadsheet software
a MUST-HAVE for companies, and that drove the PC revolution in business.
Review: A model must have three sections - (1) Inputs (2) Outputs (3) calculations
INPUTS CALCULATIONS OUTPUTS
The cash budget simply shows all cash flows into and out of the company over time
and computes the net cash flow and the cash balance at the end of each period.
The cash budget is much more detailed than the Statement of Cash Flows, and is
generally done by months rather than by quarters or years.
It is based on assumptions, the most important of which is Sales.
The primary output is the cash balance projected over time so additional
financing sources can be arranged in advance if needed.
WHAT-IF analysis on the inputs is a very important part of the decision-
making process, and thus the spreadsheet is the perfect setting.
Go to the SETUP page of this workbook.
NAME the major variables.
Name the range D5:J5 SALES and the inputs for collections
COLLECT0, COLLECT1, and COLLECT 2.
Build and complete the COLLECTIONS and PURCHASES section
Note how named ranges and cells do not have to be locked to copy correctly. How did I make this divider?
A range that has been named works differently -- a formula always refers to the same Hold down Shift to draw a
column or row of a named range. perfect horizonal line. Group
Build and complete the CALCULATIONS section. the objects after creating.
Part 2
Build and complete the CASH BUDGET section After it's grouped, you can
Clean up the formatting so the underline works correctly. Use zeros rather than spaces. use CTRL
Build and complete the CASH ACCOUNT section and move an exact copy.
Note in Cell B61 and B62 how to get a label that starts with a math symbol to work
Part 3
Build and complete the ST BORROWING/LENDING section
Note that part of this differs from the approach taken in the textbook.
With a cash balance in excess of the required minimum and loans outstanding,
the loans would be paid down rather than left to incur additional interest expense.
Any excess after short-term loans are paid off would be invested in
marketable securities to earn interest.
See how an =IF( ) formula is used to get the cumulative short-term loan
and marketable securities balances
Note: Ignore the calculation of interest on short term borrowing and earned
on marketable securities (covered on pages 82-85). Our method already
included some of this, but the interest paid and earned is small and we
will ignore it to avoid some circular calculations.
Add an input for the month of the capital outlay.
Use an =if() function to move the outlay to from month to month.
Create a dropdown list so the user can select a month. (Data validation)
Move the Inputs section to another tabbed page and delete the now empty rows.
Note how all formulas adjust when cells are moved, even to another page.
Be sure to check all formulas. Use the formula auditing tools that are available.
Color coded cell references in formulas
Use F9 to evaluate parts of formulas in the formula bar (Try on the =IF formula)
In the Formula Auditing Toolbar:
Trace Precedents
Trace Dependents
Show Formulas
Error Checking
Watch Window - Lets you see important results from anywhere
Spell check is also available (on the REVIEW menu tab)
Part 4
Create a graph that shows the balance of short-term borrowing and lending
over time.
Do the final formatting:
Get underlines and dollar signs in the right places
Turn of grid lines.
Hide unnecessary text or columns
Protect the worksheet so only the input cells can be changed.
Select the cells that the user will be able to change -- Rt-Click > Format > Protection
Turn protection on -- REVIEW tab > Protect Worksheet.
RISK ANALYSIS
Conduct sensitivity analysis on the major inputs to see what could reduce the debt
that would be required to purchase the new capital equipment.
Conduct scenario analysis to model three possible outcomes -- Good, bad, and expected.
Discuss simulation as a tool for risk analysis -- We will do some later
INPUTS
APR MAY JUN
Sales 291,000 365,000 387,000
Collection Rates for Sales:
% of sales collected in month of sale 40%
% of sales collected one month after sale 45%
% of sales collected two months after sale 15%
Raw Materials as % of following month's sales: 50%
% paid in first month following purchase 60%
% paid in second month following purchase 40%
Other Inputs
Wages as % of current month's sales: 20%
Cash dividend in June: $50,000
Capital Improvement for June or other: $200,000
Minimum allowable cash balance: $15,000
Constants:
Lease payments per month $10,000
Interest Payments - June and Sept $30,000
CALCULATIONS
APR MAY JUN
Collections:
In Month of Sale
In Month After Sale
Two Months After Sale
Total Collections
Payments on Purchases:
Raw Materials Purchases
Portion paid 1 mth after purchase
Portion paid 2 mths after purchase
Total Payments on Purchases
CASH BUDGET
APR MAY JUN
Collections
Less Disbursements
Payments on Purchases
Wages
Lease Payments
Interest
Common Stock Dividend
Taxes 25,000
Capital Outlays
Total Disbursements
Net Cash Flow
CASH ACCOUNT
APR MAY JUN
Beginning Cash Balance
+ Total Collections
- Total Disbursements
Unadjusted Cash Balance
Adjustment Needed
Ending Cash Balance 20,000
Note that this part differs from the textbook
Short-Term Borrowing/Lending
Cumulative Adjustment
Short-Term Loans Balance -
Marketable Securities Balance -
JUL AUG SEP OCT
329,000 238,000 145,000 92,000
S
JUL AUG SEP OCT
T
JUL AUG SEP
25,000
T
JUL AUG SEP
/Lending