Teradata Magazine | Special Report: ROI: A metric's journey

Now more than ever, investors and managers are tracking every penny. Companies have an unwavering responsibility to show quantifiable value for every dollar spent. Is IT at a disadvantage, since IT must compete for funding against all other corporate projects and is sometimes seen as a "riskier" investment? Not necessarily. IT projects need metrics rigorously applied against specific objectives in order to succeed in this environment. The best of the best companies are creating and monitoring these metrics.

I have encountered many companies working toward an enterprise view of their business and a keen understanding of the business value it creates. Those forward thinkers view ROI as an ongoing process that looks holistically at technology, processes, people and strategy. For example, only by systematically documenting that the data warehouse had been largely responsible for generating more than $100 million in incremental business was one company able to gain approval for a hard-fought capital investment. Calculating this metric would have been all but impossible without the appropriate processes in place.

Each organization is different, and there is no one-size-fits-all formula. However, there are several common practices every organization must consider when striving to implement a data warehouse life cycle management program.

Monetary gain is only a single aspect of ROI, but it is often one of the first indicators businesses examine when qualifying success. The following example shows how one retail company matured from examining financial return alone into examining opportunity.

Whatever your business and objectives, I think you'll identify with the challenges this retailer encountered, and find processes and information you might implement in your own company.

A major retailer has a revenue growth target of 10% and believes this business goal could be supported and measured with the help of a data warehouse. The corporate management division examines many areas of the organization and determines that improving the profitability of the market basket would be an appropriate objective for an initial project.

A cross-functional team of decision makers from IT, finance and marketing is assembled to oversee the project. Because many past IT initiatives have not delivered the expected business value, they know that to secure funding they will have to present a solid business case that includes a process to allow the company to measure the impact of this investment on an ongoing basis.

The team leverages Teradata's expertise to assist with the business case and to develop a data warehouse lifecycle management process that will define, track and monitor the project after it has been implemented. Representing Teradata's business value consulting group, I collaborate with the team throughout the following journey.

Our team determines that adding an analytical application for market basket analysis will yield the metrics and results that will solidify our business case. We begin the first project with the following plan and sample goal statements:

Once value is defined for the project, the business case must define the set of projects to be adopted to fulfill the action plan. The team collects appropriate business and financial metrics to assess the potential benefits associated with the initiative being evaluated. In addition, I work with the team to determine all the implementation costs including hardware, software, consulting and internal process costs. Together, we come up with both the revenue and margin impact. The spreadsheet in figure 1 summarizes our findings.

Spreadsheet of revenue and margin impact for
business case

Baseline assumptions
Company annual revenue	$1.5 billion
Number of market baskets	60 million
Average revenue per basket	$25.20
Average profit margin	28%
Average number of items per market basket	20
Business objective Goal: Increase the number of items by one in 25% of baskets
Financial impact derivations
Current number of market baskets	60 million
Target baskets (25% of 60 million)	15 million
Incremental number of items 1 additional item for 25% of the baskets	15 million
Average revenue value per item average basket revenue = $25.20; average number of items per basket = 20	$1.26
Average profit margin per item at 28% profit margin	$0.35
Expected revenue impact	$18,900,000
Expected margin impact	$5,292,000

Figure 1

Expected value of business case over
3-year period

Impact Analysis	Expected Value ($000s)
Benefits & Costs	Initial	2004	2005	2006	Total
Benefits	0	5,250	10,500	15,750	31,500
Costs	4,000	1,500	1,500	1,500	8,500
Operating Profit	(4,000)	3,750	9,000	14,250	23,000
Cumulative Benefits	(4,000)	(250)	8,750	23,000

Figure 2

Sample scorecard components

Metric: Number of items per market basket

Business indicators

Average number of items per market basket

Average number of items per market basket per type of shopper-household members dimension

Average number of items per market basket per type of shopper-price, value, convenience characterization dimensions

Implementation indicators

Percent of merchan- dise planners using solution

Percent of records that are accurate (data quality)

Number of recommended changes based on solution

Percent of data converted to new enterprise data warehouse

Analysis/ reporting

Average number of items in market basket per household members dimension

vs. plan
vs. prior periods
by category
by price tier

Average number of items in market basket per characterization dimension

vs. plan
vs. prior periods
by category
by price tier

Figure 3

Average number of items per market basket

	Jan/Feb	Mar/April	May/June	July/Aug
Planned	20	20.05	20.10	20.2
Actual	20	20.05	20.05	20.1
Variance	0	0	0.05*	0.1*

Figure 4

* indicates negative variance

Average number of items per market basket
as of August 2004

Size of family	Price shopper			Value shopper			Convenience shopper
	T	A	V	T	A	V	T	A	V
One	8	8	0	11	11	0	12	9	3
Two	14	14	0	18	18	0	20	17	3
Three	20	20	0	25	22	3	30	25	5
Three	50	50	0	51	51	0	60	51	9

Figure 5

(T=Target, A=Actual, V=Variance)

Value of variance margin
(Convenience shopper)

Size of family	August '04 variance	Margin ($)	# of baskets (1000s)	Total margin ($000s)
One	3	0.35	200	210
Two	3	0.35	300	315
Three	5	0.35	400	700
Three	9	0.35	100	315

Figure 6

After examinining the results, it becomes apparent that increasing the number of items per market basket by one for 25% of the market baskets would generate $18.9 million in incremental revenues, or $5.3 million in pre-tax operating profit. Comparing the cost required to implement the data warehouse and analytical application—approximately $4 million by our calculations, based on the company's choices in hardware, software, internal processes, change costs and outside consultants—we are able to develop the business case. Funding is approved for the project since the goals and profit vs. cost analysis is favorable.

Our team isn't finished with its tasks yet, however. The company's executives want monthly proof to ensure the value of this investment is actually achieved. We acknowledge that the next steps of the process are critical to ensuring success—that is, creating a way to measure impact over the long term—and the metric's journey continues.

Our team assesses the expected pre-tax revenue and margin benefits. Using Teradata's Business Impact Models (BIMs), it is projected that the first year's pre-tax operating margin would be $5.3 million, and increasing to $10.5 million and $15.8 million in years two and three, respectively. The gradual increase from period to period is due to the compounding of benefits. Incremental value is based on conservative modeling assumptions: monthly campaigns and limitation of incremental benefits (i.e. revenue and margin) to one year.

Upon considering the long-term view, the business case shows implementing the data warehouse and market basket analysis solution will deliver net after-tax benefits of nearly $12.7 million as illustrated by the 3-year net present value chart (figure 2). The table indicates the expected value in dollars over a 3-year period, using a 35% tax rate, accumulating in:

Net present value:	$12,633K (15% discount rate)
Internal rate of return:	96%
Payback period:	15 months

Phase III: Implementation of the project and implementation of the ROI life cycle management process

During the implementation of the data warehouse and analytical solution, we spend time defining key metrics and processes for the continuous measurement of the value created by this investment.

The first step is to establish a metrics plan, which consists of defining the key lagging indicator, or metric, to be monitored and tracked. And then the proper procedures and mechanisms are established to instill a continuous measurement process. This process includes business and implementation indicators and drill-down comparisons and reporting (see figure 3).

The main reason for establishing this process and a series of granular leading indicators (business indicators, implementation indicators and drill-down comparisons) is to identify forecast deviations that will likely occur. In addition, unless a framework is adopted, it will be difficult for the business to apply timely remedies to address potential implementation and usage problems in the future.

In the past, negative variance (i.e., actual results are falling short of the forecasted or expected benefits) might have been observed passively—not acknowledged or addressed too late to be able to identify the root cause of the problem. Now that we have established a process with granular leading indicators, scientific remedies can be proactively applied rather than relying on trial and error to identify the main source of the negative variation.

Let's examine this second scenario more closely, using figure 4. The chart shows that in the month of May the actual benefits start falling short of the team's forecasted or planned benefits. By tracking the activity driver—the average number of items per market basket—it might be possible to notice the deviation from plan but not to identify what caused the variance to occur.

By proactively defining relevant metrics and establishing a measurement process—including appropriate scorecard and accountability—project managers can properly analyze the details behind the observed variance and establish the root causes and proper remedial actions.

With a framework in place, management can now measure the results of planned vs. actual. For this example, a 4x3 dimension (household members x shopper characterization) measurement dashboard as seen in figure 5 shows that there has been a shortfall (not a decline, but a smaller increase than management had targeted) in reaching the goals for convenience shoppers across the board and for value shoppers in one specific household size.

Actions should be taken to review more specifically the promotions, displays and store placement for convenience offerings across the board for all households.

Also, management should drill down to find out why households of exactly three people in the value shopper category fell short of targets. This may only require simple changes, such as packaging ground beef in sizes between one pound (the traditional package, which may be too small for three people) and three pounds (the traditional "family pack" size, which may be too large for three people).

Figure 5 illustrates the changes to be seen in the convenience shopper with continuous improvement:

We could conclude that data warehousing and analytical application projects have variance imbedded in them due to process and implementation complexity. Proactively tracking and monitoring key ROI parameters and key performance indicators (KPIs) allows companies to identify potential problems early and to apply timely and adequate remedial actions.

Had this retailer not determined the existence of the variation in actual metrics as compared to the target put forth in the business case, or had the retailer passively measured the benefits, it would have left a big chunk of the potential benefits untapped.

Figure 6 demonstrates how the company gained improved ability to target promotions and displays to the convenience shopper and the added financial impact of catching the variation and its root cause early in the process as opposed to catching it too late or never. In this case, Teradata calculated the value lost resulting from not addressing the negative variance in a timely manner to be $1.5 million. In other words, the value of catching the variance early is worth $1.5 million in bottom-line business benefits.

As time goes on, the retailer continues to refine and redefine the business case, adding new approaches, action plans and applications. The company recognizes the value of using the data warehouse to assist in making action plans and business decisions and sees a strong return on investment.

It's the end of our example but only the beginning for you and your company. Establishing a data warehouse life cycle management program is not only a worthwhile activity, but a necessary one as well. A process that helps your organization articulate value from IT investments helps define IT priorities as well as business opportunities.

Contact your Teradata account manager for more information, or use the Contact Us feature.T

Cheik Daddah, director of business value consulting for Teradata, continues to focus and refine best practice thinking on the subject of data warehouse and analytical application value. With graduate degrees in economics and finance, Daddah has taught at both the undergraduate and graduate levels.