Modelling revenue - conceptual model & assignment

Note: This lesson begins from the completed file from the previous lesson:

To obtain the worked example file to accompany this chapter buy the financial modelling handbook.

Modelling objectives:

  • Model revenue per the specifications of the case;
  • Flow revenue into our income statement;
  • Decide what to do about invoice payment delay - deal with it now or come back later

Learning objectives:

  • Understand that we always model revenue based on price times volume;
  • Understand the importance of asking "how much" and "when" about every line item in the model
  • Remember that we answer when questions using flags;
  • Question how the payment lag on invoices will impact our financial statements.

Revenue, ultimately, always comes down to Price x Volume.

Different models have different amounts of complexity in calculating price and volume. Depending on the complexity involved, it can be helpful to sketch out a conceptual model of how we are going to calculate revenue before starting on the Excel modelling.

The process of sketching a conceptual model before doing any formula writing can help us to:

  • Determine whether there are any holes in our understanding of the revenue drivers for the business.
  • Think through what ingredients we are going to need
  • Think through what we need to calculate each of the components of the revenue calculation - i.e. a conceptual model one layer down in the calculation for each ingredient
  • How the different ingredients are going to relate to each other

A conceptual model of revenue for this business might look like this:

Always ask how much AND when.

There are two critical questions to keep in mind when modelling any line item: "how much" and "when".

The conceptual model above deals with all the stuff related to "how much". It doesn't deal with when. Specifically:

  • When does revenue begin?
  • When does revenue end?
  • How does the length of the model period impact the revenue?

When answering "when" questions, we always turn to our flags and to our timesheet. In this case, for revenue, we'd want to use an Operating Period flag. We'd also need to consider the quarterly timeline of the model when calculating the revenue per period.

We have updated our conceptual model to take account of time.

If the revenue calculation is straightforward, as it is here, or related to an area where we have a lot of experience and therefore intuitive understanding, we often would not need to sketch out a conceptual model like this one. Where it is more operationally complex or where we don't have a solid intuitive understanding, conceptual models like this can be helpful to check our understanding before we start modelling.

We have seen the revenue sections of this model already in our earlier skills development section. It's worth building it yourself from scratch and then comparing with the solution.

Your tasks are:

  • Build out the revenue calculations according to the case study.
  • Flow revenue into the financial statements.
  • Go through the section completion checklist.
  • Compare with the solution in the next chapter.


Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.

Subscribe to Financial Modelling Handbook

Don’t miss out on the latest financial modelling guides. Sign up now to get access to the library of members-only guides.