Module 13   Integrated Financial ModellingChapter 6

Revenue model

View chapters →

6.1 – Common sense approach

In the previous chapter, we built the Balance Sheet and P&L assumption. Within the P&L assumptions, we dealt with the revenue of the company as well. We did take a rather simplistic approach to estimate the revenue of the company. The approach is ok as long as you intend to build a simple financial model.

However, at times, taking efforts to build a dedicated revenue model of a company pays off.  With a dedicated revenue model, you can identify the key revenue drivers and get some granular insights into the behaviour of these revenue drivers.

In this chapter, I’d like to discuss the approach you need to take while building a company’s revenue model. As you can imagine, the revenue model sits within the integrated financial model, just like the assumption sheet.

Think of the revenue model as a sub-model within the financial model.

I’ll take the example of Bajaj auto in this chapter to explain how one can build a company’s revenue model.

A sensible way to start building a revenue model is by asking common sense questions about the company. In most cases, these questions themselves segways into a template for the revenue model. We will take the same approach to build Bajaj Auto’s revenue model.

So here are a bunch of common-sense questions, and the answers to these questions will help us build the revenue model.  By the way, the answers to all the questions are in the company’s annual report.

So let us start.

6.2 – Digging data

As a first step, I download the latest annual report (FY 2020-21) from Bajaj Auto’s website. I’d suggest you do the same. Like I mentioned, the annual report is where you will find all the information you’d need.

Usually, from my experience, as the company matures, the annual report also evolves and provides you with all the necessary information you’d need.

Anyway, let us get started with our common sense QnA. We will begin with a fundamental question.

What does Bajaj Auto do?

No brainer, we have seen Bajaj Auto’s bikes and autorickshaws flood the Indian streets. So it is evident that Bajaj manufactures and sells 2 and 3 wheelers. We will cross-check our assumption from the annual report as well.

 

From their annual report, we can see that our assumption is correct (image above if from the annual report). Bajaj does manufacture bikes and autorickshaws. The bikes are further segregated into different segments.

The image below shows the ‘sports segment’ or ’S segment’ bikes. Apart from the S segment, Bajaj has the Milage or M segment, Supersport or SS segment, Pro biking segment, and scooters.

But the point is Bajaj manufactures’ bikes’ or two-wheelers, so let us stick to that for now and ignore the segmentation of bikes.

Apart from bikes, they also manufacture autorickshaws’ Commerical Vehicles’ (CV) or the three-wheeler segment.

The CV category has different segments: passenger carrier (good old autorickshaws) and goods carrier.

Why are the segments important to a revenue model?

Well, if you know the segments within a category, you can also figure out the segment-wise revenue.

For example, the S segment is a segment within the bikes category, it will interesting to understand how much revenue they make segment-wise, and which are their popular segments, and what drives these segments.

With this information, you can build a granular revenue model. Unfortunately, the segment-wise revenue distribution is not available in the annual report. Hence we will consider revenue for the entire category as a whole, i.e. the two-wheeler (bikes) and the commercial vehicle (3 wheelers).

How much does Bajaj Auto manufacture?

I suppose this is also a straightforward question. As a financial modeller (or even an investor), you need to understand the manufacturing capacity of the company. The reason is simple.

Suppose they manufacture 100 bikes in the year, and if they are selling 60 bikes, then with this information we can interpret the following –

      • The manufacturing plant operates at 60% capacity utilization. Capacity utilization is a simple ratio of how much they sell versus how much they manufacture.
      • The company has enough manufacturing buffer to meet future demands
      • The company is unlikely to spend more money in terms of CAPEX anytime soon

Other perspectives –

      • Why is the company selling only 60?
      • How much do their competitors sell?
      • Where does the company stand in terms of competition?
      • How big is the industry? How many bikes (across all companies) are sold in a year?
      • What is the company’s market share? (company sales divided/industry sales)

These questions will help us size up the company and eventually help the investors in the valuation process.

Anyway, we will get back to the revenue model.  I found this image in their annual report interesting –

The image gives us all the information in one shot. Let me list down the information for you –

      • The company has three manufacturing units (or plants) in India, located in – Pantnagar, Waluj, and Chakan.
      • Waluj is the oldest plant (set up in 1984), while the Pantnagar plant is their newest.
      • All plants have been operational for a long time now.
      • Pantnagar plant has a production capacity to manufacture 1.8M bikes, no commercial vehicles here.
      • Waluj plant has a production capacity to manufacture 2.4M bikes and 9.3L commercial vehicles. Waluj is a super important plant for Bajaj auto since this plant has production of both categories plus this is the only plant to manufacture commercial vehicles.
      • Chakan plant has a production capacity to manufacture 1.2M units of bikes.

Since all the manufacturing facilities are old enough, assuming that the company has had a similar production capacity for the last few years is fair.

Where do they sell?

The question is to help us understand where their target market is. We have seen Bajaj vehicles across India. But do they sell in other countries apart from India?

Here is an extract from the annual report –

Without reading much into the details, we know –

      • Bajaj Auto sells within India (domestic market)
      • Bajaj Auto also sells outside India (international market)

From the extract, we can quickly note that Bajaj Auto sells around 2M vehicles in the global market.

How many units of two-wheelers and CVs does Bajaj Auto sell in India and the International market?

Now that we have established that Bajaj has a domestic and international market, it makes sense to figure out how many units of bikes and commercial vehicles are sold in India and in the International market.

From the annual report –

The highlighted data indicates the sale of domestic bikes. For example, in the year 2020, Bajaj Auto sold 3.9M bikes. The break up of 3.9M across different segments of bikes is not available (therefore no segment-wise revenue). But that’s ok for now.

Data for Domestic CV sales  –

As far as the exports are concerned, here is the snapshot –

The company has only reported domestic sales numbers across motorcycles (bikes) and CV for FY20 and FY21. We will have to dig up the older annual reports for historical numbers.

Ok, a quick recap at this point. So far we know –

    • The product the company sells
    • Places where it manufactures the products
    • The capacity of each manufacturing plant
    • The geographies in which the company sells
    • How many units the company sells across all their markets

That’s a fair bit of information. We now have to steer our way to find out details about how much money the company earns in terms of revenues.

Remember, so far, we collected information by asking ourselves a few common-sense questions. Once we collect all the necessary information, we make the revenue model on excel, step by step.

Let us continue our QnA.

How much revenue do they make?

The most crucial question perhaps 😊

Clearly, when we talk about revenue for this company, we need to figure four essential things –

      • How much revenue is from domestic bikes sales?
      • How much revenue is from domestic CV sales?
      • How much revenue is from international bikes sales?
      • How much revenue is from international CV sales?

If we can collect the above information, we are on track to build the revenue model.

But here is where the challenge occurs; the company does not easily give out this information. The information we have is –

Revenue is a consolidated number, which includes both domestic and export revenue. But thankfully, Bajaj Auto gives us the export revenue –

With both these bits of information, we have to back work the details. For example, for FY 2020,

Revenue =Rs.29,111 Cr

Export Revenue = Rs.12,216 Cr

So Domestic revenue must be –

29111 – 12216

= Rs.16,895 Crs.

Once we have the revenue split from domestic and exports, we can do few other things to set up the revenue model.

6.3 – Connecting the dots

We have now gathered all the info required to build the revenue model. We now have to plug these values into an excel sheet and give it a logical sequence. Please note that if you are doing this yourself, as a first step, you will have to get the historical data from the annual report.  In the section above, I’ve highlighted how the data is collected. Hopefully, that will help you accelerate your data collection process.

Given the data in hand, here are the steps that I’ll follow to develop the revenue model. As I have stressed earlier, the steps that I follow make sense to me; if you feel there is a better way, you should explore. Remember, there is no prescribed methods to build a model.

But I hope these steps will give you a good starting point.

Here is the overview of the steps I’ll carry out on excel –

      • Organize the capacity data
      • Gather the International sales data, i.e. the number of units of bikes and CV sold
      • Gather the India sales data, i.e. the number of units of bikes and CV sold
      • Add up the sales data to get consolidated bikes and CV sales data
      • Input the historical revenue data
      • Calculate the average cost of sale of bike and CV
      • Identify the trend in change of average cost of sale
      • Project the average price of the vehicle and reverse engineer the revenue data from the average prices.

If the steps above confuse you, then don’t worry, we will execute each of the steps, one at a time.

As a first step, we set up our excel sheet with the indexation. I’ve discussed this in the earlier chapters, so I’ll directly post the snapshot for your reference.

I guess you are reasonably familiar with the layout. Columns A and B are indexed, C expanded, panes frozen at E3. The actual financial years stated from F1 to J1, and the estimated years from K1 to O1.

I have organized the manufacturing capacity data. Note I have segregated this in terms of bikes and CV, but you can also arrange the data from the manufacturing plant perspective.

I have populated the manufacturing capacity numbers –

Note, the numbers are constant historically and for the future years as well.

Next up is the sales data. As I mentioned earlier, I’m interested in identifying the bike and CV sales in India and Internationally. Once I have the data, I’m also interested in year on year (YoY) changes in sales data.

As we saw earlier, most of the sales data is available in the annual report, except for the India sales data for bikes. But this is ok; the company gives us the total bike sales (India + International) and the total international bike sales data.

If we calculate the difference, we get the India bikes sales data. So a bit of number jugglery that you will have to do.

Next, we calculate the YoY change (in percentage) bikes and CV sales in the Indian and the International markets.

The math is simple for the YoY change –

= this year’s sales data/ previous year’s sales data – 1

= year on year change in sales, expressed in percentage.

The idea of calculating the YoY percentage change is to identify consistent trends if any. But clearly, there is no trend in the data we have.

We could have taken a rolling average of the yoy change and projected for future years if there was a trend. But now, we have to assume a flat YoY change.

I’ll project the YoY change without thinking much (to move ahead quickly), but of course, if this were a serious model (based on which you’d invest), then we would have to spend some time before we make the YoY change assumptions.

You can see the assumption I’ve made for the sales YoY change in percentage. You can also see the calculation that I’ve made to project the future year’s sale of bikes and CV. I’ve completed the math, and here is how the excel sheet looks –

Please note, I’ve summed up the bike and CV sales from both the Indian and the International markets to get the total sales. For your reference, I’ve highlighted the total sales of bikes for FY22E.

In the next step, we move our attention to the revenue data. I’ve taken the revenue data (India and International) from the annual report.

Below the revenue numbers, I’ve set up excel to calculate the average sale cost for vehicles (bikes + CV) across the Indian and the international markets. To calculate this, we need to divide the India revenue number by the India vehicles sold data.

Let me do this math for FY 17 –

Revenue from India (FY17) = Rs.14,815 Cr

Total vehicles sold in India (bikes+ CV) = 22,54,617

Average selling price of a vehicle = 14815*(10^7)/2254617

= Rs.65,709.61/-

If you wonder why I used 10^7 in the math above, then it is to get the revenue number in Crores.

Here is how it looks on Excel –

I’ve calculated the YoY change in average cost of sale as well. I hope at this stage; you can figure what to do next. If you do, then I’d be happy to know that my notes are helping you think ahead 😊

Anyway, here are the last two steps to complete the revenue model.

    • Assume a YoY change for future years, it could be a rolling average, or it could be a flat assumption
    • Project the average cost of sale in the Indian and the International market
    • Multiply the avg cost of sale and the number of vehicles sold to get the revenue in the Indian and International market
    • Sum up both to get the total revenue.

I’ve executed all the above steps in excel, and here is how it looks –

I have highlighted both the cells so that you can see the formula I’ve used.

Here are few other things that you can do with the revenue model –

    • We have the total bike and CV sales data. Compare this with the production data. Ensure the company is not selling more than what it is making. If yes, then our model may be wrong and needs some tweaking
    • If the vehicles sold are close to manufactured, the company may have to invest in a CAPEX cycle. This is valuable information from an overall financial modelling perspective
    • Calculate the capacity utilization, i.e. number of vehicles manufacture versus the number of vehicles sold.
    • Calculate the market share. You can get the industry bike/CV sales data from an industry report (guess even the annual report contains this), contrast this with what the company has sold, and get the market share number.

I guess this has turned into a lengthy chapter; I’ll stop it at this. But I hope this chapter has given you a sense of how you can develop a company’s revenue model using a common-sense approach. Always remember to start your revenue model by asking few basic questions.

The revenue model we have built here can be used for other auto manufacturing companies like Hero Motors, TVS, MRF, Maruti, Tata Motors, and even Tesla!

You can download the excel used in this chapter here.

Key takeaways from this chapter

    • The revenue model is a sub-model within your primary model
    • You can build complex revenue models by starting with simple common sense questions
    • All the data needed to make a revenue model is usually available in the annual report of the company
    • Use the revenue model to extract other information like capacity utilization, CAPEX cycles, and industry market share.

65 comments

  1. Omkar says:

    Yeess…finally after 33 days …Thank You 🙂

  2. Monk says:

    Yes finally, thank you sir.
    Sir, how do calculate CAPEX of a company?
    Also what are some future modules you will cover after financial modeling?
    Thank you.

  3. Johnson says:

    The change in Fixed Assets of a company should be the best indicator of capex in the company. High capex costs would weigh on future margins and are generally stated in the annual reports as well.

  4. RAMANATHAN SESHAN says:

    Respected sir,
    It would be nice to have a module on IPO. How to invest, how to read DRHP etc…

  5. Rahul says:

    Sir
    Production capacity of waluj(CV) in annual report is 930000 but it is 660000 in your model
    Correct me if am missing something here?

    Thank u for this chapter.

    • Karthik Rangappa says:

      Oh my! That’s a stupid mistake. Thanks for correcting that. Luckily I’ve not used that number anywhere else, so none of the calculations changes.

  6. Garima Nikhare says:

    Can u cover real estate modelling. It’s a whole lot different.

  7. Rahul says:

    Sir
    Can we build a revenue model for any other sectors business like fmcg, agriculture etc?

  8. Harsh says:

    Hello sir, Namastey
    Pls tell us that if it is a Service provider company then what will be the thought process to make a revenue model ?

    • Karthik Rangappa says:

      Quite the same, Harsh – What service do they provide? Whom do they cater to? Where are their clients located? How do they charge (billing methods)? How much do they charge?

  9. Ankit Aarethiya says:

    Respected sir,
    It would be nice to have a module on IPO. How to invest, how to read DRHP etc…

  10. Shivansh Agarwal says:

    the company I have chosen is a spirits company and there are two questions which I am unable to obtain form the annual report of the company and they are:-
    1. The production capacity of the company and in spirits company case no. of distilleries they have and what is their production and utilisation capacity
    2. The revenue generated from the exports and domestic as in their annual report they have not given it separately anywhere

    • Karthik Rangappa says:

      These details should be mentioned in the annual report. If not, you may want to dig around the analysts calls for these details. As far as the revenue from exports are concerned, double-check that they have exports before taking it into consideration.

  11. Shivansh Agarwal says:

    what do you mean by analyst calls

  12. Shivansh Agarwal says:

    and yes the company is involved in exports

  13. Anil says:

    Good piece, Karthik! However, is there a reason for choosing 301,885 and 257,729 as “International Sales” numbers for “FY20A” and “FY21A” respectively? I see in the AR that these are export numbers from (Table 3: Exports, in Units and Revenue for, Bajaj Auto). I would think that export numbers should be greater than or equal to the sales number. I also see that you’ve picked the actual sales numbers for motorcycles from (Table 2: Three-Wheeler Sale, Industry and Bajaj Auto (in numbers)), which have the sales numbers for CV as well.

  14. Anil says:

    Sorry, the actual sales numbers for motorcycles is from (Table 1: Sale of Motorcycles, Domestic and Exports), which is fine. However, the domestic sales for CV is picked from (Table 2: Three-Wheeler Sale, Industry and Bajaj Auto (in numbers)), but not the international sales.

  15. Shivansh Agarwal says:

    as earlier you told me to dig around the analyst call section for the export data if not given in the annual report, so where can I find analyst calls

  16. Sunil Kanoujiya says:

    Sir ye wala bhi chapters hindi me translate kare. Bahot help hoga.. Hum log ko.. Pls. Sir..

  17. Shivansh Agarwal says:

    the company which I have chosen doesn’t have a fixed manufacturing capacity every year it has a different manufacturing and selling capacity. So, should I take a average or I should just write what’s given

  18. Shivansh Agarwal says:

    the “average” production capacity is coming 82.1 million but for the year 2017 alone the production capacity was 90 million so what should be done in this case

    • Karthik Rangappa says:

      That seems like a case where the product was above average. DId the sales match up to it? This is something you need to check.

  19. Shivansh Agarwal says:

    yes the sales matched up to it, I think its because every year they are selling there one or two manufacturing facilities and many of their manufacturing facilities are dormant and from the annual reports I can tell that there production capacity is constantly decreasing as there production capacity is as follows:-
    2017- 90 million
    2018- 78.5 million
    2019- 81.58 million
    2020- 79.75 million
    2021- 70.71 million
    so what should I do in this case

    • Karthik Rangappa says:

      Declining production results in declining production (as can be seen), in this case, you need to look at the management discussion and analysis to figure what exactly the management envisions. If there are no statements addressing this, then perhaps you should look at taking an average or a constant 70.71 number with maybe an increase in the last 2 years of projection.

  20. Shivansh Agarwal says:

    and as we do the avg. of the above data its 82.1 million

  21. Shivansh Agarwal says:

    if I take average which is 82.1 million constant for every year then for the year 2017 the sales will be more than production capacity or can I do this that I take the production capacity for year 2017 as 90million and for the rest of the years I take an average

    • Karthik Rangappa says:

      Take it on the lesser side for the initial years and then marginally increase it over the final years. See if this fits well.

  22. Shivansh Agarwal says:

    I didn’t understand could you please tell it again in a more elaborative way

  23. Shivansh Agarwal says:

    ok! I will do as you say but by doing this, will it not tell that the production capacity of the company is constantly increasing but the truth is that its constantly decreasing

    • Karthik Rangappa says:

      Shivansh, what I suggested was a technique, you can plough in the numbers in a manner that makes sense. The actual numbers can be lower, good enough to ensure that it fits in line with the production capacity.

  24. Hitesh says:

    Hello Sir,

    When will you talk about all the financial ratios such as PEG, Enterprise Value, Ebitda to EV etc.
    Ratios that you have missed talking about in the fundamental analysis chapter.

  25. Shivansh Agarwal says:

    and should I do the same with selling capacity also

  26. Shivansh Agarwal says:

    and ya! one more question that if we can increase the production capacity of the company steadily like from YEAR 1- 70M , Year 2- 70.5M…. then what will go wrong if we decrease the production capacity YOY of the company as what’s happening in this case

    • Karthik Rangappa says:

      You can if you think the company is hitting a bad sales cycle. But eventually, you need to get a sense of impact on the profitability, cash flow, and valuations. All of these we will cover in this module.

  27. Sharad says:

    Sir , I want to ask that I have pledged axis liquid fund in zerodha. For how long I can pledge my liquid fund?
    Is there any interest charge on pledging of my liquid fund?

  28. Sharad says:

    Thank you so much sir.
    So it’s possible that I can pledge my liquid fund for 4-5 years too.

    • Karthik Rangappa says:

      Sharad, I don’t know if you can pledge that for so long. I’d suggest you speak to the customer desk once to get clarity 🙂

  29. Kaartik says:

    Thank you sir for providing us with this powerful content.
    Are you planning something for crypto as well? Really excited about upcoming chapters and modules!

  30. Sohan Khamitkar says:

    Integrated Financial modelling is not available in the app version of zerodha varsity. How to get certificate in integrated financial modelling? I really like this subject

  31. Shivansh Agarwal says:

    can I use the “forecast” function in excel for forecasting the data of upcoming years

  32. Shivansh Agarwal says:

    I am having one more problem with the manufacturing capacity is that the data is not provided correctly like for the annual reports there are three different data given every timefor manufacturing and selling capacity eg. in the snapshot its written (68.5 million cases produced and sold) then somewhere its written (company produced and sold around 80 million cases for the year) and then somewhere its written (company produced and sold 70.5million cases for the year) “what should I do” and this is the case with all the annual reports the data is given three time and very time its different

    • Karthik Rangappa says:

      Ah, the numbers being inconsistent is a problem. But check the revenue number and see what kind of sales will make up the sales data, for example for revenue of Rs.X, if 70.5million units sold makes sense, then go for that.

  33. Shivansh Agarwal says:

    and the sales data its not given separately anywhere everywhere its written that the company “produces and sells” about this much

Post a comment