CHAPTER 3 Cash Flow and Financial Planning 117TABLE 3.11 A Sensitivity Analysis of Coulson Industries’ Cash Budget ($000)
October November DecemberPessi- Most Opti- Pessi- Most Opti- Pessi- Most Opti-
mistic likely mistic mistic likely mistic mistic likely misticTotal cash
receipts $160 $210 $285 $210 $320 $ 410 $275 $340 $422
Less: Total cashdisbursements (^2) (^0) (^0) (^2) (^1) (^3) (^2) (^4) (^8) (^3) (^8) (^0) (^4) (^1) (^8) (^4) (^6) (^7) (^2) (^8) (^0) (^3) (^0) (^5) (^3) (^2) (^0)
Net cash flow ($ 40) ($ 3) $ 37 ($170) ($ 98) ($ 57) ($ 5) $ 35 $102
Add: Beginning
cash (^5) (^0) (^5) (^0) (^5) (^0) (^1) (^0) (^4) (^7) (^8) (^7) ( (^1) (^6) (^0) )( (^5) (^1) ) (^3) (^0)
Ending cash $ 10 $ 47 $ 87 ($160) ($ 51) $ 30 ($165) ($ 16) $132
Less: Minimum
cash balance (^2) (^5) (^2) (^5) (^2) (^5) (^2) (^5) (^2) (^5) (^2) (^5) (^2) (^5) (^2) (^5) (^2) (^5)
Required total
financing $ 15 — — $185 $ 76 — $190 $ 41 —
Excess cash
balance — $ 22 $ 62 — — $ 5 — — $107
- The term uncertaintyis used here to refer to the variability of the cash flow outcomes that may actually occur.
Coping with Uncertainty in the Cash Budget
Aside from careful estimation of cash budget inputs, there are two ways of cop-
ing with the uncertainty of the cash budget.^8 One is to prepare several cash bud-
gets—based on pessimistic, most likely, and optimistic forecasts. From this range
of cash flows, the financial manager can determine the amount of financing nec-
essary to cover the most adverse situation. The use of several cash budgets,
based on differing assumptions, also should give the financial manager a sense of
the riskiness of various alternatives. This sensitivity analysis,or “what if”
approach, is often used to analyze cash flows under a variety of circumstances.
Computers and electronic spreadsheets simplify the process of performing sensi-
tivity analysis.EXAMPLE Table 3.11 presents the summary of Coulson Industries’ cash budget prepared for
each month of concern using pessimistic, most likely, and optimistic estimates of
total cash receipts and disbursements. The most likely estimate is based on the
expected outcomes presented earlier.
During October, Coulson will, at worst, need a maximum of $15,000 of
financing and, at best, will have a $62,000 excess cash balance. During Novem-
ber, its financing requirement will be between $0 and $185,000, or it could
experience an excess cash balance of $5,000. The December projections show
maximum borrowing of $190,000 with a possible excess cash balance of