Financial Modeling Interview Questions (With Answers)

Top 20 Financial Modeling Interview Questions

If you are looking for a job that is 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 Top 20 financial modeling interview questions (with answers), which will help you make the 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 on the basis of 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 walk me through it.
• #8 – Let’s say that I have bought new equipment. How it would affect 3 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?

Recommended Courses

#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 future holds for a particular industry or for a particular project.
• In Investment Banking and Financial Research, Financial modeling means forecasting a company’s financial statements 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 on 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.
• With respect to 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 Model
• 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, financial model a sum total of 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 would 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 be able to 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 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 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 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 templates

#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 + Enter 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 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 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 will be 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 Excel

#7 – Pick a model you have built and walk 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 taken into consideration while creating that model and why.

Remember, this is one of the most important questions of all. Because your technical expertise will be judged by the model you will walk the interviewer through. And maybe 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 –

• Alibaba Financial Model
• Box IPO Financial Model
• Colgate financial model

#8 – Let’s say that I have bought new equipment. How it would affect 3 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 Investments).
• After a few years, there will be wear & tear of the PP&E, so the company needs to deduct depreciation in the income statement which will also result 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 would create an analysis in excel.

We use DATA TABLES to perform sensitivity analysis. 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 is changes in WACC and the other side is changes 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 function 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 accurately the type and amounts of revenue flows is extremely important. There are as many ways to design a revenue schedule as there are businesses.

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 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 (eg. 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, make an estimate on Average Rent per room per day on the basis of 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 really 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 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 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

• 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 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
• 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 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 Options are used by many companies to incentivize their employees. Employees get an option to buy the stock at the Strike Price.

If the 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 price to the company and get shares against each option. This results in an increase in the number of shares outstanding. This results in lower Earnings Per Share.

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 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 the use either Discounted Cash Flows or Relative Valuation for finding the target price.

DCF Valuation approach includes finding the Free Cash Flow to the Firm and thereby 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 are increasing year after year
• In order 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 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, 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?

Actually, 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 there is a circular reference in your financial model, then the excel can slow down.

Conclusion

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 wish you all the best!