During our classes so far, we have been building a model that lets us valuate companies in accordance to the Highest and Best Use (HBU) concept. We started with target D/E, which we assumed to be optimal taking industry benchmarks as a proxy for the optimal level of debt to equity ratio. As a result of such operations, the obtained implied debt was not equal to its real value. To deal with the problem, we decided not to present equity valuation using free cash flow to equity technics.
Now we are going to change the way of thinking. We assume now that valuation is going to be performed from the perspective of real debt instead of implied one. This approach should be adopted assuming that valuation is carried out from the perspective of a particular transaction, where there are only two parties involved (i.e. buyer and seller) and, therefore, the assumption of open market existence is not fulfilled. Such situation can be, for example, observed in the case of LBO transactions. To achieve the desired effect, we can easily use Solver and adjust the capital structure in WACC calculation in a way that ensures equivalence between the real and the implied debt. However, PE companies in majority of cases use the FCFE technique as the basic tool of valuation of LBO transactions. Therefore, we need to build a model that will meet the PE funds’ requirements.
Your task is to construct a model that:
1) Makes it possible to estimate FCF for 8 years
2) Starts with the assumed debt and debt repayment schedule
3) Assumes a constant cost of equity
4) Assumes g for FCFE from year 9.
5) Starts the calculation of equity value using FCFE techniques
6) Based on the equity calculations for each year of estimation, makes it possible to estimate WACC, taking into account the real debt repayment schedule
7) Based on p. 6, makes it possible to valuate equity using FCFF and EVA techniques
8) Finishes with pro forma financial statements where assets = equity + liability
To test if your model works, please assume artificial data of an invented company.