Financial Modelling Interview Questions & Answers

Question 1. What Is Financial Modeling? Why Is It Useful? Is It Only Confined To Company’s Financial Affairs?

Answer :

• First of all, financial modeling is a quantitative analysis which is used to make a decision or a forecast about a project generally in 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 companies 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 with 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 a 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 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 company’s financial affairs. It can be used in any area of any department and even in individual cases.

Question 2. What Is Working Capital And How Do You Forecast It?

Answer :

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;
Receivables turnover = Receivables/Sales * 365
A more detailed approach ma 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.

Question 3. What Are The Design Principles Of A Good Financial Model?

Answer :

this Financial Modeling 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). 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 should not be cluttered with excessive details. While producing a financial model, the financial modeller 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 which can be easily understood by other financial modellers and non-modellers.
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.

Question 4. What Is An Array Function And How Would You Use It?

Answer :

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 Depreciation Schedule where the breakup of Assets (shown horizontally) are transposed vertically using Transpose Function with Arrays.

Question 5. What Is The Difference Between Npv And Xnpv?

Answer :

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 the 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.

Question 6. What Is The Worst Financial Forecast You Have Made In Your Life?

Answer :

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.

Question 7. How Do You Forecast Revenues?

Answer :

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:

• 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 and Average Price
• Product Availability and Pricing
• Revenue 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 base

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

Question 8. How Do You Consider Stock Options In Financial Models?

Answer :

Stock Options are used by many companies to incentivise 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 the 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.

Question 9. Which Financial Model Layout Do You Prefer?

Answer :

There are primarily two types of Financial Model layouts – Vertical and Horizontal.

1. 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.
2. Horizontal Financial model Layouts:are easier to setup 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.

Question 10. Which Ratios Do You Calculate For Financial Modeling?

Answer :

There can be many ratios that are important from 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