Question
Please answer in excel formula. In spring of 2015, Jemison Electric was considering an investment in a new distribution center. Jemisons CFO anticipates additional EBIT
Please answer in excel formula.
In spring of 2015, Jemison Electric was considering an investment in a new distribution center. Jemisons CFO anticipates additional EBIT of $100000 for the first year of operation and over 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 $400000 that will be depreciated over a 5 year period towards Zero salvage value using straight line depreciation of $80000 per year. Jemisons CFO estimates that distribution center will need operating net working capital equal to 20% of EBIT to support operation. Assuming Tax rate to be 30%. And if the annual project free cash flow for each of next five years is given in the table below then,
| 0 | 1 | 2 | 3 | 4 | 5 |
EBIT |
| 100000 | 105000 | 110250 | 115763 | 121551 |
Taxes |
| (30000) | (31500) | (33075) | (34729) | (36465)7 |
NOPAT |
| 70000 | 73500 | 77175 | 81034 | 85085 |
Plus: Depreciation |
| 80000 | 80000 | 80000 | 80000 | 80000 |
Less: CAPEX | (400000) |
|
|
|
|
|
Less: Net working capital needs | (20000) | (1000) | (1050) | (1103) | (1158) | 24310 |
Plus: Salvage Value of Fixed Assets in 5 years |
|
|
|
|
|
|
Free Cash Flow (FCF) | (420000) | 149000 | 152450 | 156073 | 159876 | 189396 |
|
|
|
|
|
|
|
Net Fixed Assets (Beginning of Year) |
| 400000 | 320000 | 240000 | 160000 | 80000 |
Less : CAPEX | 400000 |
|
|
|
|
|
Less: Depreciation |
| (80000) | (80000) | (80000) | (80000) | (80000) |
Net Fixed Assets (End of Year) | 400000 | 320000 | 240000 | 160000 | 80000 | - |
- If Discount rate is 18%, what is NPV and IRR? (In Excel formulas)
- Conduct Breakeven Sensitivity Analysis (In excel formulas)
variable | Expected Value | Critical Value | % of Change |
Sales Growth |
|
|
|
EBIT (1) |
|
|
|
Tax Rate |
|
|
|
NWC/sales |
|
|
|
Discount Rate |
|
|
|
CAPEX for year 0 |
|
|
|
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started