Let us consider an example. Suppose the project costs amount to 700 million rubles. The expected revenues over the next 5 years are, respectively, 70 million rubles, 90 million rubles, 300 million rubles, 250 million rubles, and 300 million rubles.
We need to assess the economic feasibility of the project by its internal rate of return, given that the market rate of return is 12%.
Also consider the following alternatives (project costs indicated with a minus sign):
- (–600; 50; 100; 200; 200; 300)
- (–650; 90; 120; 200; 250; 250)
- (–500; 100; 100; 200; 250; 250)
To calculate the internal rate of return (IRR), the following function is used:
=IRR(Values; Guess)
In this case, the function for solving the problem uses only the argument Values, one of which must be negative (project costs). If the internal rate of return exceeds the market rate of return, the project is considered economically feasible. Otherwise, the project should be rejected.
The solution for this example is shown:
Formulas for calculation:
- In cell B84:
=IRR(B75:B80)
- In cell C84:
=IF(B84>B82,"The project is economically feasible","The project must be rejected")
Creating Scenarios
Let us consider this example for all combinations of initial data.
To create (or modify) a scenario, use the Scenario Manager command from the What-If Analysis list in the Data Tools group on the Data tab.
In the Scenario Manager dialog box, click Add to add a new scenario. In the Add Scenario window
enter a new name for the scenario and set the necessary parameters. After clicking OK, you can enter new values for the changing cells.
To save the results for the first scenario, it is not necessary to edit the cell values — simply click OK to confirm the default values and return to the Scenario Manager window.
Adding More Scenarios
To add new scenarios for this task, simply click Add again in the Scenario Manager window and repeat the above steps, changing the values of the initial data.
In Fig. :
- Scenario Turnover_Speed_1 corresponds to data (–700; 70; 90; 300; 250; 300)
- Scenario Turnover_Speed_2 corresponds to data (–600; 50; 100; 200; 200; 300)
- Scenario Turnover_Speed_3 corresponds to data (–650; 90; 120; 200; 250; 250)
- Scenario Turnover_Speed_4 corresponds to data (–500; 100; 100; 200; 250; 250)
By clicking Show, you can view the calculation results on the worksheet for the corresponding set of initial values.
Generating the Scenario Report
To obtain a summary report for all added scenarios, click Summary in the Scenario Manager window.
In the Scenario Summary dialog box
, select the desired type of report and specify the cells that contain the resulting functions.
When you click OK, a report for the scenarios is generated on the corresponding worksheet.