TM 631 Optimization Fall 2006 Dr. Frank Joseph Matejcik

1
TM 631 Optimization Fall 2006Dr. Frank Joseph
Matejcik
8th Session Ch. 21 The Art of Modeling with
Spreadsheets 10/30/06
2
Activities

• Review assignments and resources
• Hand back exams
• Assignment
• weird way of numbering problems
• Chapter 21 H L NO assigned exercises
• Chapter 21 H L

3
Tentative Schedule
Chapters Assigned 8/28/2006 1,
2 ________ 9/04/2006 Holiday 9/11/2006 3
3.1-8,3.2-4,3.6-3 9/18/2006 4 4.3-6, 4.4-6,
4.7-6 9/25/2006 6 6.3-1, 6.3-5, and
6.8-3(abce) 10/02/2006 Exam 1 10/09/2006 Holiday 1
0/16/2006 8 8.1-5, 8.1-6, 8.2-6, 8.2-7(ab),
8.2-8 10/23/2006 8 8.4 Answers in Slides
HPCNET 10/30/2006 21 No problems 11/06/2006 Exam 2
Chapters Assigned 11/13/2006 9
11/20/2006 9 cont. 11/27/2006 11 12/04/2006 11
or 13 12/11/2005 Final
4
Exam 2 Next week

• 1 Transportation Problem
• 2 Assignment Problem
• Short answer questions concerning building models
  with spreadsheets
• Open book, Open Notes, 3 hour time limit.
• Exam is not yet prepared, Study guide to follow.

5
Web Resources

• Class Web site on the HPCnet system
• http//sdmines.sdsmt.edu/sdsmt/directory/courses/2
  006fa/tm631021
• Streaming video http//its.sdsmt.edu/Distance/
• The same class session that is on the DVD is on
  the stream in lower quality. http//www.flashget.c
  om/ will allow you to capture the stream more
  readily and review the lecture, anywhere you can
  get your computer to run.
• Answers have been posted through chapter 6 and
  for section 8.4

6
21.0 The Art of Modeling with Spreadsheets

• Provides an alternative way of displaying a
  mathematical model
• Spreadsheet software (Solver) includes basic OR
  algorithms
• spreadsheet models can be solved as soon as they
  have been formulated
• Modeling in spreadsheets is more an art than a
  science.

7
21.0 The Art of Modeling with Spreadsheets

• The Recommended process has four major steps
• plan the spreadsheet model,
• build the model,
• test the model, and
• analyze the model and its results.
• There is no guarantee that the plan-build-test-ana
  lyze process will lead to a good spreadsheet
  model.

8
21.1 CASE STUDY EVERGLADE GOLDEN YEARS CASH FLOW

• Founded in 1946 by Alfred Lee.
• Sheldon Lee is the CEO.
• Julie Lee is the chief financial officer (CFO).
• Everglade will have negative cash flow for the
  next few years.
• Two types of loans offered (combined, too)
• Fixed rate 7 interest only with balloon
• Series of one yeear loans projected at 10

9
Tab 21.1 Everglade Net cash flows
10
21.2 OVERVIEW OF THE PROCESS OF MODELING WITH
SPREADSHEETS

• The temptation is to jump right in, launch Excel,
  and start entering a model. Resist this urge.
• A spreadsheet model without proper planning often
  leads to a model that is poorly organized
  difficult to interpret.
• Steps in fig. 21.1, sometimes do mentally

11
Fig 2.1 Flow diagram for plan-build-test-analyze
12
21.2 Plan Define the Problem Gather the Data

• What are the decisions to be made?
• What are the constraints on these decisions?
• What is the overall measure of performance for
  these decisions?
• Need members of management who are responsible
  for making the decisions.
• Enables an OR analyst (or team) to identify the
  right problem
• Start the sometimes lengthy process of gathering
  data

13
21.2 Plan Define the Problem Gather the Data

• Back to the Everglades example
• Julie (CFO) had much of the relevant data from
  her dealings with the bank and her projections of
  cash flow.
• Sheldon (CEO) imposed a constraint of maintaining
  gt 500,000 in cash reserves
• Sheldon (CEO) identified the objective as
  maximizing the cash balance at the end of the 10
  years after paying off all the loans.

14
21.2 Plan Visualize Where You Want to Finish

• What information should Julie provide in her
  report to Sheldon?
• What should the answer look like when
  presenting the recommended approach to the
  problem?
• What kinds of numbers need to be included in the
  recommendation?
• Lead you to the heart of the problem and help get
  the modeling process started.

15
21.2 Plan Visualize Where You Want to Finish

• The long-term loan is a single lump sum.
  Therefore, the answer should include a the
  single lump sum.
• The short-term loan can be taken in any of the 10
  years, so the answer should include 10 numbers
  indicating how much to borrow in each year.
• Loan amounts will be the changing cells.

16
21.2 Plan Visualize Where You Want to Finish

• What other numbers should Julie include
• in her report to Sheldon?
• the projected cash balance at the end of each
  year
• the amount of the interest payments
• when loan payments are due
• The above will be output cells
• Now, you have an idea of how the answer will
  look.

17
21.2 Plan Do Some Calculations by Hand

• Common stumbling block can arise when trying to
  enter a formula in one of the output cells.
• How does Julie track the cash balances?
• What formulas need to be entered?
• It is easy to get overwhelmed.
• Suggestion Pick some numbers for the changing
  cells determine the results with a calculator
  (or pencil and paper).

18
Julies Hand Calculations
19
Julies Hand Calculations
20
Julies Hand Calculations

• 1st, it clarifies what formula should be in an
  output cell. Looking at the by-hand calculations,
  it appears that the formula for the ending
  balance for a particular year is
• Ending balance starting balance cash flow
  loans - interest payments - loan paybacks.
• 2nd, hand calculations help verify models.

21
21.2 Plan Sketch Out a Spreadsheet

• Any model typically has a large number of
  different elements.
• A stumbling block is the layout.
• Where should all the pieces fit on the
  spreadsheet?
• How do you begin putting together the spreadsheet?

22
21.2 Plan Sketch Out a Spreadsheet

• Simply sketch out blocks on a piece of paper for
  the various data cells, changing cells, and
  output cells, and label them.
• Concentrate on the layout. Columns? Rows? Tables?
  
• Arrange the blocks in consistently to reduce
  headings.
• The target cell at the bottom.

23
fig. 21.2 Sketch of the spreadsheet for
Everglades
24
21.2 Plan Sketch Out a Spreadsheet

• It is generally easiest to start sketching the
  data. The rest of the model should then follow
  the layout of the data cells.
• The cash flows data are sketched as a vertical
  column (in years), then the other cash flows
  should layout the same way.
• The spreadsheet progresses logically. The data
  are at the top and left of the spreadsheet. The
  calculations flow to the bottom right target
  cell.

25
21.2 Build Start with a Small Version of the
Spreadsheet

• You may want to start by building a small,
  readily manageable version of the model. Make
  sure that youve got the logic of the model
  worked out correctly for the small version before
  expanding the model to full size.
• Works well for time related.
• Other types do the simpler cells first.
• Use range names.

26
Fig. 21.3 A small version (2003 -04)
27
21.2 Test Test the Small Version of the Model

• If you do start with a small version of the model
  first, be sure to test this version thoroughly.
  It is far easier to fix a problem early, while
  the spreadsheet is still a manageable size.
• Test by hand calculations.

28
21.2 Build Expand the Model to Full-Scale Size

• Small model can be expanded to full size.
• Excels fill commands often can be used to
  quickly copy the formulas.
• relative reference (usual)
• absolute reference ( or named cells)

29
fig. 21.4 complete spreadsheet model for the
Everglade
30
fig. 21.4 complete spreadsheet model for the
Everglade
31
21.2 Test Test the Full-Scale Version of the
Model

• Test similarly to the small model
• Also, use the debugging tools of section 21.4

32
21.2 Analyze Analyze the Model

• Run Solver
• Check the results

33
Fig. 21.5 A complete model after calling Solver
34
Fig. 21.5 A complete model after calling Solver
35
Fig. 21.5 A complete model after calling Solver
36
21.2 Conclusion of the Case Study

• Sheldon Julie meet to discuss her report.
• Need for sensitivity analysis
• Sheldon and Julie agree that the plan is sound,
  and the adjustments could be made in the short
  term loans.
• Model can be adjusted for future changes.

37
21.3 SOME GUIDELINES FOR BUILDING GOOD
SPREADSHEET MODELS

• It is possible to build spreadsheets that are
  hard to read.
• These methods should help.
• range names, shading, borders make spreadsheets
  easy to read

38
fig. 21.6 model for Wyndor Glass
39
fig. 21.6 model for Wyndor Glass
40
21.3 Enter the Data First

• Carefully lay it out
• Allow the other cells to follow the same format
  (example same rows for years)
• Wyndor model centered the data
• Generally, good to have other parts follow the
  data.

41
21.3 Organize Clearly Identify Data

• Use labels
• Also, include units
• Dollar format for numbers gives units implicitly
• Use SUMPRODUCT function (saves cells)

42
21.3 Enter Each Piece of Data into One Cell Only

• If you dont consider.
• Corrections required
• What if sensitivity analysis
• Adapting for a simulation

43
21.3 Separate Data from Formulas

• First, all the data are visible on the
  spreadsheet rather than buried in formulas, so
  the model is easier to interpret.
• Second, easier to modify since changing data only
  requires modifying the corresponding data cells.
  Accordingly, sensitivity analysis is easier.

44
21.3 Keep it Simple

• Avoid complicated Excel functions
• stick to SUMPRODUCT or SUM functions
• Spread formulas across cells to help keep
  functions simple

45
21.3 Use Range Names

• Do it by selecting the block, click Name\Define
  on the Insert menu, and type a name.
• Using range names makes the formula easier to
  interpret.
• Range names help in the Solver dialogue.
• Too many range names can be trouble
• Use names corresponding to labels.

46
21.3 Use Relative and Absolute Referencing for
Copying Formulas

• Whenever multiple related formulas will be
  needed, try to enter the formula just once and
  then use Excels fill commands to replicate the
  formula. Not only is this quicker than retyping
  the formula, but it is also less prone to error.

47
21.3 Use Borders, Shading, and Colors to
Distinguish Cell Types

• It is important to be able to easily distinguish
  between the data cells, changing cells, output
  cells, and target cell in a spreadsheet. One way
  to do this is to use different borders and cell
  shading for each of these different types of
  cells.
• Colors can help
• Color concerns color blindness, printers

48
21.3 Show the Entire Model on the Spreadsheet

• Include elements of the model (such as the , ,
  or signs and/or the right-hand sides of the
  constraints) on the sheet
• Printouts do does not include information from
  the Solver dialogue box.
• You should not need to go to the Solver dialogue
  box to determine any element of the model.

49
21.3 A Poor Spreadsheet Model

• It is certainly possible to set up a linear
  programming spreadsheet model without utilizing
  any of these ideas. Figure 21.7 shows an
  alternative spreadsheet formulation for the
  Everglade problem that violates nearly every one
  of these guidelines.

50
fig. 21.7 poor spreadsheet for Everglade
51
fig. 21.7 poor spreadsheet for Everglade
52
21.4 DEBUGGING A SPREADSHEET MODEL

• No matter how carefully it is planned built,
  models usually will not be error-free at first.
• Try values for the changing cells that you can
  predict results and see if they calculate as
  expected.
• Calculations are behaving reasonably for extreme
  cases.
• Range names should refer to the correct cells.
• Carefully study each formula. Use the toggle to
  switch between viewing the formulas and viewing
  results. Typing control- on a PC displays the
  formulas.

53
(No Transcript)
54
21.4 Audit Tool Bar

• Choosing Show Auditing Toolbar from the Auditing
  item in the Tools menu. The Auditing Toolbar is
  to display which cells make direct links to a
  cell.

55
(No Transcript)
56
(No Transcript)
57
21.5 CONCLUSIONS

• General Process
• build the model
• plan the spreadsheet model
• test the model
• analyze the model

58
21.5 CONCLUSIONS

• Guidelines for building good spreadsheet
  models.
• Enter the data first.
• Organize and clearly identify the data.
• Enter each piece of data into one cell only.
• Separate data from formulas.
• Keep it simple.
• Use range names.
• Use relative absolute references for copying
  formulas.
• Use borders, shading, and colors for cell types.
• Show the entire model on the spreadsheet.

59
21.5 CONCLUSIONS

• Toggle the worksheet between viewing the results
  in the output cells and the formulas entered into
  those output cells.
• Several other helpful features are available from
  Excels Auditing Toolbar.

60
CASE 21.1 Prudent Provisions for Pensions

• Instructions are to follow many of the steps

61
Exam 2 Next week

• 1 Transportation Problem
• 2 Assignment Problem
• Short answer questions concerning building models
  with spreadsheets
• Open book, Open Notes, 3 hour time limit.
• Exam is not yet prepared, Study guide to follow.