Calc2Code Tips of the Day

Day 1

Let’s suppose we’ve been asked to create a spreadsheet to calculate sales commission for a company for 2006, and 2007 to date. The commission structure looks like this:

• For sales below 10,000 in the calendar year, the commission earned for the year is a base of 1,000.
• For sales above 10,000 and below 50,000, there is no base, but the commission is 10% of sales.
• For sales above 50,000 and below 100,000, the commission for the first 50,000 is 10%, and the remainder is 12%.
• For sales above 100,000, the commission for the first 50,000 is 10%, for 50,000 to 100,000 is 12%, and everything above 100,000 is 15%. In addition, sales over 100,000 generate a 10,000 bonus.

The spreadsheet commission_first.xls shows one way to do this:

Each point at which the commission changes is called a tier, and we have a fairly long formula that calculates the commission as a function of the sales in B18. We then copy this formula down and across all the way to F21 to calculate commissions for all salespeople for both years.

Note that it’s important to get the $’s right so that all the references to B4 through B11 are absolute; otherwise the copy will create the wrong formulas. We also want to be sure to not have $’s around the reference to the sales figure of B18, or the formulas will also be wrong.

Again, this is a long formula. As a general rule, the longer the formula, the harder it is to come up with and the longer it takes to get it right. A more transparent and easily understood way to set up the calculation would be to split it into pieces, and add the pieces up. For example, in this version of the spreadsheet commission_second.xls :

We create a column that indicates which tier the salesperson is in (E6:E9), and to the right, we calculate the commission for being in that tier. We can then test various values for the sales in E3 to be sure that there is always 1, and only 1, value of 1 in the range E6:E9, and it’s also easier to check the commission value and verify it is sensible. This is exactly why Excel is so good for prototyping: you can see the intermediate results and make sure they are correct.

Unfortunately, this version of the calculation is more difficult to use to create a table of commissions for each salesperson. When the entire calculation is in one cell, copying it down and across is easier than when the calculation spans many cells.

So let’s use Calc2Code to convert the range of cells with the commission calculation into a single function which can be called from the table. Here’s how the configuration screen looks:

We only need to tell Calc2Code a few things:

There’s a new function we’re naming commission

There’s a single input argument, which is cell E3, and its data type is double (that means it’s a real number, not necessarily an integer).

The result of the function is in cell F11, and it, too, is a double.

And the new function is going to go inside a DLL called commission.

We close the dialog, and then push the Calc2Code Builder button, and wait for the code to be generated and compiled. The spreadsheet will be closed, and the status bar at the bottom of the screen will say “Done.” This means everything went OK.

Now, if we open up a file called commissionxll.xll, we can use this new function, in this spreadsheet, and any other spreadsheet.

So we can replace the long formula in E18 with =commission(B18) and copy that down and across. This is a much more readable spreadsheet, because it encapsulates the commission calculation into a single function call. By the way, it’s a good idea to name the new function something like commission. Had we named it do_the_thing() then if we or someone else came back to the spreadsheet a year later they’d have a tough time following what was going on. But as long as the original spreadsheet that was run through Calc2Code is saved, as any important file should be, it will be simple for others to know exactly what’s being done in that function.

Also, if you open up another spreadsheet and type =commission(100000) you’ll get the right answer, even though all the information about tiers and commissions aren’t in the new workbook. This is a handy way to keep components of a formula private and confidential.

That said, if you’re not as concerned about keeping the formula secret, there’s another way to do this that can be even more useful. This is demonstrated in commission_general.xls. In this spreadsheet, sales is not the only argument passed to the commission function. Instead, the tiers, the draw, the commissions and the bonus are all parameters to the new function. Here’s how the configuration dialog looks:

It’s a lot like before, except we’ve now specified all the other cells as inputs with their types. This new function is more useful if the particulars of the commission schedule change, or differ form region to region or product to product, and you want to have just one single commission function to use everywhere. In all cases, the spreadsheets that use the Calc2Code generated commission functions are smaller, more easily maintained and understood, and are less subject to tampering and breaking.

As a side benefit, the commission.dll file that was created can be used by the team that works in payroll, or planning, or accounting, or that runs SAP or any other system in the organization that needs to use this function.

Click here to see today’s next tip…