Financial Modeling Interview Questions
How to Answer the Top 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:
- 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.
- 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.
- 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.
- 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.
- The most common reasons we would build a financial model would be to acquire a business, raise capital, budget and forecast a company's financial position, or evaluate a business.
- There are five steps when building a financial model: collecting and preparing historical financial information, building the financial statements, performing the valuation, and providing a sensitivity analysis while showcasing the data and stress tests.
- The four most common principles when building a financial model are that the model should be concise or simple, translatable to every other situation, accurate or logical, and easy to understand even for the general public.
- There are five types of financial models that analysts should be able to implement; the three statements, DCF, M&A, IPO, and LBO model.
- Working capital gives us a glimpse into the short-term financial ability of a company to pay off its current outstanding debt with its current assets. The formula to express this would be current assets - current liabilities.
- A company can raise capital either by equity or debt. If the firm chooses to use equity, it would have to give a percentage of its company's ownership in exchange for capital. On the other hand, with debt, they would get capital with an obligation to pay it back with interest.
- Discounted Cash Flow is a widely used valuation method that permits the company to determine the NPV based on predicting future cash flows.
- The analyst should be able to forecast revenues using four methods: straight line assuming a historical growth rate; moving average, which looks into an underlying trend; simple linear regression; and multiple linear regression.
- The analyst would be expected to use tools like NPV and XNPV in Excel, similar but not the same. For example, if he has dates available when modeling, he should always use XNPV since the results would be more precise than NPV.
- The five financial ratio groups which help us analyze factors such as liquidity, margins, growth, profitability and more are leverage, efficiency, profitability, market value, and liquidity ratios.
- WACC gives us a glimpse into the profitability of investment since we would weigh the cost of using debt and equity into our decisions.
- Depreciation informs us of the value of a tangible throughout its useful life. We can calculate it using three methods: the straight-line, declining balance, double-declining balance, and sum-of-the-years digits method.
- Free cash flow is the cash left over by paying for business operations. It can be forecasted by applying a constant growth rate to the cash flow or by forecasting some formula components.
- The last step for the analyst is to audit its financial model either by following a high-level review or a formal audit approach.
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:
- 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, which would be the following:
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.
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.
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( , , and ) with excel formulas.
Themodel 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 aand 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
- Merge of Both Companies and Pro Forma Arrangements
- Deal Accretion/ Dilution
This model mostly relies on lookingof to derive the value of another company. The analyst would consider the following when doing the comparative analysis:
- Industry Classification
- 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.
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 aare:
- Design the financial projections for the target company.
- Align the three the business's .
- Build the interest and debt schedules.
- Formulate the credit metrics to see how leveraged the transaction would be.
- for the sponsor.
- Calculate the Internal Rate of Return.
, which is obtained from . This equation would give us a glimpse into the short-term of our company.
This metric looks into the business's operating liquidity and fixed assets such as Property, Plant and Equipment, and.
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, inventory, , notes receivable, and .
Current liabilities consist of what the company owes in a year or less, including, wages payable, current portion of long-term debt, accrued tax payable, dividend payable, and .
The likelihood of a company funding its business operations and growth depends on how positive its working capital is.
A company shouldto 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:
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
- 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 couldits position in the company.
For that matter, the most optimal financing method would depend on the company's profitability and how the ownerand 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 periodicalone plus the discount rate or raised to the power of the period.
DCF = CF1/(1+r)^1 + CF2/(1+r)^2 + … + CFn/(1+r)^n
- 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
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:
- 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.
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.
- The 3-month Moving Average
- 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 and obtaining the equation to forecast future revenues in Excel.
- 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 realisticby taking the projected growth rate on revenue and determining the increment in future earnings.
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
Theprovide 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 =(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:
- All the inputs pertaining to the formula should be expressed in a numerical format; otherwise, the output shall display an error.
- It would evaluate only the numerical values coming from arrays; else would be ignored when using this formula.
- The order of the inputs is considered when using the formula.
- The net present value formula assumes that all the cash flows are spaced out evenly between each other.
Theis 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, 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
- 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
Leverage ratios assess the amount of capital that is brought by debt.
Certain ratios that can evaluate a company’s leverage are:
- Debt 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:
- 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:
ratios help us analyze the company’s share price.
Certain ratios that evaluate the market value of a company are:
- Dividend Yield Ratio
- Price-Earnings Ratio
Weighted Average Cost of Capital and Calculations
Investors and analysts use the Weighted Averagethe 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 Averagewhich would be the following:
WACC = (E/V x Re) + (D/V x Rd x (1 – Tc))
- 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, which is equal to the rate of return to volatility.
The formula foris:
Re = Rf + B x (Rm - Rf)
- 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.
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 serves management to track the value of athrough 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:
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
- 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 .
2. Declining Balance
The declining balance method is anwhere the asset depreciates more in the initial years of its useful life.
= (NBV – SV) x (1/UL) x DR
- DB = Declining Balance
- NBV =
- SV = Salvage Value
- UL = Useful Life
- DR = Depreciation Rate
To calculate the asset's depreciation based on this method, we would subtract the netfrom 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
- 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.
= n/ Σn x DA
- 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:
- First, apply a constant growth rate to the present cash flow.
- 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(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:
(– 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:
= – (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. 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:
- The net income from the income statement would go to the balance sheet and cash flow statement.
- 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.
- 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.
- 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.
Theresponsibilities when model auditing, such as:
- Facilitating the tracking of the source of inputs and ensuring the assumptions don’t change by isolating constant inputs and outputs that are formulas.
- 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.
- Tracking 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:
- 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.
- 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
To continue learning and advancing your career, check out these additional helpful WSO resources: