MIS 546 – Business Analysis and Modeling

1 / 37

# MIS 546 – Business Analysis and Modeling - PowerPoint PPT Presentation

&quot;All models are wrong; some are useful.&quot;. - George Box (eminent statistician). MIS 546 – Business Analysis and Modeling. The Art &amp; Craft of Modeling. Objectives. Basic Technique Review Absolute and relative cell addressing, R1CI, range names

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

## PowerPoint Slideshow about 'MIS 546 – Business Analysis and Modeling' - yagil

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

"All models are wrong; some are useful."

- George Box (eminent statistician)

### MIS 546 – Business Analysis and Modeling

The Art & Craft of Modeling

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
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
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
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
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

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?
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
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
A “Simple” Modeling Process

Problem definition &

system study

Model construction & Data collection

necessary corrections and enhancements

assumptions

Verification and Validation Testing

predictions

influence diagram

mathematical formulas

computer program

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

Hierarchy of Modeling Skills

Learning “modeling” versus learning “about models”

Business analytics (mgt science) tools and applications

Basic modeling skills

Numeracy and logical skills

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)

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.
• 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

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?

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.

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).

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
• 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
Prep Example 2.1 takeaways
• Inputs, output, decision variables
• IF, AND, OR
• DON’T HARD CODE VALUES!
• Range names
• Creating, pasting, applying, Manager, scope,
• Formatting to aid model understanding
• Layout and documentation
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

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?
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)
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.
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?
Ex0203-BreakevenAnalysis-template.xlsx

The relationships between inputs and outputs are relatively straight forward in this simple model. Let’s build it.

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/

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

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
• 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 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]

• 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]

• 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]

• 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”?

[Adapted from “The Art of Modeling with Spreadsheets”, Powell. S.G. and Baker, K.R., John Wiley and Sons, Inc., USA, 2004]

• Many research studies have found frightening levels of error rates in important spreadsheets used in numerous industries
• http://sprig.section.informs.org/
• 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”
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
• 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
• Before starting to build the first prototype, you will think of a bunch of reasons why the first model is "wrong“ – ignore that voice.