280 likes | 343 Views
Business Software Tools 200. Topic 2 Modelling. Why Model?. Why Model?. Focus on key aspects Perform “What if?” (sensitivity analysis) Make predictions Improve (shared) understanding Reduce uncertainty Solve problems…. Good models. Are parsimonious
E N D
Business Software Tools 200 Topic 2 Modelling
Why Model? • Focus on key aspects • Perform “What if?” (sensitivity analysis) • Make predictions • Improve (shared) understanding • Reduce uncertainty • Solve problems…
Good models • Are parsimonious • Have just enough variables to explain the variation • Too many variables create complexity and confusion and may mislead (model is overfitted) • Too few variables don’t explain (model is underfitted)
Good models ctd. • Provide explanation • Give accurate predictions • Correlate with the “real world” • Have face validity • Are scalable, extensible • Understandable by others • documentation
Modelling • financial • forecasting • simulation/risk analysis • MCDM (multi criteria decision making) • goal seeking/optimisation/linear programming
Excel Add-Ins • Analysis ToolPak • Solver
Financial models • “adjusting for the time value of money based on interest, depreciation, inflation rates” • NPV (net present value) • IRR (internal rate of return) • DCF (discounted cash flow) • Amortisation (loan repayments)
Financial models • Software • Excel financial functions
Forecasting • “predicting future values based on past patterns of a time series” • trend • regression • exponential smoothing • decomposition • Box Jenkins
Forecasting • Software • Excel statistical functions
Simulation/Risk Analysis • “either the modelling of a process or a form of sensitivity analysis where many “what ifs” are performed” • Systems Dynamics • dynamic • based on store/flow/control • difference equations
Simulation/Risk Analysis • Monte Carlo • variation of given variables according to an assumed statistical pattern • static • Example software • @Risk by Palisade • Works with Excel • http://www.palisade.com.au/risk/
Simulation/Risk Analysis • discrete • snapshot of model is triggered by an event • continuous • snapshot of model is triggered at regular time intervals • visual • interface • iconic • abstract .... real
Regression • statistical model • dependent, independent variables • may be time based • software • See excel statistical functions and • Data analysis toolpack add-in
MCDM (multi criteria decision making) • “exploration of the pain and gain associated with different alternatives” • alternatives • criteria • rating of alternatives • weighting of criteria • exploration
MCDM ctd • Software • Hiview3 by Catalyze • http://www.catalyze.co.uk/resources/docs/pdf/Catalyze_Case_Study_Shampoo.pdf • See next slide copied from the above pdf
Goal Seeking/Optimisation/Linear Programming • “either the search for the pattern of variables that will lead to a goal or the pattern of variables that will optimise a goal under given constraints” • Software • Goal seek (in Excel “What if Analysis”) • Excel Add In – Solver (optimisation under constraints)
Goal seek – E.g. Find a Break even point New Perspectives on Microsoft Office Excel 2007
Working with What-If Analysis and Goal Seek New Perspectives on Microsoft Office Excel 2007
Sensitivity Analysis (“What if”) • “The performing of “what if” analysis in a given model through varying underlying variables” • fixed values • interval/increments • according to a statistical pattern