Financial Management: Modelling & Analysis

Post 5 in the management series. In this post I will discuss the basics of financial modelling, and share rudimentary models for Monte Carlo simulations and sensitivity analyses. Finally, I will give a brief introduction to regression analysis.

Introduction to financial modelling

The purpose of a model is to help you make business decisions. Given the range of decisions managers are called to make and the different contexts in which these decisions are made, it is impossible to produce a model that can be adapted to any situation. However, there are some principles that I believe modellers should keep in mind across industries & domains:

  1. Compare your model to the right base [more on this in a sec].
  2. Focus on the most important variables: typically, you will find that changes in one or two variables have a disproportionate effect on the model’s results. Focus on getting the assumptions for these variables right.
  3. Use common sense: many analysts get so much into the weeds of the models they are building that they forget to sense check the outputs. This is far more common than you might think — I’ve seen experienced managers take a glance at a model a more juniour analyst spent hours or days working on, only to dismiss it within seconds because it showed something like cannibalisation > 100%, market growth rates dropping from an average of 40% every year to -20% etc. Not that such results are not possible, but they are not likely; I’m the first to claim that our intuitions and expectations are very often wrong, and rigorous analysis is always welcome to challenge them, but if our intuitions are proven wrong, it’s worth checking why that was.
  4. Pay attention to design.
Figure 1: Launching a new car
  • Cluster input cells as much as possible: you don’t want to have a bunch of input cells, followed by calculations, followed by more inputs.
  • Break down the model into modules: for example, if you are evaluating the potential impact of an acquisition or restructuring, you may want to have separate tabs for the impacts on revenue, COGS, other costs etc.
  • Incorporate checks — for example, ensure that the sum of the revenue for each division equals total revenue.
  • Keep things simple: early in my career, I had a predilection for overly complex models —for example, ones that made use of VBA, or that relied on needlessly complex formulas. In retrospect, in the vast majority of cases, I could have reached the same conclusions using far simpler models (at the other extreme, one of my bosses tended to do his NPV calculations on napkins; find a good balance!).
  • Ensure the model does what it’s meant to do: another mistake I often made early in my career was building models that did not answer the right question. For example, my first role at P&G was in global profit forecasting; one of my tasks was to consolidate inputs and produce the global profit forecast for a division, and built a ‘reconciliation’ showing the changes between the latest forecast and the previous one, to be shared with my finance director. Now, as anyone who has worked in a planning role knows, forecasts are messy: an in-market sales team has a forecast that includes revenue uplift from a product launch; their finance manager thinks this is inflated, so they adjust it down. As the year progresses, the sales team realises they were overly optimistic, so they lower their forecast; their finance manager reduces their buffer, so in fact, as far as the total forecast goes, there is no change. Now, when presenting this to my finance director, I should have just shown no change; but I was eager to go into the details, so I was showing changes in the base forecast, offset by changes in the buffer. Given the number of different markets and P&L elements, there were many such offsetting moves — and my reconciliation was impossible to understand. Luckily, I was given the right feedback and coaching, and I learnt to show what is useful to my audience.

Monte Carlo Simulations

The best thing about a Monte Carlo simulation is its name: it’s exotic, it evokes James Bond and high-stakes gambling, it sounds sophisticated; mention Monte Carlo simulations in a meeting, and you’ll see people perk up and pay attention.

Monte Carlo Simulation: Monty Hall
Sensitivity Analysis — Price vs Volume
  • They avoid relying on point estimates: like many people, my career has involved many assignment changes; from one day to the next I’ve had to make decisions with no domain expertise. In such circumstances, making precise forecasts is impossible, and it is therefore much better to look at a range of possible outcomes. In one role, I had to evaluate the terms my multifunctional team was considering offering to a client; the profitability of the deal depended on all sorts of factors, and I had no idea what were the most likely values for each factor. By doing a sensitivity analysis, I showed to senior management that the deal would remain profitable in all but the most unfortunate combination of factors — which gave them confidence to approve the deal.
  • They help plan when circumstances change: in the example I just gave, the sensitivity analysis did not just serve to understand under what conditions the terms we wanted to offer our client were profitable; they also served to help the sales team with their negotiation strategy. Suppose you are a manufacturer negotiating with a retailer: you are willing to offer better margins to the retailer, but in return you expect them to make changes that will help you boost volume (e.g. they will give you more or better shelf space). As you negotiate back and forth, you can refer to a sensitivity analysis to see how much volume increase is needed to justify margin dilution; the alternative is to keep updating a model live, which is more prone to errors (and less elegant).

Regression Analysis

One of the most common questions finance managers are asked is ‘what is the impact of A on B’ — for example, questions I’ve had to grapple with include whether in-store marketing activities (such as banners and other promotional materials) increase sales, whether sales effort has a meaningful impact on client spend, and whether there exists a psychological barrier that drastically lowers consumption once a certain price level has been exceeded (for example, it might be that consumer demand is inelastic for prices between $1-$2, but if the price of a product goes above $2, demand collapses).

Regression Analysis: Scatter Plot
  • y: the independent variable — in our example, ice cream sales.
  • a: the response of the independent variable (in our example, ice cream sales) to the change in the dependent variable. For instance, if a = 2, it means that a one-degree change in temperature results in $2k change in ice cream sales (the actual scale (whether we are talking $, $k, $m etc) depends on how we set up the data; here I assume we compared $k sales vs degree temperatures).
  • b: the value of the independent variable when x is 0. For example, b = 10 means that we expect $10k in ice cream sales if the temperature is 0.
Regression: setting up the data
Regression: LINEST output
  • The value of the intercept: this shows what happens when the price is 0 — in this case, the regression suggests that if we do not charge money, we can expect people to want to receive 41 units.
  • The R-Squared (Rsq in the table): this is where a lot of people become confused. You may have heard that R-Squared (whose value ranges between 0 and 1) denotes the strength of a relationship between two variables, and so, seeing an RSQ of 0.09 may suggest to you that these two variables have a very weak link. The intuitive way to understand RSQ is that it shows the variance in the independent variable that can be explained by the dependent variable. 0.09, or 9%, means that 9% of the differences in quantities sold can be explained by price (other factors can include things like marketing activity, number of stores stocking the product, supply shortages, seasonality, etc). But this does not mean that the link between price and demand is not strong! The correct way to interpret our result is that, everything else being equal, a $1 price change results in a 0.69 change in quantity sold (in the opposite direction).
  • I set quantity sold as = randbetween(30, 50) — price * randbetween(1, 20)/20.

I work at Monzo. Ex Google, P&G