"Unit 8 Tutorial ERP Cost Benefit Analysis and Discounted Cash Flow Analysis ERP Cost Benefit analysis The assumptions are set out first Make sur"
Unit 8 Tutorial: ERP Cost Benefit Analysis and Discounted Cash Flow Analysis ERP Cost Benefit analysis The assumptions are set out first. Make sure you look at all the cells in the spreadsheet to understand the formula behind each cell. Benefits Costs One time Ongoing Sales 8000000 Hardware 10000 2000 Possible % increase in sales 5 Software 400000 10000 50% of possible increase 200000 Customising 12000 6000 Data control Annual direct labour cost 1000000 Inventory records 50000 2000 Possible % saving 5 Bills of material (BOM) 6000 50% of possible labour savings 25000 Routings 6000 Education Annual purchase material cost 3000000 External 20000 Possible % cost saving 10 Internal 4000 50% of possible materials' saving 150000 Direct labour 3000 Full time project leader 30000 Current raw material inventory value 400000 Additional project staff 20000 (1) Possible % reduction in raw material 25 Outside consultancy 20000 1000 50% of possible inventory saving 50000 Miscellaneous 20000 3000 Discount rate (%) 8 Total annual benefit 425000 Total implementation 598000 27000 cost Discounted Cash Flow Analysis Present value after 2 years Present value = Future value (100+discount rate)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 ($B$19) holds the value for the discount rate Cell A27 holds the value of the number of years considered (“n”) (E27 and A28 are known as a relative addresses-if you copy the formula down the spreadsheet this will increment e.g. to E28 and A28. $B$19 is an absolute address and the formula always points to a fixed cell). Analysis spreadsheet cash flow = recurring annual benefits – total benefits costs PV = Present Value. value t for the Equal to cash flow in formula year 0, decreases thereafter Year Annual One-time On going Net cash PV benefits flow 0 425000 598000 27000 -200000 -200000 1 425000 27000 398000 368518.52 2 NPV 1. Complete the rest of the missing cells (shaded red) and find the NPV. You can use a calculator or the spreadsheet COMM80Unit8DCF.XLS Now use the spreadsheet to answer the following questions 2. Complete the following table for increased sales of 3% • What is the NPV? • Is the project still viable? Year Annual One-time On going Net cash PV benefits Costs costs flow 0 1 2 NPV Sensitivity analysis 3. Change the value of the projected % increase in sales to 2% then repeat for 3% and 4% and complete the following table (0% and 1% have been entered for you so you can check you are following the correct procedure). What if scenario % increase in sales NPV 0 -46913.58 1 64417.01 2 3 4 5 4. Draw a graph of the above data in the space below of NPV against % increase in sales. NPV 0 % Increase in sales 5. Comment on the results End of Unit 8 Tutorial