Session 3A: Financial Modeling

1. Session 3A: Financial Modeling Dr. Frank Selto CURISES Cost and Managerial Accounting

2. Overview What is Financial Modeling? Objectives of Financial Modeling Profit-planning Models Drivers of Revenues and Costs Model Variations Taxes, Multiple Products, Multiple cost drivers Modeling Risk Optimization Models Single constraint, Theory of constraints, Multiple constraints

3. What is Financial Modeling? Model is representation of reality Financial model is simulation of cost and revenue relations to support business decisions Profit-Planning/Decision Making Risk assessment Capital budgeting Pro Forma financial statements Federal budgets next

4. US Federal FY 2010 “Discretionary” Budget

5. Federal Deficit Model FY 2010

6. Visual Hurricane Model

7. Objectives of Financial Modeling Improve decision making Communicate clearly Promote learning Train employees Simulate processes and financial results Respond easily and flexibly to changes Requires computer spreadsheet models Very common Internally, highly value-added

8. A Few Financial Modeling Websites http://www.exinfm.com/free_spreadsheets.html http://www.centerforagroforestry.org/profit/walnutfinancialmodel.asp http://www.dupont.com/teflon/bakeware/calculat.html

9. Profit Planning Models CVP Models are basic profit planning models Also Useful for Control & Evaluations Model revenues and costs as linear relations without time lags (simplifications) Example: Revenues = $7xQ (Q = quantity sold) Fixed costs = $450,000 per period Variable costs = $2xQ (Q = quantity produced) Note: CVP models usually assume quantity sold = quantity produced – no inventory effects

10. Basic CVP Model Profit = Revenues – Costs Profit = P x Q – [V x Q + F] Profit = $7Q – [$450,000 + $2Q] Profit = $7Q – $2Q – $450,000 Profit = $(7-2)Q - $450,000 Breakeven Profit = 0 = $5Q - $450,000 Breakeven Quantity = $450,000 ÷ $5 = 90,000 units per period

11. Target Profit Replace “0” with Target Profit (before tax) $400,000 Target Profit 400000 = (7-2)Q -450000 5Q =400000 + 450000

12. CVP Chart

13. Spreadsheet Models Gather information on parameters Cost and revenue drivers Cost behavior (committed, variable) Model relations among parameters Usually linear relations But can use nonlinear relations Separate parameters and formulas

14. Example Spreadsheet Model (from text)

15. Model Variations – Taxes After-tax Income = Pre-tax Income – Taxes After-tax Income = Pre-tax Income – Pre-tax Income X Tax rate (t) After-tax Income = (1- t) x Pre-tax Income Pre-tax Income = After-tax Income ÷ (1- t) Pre-tax Income = $400,000 ÷ (1- 0.2) Pre-tax Income = $500,000 $500,000 = $5Q - $450,000 Q = (500,000 + 450,000) ÷ 5 = 190,000 units per period

16. Modeling Risk Sensitivity analysis What if? Vary each model parameter One at a time Observe change in outcome (profit) Measure model elasticity (% change in profit/% change in parameter) Scenario analysis Best, Worst, Most-Likely cases

17. Sensitivity Analysis (Excel spreadsheet) Evaluate risk by measuring effects of possible parameter changes Steps to follow: Establish “Base” levels of parameters Determine likely ranges of parameters (high and low) Compute outcome (profit, breakeven, etc.) with all parameters but one at base levels Compute outcome with one parameter at high level Repeat with the parameter at low level Repeat for all parameters Compute model elasticity for all parameters Percentage change in outcome ÷ percentage change in parameter

18. Scenario Analysis Evaluate risk by creating alternative scenarios Scenarios are feasible combinations of parameters Best case – Highest sales prices + lowest costs (only one possibility) Worst case – Lowest sales prices + highest costs (only one possibility) Most-likely case – Most likely combination of prices and costs Evaluate the up-side and down-side risks Continue the spreadsheet example

19. Model Variations – Multiple Products Sales Mix = Proportions of Multiple Products Produced and Sold Sales mix: Q1 = 80% Q2 = 20% CM: CM1 = $4 CM2 = $13 Fixed cost = $550,000 per period Weighted-Average Unit Contribution Margin (WAUCM) = Proportions of Each Product Multiplied by Its Contribution Margin WAUCM = 0.8 x $4 + 0.2 x $13 = $5.80 Breakeven Quantity = $550,000/$5.80 = 94,828 units Q1 = 0.8 x 94,828 = 75,862 Q1 per period Q2 = 0.2 x 94,828 = 18,966 Q2 per period

20. Optimization Models – Single Constraint Single constraints are unusual, but interesting place to begin Should maximize profit from limiting resource Profit per unit of limiting resource Example: 12 processing hours extra How to best use these constrained hours?

21. Example of Single Constraint Three types of jobs: Constraint = processing hours

22. Maximum CM Possible Product A: 12 hours ÷ 4 hours/unit = 3 units 3 units X $2,500/unit = $7,500 Product B: 12 hours ÷ 11 hours/unit = 1 unit 1 unit X $5,500/unit = $5,500 Product C: 12 hours ÷ 3 hours/unit = 4 units 4 units X $1,800/unit = $7,200

23. Model Variations – Multiple Cost Drivers Sales/Production Volume Usually is not Only Driver of Costs (or Revenues) Total Cost = Unit variable cost x Unit activity + (Batch cost x Batch Activity) + (Product cost x Product Activity) + (Customer cost x Customer Activity) + (Facility cost x Facility Activity) Note: Usually need to compute breakeven and target levels carefully because of “lumpiness” of non-unit level costs

24. Optimization Models – Multiple Constraints Extension of CVP Models Maximize profits (eg, CVP) Subject to constrained capacities Linear programming Also easily done on MS Excel See chapter 12, Appendix 2 Not covered in this course

25. Re-Cap of Financial Modeling One of the most useful financial activities Requires nearly all the skills & knowledge of this class Most cross-functional teams have a financial analyst who creates financial models of business problems/solutions Key component of project management Also key management training tool Learn to do this