160 likes | 168 Views
Learn how to assess project viability using Cost Benefit Analysis (CBA) and Discounted Cash Flow (DCF) models, sensitivity analysis, and more with practical examples and spreadsheet techniques. Explore key elements and assumptions in financial risk assessment.
E N D
Unit 11: Risk/Benefit Assessment Using Simple Financial Models CSEM04: Risk and Opportunities of Systems Change in Organisations Prof. Helen M Edwards
Overview • Cost Benefit Analysis (CBA) • The difference between CBA and Discounted Cash Flow (DCF) • Applying the DCF formula to calculate present value (PV) • Applying DCF to find the Net Present Value (NPV) for a project • Assessing the viability of a project from the NPV • Sensitivity analysis for what-if scenarios • Example (based on ERP system)
5 basic elements of a cost benefit analysis: • Project definition and identification • Complete enumeration of the consequences of going ahead with the project • Aggregation of consequences at each time period in the project’s life to get time series for project costs and benefits • Aggregation of the cost and benefit streams over time to get a figure for the project’s net present value • Sensitivity analysis
Aggregation stage • Uses prices to aggregate over the desirable consequences (e.g.more apples available) to get the benefits series: B0...BT • Uses prices to aggregate over the undesirable consequences (e.g. more sulphur emissions) to get the C0....CT series.
Enumeration stage • This involves the physical consequences of the project’ • its output is in terms of • hours of labour, • hardware costs, • inventory costs, • cost of new staff • … • At this provided for each date from 0 through to T. • N.B. May be difficult to put a value on people factors e.g. motivation - but some financial equivalence is needed if the approach is to be used.
Future £75 Present £100 Discounting the future • Money invested today in a project is worth less in the future because of losses due to the interest rate (= discount rate)
Achieving a Net Present Value Requires: • Aggregation of consequences at each time period in the project’s life to get time series for project costs and benefit. • Aggregation of the cost and benefit streams over time to get a figure for the project’s net present value. • Sensitivityanalysis.
Discounted Cash Flow • Discounting is the process of transforming future money sums into their equivalent present values. • Similar to cost benefit analysis but the costs and benefits are converted into an income stream. • The calculation looks complicated but is easier understood when set out in a spreadsheet.
Example formula for calculating present value Present value after 2 years Present value = Future value ((100+discount rate)/100)2 The formula for “Present value” after n years (where “n” can take any value 0, 1, 2, ….) is: Present value = Future value*((100+discount rate)/100)-n An example of the equivalent formula in the Excel spreadsheet is: Present Value=E27*((100+$B$19)/100)^-A27 Where Cell E27 holds the value of “Future value” Cell B19 holds the value for the discount rate Cell A27 holds the value of the number of years considered (“n”)
Sum the present values Year 0 cash flow Mathematical expression to calculate Net Present Value (NPV)
Spreadsheet model assumptions • The first part of the spreadsheet shows the assumptions made. • The analysis part contains the formulae to calculate the net cash flow for each year of the project and the present value for each year.
Assumptions: Benefits (ERP cost benefit analysis)
Assumptions: Costs (ERP Cost benefit analysis)
Analysis Component of the spreadsheet • This contains the formulae to calculate • the net cash flow for each year of the project and • the present value for each year
Sensitivity analysis • In the given example a positive NPV makes project viable. • Altering different variables one at a time will show which variables the project is sensitive to. • i.e. small changes in one variable affect NPV significantly.
Activities and Thoughts • Using the spreadsheet provided do the tutorial to practise the technique. • Can you create a similar spreadsheet for a systems purchase in your workplace? • What would the relevant variables be? • How does this approach help in the consideration of the “riskiness” of a project?