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 10/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/2006fa/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.com/ 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-analyze 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 > $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. Julie’s Hand Calculations

19. Julie’s Hand Calculations

20. Julie’s 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 Everglade’s

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 you’ve 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. • Excel’s 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 don’t 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 Excel’s 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