Financial Modeling Interview Questions With PDF & Tutorial

Sensitivity analysis can be done using the data table function in the Data Ribboonu003e What if Analysis u003e Data table Data table is an array function. 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 u0026 tax rate effect on profitability. Hence in the 2 x 2 Matrix interest rate and tax rate become the rows and col. 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 u0026 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.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 column 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.

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.

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.

Even Cash Flow Method: Decision making related to stock market performance or a mutual fund investment, there is a particular entry point and an exit point.
Rate = (FV/PV)^(1/n)-1 (CAGR). Excel: Just type =rate (All the expected details).
Uneven Cash flow method: In regular cases, the returns are not calculated in the same method. In such cases we are more concerned about 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 “ Internal rate of Return” u0026 “Net present value” Method.
In IRR, 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).
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.

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

Explicit Forecasted Present value
Perpetual growth
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

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

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.

Can you tell what the purpose is?
Lets say he says filtering based on the highest return performance, then
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 u0026 ROE is likely also to continue to perform better.

Revenue drivers are important. 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

In a normal layman’s 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: We should regularly audit the model by reviewing all calculations, cross-checking formulas, and using Excel’s auditing tools. This process helps you identify and correct errors before finalizing the model.
• Version Control: Maintain version control to track changes and if needed revert to previous versions. 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. Borrowers make payments on debt after they meet senior debt obligations.
• Equity: Model the equity contribution last, and reflect the residual cash flows after you have satisfied all debt obligations.. 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, estimate the cost of deferred maintenance to be addressed immediately or over first few years.
• CapEx Schedule: Create a schedule for capital expenditures, detailing timeline of work 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: Whether the Cap Ex will be financed through additional debt or paid by 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.

No. The above questions are specifically testable for freshers

Candidates under KPMG financial modeling assessment would require a testing of various technical, practical, and behavioral skills as well. Most probably you will have to create, present or modify a three-stated financial model, undertake a DCF, and structural and operational things such as handling circular references and scenario analyses etc in Excel. It is essential to know the basics core financial ratios, valuation basics, as well as how to treat difficult issues such as modeling depreciation, capex and other complex items.

In a modelling interview the type of questions can vary but majorly the focus will be on financial statement linkages, valuation basics, Ratio analysis and some business understanding.

We apply financial planning, analysis in the form of capital budgeting and forecasting decision making.