Flag In the spring of 2015, Jemison Electric was considering an investment in a new distribution center. Jemison's 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. Jemison's CFO estimates that the distribution center will need additional net working capital equal to 20% of new EBIT Assuming the firm faces a 30% tax rate; calculate the project's annual project free cash flow (FCF) 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. Fill out chart below. The questions I need answered are: If the discount rate is 18%, what is the NPV of this project? What is the IRR? Conduct a break-even sensitivity analysis and identify the top 3 value drivers And more than just the number, I need to know what cell references you used to get the answer you got!

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 Solution Year 0 $ 1 100,000 $ (30,000) 70,000 $ 80,000 2 105,000 $ (31,500) 73,500 $ 80,000 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 (400,000) (20,000) (1,000) (1,050) (1,103) (1,158) 24,310 Note 1: At the end of year 5 the firm liquidates all of it's investment in net operating working capital. Iyear 5 Firm Free Cash Flow (FFCF) (420,000) $ 149,000 $ 152,450 $ 156,073 $ 159,876 $ 189,396 $ 400,000 320,000 $ 240,000 $ 160,000 $ 80,000 400,000 Net Fixed Assets (beginning of the year) Plus: CAPEX Less: Depreciation Expense for the Year Net Fixed Assets (end of the year) (80,000) (80,000) 240,000 $ (80,000) 160,000 $ (80,000) 80,000 $ (80,000) 400,000 $ 320,000 $ Discount Rate NPV IRR 18.00% 75,998 Break Even Analysis variable Sales Growth EBIT (1) Tax Rate Expected Value Critical Value % of Change 5% 100,000 30% 20% 18.00% 400,000 NWC/sales Discount Rate CAPEX for year 0 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 Solution Year 0 $ 1 100,000 $ (30,000) 70,000 $ 80,000 2 105,000 $ (31,500) 73,500 $ 80,000 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 (400,000) (20,000) (1,000) (1,050) (1,103) (1,158) 24,310 Note 1: At the end of year 5 the firm liquidates all of it's investment in net operating working capital. Iyear 5 Firm Free Cash Flow (FFCF) (420,000) $ 149,000 $ 152,450 $ 156,073 $ 159,876 $ 189,396 $ 400,000 320,000 $ 240,000 $ 160,000 $ 80,000 400,000 Net Fixed Assets (beginning of the year) Plus: CAPEX Less: Depreciation Expense for the Year Net Fixed Assets (end of the year) (80,000) (80,000) 240,000 $ (80,000) 160,000 $ (80,000) 80,000 $ (80,000) 400,000 $ 320,000 $ Discount Rate NPV IRR 18.00% 75,998 Break Even Analysis variable Sales Growth EBIT (1) Tax Rate Expected Value Critical Value % of Change 5% 100,000 30% 20% 18.00% 400,000 NWC/sales Discount Rate CAPEX for year 0