Financial Modeling Interview Questions

Financial modeling aims to build a representation of a real-world financial situation.This is done to forecast the impact of a future event or decision while considering a company’s expenses and earnings through a timeline on a spreadsheet.

When forecasting a model, we usually consider the company’s performance while preparing an income statement, balance sheet, and cash flow statement.

The analyst could compile more advanced financial models, such as mergers and acquisitions, leveraged buyouts, discounted cash flow analysis, and sensitivity analysis.

We can synthesize the reasons why analysts build four financial models, which are:

1. Acquisitions of businesses/ assets
   Mergers and Acquisitions (M&A) occur when a company purchases another business to control it. The company which acts as the buyer would need to purchase 50% or more of the targeted company’s stock to make decisions for the firm.
   There are several reasons for M&A to take place. Diversifying, economies of scale, cost reduction, and new audience targeting are the most common.
2. Raising capital through debt/ equity.
   Companies have two ways to raise capital and sustain business operations: equity and debt financing. Debt financing consists of borrowing money through loans or bonds, which have interest payments apart from the principal to be paid to the creditor.
   Equity financing is the capital used in the firm that belongs to the owner or the stocks sold to the shareholders in exchange for the company's ownership.
3. Budgeting and Forecasting
   Management uses these tools when establishing their expectations of the firm while understanding their current financial position and estimations based on this.
   Budgeting is what the company expects to achieve in the future based on its financial expectations, such as estimates of revenues and costs, expected cash flows, and expected decreases in debt. Financial forecasting looks into the historical data of the company and estimates results based on previous performance.
4. Business Valuation
   A company valuation determines a firm's economic value by analyzing all business areas between its departments and units.
   There are several ways to calculate the valuation of a company, such as market capitalization, times revenue method, earnings multiplier, DCF method, book value, and liquidation value.

Process of Building a Financial Model

The process that an analyst should follow to ensure that the financial model is reliable and logical to the task at hand is as follows:

1. Prepare and collect historical financial information.

The analyst must collect information from the financial statements for the company for at least three years or more and input it into a spreadsheet.
He should calculate gross margins, inventory days, accounts receivable/payable days, and revenue growth. This would permit him to prepare the forecast later.

2. Prepare the Financial Statements.

After we’ve completed the entry of the historical financial data and included the supporting schedules, such as PPE and Debt and Interest, we can start computing the forecast assumptions for the income statement and balance sheet. Then, we can link them to the cash flow statement with the reconciliation method.

3. Perform the Financial Modeling Valuation.

The analyst should utilize the model he deems the fittest for the valuation, usually the Discounted Cash Flow Model. The Discounted Cash Flow estimates the value of the investment with its expected future cash flows.

The model uses the time value of money and discount rates to find the present value of future cash flows.

The formula for the DCF is the following:

Where:

• CF1 = The Cash Flow for Year One
• CF2 = The Cash Flow for Year Two
• CFN = The Cash Flow for additional years
• r = The discount rate

4. Provide a Sensitivity Analysis of the Model.

We can compute different scenario analyses concerning the model to see how it would perform in different situations. This would lead to noticing the volatility of the investment decision.

5. Provide Visualization for the Forecasted Data.

The display of the forecast model through graphs and charts should provide a clear picture of how feasible the model is when pursuing it. As a result, the management team's decision should be easier on whether to implement the model.

6. Stress Testing the Forecasted Model.

The analyst should test the model based on an extreme-case scenario to see how the financial model would perform under these new circumstances.

A popular scenario is the 2008-2009 US recession, which is used to measure the sustainability of certain financial models.

Principles to design a proper financial model

There are commonly four principles the analyst should follow when designing a financial model, which would be the following:

1. Simple

Financial models should be simple and not filled with unnecessary information. For that meaning, the simpler, the better model.

Formulas should not be longer than one line of the Excel formula bar while limiting the usage of named ranges since the analyst would prefer the formula to be easy to understand for management.

2. Translatable

The analyst must ensure that the model is translatable to every other situation. This would depend on how simple it is to change the model when necessary.

The analyst should try to reduce circular logic whenever possible since it may not calculate correctly while using certain functions and ensure the use of binary flags and control accounts.|

The purpose of this is to record volumes and different business phases.

3. Dependable

The analyst should maintain the model's integrity, meaning its structure should be meticulous, accurate, and logical.

Models should not be on hard-coded numbers. It is advised that the inputs corresponding to the assumptions should update automatically with any change.

A model will not be perfect in such a case, and for this, the analyst should build error checks and not hide columns or rows to make the model easy to audit.

4. Easy to understand

Also, the model should be easily understandable by other analysts and non-analysts when looking into the formulas used.

One of the most common ways to make the financial model understandable is by using proper formatting and ensuring proper number formatting.

The analyst should keep the integrity and try to reduce the rounding errors of the model by maintaining the raw data previously input and not trying to manually round figures.

Only if nIf necessary, the analyst should have available Excel tools at his disposal to maintain the model's integrity and make it understandable to anyone who would use it.

Also, the analyst should try to use different formats for assumptions and calculations while defining the proper format for the numbers.  

Most common types of financial models and their processes

The five most commonly known financial models used by analysts are the following:

1. The Three-Statement Model

This is one of the most common and basic models used by analysts. This method requires linking all three financial statements (Balance Sheet, Income Statement, and Cash Flow Statement) with excel formulas.

2. DCF Model

The DCF model evaluates the projected future cash flows of an investment opportunity. Based on this, the analyst would consider if it is pertinent to make the initial investment.

We would assess the expected cash flows based on a discount rate and then sum up our future cash flows to see if they exceed the required investment in the project.

3. Mergers & Acquisitions Model

This model represents an analysis of the amalgamation of two companies that combine through an M&A process. This occurs when a company offers to buy one company using its shares or the available cash in the purchase.

The process of performing an M&A deal would be the following:

• Making Premises regarding the Acquisition
• Making Assumptions
• Valuation of Both Companies
• Merge of Both Companies and Pro Forma Arrangements
• Deal Accretion/ Dilution

4. IPO Model

This model mostly relies on looking at the financial ratios of similar public companies to derive the value of another company. The analyst would consider the following when doing the comparative analysis:

• Industry Classification
• Geography
• Size (revenue, assets, employees)
• Growth Rate
• Margins and Profitability

This valuation method requires the analyst to include a discount on the IPO when the stock trades in the secondary market.

5. LBO Model

This valuation method is more advanced than most models since it requires the analyst to model complex debt schedules. This process is mostly done in specific financial sectors, such as private equity and investment banking.

The steps needed to perform a leveraged buyout model are:

• Design the financial projections for the target company.
• Align the three financial statements and calculate the business's free cash flow.
• Build the interest and debt schedules.
• Formulate the credit metrics to see how leveraged the transaction would be.
• Determine the Free Cash Flow for the sponsor.
• Calculate the Internal Rate of Return.
• Implement the sensitivity analysis.

Working Capital

Working Capital is mainly known as net working capital, which is obtained by subtracting current assets from current liabilities. This equation would give us a glimpse into the short-term financial health of our company.

This metric looks into the business's operating liquidity and fixed assets such as Property, Plant and Equipment, and Working Capital.

If current liabilities are less than current assets, that would mean that the company cannot support its day-to-day operations and vice versa.

Therefore, the most optimal ratio for the company is 1. On the other hand, having too many assets compared to liabilities could be interpreted as a lack of proper management of the current assets.

We can see the formula for working capital below:

Working Capital = Current Assets – Current Liabilities

Current assets consist of the company’s acquisitions/earnings that have a year, or less lifespan, including cash and cash equivalents, inventory, accounts receivable, notes receivable, and prepaid expenses.

Current liabilities consist of what the company owes in a year or less, including accounts payable, wages payable, current portion of long-term debt, accrued tax payable, dividend payable, and unearned revenue.

The likelihood of a company funding its business operations and growth depends on how positive its working capital is.

A company should aim to have high working capital, but sometimes it’s not always a good sign. For example, it might mean that the company is replenished with inventory and lacks investing its cash surplus.

Differences between Debt and Equity Financing

There are two methods by which a company can raise capital:

1. Equity financing 

The owners must give a percentage of the company’s ownership to raise capital. It is most common in the initial stages of a company and is popularly done for expansion.

Although equity financing permits the owners to raise capital without paying it back, it has disadvantages, such as limited decision-making and the split of profits with the investors depending on their acquired percentage of ownership.

The examples of sources of equity financing are:

• Corporate Investors
• Crowdfunding
• VC firms
• Initial Public Offerings
• Angel Investors

2. Debt financing 

Requires management to raise capital by paying off the sum given on top of interest with a payment structure for a given time.

This method of financing permits management to raise capital without affecting its ownership structure. In addition, the payments are tax-deductible, with the only disadvantage of having to pay interest on the payback of the capital obtained.

The different sources of debt financing are:

• Term Loans
• Lines of Credit
• Invoice Factoring
• P2P Lending Options

To know which financing method would be cheaper for a company, we would examine its current standing and financials.

Debt financing will not be the best option if the company is not profitable since it would have to endure the pressure of the lenders. But conversely, if the business is not meeting the expectations of the board of investors, it could end up divesting its position in the company.

For that matter, the most optimal financing method would depend on the company's profitability and how the owner compares the cost of equity and debt when raising capital. 

Discounted Cash Flow

DCF is a valuation method that tries to determine the current value of an investment. This is done using expected future cash flows.

This valuation method uses a discount rate to find the present value of expected future cash flows.

The purpose is to know if the expected value would be greater than the initial investment based on this decision to invest.

The limitation of the DCF is that it requires the analyst to use estimates and not factual figures.

The DCF makes it difficult to estimate certain concepts, such as economic status, competition, threats, and opportunities. 

The formula for the DCF can be explained as the sum of each periodical cash flow divided by one plus the discount rate or WACC raised to the power of the period.

DCF = CF1/(1+r)^1 + CF2/(1+r)^2 + … + CFn/(1+r)^n

Where:

• CF1 = The Cash Flow for Year One
• CF2 = The Cash Flow for Year Two
• CFn = The Cash Flow for additional years
• r = The discount rate

Forecasting Revenues

Whenever an analyst tries to produce a reliable revenue forecast, he needs to gather data from the business, the consumers, and the industry.

The four most common methods to predict revenues are the following:

1. Straight Line
   It is considered one of the simplest approaches due to the low level of math required. 
   The analyst would use trends to predict the future growth in revenue of the company.
   The business must assume a historical growth rate that remains constant based on the average past growth rate in revenue over the last five years.
2. Moving Average
   Moving averages help us look into patterns hidden in the data. We predict future values by understanding the underlying trend.
   The most common moving averages are the 3-month and 5-month moving averages:

    

    

    

   • The 3-month Moving Average
     It takes the average of the current and previous two months' revenues. So, for example, If we are in September and want to calculate future revenues, we would take the past two months and the current one and the average.
   • The 5-Month Moving Average
     Like the 3-month moving average, the analyst here takes five months instead of only three months to calculate the average.          
3. Simple Linear Regression
   Linear regression allows us to examine the dependency between variables for estimation purposes. To properly use linear regression, we would pick two variables with a high degree of correlation.
   We would know if two variables are highly correlated after visualizing the data with a scatterplot and obtaining the equation to forecast future revenues in Excel.
4. Multiple Linear Regression
   Like simple linear regression, multiple linear regression looks into more variables to provide accurate estimations. We can use the Data Analysis tool from Excel to do the regression and get an output of the estimates on the various coefficients to forecast revenues.

Usually, this type of data is ascertained through annual reports, buyer surveys, and industry groups.

We can calculate the forecasted revenue by using the average selling price based on past prices and multiplying that by the expected number of units to be sold.

We should be able to make a realistic model forecast revenue by taking the projected growth rate on revenue and determining the increment in future earnings.

Market share growth and expansion into new or existing markets could potentially affect projected unit sales and price change when doing the model.

Difference between NPV and XNPV Functions

The Net Present Value function can provide us with a series of cash flows and a discount rate. In addition, this function speculates that the payments are equal and periodic.

This formula helps us estimate an investment's value or understand if a specific investment makes sense.

The formula in Excel would be the following =NPV (rate, [amount 1], [amount 2],…), where the discounted rate is inputted in the formula, and the amounts represent the series of payments made.

We must remember the following when using this formula:

1. All the inputs pertaining to the formula should be expressed in a numerical format; otherwise, the output shall display an error.
2. It would evaluate only the numerical values coming from arrays; else would be ignored when using this formula.
3. The order of the inputs is considered when using the formula.
4. The net present value formula assumes that all the cash flows are spaced out evenly between each other.

The XNPV function is much more accurate than the Net Present Value formula since it considers the dates for each cash flow. Therefore, the analyst must input a discount rate, a series of cash flows, and a succession of the respective dates for each cash flow.

The formula would be:

XNPV (Rate, Cash Flows, Cash Flow Dates)

The XNPV function is much more precise than the NPV counterpart. Nevertheless, XNPV is recommended since it provides more accurate estimations of its calculations.

XNPV is commonly used in financial modeling, especially in investment banking, such as Leveraged Buyouts or acquisitions. 

Types of Financial Ratios

Financial ratios serve us to assess the performance of a company, usually following its industry.

These ratios are taken from the company's financial statements, such as the income statement, balance sheet, and cash flow statement.

By glancing at a company's financial ratios, we can evaluate its liquidity, margins, growth, profitability, leverage, rate of return, and much more information.

We can group financial ratios into the following categories:

• Leverage Ratios
• Efficiency Ratios
• Profitability Ratios
• Market Value Ratios
• Liquidity Ratios

Liquidity ratios measure how efficiently the company can pay off short-term liabilities with its current assets.

Certain ratios that can evaluate the liquidity of a company are:

• Current Ratio
• Acid-test Ratio
• Cash Ratio
• Operating Cash Flow Ratio

Leverage ratios assess the amount of capital that is brought by debt.

Certain ratios that can evaluate a company’s leverage are:

• Debt Ratio
• Debt to Equity Ratio
• Interest Coverage Ratio
• Debt Service Coverage Ratio

Efficiency ratios evaluate how efficient a company is regarding the usage of its assets.

Certain ratios that measure the efficient usage of a company’s assets are:

• Asset Turnover Ratio
• Inventory Turnover Ratio
• Receivables Turnover Ratio
• Day Sales in Inventory Ratio

Profitability ratios assess how much of the revenue generated by the business turns into profit.

Certain ratios that evaluate the level of profitability of a company are:

• Gross Margin Ratio
• Operating Margin Ratio
• Return on Assets Ratio
• Return on Equity Ratio

Market value ratios help us analyze the company’s share price.

Certain ratios that evaluate the market value of a company are:

• Book Value per Share Ratio
• Dividend Yield Ratio
• Earnings per Share Ratio
• Price-Earnings Ratio

Weighted Average Cost of Capital and Calculations

Investors and analysts use the Weighted Average Cost of Capital to measure the net profitability of an investment decision by looking into the company’s debt and equity in its calculation.

The formula for WACC includes the Weighted Average Cost of Equity and the Weighted Average Cost of Debt which would be the following:

 WACC = (E/V x Re) + (D/V x Rd x (1 – Tc))

Where:

• E = Equity
• D = Debt
• V = Entity’s Value (Sum of equity and debt)
• Re = Return on Equity
• Rd = Return on Debt
• Tc = Total Cost

The cost of equity can be found using the Capital Asset Pricing Model, which is equal to the rate of return to volatility. 

The formula for CAPM is:

Re = Rf + B x (Rm - Rf)

Where:

• Rf = Risk-Free Rate
• B = Beta
• Rm = Return of the Market

The cost of equity can be defined as an implied cost, which is the return required by the stockholders for investing in a particular share. 

Beta estimates the stock’s volatility compared to the overall market risk.

WACC helps decide whether to invest in a project or company. A company’s optimal WACC should be the lowest since it would provide the lowest cost of capital maximizing profit.

Depreciation 

Depreciation serves management to track the value of a tangible asset through its useful life. Unlike amortization, which tracks the value of an intangible asset through its useful life.

Depreciation should be accounted as an expense that would appear on the income statement and credited to the balance sheet as accumulated depreciation. 

Businesses do this mostly for cash and accounting purposes since depreciation doesn’t represent a real expense; this decreases the total amount of capital owed on taxes.

There are several ways to spread the total life of the asset with depreciation, such as:

1. Straight-Line

This method of depreciation is one of the most popular and basic ones. But, as the name suggests, it spreads equally throughout the asset's useful life.
Straight-Line Depreciation Formula:

SL = (CA – SV)/ UL

Where:

• SL = Straight Line Method
• CA = Purchase Value 
• SV = Salvage Value
• UL = Useful Life
  Based on this, we would consider the amount at which the asset would be purchased and subtract its salvage value divided by its useful life to calculate the depreciation expense.

2. Declining Balance

The declining balance method is an accelerated depreciation where the asset depreciates more in the initial years of its useful life.

DB = (NBV – SV) x (1/UL) x DR

Where:

• DB = Declining Balance
• NBV = Net Book Value
• SV = Salvage Value
• UL = Useful Life
• DR = Depreciation Rate

To calculate the asset's depreciation based on this method, we would subtract the net book value from its salvage value and then multiply it by 1 divided by its useful life multiplied by the depreciation rate.  

3. Double-Declining Balance

Like the declining balance depreciation method, the double-declining balance depreciates the asset at twice the rate and permits the company to defer income taxes to later years.

DDB = (NBV – SV) x (2/UL) x DR

Where:

• DDB = Double declining balance
• NBV = Net Book Value
• SV = Salvage Value 
• UL = Useful Life
• DR = Depreciation Rate
  The only difference from the declining balance formula is that we take 2 divided by the useful life of the asset to depreciate at twice the rate.
  The formula would translate to net book value minus salvage value multiplied by 2 divided by useful life multiplied by its depreciation rate.

4. Sum-of-the-Years’ Digits

This depreciation method requires adding all the digits of the asset’s expected life. Expensing the asset's life in this manner permits one to gain a greater tax shield in the initial process of depreciating the asset.

SYD = n/ Σn x DA

Where:

• SYD = Sum of the Years Digits
• n = useful life of the asset
• Σn = sum of the years 
• DA = Depreciable amount 
  The following formula would translate to the asset's useful life divided by the sum of years, which is then multiplied by the depreciable amount.

Forecasting Free Cash Flow

Free Cash Flow is the available cash flow for the business after paying for its day-to-day operations.

A firm can forecast free cash flow based on two methods: 

1. First, apply a constant growth rate to the present cash flow.
2. Forecasting some components of free cash flow.

Applying a constant growth rate requires us to use a historical growth rate depending on the company's previous performance and consider if the free cash flow has been increasing at a constant rate.

Some components we can use to forecast free cash flow include EBIT(1-Tax), fixed capital investments, working capital investments, and net non-cash changes.

We can forecast Free Cash Flow with EBIT (1- Tax Rate) minus incremental fixed capital investments and incremental working capital investments. 

The capital investments can be estimated by multiplying their past proportion by sales increment by the expected increment in sales.

The formula for fixed capital investments as a proportion of an increment of sales can be computed as follows:

(CAPEX – Depreciation Expense)/ Increase in Sales

Incremental fixed working capital investment as a proportion of an increment of sales can be formulated as follows:

(Increment in Working Capital Investments)/ Increment in Sales

Acknowledging this, the Forecasted Free Cash Flow formula would come out to be:

FCFE = Net Income – (Fixed Capital Investments – Depreciation) – Working Capital Investments + Net Borrowing

Linking the Three Financial Statements

Analysts should know that a company's financial statements are linked to each other to understand how they are connected.

Linking all three financial statements is possible by following the accrual accounting method. However, following a cash basis would mean the analyst to consider every single transaction, which is unfeasible for medium or large corporations.

To understand how the financial statements are linked, we should emphasize that:

1. The net income from the income statement would go to the balance sheet and cash flow statement.
2. Since depreciation is a non-cash expense, it is added back, and the capital expenditures are deducted from the cash flow statement, determining PP&E on the balance sheet.
3. The financial activities from the cash flow statement affect the cash and balance sheet from finalizing except for interest since it is displayed on the income statement.
4. The closing cash balance from the balance sheet is the addition of the last period’s closing cash balance and the actual period cash from operations, investing, and financing.

Auditing the Financial Model in Excel

Model auditing is fundamental when ensuring that the spreadsheet contains no errors or these mistakes have been eliminated. 

The auditor has several responsibilities when model auditing, such as:

1. Facilitating the tracking of the source of inputs and ensuring the assumptions don’t change by isolating constant inputs and outputs that are formulas.
2. The analyst should ensure that all inputs are constants and all outputs are formulas. Excel’s function permits the analyst to highlight the cells with distinct content like texts, formulas, and constants.
3. Tracking precedents permit the analyst to determine the precedent of the cell he’d like to check while evaluating the dependents to which the input cell flows.

The purpose of undergoing such rigorous scrutiny is to provide more accurate outputs and assurance to the external parties that would evaluate and use the model.

The model in synthesis confirms or denies what it is expected to prove, and the margin of error is minimal and will not impact the business agenda.

The auditor can take two approaches when auditing the financial model:

1. High-level review
   This method of review provides more confidence to external parties. Apart from fixing mistakes, it can aid in spotting errors without doing an exhaustive search on the model.
2. Formal model audit
   Some external parties, such as stakeholders or investors, may require this review method since it is the most formal and comprehensive. It provides a submitted report containing a detailed list of the errors for the business to solve.

Researched and Authored by Sadel Santos | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• Informational Interview
• Investment Banking Interview Questions
• Private Equity Interview Questions and Answers
• Superday
• Venture Capital Interview Questions