Using Investment Analysis in Rational Focal Point: Part 2. Build an advanced financial model and compare results for two projects

This article describes how to build a financial model for a car manufacturing project and then reuse the variables for another project and compare the financial results.

Swathi Jain (swathi.jain@in.ibm.com), User Assistance Team Lead, IBM

author photoSwathi leads the Rational Focal Point User Assistance team and is also an information architect. She collaborates with various teams to deliver high-quality documentation for the products and handles the multimedia content to help promote products and educate customers.



Veena K. Omprakash (veena.kumari@in.ibm.com), Information Developer, IBM

author photoVeena is an information developer for Rational Focal Point, working at the IBM India Software Lab. She has worked in this role for other Rational products and has contributed to developing various user assistance deliverables.



18 December 2012

Scenario

In this second part of a two-part article, we discuss building an advanced financial model for analyzing investment in producing different hybrid cars projects. A car manufacturing company is considering manufacturing BioCar1 and BioCar2, with the technology that is employed as the difference.

BioCar1
This project is to manufacture low-cost cars that are targeted at low and mid-segment market. These cars would be fuel-efficient and have low input costs and quicker delivery time.
 
BioCar2
This project is to manufacture high-end cars that are more efficient than BioCar1, but they would also sell for more. These cars would have higher input costs targeted at a high-end market, and the sales would be considerably lower than BioCar1.
 

The management now needs to determine the potential input costs, output costs, delivery time and quantity, sales, and services revenue for both of the projects. With all of this data, they also need to analyze which project would have better ROI, NPV, and find out which project would be feasible and provide optimal benefits over a period of time.

First, they configure the time grid attribute and the related financial metric attributes, and then they build the model to calculate the development cost, maintenance cost, and the sales revenue.

Note:
None of the data is precise, including the type of streams, variables, and their values. The values might not resemble the real-world values as used in the automobile industry. The values are used as an example with the main intention of showing the usage of Investment analysis by using Rational Focal Point.


Configure the model

Several key computations must be considered when you are configuring the financial model:

Costs
The expected costs to complete the project and expected costs after delivery
 
Benefits
Expected revenue after delivery and expected revenue and cost savings from reuse
 
Risk
Combined uncertainty in estimated costs and benefits
 

Determine the monetary and non-monetary streams

To build a financial model in any scenario, you must determine all the possible cost and revenue streams along with the associated risks. For the model in this example, you will consider the following cost, benefit, and non-monetary streams:

Cost streams

Engineering
This stream would include the cost of engineers and the number of engineers that are required in the project.
 
Manufacturing
This stream would include the assembly costs and bill of materials.
 
Services and warranty
This stream would include the warranty costs that are borne by the company for the delivered cars.
 

Benefit streams

Sales revenue
This stream would include the total sales of the cars and the revenue generated from sales.
 
Services revenue
This stream would include the post-warranty services rendered by the company for the customers, and the main income would be from the sale of car parts.
 

Non-monetary streams

No. of engineers
This stream includes the projection of the resource requirement for the cars project.
 
Quantity
This variable is the projection of the cars that would be manufactured.
 

Configure the time grid attributes and output attributes

In this lesson, we configure a time grid attribute and output attributes to retrieve the investment analysis results.

  1. Open an existing workspace or create a new one.
  2. To create a module, click Configure > Module.
  3. Click Add Module, and name the module as BioCars.
  4. To add attributes to the module, click Configure > Attributes, and select the BioCars module.
  5. Create two Date types of attributes, and name them Project Start Date and Project End Date.
  6. Create an attribute of type time grid, and configure that attribute:
    1. Type Project Specification as the name for the time grid attribute.
    2. Specify bcar as the alias name for the time grid attribute.
       
      Important:
      This alias name must be used for prefixes when you create the attributes to retrieve the financial analysis results.
    3. Select the Allow Investment Analysis check box.
Figure 1. Time grid sheets
Time grid sheets: High, Likely, Low, Actual
  1. Add the sheets to store the high, likely, low, and actual value estimates.
    1. Click Add Sheet, and create four sheets to set the appropriate scenario type: High, Likely, Low, and Actual.
    2. From the Time Interval list, select Year for all the sheets.
    3. Select the Date attribute check box, and select Project Start Date and Project End Date for all the sheets.
  2. Add the cost and benefit streams for the project.
    1. Click Add Row, and add the following cost, benefit, and non-monetary streams, as shown in the figure.
       
      Note:

      All cost streams must have the same units. The benefits can be monetary or non-monetary. However, the monetary benefit must have the same unit as the cost.
    2. In the Time interval field, select Year.
    3. In the Display type field, select Stream.
    4. Click OK to save the time grid.
Figure 2. Time grid streams
time grid rows: Cost, benefit, variables

You can retrieve the results from the financial analysis of the model by creating output attributes in the same module that contains the time grid. To return the results, the alias of the time grid is used as a prefix for the alias of each result attributes.

  1. To retrieve the financial model results, create the result attributes as listed in the table:
Table 1. Output attributes
Attribute typeAttribute nameAlias name
Time Grid Project Specification bcar
Matrix NPV Distribution bcar__NPV
Float NPV Mean bcar__NPVMean
Float NPV Standard Deviation bcar__NPVStddev
Integer ROI bcar__ROI
Float IRR bcar__IRR
Integer Payback Period bcar__ paybackPeriod
Matrix ROI to Date bcar__ ROIToDate
Matrix ROI to Go bcar__ ROIToGo
Matrix Payback period distribution bcar__ paybackPeriodDist

Build a financial model for BioCar1 project

After setting up the attributes required for the projects, you are ready to build the financial model.

Create the project

In the BioCar module, create an element for the BioCar1 project:

  1. To add elements to the BioCar module, click Modules > BioCars.
  2. Click Add Element.
  3. Specify the element name as BioCar1, and provide a description for the type of project.
  4. Click OK to save the changes.
  5. Click one of the stream names to open the Investment Analysis mode.

Calculate the engineering cost

To calculate the engineering cost, you need to determine the number of engineers and the investment for each engineer.

Estimate the number of engineers

First, you need to estimate the number of engineers that are required over a period of time. This article is based on the following assumptions:

  • The year of delivery for the BioCar1 project is 2015.
  • The period from 2012 to 2015 will be the time invested on research and engineering effort, and the number of engineers required will be considerably fewer.
  • The period from 2015 to 2024 will be the time when the manufacturing of BioCar1will rise, and the number of engineers required will be high.
  • The period from 2024 to 2032 will be the time when the manufacturing of the car will decline gradually, and the number of engineers that are required will also decline.

To estimate the number of engineers that is required throughout the project phase:

  1. In the BioCar1 element, click to open the No. of engineers stream.
  2. Click the Estimates tab. Use the graphical editor or grid, and project your assumptions of the number of developers that will be required over time.
Figure 3. Number of engineers for BioCar1 project
graph and table of estimates

Tip:
When you plot the estimates in the grid, clear the auto recalc check box in the Variables tab. When the auto recalc option in the Variables tab is On, any change in data will trigger a recalculation of all the dependant calculator tapes. The auto recalc option in the Calculator tab will rerun only the current tape in the tab, thus bypassing the need to click the Run Simulation button repeatedly.

  1. Specify the cost of each engineer. Click the Variables tab and create a variable for amount invested in each engineer, and specify the assumed values:
Table 2. Variable for cost per engineer
Variable nameTypeValue
CostPerEngineer Normal 250000, 50000

Note:
The first value of 250,000 in the Normal type represents a mean value, and the second value of 50,000 is the standard deviation value.

  1. Click Save.

Calculate the total engineering cost

You need to assess the likely engineering cost by taking into account the amount invested per engineer and the number of engineers that are required over that period of time. To calculate the cost, use the calculator tape feature in Focal Point.

  1. Select the Engineering cost stream.
  2. To create a calculator tape, click the Calculator tab. Create a tape called Engineering cost.
  3. To add a variable, click Add Variable, and name the variable for the total engineering cost with the following parameters:
Table 3. Variable for engineering cost
Variable nameTypeValue
TotalEngineeringCost Formula CostPerEngineer*'No of engineers'[PERIOD]
  1. Select the Auto recalc check box.
  2. To run the simulation, click Run Simulation. In Rational Focal Point, the financial metrics are calculated by using the Monte Carlo simulation.
  3. To see the engineering cost in 2012, click Apply simulation to 2012. The cost is plotted in the graph for the year 2012. Similarly, to see the engineering cost for each year, move the slider to each year, and click Apply simulation to YYYY.
Figure 4. Engineering cost
Simulation result with two graphs

In Figure 4, TEC is TotalEngineeringCost, and CE is CostPerEngineer.

  1. To reuse this tape for the new elements that you create, you must save the tape as a default tape. To save the tape to default tapes list, and click Tapes > Save tape to default tapes. Ensure that visibility is set to local.
  2. Click Save.

Important:
In this tutorial, all of the tapes that are created in the BioCar1 project will be reused in the BioCar2 project. Therefore, all of the tapes that are created in the BioCar1 project are saved as default tapes.

Calculate the total manufacturing cost

You need to determine the total manufacturing cost that includes the cumulative total on the bill of materials and the cost of assembling the car. For this calculation, the number of cars that will be manufactured must be assessed.

Estimate the quantity of cars to be manufactured

To estimate the number of cars to be manufactured, follow these steps:

  1. Select the Quantity stream.
  2. Click the Estimates tab.
  3. Use the graphical editor or grid and project your assumptions of the quantity of BioCar1 to be manufactured over time.

After you have ascertained the quantity of cars to be manufactured, you can calculate the manufacturing cost.

Figure 5. Quantity of BioCar1
Simulation result with table and graph

Calculate the total manufacturing cost

To calculate the manufacturing cost, you must specify the assembly costs and bill of materials per car.

  1. Select the Manufacturing cost stream.
  2. Click the Variables tab, create the variables for assembly cost and bill of material, and specify the assumed values:
Table 4. Manufacturing cost variables
Variable nameTypeValue
BOMPerCar Triangular 1250, 1500, 2000
AssemblyPricePerCar Triangular 800, 1000, 1300

The three values in the Triangular type represent the low, likely, and high estimates, respectively.

  1. Click the Calculator tab, and create a calculator tape called Manufacturing.
  2. Create the variables shown in Table 5, and specify the formula.
Table 5. Manufacturing cost
Variable nameTypeValue
BOM Formula BOMPerCar*'Quantity'[PERIOD]
Assembly Formula AssemblyPricePerCar*'Quantity'[PERIOD]
Manufacturing Formula BOM+Assembly
  1. Run the simulation.
  2. Move to the required time period and see the simulation result.

In Figure 6, BOMP is BOMPerCar, ASMB is Assembly, and MFG is Manufacturing.

Figure 6. BioCar1 manufacturing stream
Simulation result shows two graphs
  1. Save the tape as default tape. Click Tapes > Save to Default Tapes.
  2. Click Save.

Calculate the warranty cost

For this article, we have assumed that the company incurs a warranty cost for three years after the sale of each car.

  1. Select the Warranty stream.
  2. Click the Variables tab. Create these variables and specify the estimates:
Table 6. Variables for warranty cost
Variable nameTypeValue
War1 Triangular 300, 400, 700
War2 Triangular 400, 500, 1000
War3 Triangular 600, 700, 1120
  1. Create a calculator tape called Warranty.
  2. Create these variables, and specify the formula:
Table 7. Warranty cost
Variable nameTypeValue
Warranty1 Formula War1*'Quantity'[PERIOD]
Warranty2 Formula War2*'Quantity'[(PERIOD-1)]
Warranty3 Formula War3*'Quantity'[(PERIOD-2)]
Warranty Formula Warranty1+Warranty2+Warranty3
  1. Run the simulation.
  2. Move to the required time period and see the simulation result.
Figure 7. Warranty stream for BioCar1
Simulation result and graph for the warranty cost
  1. Save the tape as the default by clicking Tapes > Save to Default Tapes.

You have now calculated the amount that the company needs to invest in the engineering, manufacturing, and warranties. Next, estimate the sales and service revenue.

Calculate the sales revenue

For the project, to generate revenue on the sale of each car, a profit margin must be set. To calculate the sales revenue:

  1. Select the Sales stream.
  2. Click the Variables tab. Create a variable for the profit margin on the sale of each car and specify the estimates:
Table 8. Variable for sales revenue
Variable nameTypeValue
Markup Triangular 1.5, 2.0, 2.5
  1. Create a calculator tape called Sales.
  2. Create the variables, and specify the formula:
Table 9. Sales revenue
Variable nameTypeValue
Sales Formula Markup*'Manufacturing'[PERIOD]
  1. Run the simulation.
  2. Move to the required time period to see the simulation result.
Figure 8. Sales revenue stream of BioCar1
Simulation result, revenue graph
  1. Save the tape as the defaul. Click Tapes > Save to Default Tapes.

Calculate the services revenue

This stream includes the post-warranty services that are rendered by the company for the customers. The revenue generation would be through the sales of the parts for the cars.

  1. Select the Services stream.
  2. Click the Variables tab, and create a variable for the sale of parts of the car and specify the estimates:
Table 10. Variable for services revenue
Variable nameTypeValue
Parts Triangular 1250, 1500, 1750
  1. Create a calculator tape called Services.
  2. Create the variables, and specify the formula:
Table 11. Services revenue
Variable nameTypeValue
Services Formula Parts*'Quantity'[(PERIOD-3)]
  1. Run the simulation.
  2. Move to the required time period to see the simulation result.
Figure 9. Services revenue stream of BioCar1
graph, simulation result
  1. Save the tape as the default. Click Tapes > Save to Default Tapes.
  2. To save the variables that you created to be reused in the new elements that you create, save the variables as the default set of variables.
  3. Click the Variables tab, and click Save as default.

Now, you have built a financial model for BioCar 1 using these variables and values.

Figure 10: Variables of BioCar1
Variables are used to calculate the cost & benefit for BioCar1 project

Similarly, you need to build a model for BioCar2.


Build a financial model for BioCar2 project

The BioCar2 project is built the same way as BioCar1 project. You will reuse the same calculator tapes and the variables that you created for BioCar1 project and update the values.

These are the differences in building this mode:

  • Change in the project duration, the project life is from 2012 to 2037.
  • Change in the delivery time to 2017.
  • Change in the value of the variables in the Variables tab.
  • Projection of the number of engineers.
  • Projection in the quantity.

To build the BioCar2 project:

  1. Create a new element called BioCar2.
  2. Launch Investment Analysis mode.
  3. Select the No of Engineers stream, and click the Estimates tab. Use the graphical editor or grid and project your assumptions of the number of engineers that are required over time.
  4. Select the Quantity stream, and click the Estimates tab. Use the graphical editor or grid, and project your assumptions of the quantity to be manufactured over time.
  5. Click the Variables tab, and change the value of the variable. The values for the variables for BioCar2 project are shown in the image.
Figure 11. Variable tab for BioCar2
Variables to calculate the cost and benefit
  1. Reuse the saved default tapes to assess the cost and revenue streams for BioCar2 project.
    1. To calculate the engineering cost, select the Engineering stream.
    2. Click the Calculator tab, and click Tapes > Load Tape.
    3. Select the Engineering tape.
    4. Run the simulation.
    5. Move the slider to the required time period and click Apply simulation to YYYY.
    6. Click Tapes > Save Tape. The tape is saved locally to this element.
    7. Similarly, calculate various other costs and benefit values for this project.

Table 2 shows the stream and tape to select.

Table 10. Stream and tape for BioCar2
CalculationStreamTape
Manufacturing cost Manufacturing Manufacturing
Warranty cost Warranty Warranty
Sales revenue Sales Sales
Services revenue Services Services
  1. Click Save to save the investment analysis for BioCar2 project.

Analyze the results for the BioCars project

Analyze the output variables for the BioCar1 project, including NPV, ROI, IRR, and payback period. Figure 12 shows a sample screen capture of results.

Analyzing the output attributes

Output attributes for BioCar1 project

Figure 12. Output attributes for BioCar1
graph and amounts for each metric

The results for BioCar1 indicate a good outcome:

  • The rate of interest is 35% of investment.
  • The payback period of 4 indicates that the project will recover its initial investment in 4 years and will be profitable after that.
  • The internal rate of return (IRR) of 67.46 is the discount rate at which the net present value of costs (negative cash flow) equals the net present value of the benefits (positive cash flow).

Output attributes for BioCar2 project

The output attributes for BioCar2 show better financial results:

  • The rate of interest is of 111% of investment.
  • The payback period of 5 indicates that the project will recover its initial investment in 5 years and will be profitable thereafter.
  • The internal rate of return of 3270.28 is the discount rate at which the net present value of costs (negative cash flow) equals the net present value of the benefits (positive cash flow).
Figure 13. Investment analysis results for BioCar2 project
Graph and data for NPV, ROI, IRR, and payback time

Analyzing the NPV probability

The NPV Probability tab shows the probability distribution of the expected net present value (NPV) for the BioCars module. The probability distribution shows the 10th percentile, the mode, and 90th percentile values for the NPV. The vertical line in the middle of the distribution curve indicates the mode value.

Analyzing the NPV probability for BioCar1 project

The NPV probability distribution for BioCar1 project shows a good net present value at the 10th and 90th percentile, with the mode value of 1,438,266,458.09.

Figure 14. NPV probability distribution for BioCar1 project
graph, in green

Analyzing the NPV probability for BioCar2 project

The NPV probability distribution for BioCar2 project shows a good net present value at the 10th and 90th percentile, with the mode value of 4,902,729,083.21.

Figure 15. NPV probability distribution for BioCar2 project
Analyze the probability of net present value for BioCar2 project

Important: In some scenarios, the NPV probability distribution might be displayed in red after you build and simulate the project. This red curve indicates a negative net present value of the project at any given point in time. This indicates that the project is not a good investment.

For example, notice that the graphed curve in Figure 16 is in red, as an alert.

Figure 16. Negative NPV probability distribution
graph, in red

Conclusion

For the BioCar1 and BioCar2 projects, you have calculated the amount that the company needs to invest in these areas:

  • The engineering cost
  • The manufacturing cost
  • The warranty cost

You also calculated the revenue that the projects would yield on two kinds of revenue:

  • The sales revenue
  • The services revenue

In a comparison of the BioCar1 and BioCar2 projects, the information about the financial metric is valuable in arriving at a decision. By looking at the values for BioCars 1 and 2, you see that:

  • BioCar2 has a high ROI of 111% compared to the ROI value of 35% for BioCar1. This indicates that BioCar2 project yields a better gain on investment.
  • The NPV probability of BioCar2 has a better distribution, indicating a high net present value compared to BioCar1.
  • The payback period of BioCar1 shows a value of 4, indicating that the project will recover its initial investment in 4 years, compared to the 5-year payback period of BioCar2.
  • The NPV mean of BioCar2 shows a lower expected value, which indicates a better risk minimization option.

Comparing the result attributes of BioCar1 and BioCar2 projects, BioCar2 is the project that should be undertaken, because the cost and benefit estimates yield a better value.


Acknowledgements

To create this article, we collaborated with various subject-matter experts (SMEs) to derive various business cases and find the best way to implement this feature in real-life scenarios. We thank Murray Cantor, IBM Distinguished Engineer, for guiding us through the whole process of setting up the model. We also thank Michael Rowe, Vatsala Swamy, Bjorn Kylberg, and Neeti Sukhtankar for providing their valuable insights and feedback while we were developing this article.

Resources

Learn

Get products and technologies

  • Download a free trial version of Rational software.
  • Evaluate other IBM software in the way that suits you best: Download it for a trial, try it online, use it in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement service-oriented architecture efficiently.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Rational software on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Rational, DevOps
ArticleID=852191
ArticleTitle=Using Investment Analysis in Rational Focal Point: Part 2. Build an advanced financial model and compare results for two projects
publish-date=12182012