About the Solution

[Previous] [Next]

Spreadsheets are probably the most commonly used programming language in existence. This might sound strange to you, since a spreadsheet does not look like any type of programming environment you might know. However, every time you enter a formula into a cell, you actually create a small program or add to the existing program created by all the formulas in the spreadsheet. This program is executed many times during the life of a spreadsheet and usually completes in a few seconds or less.

So, if spreadsheets are really just programs, why do users create spreadsheets instead of small Microsoft Visual Basic or C applications? Is it because you can create programs in a spreadsheet that are impossible to translate into other programming languages? Any talented developer can reimplement the most complex spreadsheet model in Basic or C code, so that can't be the reason. I believe that the real reason users create spreadsheets is threefold.

First, creating certain kinds of models is simply easier in a spreadsheet than in a traditional, procedural programming language. Spreadsheet programs are more declarative, meaning you declare your intentions by constructing formulas that reference other cells, and the spreadsheet recalculation engine determines a program's structure from those dependencies. The recalculation engine automatically determines the program's control of flow (which is where the program starts and how it proceeds), and although the flow is derived from your declarative dependencies, you do not explicitly control or dictate it. The recalculation engine's ability to automatically determine control of flow based on dependencies often makes iterative programs, recursive programs, or programs with tricky dependencies easier to express in a spreadsheet model.

Second, the function and cell reference programming model exposed by spreadsheets is much easier to grasp and use, especially for novice programmers. Even those who would never consider themselves programmers can create a SUM formula using Microsoft Excel's AutoSum command bar button. The user thinks of the spreadsheet model much like he or she thinks about a calculator. However, the SUM function creates a small program, equivalent to:

 Function CellValue(Range)     For Each cell In Range.Cells         If IsNumeric(cell.Value) Then             CellValue = CellValue + cell.Value         End If     Next End Function 

In fact, the real SUM function is much more complicated than this, handling all kinds of weird cases, such as numbers entered as text (entering a value of '2 into a cell causes the calculation engine to treat it as text, even though it is displayed as a number), error conditions, or Null values. If novice users had to write complex Visual Basic or C code every time they wanted to add up some numbers, they would toss their spreadsheet program and use their trusty solar calculators. Instead, users who are not trained in traditional programming, such as finance analysts, can easily build complex models that equate to hundreds of thousands of lines of procedural code.

Third, spreadsheets allow users to merge their programs with the report presentation they eventually want to see or print. In a more traditional programming language, you must write code (in addition to the code that performs calculations) to display the results in a professional-looking report. Spreadsheets allow users to perform both steps at once because the result of each formula is displayed in the same cell. Spreadsheet programs such as Excel also allow users to heavily format their reports, enabling users to produce any type of output imaginable. This formatting and presentation information is just as critical as the original mathematical model, as is often exemplified by users who spend more time formatting their spreadsheets than verifying the accuracy of their models.

Therefore, if you accept that spreadsheet models can be quite complex programs and that users not formally trained in programming can create and maintain them, it makes sense for you to use these models in business solutions that need them. Furthermore, if you accept that the presentation and formatting in a spreadsheet is just as important as the model itself, it also makes sense for you to use that formatting when displaying the results of the program. The Loan Calculation solution described in this chapter illustrates these two concepts, showing you how to use existing models in your own solutions and how to use the formatting captured in a spreadsheet to drive the presentation of the model's results.

The Loan Calculation solution illustrates a mortgage calculation complete with payment table. I bought my first house about a year ago, and I was surprised to discover just how complicated a mortgage calculation can be. How much could I afford? How much should I include in a down payment? How much difference does it make whether I get a 6.5 percent interest rate or a 7 percent rate? How much could I save in interest if I paid an extra $100 a month toward the principal? I quickly realized that I could model this in Excel, including a full payment table. I could have written a Visual Basic program to calculate all this, but it was much easier to model it in a spreadsheet—plus I could print a nicely formatted report of any calculation result. This solution uses that spreadsheet (enhanced a bit since its original creation), both on the web server and as the model for an interactive Spreadsheet control on the client. I built the model in Excel 2000, saved it in the HTML file format, and used it directly from the Spreadsheet component (more on how that works later). I made all changes to the model using Excel, which allowed me to use all the powerful editing features of that product.