Scenario Analysis
In the previous sections you have learn about how to model feasibility study for a public transportation system of bus system and how to perform an economic evaluation which include financial analysis computation . The question remain on how sensitive this system against externalities such as change of demand.
In this section we will try to answer those questions using Scenario analysis of MS Excel. People often called this Scenario tool as What-if Analysis. What if analysis or scenario analysis is sometimes also called deterministic simulation. It simulates the system to find possible effect of some possible scenarios. Using the Scenario tool, you can simulate some scenario values of input and get the results without affecting the worksheet that you have built.
Remember that Load factor represent the average fluctuation of demand for the particular bus route. Higher load factor indicate that many people are using the bus within the trip that go up and go down before the destination station. Small load factor indicate that less passenger using that route and most of them using the bus straight from origin to destination.
The present value of the load factor is 0.8 for minivan and 0.7 for the minibus. We want to know what will happen if the load factor varies from 0.5 to 1.2. Here is the procedure step by step
1. Use menu Tools > Scenarios
2. Scenario dialog pop up and click Add
3. Click Scenario name LF=0.5, then Changing cell point to the Load Factors, then click OK button
4. In the next dialog, change the value of both cells into 0.5 and then click Add button.
5. The same scenario dialog reveal again and you type scenario name LF=0.6 and click OK button
6. In the next dialog, change the value of both cells into 0.6 and then click Add button.
7. Repeat this process for Load factor value of 0.7, 0.8, 0.9, 1.0, 1.1 until the value of Load factor is 1.2. This last time you don't click the Add button. Instead, you click OK button.
8. In the next dialog, click Summary button.
9. In the Scenario Summary dialog, select the output of the computation (in this case the NPV, BCR and IRR) and click OK
The result of Scenario Analysis is shown below (after some change in the title of the variables). It show that if the Load Factor is below 0.8 (the exact LF boundary is to be determined using Goal Seek below), the minivan is not feasible, while minibus Load Factor boundary is somewhat lower than 0.6. This result means that the minibus is more robust against fluctuation of demand than the minivan.
To determine the exact boundaries of the Load Factor that make the NPV of minibus or minivan zero, we can use Goal Seek tool.
1. If you are still in the Scenario Summary sheet, go back to the Bus Sheet.
2. In MS Excel menu click Tools > Goal Seek
3. In the Goal seek dialog, click set cell and point to NPV of minivan address, then use Tab key and type 0 (zero) in To Value text box. After that, use Tab key and click the address of Load Factor of Minivan, then click OK
4. It shows the boundary of Load factor for Minivan is 0.752. Record this number using your pen and pencil and click Cancel button.
5. Use Goal seek again for the minibus with exactly the same step as step no 3 and 4 above, but alter the address to point to the NPV of minibus and changing cell of the load factor of Minibus. This time we get the boundary of Load Factor is 0.560. Don't forget to cancel the goal seek to avoid permanent change.
MS Excel file companion of this tutorial can be downloaded here .
This tutorial is copyrighted .
Preferable reference for this tutorial is
Teknomo, Kardi (2006) Tutorial on Feasibility Study. http://people.revoledu.com/kardi/tutorial/What-If-Analysis/index.html