Financial Modeling Interview Questions (With Answers)

If you are looking for a job related to financial modeling, 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 the Top 20 financial modeling interview questions (with answers), which will help you leap from being a potential employee to a new one.

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 are the list of Top 20 Financial Modeling Interview Questions –

#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, financial modeling is a quantitative analysis 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 statements like Balance Sheets, Cash Flows, and Income. 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 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, and 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 discuss the financial models you have prepared. You may refer to examples like the Box IPO Model and Alibaba Financial Mode.
• 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 department area 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 module 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 different schedules are linked to the core statements upon completion

Also, 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 the company's current assets 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 receivables, etc., and how much cash needs to be paid for accounts payable 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 of the company's liquidity.

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 formula;
• 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 Forecast

• 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 trends 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 trends or management guidance and then compute the Accounts Payables using the formula 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 is necessary.

A stands for Appropriate: Financial models shouldn't be cluttered with excessive details. While producing a financial model, the financial modeler should always 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 based on formulas that other financial modelers and non-modelers can easily understand.

COLGATE BALANCE SHEET HISTORICAL DATA

Also, note the color standards popularly used in Financial Models –

• Blue - Use this color for any constant used in the model.
• Black - Use Black for any formulas used in the Financial Model
• Green - Green color is used for cross-references from different sheets.

Download this Financial Modeling templates

#5 - What is an array function, and how would you use it?

If you have a laptop, it will 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 on 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, highlight the range of cells first.
• Type in the array formula in the first cell.
• Press Ctrl + Shift + Enter to get the results.

In the Financial model, we use 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 compute Net Present Value 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, quarterly, or yearly payments, one can easily use NPV, and in the case of not-so-regular payments, XNPV would be suitable.

For details, look at Financial Functions in Excel

#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 and the spreadsheet, and show the model you have built for any project or company. Then explain how you have built the model, 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 it. The next questions for the rest of the interview will be based on the model you have built.  So choose prudently.

You may also use the following examples:

• Alibaba Financial Model
• Box IPO Financial Model

#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) will go up.
• In the cash flow statement, the purchase of PP&E would be treated as cash outflow (cash flow from Investments).
• After a few years, there will be wear & tear on the PP&E, so the company needs to deduct depreciation in the income statement, resulting in less net income.
• In the balance sheet, retained earnings 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 operations.”

#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 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 on the other change 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 Excel in financial modeling or not.

LOOKUP is a function that allows you to consider the value entered; then find it within a data range; once the data range is selected, 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 find a value in the same row from a specified column.

VLOOKUP is typically used to prepare Comparable Comps the reference data is stored in separate sheets and 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. There are as many ways to design a revenue schedule as there are businesses. A well-designed and logical revenue model reflecting the type and amounts of revenue flows accurately is extremely important.

Some common types include:

1. Sales Growth
2. Inflationary and Volume/ Mix effects
3. Unit Volume, Change in Volume, Average Price and Change in Price
4. Dollar Market Size and Growth
5. Unit Market Size and Growth
6. Volume Capacity, Capacity Utilization rate, and Average Price
7. Product Availability and Pricing
8. Revenue was driven by investment in capital, marketing, or R&D
9. Revenue-based on installed base (continuing sales of parts, disposables, service, and add-ons, etc.).
10. Employee based
11. Store, facility, or Square footage based
12. 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 rates (e.g., 80%, etc.). This means that 80% of the rooms are occupied, and 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 expenses as follows -

1. Percentage of Revenues: Simple but offers no insight into any leverage (economy of scale or fixed cost burden.
2. Costs other than depreciation as a percent of revenues and depreciation from a separate schedule: This approach is the minimum acceptable in most cases and permits only partial analysis of operating leverage.
3. 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 made. Additionally, you will get a good understanding of the kind of items included in the financial statements.

Many 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, thereby creating confusion. With this, you may miss out on important details.

My golden rule - Use the SEC filings 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 Ratio should be maintained if there is any guidance by the management
• The 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 Leases might have to capitalize and convert 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 Options to incentivize their employees. Employees get an option to buy the stock at the Strike Price.

If the market price exceeds the stock price, the employee can exercise options and profit from it.

When the employees exercise their options, they pay the strike price to the company and get shares against each option. This increases the number of shares outstanding[. This results in lower Earnings Per Share.

The options proceeds received by the company can be used either to buy back shares or deployed in the projects.

Also, look at the Treasury Stock Method

#17 - Which valuation tools are used once you have prepared the Financial Model

Once you have prepared the financial model, you can use Discounted Cash Flows or Relative Valuation for finding the target price.

DCF Valuation approach includes finding the Free Cash Flow to the Firm and finding the present value of FCFF until perpetuity.

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 value)

#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 you have many numbers of sheets interlinked. 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, Quick Ratio, and Cash Ratio
• Return on Equity
• Return on Assets
• Turnover Ratios like Inventory Turnover Ratios, 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 Analysis

#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 analysis[ causes slow down.
• Array formulas (as used for Transpose and other calculations) can cause a significant slowdown.
• If your financial model has a circular reference in excel, then excel can slow down.

Conclusion

Financial modeling interviews will not be confined to only financial modeling questions. You must be thorough with accounts, general finance questions, excel & advance excel, general HR questions, and current affairs. The above questions will help you understand what questions you can expect in interviews and how to answer them.

Prepare well, and I wish you all the best!

• Investment Banking Interview Questions (with Answers)
• Top 20 Equity Research Interview Questions
• Financial Planning and Analysis Interview Questions
• Coursera Financial Model