Taking Dashboards to the next Level: Dashboard Simulators tm
By Loren Abdulezer, CEO, Evolving Technologies Corporation
a Crystal Xcelsius Consulting Partner
http://www.XcelsiusBestPractices.com
718-875-1718

Abstract

This article introduces Dashboard Simulators and illustrates basic techniques for creating and applying them. Dashboard Simulators allow you do things you can't normally do in Excel...at least not easily. Some of their basic facilities include incrementing and looping mechanisms. Others entail altering how computations are performed. This article addresses how to harness these capabilities in ways you wouldn't ordinarily think about when working with conventional spreadsheets. In the process, practical issues in deploying Dashboard Simulators tm are explained.

Going beyond the traditional dashboard

Traditional dashboards are considered a standard tool of the trade for today's business managers and executives. But what about the preferred tool of tomorrow? Good news: If you own Crystal Xcelsius, then you already have it.

I want you to think about what you do with dashboards. You can view Key Performance Indicators (KPIs). You can "drill-down" into your data to better understand the behind the scenes details. You can also present information, flip it around, and fold or summarize it. I refer to this as "digital origami." All these are wonderful capabilities of dashboard technology.

Crystal Xcelsius allows you to go a step further. You can directly harness underlying spreadsheet formulas to create a mathematical model. Using spreadsheet formulas inside a Crystal Xcelsius dashboard is certainly not new. The way in which you use it can significantly augment and enhance a dashboard. This article addresses how to harness these capabilities in ways you wouldn't ordinarily think about when working with conventional spreadsheets.

Computationally Challenged Spreadsheets

Spreadsheets provide no natural mechanism for incrementing a value or making a loop. In an Excel spreadsheet you can't casually have a formula that refers to itself, for example: if the cell B6 contains the formula: "=B6+1," then you will receive a circular reference error.

There are ways to get around such obstacles within Excel. To create an incrementing mechanism like this, you can use some non-standard techniques. You can:

* enable iterative calculations

* use Excel data tables to increment a value

* write a VBA macro

The first and second techniques do not require macros. The use of data tables to increment values is discussed at length in my forthcoming book "Escape from Excel Hell".

In the third approach you can write a macro to copy a value to a variable, increment that variable, and paste it back to the original location. While you are afforded full flexibility, you can get heavily steeped into writing code. If you want to add fancy dashboard-like facilities (comparable to visual components in Crystal Xcelsius), you'll quickly find yourself building a full fledged application, and needing to support that application.

As an alternative to these approaches, you can create a dashboard using Crystal Xcelsius that does the incrementing for you.

Creating an incrementing mechanism in Crystal Xcelsius

The mechanisms for incrementing are fairly simple, but not necessarily obvious.

Let's go back to our example of incrementing the value in B6. We could write a formula in cell C6 which is:

=B6+1

If we could copy the result of this computation back into B6, the value of B6 would be incremented, and so would the value of the cell in C6. The top half of Figure 1 shows this in action.

Figure 1: (Interactive) Increment and Loop Examples

The technique involved is to use an Xcelsius visual component like an Icon or a Toggle Button. Normally, these components have a binary state, that allow you to insert one value if you click the button once, and an alternate value the next time you click the button or icon. This makes its easy and convenient to create a kind of On/Off switch. Nothing stops you from using the same value for both the on and off states. This is the magic ingredient that makes incrementing easy to implement.

As shown in Figure 2, you can set both these states to the same value.


Figure 2: Configuring an Icon Component to increment values

Click the icon in its "On" or "Off" state has the same action; it just increments the counter.

The Xcelsius Toggle button works the same way. Adjust each of the labels to display the same information. In Figure 1 the label reads "Toggle button to Increment" regardless of whether it is in the on or off state.

In the lower half of Figure 1, I show how to create a rudimentary loop. Rather than incrementing indefinitely, you can loop back using the Excel MOD function. In this case I have it loop from 1 through 360 using:

=MOD(1+B11,360)

Practical Implementation Issues

Now, let's see how we can apply these incrementing mechanisms to an actual Crystal Xcelsius dashboard. I want to show a simplified Help Desk simulator and explain some practical issues (see Figure 3 and 6). This dashboard simulates a call center or help desk operation and aids in capacity planning.

To simulate likely scenarios, the dashboard makes use of statistical sampling. Specifically, the Poisson distribution is used to model varying arrival rates. Behind the scenes, a set of random numbers (with decimal values between 0 and 1) need to be generated. The Excel RAND function serves accomplishes this. Within Excel, RAND is a particularly volatile function. Anytime there is a slight change to the spreadsheet, RAND throws away the previously generated number and creates a new one. It is good to keep generating a new set of numbers, but what if you want to hold on to the current set and tweak other variables? You can do it within Excel, but it's actually easier to do it within Crystal Xcelsius.

Figure 3 shows you how the spreadsheet is set up within Excel before it is imported into Crystal Xcelsius. Start with a pool of random numbers (column F). The values for the freshly generated set of random numbers will be copied over to column C when we choose to copy it.


Figure 3: Spreadsheet to be imported to Crystal Xcelsius

To do this, we will use Crystal Xcelsius' Icon or Toggle components. It will work in a similar manner to the logic inside our Excel spreadsheet, but with a couple of twists.

The first twist is that an Icon or Toggle button can only copy over one cell per click. To move cells in quantity, you need to use the Source Data Component (see Figure 4). After inserting the Source Data Component into your design canvas, double-click it to open the Properties pane. Set the Insert Option to columns.


Figure 4: Configuring the Source Data Component

In the Behavior tab of the Source Data Component, set the Index Cell to I3 (this is a specialized driver cell for this dashboard). This "driver cell" is going to be driven by none other than a Toggle button, set up similarly to the way it was done earlier in the article (see Figure 5).


Figure 5: Configuring the Toggle button to work in tandem with the Source Data Component

The next twist is this: every time you click the Toggle button, it should trigger the Source Data Component to generate and deposit a new set of random numbers in column C. This works fine the first time you click the toggle button, but nothing seems to happen thereafter. Instead of being volatile, the RAND function inside the Crystal Xcelsius environment is fully sedated. There's a way to take care of this. Look back at Figure 3. Notice that the formula of cell F17 is different than the ones below it. Instead of:

= RAND()

You have:

= RAND()+$C17*1E-23

This is a "trigger cell" that forces RAND to recalculate en masse. It takes the value in cell C17 and multiplies it by a miniscule number. Excel and Crystal Xcelsius carry out calculations to a precision of 15 decimal places. When C17 is multiplied by ten to the minus 23rd power, the resulting number is too small to alter the value produced by RAND. The net effect is that this more complicated version of RAND behaves as all the other RAND computations, but it has the side effect triggering recalculations.

Figure 6: (Interactive) Help Desk Simulation

You now have the best of both worlds. You can generate a new set of random numbers whenever you wish and tweak other parameters in your model with sliders and other visual components. Best of all, you need not worry that RAND is going to inadvertently recalculate.

Closing Thoughts

By now it should be clear that Dashboard Simulators tm work differently the garden variety dashboards we're all used to. They expand the range of possibilities.

What's important is that they use the exact same visual components as conventional dashboards. They're just used differently.

 

2006 Evolving Technologies Corporation - all rights reserved.

Loren Abdulezer is the CEO of Evolving Technologies Corporation (ETC), and author of the best-selling "Excel Best Practices for Business" and "Escape from Excel Hell". ETC, an Xcelsius Consulting Partner, is a technology consulting firm based in New York City. More information about Xcelsius can be found on Loren's web site: XcelsiusBestPractices.com. He can be reached at la@evolvingtech.com. The files referenced in this article can be found in the Article Reprints section of: http://www.xcelsiusbestpractices.com/. 2006 Evolving Technologies Corporation - all rights reserved. Dashboard Simulator is a trademark of Evolving Technologies Corporation.

Close window