Top 20 Financial Modeling Interview Questions
If you are looking for a job related to financial modelingFinancial ModelingFinancial modeling refers to the use of excel-based models to reflect a company's projected financial performance. Such models represent the financial situation by taking into account risks and future assumptions, which are critical for making significant decisions in the future, such as raising capital or valuing a business, and interpreting their impact., you need to prepare for the interview questions. Now, every interview is different, and the scope of a job position is also different. Still, we can pinpoint Top 20 financial modeling interview questions (with answers), which will help you leap from being a potential employee to a new one.
According to a financial modeler who has been doing modeling for nearly 15 years depicts the following way of taking the interview –
- First, ask for a sample where the interviewee has done some work and
- Then, ask questions based on that.
Asking questions based on the sample may vary, but the following are the top questions the interviewer asks for hiring for the position of a financial analyst and financial modeler.
Let’s get started. Here is the list of Top 20 Financial Modeling Interview Questions –
- #1 – What is financial modeling? Why is it useful?
- #2 – How do you build a Financial Model?
- #3 – What is working capital, and how do you forecast it?
- #4 – What are the design principles of a good financial model?
- #5 – What is an array function, and how would you use it?
- #6 – What is the difference between NPV and XNPV?
- #7 – Pick a model you have built and walked me through it.
- #8 – Let’s say that I have bought new equipment. How it would affect three financial statements.
- #9 – What is Sensitivity Analysis in Financial Modeling?
- #10 – What are LOOKUP and VLOOKUP? What to use when?
- #11 – What is the worst financial forecast you have made in your life?
- #12 – How do you forecast revenues?
- #13 – How do you forecast Costs?
- #14 – Where do you pick the historical Financial Statements?
- #15 – How do you forecast Debt in your Financial Model?
- #16 – How do you consider Stock Options in Financial Models?
- #17 – Which valuation tools are used once you have prepared the Financial Model
- #18 – Which Financial Model Layout do you prefer?
- #19 – Which ratios do you calculate for Financial Modeling?
- #20 – Can you tell which excel function would slow down the recalculation process of a large financial model?
#1 – What is financial modeling? Why is it useful? Is it only confined to the company’s financial affairs?
This is the most basic and important Financial Modeling Interview Question.
- First of all, financial modeling is a quantitative analysis that is used to make a decision or a forecast about a project, generally in the asset pricing model or corporate finance. Different hypothetical variables are used in a formula to ascertain what the future holds for a particular industry or a particular project.
- In Investment Banking and Financial Research, Financial modeling means forecasting a company’s financial statementsCompany's Financial StatementsFinancial statements are written reports prepared by a company's management to present the company's financial affairs over a given period (quarter, six monthly or yearly). These statements, which include the Balance Sheet, Income Statement, Cash Flows, and Shareholders Equity Statement, must be prepared in accordance with prescribed and standardized accounting standards to ensure uniformity in reporting at all levels. like Balance Sheet, Cash Flows, and Income Statement. These forecasts are, in turn, used for company valuations and financial analysis.
- It is always good to cite an example of this. You can illustrate your point in the following manner – Let’s say there are two projects that a company is working on. The company wants to know whether it is prudent to keep working on two projects or concentrate their full effort on one project. Using financial modeling, you can use various hypothetical factors like return, risk, cash inflow, the cost of running the projects, and then come to forecasting, which may help the company to go for the most prudent choice.
- For Investment Banking, you can talk about the Financial Models that you have prepared. You may refer to examples like the Box IPO Model and Alibaba Financial ModelAlibaba Financial ModelAlibaba is the most profitable Chinese e-commerce company and its IPO is a big deal due to its size. With its huge size and network, Alibaba IPO may look at international expansion beyond China and may lead to price wars and intensive competition in the US..
- Also, note that Financial modeling is useful because it helps companies and individuals make better decisions.
- Financial modeling is not confined to only the company’s financial affairs. It can be used in any area of any department and even in individual cases.
#2 – How do you build a Financial Model?
Go through this Financial Modeling in Excel Training to build a financial model.
Financial Modeling is easy, as well as complex. If you look at the Financial Model, you will find it complex; however, the financial model has smaller and simple modules. The key here is to prepare each smaller modules and interconnect each other to prepare the final financial model.
You can see below various Financial Modeling Schedules / Modules –
Please note the following –
- The core modules are the Income Statement, Balance Sheet, and Cash Flows.
- The additional modules are the depreciation schedule, working capital schedule, intangibles schedule, shareholder’s equity schedule, other long-term items schedule, debt schedule, etc.
- The additional schedules are linked to the core statements upon their completion
Also, have a look at Types of Financial Models
#3 – What is working capital, and how do you forecast it?
This is a basic question of finance. You would answer in the following manner –
If we deduct current liabilities from current assets of the company during a period (usually a year), we will get working capital. Working capital is the difference between how much cash is tied up in inventories, accounts receivablesAccounts ReceivablesAccounts receivables refer to the amount due on the customers for the credit sales of the products or services made by the company to them. It appears as a current asset in the corporate balance sheet., etc. and how much cash needs to be paid for accounts payableAccounts PayableAccounts payable is the amount due by a business to its suppliers or vendors for the purchase of products or services. It is categorized as current liabilities on the balance sheet and must be satisfied within an accounting period. and other short-term obligations.
From the working capital, you would also understand the ratio (current ratio) between current assets and current liabilities. The current ratio will give you an idea about the liquidity of the company.
Generally, when you forecast Working Capital, you do not take Cash in “Current Assets” and any debt in the “Current Liabilities.”
Working Capital Forecast essentially involves forecasting Receivables, Inventory, and Payables.
Accounts Receivable Forecast
- Generally modeled as Days Sales Outstanding formulaDays Sales Outstanding FormulaDays sales outstanding portrays the company's efficiency to recover its credit sales bills from the debtors. The number of days debtors took to make the payment is computed by multiplying the fraction of accounts receivables to net credit sales with 365 days.;
- Receivables turnover = Receivables/Sales * 365
- A more detailed approach may include aging or receivables by business segment if the collections vary widely by segments
- Receivables = Receivables turnover days/365*Revenues
- Inventories are driven by costs (never by sales);
- Inventory turnover = Inventory/COGS * 365; For Historical
- Assume an Inventory turnover number for future years based on historical trend or management guidance and then compute the Inventory using the formula given below
- Inventory = Inventory turnover days/365*COGS; For Forecast
Accounts Payable Forecast
- Accounts Payables (Part of Working Capital Schedule):
- Payables turnover = Payables/COGS * 365; For Historical
- Assume Payables turnover days for future years based on historical trend or management guidance and then compute the Accounts Payables using the formula given below
- Accounts Payables = Payables turnover days/365*COGS; for Forecast
#4 – What are the design principles of a good financial model?
Another easy question.
Answer this Financial Modeling question using an acronym – FAST.
F stands for Flexibility: Every financial model should be flexible in its scope and adaptable in every situation (as contingency is a natural part of any business or industry). The flexibility of a financial model depends on how easy it is to modify the model whenever and wherever it would be necessary.
A stands for Appropriate: Financial models shouldn’t be cluttered with excessive details. While producing a financial model, the financial modeler always should understand what the financial model is, i.e., a good representation of reality.
S stands for Structure: The logical integrity of a financial model is of utter importance. As the author of the model may change, the structure should be rigorous, and integrity should be kept at the forefront.
T stands for Transparent: Financial models should be such and based on such formulas that can be easily understood by other financial modelers and non-modelers.
COLGATE BALANCE SHEET HISTORICAL DATA
Also, note the color standards popularly used in Financial Models –
- Blue – Use this color for any constant that is used in the model.
- Black – Use Black color for any formulas used in the Financial Model
- Green – Green color is used for any cross-references from different sheets.
Download this Financial Modeling templatesFinancial Modeling TemplatesYou can download many financial modeling templates, including the Alibaba IPO model, Box IPO model, Colgate financial model, free cash flow to firm model, sensitivity analysis model, comparable company analysis model, PE and PE band chart.
#5 – What is an array function, and how would you use it?
If you have a laptop with you, it would be easier to show and answer this Financial Modeling Interview Question. If not, then just explain how it is done.
An array formula helps you to perform multiple computations one or more sets of values.
There are three steps one should follow to compute array function in excel –
- Before entering the array formula into the cell, first, highlight the range of cells.
- Type in the array formula in the first cell.
- Press Ctrl + Shift + EnterPress Ctrl + Shift + EnterCtrl-Shift Enter In Excel is a shortcut command that facilitates implementing the array formula in the excel function to execute an intricate computation of the given data. Altogether it transforms a particular data into an array format in excel with multiple data values for this purpose. to get the results.
In the Financial model, we make use of arrays in the Depreciation Schedule, where the breakup of Assets (shown horizontally) are transposed vertically using a Transpose Function in excel with Arrays.
#6 – What is the difference between NPV and XNPV?
The answer to this Financial modeling Question will be clear cut. There is a clear difference between NPV and XNPV. Both of these compute Net Present ValueNet Present ValueNet Present Value (NPV) estimates the profitability of a project and is the difference between the present value of cash inflows and the present value of cash outflows over the project’s time period. If the difference is positive, the project is profitable; otherwise, it is not. by looking into future cash flows (positive & negative). The only difference between NPV and XNPV is –
- # NPV assumes that the cash flows come in equal time intervals.
- # XNPV assumes that the cash flows don’t come in equal time intervals.
When there are monthly or quarterly or yearly payments, one can easily use NPV, and in the case not-so-regular payments, XNPV would be suitable.
For details, have a look at Financial Functions in ExcelFinancial Functions In ExcelExcel is known for making complex formulas easy to use and apply. Most needed functions are 1.Future Value 2.FVSchedule 3.Present Value 4.Net Present Value 5.XNPV 6.PMT 7.PPMT 8.Internal Rate of Return 9.Modified Internal Rate of Return 10.XIRR 11.NPER 12.RATE 13.EFFECT 14.NOMINAL 15.SLN
#7 – Pick a model you have built and walked me through it.
If you have already built a model, this question is super easy. Just open your laptop, open the spreadsheet, and show the model you have built for any project or company. Then explain how you have built the model and which hypothetical factors you have considered while creating that model and why.
Remember, this is one of the most important questions of all. Because the model will judge your technical expertise, you will walk the interviewer through. The next questions for the rest of the interview will be based on the model you have built. So choose prudently.
You may use the following examples as well –
#8 – Let’s say that I have bought new equipment. How it would affect three financial statements.
This may seem a bit like accounting questions. But to check the finance knowledge of a modeler, the interviewer often asks this Financial Modeling question.
Here’s how you should answer it:
- In the beginning, there would be no impact on the income statement.
- In the balance sheet, cash will go down, and PP&E (Property, Plant & Equipment) would go up.
- In the cash flow statement, the purchase of PP&E would be treated as cash outflow (cash flow from InvestmentsCash Flow From InvestmentsCash flow from investing activities refer to the money acquired or spent on the purchase or disposal of the fixed assets (both tangible and intangible) for the business purpose. For instance, the purchase of land and joint venture investment is cash outflow, while equipment sale is a cash inflow.).
- After a few years, there will be wear & tear of the PP&E, so the company needs to deduct depreciation in the income statement, resulting in less net income.
- In the balance sheet, retained earningsRetained EarningsRetained Earnings are defined as the cumulative earnings earned by the company till the date after adjusting for the distribution of the dividend or the other distributions to the investors of the company. It is shown as the part of owner’s equity in the liability side of the balance sheet of the company. will get reduced.
- And in the cash flow statement, the depreciation will be added back as a non-cash expense in the “cash flow from operationsCash Flow From OperationsCash flow from Operations is the first of the three parts of the cash flow statement that shows the cash inflows and outflows from core operating business in an accounting year. Operating Activities includes cash received from Sales, cash expenses paid for direct costs as well as payment is done for funding working capital..”
#9 – What is Sensitivity Analysis in Financial Modeling?
If you have an analysis already on your laptop, show it to your interviewer to answer this question.
Sensitivity analysis is one of the analyses used in financial modeling. This analysis helps one understand how the target variable is affected by the change in the input variable. For example, if you want to see how the stock price of a company is affected by its input variables, we would take a few input variables and would create an analysis in excel.
We use DATA TABLES to perform sensitivity analysis. The most popular sensitivity analysis is done on the effect of WACC and the Company’s Growth rate on the Share Price.
As we see from above, on one side are changes in WACC, and the other side is changed in Growth Rates. In the middlebox is Share Price sensitivity to these variables.
#10 – What are LOOKUP and VLOOKUP? What to use when?
Often the interviewer wants to know whether you are proficient in using excels in financial modeling or not.
LOOKUP is a functionLOOKUP Is A FunctionThe LOOKUP excel function searches a value in a range (single row or column). It returns a corresponding match from the exact position of another range. The corresponding match is a piece of information associated with the value being searched. that allows you to consider the value entered; then, find it within a data range; once the data range is selected, then the function returns a value from the same data range without needing to scroll through.
VLOOKUP, on the other hand, is one of the sub-function of LOOKUP.
The purpose of the VLOOKUP Function is to search for a value in the leftmost column of the data range, and then it finds out a value in the same row from a column you have specified.
VLOOKUP is typically used to prepare Comparable Comps where the reference data is stored in separate sheets and are pulled together in a condensed Comparable Company Analysis table.
#11 – What is the worst financial forecast you have made in your life?
This is a very tricky question.
You need to handle it well.
Answering this question is similar to answering about your weaknesses.
So, you need to be tactful.
You should never pick one financial model and talk about it. Rather pick two models – one that you couldn’t forecast right and another where you have hit the nail. And then give a comparison between these two. And tell the interviewer why one went belly up and another has become one of your best predictions.
12. How do you forecast revenues?
For most companies, revenues are a fundamental driver of economic performance. A well designed and logical revenue model reflecting the type and amounts of revenue flows accurately is extremely important. There are as many ways to design a revenue schedule as there are businesses.
Some common types include:
- Sales Growth
- Inflationary and Volume/ Mix effects
- Unit Volume, Change in Volume, Average Price and Change in Price
- Dollar Market Size and Growth
- Unit Market Size and Growth
- Volume Capacity, Capacity Utilization rate, and Average Price
- Product Availability and Pricing
- Revenue was driven by investment in capital, marketing, or R&D
- Revenue-based on installed base (continuing sales of parts, disposables, service, and add-ons, etc.).
- Employee based
- Store, facility, or Square footage based
- Occupancy-factor based
An example you can include is that of projecting revenues of Hotels.
Revenue for Hotels should be calculated as follows –
- Get the total number of rooms each year along with forecasts.
- Hotel Industry tracks occupancy ratesOccupancy RatesThe occupancy rate is defined as the ratio of rented units to the total count of available units in a building, tower, housing unit, state, or city. It is one of the critical and important concepts for those investors who are fairly interested in dealing with real estate transactions. (e.g., 80%, etc.). This means that 80% of the rooms are occupied, others are vacant and don’t result in revenues. Make an estimate of the occupancy rate for this hotel.
- Also, estimate Average Rent per room per day based on historicals.
- Total Revenues = Total Number of Rooms x Occupancy Rates x Average Rent per room Per day x 365
13. How do you forecast Costs?
You can forecast Costs and other expensesExpensesOther expenses comprise all the non-operating costs incurred for the supporting business operations. Such payments like rent, insurance and taxes have no direct connection with the mainstream business activities. as follows –
- Percentage of Revenues: Simple but offers no insight into any leverage (economy of scale or fixed cost burden.
- Costs other than depreciation as a percent of revenues and depreciation from a separate schedule: This approach is really the minimum acceptable in most cases, and permits only partial analysis of operating leverageOperating LeverageOperating Leverage is an accounting metric that helps the analyst in analyzing how a company’s operations are related to the company’s revenues. The ratio gives details about how much of a revenue increase will the company have with a specific percentage of sales increase – which puts the predictability of sales into the forefront..
- Variable costs based on revenue or volume, fixed costs based on historical trends, and depreciation from a separate schedule: This approach is the minimum necessary for sensitivity analysis of profitability based on multiple revenue scenarios
In the above snapshot, we have used a simple cost as a percentage of Costs or percentage of Sales assumption.
14. Where do you pick the historical Financial Statements?
A best practice is to pick the financial statements from the Annual Reports or the SEC Filings directly. This may involve copying and pasting the data from the annual report to the excel sheet.
Many feel that this task is for losers; however, my take is that this is the most important task in creating the financial model. Once you start populating the data, you will realize the subtle changes in the financial statements that the company may have done. Additionally, you will get a good understanding of the kind of items included in the financial statements.
Many would argue that Bloomberg and other databases will provide an error-free financial statement. I respect these databases; however, I face one problem while using these databases. These databases use a very standardized way to report financial statements. With this, they may include/exclude key items from one line item to another and thereby creating confusion. With this, you may miss out on important details.
My golden rule – Use the SEC filingsSEC FilingsSEC filings are formal documents submitted to the Securities and Exchange Commission in the United States that contain financial information about the company as well as any other relevant information about recent or upcoming activities. and nothing else for Financial Statements.
source: Colgate SEC Filings
#15 – How do you forecast Debt in your Financial Model?
This is an advanced Question. Usually modeled as part of a debt schedule
- The key feature of the debt schedule is to use the Revolver facility and how it works so that the minimum cash balance is maintained and ensures that the Cash account does not become negative in case the operating cash flow is negative (Companies in the investment phase who need a lot of debt in initial years of operation – Telecom cos for example)
- The overall range of Debt to Equity RatioDebt To Equity RatioThe debt to equity ratio is a representation of the company's capital structure that determines the proportion of external liabilities to the shareholders' equity. It helps the investors determine the organization's leverage position and risk level. should be maintained if there is any guidance by the management
- Debt balance can also be assumed to be constant unless there is a need to increase the debt
- Notes to the accounts would give repayment terms and conditions which need to be accounted for while building the debt schedule
- For some industries, like Airlines, Retail, etc., Operating LeasesOperating LeasesAn operating lease is a type of lease that allows one party (the lessee), to use an asset held by another party (the lessor) in exchange for rental payments that are less than the asset's economic rights for a particular period and without transferring any ownership rights at the end of the lease term. might have to capitalize and converted to debt. However, this is a complex topic and beyond the scope of discussion at this point
#16 – How do you consider Stock Options in Financial Models?
This is another example of the Advanced Financial Modeling Interview question.
Stock OptionsStock OptionsStock options are derivative instruments that give the holder the right to buy or sell any stock at a predetermined price regardless of the prevailing market prices. It typically consists of four components: the strike price, the expiry date, the lot size, and the share premium. are used by many companies to incentivize their employees. Employees get an option to buy the stock at the Strike Price.
If the market priceMarket PriceMarket price refers to the current price prevailing in the market at which goods, services, or assets are purchased or sold. The price point at which the supply of a commodity matches its demand in the market becomes its market price. is greater than the stock price, then the employee can exercise its options and profit from it.
When the employees exercise their options, they pay the strike priceStrike PriceExercise price or strike price refers to the price at which the underlying stock is purchased or sold by the persons trading in the options of calls & puts available in the derivative trading. Thus, the exercise price is a term used in the derivative market. to the company and get shares against each option. This increases the number of shares outstandingNumber Of Shares OutstandingOutstanding shares are the stocks available with the company's shareholders at a given point of time after excluding the shares that the entity had repurchased. It is shown as a part of the owner's equity in the liability side of the company's balance sheet.. This results in lower Earnings Per ShareEarnings Per ShareEarnings Per Share (EPS) is a key financial metric that investors use to assess a company's performance and profitability before investing. It is calculated by dividing total earnings or total net income by the total number of outstanding shares. The higher the earnings per share (EPS), the more profitable the company is..
The options proceeds received by the company can be thereby used either to buy back shares or can be deployed in the projects.
Also, look at the Treasury Stock MethodTreasury Stock MethodTreasury Stock Method is an accounting approach assuming that the options & stock warrants are exercised at the beginning of the year (or date of issue, if later) & proceeds from the exercise of these options & warrants are used to repurchase shares in the market.
#17 – Which valuation tools are used once you have prepared the Financial Model
Once you have prepared the financial model, you can use the use either Discounted Cash Flows or Relative Valuation for finding the target price.
For example, presented below is the Free Cash Flow to the Firm of Alibaba. The Free Cash flow is divided into two parts – a) Historical FCFF and b) Forecast FCFF
- Historical FCFF is arrived at from the Income Statement, Balance Sheet and Cash Flows of the company from its Annual Reports
- Forecast FCFF is calculated only after forecasting the Financial Statements
- We note that Alibaba’s Free Cash Flow is increasing year after year
- To find the valuation of Alibaba, we must find the present value of all the future financial years (till perpetuity – Terminal valueTerminal ValueTerminal Value is the value of a project at a stage beyond which it's present value cannot be calculated. This value is the permanent value from there onwards. )
#18 – Which Financial Model Layout do you prefer?
This Financial Modeling Question is very easy. There are primarily two types of Financial Model layouts – Vertical and Horizontal.
- Vertical Financial Model Layouts are compact. You can easily align the columns and headings. However, they are tougher to navigate because a lot of data is contained in a single sheet.
- Horizontal Financial model Layouts are easier to set up with each module in a separate sheet. Here the readability is high as you can name the individual tabs accordingly. The only problem is that there are many numbers of sheets which you have interlink. I prefer the Horizontal Layouts as I find them easier to manage and audit.
19. Which ratios do you calculate for Financial Modeling?
There can be many ratios that are important from the Financial Modeling point of view. Some of the important ones are listed below
- Liquidity ratios like Current Ratio Current RatioThe current ratio is a liquidity ratio that measures how efficiently a company can repay it' short-term loans within a year. Current ratio = current assets/current liabilities , Quick Ratio, and Cash RatioCash RatioCash Ratio is calculated by dividing the total cash and the cash equivalents of the company by total current liabilities. It indicates how quickly a business can pay off its short term liabilities using the non-current assets.
- Return on EquityReturn On EquityReturn on Equity (ROE) represents financial performance of a company. It is calculated as the net income divided by the shareholders equity. ROE signifies the efficiency in which the company is using assets to make profit.
- Return on Assets
- Turnover Ratios like Inventory Turnover RatiosInventory Turnover RatiosInventory Turnover Ratio is a measure to determine the efficiency of a Company concerning its overall inventory management. To calculate the ratio, divide the cost of goods sold by the gross inventory. , Receivables Turnover ratio, Payables Turnover Ratio
- Margins – Gross, Operating, and Net
- Debt to Equity Ratio
Also, have a look at this Complete practical guide on Ratio AnalysisRatio AnalysisRatio analysis is the quantitative interpretation of the company's financial performance. It provides valuable information about the organization's profitability, solvency, operational efficiency and liquidity positions as represented by the financial statements.
#20 – Can you tell which excel function would slow down the recalculation process of a large financial model?
The answer to this Financial Modeling question is not one; it can be because of multiple reasons.
- Data tables usage for sensitivity analysisData Tables Usage For Sensitivity AnalysisSensitivity analysis in excel helps us study the uncertainty in the output of the model with the changes in the input variables. It primarily does stress testing of our modeled assumptions and leads to value-added insights. In the context of DCF valuation, Sensitivity Analysis in excel is especially useful in finance for modeling share price or valuation sensitivity to assumptions like growth rates or cost of capital. causes slow down.
- Array formulas (as used for Transpose and other calculations) can cause a significant slowdown.
- If there is a circular reference in excel in your financial model, then the excel can slow down.
Financial modeling interviews will not be confined to only financial modeling questions. You need to be thorough with accounts, general finance questions, excel & advance excel, general HR questions, and current affairs. The above questions will help you understand what sort of questions you can expect in interviews and how to answer them.
Prepare well, and I wish you all the best!