Cash Budget Model

Cash budget is a financial budget prepared to calculate the budgeted cash inflows and outflows during a period and the budgeted cash balance at the end of the period. Cash budget helps the managers to determine any excessive idle cash or cash shortage that is expected during the period. Such information helps the managers to plan accordingly. For example if any cash shortage in expected in future, the managers plan to change the credit policy or to borrow money and if excessive idle cash is expected, they plan to invest it or to use it for the repayment of loan.

Example

The Everglade Golden Years Company operates upscale retirement communities in certain parts of southern Florida. The company was founded in 1946, and enjoyed many successful years. Unfortunately, the past few years have been difficult ones. The demand for retirement community housing has been light, and Everglade has been unable to maintain full occupancy. However, the market has picked up recently and the future is looking brighter. Ever glade has recently broken ground for the construction of a new retirement community and has more new construction planned over the next 10 years.

Julie Lee is the CFO at Everglade, and has been trying to come to grips with the imminent cash flow problem. Her projection of cash flows for the next 10 years is shown in the table below. With less money currently coming in than would be provided by full occupancy, and with all the construction costs for the new retirement community, Everglade will have negative cash flow for the next few years. With only $1 million in cash reserves, it appears that Everglade will need to take out some loans in order to meet financial obligations. Also, to protect against uncertainty, company policy dictates maintaining a balance of at least $500,000 in cash reserves at all times.

The company’s bank has offered two types of loans to Everglade. The first is a ten year loan with interest only payments made annually and then the entire principal repaid in a single balloon payment after ten years. The interest rate on the ten year loan is a favorable 7 percent per year. The second option is a series of one year loans. These loans can be taken out each year as needed, but each must be repaid (with interest) the following year. The interest rate for these short term loans is projected to be 10 percent per year.

The spreadsheet might be organized as

• Decision Variables: Long Term Loan and Short Term Loans
• Objective Function: Maximize Ending Cash Balance
• Constraints: Ending Balance >= Minimum Balance

Key Relationships:

• LT Interest = – Long Term Interest Rate * Long Term Loan
• ST Interest = – Short Term Interest Rate * Prior Period Short Term Loan
• LT Payback = – Long Term Loan (Dec Var)
• ST Payback = – Prior Period Short Term Loan (Dec Var)
• Ending balance = Starting balance + Cash flow + Loans – Interest payments – Loan
• paybacks

Using the “most likely” cash flow in a deterministic model yields an ending balance of $2.92. Our optimal solution was determined assuming no deviation from the projected cash flows. Maintaining this solution for the long term loan we want to incorporate uncertainty for the cash flows and determine the likelihood of achieving a positive cash balance at the end of the ten years.

We will model the uncertainty of the cash flows for each year using a triangular distribution with parameters as shown below:

We will incorporate the uncertainty into our model, identify our forecast cell and run the simulation to answer the management question about our risk.

The result of our Monte Carlo analysis reveals there is about an 87% chance the ending balance will be positive: