The 5 Financial Models every FP&A Analyst needs to know

modeling
Written By Christian Wattig

As FP&A Analysts, we are routinely asked to determine if an investment is likely to be worth it and how risky it may be. That’s when we need to know which type of Financial Model we should use in which situation.

For example, the executive team may be considering entering a new market, however, that would require building a new factory. The Operations manager reaches out to FP&A in this case, asking if they can build a model that compares the investments to the financial benefits and suggests under which circumstances the team should proceed.

Which model should the Analyst choose to achieve that goal? Is a simple ROI model sufficient or do we need something more robust like an NPV or even an LTV model? Let’s take a closer look at the 5 types of Financial Models every FP&A Analyst needs to know and what they are used for.

ROI & Payback Analysis

ROI stands for return on investment and is typically expressed as a percentage. In its simplest form, it’s calculated by dividing revenues from an investment by the cost of the project. The ratio indicates that the project is worth pursuing if it exceeds 1, i.e. revenues are higher than costs and the project is profitable.

This metric is popular because it’s easy to understand and communicate. Its meaning is immediately clear to stakeholders who don’t have a background in Finance. In practice, the difficulty lies in accurately allocating benefits to a discrete investment. For example, Return on Marketing Investment can be difficult to assess if it is unclear how much revenue a discreet marketing activity such as a TV ad contributed when the company also ran an online and print campaign at the same time. 

Payback analysis is closely related to ROI. It expresses how many months it takes until an investment is paid back in full. It uses the same inputs as ROI and can easily be derived by dividing 12 by the ROI. Companies use Payback models to set targets on acceptable investment levels in relation to revenues.

They also help to ensure that the company does not underinvest. For example, the target for a Marketing team may be a payback period of around 9 months. This enables them to spend more when the investment has a higher ROI and to reduce investments if revenues do not keep up with cost increases.

A disadvantage of ROI Analysis is that it does not take into account the time horizon. In other words, it doesn’t differentiate between projects that pay back shortly after the initial investment and projects that have revenues only after a significant ramp-up time. When dealing with these kinds of projects, we have to run an NPV analysis.

Do you like this post? Subscribe to my free weekly newsletter "FP&A Tuesday" for insights and actionable advice just like it:

NPV Analysis

NPV stands for Net Present Value. It compares the present value of cash outflows and cash inflows to determine the profitability of a project. An NPV of zero means that the present value of cash generated by the project is equal to the present value of investments.

The advantage over an ROI analysis is that NPV takes the time value of money into consideration. The concept is simple: The longer I have to wait for my investment to pay back, the higher are my opportunity costs because I cannot invest the allocated funds elsewhere.

This means that future cash flows need to be discounted by the rate of return we could reasonably expect from an alternative investment. Most companies choose to specify a fixed discount rate that must be used for all evaluations of a given category to ensure NPV is comparable between different projects. Capital budgets tend to be limited, so NPV can be used to prioritize among different investment options. 

NPV estimates what the return on the investment may be today in absolute terms. However, to assess risk in relation to returns of an investment, we need to consider both, NPV and IRR.


IRR Analysis

IRR stands for Integrated Rate of Return. It’s an output of an NPV analysis and specifies the discount rate that would result in an NPV of zero. To put it differently, NPV is the amount of money we make from an investment, while IRR tells us the equivalent rate of return of the project. 

People sometimes struggle with putting NPV results in context. They wonder what is a good NPV for the project they are leading, is it $10,000, $20,000, or maybe even $100,000? Generally speaking, the higher the risk of an investment, the higher the expected return needs to be.

The absolute value of NPV needs to be considered in relation to the amount of the investment, which makes it harder to interpret and compare to alternatives. IRR however provides a percentage that we can immediately compare to other projects. Additionally, managers can apply straightforward thresholds or rules-of-thumb, such as “a high-risk project needs an IRR of at least 25%, while a medium-risk project only requires IRR of 15%.”


Sensitivity Analysis 

Oftentimes, we are dealing with imperfect information. That means some inputs are easily available, while others have to be assumed based on previous experience - which may be inaccurate.

At times we can’t even rely on history and have to come up with an assumption with very little or no data to fall back on.

Consider the following scenario, which is a common issue FP&A teams are confronted with. You may need to estimate sales of a new product launch with associated marketing expenses. You were given a budget for expenses, but no further guidance on sales.

This is a situation where break-even sensitivity analysis is required. It allows you to calculate exactly how much of the new product we would have to sell with a given investment to “break-even”, meaning with a profit margin of zero. As a next step, we would discuss the resulting sales figures with the relevant team to determine if it’s realistic or if we may need to proceed with a lower sales goal and consequently reduced Marketing expenses.

Sensitivity analysis can also be applied along with most other models discussed here, such as NPV and LTV models.


LTV

Customer Life Time Value (abbreviated CLTV or often just LTV) estimates how much net profit we expect to receive on average from customers over their lifetime, that is over the period of time they spend money on our products or services. It’s the future value we expect to receive on a per-customer basis. The measure is typically an output of a predictive model, but can also be estimated using past periods.

Companies apply this concept with various degrees of complexity using different formulas and methods depending on what best fits their industry, business model, and data availability. That’s why I will not explain how to calculate LTV - various formulas are easily available online. 

What’s important to know is in which circumstances you should consider applying it. A company offering different products that customers purchase repeatedly, such as subscriptions or Software as a Service (SaaS) companies, needs to understand how their products compare to each other in terms of financial attractiveness.

One way of looking at it is to consider various profit margins and growth rates of revenues. While these metrics are important to understand how the business is doing today, they focus only on the current period and don’t tell us much about future potential. Product A may have a slightly better profit margin than product B and even a faster growth rate in the current period, however, if customers are more loyal to product B, meaning they are less likely to churn, that product may be more worthy of investments than product A. 

Another use case for LTV is when aiming to determine optimal investment levels. By comparing customer acquisition cost to the value of the customer as expressed by LTV, we can set minimum and maximum thresholds to optimize investment levels.

For example, consider a company that finds its customers by advertising on TV and social media. It’s important to spend enough money to keep expanding the size of the audience. At the same time, we need to make sure not to spend more than we can reasonably assume to receive from our customers in terms of revenue. Hence, the marketing department needs to keep a close eye on LTV and customer acquisition costs.

Learn how to use those 5 models and you are well on your way to mastering the Financial Modeling aspect of a typical FP&A role.

Christian Wattig
Previous

Why you need to understand P&L, Balance Sheet, and Cash Flow
Next

How to add value when your business partners know more than you