This month wraps up a three-part series that began by describing key elements of a business case followed by why single point estimates fail to provide stakeholder buy-in. Examples describing the different characteristics of estimates were discussed ranging from single point through various probability distributions. An example cost forecast was generated using Monte- Carlo simulation. Monte-Carlo simulation and subsequent analysis can be run using spreadsheet and add-in software. Decisioneering’s add-in software package Crystal Ball is used for this discussion series.
Today, I will advance concepts from the first two discussions and apply them to a generic data warehouse financial model. Emphasis will be placed on the business contribution of the solution. Some cost analysis is addressed within the labor category since it tends to be the most expensive component. However, the major focus is geared toward the areas of solution that contribute to the business.
This spreadsheet model calculates the net present value (NPV) for a data warehouse project. Data warehouse solutions provide visibility into information previously unavailable through transaction system reporting. Additionally, the use and benefits of the solution evolve over time so the accuracy of the financial model should take into account time dependent estimates. The model in Figure 1 accommodates this characteristic. Several sections make up the financial model and can easily be expanded and customized. The major sections include:
- Estimate Parameters Describe the boundaries and characteristics of each estimate along with the type of distribution used based on given set of assumptions. These parameterized cells drive the baseline estimate listed on the far right side.
- Independent Estimates Relate to assumptions that do not significantly change over time. Selecting the distribution for each assumption depends on how much information is available that describes the behavior associated with the estimate. When faced with limited detail, uniform or triangular distribution are reasonable candidates.
- Time Dependent Estimates Relate to assumptions that are subject to change over time. For example, related events likely to increase or decrease over time associated with the solution.
- Solution Contributions Describe how the solution based on given estimates will financially contribute to the organization.
- Current State Costs Provide supporting detail regarding existing costs of any current state applications or systems that will be replaced by the new solution.
- Solution Costs Describe how the solution costs break down by category and time. Begin with the major categories of cost like labor and materials (hardware, software, etc.) and continue with additional detail where appropriate.
- Solution Benefits Contain the difference between contribution and cost across time. Additionally, the discount factor is calculated and factored into the total discounted net savings for the solution.
- Forecast Values Based upon the total discounted net savings. Other financial forecasts can be developed and derived from information contained within the model. For example, one could calculate return on investment (ROI) by dividing the discounted net savings with the total cost of solution and multiply result by 100.
Figure 1. Data Warehouse Financial Model
Solution Contribution Detail
One of the most important sections of the financial model is the solution contribution. Without it, one need not continue. Three areas were expanded using the developed estimates.
- Improved Margin of Sales Most organizations sell a product. Gaining access to accurate information related to sales and increased visibility across different functional groups supporting the process will produce insight previously unavailable. In this example, stakeholders agree that reports and analysis associated with the data warehouse solution will enhance 10 percent of total sales by approximately five percent. However, the range of enhancement could be one or two percentage points above or below with the majority of them falling toward the low side. A lognormal distribution was used to represent this estimate based this behavior. By multiplying 10 percent of the yearly sales forecast by the percent sales enhancement results in the sales margin contribution for solution.
- Operational Anomaly When multiple non-integrated operational systems support the business and rely on information that originates outside of their control, it is common for situations or anomalies to develop. These situations can range from inventory shortages to product defects. The range of cost avoidance will vary and can be represented as a distribution. In this case, the distribution is triangular. The number of times per year these situations occur can be represented as a distribution. In this example, a Poisson distribution was used with a decreasing rate per year. Decreases are due to the evolving benefits associated with the solution. The contribution for the operational anomaly can be expressed by this equation:
(Anomaly Impact *Percent Cost Avoidance)* (Number of Occurrences)
- Reporting Enhancement Basis reporting is used to support the business in order to understand operations. These reports evolve over time. End users often request IT professionals to create or modify reports. Maintaining this type of environment is costly. Most end users want the flexibility to generate their own reports and analyze the results. By structuring the data within the warehouse by subject area and providing access to this data through an intuitive easy-to-use interface, end users may be able to develop many of their own reports. Most data warehouse solutions can claim this as contributing benefit. However, this should not be the only one. The basis behind this contribution is to understand the current reporting environment costs and factor in the reporting enhancement percentage.
Solution Cost Detail
Understanding the costs associated with the solution is vital. Individuals funding the solution want to know the range of cost as well as which factors have the largest cost impacts. Two areas were expanded using the developed estimates.
- Hardware and software The costs for hardware and software vary over time. For this example, the cost related to hardware was spread across two years. These costs can be narrowed down based on the availability of cost information; however, many times they are expressed as a range with an equal chance of being anywhere within expressed range. In each case (hardware and software), the range estimate was expressed using the uniform distribution.
- Labor This is generally one of the most costly items on the budget for building the solution. Since many organizations request external assistance to design and develop data warehouse solutions, allowances have been made to accommodate both internal and external labor. Additionally, once the solution is in operation, support resources with different cost structures take over daily operation. Building in flexibility and gaining an understanding of the solution life cycle will increase the accuracy of related expenses.
Interpreting the Results
The primary reason for introducing these concepts is to gain a better perspective of the calculated forecast. In this case, our interest was NPV. Figure 2 is a cumulative distribution result for NPV after 2,500 trials and Figure 3 provides a frequency distribution view. Since anything below zero is undesirable, we flagged those results in red. Certainty is the percent chance that a particular value will fall within a given range. The chart below tells us that 78 percent of the values associated with the solution will be positive with most of those values falling close to $400K. Two-thirds of the results fall within $500K of the mean. The upside of investment is much higher then the downside.
Figure 2: Cumulative Distribution
Figure 3: Frequency Distribution
Applying these techniques to the key estimates associated with your business case can lead to greater understanding and insight. Expressing data warehouse investments with an understanding of the business impact is essential for gaining stakeholder buy-in.
Register or login for access to this item and much more
All Information Management content is archived after seven days.
Community members receive:
- All recent and archived articles
- Conference offers and updates
- A full menu of enewsletter options
- Web seminars, white papers, ebooks
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access