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.