Perform sensitivity analysis to analyze the effect of expenses on the project’s NPV and IRR. In your analysis, allow costs to vary between $0 and $100,000, in $5,000 increments. Use Goal Seek to find what level of expenses Lewis Industries needs to just break even from a NPV perspective (i.e., what level of expenses produces a NPV of $0)? Use the following information to complete.
| Original | Increased by 5000 |
Increased by 10000 |
Increased by 8793 | |
| Incremntal Revenue | 110000 | 110000 | 110000 | 110000 |
| Less Incremantal Expenses | 60000 | 65000 | 70000 | 68793 |
| Less:Depriciation Expenses | 25000 | 25000 | 25000 | 25000 |
| EBIT | 25000 | 20000 | 15000 | 16207 |
| Less: Incometax | 10000 | 8000 | 6000 | 6482.8 |
| NOPAT | 15000 | 12000 | 9000 | 9724.2 |
| Add: Depriciation | 25000 | 25000 | 25000 | 25000 |
| OCF | 40000 | 37000 | 34000 | 34724.2 |
| PVIFA (12% for 4 Year) | 3.0373 | 3.0373 | 3.0373 | 3.0373 |
| 121492 | 112380.1 | 103268.2 | 105467.81 | |
| Wc | 15000 | 15000 | 15000 | 15000 |
| 0.6355 | 0.6355 | 0.6355 | 0.6355 | |
| 9532.5 | 9532.5 | 9532.5 | 9532.5 | |
| Net CF | 131024.5 | 121912.6 | 112800.7 | 115000.31 |
| less: | ||||
| Cost of Equipment | 100000 | 100000 | 100000 | 100000 |
| Increase in NWC | 15000 | 15000 | 15000 | 15000 |
| 16024.5 | 6912.6 | -2199.3 | 0 | |
| Change In NPV | 9111.9 | 18223.8 | ||
| Effect on NPV Due to Change Per 1 $ in cost |
1.82238 | 1.82238 |
We can see from the above calculation that if there is 1 $ increase in the Cost then there is $ 1.82 Change in NPV that is called Sansitivity Analysis .
In the last I have taken the cost 68793 then Npv comes to Zero which shows that of there in cost of 68793 then it is brak down point for the compnay . Company is at indifrance point to install a machine or not
