Example: Running Sensitivity Analysis Using a Macro

The file Ch14.1.ProfitSensitivity.xlsm contains an example of using a macro to run a sensitivity analysis, by stepping through a set of input values, recalculating the model and recording the results for the output(s). The model is similar to that used in Chapter 13, where it was used to demonstrate the application of GoalSeek to find the breakeven sales volume of a business for a given set of input assumptions, including the sales price. In this example, we are not (yet) using GoalSeek to find the breakeven volume; rather (as a first step), we aim to calculate the profit for various values of the input price (shown in cells E3:E11), and to record the resulting profit figures (in cells F3:F11). (Thus, at this stage, without the need for the additional GoalSeek procedure such analysis could be performed using a DataTable.) In order to automate the use of various input prices, the cell references that are required by the macro are given named ranges. Figure 14.1 shows the model prior to the running of the macro (including the button to run it), and Figure 14.2 shows the model once the macro has been run.

The core elements of the VBA code are:

N = Range("PriceHeader").CurrentRegion.Rows.Count - 1
For i = 1 To N
 Range("Price") = Range("PriceHeader").Offset(i, 0)
 Application.Calculate
 Range("ProfitHeader").Offset(i, 0) = Range("Profit")
Next i

(As covered in Part VI, in practice more sophistication (and best practices) could be built into this procedure, including clearing the results range at the beginning of each run of the macro, and the use of full and explicit referencing. One could also track the initial state before the macro is run (i.e. the value used for the price) and reset the model to this state afterwards, as well as perhaps switching off screen-updating, and so on.)

Example: Running Scenarios Using a Macro

The file Ch14.2.RevScenario.xlsm contains an example of the use of a macro to run scenario analysis. The macro simply runs through a set of integers, and uses a lookup function (such as CHOOSE) to select the appropriate data for each scenario. All cells or ranges that need referencing from the VBA code are given Excel named ranges as part of the process of writing the macro. Figure 14.3 shows the results of running the macro, whose key elements are:

For i = 1 To 3
 Range("ScenarioNo") = i
 Application.Calculate
 Range("ResultsHeader").Offset(i, 0) = Range("Output")
Next i

Example: Using a Macro to Run Breakeven Analysis with GoalSeek

The file Ch14.3.GoalSeekMacro.Breakevenanalysis.xlsm contains an example of the automation of GoalSeek within a macro. It is similar to the first example in the chapter, with the additional step added in which (for each value of the price) GoalSeek is used to find the breakeven volume. The macro was created by simply recording the GoalSeek process once, and placing this within the loop which steps through the set of values that are to be used for the prices. Figure 14.4 shows the results of running the macro.

Note that we are using the setting-to-zero approach described earlier. The core aspects of the VBA code are:

N = Range("PriceHeader").CurrentRegion.Rows.Count - 1
For i = 1 To N
Range("Price") = Range("PriceHeader").Offset(i, 0)
Range("DiffToTarget").GoalSeek Goal:=0, ChangingCell:=Range("Volume")
Range("BEvenVolHeader").Offset(i, 0) = Range("Volume")
Next i

Example: Using Solver Within a Macro to Create a Frontier of Optimum Solutions

The use of Solver within VBA code is in many ways similar to that of GoalSeek: in principle, the most effective method is to record a macro of the process, ensure that all Excel ranges are referred to with named ranges and place the code within an appropriate loop (which is used to govern the way that input values are changed before Solver is applied). However, there are several points worth noting:

• One first needs to make a reference to the add-in within the Visual Basic Editor. This can be achieved using Tools/References and checking the relevant box for the Solver. Of course, Solver needs to be have been installed on the computer (as described in Chapter 13). This can therefore be inconvenient if the optimisation is to be run by someone who is not familiar with this process (e.g. if the file is sent to someone else by e-mail, who will need to implement this procedure).
• The recording of a macro does not capture the full syntax necessary to place the code within a loop. To automate the process requires one to add the True statement after SolverSolve in the code. This captures the closing of the message box once Solver has finished running, which is otherwise not captured by the recording process.

The file Ch14.4.SolverAssetSale.OneStep.xlsm contains the example from Chapter 13, in which the maximum sales proceeds were realised for a specific capital gains threshold. It is adapted to calculate the same figure in the case that a range of threshold values is desired to be run. The following shows the core of the required code, using the (essentially self-explanatory) named ranges created in the file, and noting the points above about referencing and the addition of the True statement:

N = Range("ResultsHeader").CurrentRegion.Rows.Count – 1

For i = 1 To N
'CHANGE CGT Threshold Values
Range("CGTThreshold").Value = Range("CGTThresholdsHeader").Offset(i, 0).Value
Application.Calculate

'RUN SOLVER
 SolverOk SetCell:=Range("ValueRealised"), MaxMinVal:=1, ValueOf:=0, _
 ByChange:=Range("TrialValues"), _
 Engine:=1, EngineDesc:="GRG Nonlinear"
 SolverSolve True

'RECORD RESULTS
 Range("ResultsHeader").Cells(1, 1).Offset(i, 0).Value = Range("ValueRealised").Value
 Range("ResultsHeader").Cells(1, 1).Offset(i, 1).Value = Range("Gains").Value
Next i

Figure 14.5 shows the completed model, including a table of values showing the maximum proceeds that are realisable for various capital gains tax threshold levels.