Chapter 9: Building and Using Decision Models 2007 Pearson EducationModelsnA model is an abstraction or representation of a real system, idea, or object. nPicturenSpreadsheetnMathematical relationshipsTypes of Decision ModelsnDescriptive - describe relationships and provide information for evaluation nPrescriptive (optimization models) - determine an optimal policy, that is, the best course of action that a decision maker should take to maximize or minimize some objective New Store Financial Analysis Model Spreadsheet Net Present ValuenMeasures the worth of a stream of cash flows, taking into account the time value of money. nA cash flow of F dollars t time periods in the future is worth F/(1 + i)t dollars today, where i is the discount rate. Building Decision Models on SpreadsheetsnOrganize information into a logical framework that will make the spreadsheet easier to develop and reduce errors nSketch a logical design of the spreadsheet nSeparate the model inputs from the model itself and to reference the input cells in the model formulas ExamplenProfit = Revenue - Cost nRevenue depends on the unit price and quantity sold. nTotal cost depends on the unit cost, quantity produced, and relevant fixed costs. nProfit = (Unit price)(Quantity sold) - Cost nProfit = (Unit price)(Quantity sold) - Fixed cost + (Unit cost)(Quantity produced)Profit Model SpreadsheetElements in ModelsnConstant data nUncontrollable inputs, quantities that can change, but cannot be directly controlled by the decisionmaker. nControllable inputs, often called decision variables. Spreadsheet QualitynImprove the design and format of the spreadsheet itself.nImprove the process used to develop a spreadsheet.nInspect your results carefully and use appropriate tools available in Excel.nUse cell and range namesnUse the Data Validation toolnInspect and audit formulasModel AssumptionsnAll models reflect assumptions used by the modeler.nAssumptions simplify models and make them easier to manipulate and solvenAssumptions should be as realistic as necessary to make models useful but not overly complexnAssumptions should be clearly stated and documentedOptimization ModelsnSales = -2.6556 Price + 3016.2 nRevenue = Price (-2.6556 Price + 3016.2) = -2.6556(Price)2 + 3016.2 PricenFind the best value of price to maximize revenueModels Involving UncertaintynUncontrollable inputs often exhibit random behavior, which must be incorporated into modelsnSpecify probability distributions for the appropriate uncontrollable inputs. nExample: assume that demand is normally distributed with a mean of 50,000 and a standard deviation of 10,000 units. nModels that include randomness are called probabilistic, or stochastic, models. Distribution FittingFor clues: Examine descriptive statistics Examine histogramNormal Probability PlotPHStat Normal Probability PlotDistribution Fitting With Crystal BallDefine Assumption buttonCrystal Ball Distribution GalleryFit Distribution DialogDistribution Fitting Comparison Chart Goodness of Fit StatisticsModel AnalysisnScenario evaluation evaluate specific combinations of model inputs that reflect key model assumptions. nSensitivity analysis (what if analysis) - the process of changing key model inputs to determine their effect on the outputs Data TablesnUsed for sensitivity analyses on variables of a decision modelnExcel toolsnOne-way data tablesnTwo-way data tablesOne Way Data TableTwo Way Data TableTornado ChartnGraphically shows the impact that variation in one model input has on some output while holding all other inputs constant Spider ChartnShows the sensitivity that percentage changes in inputs have on a model output. Solving Optimization ModelsnAnalytical solutions closed-form mathematical formulasnAlgorithm - a systematic procedure that finds a solution to a problem nHeuristics - solution procedures that generally find good solutions without guarantees of finding an optimal solution Practical IssuesnManagers may not always need exact optimal solutionsnInexact or limited data used to estimate uncontrollable quantities in models may contain more error than that of a non-optimal solution. nThe assumptions used in a model make it an inaccurate representation of the real problem, making having the best solution pointless. nManagers may not need the very best solution; anything better than the present one will often suffice, so long as it can be obtained at a reasonable cost and in a reasonable amount of time. Excel SolvernMaximize Revenue = -2.6556(Price)2 + 3016.2 Price Spreadsheet Model Solver ModelSolution:Price = $567.89Revenue = $856,441.34