Financial Modeling Interview Questions With PDF & Tutorial

This question will not be a deal breaker but as a interviewer myself I would probably rate you a beginner.
Sensitivity analysis can be done using the data table function in the Data Ribboon> What if Analysis > Data table Data table is an array function and thus is hugely useful to test the model scenarios.
A Data table can be created by creating 2 x 2 Matrix of the assumptions to be tested. For example in a very basic table, we could test the interest rate & tax rate effect on profitability
Hence in the 2 x 2 Matrix interest rate and tax rate become the rows and col. So we can create various scenarios of interest and tax rate
To the side of the matrix we need to link the original profit calculation, which tells excel that this is the calculation which needs to be replicated by changing the two assumptions
Its important to note, that this won’t work unless your calculation and model is linked and no hard coding is done.

V look up or vertical look up function & Index match are both used to find the related data, basis some field data point you have.
If you check the above data set, the data firstly flows from top to down i.e vertical. Hence vlook up gets used. Second we take customer number(3604-2407), as the source of finding profit.
So vlook up function asks for three things Look up value – 3604-2407
Table array- the place where you are looking. Now this is the difference between the two functions. Table array needs to be selected to make sure the source data col, is to the left most side of the selection, followed by the numeric index position of the finding data. The third coloum is where the profit lies.

While with index and match removes this constraint and you don’t have the constraint of left most side selection. You can check out the below image

A typical question to test, whether you understand how calculations actually work on excel.
Circular reference is basically an error caused when a calculation is performed linking its self. May sound confusion but let me show you below
Its important to understand not just what is circular reference but how to correct this.
Wait wasn’t it an error? So you mean to say, we use circular calculation?
Yes in fact a lot of models would be difficult to calculate if it wasn’t for circular calculation feature of excel. Let me give you an example: Lets say you took a loan $100 with interest rate of 5% compounded annually. However you can’t pay the interest for 3 years. So what would would be the loan accrued until 3 years.

A debt schedule is a detailed plan that outlines a company’s debt obligations, including principal repayments, interest payments, and the timing of these payments. It is a critical component in financial models as it impacts the cash flow forecasts and the overall financial health of the company.

The question is asking the return calculation methods, and there are mainly two approaches
Even cash flows
Uneven cash flows
Even Cash Flow Method
When we are dealing with decision making related to stock market performance or a mutual fund investment, then there is a particular entry point and an exit point
Example
For example: You bought a stock at $10 in 2022 and sold it in lets say at $45 in three years. Then what is the return generated?
Hence there are only two points we are concerned with the entry at $10 and exit at $45 within 3 years. So in this case we can use the rate(CAGR) formula
Rate = (FV/PV)^(1/n)-1
CAGR
This can also be done in excel using the rate function: Just type =rate( put all the expected details)
Uneven Cash flow method
However in regular cases of start ups, the returns are not calculated in the same method because you really don’t buy the business you are starting and don’t really exit the business by selling ( you can but it’s too assumptions).
In such cases we are more concerned with the initial investment to start the business
The Free cashflow generated during lets say 4-5 years of the forecasting
Selling of assets and shutting down the business at the end
In these cases we use what is called “ Internal rate of Return” & “Net present value” Method.
Why do we use that?
Take an example: lets you start a business of manufacturing bricks, for that you create a factory and invest about $10,000. In the consecutive years you get $5000, $10000, $4000 as free cash flows.
How would you calculate rate of return based on the previous method discussed above?
Hence what IRR, does is we try to estimate the rate of return based on the logic that there must be some rate, which can make NPV zero( Initial Investment- Future cash flows).
However IRR has its problems, it assumes that we reinvest the surplus at IRR
It considers everything happens sequentially
You can learn how to calculate this with a model here: Learn

This question will prove whether you understand time value of money in its intended way or not.
If you are answer is “Yes”, then you are wrong.

Very much a continuation of the financial mathematics, based on the present value concepts.
We can value a company using DCF, by first forecasting the free cash flow of the company
FCFF- To find the enterprise Value
FCFE- To find the equity value
Usually we use FCFF because, it can also be used to find the acquisition value. When you acquire a company you pay the enterprise value.
We then estimate the cost of capital and cost of equity for the company based on the method chosen. If we are using FCFF, then we use WACC (Weighted average cost of capital), else we use Cost of equity ( Derived from CAPM)
                      We discount the cashflow back to zero based on the discounting rate( either WACC or Ke        . Also since we are discounting only the forecasted period, but the business is a going concern hence we use the concept of Gordon growth model (Very similar to the present value of $100 example taken above).
The present value thus, has two parts
Explicit Forecasted Present value
Perpetual growth
Lets take an example and it will be more clear:
Lets say we are hypothetically valuing Infosys. Its FCFE for 3 years( Forecasted period) is as follows
$5000
$6500
$10000
Ke: 12%
Perpetual Growth rate: 5%
No of shares= 1000
CMP:$10
Check out the calculation below.

Quite a practical question. The interviewer is trying to test, if you have spend enough time spending on practical cases.
You can actually value a company with even negative cashflows but not really by DCF. You can do that by using relative valuation. Some companies might be negative because of their growth phase, hence doesn’t really mean that the company has no value.
There are multiple approaches available:
Do valuation based on P/E, EV/EBITDA etc on profitable companies and then try to adjust it to estimate the value of the negative cash flow company
You could also do the same with DCF, and try to predict the profitable phase.
You can do asset based valuation, so no cash flow needed.

This should be straightforward but be careful not just give a lazy answer. You can follow up you answer with a question
Can you tell what the purpose is?
Lets say he says filtering based on the highest return performance. Then you can punch your way forward by mentioning
Return on Equity
Return on capital
ROE and ROC are very important ratios for filtration, because any company consistently performing over and above 15% ROC & ROE is likely also to continue to perform better.

This is the trick to test your logical bent.
Revenue drivers are important, very important in equity research. You can’t really understand a business, if you don’t have the ability to understand the two most important factors behind the revenue
Price X Quantity
& No! The grocery store revenue driver is not Price of inventory X Quantity of inventory.
How can you really predict or forecast the inventory. Also how can you ever judge whether the goods are actually getting sold or not.  The simplest way to think about revenue drivers is to think, this way.
What is the similarity between a large super market and small grocery store?
Space!
Larger space, larger revenue. Hence area matters in a super market business. So how about we design the revenue driver as:
Revenue Per sq feet( That’s price) x Space
Now you can forecast the revenue of the grocery store by forecasting space or the revenue per sq feet( premium products staking will leading higher revenue per sq feet)

Project finance is usually funded through debt and the banks want to know whether your business has the capability to repay. Hence practically they want to understand if your cashflows are going to be enough to cover the interest and principal payments
So the ratios we can use is
Debt Service coverage ratio
Interest Coverage Ratio
Pay back period
Discounted pay pack period
Profitability Index

Tricky question! In a normal laymans talk , this is impossible but if you think about the possibilities then it is.
Larger number of employees doesn’t mean good.  Higher number of employees can also mean, more costs of maintaining an employee.
It might be possible to increase revenue by incentivising a smaller number of employees and removing inefficient employees.
Why for example: Lets you had 10 employees and you were paying them $1000 in total. Out of which 5 employees are under performing on a major scale. The rest 5 star performers though feel under paid.
Hence we could pay each one of the 10% higher and saving $500. So you not only saved $500 but also may be increase the motivation of the star performers to perform at 100% capacity

Break even sales is the minimum sales you would have to do, in order to cover your fixed costs. Its very important for a business to constantly know, whats the kind of minimum sales required to not shut down.
Hence here you need to understand what is variable cost and what is fixed costs. Variable costs include- salaries, raw materials etc. Fixed costs include rent, plant and machinery maintenance.
So divide the fixed costs by average revenue to find the break even sales

Net operating income is EBIT in normal sense that’s all. The only difference in real estate is that we generally consider real estate maintenance expenses, tax etc

n a very common sense level, this is similar to dividend yield. In dividend yield you were trying to find the income to price. Similary in real estate you change that to NOI/ Price.
Hence real estate companies talk in terms of 5%, 10%, 6% exit cap rate. There is no DCF that takes place in case of real estate.
At the start of the acquisition of the real estate property we are interested in at what exit cap rate can we exit this property later on.

Explanation: The Perpetual Growth Model, also known as the Gordon Growth Model, is used in the terminal value calculation of a Discounted Cash Flow (DCF) analysis. It assumes that free cash flows will grow at a constant rate indefinitely. The formula is Terminal Value = (Final Year Free Cash Flow * (1 + g)) / (r – g), where “g” is the perpetual growth rate and “r” is the discount rate.

Financial forecasting involves predicting future financial performance based on historical data, current trends, and assumptions about future conditions. It is crucial for budgeting, planning, and decision-making, as it helps companies anticipate revenues, expenses, and capital needs.

The Discounted Payback Period is the time it takes for an investment to break even in terms of net present value (NPV). Unlike the regular payback period, it accounts for the time value of money by discounting the cash flows. It is calculated by summing the discounted cash flows until they equal the initial investment.

Financial modeling best practices are essential for creating accurate and reliable models. The top best practices include:
• Simplicity: Keep the model as simple as possible while still capturing the necessary details. Avoid overcomplicating with unnecessary calculations.
• Transparency: Make sure all assumptions, inputs, and calculations are clear and well-documented. Use comments or separate sheets for assumptions and sources.
• Consistency: Use consistent formatting, naming conventions, and color-coding throughout the model. This makes it easier to follow and reduces the risk of errors.
• Modularity: Build the model in distinct, easily understandable sections, such as separate tabs for income statements, balance sheets, and cash flow statements.
• Error Checking: Regularly check for errors by auditing formulas, using Excel’s error-checking tools, and conducting scenario analysis.

Ensuring the robustness of a financial model involves several key steps:
• Stress Testing: Stress testing involves pushing the model’s inputs to their extremes to see how the outputs behave. This helps identify vulnerabilities or areas where the model may fail under different scenarios.
• Scenario Analysis: Conducting scenario analysis allows you to test the model under various conditions, such as best-case, worst-case, and base-case scenarios. This provides insights into how changes in key variables affect the overall outcomes.
• Model Auditing: Regularly audit the model by reviewing all calculations, cross-checking formulas, and using Excel’s auditing tools. This helps to identify and correct errors before the model is finalized.
• Version Control: Maintain version control to track changes and revert to previous versions if needed. This helps in keeping the model’s integrity intact, especially when multiple users are involved.

n a multi-tenant property, rent escalations and lease renewals are crucial factors that affect cash flow projections. To model this, you need to set up schedules for each tenant that account for:
• Base rent increases: These are often tied to inflation (CPI adjustments) or fixed percentage increases.
• Lease renewal options: Include assumptions on renewal probability, potential rent increases, and vacancy periods between leases.
• Step rents: Model the effect of staggered rent increases over the lease term. The overall impact on NOI should reflect these variables over the investment horizon.

 When modeling a JV partnership, it’s essential to reflect the different equity contributions, preferred returns, and profit-sharing arrangements:
• Equity Contribution: Model the capital contributions from each partner according to the agreed structure (e.g., 70/30, 60/40).
• Preferred Return (Hurdle Rate): Calculate the preferred return on equity before profits are distributed.
• Profit Sharing (Waterfall Distribution): Implement the waterfall structure, where profits are distributed according to pre-agreed tiers, such as IRR hurdles or cash-on-cash returns.
• Promote Structure: Include any promote structures where the developer or a specific partner receives a higher share of profits after certain financial milestones are achieved.

Property taxes can change significantly after a reassessment, which usually occurs after a property sale or significant improvement:
• Current Property Tax: Start with the current tax rate applied to the assessed value.
• Reassessment: Model the impact of a new assessment value post-acquisition or after significant capital improvements. This may involve estimating a new tax rate based on comparable property sales or improvements.
• Tax Abatements: Consider any tax abatement programs that might delay or reduce the tax burden for a specified period.
• Future Tax Growth: Incorporate assumptions on how property taxes will escalate annually based on local tax laws and projected appreciation in property value.

Debt structuring with multiple layers of financing involves careful modeling of cash flows and repayments:
• Senior Debt: Model the senior debt first, including interest payments, amortization schedules, and covenants. Senior debt usually has the lowest interest rate but the highest claim on cash flows.
• Mezzanine Financing: Include mezzanine financing, which typically comes with a higher interest rate and can include equity participation or warrants. Payments on mezzanine debt occur after senior debt obligations are met.
• Equity: Model the equity contribution last, reflecting the residual cash flows after all debt obligations have been satisfied. The equity holders’ return is based on cash flows remaining after servicing both senior and mezzanine debt.
• Debt Coverage Ratios: Calculate key metrics like Debt Service Coverage Ratio (DSCR) to ensure the project can service its debt at all levels.

Modeling a property with deferred maintenance requires detailed capital expenditure planning and its impact on future cash flows:
• Initial Assessment: Identify and estimate the cost of deferred maintenance that needs to be addressed immediately or over the first few years.
• CapEx Schedule: Create a schedule for capital expenditures, detailing when the work will be done and how much it will cost. Spread these costs over the project timeline.
• Impact on Cash Flow: Reflect the impact of these expenditures on cash flow, both in terms of the outlay for maintenance and potential increases in rental income or property value due to improvements.
• Financing: Consider whether the CapEx will be financed through additional debt or paid out of operating cash flow.
• Depreciation and Tax Benefits: Include the effects of depreciation on the capital improvements and any tax benefits that may arise from these expenditures.