Chapter 4

• Spreadsheet modelling is more of an art than a science
• General Steps to building a spreadsheet model

  Plan, Build, Test, Analyze
• Guideline for Building "Good" Spreadsheet Models:

  (1) Enter the Data first (2) Organize and Clearly identify the data (3) Enter each piece of data into One Cell only (4) Separate data from formulas (5) Keep it Simple (6) Use Range Names (7) Use Relative and Absolute references to simplify copying formulas (8) Use Borders, Shading, and Color to distinguish cell types (9) Show entire model on spreadsheet
• Three Tests for a “Good” Spreadsheet Model:

  • You should be able to immediately identify the data cells, changing cells, and objective cell.

  • All elements of the model should be visible on the spreadsheet (including all constraints). You should not have to look in the Solver dialogue box to figure out the model.

  • Each equation should be simple enough that you can tell what the equation is in each output cell without looking
• Pressing control + ~ toggles back-and-forth between showing values and showing formulas in the spreadsheet
• Toggle is the act of switching back and forth between viewing the results in the output cells and viewing the formulas entered into those output cells.
• Auditing tools is a set of tools provided by Excel to aid in debugging a spreadsheet model
• Syntax is the spelling and grammar of a programming language.
• Syntax error is a character or string incorrectly placed in a command or instruction that causes a failure in execution
• A circular reference in computer programming is a situation in which two values reference each other. This type of reference can be extremely problematic if two functions or values depend on one another for definition.
• Adding a dollar sign ($) before the row and column will make the cell reference absolute