|
Page 1 of 2
Representing business data over different timeline intervals -Targets, Actuals, and Gaps Timeline data is sometimes easy to obtain but can at times be difficult to present. This article illustrates simple and effective techniques for presenting timeline where some degree of analysis is required as it relates to presenting drill down capabilities with Key Performance Indicators (KPI).
Overview
Imagine you have a wealth of data concerning the sales levels for a variety of products. Management spent a ton of money to deploy this system for predicting sales. They are chomping at the bit to compare actual sales against target projections. The sales targets and actual levels are recorded at the daily level. You need to display information by the month, weekly, and finally at the daily level. How do you go about doing this?
This is not a difficult task; it just requires that you organize your information in some easy to use manner to facilitate analysis and presentation. The example given in this article is based on sample data for three products comparing target and actual sales tabulated on a daily basis for one calendar quarter, however, bear in mind that the same techniques apply even if you had dozen of products and are looking at years worth of data.
The files referenced in this article can be found HERE. To download files in the File Vault you need to be registered (This is a one time process, and it's free!) and you need to log in. IMPORTANT NOTE: There has been a slight revision in one of the sets
of spreadsheet formulas. All files for this article have accordingly
been revised and updated. If you previously downloaded these files before
12 March 2008, you may wish to update with the new set (they are all listed with a file date of 12 March 2008).
Organize Your Data
A first step is to gather your data in one place and create a structure that makes it easy to analyze. If you are preparing target and actual values for various products, you may want to create a pattern similar to Figure 1. In this case, each day's data has its own row and all the target values are together (target 1, followed by target 2, followed by target 3), as are the actual sales. Instead of a T1, T2, T3..., A1, A2, A3... you could have T1, A1, T2, A2, T3, A3... While this is feasible, the first arrangement is actually easier to work with.
 Figure 1: Arrange your source data in one location and uniformly structure them into columns.
One of items you may wish to present in your dashboard is the shortfall between and actual value and its respective target value. This "Gap" or differential can be used as one of your KPIs and is easily displayed using a Trends Icon visual component in Crystal Xcelsius (see Figure 2). Your pattern of arranging data for three products should be Target 1, Target 2, Target 3, Actual 1, Actual 2, Actual 3, Gap 1, Gap 2, and Gap3. Gaps are just computed in a third set of columns where Gap 1 is simply Actual 1 less Target 1, etc.
The objective is to present information for just one product at a time and summarize it according to the appropriate level, be it by month, week, or daily level.
The objective is to present information for just one product at a time and summarize it according to the appropriate level, be it by month, week, or daily level. 
For the moment, just focus on analysis at the weekly level. Your task is to take daily information and aggregate it into buckets of seven days at a time. This means that you want to show aggregate data for days 1 through 7 in your timeline, followed by days 8 through 14, and so forth. This would translate to specific calendar days, and then followed by the computed sum for the respective target values, actual values, and their gap.
You may want to follow along with the spreadsheet supplied with this article. Open Article14TAGAnalysis.xls, and scroll over to Column Q on the "AnalysisPage" worksheet (see Figure 3).
 Figure 2: KPIs as Trend Icons
The correct answer for this question can be computed in cell E9, which is simply:
The sum of the values of the target data for Product 1 as well as any of the other data is computed using a SUMIF function. Column L is an auxiliary column that gives a week number of the particular day's data happens to be. If that number matches the value in Column Q (the week number in question), then sum it. The actual formula used is:
=SUMIF($L$9:$L$99,"="&$Q9,B$9:B$99)
Once you go through all this data analysis, you can summarize the important information on a "ReadFromPage" worksheet (see Figure 4).
|
