lecture1

Modelling and Business Decisions : Modelling and Business Decisions Robert Zimmer Room 6, 25 St James


This course is about building models and making decisions : This course is about building models and making decisions It is about organising information It is about being able to ask what-if questions It is about applying powerful mathematical models (I might try to teach you some maths when you aren’t looking but that is incidental)


Example of a decision:should I have another beer? : Example of a decision: should I have another beer? Organising Information: How much money I have How much money a beer costs How drunk am I? Do I have to drive? How fat am I? How much do I like the people in the pub? How much do I like the people at home?


Slide4 : What-if questions What if I can convince the barman to give me a half-price beer What if I decide that I like these people twice as much as I did …


Slide5 : The Maths here would be difficult Luckily for us, Microsoft has implemented a special beer decision function: the famous Beer Decision Algorithm within excel So all we need to do is put all of our figures on a spreadsheet, press the = button to pull up the formula function and type BDA(A1,A2,….) or whatever


Slide6 : Then Bob’s our uncle. We know whether to have the drink or not Because we did this on a spreadsheet it is flexible enough for us to change the parameters (that is, inputs) and find out if we should change our decisions


Another Question : Another Question What is the most money I am prepared to pay for this drink? That is at what price does the pleasure of the drink become less than its price?


Some more questions : Some more questions What is the geometric shape of all the points at which the pleasure of the beer exactly matches the pain of the payment? How will my pleasure, my weight, and my mental state compare if instead of a beer I have chips? or do my Java coursework?


After this course you will be able to answer questions like this : After this course you will be able to answer questions like this Some names for what we are studying: operations research, decision science, management science Our tool of choice: the humble spreadsheet.


It’s not just beer: example 1 : It’s not just beer: example 1 Merril Lynch 5 million customers 16,000 financial advisors Developed a model to design product features and pricing options to better reflect customer value Benefits: $80 million increase in annual revenue $22 billion increase in net assets


It’s not just beer: example 2 : It’s not just beer: example 2 Jan de Wit Co. Brazil’s largest lily farmer Annually plants 3.5 million bulbs and produces 420,000 pots & 220,000 bundles of lilies in 50 varieties. Developed model to determine what to plant, when to plant it, and how to sell it. Benefits: 26% increase in revenue 32% increase in contribution margin


Slide12 : NBC Must determine program schedules Schedules must meet advertisers demographic and cost requirements Developed optimization model to determine optimal timing and pricing of commercials Benefits: $50 million increase in annual revenue


Our modus operandi : Our modus operandi Make a mathematical model Implement it in excel Play with it to find out how the answers depend on the input variables Use the inbuilt mathematical functions to do complicated analyses Use the excel graphic packages to make diagrams


How you will learn to do this : How you will learn to do this Option 1: You will listen to me, go to the labs, and not think about the subject in between Option 2: You will not listen to me and stay home Option 3: You will listen to me and do everything I tell you to Option 4: You won’t leave off your modelling practice, even to listen to me


Models : Models Everyone uses models to make decisions. Types of models: Mental (arranging furniture) Visual (blueprints, road maps) Physical/Scale (aerodynamics, buildings) Mathematical (what we’ll be studying)


Mathematical Models : Mathematical Models Profit = Revenue - Expenses or Profit = f(Revenue, Expenses) or Y = f(X1, X2)


Characteristics of Models : Characteristics of Models Models are simplified versions of the things they represent A valid model accurately represents the relevant characteristics of the object or decision being studied So a large part of the art: is what is relevant and what can be abstracted away


Benefits of Models : Benefits of Models Economy - it is often less costly to analyze decision problems using models. Timeliness - models often deliver needed information more quickly than their real-world counterparts. Feasibility - models can be used to do things that would be impossible. Models give us insight & understanding that improves decision making.


Maths : Maths Y = f(X1, X2, …, Xn) Y = dependent variable (aka bottom-line performance measure) Xi = independent variables (inputs having an impact on Y) f(.) = function defining the relationship between the Xi & Y


Slide21 : Identify Problem Formulate & Implement Model Analyze Model Test Results Implement Solution unsatisfactory results


1.4 Seven-Step Modeling Process : 1.4 Seven-Step Modeling Process Step 1: Problem Definition - Define the problem including the objectives and the parts of the organization that must be studied. Step 2: Data Collection – Collect the data to estimate the value of parameters that affect the organization’s problem. Step 3: Model Development – Develop an analytical or simulation model. Step 4: Model Verification – Determine whether the model is an accurate representation of reality.


Slide23 : Step 5: Optimization and Decision Making – Given the model and a set of possible decisions, the analyst must choose the decision that best meets the organization’s objectives. Step 6: Model Communication to Management – The analyst presents the model and the recommendations to the organization. Step 7: Model Implementation – If the organization accepts the model then the analysts assists with implementation. Implementation must be monitored constantly to ensure that the model enables the organization to meets its objectives.


Slide24 : Models can be used for structurable aspects of decision problems. Other aspects cannot be structured easily and require intuition and judgment. Caution: Human judgment and intuition is not always rational!


Framing Effects : Framing Effects Refers to how decision-makers view a problem from a win-loss perspective. The way a problem is framed often influences choices in irrational ways… Suppose you’ve been given $1000 and must choose between: A. Receive $500 more immediately B. Flip a coin and receive $1000 more if heads occurs or $0 more if tails occurs


Slide26 : Now suppose you’ve been given $2000 and must choose between: A. Give back $500 immediately B. Flip a coin and give back $0 if heads occurs or give back $1000 if tails occurs


Chapter 2 : Chapter 2 Introduction to Spreadsheet Modeling


2.1 Introduction : 2.1 Introduction Excel skills are critical. There is an Excel tutorial on the CD-ROM that accompanies the book. Excel’s features will provide insight into solving real business problems.


2.2 Basic Spreadsheet Modeling: Concepts and Best Practices : 2.2 Basic Spreadsheet Modeling: Concepts and Best Practices Most mathematical models, including spreadsheet models, involve inputs, decision variables, and outputs. The model inputs are given values that are fixed. The decision variables are values that a decision maker has control over. The model outputs are the ultimate values of interest.


Slide31 : Spreadsheet modeling is the process of entering the inputs and decision variables into a spreadsheet and then relating them appropriately, by means of formulas, to obtain the outputs. Once a model is created there are several directions in which to proceed. Sensitivity analysis to see how one or more outputs change as selected inputs or decision variables change. Finding the value of a decision variable that maximizes or minimizes a particular output. Create graphs to show graphically how certain parameters of the model are related.


Slide32 : Good spreadsheet modeling practices are essential. Spreadsheet models should be designed with readability in mind. Several features that improve readability include: A clear logical layout to the overall model Separation of different parts of a model Clear headings for different sections of the model Liberal use of range names Liberal use of formatting features Liberal use of cell comments Liberal use of text boxes for assumptions, lists or explanations


Example 2.1 – Building a Model : Example 2.1 – Building a Model Randy Kitchell is a NCAA t-shirt vendor. The fixed cost of any order is $750, the variable cost is $6 per shirt. Randy’s selling price is $10 per shirt, until a week after the tournament when it will drop to $4 apiece. The expected demand at full price is 1500 shirts. He wants to build a spreadsheet model that will let him experiment with the uncertain demand and his order quantity.


Ex. 2.1(cont’d) - Building a Model : Ex. 2.1(cont’d) - Building a Model The logic behind the model is simple. An Excel IF function will be used.


Slide35 : In this model the profit is calculated with the formula Profit = Revenue – Cost and the Cost = 750 + 6*B4


Revenue : Revenue Case 1: Demand outstrips order (B3 > B4) In that case everything gets sold for 10 dollars Revenue is then simply 10*B4 (since B4 is the number ordered)


Revenue : Revenue Case 2:You have ordered too many. That is order (B3) is less than peak demand Then you can only sell B3 at 10 dollars and the rest (B4-B3) at 4 dollars Revenue = 10*B3+4*(B4-B3)


Revenue Formula : Revenue Formula Revenue = IF(B3>B4,10*B4,10*B3+4*(B4-B3))


Profit Formula : Profit Formula Profit = IF(B3>B4,10*B4,10*B3+4*(B4-B3)) – (750 + 6* B4)


More Flexibility : More Flexibility


Ex. 2.1(cont’d) - Building a Model : Ex. 2.1(cont’d) - Building a Model The formula can be rewritten to be more flexible. =-B3-B4*B9+IF(B8>B9,10*B8+B6*(B9-B8)) It can be made more readable by using range names. The formula would then read =-Fixed_order_cost-Variable_cost*Order + IF(Demand > Order, Selling_price*Order, 10*Demand+Salvage_value* (Order-Demand)