spreadsheet engineering l.
Skip this Video
Loading SlideShow in 5 Seconds..
Spreadsheet Engineering PowerPoint Presentation
Download Presentation
Spreadsheet Engineering

Loading in 2 Seconds...

play fullscreen
1 / 20

Spreadsheet Engineering - PowerPoint PPT Presentation

  • Uploaded on

Spreadsheet Engineering. Builders use blueprints or plans Without plans structures will fail to be effective Advanced planning in any sort of design can speed up implementation Spreadsheets are no different from other arenas of design Engineering and advanced planning aids effectiveness.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

Spreadsheet Engineering

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
spreadsheet engineering
Spreadsheet Engineering
  • Builders use blueprints or plans
    • Without plans structures will fail to be effective
  • Advanced planning in any sort of design can speed up implementation
  • Spreadsheets are no different from other arenas of design
    • Engineering and advanced planning aids effectiveness
spreadsheet design
Spreadsheet Design
  • An efficient process minimizes time spent
  • An effective process yields results that meet users’ requirements
  • Good design helps analysts spend the majority of their effort improving decisions, rather than building and fixing models
the phases of spreadsheet modeling
The Phases of Spreadsheet Modeling
  • Designing
  • Building
  • Testing
designing a spreadsheet
Designing a Spreadsheet
  • Plan
  • Modularize
  • Start small
  • Design for use
  • Keep it simple
  • Design for communication
  • Document important data and formulas
  • “Measure twice, cut once”
  • Will decrease time spent correcting mistakes
  • Turn computer off and think before beginning
  • Begin with a sketch
    • Physical layout of major elements
    • Rough indication of calculation flow
  • Anticipate model’s ultimate uses
  • Group like items and separate unlike items
  • Separate
    • Data
    • Decision variables
    • Outcome measures
    • Detailed calculations
  • Influence diagrams aid with this design
  • Formulas should generally reference cells above and to the left
start small
Start Small
  • Sketch full design but do not build all at once
  • Isolate one module then build and test that module
  • Local mistakes much easier to detect than when part of the global model
design for use
Design for Use
  • Anticipate who will use spreadsheet
    • What type of questions will be asked?
  • Make it easy to change common parameters
  • Make it easy to find key outputs
    • Group in one place
  • Include graphs of outputs
  • Record numerical values of base case outputs
keep it simple
Keep It Simple
  • Complex spreadsheets:
    • Require more time and effort to build
    • Are much more difficult to debug
  • Keep formulas short
    • Decompose complex calculations into intermediate steps
design for communication
Design for Communication
  • Spreadsheets’ lives are often longer than expected
  • Use visual cues that reinforce model’s logic
    • Use informative labels
    • Use blank spaces
    • Use outlines, color, bold fonts, as appropriate
  • Split windows can aid in viewing
document important data and formulas
Document Important Data and Formulas
  • Record source for important parameters
  • Explain important formulas
  • Use Cell Comments to describe cell contents
  • Consider a separate module to list assumptions
cell comments
Cell Comments
  • Insert – Comment to add documentation to a cell
    • Comment & indicator – permanently display comment
    • Indicator – red triangle indicates comment, display when cursor in cell
    • None – neither comment nor indicator visible
workbook design
Workbook Design
  • Use separate sheets to group similar kinds of information
  • Design workbooks for ease of navigation
use separate sheets to group similar kinds of information
Use Separate Sheets to Group Similar Kinds of Information
  • Makes model easier for outsider to use
  • Isolate technical details of model
  • Bring assumptions and results to the fore
  • Users should only interact with a few, easily recognized, sheets
design workbooks for ease of navigation
Design Workbooks for Ease of Navigation
  • Any form of structural help for users is beneficial
  • Use revealing names for individual sheets
    • Double-click on name tab at bottom of spreadsheet to edit name
building a spreadsheet
Building a Spreadsheet
  • Follow a plan
  • Build one module at a time
  • Predict the outcome of each formula
  • Copy and paste formulas carefully
  • Use relative and absolute addressing to simplify copying
  • Use the Function Wizard to ensure correct syntax
  • Use range names to make formulas easy to read
  • Use dummy input data to make errors stand out
testing a spreadsheet
Testing a Spreadsheet
  • Check that numerical results look plausible
  • Check that formulas are correct
  • Test that model performance is plausible
check that numerical results look plausible
Check That Numerical Results Look Plausible
  • Make rough estimates
  • Check with a calculator
  • Test extreme cases
check that formulas are correct
Check That Formulas Are Correct
  • Check manually
  • Display individual cell references
  • Display all formulas
  • Use the auditing tools
  • Use error checking
  • Spreadsheets deserve careful engineering
    • Most spreadsheets contain errors
    • Users are over confident about their models
  • Rules for spreadsheet modeling
    • Designing a spreadsheet
    • Designing a workbook
    • Building a spreadsheet
    • Testing a spreadsheet