7.1 – Recap and way forward

In the previous chapter, we looked at how a basic common sense approach can lead us to build a simple revenue model of a company. We made the revenue model using the helper model. In this chapter, we switch back to the primary model and continue to build our financial model.

To refresh your memory, so far in the primary model, we have –

• • Set up the basic excel layout for the financial model (indexing, grids, pane freeze)
  • Input the balance sheet and P&L data. The data source for the model input is the annual report of the company
  • Colour coded the numbers to distinguish between assumptions and facts
  • Built the P&L assumptions
  • Built the Balance sheet assumption

Both P&L and balance sheet assumptions are in the same sheet, called the ‘assumption sheet’. So far, our model has only three sheets –

• • Assumption sheet
  • Balance sheet, sheet
  • P&L Sheet

While we did make assumptions for line items where ever possible, we left out few line items to build a separate schedule for the same. I’d suggest you download the excel sheet to get a quick grasp of where we are in our journey of building a financial model.

Over the following few chapters, let us go ahead and build these schedules.

We will start with the asset schedule.

7.2 – Base rule

Before we build schedules, we need to understand the concept of the base rule. It is a simple concept, you’d probably already know this, but I might as well discuss it now 😊

As usual, let us take an example.

We all know electric vehicles are making a buzz in the market. Ola has plans to manufacture and sell electric bikes.

Consider for the sake of simplicity that Ola manufactures 4000 electric bikes in its first year of operation. Here are few data points that I’ve made up –

• • Number of bikes manufactured in 1^st-year operations = 4000
  • Number of bikes sold = 3750
  • Number of bikes unsold = 250

On excel –

I’ve introduced opening balance, total bikes, and closing balance here. The opening balance in this context is the number of unsold bikes carried forward from the previous year. It is zero in this example since it’s Ola’s first year of operation.

Total bikes are the sum of opening balance and bikes manufactured. It is 4000 in this case as the opening balance is zero.

The closing balance is the number of unsold bikes for the given year.

Now, let us assume that Ola manufactures and sells the same number of bikes in the 2^nd year.

Can you pause and tell me what the opening balance, total bikes, and closing balance for the 2^nd year is?

I hope you got that right. If not, let me quickly explain –

The opening balance for year 2 is the closing balance of year 1. So, in this case, the opening balance for Year 2 is 250.

They manufacture 4000 new bikes, so the total number of bikes is 4250, of which 3750 bikes are sold. Hence the closing balance for year 2 is 500.

The opening balance for Year 3 is the closing balance for year 2. So on and so forth.

The technique of linking the closing and opening balance is the ‘Base rule’. We use this pretty much in all the schedules that we build, including the asset schedule. For now, keep the base rule in the back of your mind. We will get back to it shortly.

7.3 – CAPEX

At this stage, let’s quickly understand what we are dealing with here. If you take a good look at the assets (or application of funds) side of the balance sheet –

You will quickly understand that the Gross block is a large item. In fact, in most balance sheets (at least for manufacturing companies), the gross block is the most significant chunk on the asset side. More so, when it comes to the balance sheet assumptions, we have used gross block extensively –

Given its heavyweight, it makes perfect sense to dig deeper into Gross block and strengthen our understanding. I would suggest you do this little exercise –

• • Pick a company of your choice and open its annual report
  • Open the balance sheet, check the asset side, and pay attention to the Gross block
  • Notice the associated note number
  • Look at the notes in detail

What do you observe? You are likely to notice the following –

• • Gross block is also called ‘Property, plant, and equipment.’
  • Gross block is (most likely) the heavyweight on the asset side
  • Few companies may report gross block, deduct the depreciation, and report the netblock
  • Few companies report the netblock directly

Further, the associated notes give you a detailed breakup of the gross block, so the notes give you a sense of the nature of this line item. Gross block invariably includes all the details related to the assets the company holds  –

• • Land (freehold and leasehold)
  • Improvements for leasehold lands
  • Buildings
  • Plant and machinery
  • Computer hardware
  • Factory equipment’s
  • Electric fittings
  • Vehicles (including aircraft)
  • Maintenance and repair works

The things listed here are ‘CAPEX’ in nature. Now, what is CAPEX?

Capital expenditure or just the CAPEX of a company are funds used by the company to invest,  upgrade, and maintain physical assets such as the ones listed above. For example, if the office roof is leaky, money spent on fixing the roof gets reported as CAPEX.

Or if a manufacturing company wants to build a new manufacturing plant, then right from acquiring the land (or leasing), to setting up the plant with equipment and factory machinery is considered as ‘Capital expenditure’.

Some companies can take up projects so large that the capital expenditure can run across several consecutive years, draining the company of its financing avenues. Of course, companies do this with an expectation that the future payoff from the project far exceeds its current capital expenditure.

When such capital expenditure occurs across many years, it’s called ‘the CAPEX cycle’ of a company.

As an analyst, it is crucial to understand if the company is going through a CAPEX cycle (expansion)  or running just the maintenance CAPEX. If the company is in an expansionary phase, you need to understand why, how, and will the payoff likely exceed the current cash burn.

If the company’s CAPEX is essentially maintenance CAPEX, then you need to figure if the maintenance CAPEX is something that the company can sustain through year on year.

Whenever I think of how maintenance CAPEX can be taxing, I remember my friend’s story.

A good friend of mine (a regular salaried person, like many of us in Bangalore) sold a plot in Bangalore and suddenly became cash-rich overnight. The first thing he did was reinvest the bulk of cash into another property. With the remaining money, he bought himself a fancy BMW, and with that, he expended all the cash he gained from the sale of the property.

The maintenance CAPEX on the BMW is quite heavy – its fuel-guzzling, hefty insurance premiums, and repairs are super expensive. His salary was not supportive of such a CAPEX in the first place. Eventually, he had to sell the car.

I detoured to give you this story to bring your attention to the maintenance CAPEX of the company.

Ensure the company earns enough to maintain its assets.

7.4 – Estimating CAPEX

We discussed Ola earlier in the chapter, so let me continue the same example here. Assume, Ola commenced its manufacturing operation with an initial CAPEX plan of 500Crs for year 1. Assume they invest 500Cr into acquiring land, machinery, equipment’s, assembly lines, etc. Remember, this is also Ola’s gross block.

What do you think is Ola’s opening and closing gross block?

We know that for year 2, the opening balance is the closing balance of year 1. Assume Ola does another 100Cr worth of CAPEX in year 2.

I hope it is clear so far. In year 3, assume Ola does not add any Capex but rather sells off machinery worth 50Crs. What do you think is the closing balance for Year 3? And what is and the opening balance for year 4?

Here is the table –

Again, the opening balance for Year 4 is the closing balance for Year 3. Hopefully, this example gives you a sense of calculating the opening and closing balance of the gross block.

When you look at the balance sheet of companies, they directly report the gross block (or property, plant, and equipment) number. Of course, they do not state the CAPEX number in the balance sheet. For example, in the model that we are working on, the gross block numbers are as follows –

These numbers are good enough starting point to develop the asset schedule.

7.5 – Asset schedule

We will now start building the asset schedule for the model. As a first step, let’s set up our excel sheet. Setting up the excel is precisely the same as the other sheets in the model –

Let’s roll out the base rule in motion; hopefully, you get the drift here –

As you can imagine, I’ve linked the closing gross block for Year 1 as the opening gross block for Year 2.

The closing gross block for year 2 is 310.58, and this means the assets of the company has increased –

310.58 – 257.78

= 52.80

Hence the CAPEX for year two must be 52.80 Crs. Since the CAPEX has increased, there has been no disposal of assets. I can add this on excel –

I hope you found this easy to understand because it is 😊

I can continue the calculation the same way for the rest of the years.

The numbers here will match the numbers stated in the balance sheet, but we have managed to extract the CAPEX numbers from gross block, which wasn’t explicitly available in the balance sheet.

Remember, the end objective is to arrive at the netblock of the company. Net block, as you know –

Gross Block – Depreciation = Net block.

Sounds straightforward, but here is a twist.

I want you to quickly take a look at the Y2 depreciation numbers stated in the Balance sheet and P&L. Tell me what you think?

Y2, depreciation in the Balance sheet is 121.73 Crs, and for the same year, Depreciation in P&L is 24.45 Crs. Which one will you consider here?

Well, you have to consider both.

Remember, depreciation is an expense. Hence it gets stated in P&L as an expense for that year. But this year’s depreciation gets carried over to the following year in the balance sheet; hence it’s called the accumulated depreciation.

Let us deal with it in excel. Here is how I’ve set up my sheet –

Yes, we will apply the base rule again. I’ll take the closing balance for year 1, set that as the opening balance for year 2. I’ll add to this the current year depreciation stated in the P&L. When I add the opening balance and closing balance, I should get the closing balance of the accumulated depreciation for year 2. The number I get here should match the depreciation number stated in the balance sheet.

As per the base rule, the closing balance is 125.39Crs, but as stated in the Balance sheet is 121.73 Crs. A difference of nearly 3.66 Crs.

How and why is this difference?

Well, the difference arises due to the small asset write-offs and adjustment that happens. We treat this as a depreciation non-expense. If you adjust for this, the numbers should match.

I hope you’ve been able to keep up with this. It is not too complicated, but as a person doing this for the first time, you may find it overwhelming.

Now that we have calculated the gross block and the accumulated depreciation getting the netblock is pretty straightforward.

Net block is the difference between the closing gross block and the closing balance of depreciation.

I’d suggest you match this with the netblock number in the balance sheet for your reference.

7.6 – Capex projections

Now that we have set up the asset schedule, we need to move ahead and make some projections here.

The most crucial projection here is the CAPEX projection. It is super important to understand how the company will be dealing with capital expenditure over the coming years. Now, because this is such an important figure, companies usually explicitly state their CAPEX plans. You can get this information by reading the management discussion analysis or the analyst reports (or even watching a business channel).

I’d suggest you take some time to watch this interview to the context into how you can project the CAPEX by considering management’s statements.

In this recent interview, the CFO of Bajaj Auto clearly states the CAPEX requirements for the coming years. In my view, this kind of information is more valuable than any projection we can do.

If you don’t have access to this kind of information, then you have two alternatives –

• • Find out the average CAPEX and assume the averages hold for the coming years
  • Variable method, here you look at the company’s historical CAPEX. If the company has been through an expansionary phase (high capex spend), you taper it down. Alternatively, if the company has had a low CAPEX cycle, you gradually increase the CAPEX spend.

Remember, both these techniques are your alternate. Your first option should be the management itself.

In this model, we will use the variable method. Historically, the CAPEX was high, so I will gradually taper it down with an assumption that the company is through with the bulk of its CAPEX cycle. I’ve also assumed that the company has zero disposal of assets.

Now that you have the CAPEX number, isn’t it easy to figure the Closing gross block number? Of course, it is. Let us complete this –

Now that we have the closing gross block number, we can plough these numbers back to the balance sheet.

With this, we have made our first balance sheet projection, so congratulations on that 😊

But why did we plough this back into the Balance sheet right away? Why not complete the depreciation projections and then make the projections in the balance sheet? Well, there is a reason for that.

I’m tempted to continue that explanation here, but I guess this is a super long chapter already. I promise I’ll put up the next chapter quickly, which will have this explanation.

Stay tuned until then!

Download the excel used in this chapter here.

Key takeaways from this chapter

• As per the base rule, the closing balance for year 1 is the opening balance for year 2. We use base rule across many schedules in a financial model
• Gross block includes all the assets that the company owns; usually, the gross block number is a heavyweight on the asset side
• Capital expenditure or the CAPEX of a company includes all funds spent on acquiring new assets or maintaining assets
• Asset schedule helps us extract the CAPEX numbers from the gross block number
• To project the CAPEX, ideally, one should look at what the management has to say
• Other CAPEX projections techniques include the averages and the variable method.