Spreadsheet Engineering
An effective process yields results that meet users’ requirements and minimizes time spent.
This story is the second part of the case, if you haven’t read the first part — Influence Diagram, please click the link and read the case first.
The Phases of Spreadsheet Engineering
- Design
- Build
- Test
1. Design
A blueprint for the spreadsheet
- A Physical layout of major elements
- Rough indication of calculation flow
The main elements of blueprint
- Data (parameters)
- Decision variables
- Outcome measures
- Detailed calculations
2. Build
•Follow our blueprint.
•Predict the outcome of each formula.
•Use relative and absolute addressing to simplify copying.
•Use dummy input data to make errors stand out.
3. Test
- Check visually
- Display individual cell references
- Display all formulas
- Use the auditing tools
- Use error traps
Display individual cell references
Press “F2” or double-click on the cell of interest
Display all formulas
Hold down “Ctrl”+ “~”, or, Formulas ► Formula Auditing ► Show Formulas
Use the auditing tools
Trace Dependents: Colored arrows to successors
In our case, if we select the Number of catalogs and click Trace Dependent the arrows will indicate that both Number of responses and Total catalogs cost are affected by the Number of catalogs.
Trace Precedents: Colored arrows to predecessors
In our case {Revenue — Cost = profit}, if we select Profit and click Trace Precedents the arrows will indicate that Revenue and Cost affect the Profit.
Error traps
Use some formulas added to a spreadsheet that warns the user of potential errors
We just create a useful spreadsheet, let’s analysis it!
