PLEASE SOLVE THIS USING DCF VALUATION METHOD IN DETAIL (PLEASE PROVIDE IN DETAIL FORMULA OF EXCEL)

You have been given the summarised financial statement of a company. You are required to forecast the financial statement for the next three years and value the company using two stage DCF model using free cash flow to firm. Assume a perpetual growth of 4% for the purpose of valuation. Hint: Perpetual value of cash flow under Gordon growth model can be found using the following formula: FCF1/(kg) where FCF1 represents the free cash flow next year; k represents cost of capital and g represents perpertual growth rate Income Statement (Rs.Incrores,exceptpersharenumbers)SalesOperatingexpenses(cash)DepreciationandamortizationOperatingprofitInterestexpensesProfitbeforetaxTaxNetprofitAveragesharesoutstandingEPS2016365.0(140.0)(30.0)195.0(29.0)166.0(53.1)112.954.02.1 Balance Sheet \begin{tabular}{lr} (Rs. In crores, except per share numbers) & 2016 \\ Fixed Assets & 240 \\ Debtors & 65 \\ Inventory & 54 \\ Cash & 45 \\ \hline Total Assets & 404 \\ \hline \hline & \\ Borrowings & 185.0 \\ Payables & 55.0 \\ Equity & 164.0 \\ \hline Total Liability & 404 \\ \hline \hline \end{tabular} Cash flow (Rs.Incrores,exceptpersharenumbers)NetProfitDepreciatioandAmortizationInterestexpensesIncreaseindebtorsIncreaseininventoryIncreaseinpayablesOperatingcashflowPurchaseoffixedassetsOtherinvestingcashflowInvestingcashflowLoanrepaidInterestexpensesInvestingcashflow2016112.930.029.0(8.0)(5.0)4.0162.9(45.0)(16.0)(61.0)(35.0) Total cash flow 37.90 Use the following assumption for this problem Sales is expected to grow by 15% next year followed by 10% and 5% for the subsequent two years Operating margin is expected to increase by 25 bps each year over the next three years Depreciation shall be calculated under WDV method on fixed assets @10\% Capex is expected to be 5% of sales for the year Interest expense is expected to be 15% of loan outstanding Assume no loan is borrowed or repaid Debtors and inventory is expected to be 15% of sales, each. Payables is expected to be 20% of sales all through The weighted average cost of capital for the company is 18% You have been given the summarised financial statement of a company. You are required to forecast the financial statement for the next three years and value the company using two stage DCF model using free cash flow to firm. Assume a perpetual growth of 4% for the purpose of valuation. Hint: Perpetual value of cash flow under Gordon growth model can be found using the following formula: FCF1/(kg) where FCF1 represents the free cash flow next year; k represents cost of capital and g represents perpertual growth rate Income Statement (Rs.Incrores,exceptpersharenumbers)SalesOperatingexpenses(cash)DepreciationandamortizationOperatingprofitInterestexpensesProfitbeforetaxTaxNetprofitAveragesharesoutstandingEPS2016365.0(140.0)(30.0)195.0(29.0)166.0(53.1)112.954.02.1 Balance Sheet \begin{tabular}{lr} (Rs. In crores, except per share numbers) & 2016 \\ Fixed Assets & 240 \\ Debtors & 65 \\ Inventory & 54 \\ Cash & 45 \\ \hline Total Assets & 404 \\ \hline \hline & \\ Borrowings & 185.0 \\ Payables & 55.0 \\ Equity & 164.0 \\ \hline Total Liability & 404 \\ \hline \hline \end{tabular} Cash flow (Rs.Incrores,exceptpersharenumbers)NetProfitDepreciatioandAmortizationInterestexpensesIncreaseindebtorsIncreaseininventoryIncreaseinpayablesOperatingcashflowPurchaseoffixedassetsOtherinvestingcashflowInvestingcashflowLoanrepaidInterestexpensesInvestingcashflow2016112.930.029.0(8.0)(5.0)4.0162.9(45.0)(16.0)(61.0)(35.0) Total cash flow 37.90 Use the following assumption for this problem Sales is expected to grow by 15% next year followed by 10% and 5% for the subsequent two years Operating margin is expected to increase by 25 bps each year over the next three years Depreciation shall be calculated under WDV method on fixed assets @10\% Capex is expected to be 5% of sales for the year Interest expense is expected to be 15% of loan outstanding Assume no loan is borrowed or repaid Debtors and inventory is expected to be 15% of sales, each. Payables is expected to be 20% of sales all through The weighted average cost of capital for the company is 18%