Creating Financial Model — Microsoft Excel
In this article, we will walk you through the core concept of creating financial model in finance using excel. We will understanding and deep dive into the intricacies of learning step by step manner, so that we will precisely learn each and every part of creating financial model.
Creating Financial Modeling
Let’s make a financial model by following the steps below:
- Mapping: First, we will have an unarranged set of data from the income statement. We must map this to create the output income statement.
As we can see in the above figure , the data is unarranged and we must do mapping for this unarranged data to build an output income statement. Income statement has the calculation as shown in table below.
Let’s map the data of above figure as shown in the above table,
Insert a column between Particulars and 2019-20 and give the name as per table.
The above figure, is the mapping of data of income statement to build an output income statement.
2. Now, create an output income statement as per the given data for every year.
- First, create the format for income statement.
- As shown in the above figure, we have created the format of income statement.
3. Now we put figures in the format with Sumifs formula in Revenue, Cost of goods sold, Operating Expenses, D & A, Interest Expenses and Taxes
rows. We will get all these in the above figure.
=SUMIFS Data_of_Income_Statement!C$2:C$23,Data_of_Income_
Statement, $B$2:$B$23,Income_Statement!$A4, is the formula used in cell B4 in the above figure for Revenue.
4. Copy and paste the formula in C4:D4 to get the other values of Revenue=-SUMIFS(Data_of_Income_Statement
C$2:C$23,Data_of_Income_
Statement!$B$2:$B$23,Income_Statement!$A5) is the formula used in B5 as
shown in Figure 6.5 for Cost of goods sold.
Copy and paste the formula in cell C5:D5 to get the other values of COGS.
Now, copy all 4 cells together (B5:D5) and paste in B7:D7, B9:D9, B11:D11 and B13:D13 to get the values of Operating Expenses, D&A, Interest Expenses and Taxes.
5. Now filled the remaining line items to complete the income statement.
Now, we get gross profit from Revenue – Cost of Goods Sold, EBITDA = Gross
Profit – Operating expenses, EBIT = EBITA – D&A, EBT = EBIT – Interest Expenses, Net Income = EBT – Taxes, as shown in the figure below.
The above figure, output is the income statement after all calculations.
6. Now we move forward to the balance sheet.
The above figure, is the balance sheet of company for 19-20, 20-21 and 21-22.
7. Now, we will add the forecasting period.
As shown in the above figure, we have added 22-23, 23-24,24-25,25-26 and 26-27 for
the next 5 years forecasting.
8. Now we will add scenarios.
Revenue % growth: Year on Year (YoY) growth/variance percentages to
estimate forecasting period.
Best Case, Base Case, Worst Case: We create cases with Best, Base and Worst Cases.
COGS as % of revenue: Cost of Goods Sold percentage against revenue to calculate COGS for forecasting periods.
Best Case, Base Case, Worst Case – We create cases with Best, Base and Worst Cases.
OpEx as % of revenue: Operating Expenses percentage against revenue to calculate OpEx for forecasting periods.
Best Case, Base Case, Worst Case – We create cases with Best, Base and Worst Cases.
We also add a cell for scenario for the selection of Best Case, Base Case, Worst Case. Calculation formula of Gross Profit, EBITDA, EBIT, EBT and Net Income. Currently, it is zero, as shown in the above figure.
9. Now we will calculate Revenuer growth, COGS as % of revenue and OpEx as % of revenue for the past years, as shown in the figure below.
Revenue Growth = (Current Year – Previous Year)/Current Year
Cell C17 =ROUND((C4-B4)/C4,3) D17 =ROUND((D4-C4)/D4,3)
COGS as % of Revenue = COGS/Revenue
B23 = ROUND(B5/B4,3) and copy paste this to the next two cells
OpEx as % of Revenue = Opex/Revenue
B29 =ROUND(B7/B4,3) and copy paste this to the next two cells and we cannot create case scenarios for historical years, so all scenarios are the same for the past years, as shown in the figure below.
As we can see in the above figure, we have the same case scenarios in all three metrics. We just used equal to formula for Selected Case in Best Case, Base Case and Worst Case for revenue % COGS % and OpEx %.
10. Now we have figures of percentages of all metrics. The next step is to create Best Case, Base Case and Worst Case scenarios for all three metrics.
- We will take the best percentage between COGS as % of revenue and OpEx as % of revenue, best case and worst percentage for worst case and average of all three years for base case.
- For revenue growth, we have only two percentages, so we will take the lowest as the worst, the highest as the best and the average of both as the base case.
11. Feed all percentages in forecasting years, as shown in the above figure.
- Excel formula is used in all scenarios, as shown in the figure below.
- As we can see in the figure above, the MAX, AVERAGE, & MIN functions are used for the Best, Base & Worst Case, respectively.
12. Now we need to create a drop down list in cell D1 and create a VLookup formula for the selected case. Let’s see how.
- As we can see in the above figure , we have selected A19:A21 as the drop down
list in Source in data validation dialog box. - Then, click on OK.
13. Cell No. E18 to I18 are to be used for Revenue Growth percentages, Cell No. E24 to I24 for COGS as % of Revenue and E30 to I30 for selected cases with
VLOOKUP functions.
Cell E18 to I18=VLOOKUP($D$1,$A$19:$I$21,COLUMN(),0) for revenue %
growth
Where Column() denotes the column number that is required for the VLOOKUP formula.
Column A counts as 1, B as 2, C as 3, D as 4, E as 5, F as 6, G as 7, H as 8 and I as 9.
Cell E24 to I24 =VLOOKUP($D$1,$A$25:$I$27,COLUMN(),0) for COGS as %
of revenue.
Cell E30 to I30 =VLOOKUP($D$1,$A$31:$I$33,COLUMN(),0) for OpEx as % of revenue, as shown in the figure below.
As we can see in the above figure, we have selected the Best Case, and the Selected Case is showing the Best Case percentages.
- As we can see in the above figure, when we select the best case, we get the base case percentages in the selected case cells, the same will happen for the worst case.
14. Now, it’s time to move forward and work on the balance sheet and calculate DSO, DPO and DIO,
and forecasting for next 5 years
We also add Other Liabilities % , Other Assets % and Provision % with the previous working capital metrics.
As we can see in the above figure, we have added metrics in balance sheets.
15. Now we will calculate with formulas that we learnt in Chapter 4, Working Capital,
DSO = Trade Receivable/Revenue*365
DPO = Trade Payable/Revenue*365
DIO = Inventory/Revenue*365
Other Assets % = Other Assets/Revenue
Other Liabilities % = Other Liabilities/Revenue
Provision % = Provisions/Revenue
Now, put the previous formula in Excel. We can get revenue from the income statement sheet.
As we can see in the above figure, we have found the DSO, DPO and DIO, along with Other Assets % and Other Liabilities.
Formula used for all three years are mentioned in the figure below.
The above figure, shows the formulas used for various components’ calculations.
16. The next step is to add the forecasting period in the balance sheet and estimation calculations for the forecasting period.
Let’s add the forecasting period in Balance Sheet and we average all three historical periods for all five metrics to get the estimation for the first year of forecasting, which is the year ending 31 March 2023.
As we can see in the above figure, The next step is to add the forecasting period in the balance sheet and estimation calculations for the forecasting period.
Let’s add the forecasting period in Balance Sheet and we average all three historical periods for all five metrics to get the estimation for the first year of forecasting, which is the year ending 31 March 2023, we get DSO, DPO, DIO, Other Assets %, Other Liabilities % Provision % to be 25.4, 28.0, 18.0,2.6%, 2.0% and 1.9%,
respectively for the first year forecasting. The formulas used to calculate forecasting of metrics in balance sheet are shown in the figure below.
As we can see in the above figure, the average function is used for all metrics and the averages are rolling forwards averages.
17. Now that we have all forecasting metrics in the balance sheet and Income Statement, the next step is to calculate the values of Income statement and balance sheet of the next 5 years from the estimated metrics.
Now we will calculate the values for the forecasting period in Income
Statement, as shown in the Figure below.
As we can see in the above figure , we get the values of Revenue, COGS and OpEx in Income Statement. D & A, Interest Expenses and Taxes will be calculated later in forecasting.
The formulas used to calculate Revenue, COGS and OpEx are shown in the figure below.
As we can see in the above figure, Revenue = Last Year Sales * (1 + Estimated Growth Percentage of that year)
COGS = Revenue * Estimated Percentage
OpEx = Revenue * Estimated Percentage
Provisions = Revenue * Estimated Percentage
18. Now we will work on balance sheet matrics, as shown in the figure below.
Trade Receivable = Revenue/365*DSO
Trade Payable = Revenue/365*DPO
Inventory = Revenue/365*DIO
Other Assets = Revenue * Estimated Percentage
Other Liabilities = Revenue * Estimated Percentage
Provision = Revenue * Estimated Percentage
As we can see in the above figure, we get the values of Trade Receivables, Trade Payables, Inventory, Other Assets, Other Liabilities and Provision
for the next 5 years on estimated basis with rolling forward average.
Rolling forward average means for the first year, we use all three historical years, 31 Mar 19 to 31 Mar 22, while for the second year, we use the last 2 historical years and 1 forecasted year, 31 Mar 20 to 31 Mar 22 and so on.
Fixed Assets, Cash, Debts and Equity will be calculated later in
forecasting.
The formulas used for the previous calculations are shown in the figure below.
As we can see in the above figure, all formulas are defined by revenue.
19. Now we will calculate the Fixed Assets Forecasting.
Add a new sheet in the Forecasting workbook, rename it as FA and format it
and add the values we have from the income statement and balance sheet, as shown in the figure below.
As we can see in the above figure, we put whatever values we have.
We have closing Fixed Assets (FA) in the balance sheet, D&A in the income statement and the opening FA is equal to the closing FA of previous years, so we have the opening FA as well.
Only Capex is the new thing in the sheet, it can be calculated with the following formula:
Capex = Closing FA – Opening FA + D&A
For the year ending 31 Mar 20, we don’t have Opening FA, so we cannot calculate Capex in this case and keep it blank; this is shown in the above figure. Now, let’s come to the year ending 31 Mar 21.
Opening FA = 512.33, D&A = 78.00 and Closing FA = 478.90
Capex = 478.90 – 512.33 + 78.00 = 44.57 is the result, as shown in the above figure.
20. Now, let’s add forecasting metrics to forecasting future values, as shown in the figure below.
As we can see in the above figure, we have defined D&A as a % of Opening Fixed Assets and Capex as a % of Opening Fixed Assets to get the future estimates.
D&A as a % of Opening Fixed Assets = D&A/Opening Fixed Assets
Capex as a % of Opening Fixed Assets = Capex/Opening Fixed Assets
Now, rolling forward average the both years for next 5 years, as we did in
balance sheet metrics.
As we can see in the above figure, DA% = -16.5% = [(-15.2% + (-17.7%))/2] and Capex% = 8.5%= [(8.7%+8.3%)/2] and so on.
21. Next, get the forecasted values.
Opening FA is equal to Closing FA of the previous year
Closing FA = Opening FA + Capex – D&A
D&A is already in negative format, so we will use the formula =sum(E4:E6) to get Closing FA, as shown in the above figure and we already have the estimated percentages for D&A and Capex.
Lets put the previous calculations/estimations in our sheet, as shown in the above figure.
As we can see in the above figure, we have estimated values of Fixed Assets for forecasting. The formulas used in the above figure.
22. Now we put values of D&A of the forecasted period in the Income Statement, as shown in the above figure and closing Fixed Assets in the balance sheet, as shown in the figure below.
As we can see in the above figure, the highlighted cells of D&A came from Fixed Assets forecasting.
The formulas used in the figure below is as follows,
E9 =FA!E5, F9 = FA!F5 and so on.
As we can see in the above figure, we got the values of fixed assets as the highlighted cells.
The formulas used in the Figure below is as follows,
E6 =FA!E7, F6 =FA!F7 and so on.
23. The next step is to calculate the debt and repayment schedule for the forecasted period.
Company decides to pay the their all debts in next five years to become debt
free and rate of interest of debts are 10% First of all, add a new sheet and rename it to Debts, format it as shown in the figure below and put the value of closing balance of the last year of the historical period.
As we can see in the above figure, we have the value closing balances of Debts for FY 21-22 and interest rate 10% and period 5 years. We calculate the PMT on the basis of the available information.
PMT =ROUND(PMT(B9,B10,-D7),2)
Next, we will make a payment schedule, as shown in the figure below.
As we can see in the above figure, we have calculated Installment, Interest and Principal Repayment with the help of PMT, IPMT and PPMT functions. Formula used for payment schedule is shown in the figure below.
As we can see in the above figure, PMT, IPMT and PPMT are used for payment schedule.
24. Now, transfer repayment schedule to Debts Forecasting.
Closing Debts is taken from the balance sheet for the last historical year and Opening Debts is equal to Closing Debt of the previous year.
Closing Debts for forecasted period = Opening Debts + New Debts – Principal Repayment.
As we can see in the above figure, Debts and Principal Repayment Calculated for Forecasted Period
Formula used in forecasted period is shown in the figure below.
As we can see in the above figure, Excel formula used in calculation of Debts and Principal Repayment of Forecasting Period.
25. The next step is to put the Closing Debts and Interest Expenses in balance sheet and income statement, respectively, as shown in the figure below.
As we can see in the above figure, the highlighted values are Debts of Forecasted Period. Formula for the figure below is as follows,
E14=Debts!E17, F14 = Debts!F17 and so on.
As we can see in the above figure, the highlighted values are Interest Expenses of Forecasted Period.
Formula for the above figure is as follows,
E11 = -Debts!E16, F11 = -Debts!F16 and so on.
26. Our progress is on the mark till now, we are learning everything step by step.
The next step is to complete the Income Statement.
Only taxes are pending, as shown in the above figure, in the previous step.
Tax rate is 30% of EBT, so let’s calculate the taxes.
As we can see in the above figure, tax is deducted from EBT to get the Net Income.
The formula used to calculate tax is shown in the figure below.
As we can see in the above figure, if EBT is positive (profit), the formula EBT * 30% and if it is negative loss, tax will be zero because we cannot pay tax on loss.
27. We will create the equity schedule to fill equity values in the balance sheet.
A company decided to pay dividends @ 25% on net income and not to introduce more capital in business.
Add a new sheet and rename it to Equity, format it as shown in Figure below.
As we can see in the above figure, we need only the closing equity of the year ended 31 Mar 2022.
Now, we will put the value of Increase in Capital, which is nil for every year as decided by the company, Net Income/(Loss) from Income statement and Dividends @ 25% if there is a profit.
Opening Equity is equal to closing equity of the previous years.
As we can see in the above figure, we got the closing equity to put the value of equity in the balance sheet.
Excel formulas are used in equity schedule, as shown in the figure below.
As we can see in the above figure, we have Excel formula for equity schedule.
28. Now we can fill the equity in the balance sheet.
As we can see in the above figure, we have filled the values of equity in the balance sheet from the equity schedule, in the highlighted cells.
The Excel formula used is as follows,
E16 =Equity!E8, F16 = Equtiy!F8 and so on.
29. Now, we have reached our final step of Cash Flow Preparation and Cash Value, filling in the balance sheet.
Create a new sheet and rename it to CFS, format as shown in the figure below.
As we can see in the above figure, we have created a format of Cash Flow Statement for the forecasted period.
30. Put values as mentioned in the remarks of the forecasted CFS format in the Figure below.
As we can see in the above figure, we got the Net Cash Flow of every year. It is negative in the first year, while it is positive in all the other years.
The formulas used for CFS are mentioned in the figure below.
In the above figure, shows all formulas derived from income statement, Balance sheet, equity, and Fixed Assets Sheets.
31. Now we put Net Cash Flow in the balance sheet to complete it.
Cash = Net Cash Flow of Cash Flow Statement + Cash of Previous Year
As we can see in the above figure, we got the values of cash.
The formula used in Cash Flow is as follows,
E8 =D8+CFS!C19 = 34.59 + (10.14) = 24.25
F8 =E8+CFS!D19 = 24.25 + 30.06 = 54.51
and so on.
We have completed our forecasting. Let’s check all cases of Income Statement, Balance Sheet and Cash Flow Statement in the figure below.
As we can see in the above figure, we have the base case income statement after selecting the base case.
As we can see in the above figure, we have the base case balance sheet after selecting the base case.
As we can see in the above figure, we have the base case cash flow statement after selecting the base case.
As we can see in the above figure, we have the best case income statement after the selecting best case.
As we can see in the above figure, we have the best case balance sheet after selecting the best case.
As we can see in the above figure, we have the best case cash flow statement after selecting the best case.
As we can see in the above figure, we have the worst case income statement after selecting the worst case.
As we can see in the above figure, we have the worst case balance sheet after selecting the worst case.
As we can see in the above figure, we have the worst case cash flow statement after selecting the worst case.
Conclusion
Finally, we will covering the most important fundamentals of financial modeling and along with that we also take a hands on step by step drive of, how to create a financial model with microsoft excel and we have completed the forecasting modeling, which included cash flow modeling.
We have learnt how to create case scneario and can forecast the income statement, balance sheet and cash flow statement, the three statements model.
