80.2K

Verified Solution

Link Copied!

2-3 Calculating Project FCF In the spring of 2015, Jemison Electric was considering an investment in a new distribution center. Jemisons CFO anticipates additional earnings before interest and taxes (EBIT) of $100,000 for the first year of operation of the center, and, over the next five years, the firm estimates that this amount will grow at a rate of 5% per year. The distribution center will require an initial investment of $400,000 that will be depreciated over a five-year period toward a zero salvage value using straight-line depreciation of $80,000 per year. Jemisons CFO estimates that the distribution center will need operating net working capital equal to 20% of EBIT to support operation.

Assuming the firm faces a 30% tax rate, calculate the projects annual project free cash flows (FCFs) for each of the next five years where the salvage value of operating networking capital and fixed assets is assumed to equal their book values, respectively.

1. If the discount rate is 18%, what is the NPV of this project? What is the IRR?
2. Conduct a break-even sensitivity analysis and identify the top 3 value driver

A B D E F G H K L M N O 1 PROBLEM 2-3 2 3 4 5 5 6 Growth rate in EBIT for years 1-5 EBIT (year 1) CAPEX for year 0 Depreciable life of fixed assets Tax rate New working capital for years 1-5 Given 5% $ 100,000 $ 400,000 5 years 30% 20% of EBIT Solution Legend = Value given in problem = Formula/Calculation Analysis required = Qualitative analysis or Short answer required = Goal Seek or Solver cell = Crystal Ball Input = Crystal Ball Output 7 8 9 10 11 Solution Year 0 $ 12 13 14 15 16 17 18 19 100,000 $ (30,000) 70,000 $ 80,000 2 105,000 $ (31,500) 73,500 $ 80,000 3 3 110,250 $ (33,075) 77,175 $ 80,000 4 115,763 $ (34,729) 81,034 $ 80,000 5 121,551 (36,465) 85,085 80,000 $ EBIT Taxes NOPAT Plus: Depreciation Less: CAPEX Less: Net working capital needs (See Note 1) Plus: Salvage value of the fixed assets in year 5 Firm Free Cash Flow (FFCF) (400,000) (20,000) (1,000) (1,050) (1,103) (1,158) 24,310 20 Note 1: At the end of year 5 the firm liquidates all of it's investment in net operating working capital. $ (420,000) $ $ 149,000 $ 152,450 $ 156,073 $ 159,876 $ 189,396 $ 400,000 $ 320,000 $ 240,000 $ 160,000 $ 80,000 400,000 21 22 23 24 25 26 27 28 29 30 31 32 Net Fixed Assets (beginning of the year) Plus: CAPEX Less: Depreciation Expense for the Year Net Fixed Assets (end of the year) (80,000) $ 400,000 $ (80,000) (80,000) 320,000 $ 240,000 $ (80,000) 160,000 $ (80,000) 80,000 $ 18.00% Discount Rate NPV IRR 33 Break Even Analysis 34 35 36 37 variable Expected Value Critical Value % of Change Sales Growth EBIT (1) Tax Rate NWC/sales Discount Ratel CAPEX for year o 38 39 40 41 Please identify the top 3 value drivers Year 0 1 = $C$6*(1+ $C$5)^(D14-1) --D15'$C$9 =D15+D16 =C7/08 0 2 = $C$6*(1+ $C$5)*(E14-1) --E15'$C$9 -E15.E16 =D18 0 3 = $C$6*(1+ $C$5)*(F14-1) F15'$C$3 F15+F15 =E18 0 = $C$6"(1-$C$5) (G14-1) --G15'$C$9 =G15 G16 =F18 0 5 = $C$6*(1+ $C$5)*(H14-1) =-H15"$C$9 =H15+H16 =G18 0 =-C7 --(D15-C15)'$C$10 --(E15-D15)'$C$10 --(F15-E15)'$C$10 -(G15-F160*$10 --(H15-G15)'$C$10 --[115-H15)'$C$10 =C19.C20 =D17+D18+D19+D20 =E17.E18 E19.E20 =F17-F18F19.F20 =G17.G18+G19.G20 =H27 =H17-H18H19.H20-H21 --C19 --C18 =C24.C25-C26 =C27 0 -016 =D24+D25+D26 =D27 0 =D26-D2575 =E24.E25-E26 =E27 0 -E26-E25/5 =F24+F25-F26 =F27 0 =F26-F25/5 =G24+G25.G26 =G27 0 =G26-G25/5 =H24+H25+H26

Answer & Explanation Solved by verified expert

See Answer