ARM Outputs
The discussion below depends on an understanding of an Example Model.
Screen Views
The user can select a view in one of three ways. This first will be illustrated here, while the other two will be mentioned only briefly as they require a greater understanding of the tool than this short article will enable.
The first way to navigate to the information you want to work with is through a series of hierarchical graphical images. These are basically GIFs with hotpoints defined. Each Hotpoint can take you either to another graphical image or to a data view. Since the images are GIFs, they can be as complex as needed.
Figure 3 – Navigation Graphic – ABC Company Top
Figure 3 above shows a navigation graphic for the ABC Company. You can see the three Provinces and the departments for the Company and the Provinces. Putting the cursor over the box with Ontario makes the cursor show as a magnifying glass, indicating drilldown to another picture. Clicking yields the figure below.
Figure 4 – Navigation Graphic
–
We are now looking at the Ontario graphic, showing the two Ontario Stores, and the departments for Ontario and each of the Stores. If we put the cursor over the box with Ontario, we now get a different symbol. It is a worksheet symbol, showing that clicking there will provide data, rather than a further graphic. Clicking leads to the graphic shown below.
Figure 5 – Ontario Worksheet by Date
Figure 5 shows a worksheet which is the main vehicle to display data in ARM. The main part of the worksheet is the data area which, in this worksheet, shows the values of Income, Sales and Cost for Ontario by month.
Drilldown
Perhaps the most powerful feature in the entire ARM program is the ability to drill down every calculation in the system to its source values. This feature allows you to both validate a model when it is being built, and to answer many questions about a calculated number in a running model. A small example will illustrate this point.
Figure 6 – Drilldown Top
If you choose ‘Show Value Detail’ after selecting the April 2002 Income cell, you get Figure 6, representing the top of the drill down tree for this value. Remember that we are looking at the measurement items for the Province of Ontario. Where does the $202,903 come from? The Function used to compute it is an ‘a – b’, that is a subtraction of two values. What are the values? Just press the ‘+’ button on the left of the line and get to Figure 7.
Note that in dealing with this type of model, some people may prefer to define it in other ways, for example having the Income for a Province being the sum of the Incomes for a Store. ARM allows the flexibility to define your model in the way which bests fits your requirements.
Figure 7 – Drilldown, second level
As you might have expected, the Income is the Sales less the Cost, as is shown. Let us carry this on one more level. Where does the Sales amount come from? The Function used to compute it is ‘sum’ meaning add up a number of items. Just press the ‘+’ to the left of the Sales line to see what those items are (Figure 8).
Figure 8 – Drilldown, third level
Of course the Sales for Ontario is the sum of the sales for Housewares, Sporting Goods and Ladies Wear for Ontario. We could keep drilling down on any of these calculations until we reach the source data. Imagine being able to do this in Excel!
Figure 9 – Ontario Worksheet by Date
Back to the worksheet (Figure 9), you can see five tabs (By Date, By Item, By Variance, Detail and Graphs) which appear on all worksheets and allow you to view the data in different ways. We are currently on the ‘By Date’ tab, showing dates across and items down. The ‘By Item’ tab is shown in Figure 6 and is the transpose of the ‘By Date’ tab – dates down, items across.
Figure 10 – Ontario Worksheet by Item
Just above the numbers in the table and below the tabs, you can see the ‘Planned’ and ‘Actual’ buttons. These are the configured names for the dimensions (parallel sets of numbers) of the model. You can have any number of dimensions, each with a name you specify. In this model, there is a ‘Planned’ and ‘Actual’ set of numbers for each measurement. The ‘Actual’ button is pressed, so the worksheet is showing the ‘Actual’ numbers. The ‘Planned’ button will show the corresponding ‘Planned’ numbers. Selecting the ‘By Variance’ tab allows you to see ‘Planned’ vs ‘Actual’ for any one date for all items in the worksheet.
We will not discuss the ‘Detail’ tab here to any great extent. Suffice to say that it shows calculation rules for each item, and is not used very often in normal practice.
Figure 11 shows a graph of Income over time for the ‘Actual’ dimension. You can:
- show any or all of the dimensions on a graph
- choose any or all of the items you are measuring in separate graphs
Figure 11 – Ontario Worksheet Graph
Figure 12 – Ontario Worksheet by Date
See Figure 12 – the ‘By Date’ tab.
· Four buttons exist just under the ‘By Date’ tab. We will not describe them in detail, but they provide the ability to change date ranges (more or fewer months) or change the date frequency (to show by month, by quarter or by year) or both.
· We already discussed the ‘Planned’ and ‘Actual’ dimension buttons.
· The two yellow buttons to the right of the ‘Actual’ button provide you with the ability to make and access your own worksheets, containing information from anywhere in the model (these worksheets are called ‘Ad hoc Worksheets’ and provide the means for end users to get the precise view of the data they require without changing the model).
· The last three buttons are used for reporting via Crystal Reports®.
ARM provides two other ways to navigate to worksheets. One requires a fairly detailed understanding both of the model you are navigating and the building blocks of ARM, and is beyond the scope of this article. The other is a fairly straightforward way of choosing the Worksheet Group (or common worksheet layout) followed by the Member (the actual worksheet) as illustrated in Figure 13.
Figure 13 – Alternative Worksheet Selection
In this example, a Worksheet Group named Detail has a number of Members, each one representing information from a different Department or Location. The highlighted Member is the one we had selected from the navigation graphic above.
Exports
ARM provides a way to export data to Microsoft® Excel so you can manipulate it in any way you wish, in contrast with any Windows program where you can use various screen capture programs (such as SnagIt) to print screen snapshots, but cannot then manipulate the data with other tools. Additionally, this data is exported formatted in such a way that you can print it directly from Excel with reasonable headers, footers, bolding, gridlines, repeated titles and more as you can see in Figures 13 and 14.
Figure 13 – Excel Export of Drilldown
Figure 14 – Excel Export of Worksheet
Note that no formatting was done to get the above two figures. If you have ever exported data to a text file then imported to Excel, you will know how much work this saves each time. A tool called FormatX is used to do this automatic formatting. (See http://www.CherniakSoftware.com/FormatX for more information)
Reports
ARM interfaces with Crystal Reports in two ways. Generic ‘cross tab’ reports can be defined in Crystal Reports to provide a standardized view for worksheet data.
Figure 15 – Crystal Report Output for Cross Tab Report
These report definitions can be made in one place and used for all worksheets in the system. In Figure 15, not only the value for an item is shown, but also its status, a benefit of using Crystal Reports. However, if you need a specialized report, the ability to define customized Crystal Reports templates is available to give you much more flexibility.
E-mail Notifications
If you are keeping track of the height of a water reservoir or the temperature of a critical process, there are times when appropriate people should be informed if metrics are above or below a critical threshold. ARM provides the facility to inform an individual or group of individuals for each item being measured, and for each calculated value as well.
Next section: ARM Configuration Basics