Learn Financial Modelling

  1. Financial Modelling
    1. Financial Modelling Introduction
    2. Financial Modelling Tools & steps
    3. How to Make a Financial Model and choose the best Company and Excel Workbook Setup?
    4. How to build a financial model Step-by-Step Guide to Excel Sheet Setup?
    5. Financial Statements: A Step-by-Step Guide to Extracting Historical Data
    6. Financial modelling excel
    7. Learn financial modelling Balance Sheets, P&L, and Assumptions Know About
    8. What is financial modelling Assumptions and Projections?
    9. Financial modelling and valuation
    10. Investment decision calculation
    11. The balance sheet’s asset side reveals the company’s line items.
    12. Revenue Model & Growth Rate in in P&L Assumptions
    13. Basics of financial modelling CAPEX and Asset Schedule
    14. Financial Analysis: Gross Block and CAPEX
    15. Gross block & Capex: Constructing the Asset Schedule
    16. Depreciation : Connecting P&L and Balance Sheet for Accurate Asset Forecasting
    17. depreciation expense : Exploring Different Methods in Financial Modeling
    18. Debt Management: Connecting P&L and Balance Sheet for Accurate Liability Projection
    19. Interest Rate Calculation & Debt Schedule
    20. Share Capital & Reserves
    21. IPOs and Under subscription : Bata’s Share Capital Dynamics
    22. Reserves & Surplus understanding Bata schedule
    23. Reserves and surplus schedule How to Build on Excel
    24. Financial modelling projections
    25. Balance Sheet Projections and Completing Reserves Schedule
    26. Cash Flow Statements Analysing Operations, Investments, and Financing Activities
    27. What Is Valuation for Investor
    28. Free Cash Flow Key Components, Formulas and How to Calculate?
    29. FCFF and FCFE uses in Mastering Free Cash Flow Calculation
    30. WACC Weighted Average Cost of Capital Analysis
    31. Market Risk Premium analysis
    32. Tax Shield and its Impact on Equity Holder Returns
    33. Weighted Average Cost of Capital and Terminal Growth in Valuation
    34. Terminal Value Understanding Perpetual Cash Flow Projections in DCF Model
    35. Learn Financial Modelling
    36. Free Cash Flow to the Firm (FCFF) Calculation with examples
    37. Stock Valuation DCF Model & Stock Market Value
Marketopedia / Financial Modelling / 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.