MIS 546 – Business Analysis and Modeling

1. "All models are wrong; some are useful." - George Box (eminent statistician) MIS 546 – Business Analysis and Modeling The Art & Craft of Modeling

2. Objectives • Basic Technique Review • Absolute and relative cell addressing, R1CI, range names • Introduce some general mathematical modeling principles • The “art and craft” of the course • Introduce spreadsheet based modeling/engineering through examples based on simple yet realistic business problems • Continue to add to growing level of Excel expertise in context of business modeling

3. An acknowledgement • I know this course will be hard for some of you • not a plug and chug course • be creative, be a problem solver • it’s good for classes to be hard, to push your intellectual envelope (a personal example) • Homework probs. often similar to some example in the chapter, and sometimes NOT • the text is not a cookbook • real problems rarely yield to cookbook answers

4. What will endure? • Barrage of improvement techniques, tools and philosophies • Quality circles, TQM, BPR, just-in-time, Japanese production methods, Lean, Six Sigma, Lean Six Sigma, Lean Six Sigma for Design… • No magic, all have something to contribute • Scientific method • Observe, classify, theoretical conjecture, experimental refutation, REPEAT • Modeling, improvement, experimentation, evaluation • Common sense and holistic view • Intuition, understanding underlying system, synthesis skills, working knowledge of the basics (physics of the process, statistical thinking, psychology, business fundamentals) • Balancing the quantitative and qualitative • Systems analysis

5. Models • Simplified representation or abstraction of reality. • Capture essence of system without unnecessary details • Models tailored for specific types of problems • Models help us understand the world • Prediction (What if?) • Optimization (What’s best?) • Often models much easier, faster, and cheaper to experiment with than the real system

6. Models provide a bridge Simplified abstraction of reality Capture essence of problem Model Problem Excel Workbook (calculations) Interpretation Decisions Analysts World “Real” World From Monahan, G., “Management Decision Making”, Cambridge University Press, 2000

7. Why do we model for decision making? • Building model forces detailed examination and thought about a problem • structures our thinking • must articulate our assumptions, preconceived notions • Model building may illuminate solution without actually using the model • Searching for general insights • form of relationship between key variables involved in decision • importance of various parameters on decisions • Example: Mystery data • Looking for specific numeric answers to a decision making problem • If we add 1 lab tech between 7a-3p, how much reduction can we expect in test turnaround time? • Foam • Find the best way to do something • Which routing schedule minimizes our delivery costs?

8. Sampling of a few types of models • Physical – cars, buildings, dummies, bubbles, CommonCraft Show • Diagrams – E-R, flow chart, UML class diagram, blueprints, decision trees, influence diagrams • Statistical – regression equation, probability distribution • Mathematical – F=MA, Pythagorean theorem of baseball (?), the queueing model in the 7-11 example, SchedulingDSS example • Computer simulation – discrete event, monte-carlo, systems dynamics, agents, CGI (the late Benoit Mandelbrodt and his amazing sets) • Computational – neural networks, ant colonies, genetic algorithms, swarms, flocks

9. A 7-step (Idealized) Modeling Process (PMS 1.4) • Define the problem • “exploring the mess” • Observe system / collect data • Formulate model(s) • Much “art and craft” • Verify/validate model and use for prediction and exploration of system being modeled • Use model to help select among alternatives • Present results to decision makers • Implement solution and evaluate outcomes

10. A “Simple” Modeling Process Problem definition & system study questions about real system Model construction & Data collection necessary corrections and enhancements assumptions Verification and Validation Testing predictions influence diagram mathematical formulas computer program Spreadsheets/databases Verification: Making sure the model does what you think it does and Validation: that it captures reality well enough to be useful test cases walk-throughs compare with real system Exercise the model

11. Hierarchy of Modeling Skills Learning “modeling” versus learning “about models” Business analytics (mgt science) tools and applications Advanced modeling skills Basic modeling skills Numeracy and logical skills

12. Basic Modeling Concepts Inputs relationships Outputs roles in model constraints Decision Variables relationships FYI: The above is NOT an influence chart (you’ll see in a minute)

13. Heuristic 1 - Simplify the Problem • Focus on the connections between the key decisions to be made and the outcomes of those decisions. • Identify the central trade-offs that make these decisions difficult and try to build a model that helps explore those tradeoffs. • Start with a “low resolution” model • Resolution = scope + level of detail • Do not hesitate to make “heroic assumptions” • Assume something to be true that you know not to be true • Assume you know something you know you don’t know Adapted from “The Art of Modeling with Spreadsheets”, Powell. S.G. and Baker, K.R., John Wiley and Sons, Inc., USA, 2004

14. Heuristic 2 - Decompose the Problem • Can we break up the problem in such a way that we create submodels for each part? • Submodels should be smaller and easier to model • What is linkage between submodels?

15. Model Inputs & dec. var outputs Heuristic 3 - Identify inputs and outputs ! • Categorizing variables • inputs, parameters • decision variables • performance measures, outputs Don’t worryso much at this point about how you’re going to get from the inputs to the outputs.

16. Starting to Model: Influence Charts Base input Decision variable Intermediate variable Output variable influential relationship Let’s first build one for very simple example - Prob2.20 on p62 (p64 in 3e) and then build influence diagram for Copy Shop problem (Prob 2.22).

17. Creating influence charts - Handout • Somewhat of a loosely structured approach to sketching model relationships • Each variable becomes a shape • No variable should appear more than once • Start with the final output and work backwards • What immediate inputs needed to get final output? • Continue to work backwards from these intermediate inputs until you reach base inputs (e.g. numbers) asking same question • Arrow pointing from shape A to shape B means A influences B • Don’t worry about details of how A influences B • The final output should NOT have any outgoing arrows. All other shapes should have at least one arrow coming out of it • Base inputs will NOT have any incoming arrows • Make up special symbols for special quantities • I use double lined shape borders for uncertain variables

18. Prep Example 2.1 takeaways • Inputs, output, decision variables • Basic spreadsheet design • IF, AND, OR • DON’T HARD CODE VALUES! • Range names • Readability • Creating, pasting, applying, Manager, scope, • Formatting to aid model understanding • Layout and documentation

19. Prep Example 2.2 takeaways • Numeric and graphical “what if?” • Absolute and relative cell addressing for effective formula copying • Distinguishing base input cells • Thinking about structure of outputs • Row vs. column orientation • Charting basics

20. Spreadsheet modeling basics Formulas Inputs Outputs • Plan general structure and format of model • use influence diagrams for logical structure • blank spreadsheet like a “blank canvas” – plan the physical structure • Enter inputs (parameters) and decision variables • Develop relationships between them via formulas to the model outputs • Then we can “exercise the model” • use it to explore situation of interest • What If? or What’s Best?

21. Practice Model Building • Modeling principles • Previous and following slides have a number of general modeling principles and heuristics • We will discuss them while we build a Break Even Analysis model • You can find Ex0203-BreakevenAnalysis-template.xlsx within the Downloads zip file for this module • Spreadsheet design and development practices • Excel techniques • Recommend reading/working/understanding all the Examples in Chapter 2 (Ex 2.1 – Ex 2.6)

22. In-Class Modeling CaseFinding Break-Even Point - Handout • The Quality Sweaters Company sells hand-knit sweaters. They are planning to print a brochure of its products and undertake a direct mail campaign. • The cost of printing the brochure is $20,000 plus $0.10 per catalog. The cost of mailing each catalog is $0.15. In addition, the company will include direct reply envelopes in its mailings. It incurs $0.20 in extra cost for each direct mail envelope that is used by a respondent. • The average size of a customer order is $40, and the company’s variable cost per order averages around 80% of the order’s value.

23. Break Even Case -- continued • The company plans to mail 100,000 catalogs. It wants to develop a spreadsheet model to answer the following questions: • How does a change in the response rate affect profit? • For what response rate does a company break even? • If the company estimates a response rate of 3%, should it proceed with the mailing? • How does the presence of uncertainty affect the usefulness of the model?

24. Ex0203-BreakevenAnalysis-template.xlsx The relationships between inputs and outputs are relatively straight forward in this simple model. Let’s build it.

25. Numeracy and logical skills • Make quick rough numerical estimates • Reasonable value for response rate in Quality Sweaters case? • Use special cases to test limits of calculation • Response rate of 0%, 100% • Check consistency of units • “smell test” • Does it seem reasonable that profits increase linearly as response rate rises all the way to 100%? http://xkcd.com/687/

26. More on inputs and outputs • Use simple graphical techniques such as influence charts to help you conceptualize which inputs affect which outputs. • Many variables will be intermediate inputs and/or outputs – i.e. they will end up as formulas • Base inputs are those that you will enter as numbers • Draw simple graphs that might show shape of outputs as function of inputs. Visualize

27. Exercising the ModelThings we might do • How do input and/or decision variable values affect outputs (“what if?” and sensitivity analysis)? • Find values of decision variables that minimize or maximize the outputs (optimization) • Create graphic or symbolic representation of model parameter relationships (visualization, data exploration) • Communicate with others about the nature of the problem • Example: AutoLeaselator.xls

28. A few spreadsheet design tips • Clear, logical layout of overall model • Sketch out on paper if that helps you • Separation of different model parts across multiple worksheets or different sections of same worksheet • Clear headings for different model sections and the inputs, outputs and decision variables • DON’T HARD CODE VALUES INTO FORMULAS! • Isolate your input parameters so that it is clear that these values can be changed by the user (which might be you) • Start small – get individual sections designed and working, add complexity as needed • Design for use • Anticipate how you or others will use the spreadsheet • Think about navigation • Think about extensibility • Think about data integrity • Think about protecting worksheet from unwanted changes Adapted from [from “The Art of Modeling with Spreadsheets”, Powell. S.G. and Baker, K.R., John Wiley and Sons, Inc., USA, 2004]

29. A few spreadsheet design tips • Keep it simple • Break up massive formulas into pieces • Use range names • Design for communication • Use visual cues to guide user • Judicious use of formatting and conditional formatting • Dynamic labels (formulas that return different text values depending on value in other cells) • Document important data and formulas • Cell comments and text boxes • Explanation sheets • Range names are a form of documentation [Adapted from from“The Art of Modeling with Spreadsheets”, Powell. S.G. and Baker, K.R., John Wiley and Sons, Inc., USA, 2004]

30. A few spreadsheet construction tips • Build one “module” at a time • Do formula auditing as you go by predicting results of each formula • Learn to be a formula copy and paste expert • Thoroughly understand relative, absolute, and mixed cell referencing • Understand how range names behave when copied (even across sheets) • Try to plan structure of sheet to take advantage of copying and pasting • Use the function wizard to learn about and ensure correct function syntax • Use range names but don’t range name everything [Adapted from “The Art of Modeling with Spreadsheets”, Powell. S.G. and Baker, K.R., John Wiley and Sons, Inc., USA, 2004]

31. A few spreadsheet testing tips • Check plausibility of numbers (“smell test”) • Make rough estimates, calculator, extreme cases • Test with contrived input data to make an error very noticeable • Check formula correctness • Manually (by yourself and others) • Display individual cell references • Display all formulas – Control-~ • Use Auditing Toolbar • Use built in Error Checking – ISERROR() • Check plausibility of model • Use your model with a variety of inputs • Does its performance pass the “smell test”? • Do sensitivity analysis (we will talk much more about this) [Adapted from “The Art of Modeling with Spreadsheets”, Powell. S.G. and Baker, K.R., John Wiley and Sons, Inc., USA, 2004]

32. Errors in Spreadsheet Models • Many research studies have found frightening levels of error rates in important spreadsheets used in numerous industries • What we know about spreadsheet errorshttp://pubpages.unh.edu/~rbg/ss_research/papers/panko_2000_whatknow.pdf • http://sprig.section.informs.org/ • http://www.sciencenews.org/view/generic/id/6866/title/Math_Trek__The_Risky_Business_of_Spreadsheet_Errors • Spreadsheet oops http://www.eusprig.org/stories.htm • The Spreadsheet Projecthttp://mba.tuck.dartmouth.edu/spreadsheet/ • Spreadsheet Analyticshttp://www.usfca.edu/research/spreadsheetanalytics/ • Nature of end-user spreadsheet development • non-IS developers, ad-hoc, iterative, under time pressure • spreadsheets are very flexible development environment • designed for “personal use”

33. Heuristic 4 - Build a prototype and refine it • Toys are good • Try to get a simple model working. • By "working" we mean, it takes some inputs, does some sort of calculation and creates some outputs. • Make whatever assumptions are needed to try to get a model working. • Document these assumptions. • Use metaphors, analogies, similarities • Bottlenecks • Airlines and hotel industry models for “perishable” capacity

34. Heuristic 5 - Identify Parameters and Perform Sensitivity Analysis • One of the primary reasons we build models is to change certain values and see what happens to outputs of interest. • Some parameters we might do sensitivity analysis on because we are unsure of their values • Other parameters we might do sensitivity analysis on because we actually have some control over their value • Excel lends itself well to sensitivity analysis via copying formulas, data tables, graphs, scenarios, and even simulation • Parameterization – “call it alpha” • Sales = f(a,Price), Example: Sales = 1000-a*Price

35. Heuristic 6 - Separate the creation of ideas from their evaluation. • Before starting to build the first prototype, you will think of a bunch of reasons why the first model is "wrong“ – ignore that voice. • Quiet the critic in your head • When in the idea phase (divergent thinking) don't do too much evaluation as you may miss an insightful approach. • It's analogous to common brainstorming techniques. • Accept that modeling may feel like “muddling through” • many “right” answers

36. Heuristic 7 - Work backward from the desired answer • Try to imagine what the "answer(s)" or "deliverable(s)" will look like and then figure out how to get there. • Back in to the answer – vary the inputs to get the answer you want • Example: Goal Seek to find break even point • Will the answer be a single number, a graph, a table of numbers, a probability, a range, some combination of these? • Invoke Heuristic 6 and don't criticize your "answer" too much as being undoable. • This technique can help overcome modelers block when you just aren't sure how to even get started. • You can try to create "The Magic One Pager“ to help you visualize the answer.

37. A Magic One Pager example