Learn Financial Modelling

As we approach the final chapter in this module, let us quickly review all that we have gathered thus far.

We started by going over how to set up the excel sheet for constructing a financial model. We talked about style accuracy and how important it is to make sure that information in various cells is congruent throughout all of your sheets. For instance, make sure that column J always has data from year 6 in sheet 1 as well as joined to the same information in other tabs.

We transferred the historical data from the annual report, emphasizing the P&L and Balance Sheet statements. Be informed that there are other places to get this information, such as third-party websites. Nevertheless, the company’s annual report is the most reliable source; thus, it should always be favoured. Additionally, we tinted assumptions and calculated numbers.

We created an assumption sheet containing all assumptions, split into Profit & Loss and Balance Sheet sections. We examined two methods of making assumptions – the growth driver, which utilises past averages, and the percentage technique.

For certain businesses, having a dedicated revenue model can be invaluable. It provides detailed information on what could affect the business’s income.

We constructed the asset and debt schedule of the firm. The asset schedule offers us understanding into depreciation and CAPEX, while the debt schedule provides us with insights regarding the cost of borrowing. Both these items are linked to the balance sheet.

This schedule is made up of both Profit & Loss and Balance Sheet figures. We have created a Reserve Schedule using these numerical data sets.

Having put together all the timetables and forecasts, we now create P&L and Balance sheet predictions. At this juncture, every entry on the two documents is estimated. All that is left are the cash and its balance figures on the balance sheet.

We constructed the cash flow statement utilizing an indirect method to ascertain the cash balance. This final value is then fed back to the balance sheet, and if all the numbers are accurate, it should arrive at a balanced outcome.

The financial model has reportedly achieved a significant milestone when the cash value was recorded on the balance sheet, creating a harmonious balance.

Following the cash flow statement chapter, we transition to discussing the theory of valuations. Next, we put that learning into practice by implementing a valuation model and integrating all theories together.

In the preceding chapters, mainly between 14 and 17, we took a look at theories concerning assessment. Now in this chapter, let us put the discounted cash flow valuation (DCF) formula into practice through the primary model. The result of the DCF model is the stock price of the firm.

– Assumptions

The DCF valuation sheet will have a different format than the others, since there is no past data to be used. As always, we will begin by labelling columns A and B, then rename the sheet.

In the beginning, we will gather the necessary data to execute DCF.

We can use long-dated Government securities (bond) yield as an proxy for the risk-free rate. This data is made available on RBI’s website, and presently the 10-year bond yield serves as a suitable option, currently standing at 7%.

Calculating the beta of a stock is straightforward; refer to section 11.5 here for details. The company we’re modelling has a beta of 1.2, which indicates it’s a high beta stock. Fortunately, with this integrated financial model, you’re free to update these figures whenever you like.

The anticipated market returns run between 10% and 12%, so we’ll select a rate of 12% for now.

The cost of Equity is derived from the CAPM formula, which was discussed in the previous chapters. Essentially, this figure is calculated as the risk-free rate plus a multiplication of the difference between the expected market rate and the risk-free rate with the company’s beta. It’s quite simple to compute if you refer to the excel formula.

The cost of debt for the company is 10%.

The tax rate can be changed to a percentage of one’s choosing. 25% is the default, but feel free to adjust it as you deem fit.

The ideal debt-to-equity ratio is set at 50%. Having no debt is a desirable situation, but it can be difficult to achieve. Companies may have to take on credit for capital expenses, yet need to make sure they don’t breach this percentage.

The terminal growth rate is a key assumption for the DCF model. As previously discussed, we will likely use a value that falls between 4 and 5%, close to the nation’s long-term inflation rate.

The weighted average cost of capital (WACC) is something that we will calculate in excel directly. But I do hope you recollect the discussion we had previously on WACC.

The weighted average cost of capital (WACC) is employed to weigh up the anticipated return on both debt and equity. This figure is then used in order to discount future cash flows.