The 10 minute Financial Modelling quick reference guide

6 mins read

8 of our best financial modelling posts from 2021, presented as a short financial modeling quick reference guide. With an accompanying video playlist. Bookmark and read the posts or listen to videos on your work commute, waiting at the train station or in the checkout counter queue at the grocery store. If you like the summary, check out the detailed post, linked in the headings below.

1. Financial Modeling Lessons for startups.

Startups and founders typically operate in noisy environments where quick decision making is required. Financial models used correctly are an invaluable tool for clarity.

  1. Use financial models as thinking tools. Structure them to give you answers to specific questions, such as which investments to make, or which problems to focus on first.
  2. Asking the right questions of your models is key. Which means breaking your situation down into the correct sub-problems. An example would be, what is the probability of orders decreasing for the next quarter?
  3. Make simple models first, and add complexity with each revision so you know how to read the model. And know how to troubleshoot if it breaks.
  4. Use dynamic models. When we change inputs dynamic models update automatically. Which means that you can use dynamic models to test scenarios by changing the input. How does tripling conversion rates overnight impact the topline and bottom line?
  5. Use simulations for predicting trends. Use Monte Carlo simulators to forecast trends in the presence of multiple random variables. For example, what are the chances of our company doubling in size in the next year?

2. The Zen of building financial models

Do you identify as a passionate practitioners of building financial models. Someone who enjoys unlocking stories data is telling them. With such practitioners emerges a guidebook, a set of rules that we affectionately refer to as the Zen of building financial models. Here are 8 rules to explore:

  1. Start with simple models. Just as language is graceful and elegant in its simplicity, models are useful and informative with their simplicity. Simple models are easy to build, easy to debug, and easy to understand.
  2. Complex models are built using simpler, interconnected sub-models. For example, build a revenue forecasting model using sub-models to forecast market size, demand, and obtainable market share. Then link these three sub-models to one another to arrive at a final answer.
  3. Form follows function. Each of the sub-models in the above example can be edited or fixed with relative ease. And each sub-model is built to answer the simplest question.
  4. Calibrate with the real world. Look for benchmarks to compare with in the real world when you do get an answer from your model. The answers need to be realistic, and if they aren’t, then tweak input assumptions.
  5. Use your peers. The easiest way to get high-value opinions is to ask your fellow professionals. Make this a habit. Ask for their thoughts on your sub-models, on your assumptions and on your results.
  6. Fallacy of sunk costs. Sometimes, models need to be scrapped and restarted. And that’s okay.
  7. The goal of good financial modelling is to understand your business, its problems and its opportunities better. Not to get good at using spreadsheets.
  8. The person reading your model is as important as the model itself. Build them for a wider audience, understand how to use data correctly.

3. Common mistakes in financial modeling? Are you making them?

There are common mistakes even experienced professionals make when building models. If you’ve built balance sheets, income statements and cash flows, chances are you’ve made these mistakes before. Follow these rules to never make these conceptual mistakes again.

  1. Model cash and equity separately, and never as a balancing item.
  2. Use a three-line breakdown: Open, Change, Close.
  3. Cash balances should flow from statement of cash flows. Do not be model them as a percentage of revenues or current assets.
  4. Link your statements with each other. Balance Sheet. Income Statement. Statement of cash flows. Link cash from opening balance sheet to beginning cash in statement of cash flows. Ending cash from statement of cash flows back into the balance sheet.
  5. Reconcile the linked items using automated cross-checks.

An added mistake I see people who build models for projections is that they fail to situations where things go awry. Build models for Dr. Doom. What happens if the business breaks? when growth stagnates? if business shrinks? Build these models, and then figure out what to do if it happens.

4. Modelling Growth in a Pandemic

Business owners, founders and consultants all have to make incredibly difficult decisions regularly. A very relevant example would be forecasting how much business would stall when the pandemic was announced. This article is a case study into a real eCommerce clothing business which needed to forecast how much inventory they needed to order as the first wave of lockdowns was being initiated. Here are the key takeaways.

  1. You want data-driven projections. Not gut feeling ones. Use past order history to figure out if you will have a bad month, bad quarter, or bad year.
  2. Ask the right questions. How can past order history give us an answer? What is the best way to model this data?

Model using relative changes, instead of absolute values. Relative change is a more accurate measure of business growth as compared to absolute values. Relative changes (percentage change) is standardized, so it can be binned and tabulated for visual indicators.

5. Estimating market size. The seven deadly sins.

Startups’ product decisions depend on whether they will find enough customers to pay back investments made. Therefore, core to the financial models they use is the question of market size. Often the numbers are significantly off-kilter. Why is this so? While mentoring founders to help them arrive at plausible market sizes, I have found seven mistakes made:

  1. Using industry reports. Industry reports do not readily translate to number of sales. Hence, before using them, do your groundwork and due diligence. Understand the math behind the numbers and own them. Founder driven data from primary markets trump industry numbers for credibility.
  2. Markets and Relevance. If you are a startup choose local over global for reach and service. First, build bandwidth before expanding outwards.
  3. Who is the customer? Understand your customer profile and use it to adjust your market size estimate.
  4. Getting the math right. Use two paths to the number. First, a top-down approach, estimating the broad market estimate then drilling down to obtainable market share. Second, a bottom-up approach, defining a single unit of consumption and then aggregating it. As a result, two paths that help you validate your estimates.
  5. Big markets? Should you go for them? Big markets are not always better. Many face big problems – complex operations, customer dissatisfaction and lack of credit worthiness, logistic bottlenecks – that require large commitments and deep pockets to resolve.
  6. Growth and other complications. Factor in growth, don’t use static estimates of size. However, beware of assuming that astronomical growth rates will continue forever. Instead, consider market dynamics and alternatives that could dampen growth.
  7. Competition. Your market size is contingent on existing players in the field. Who are the big players? What proportion of market share do they hold? How many are fighting over the remaining market share?

6. Blue Ex: A Third-Party Logistics Valuation Case study

We present a valuation framework and related challenges for Pakistan first logistic IPO, Blue Ex in this post. The narrative behind the approach is

  1. A bet on a growing e-commerce market
  2. High correlations with major ecommerce segments like fashion, cosmetics, personal care and consumer electronics
  3. Comparative multiples in similar markets, and
  4. Scale efficiencies linked to growth

In conclusion, we give a range of possible target prices post listing for Blue Ex.

7. Adding Monte Carlo Simulation to your financial models

Do you want to use Monte Carlo simulations for scenario analysis. Monte Carlo simulations give you a range of different outcomes. The distribution of simulated outcomes gives associated probabilities.

The simulator will iterate through the model multiple times by varying the inputs to the model each time randomly (typically as a normal distribution) and store the results. Tabulate and analyze the results of all these iterations to obtain the probabilities of different scenarios. In addition, if you are a startup, simulations are useful as historical data may be unavailable – a distribution of derived results may still be obtained by varying input parameters.

In this post, we give a step-by-step approach of implementing Monte Carlo simulations to your financial models.

8. Price to Sales & Valuation

Relative value is the most pervasive valuation method used in the world of investments. It is present in the very language we use, and the rules of thumb we use. For example, calling companies with a Price-to-earnings less than 10 as undervalued or cheap.

In this post, we specifically delve into using Price-to-sales (PS) ratios. Unlike PE ratios, we do not floor PS ratios at zero. This means that, during periods of negative earnings, PS ratios are invaluable in calculating recovery potentials and timelines. We also compare them to other metrics of relative value such as debt load, growth, profitability, and margins and finally compare PS ratios across industries such as banking and payment services.

The Zen of Financial Modeling Play list.

This year, we started posting short form video lectures on Twitter, LinkedIn and YouTube as well. Those are organized in a playlist here. They come in short 3 minute and longer 10-15 minutes versions. Pick the ones that work the best for you.