EXAMPLES OF PERFORMING SIMULATIONS ON SPREADSHEETS WITH ANALYTIC SOLVER PLATFORM:CASH FLOW MANAGEMENT

CASH FLOW MANAGEMENT

Many applications of simulation involve scenarios that evolve far into the future. Since nobody can predict the future with certainty, simulation is needed to take future uncertain- ties into account when making decisions. For example, businesses typically have great uncertainty about what their future cash flows will be. An attempt often is made to predict these future cash flows as a first step toward making decisions about what should be done (e.g., arranging for loans) to meet cash flow needs. However, effective cash management requires going a step further to consider the effect of the uncertainty in the future cash flows. This is where simulation comes in, with uncertain variable cells being used for the cash flows in various future periods. This process is illustrated by the following example.

The Everglade Cash Flow Management Problem

The case study analyzed in Chap. 21 involves the Everglade Golden Years Company (which operates upscale retirement communities) and its efforts to manage its cash flow problems. In particular, because of both a temporary decline in business and some current or future construction costs, the company is facing some negative cash flows in the next few years as well as in some more distant years. As first provided in Table 21.1, Table 28.1 shows the projected net cash flows over the next 10 years (2014 to 2023). The company has some new retirement communities opening within the 10 years, so it is anticipated (or at least hoped) that a large positive cash flow will occur in 2023. Therefore, the problem confronting Everglade management is how to best arrange Everglade’s financing to tide the company over until its investments in new retirement communities can start to pay off.

Chapter 21 describes how a decision was made to combine taking a long-term (10- year) loan now (the beginning of 2014) and a series of short-term (1-year) loans as needed to maintain a positive cash balance of at least $500,000 (as dictated by company policy) throughout the 10 years. Assuming no deviation from the projected cash flows shown in Table 28.1, linear programming was used to optimize the size of both the long-term loan and the short-term loans so as to maximize the company’s cash balance at the beginning of 2024 when all of the loans have been paid off. Figure 21.5 in Chap. 21 shows the complete spreadsheet model after using Solver to obtain the optimal solution. For your convenience, Figure 21.5 is repeated here as Fig. 28.10. The changing cells, LTLoan (D11) and STLoan (E11:E20), give the sizes of the long-term loan and the short-term loans at the beginning of the various years. The objective cell EndBalance (J21) indicates that the resulting cash bal- ance at the end of the 10 years (the beginning of 2024) would be $5.39 million. Since this is the cell that is being maximized, any other plan for the sizes of the loans would result in a smaller cash balance at the end of the 10 years.

Obtaining the “optimal” financing plan presented in Fig. 28.10 is an excellent first step in developing a final plan. However, the drawback of the spreadsheet model in Fig.

28.10 is that it makes no allowance for the inevitable deviations from the projected cash flows shown in Table 28.1. The actual cash flow for the first year (2014) probably will turn out to be quite close to the projection. However, it is difficult to predict the cash flows in even the second and third years with much accuracy, let alone up to 10 years into the future. Simulation is needed to assess the effect of these uncertainties.

The spreadsheet model that used linear programming in Chap. 21 (Fig. 21.5) to analyze the Everglade Golden Years Company cash flow management problem without taking the uncertainty in future cash flows into account.

Solver Options:

Make Variables Nonnegative Solving Method: Simplex LP

A Spreadsheet Model for Applying Simulation

Figure 28.11 shows the modification of the spreadsheet model in Fig. 28.10 that is needed to apply simulation. One key difference is that the constants in CashFlow (C11:C20) in Fig. 28.10 have turned into random inputs in CashFlow (F12:F21) in Fig. 28.11. Thus, the latter cells, CashFlow (F12:F21), are uncertain variable cells. (The numbers appearing in these cells are just one possible random outcome—the last trial of the simulation run.) As indicated in cells D9:E9, the assumption has been made that each of the cash flows has a triangular distribution. Estimates have been made of the three parameters of this distribu- tion (min, likely, and max) for each of the years, as presented in cells C12:E21.

The number 4.65 entered into LTLoan (G12) is the size of the long-term loan (in mil- lions of dollars) that was obtained in Fig. 28.10. However, because of the variability in the cash flows, it no longer makes sense to lock in the sizes of the short-term loans that were obtained in STLoan (E11:E20) in Fig. 28.10. It is better to be flexible and adjust these sizes based on the actual cash flows that occur in the preceding years. If the balance at the begin- ning of a year (as calculated in BalanceBeforeSTLoan [L12:L22]) already exceeds the required minimum balance of $0.50 million, then there is no need to take any short-term loan at that point. However, if the balance is not this large, then a sufficiently large short- term loan should be taken to bring the balance up to $0.50 million. This is what is done by the equations entered into STLoan (M12:M22) that are shown at the bottom of Fig. 28.11.

The objective cell EndBalance (J21) in Fig. 28.10 becomes the results cell EndBalance (N22) in Fig. 28.11. A statistic cell, MeanEndBalance (N24), is defined to determine the mean value of EndBalance for the simulation run. On any trial of the simulation, if the sim- ulated cash flows in CashFlow (F12:F21) in Fig. 28.11 are more favorable than the pro- jected cash flows given in Table 28.1 (as is the case for the current numbers in Fig. 28.11), then EndBalance (N22) in Fig. 28.11 would be larger than EndBalance (J21) in Fig. 28.10. However, if the simulated cash flows are less favorable than the projections, then EndBal- ance (N22) in Fig. 28.11 might even be a negative number. For example, if all the simulated cash flows are close to the corresponding minimum values given in cells C12:C21, then the required short-term loans will become so large that paying off the last one at the beginning of 2024 (along with paying off the long-term loan then) will result in a very large negative number in EndBalance (N22). This would spell serious trouble for the company. Simulation will reveal the relative likelihood of this occurring versus a favorable outcome.

Here is a summary of the key cells in this model:

Uncertain variable cells: CashFlow (F12:F21)

Results cell: EndBalance (N22)

Statistic cell: MeanEndBalance (N24)

(See Sec. 20.6 for the details regarding how to define uncertain variable cells, results cells, and statistic cells.)

The Simulation Results

Figure 28.12 shows the results from applying simulation with 1,000 trials. Because Ever- glade management is particularly interested in learning how likely it is that the current financing plan would result in a positive cash balance at the end of the 10 years, the num- ber 0 has been entered into the Lower Cutoff box in the statistics table. The Likelihood box then indicates that over 95 percent of the trials resulted in a positive cash balance at the end. Furthermore, the frequency chart shows that many of these positive cash balances are reasonably large, with many exceeding $10 million. The overall mean is $9.18 million.

On the other hand, it is worrisome that nearly 5 percent of the trials resulted in a neg- ative cash balance at the end. Although huge losses were rare, some of these negative cash balances were quite significant, ranging up to $5 million.

Conclusions

Everglade management is pleased that the simulation results indicate that the proposed financing plan is likely to lead to a favorable outcome at the end of the 10 years. At the same time, management feels that it would be prudent to take steps to reduce the 5 percent chance of an unfavorable outcome.

One possibility would be to increase the size of the long-term loan, since this would reduce the sizes of the higher interest short-term loans that would be needed in the later years if the cash flows are not as good as currently projected. This possibility is investigated in Problem 28.9.

The scenarios that would lead to a negative cash balance at the end of the 10 years are those where the company’s retirement communities fail to achieve full occupancy because of overestimating the demand for this service. Therefore, Everglade management concludes that it should take a more cautious approach in moving forward with its current plans to build more retirement communities over the next 10 years. In each case, the final decisions regarding the start date for construction and the size of the retirement community should be made only after obtaining and carefully assessing a detailed forecast of the trends in the demand for this service.

After adopting this policy, Everglade management approves the financing plan that is incorporated into the spreadsheet model in Fig. 28.11. In particular, a 10-year loan of $4.65 million will be taken now (the beginning of 2014). In addition, a one-year loan will be taken at the beginning of each year from 2014 to 2023 if it is needed to bring the cash balance for that year up to the level of $500,000 required by company policy.