Please show the calculation & formulas in Excel Sheet.

XYZ Ltd is planning to invest in a project which costs in Year 0 a capital investment of Rs 80 crores. It has made following projections. Its expected sales & EBITDA forecast is as follows:

Year Sales EBITDA %

1 Rs 10 cr 0%

2 Rs 20 cr 10%

3 Rs 50 cr 15%

4 Rs 100cr 20%

5 Rs 120cr 22%

6 Rs 150cr 22%

7 Rs 150cr 22%

Its annual depreciation is Rs 2 cr, Its annual interest costs are Rs 1 cr, tax rate is 34% , Working capital requirements are 15% of sales. Annual capital expenditure requirements after initial capital investment in Year 0 are Year 1 (Rs 5 cr), Year 2 (Rs 3 cr) and there after Rs 2 crores annually. In year 7 business will have a terminal value of Rs 200 crores i.e. in 7th year at the end of project, the project residual things ie assets and working capital etc can be sold for Rs 200 crores.

For XYZ Ltd cost of capital or hurdle rate assume Debt, Equity 1:1, pre tax cost of debt is 14%, cost of equity is 25%.

Find NPV, IRR and Payback period and give your conclusion if XYZ Ltd should do the project.

Hint: Use excel to calculate XYZ Ltd WACC (weighted average cost of capital) and then prepare cash flows to find year wise final + - cash flows ie cash outflows or cash inflows and then find IRR and NPV at WACC PV Discounting Rate. When making cash flows remember to calculate Working Capital Requirements and only take increase/ decrease in Working Capital as you Cash Outlow/ Inflow etc. Terminal value is Cash Inflow in Year 7.