Top 9 Financial Modelling Interview Questions You Must Prepare 29.Mar.2024

If you have a laptop with you, it would be easier to show and wer 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 trposed vertically using Trpose Function with Arrays.

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

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.

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

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.

The wer 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.

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.

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 Trparent: 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.

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.