Unit 8 Tutorial ERP Cost Benefit Analysis and Discounted Cash Flow Analysis ERP Cost Benefit analysis The assumptions are set out first Make sur by wpm87015


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

                    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

      Year           Annual         One-time On going      Net cash          PV
                     benefits                                flow
        0            425000          598000    27000       -200000        -200000
        1            425000                    27000        398000       368518.52

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

4. Draw a graph of the above data in the space below of NPV against % increase in sales.


                                       % Increase in sales

5. Comment on the results

                                  End of Unit 8 Tutorial

To top