Excel and written paper for business finance

profiledrim_ghivas
gilligan_island.xlsx
Home>Business & Finance homework help>Excel and written paper for business finance

Question 1

Your power plant on Gilligan's Island is producing too much air pollution. You have three choices for dealing with this problem.
1. You can pay a pollution tax (one time) of $10m immediately.
2. You can close the plant and install a power cable from the mainland. That will cost you $1m at the end of this year, $3m at the end of next year (construction costs) and then $.05m forever after that for maintenance.
3. You can retrofit the plant switch scrubbers to reduce the emissions. That will cost $9m at the end of this year and $.01m forever after that for maintenance.
Assume that the cost of generating power on the mainland is approximately the same as the cost of generating power at your Gilligan's Island plant and assume your cost of capital (WACC) is 10%. Which alternative world you choose?
1 10m
2
Years 0 1 2 3 4 ….
Payments 0 1m 3m .5m .5m ….
r = 0.5 = 5m
0.1
Gilligan's Island will run a cable from the mainland for power and shut down the coconut power plant on the Island. This is a sad day for the island, but it’s the cost of going green here at Gilligan's Island. (You have to make sure you sing "Here at Gilligan's Island.")
1.m + 3.m + 5.m = 7,145,004
1.1 1.1^2 1.3^3
3
Years 0 1 2 3 ….
Payments 0 9m 0.1m .1m ….
r = 0.1 = 1m
0.1
9.m + 1m = 9,008,264
1.1 1.1^2

Question 2

Start Up Year's 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
Locations 25 Fixed Assets (20,000,000) - - - - - - - - - - - - - - - - - - - 7,000,000 0
Start Up 20,000,000 Rev - 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000 50,000,000
Land 1/4 of Startup Cost - (45,000,000) (45,000,000) (45,000,000) (45,000,000) (45,000,000) (45,000,000) (45,000,000) (45,000,000) (45,000,000) (45,000,000) (45,000,000) (45,000,000) (45,000,000) (45,000,000) (45,000,000) (45,000,000) (45,000,000) (45,000,000) (45,000,000) (45,000,000)
Depreciation 750,000 Wage Cost - (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000) (250,000)
Land in 20 years 7,000,000 Deprication - (750,000) (750,000) (750,000) (750,000) (750,000) (750,000) (750,000) (750,000) (750,000) (750,000) (750,000) (750,000) (750,000) (750,000) (750,000) (750,000) (750,000) (750,000) (750,000) (750,000)
Expected Sales and Cost EBIT - 4,000,000 4,000,000 4,000,000 4,000,000 4,000,000 4,000,000 4,000,000 4,000,000 4,000,000 4,000,000 4,000,000 4,000,000 4,000,000 4,000,000 4,000,000 4,000,000 4,000,000 4,000,000 4,000,000 4,000,000
Average Sales 2,000,000 Interest - - - - - - - - - - - - - - - - - - - - -
Operating Costs 1,800,000 Tax - (1,320,000) (1,320,000) (1,320,000) (1,320,000) (1,320,000) (1,320,000) (1,320,000) (1,320,000) (1,320,000) (1,320,000) (1,320,000) (1,320,000) (1,320,000) (1,320,000) (1,320,000) (1,320,000) (1,320,000) (1,320,000) (1,320,000) (1,320,000)
Working Capital Need Net Income - 2,680,000 2,680,000 2,680,000 2,680,000 2,680,000 2,680,000 2,680,000 2,680,000 2,680,000 2,680,000 2,680,000 2,680,000 2,680,000 2,680,000 2,680,000 2,680,000 2,680,000 2,680,000 2,680,000 2,680,000
First Year 1,000,000 Deprication - 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000 750,000
frist five years 100,000 Operation Captial - 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000
Final Two Years -750,000 Working Capital (1,000,000) (1,100,000) (1,200,000) (1,300,000) (1,400,000) (1,500,000) - - - - - - - - - - - - - (750,000) (750,000)
Wages Change in WC 1,000,000 100,000 100,000 100,000 100,000 100,000 - - - - - - - - - - - - - (750,000) (750,000)
Brother-n-Law 100,000 Project Cash Flow (21,000,000) 3,330,000 3,330,000 3,330,000 3,330,000 3,330,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 3,430,000 2,680,000 9,680,000
Real workers 250,000 Discount Factor 1 0.906 0.820 0.743 0.673 0.610 0.552 0.500 0.453 0.410 0.372 0.337 0.305 0.276 0.250 0.227 0.205 0.186 0.168 0.153 0.138
WACC and Tax Rate PV (21,000,000) 3,016,304 2,732,160 2,474,782 2,241,651 2,030,481 1,894,435 1,715,974 1,554,324 1,407,902 1,275,274 1,155,139 1,046,322 947,755 858,474 777,603 704,351 637,999 577,897 408,999 1,338,116
Firm's WACC 10% NPV 7,795,942
Dount's WACC 0.1040
Tax Rate 33%
1 1,000,000 6.7274999493 6727499.94932561
2 100,000 6.1159090448 611590.904484146
3 100,000 5.5599173135 555991.731349224
4 100,000 5.054470285 505447.028499294
5 100,000 4.5949729864 459497.298635722
5559917.31349224
2,663,637
$8,223,554
2
$4,111,777.16
$4,522,954.87