Use Xcelsius to Communicate Your Incentive Plans

By David Harper

Crystal Xcelsius is ideal for creating business dashboards, but don’t forget that it can serve many other applications, too. I personally believe many of these applications have yet to be discovered for a simple reason: Excel is the de facto, distributed database of choice in most organizations. It is beloved by quant jocks, but not exactly popular with the wider general audience. Anywhere and anytime that mission-critical data is stored in Excel—whether a budget, a list, a flat file database, or an incentive plan—Xcelsius can be the tool that cracks open the Excel container and shares the inside data-stuffing with an audience not accustomed to speaking algebra (i.e., =$B$4*D1^3) as their primary language.

An ideal application for Xcelsius is communicating the sales incentive plan. Software vendors have been, for years, trying to get organizations to abandon their homegrown, Excel-based incentive plan tracking programs in favor of proprietary software systems; but alas, Excel is still the most popular administrative tool in this category. There is a category of software applications specifically designed to administer and communicate compensation and incentive plans (Enterprise Incentive Management, EIM). But if your company is not ready to adopt an EIM solution, Xcelsius can turbo-charge your plan communication—and then some!

A Typical Sales Incentive Plan

To show you an example, I borrowed an archetypal sales compensation plan from David Cichelli’s excellent book, Compensating The Sales Force (McGraw-Hill 2003. Visit www.compensatingthesalesforce.com). Mr. Cichelli is the leading authority on sales force effectiveness and incentive plan design. His book illustrates many other plan types – for various situations and objectives.

According to Mr. Cichelli, “sales people need tools to better understand how their performance affects their pay.“ The purpose of this example is to replace the typical incentive plan communication document with a visual display. Instead of a boring paper flyer, what if we “sell the salesperson” on his/her own sales plan? I chose a standard commission plan coupled with a multiplier, but we could just as easily communicate a quota-based plan. In this hypothetical plan, the sales rep earns a 4% commission on dollar production up to a certain level (in this case, $1,000) and then steps-up to earn 8% on all dollars above this level. Our hypothetical plan also offers a multiplier: the salesperson has other qualitative (or MBO-type) goals and depending on his/her achievement against these goals, the multiplier kicks in and “magnifies” the commission payout. For example in this plan, if the salesperson achieves 110% versus plan against his MBO goals, the multiplier is 120% and his total payout equals the commission x 120%.

Basic Chart

I am going to show you two charts: a simple chart that gets the job done, and a chart with a few visual enhancements. The simple chart contains a payout curve (the blue line) and an actual payout bar (the green bar). The payout curve—just a line with a kink to reflect the “progressive” nature of this plan—illustrates the salesperson’s opportunity on the y axis (the potential cash payout) against sales production on the x axis:

Try it, it's interactive

Four sections of the linked spreadsheet

The embedded spreadsheet contains four sections. In the first section, we collect the plan inputs including the commission schedule, the multiplier schedule, and the sales production goal:

In the second section, we reference the above plan parameters in order to create the data elements for the line/bar combination chart in Xcelsius. Based on the plan parameters, commission payouts are calculated for a sequence of production levels. Here, I broke out the commission calculation for readability, but Column O in the embedded spreadsheet contains a single calculated column (“Alternative Commission”); this alternative calculation is more compact but less readable as a screenshot.

The columns under Bar Chart provide the data series that plots the single actual, payout bar. (Remember, we are using a line/bar combination chart, a line series for the payout curve and a bar chart for the actual payout). An if-statement evaluates only one of the intervals as TRUE; this is the one that gets plotted. The others evaluate to FALSE. The nice thing about FALSE in a bar chart is that it empties the series instead of plotting a zero—we don’t really want to show zeros.

The third section contains the two data points that we want to give the salesperson control of with visual components on the display: his achievement (i.e., the actual sales production) and his multiplier.

The fourth section contains a few administrative items, including the text for the two dynamic labels that appear in the chart. If you have not tried to push dynamic text to your Xcelsius charts, I encourage you to take a look at the contents of Label 1 (cell H38).

There are two things to know in building these formulas. One, you combine (concatenate) strings with the ampersand (“&”), and two, you format text with the =text() function. The =text function takes two parameters: the reference to the string and a formatting code.

The simple chart

The simple chart contains only five components (as displayed in the Object Browser): the combination chart that contains a line series and a bar series; two components that link to our dynamic text (so the user can see how the commission is calculated); and two components (slider and dial) so the user can perform “what-ifs” on his sales production and multiplier.

The fancy chart

The fancy chart takes further advantage of Xcelsius. You can see we added a background; gave the slider double-duty by having it replace the x-axis; added two more user-control components; and added a button that will display the plan details.

Try it, it's interactive

Add a background

It is so easy to add a background image in Xcelsius. Drag and drop the Image Component on the canvas and Import the JPEG file. Because this is the background, we want it to appear underneath everything else, so we make sure it appears at the top of the Object Browser (Format > Send to Back from the upper menus in Xcelsius).

Move the Slider to the X-axis

Our simple chart really shows two x-axes, which is redundant. Why not make the slider also serve as the x-axis? In The Visual Display of Quantitative Information, Edward Tufte implores us to “erase redundant data-ink.”

Add Two User-control Components

I added two more controls (Spinners) so the user could fine tune their production and multiplier inputs. They link to the same cells in the spreadsheet. Wait a second, isn’t that redundant? Yes, but we are making life easier for the user. If they have a specific production number in mind, we don’t want them to get frustrated with the slider. Form should follow function, as the designers say.

We also activated the alert functionality in the Metric #2 Spinner. Like many plans, ours has a sort of threshold at 80% achievement and plateaus at 120%, so we show a red background when this is low and a green background when it is high. To do this, just go to the Alerts tab, click Enable Alerts, and then set your Alert Levels. See illustration below:

Add the Plan Description Button

We wanted to add a button that conveniently displays the plan details, should the sales rep forget. If you’ve ever tried to do this kind of layering in Flash MX 2004, buckle up your seat belt before trying it. I recommend this only because I almost fell off my chair when I saw how easy this is to do in Xcelsius! We only need a toggle button and another image component. The purpose of the toggle button is to change the value in cell H44. When the user clicks the toggle button, this cell changes to a value of “1.” This in turn activates the image component (which contains a JPEG of the plan description) because cell H44 is linked to the Display Status in the Behavior tab of the Image Component, as below:

That all, folks! I hope you enjoyed this tour. This was just an introductory example – Xcelsius can do much more than create a dynamic calculator. But the great thing about Xcelsius is that, if you choose to stay with Excel in your work, you can use additional flash (.SWF) assets for rich media deployment. When I show people Xcelsius, this is one of their favorite features: they are not forced into a new, proprietary software platform. They can just add (in modular style) flash assets, as desired. And if usability were a crime instead of a virtue, Xcelsius would be on the most-wanted list. Speaking of crimes, you’ve no doubt heard the explanation given by bank robber Willie Sutton, when asked why he robbed banks. He said, “Because that’s where the money is.” The next time someone asks you why you need Xcelsius, you might admonish them with a flair of cowboy swagger, “because Excel is where the data is.”

David Harper is the Principal of Investor Alternatives, LLC, a firm that specializes in investment research, software sector coverage, and derivatives valuation. He is the Editor-in-Chief of Investopedia Advisor, a newsletter devoted to the early recognition of public companies that are likely to be lead future market. He publishes the Bionic Turtle Study Notes, courseware for learning advanced risk management concepts. He is a Charted Financial Analyst (CFA) and Financial Risk Manager (FRM).