Financial modelling Notes, Lecture notes of Finance

Download Financial modelling Notes and more Lecture notes Finance in PDF only on Docsity!

Here, we will try to show you the main steps that are necessary to build a complete financial model (the same model that is created in the video lessons).

First off, start by organizing historical financials in clean and good-looking output sheets. Calculate historical growth and profitability margins.

Build assumptions for the development of Revenues (considering historical trends and industry outlook), Cogs (project as a percentage of revenues and consider historical trends), and Opex (project as a percentage of revenues and consider historical trends).

You will need a combination of functions that will extract the respective scenario you have selected. Check out the videos. We showed several combinations (Choose & Match, Offset & Match, Vlookup & Columns, Vlookup & Match, etc.).

Multiply revenues and the percentage that Cogs are expected to be of revenues to obtain Cogs. Do the same for Opex

Calculate historical DSO, DIO and DPO figures. Calculate the historical weight of Other assets and Other liabilities with respect to revenues.

Create a “Fixed Asset Roll Forward” sheet. Calculate historical percentage of D&A and Capex with respect to Beginning PP&E. Then use the average in order to forecast D&A and Capex in the forecast period. An alternative approach could be to model Capex as a percentage of revenues. Model Capex as a percentage of Beginning PP&E or as a percentage of revenues

Create a “Financial liabilities” sheet. Depending on the number of debt facilities that the company has you will have to build a detailed schedule of payments for each of them. Try to separate debt repayments and interest expenses. Use the PMT function to calculate the firm’s annual or monthly debt payment if you assume that the company will extinguish its debt in 5,10,15 or 20 years. Calculate the portion paid for Interest expenses Calculate the portion paid for debt repayment Assume no new debt

Now that we have calculated Net Income, we can build an equity schedule. Let’s create a new sheet called “Equity schedule”.

Net Income feeds the Equity schedule. The other parameters are Beginning equity, Increase of capital, Dividends, and the end result is Ending equity. Then, once we have forecasted Ending equity, it feeds the Balance Sheet. Use Ending equity of the last historical period as a Beginning equity for the first period Assume there will be no increases of capital in the forecast period Model dividends as a percentage of Net Income or Cash Flow

Calculate the firm’s Cash flow in the forecast period.

Complete the Balance Sheet by summing Beginning Cash and Net Cash Flow for the respective year. Bear in mind that Beginning Cash is Ending Cash for the previous year. As you can see here, once we have done that the Balance Sheet balances. As it should 