112 CHAPTER ◆ 1 0 Prototype in Modeling Software
firms have generally organized their design techniques relative to their business context.
Nevertheless, some best practices exist. The six golden rules of spreadsheet design:^14● Separate inputs, calculations and results. Place inputs in a separate part of the work-
sheet from calculations. Outputs should be in another part still.
● Use one formula per row or column.
● Refer to the above and to the left.
● Use multiple worksheets for ease of expansion and for repeatable blocks.
● Use each column for the same purpose throughout the model.
● Include a documentation sheet that identifies key formulas.Grossman and Ozluk add the following:● Keep formulas simple to enable check of intermediate calculations.
● Use named cells and ranges.
● Enter each input once.
● Use absolute references.
● Use cell-protection to prevent accidental or unauthorized modification of formulas.
● Make the spreadsheet file read-only to prevent accidental overwriting.^15Also, we recommend enhancing the look of the spreadsheet with bold fonts, borders, shad-
ing, and graphs when displaying results. This will make the spreadsheet easier to read.10.4. STEP 3, LOOP 1: Prototype Known Calculations
After researching similar and competing systems, the product team should convert all
known calculations and logical algorithms to a common, standardized format. The team
members charged with the prototyping task, namely, the financial engineers and program-
mers, can take the standardized equations and build component prototypes from them.We know a firm that used Excel ’ s standard functions to average the volatility between historical volatili-
ties. That is, given several standard deviations for several stocks, they calculated the portfolio volatility
as the average of the constituents. Of course, standard deviations are not additive, but no one caught it
for several years. How much money do you suppose they lost because their programmers used standard
Excel functions in unexpected ways?In prototyping, the difficult step is taking known knowledge and applying it to an
unknown problem. (Our methodology teaches how to perform research by also teach-
ing how to apply unknown knowledge to unknown problems.) As stated previously, we
believe in risk-driven iterative development. As such, the team should consider which
components they believe will be the most difficult to build. These components must be
the first ones built into prototype models. There are multiple reasons for this approach:● Time spent on the easy tasks will be wasted if the harder portions end up not working.
● Objectives could radically shift based upon the conclusions formed after building
the most difficult pieces. For example, calculating a model for an entire volatility