Key words: DCF modeling, Excel, Finance

Financial modeling is about data being fed into a spread sheet like MS Excel and then being analyzed by the various options given within the application.

DCF (Discounted Cash Flow) is mainly about the TVM (time value of money).  What we invest today in the time to come if invested properly would be equal to what value in the future.

Excel gives us the capability to build in parameters to regress the data and get the most appropriate results based on the data available.  The higher the sample data the more accurate we will get the result.  If the sample size of the data is too small then even the best of analysis tools will give distorted results.

We can go through a series of steps in order to make a good DCF

To start a financial model off, we’ll first go through the results from the historical data. First, load the past financial statements into Excel, ideally, three-plus years. Reverse engineering for historical periods assumptions comes next, i.e., calculate the key points like gross margins, fixed costs, inventory days, growth rate, variable costs, AP days, etc. From the past calculations, you can formulate assumptions to forecast the future period.

Working on the income statement

Assumptions help you to compute the required income statement with COGS (cost of goods sold), operating expenses, revenue, and GP (gross profit) till EBITDA.

Starting the balance sheet

The Balance Sheet comes after the income statement while preparing the financial model. The functions of COGS and revenue, which are the inventory and accounts receivables should be calculated next including the assumptions of the inventory days and AR days. Furthermore, the functions of COGS as well as AP days, which is accounts payables should be filled up.

Building the support schedules

Preparing the asset’s schedule like PP&E (Property, Plant and Equipment), and interest & debt before thorough the both, income statement and balance sheet. From the historical periods, pull out the PP&E schedule to subtract depreciation from it and add the capital expenses. The historical period gives the information about the debt schedule and then you can minus the repayments from it and add the increases in debt.

Completing the balance sheet & income statement

The supporting schedule stats aid in completing the remaining balance sheet and income statement. Link the asset’s schedule of PP&E to the depreciation and debt schedule to the interest in the income statement. From there, EBT, taxes, and net income can be calculated. Link the closing balance of debt from the schedules to the PP&E (property, plant and equipment) closing balance in the balance sheet. You can figure out the shareholder’s equity by taking the closing balance of the previous year, adding the capital raised and net income, and subtracting the shares purchased or dividends.

Building the Cash Flow statement

Now we have ready income statement and balance sheet, the two major financial statements, the reconciliations method can be used to figure out the cash flow statement. Beginning from the net income, followed by the addition of depreciation, then adjusting the variation in non-monetary working capital and that would result in cash from operations.

Performing the DCF Model Analysis

With the three-statement model ready, it is time to go through the business valuation and measure the free cash flow and discounted it back at the firm’s cost of capital to today.

After this, you may add sensitivity scenarios & analysis

After completion of the above, we need to include scenarios and sensitivity analysis in the model. The purpose of that analysis is to find out what specific part of the company will get affected due to fluctuations in assumptions. That is very helpful for measuring the investment risk or for the purposes of business plans. Example: Should a company raise money if sales volume falls by X percentage?

Building charts and graphs

Presenting the results in a transparent method is what makes a great finance analysis stand out. The most efficient way to convey or depict the outcomes of a financial model is via charts and graphs.

Stress testing and auditing the model

After completing the model, it is still yet to be finished. It is time to initiate the stress testing to observe either the model is working according to the expectations. It is crucial to check the proper working of the formulas of Excel.

The importance of financial modeling in modern businesses

Better financial modeling is unconditionally very critical for any business to continue growing with as little downside risk as possible.

As with mostly all business & investment decisions, you cannot “let your winners run” or “cut your losers short” if you do not complete a detailed analysis of every possible outcome of your choices. And it goes without saying that getting rid of losses is normally more significant than chasing profits.

Without competent financial modeling, all operational businesses are like a ship adrift at sea without navigational instruments.