Complete the Model
Once the decision model has been formulated, it can be analyzed using a computer system if it involves multiple variables. However, a simple and effective method involving a spreadsheet will provide more flexibility to suit the multitude of asset groups involved. Two simple spreadsheet examples are shown below.
Asset: | Pump Station |
Theoretical Effective Life: | 30 years |
Replacement Cost: | $100 000 |
Opportunity Cost of Capital (%): | 5.00 |
Interest Rate (%): | 5.00 |
Option 1 – Different Maintenance Regime
Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
Treatment Option: | Maintain | . | . | .. | . | . | . |
Year Required: | 2 | .. | . | . | . | . | . | . |
Effectiveness (Life Extension): | 4 | .. | .. | . | ... | . | . | . |
Treatment cost | . | . | 20000 . | . | . | . | . | . |
BENEFITS |
Deferred Replacement Program | . | . | . | 417 . | 417 . | 417 . | 417 . | . |
Reduced O&M
say 10% in 1st year
(of $5 000/yr) | . | . | . | 500 . | 375 . | 250 . | 125 . | 0 . |
Improved Level of Service
say 10% in first year
(of $10 000/yr) | . | . | . | 1000 . | 750 . | 500 . | 250 . | . |
Improved Production (if any) | . | . | . | . | . | . | . | . |
Risk Reduction
(say potential risk = 50 000)
risk reduction 20% in first yr - 0% in year 5 | . | . | . | 10000 . | 7500 . | 5000 . | 2500 . | . |
TOTAL BENEFITS | . | . | 30418. | 11917. | 9042. | 6167. | 3292. | . |
Discount Factors | . | . | . | 1.05 . | 1.1025. | 1.1576. | 1.2155 . | . |
PV in Year 2
Cost | . | . | 20 000 . | . | B/C = . | 1.521 . | . | . |
Benefit | . | . | 27 587. | .. | (B/C) = . | 1.379 . | . | . |
NPV at Year 2 (for 1 cycle) | . | . | 7 587 . | . | . | . | . | . |
NPV at Year 2 for recurring treatment | . | . | 42 792. | . | . | . | . | . |
NPV at year 0 | 38814 | .. | . | . | . | . | . | . |
B/C : Ratio based on total accrued value
(B/C): Ratio on discounted basis |
Option 2 – A Rehabilitation Option
Year | 0. | 1. | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
Treatment Option: | Rehabilitate | . | . | . | . | . | . | . | . | . |
Year Required: | 5 | . | . | . | . | . | .. | . | . | . | . |
Effectiveness (Life Extension): | 5 years | . | . | . | . | .. | . | . | . | . |
Treatment cost | . | . | . | . | . | 30000 | . | . | . | . | . |
BENEFITS |
Deferred Replacement Program | . | . | . | . | . | . | 500 | 500 | 500 | 500 | 500 |
Reduced O&M
say 10% in 1st year
(of $5 000/yr) | . | . | . | . | . | . | 750 | 600 | 450 | 300 | 150 |
Improved Level of Service
say 10% in first year
(of $10 000/yr) | . | . | . | . | . | . | 1000 | 800 | 600 | 400 | 200 |
Improved Production (if any) | . | . | . | . | . | . | . | . | . | . | . |
Risk Reduction
(say potential risk = 50 000)
risk reduction 20% in first yr - 0% in year 5 | . | . | . | . | . | . | 12500 | 10000 | 7500 | 5000 | 2500 |
TOTAL BENEFITS | . | . | . | . | . | 45250 | 14750 | 11900 | 9050 | 6200 | 3350 |
Discount Factors | . | . | . | . | . | . | 1.05 | 1.1025 | 1.1576 | 1.2155 | 1.2763 |
PV in Year 2 | . | . | . | . | . | . | B/C = | 1.5083 | . | . | . |
Cost | . | . | . | . | . | 30000 | (B/C)= | 1.3462 | . | . | . |
Benefit | . | . | . | . | . | 40385 | . | . | . | . | . |
NPV at Year 2 (for 1 cycle) | . | . | . | . | . | 10385 | . | . | . | . | . |
NPV at Year 2 for recurring treatment | . | . | . | . | . | 47971 | . | . | . | . | . |
NPV at year 0 | 37587 | . | . | . | . | . | . | . | . | . | . |
B/C : Ratio based on total accrued value
(B/C): Ratio on discounted basis |