Business Software Tools 200

1. Business Software Tools 200 Topic 2 Modelling

2. Why Model?

3. Why Model? • Focus on key aspects • Perform “What if?” (sensitivity analysis) • Make predictions • Improve (shared) understanding • Reduce uncertainty • Solve problems…

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

5. Good models ctd. • Provide explanation • Give accurate predictions • Correlate with the “real world” • Have face validity • Are scalable, extensible • Understandable by others • documentation

6. Modelling • financial • forecasting • simulation/risk analysis • MCDM (multi criteria decision making) • goal seeking/optimisation/linear programming

7. Excel Add-Ins • Analysis ToolPak • Solver

8. Excel Add-Ins

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

10. Financial models • Software • Excel financial functions

11. Forecasting • “predicting future values based on past patterns of a time series” • trend • regression • exponential smoothing • decomposition • Box Jenkins

12. Forecasting • Software • Excel statistical functions

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

14. 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/

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

17. Regression • statistical model • dependent, independent variables • may be time based • software • See excel statistical functions and • Data analysis toolpack add-in

18. Regression (Excel)

19. MCDM (multi criteria decision making) • “exploration of the pain and gain associated with different alternatives” • alternatives • criteria • rating of alternatives • weighting of criteria • exploration

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

21. Hiview3 – Criteria hierarchy

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

23. Goal seek – E.g. Find a Break even point New Perspectives on Microsoft Office Excel 2007

24. Working with What-If Analysis and Goal Seek New Perspectives on Microsoft Office Excel 2007

25. Solver (Excel)

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

27. Excel (What if Analysis)

28. Excel (What if Analysis)