A Simple Business Component
This article's sample app relates to sales tax calculations (click here to see the spreadsheet programming sample app with source code). Currently, mail-order and Web-based retailers are not required to collect state sales tax for orders shipped to states where they don't have a "substantial physical presence" (called Nexus). However, imminent legislation apparently will compel such retailers to correctly asses and collect sales tax for all states to which they ship, whether they have nexus or not.

Lets first examine a very basic tax calculator and then move on to a more sophisticated one. Figure 1 shows how a basic tax calculator can be developed in Excel.

Figure 1: Spreadsheet with Sales Tax Calculations

The following is the pseudo code from the application. It calls the tax component to pass the transaction data (input data) into the spreadsheet:

// Pass the Subtotal, State, and Nexus data into cells B3, B4, B5
TaxSheet.SetValue(3, 2, 250.29); // Subtotal
TaxSheet.SetValue(4, 2, AR); // State
TaxSheet.SetValue(5, 2, true); // Nexus indicator

and retrieve the result (output data):

// Get the tax from cell B7
double tax = taxSheet.getValue(7, 2); // Calculated Sales Tax Amt.

In this simple example, a single cell formula in cell B7 calculates the tax rate:

"=IF(B5, LOOKUP(B4, E3:E56, D3:D56)*B3,0)"

This is a highly simplified example. It basically implements a lookup table in Excel rather than in code. The input cells (B3, B4, and B5) and the output cell (B7) act like the public API of the tax component. The other cells act like private members of the component. As is the case with all component-oriented development, as long as the pubic API doesnt change, the private members in Excel can be continually enhanced and updated without having to change the code of the caller. You can further isolate changes by passing values in and out of the spreadsheet by name instead of by Row,Col. So the pseudo code would look something like the following:

TaxSheet.SetValueByName("SalesSubtotal", 250.29); // Subtotal

Now you can move your cells around without having to change code. Using named cells also makes your cell formulas a lot more readable.

The collection of input cells and output cells make up the public API of the spreadsheet business component. The formula cells in the hidden layer of intermediate calculations are analogous to private methods of the business component.

Now let's take a look at a more realistic example. In some states, different types of purchases are taxed at different rates. For example, food, liquor, clothing, and automobiles may all be taxed at different rates depending on the ship-to state. Figure 2 shows a tax component spreadsheet that accommodates multiple items and different tax rates based on the type of the item. Figure 3 shows the state tax data for multiple tax types.

Figure 2: More Sophisticated Tax Calculator Component

Figure 3: State Tax Data for Multiple Item Types

Using these spreadsheets allows you to determine the line item taxes and totals all at once, rather than item by item. Now assume that you have to make this component available as a Web service that enables clients to submit a complete customer order and receive the results in one pass. If you used Knowledge Dynamics' KDCalc, you could employ its ProcessXML function, which allows multiple input cell values to be passed in and multiple results to be returned as XML. Listing 1 shows the pseudo code for this function. The return value, sResult, is an XML doc that contains all the requested output cell values.

Now a multi-line order can be processed all at once. More importantly, if the tax rates or the tax rules change, users can update the spreadsheet without changing any code. For example, some states now charge a luxury tax for autos that cost more than $50,000. To accommodate this, the tax expert would alter the cell formulas and the state tax table, but the Web service wrapper code would not have to change. Building and maintaining the tax logic in Excel takes a fraction of the effort that coding it by hand does. The relative efficiency improves even further for more complex business logic.

Now that you've seen a simple application in action, take another look at some of the important considerations for making the best use of spreadsheet programming.