DATA TABLE S

The model so far has produced a single point answer: the capital and cash flows discounted at 9.5% result in a net present value. The model would be more powerful if you could display the net present values for a range of discount rates simultaneously on the same sheet. This can be achieved by the array function TABLE, which can be found under the toolbar Data, Table.

The steps are:

•

Set up a grid with an interval as an input.

•

Enter the function.

•

Graph the results.

The dynamic graph has been moved down on the Data_Tables sheet to make room for the data or sensitivity table (see Figure 3.43). The grid consists of an interval and then a row of discount rates in line 29. The 9.5% is an absolute and is marked in blue as an input. The cells on either side are plus or minus the interval. Cell B30 looks up the answer in cell C22. When complete the data table will show the net present value at each of these interest rates.

The next stage is to highlight the grid area and enter the data table (see Figure 3.44).

Cell C81 in this interim version is the periodic discount rate derived from the combo box. Excel inserts the figures in the grid and the answer of 7,502.58 at 9.5% is visible. This shows the sensitivity of the final answer to changes in the discount rate.

Table is an array function, which means that you cannot alter individ- ual cells within the group. If you try to alter any of cells C31 to H31, you will get an error message. Similarly, if you copy a data table from one sheet to another, only the values will be pasted. You have to highlight the grid and re-input the table on the new sheet.

Data tables

Rather than create a further chart, this example uses the existing ‘dynamic graph’ and increases the inputs to line 31 (see Figure 3.45). Line 31 is simply a variance to the original answer. The Offset function merely requires the rows to index down by and so no other programming changes are necessary.

Data tables can be single dimensional as above or two dimensional. There are often two dominant variables in a model and this approach allows you to ‘flex’ the variables. It is important to use a grid to set out the table and best not to hard code the interval. This means that you can always change the interval quickly and see on any printouts the interval used. In addition, it is best practice to input the current value for the vari- able in the middle so you can see the values on either side. Some applications with the book then use a macro to update the input values on the table by copying down the values from the inputs area.

19. SCENARIOS

If there were several versions of this simple example project, producing multiple spreadsheets would be wasteful and potentially could introduce errors. Similar spreadsheets tend to diverge over time and be more diffi- cult to maintain. Scenarios provide the facility to ‘remember’ inputs so that you can load them at any time. As an added bonus, Excel will pro- duce a management report based on the scenarios.

Scenarios are accessed using Tools, Scenarios, Add(see Figure 3.46).

There are saved cases on the Scenarios sheet.

You can select multiple cells by separating them with a comma. When you have selected them, Excel allows you to review the values in each of

the cells before saving them. Press Showto display the scenario.

There are further examples on the sheet, named best and worst case. These vary only the capital value and periodic cash flow. If you press

Tools, Scenarios, Summaryand select cells C22 and E22 as the result cells, Excel produces the management report shown in Figure 3.47.

It is always best to start from a Base Case and vary these inputs rather than developing further scenarios. Here Worst and Best Case vary only two cells from the original scenario. It is therefore clearer what changes from the initial estimate.

Scenarios

Only one cell is named on this sheet: this is Scenarios!$C$22, which shows as a name in line 17 rather than a cell reference. This is a static or values only report which will not change if the underlying values change. If the model changes, you have to run the report again. It also acts like an audit trail since you could print this out and keep it in a file to show what inputs produce the range of results.

20. GOALSEEK

Data Tables and Scenarios produce management information and make the model more powerful while reducing the amount of necessary code to derive the results. Goalseek assists with ‘what-if’ by working back from an answer and changing one variable. Suppose you wanted to know what periodic cash flow produces a net present value of 8,000. Rather than entering numbers into cell C6, you could go to Tools, Goalseek(see Figure 3.48).

The parameters are set cell X to Y by changing Z. This is changing only one parameter at a time by working backward from the answer and converging on the correct input. In this example on the Goalseek sheet, Excel will set the answer to 8,000 by varying the periodic cash flow. The answer is 28129.5568837666. Note that there are no constraints to the Fig 3.47

calculation and Excel will display an error if it does not find an answer within a specified time or number of attempts. There is no possibility also of using constraints such as forcing a positive answer in the variable. For problems with rules and constraints, you need Solver.

21. SOLVER

Solver is a more advanced form of Goalseek since you can optimize or find values by changing multiple cells subject to constraints. Solver is an add-in to Excel, which has to be installed at the time of installation. Go to Tools,

Solver and if Solver is not there, check that Solver is ticked at Tools Add-Ins. If you still cannot find Solver, re-install Excel with this option.

Solver makes it possible to work back from an answer, which can be:

•

minimum;

•

maximum;

•

particular value.

In this example, management wants to know if a net present value of 8,000 is possible if:

•

capital value is greater than or equal to 98,500;

•

periodic cash flow is less than or equal to 28,500.

Solver

When you press Solve, Excel sets up the problem and tries to solve it. If it cannot, then an error message will be displayed. Here, Solver finds a solution as 99968.3995017415 and 28121.3262540137 within the para- meters set above. Usually it is best to get a problem to work and then tighten the parameters. This allows you to see which of your constraints are not allowing Solver to converge on a solution. Solver also produces a management report as shown in Figure 3.50 when you select the option on the Answer dialog.

Fig 3.49

The management report shows the amount of ‘slack’ in the solution. 1,968.40 of 98,000 is not needed as the model stopped at 99,968.40. It is also beneficial to save each of the answers as a scenario since there are now five answers with differing inputs. You can show all the scenarios on the Solver sheet (see Figure 3.51).

In document Mastering Financial Modelling.pdf (Page 67-74)