Please use Excel and upload the excel file and copy the link Growth Fertilizer p
ID: 2766276 • Letter: P
Question
Please use Excel and upload the excel file and copy the link
Growth Fertilizer purchases a gravity settling tank by borrowing the $50,000 purchase price. The loan is to be repaid with four equal annual payments at an annual compound rate of 15%. It is anticipated that the tank will be used for 9 years and then sold for $3,000. Annual operating and maintenance expenses are estimated to be $9000/year increasing by $500 per year thereafter. Savings of $15,000/year are estimated over the present filtration system increasing by 3% per year thereafter. The firm uses a MARR of 15% for its economic analyses. Perform a before tax cash flow sensitivity analysis to determine the effects on the economic feasibility of the plan due to errors in estimating the annual operating and maintenance costs, initial investment, and savings. Use a range of (-30%, +30%) with intervals of 10%.Explanation / Answer
1Sensitivity Analysis 1: At 0%
Year
Cash outflow
Cash in flow
Net cash flow
PVF @ 15%
Discounted cash flow
0
-50000
-50000
1
-50000
1
9000
15000
6000
0.869565
5217.391
2
9500
15450
5950
0.756144
4499.055
3
10000
15913.5
5913.5
0.657516
3888.222
4
10500
16390.91
5890.905
0.571753
3368.144
5
11000
16882.63
5882.632
0.497177
2924.708
6
11500
17389.11
5889.111
0.432328
2546.025
7
12000
17910.78
5910.784
0.375937
2222.083
8
12500
18448.11
5948.108
0.326902
1944.447
9
13000
19001.55
6001.551
0.284262
1706.015
9
3000
3000
0.284262
852.786
NPV
-20831.1
Sensitivity Analysis 2: At sensitivity %
Year
Cash outflow
Cash in flow
Net cash flow
Sensitivity @ -30%
PVF @ 15%
Discounted cash flow
0
-50000
-50000
-35000
1
-35000
1
9000
15000
6000
4200
0.869565
3652.174
2
9500
15450
5950
4165
0.756144
3149.338
3
10000
15913.5
5913.5
4139.45
0.657516
2721.756
4
10500
16390.91
5890.905
4123.634
0.571753
2357.701
5
11000
16882.63
5882.632
4117.843
0.497177
2047.295
6
11500
17389.11
5889.111
4122.378
0.432328
1782.218
7
12000
17910.78
5910.784
4137.549
0.375937
1555.458
8
12500
18448.11
5948.108
4163.676
0.326902
1361.113
9
13000
19001.55
6001.551
4201.086
0.284262
1194.211
9
3000
3000
2100
0.284262
596.9502
NPV
-14581.8
Sensitivity Analysis 2: At sensitivity %
Year
Cash outflow
Cash in flow
Net cash flow
Sensitivity @ -20%
PVF @ 15%
Discounted cash flow
0
-50000
-50000
-40000
1
-40000
1
9000
15000
6000
4800
0.869565
4173.913
2
9500
15450
5950
4760
0.756144
3599.244
3
10000
15913.5
5913.5
4730.8
0.657516
3110.578
4
10500
16390.91
5890.905
4712.724
0.571753
2694.515
5
11000
16882.63
5882.632
4706.106
0.497177
2339.766
6
11500
17389.11
5889.111
4711.289
0.432328
2036.82
7
12000
17910.78
5910.784
4728.628
0.375937
1777.666
8
12500
18448.11
5948.108
4758.486
0.326902
1555.558
9
13000
19001.55
6001.551
4801.241
0.284262
1364.812
9
3000
3000
2400
0.284262
682.2288
NPV
-16664.9
Sensitivity Analysis 2: At sensitivity %
Year
Cash outflow
Cash in flow
Net cash flow
Sensitivity @ -10%
PVF @ 15%
Discounted cash flow
0
-50000
-50000
-45000
1
-45000
1
9000
15000
6000
5400
0.869565
4695.652
2
9500
15450
5950
5355
0.756144
4049.149
3
10000
15913.5
5913.5
5322.15
0.657516
3499.4
4
10500
16390.91
5890.905
5301.815
0.571753
3031.33
5
11000
16882.63
5882.632
5294.369
0.497177
2632.237
6
11500
17389.11
5889.111
5300.2
0.432328
2291.423
7
12000
17910.78
5910.784
5319.706
0.375937
1999.875
8
12500
18448.11
5948.108
5353.297
0.326902
1750.002
9
13000
19001.55
6001.551
5401.396
0.284262
1535.414
9
3000
3000
2700
0.284262
767.5074
NPV
-18748
Sensitivity Analysis 2: At sensitivity %
Year
Cash outflow
Cash in flow
Net cash flow
Sensitivity @ 10%
PVF @ 15%
Discounted cash flow
0
-50000
-50000
-55000
1
-55000
1
9000
15000
6000
6600
0.869565
5739.13
2
9500
15450
5950
6545
0.756144
4948.96
3
10000
15913.5
5913.5
6504.85
0.657516
4277.044
4
10500
16390.91
5890.905
6479.996
0.571753
3704.958
5
11000
16882.63
5882.632
6470.895
0.497177
3217.179
6
11500
17389.11
5889.111
6478.022
0.432328
2800.628
7
12000
17910.78
5910.784
6501.863
0.375937
2444.291
8
12500
18448.11
5948.108
6542.919
0.326902
2138.892
9
13000
19001.55
6001.551
6601.706
0.284262
1876.617
9
3000
3000
3300
0.284262
938.0646
NPV
-22914.2
Sensitivity Analysis 2: At sensitivity %
Year
Cash outflow
Cash in flow
Net cash flow
Sensitivity @ 20%
PVF @ 15%
Discounted cash flow
0
-50000
-50000
-60000
1
-60000
1
9000
15000
6000
7200
0.869565
6260.87
2
9500
15450
5950
7140
0.756144
5398.866
3
10000
15913.5
5913.5
7096.2
0.657516
4665.867
4
10500
16390.91
5890.905
7069.086
0.571753
4041.773
5
11000
16882.63
5882.632
7059.159
0.497177
3509.649
6
11500
17389.11
5889.111
7066.933
0.432328
3055.23
7
12000
17910.78
5910.784
7092.941
0.375937
2666.499
8
12500
18448.11
5948.108
7137.73
0.326902
2333.336
9
13000
19001.55
6001.551
7201.861
0.284262
2047.219
9
3000
3000
3600
0.284262
1023.343
NPV
-24997.3
Sensitivity Analysis 2: At sensitivity %
Year
Cash outflow
Cash in flow
Net cash flow
Sensitivity @ 30%
PVF @ 15%
Discounted cash flow
0
-50000
-50000
-65000
1
-65000
1
9000
15000
6000
7800
0.869565
6782.609
2
9500
15450
5950
7735
0.756144
5848.771
3
10000
15913.5
5913.5
7687.55
0.657516
5054.689
4
10500
16390.91
5890.905
7658.177
0.571753
4378.587
5
11000
16882.63
5882.632
7647.422
0.497177
3802.12
6
11500
17389.11
5889.111
7655.844
0.432328
3309.833
7
12000
17910.78
5910.784
7684.02
0.375937
2888.708
8
12500
18448.11
5948.108
7732.54
0.326902
2527.781
9
13000
19001.55
6001.551
7802.017
0.284262
2217.82
9
3000
3000
3900
0.284262
1108.622
NPV
-27080.5
1Sensitivity Analysis 1: At 0%
Year
Cash outflow
Cash in flow
Net cash flow
PVF @ 15%
Discounted cash flow
0
-50000
-50000
1
-50000
1
9000
15000
6000
0.869565
5217.391
2
9500
15450
5950
0.756144
4499.055
3
10000
15913.5
5913.5
0.657516
3888.222
4
10500
16390.91
5890.905
0.571753
3368.144
5
11000
16882.63
5882.632
0.497177
2924.708
6
11500
17389.11
5889.111
0.432328
2546.025
7
12000
17910.78
5910.784
0.375937
2222.083
8
12500
18448.11
5948.108
0.326902
1944.447
9
13000
19001.55
6001.551
0.284262
1706.015
9
3000
3000
0.284262
852.786
NPV
-20831.1
Sensitivity Analysis 2: At sensitivity %
Year
Cash outflow
Cash in flow
Net cash flow
Sensitivity @ -30%
PVF @ 15%
Discounted cash flow
0
-50000
-50000
-35000
1
-35000
1
9000
15000
6000
4200
0.869565
3652.174
2
9500
15450
5950
4165
0.756144
3149.338
3
10000
15913.5
5913.5
4139.45
0.657516
2721.756
4
10500
16390.91
5890.905
4123.634
0.571753
2357.701
5
11000
16882.63
5882.632
4117.843
0.497177
2047.295
6
11500
17389.11
5889.111
4122.378
0.432328
1782.218
7
12000
17910.78
5910.784
4137.549
0.375937
1555.458
8
12500
18448.11
5948.108
4163.676
0.326902
1361.113
9
13000
19001.55
6001.551
4201.086
0.284262
1194.211
9
3000
3000
2100
0.284262
596.9502
NPV
-14581.8
Sensitivity Analysis 2: At sensitivity %
Year
Cash outflow
Cash in flow
Net cash flow
Sensitivity @ -20%
PVF @ 15%
Discounted cash flow
0
-50000
-50000
-40000
1
-40000
1
9000
15000
6000
4800
0.869565
4173.913
2
9500
15450
5950
4760
0.756144
3599.244
3
10000
15913.5
5913.5
4730.8
0.657516
3110.578
4
10500
16390.91
5890.905
4712.724
0.571753
2694.515
5
11000
16882.63
5882.632
4706.106
0.497177
2339.766
6
11500
17389.11
5889.111
4711.289
0.432328
2036.82
7
12000
17910.78
5910.784
4728.628
0.375937
1777.666
8
12500
18448.11
5948.108
4758.486
0.326902
1555.558
9
13000
19001.55
6001.551
4801.241
0.284262
1364.812
9
3000
3000
2400
0.284262
682.2288
NPV
-16664.9
Sensitivity Analysis 2: At sensitivity %
Year
Cash outflow
Cash in flow
Net cash flow
Sensitivity @ -10%
PVF @ 15%
Discounted cash flow
0
-50000
-50000
-45000
1
-45000
1
9000
15000
6000
5400
0.869565
4695.652
2
9500
15450
5950
5355
0.756144
4049.149
3
10000
15913.5
5913.5
5322.15
0.657516
3499.4
4
10500
16390.91
5890.905
5301.815
0.571753
3031.33
5
11000
16882.63
5882.632
5294.369
0.497177
2632.237
6
11500
17389.11
5889.111
5300.2
0.432328
2291.423
7
12000
17910.78
5910.784
5319.706
0.375937
1999.875
8
12500
18448.11
5948.108
5353.297
0.326902
1750.002
9
13000
19001.55
6001.551
5401.396
0.284262
1535.414
9
3000
3000
2700
0.284262
767.5074
NPV
-18748
Sensitivity Analysis 2: At sensitivity %
Year
Cash outflow
Cash in flow
Net cash flow
Sensitivity @ 10%
PVF @ 15%
Discounted cash flow
0
-50000
-50000
-55000
1
-55000
1
9000
15000
6000
6600
0.869565
5739.13
2
9500
15450
5950
6545
0.756144
4948.96
3
10000
15913.5
5913.5
6504.85
0.657516
4277.044
4
10500
16390.91
5890.905
6479.996
0.571753
3704.958
5
11000
16882.63
5882.632
6470.895
0.497177
3217.179
6
11500
17389.11
5889.111
6478.022
0.432328
2800.628
7
12000
17910.78
5910.784
6501.863
0.375937
2444.291
8
12500
18448.11
5948.108
6542.919
0.326902
2138.892
9
13000
19001.55
6001.551
6601.706
0.284262
1876.617
9
3000
3000
3300
0.284262
938.0646
NPV
-22914.2
Sensitivity Analysis 2: At sensitivity %
Year
Cash outflow
Cash in flow
Net cash flow
Sensitivity @ 20%
PVF @ 15%
Discounted cash flow
0
-50000
-50000
-60000
1
-60000
1
9000
15000
6000
7200
0.869565
6260.87
2
9500
15450
5950
7140
0.756144
5398.866
3
10000
15913.5
5913.5
7096.2
0.657516
4665.867
4
10500
16390.91
5890.905
7069.086
0.571753
4041.773
5
11000
16882.63
5882.632
7059.159
0.497177
3509.649
6
11500
17389.11
5889.111
7066.933
0.432328
3055.23
7
12000
17910.78
5910.784
7092.941
0.375937
2666.499
8
12500
18448.11
5948.108
7137.73
0.326902
2333.336
9
13000
19001.55
6001.551
7201.861
0.284262
2047.219
9
3000
3000
3600
0.284262
1023.343
NPV
-24997.3
Sensitivity Analysis 2: At sensitivity %
Year
Cash outflow
Cash in flow
Net cash flow
Sensitivity @ 30%
PVF @ 15%
Discounted cash flow
0
-50000
-50000
-65000
1
-65000
1
9000
15000
6000
7800
0.869565
6782.609
2
9500
15450
5950
7735
0.756144
5848.771
3
10000
15913.5
5913.5
7687.55
0.657516
5054.689
4
10500
16390.91
5890.905
7658.177
0.571753
4378.587
5
11000
16882.63
5882.632
7647.422
0.497177
3802.12
6
11500
17389.11
5889.111
7655.844
0.432328
3309.833
7
12000
17910.78
5910.784
7684.02
0.375937
2888.708
8
12500
18448.11
5948.108
7732.54
0.326902
2527.781
9
13000
19001.55
6001.551
7802.017
0.284262
2217.82
9
3000
3000
3900
0.284262
1108.622
NPV
-27080.5