use excel and clearly show equations The Company HE Inc. is considering upgradin
ID: 2426983 • Letter: U
Question
use excel and clearly show equations
The Company HE Inc. is considering upgrading their distribution center (DC) and have received investment proposal from four different vendors. the budget limitation for the investment is $1 million. the investment proposals are listed in Table 1. Alternative 1 and 3 represents the implementation of RFID (which is the main goal of the investment) and are mutually exclusive; however one of them must be included in the project if it is approved. Alternative 2 is contingent on alternative 3. the company uses a MARR of 15%. Based on the internal rate of return determine the preferred alternative.Explanation / Answer
Standing alone only projects 2 & 4 have IRR > MARR. Others, namely projeccts 1 & 3, are to be rejected.
But project 2 & 3 can be considered together, giving an IRR of 25.26%, which is greater than MARR, and hence acceptable.
Hence, the acceptable alternatives are
Project 2 & 3 with an IRR of 25.26%, requiring initial investment of $1,000,000, leaves no balance of funds.
and Project 4 with an IRR of 61.95%, requiring initial investment of $350,000, leaves a balance of $650,000
Hence, Projects 2 & 3 may be implemented.
The calculations are given below:
PROPOSAL 1 0 1 2 3 4 5 6 7 8 9 10 initial investment -650000 Salvage value 130000 Savings generated 100000 105000 110000 115000 120000 125000 130000 135000 140000 145000 annual operating cost -30000 -31800 -33708 -35730 -37874 -40147 -42556 -45109 -47815 -50684 Net cash savings 70000 73200 76292 79270 82126 84853 87444 89891 92185 94316 Net cash flow -650000 70000 73200 76292 79270 82126 84853 87444 89891 92185 224316 Cumulative pvif @ 15% 0.869565 0.756144 0.657516 0.571753 0.497177 0.432328 0.375937 0.326902 0.284262 0.247185 pv 60869.57 55349.72 50163.23 45322.61 40830.98 36684.39 32873.6 29385.56 26204.6 55447.39 433132 pvif @ 10% 0.909091 0.826446 0.751315 0.683013 0.620921 0.564474 0.513158 0.466507 0.424098 0.385543 pv 63636.36 60495.87 57319.31 54142.15 50993.59 47897.44 44872.82 41934.86 39095.25 86483.39 546871 pvif @ 5% 0.952381 0.907029 0.863838 0.822702 0.783526 0.746215 0.710681 0.676839 0.644609 0.613913 pv 66666.67 66394.56 65903.9 65215.23 64347.63 63318.79 62145.12 60841.83 59422.99 137710.3 711967 IRR = 5 + 61967/(711967-546874)= 5.37 % 5.375 PROPOSAL 2 0 1 2 3 4 5 6 7 8 9 10 initial investment -300000 Salvage value 45000 Savings generated 200000 206000 212000 218000 224000 230000 236000 242000 248000 254000 annual operating cost -6000 -6660 -7392.6 -8205.79 -9108.42 -10110.3 -11222.5 -12457 -13827.2 -15348.2 Net cash savings 194000 199340 204607.4 209794 214892 219890 224778 229543 234173 238652 Net cash flow -300000 194000 199340 204607.4 209794 214892 219890 224778 229543 234173 283652 Cumulative pvif @ 25% 0.8 0.64 0.512 0.4096 0.32768 0.262144 0.209715 0.167772 0.134218 0.107374 pv 155200 127577.6 104759 85931.71 70415.67 57642.75 47139.26 38510.93 31430.14 30456.88 749064 pvif @ 35% 0.740741 0.548697 0.406442 0.301068 0.223014 0.165195 0.122367 0.090642 0.067142 0.049735 pv 143703.7 109377.2 83161.06 63162.37 47923.72 36324.71 27505.31 20806.26 15722.89 14107.43 561795 pvif @ 45% 0.689655 0.475624 0.328017 0.226218 0.156013 0.107595 0.074203 0.051175 0.035293 0.02434 pv 133793.1 94810.94 67114.65 47459.32 33525.82 23659.02 16679.26 11746.81 8264.648 6904.075 443958 pvif @ 55% 0.645161 0.416233 0.268537 0.17325 0.111774 0.072112 0.046524 0.030016 0.019365 0.012493 pv 86318.13 39463.45 18022.8 8222.326 3747.321 1706.108 775.9879 352.5872 160.0439 86.25584 158855 IRR = 45 + 143958/(443958-158855)= 45.5 % 45.50 PROPOSAL 3 0 1 2 3 4 5 6 7 8 9 10 initial investment -700000 Salvage value 175000 Savings generated 97000 105000 113000 121000 129000 137000 145000 153000 161000 169000 annual operating cost -45000 -47250 -49612.5 -52093.1 -54697.8 -57432.7 -60304.3 -63319.5 -66485.5 -69809.8 Net cash savings 52000 57750 63387.5 68907 74302 79567 84696 89680 94515 99190 Net cash flow -700000 52000 57750 63387.5 68907 74302 79567 84696 89680 94515 274190 Cumulative pvif @ 15% 0.869565 0.756144 0.657516 0.571753 0.497177 0.432328 0.375937 0.326902 0.284262 0.247185 pv 45217.39 43667.3 41678.31 39397.73 36941.33 34399.15 31840.25 29316.71 26866.92 67775.63 397101 pvif @ 5% 0.952381 0.907029 0.863838 0.822702 0.783526 0.746215 0.710681 0.676839 0.644609 0.613913 pv 49523.81 52380.95 54756.51 56689.86 58217.73 59374.37 60191.65 60699.28 60924.89 168329 681088 pvif @ 4% 0.961538 0.924556 0.888996 0.854804 0.821927 0.790315 0.759918 0.73069 0.702587 0.675564 pv 50000 53393.12 56351.26 58901.89 61071.01 62883.22 64361.77 65528.65 66404.64 185233.1 724129 IRR = 4 + 24129/(724129-681088) 4.56 % PROPOSAL 2 & 3 0 1 2 3 4 5 6 7 8 9 10 initial investment -1000000 Salvage value 220000 Savings generated 297000 311000 325000 339000 353000 367000 381000 395000 409000 423000 annual operating cost -51000 -53910 -57005.1 -60298.9 -63806.2 -67543 -71526.8 -75776.5 -80312.7 -85158 Net cash savings 246000 257090 267994.9 278701 289194 299457 309473 319224 328687 337842 Net cash flow -1000000 246000 257090 267994.9 278701 289194 299457 309473 319224 328687 557842 Cumulative pvif @ 15% 0.869565 0.756144 0.657516 0.571753 0.497177 0.432328 0.375937 0.326902 0.284262 0.247185 pv 213913 194397 176211 159348.3 143780.4 129463.5 116342.4 104354.7 93433.44 137890 1469134 pvif @25% 0.80 0.64 0.512 0.4096 0.32768 0.262144 0.209715 0.167772 0.134218 0.107374 pv 196800 164537.6 137213.4 114156 94763.02 78500.85 64901.24 53556.82 44115.66 59897.83 1008442 pvif @ 26% 0.793651 0.629882 0.499906 0.396751 0.314882 0.249906 0.198338 0.157411 0.12493 0.09915 pv 195238.1 161936.3 133972.3 110574.9 91061.8 74836.1 61380.33 50249.36 41062.75 55310.27 975622 IRR = 26 - (1000000-975622)/(1008442-975622) 25.26 %Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.