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 –
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:
There are primarily two types of Financial Model layouts – Vertical and Horizontal.
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:
Revenue for Hotels should be calculated as follows –
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 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:
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:
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.