Spreadsheet EngineeringSpreadsheet Engineering““Training in spreadsheet modeling improves Training in spreadsheet modeling improves

both the efficiency and effectiveness with both the efficiency and effectiveness with which analysts use spreadsheets” which analysts use spreadsheets”

Steve Powell, Dartmouth CollegeSteve Powell, Dartmouth College

The Modeling ProcessThe Modeling Process

Frame the problemFrame the problem

Diagram the problem Diagram the problem • Influence chartsInfluence charts

Build a modelBuild a model

Analyze for insightsAnalyze for insights

Problem FramingProblem Framing

What’s included, what’s excluded?What’s included, what’s excluded? What can we assume?What can we assume? What do we know?What do we know? What are our objectives?What are our objectives? What are our decisions or options?What are our decisions or options? What are the constraints?What are the constraints?

Example Problem 1Example Problem 1

Determine the price we should set for Determine the price we should set for our product so as to generate the our product so as to generate the highest possible profit this coming year.highest possible profit this coming year.

Influence ChartsInfluence Charts

Use to break through modeler’s blockUse to break through modeler’s block

Help organize the approach to modelingHelp organize the approach to modeling

Begin model documentationBegin model documentation

Picture the connections between the Picture the connections between the inputs and outputs without defining the inputs and outputs without defining the math logicmath logic

Building Influence DiagramsBuilding Influence Diagrams

Start with a key output Start with a key output • What single measure will decision-maker use to What single measure will decision-maker use to

assess the success of the project?assess the success of the project?

Decompose the output measure into a small set Decompose the output measure into a small set of variables that determine it directlyof variables that determine it directly

Take each influencing variable and repeat this Take each influencing variable and repeat this process of decomposition until all outputs are process of decomposition until all outputs are defineddefined

Make sure that each variable appears only onceMake sure that each variable appears only once

Symbols Needed in Influence Symbols Needed in Influence DiagramsDiagrams

Objective/ Key Output

Variable

Fixed Input Parameter

Random Input Parameter

Decision

Model Building: Model Building: Spreadsheet Design GuidelinesSpreadsheet Design Guidelines

When organizing the data, common input When organizing the data, common input variables should be presented together and variables should be presented together and labeledlabeled

Model results should be clearly labeled and Model results should be clearly labeled and emphasizedemphasized

Physical results should be separated from the Physical results should be separated from the financial resultsfinancial results

Units of measure should be documented Units of measure should be documented where appropriatewhere appropriate

Figure 3.18 of textFigure 3.18 of text

Model DocumentationModel Documentation

Do not embed constants; Place in Do not embed constants; Place in separate module of worksheets with separate module of worksheets with appropriate labels and color shadingappropriate labels and color shading

Attach cell notes/commentsAttach cell notes/comments

Use text boxesUse text boxes

Use range namesUse range names