11.1 – Move and Copy
The last chapter helped us understand how to build a reserves schedule for a given company. We made the reserves schedule for Bata India Limited and, in the process, discussed the concept of share capital, security premium reserve, capital reserve, and general reserve. Most importantly, we also discussed how the bottom line from the P&L statement flows into the reserves in the balance sheet, thus linking the P&L and Balance sheet statements.
This chapter will switch back to building the reserves schedule for the main model we are working with. As you know, we do not have access to the balance sheet and the associated notes of this company; hence we will have to make do with the raw data. You will soon realize that the reserves schedule we are about to build is no different from Bata India’s reserves schedule.
I’ll keep this chapter short because there is no conceptual explanation. This chapter will demonstrate how to build the reserve schedule. Given how straightforward this chapter is, you can also skip it. Or maybe skim through it as a revision of the previous chapter.
Setting up the excel for reserves schedule is straightforward, but let me take this opportunity to introduce a shortcut on excel. We know that the reserves schedule sheet on excel will look just like the other schedule that we have already built. Each column will represent the same years, and that won’t change. Given the consistency across the financial model, we can create a copy of any of the schedules (debt or asset) and modify the same.
To create a duplicate, go to the sheet (I’ll go to the debt schedule) and right-click on the tab –
Click on ‘Move or Copy’ and click on the sheet you want to copy.
Clicking on ‘Create a copy’ will create a duplicate copy of the sheet you’ve selected, the debt schedule sheet in this case. Here is how it looks –
The number 2 in the bracket indicates a copy of an already existing sheet in the workbook. Once the copy is created, you can delete the contents on this sheet and retain the column indexing, like seen below –
The move and copy technique is a shortcut and saves time setting up the sheet. We avoid going through several steps, and our sheet gets set up quickly.
11.2 – Building the schedule
Initially, the share capital of the company was INR 13.9Crs. The company raised equity in the 3rd year, bumping the share capital to INR 17.08Crs. We can assume that the company won’t raise fresh money and keep the share capital constant.
For the split-up of reserves, here are the line items. Of course, we don’t have the associated notes for this; you must consider what I state here as the actual data.
The company has Capital reserves at just Rs.11,500/-. I know it is a relatively small number, but I suppose the company maintains this for optics.
The security premium reserve is at INR 31.19 Crs across all the years. The opening balance of Year 1 general reserves of the company is at INR 83.81Crs. The yearly addition to general reserves is mentioned in the P&L, which we can pull to the reserves schedule.
The bottom line of PAT feeds into the surplus part of the ‘Reserves and Surplus’ schedule.
With this data, we can build the reserves schedule. Here is how the sheet looks –
As you can see, I’ve linked the yearly additions for the general reserves from P&L. Like I stated earlier, the surplus in the Profit and loss account is the PAT from P&L.
To complete the reserves schedule, we will have to project the general reserves addition during the year; this is a P&L projection. We can go back to the assumption sheet and build a separate assumption or make a projection directly in the P&L.
But as you can see, the appropriation to general reserves depends on PAT, which further relies on revenue and expenses. In the next chapter, let us compile everything we have done and project both the balance sheet and P&L. Of course, we will also complete the reserves schedule in the next chapter.
You can download the excel sheet used in this chapter.
Key takeaways from this chapter
- Move and copy feature in excel helps you replicate excel sheets in the given workbook
- Some companies, in their P&L, give the split of apportions they would make towards the general reserves
- To complete the reserves schedule, one must ensure the P&L is fully projected.
when will the next ch come
I’ll try and put it up soon.
in the company which I have taken have different balances for the retained earnings for the year 2018 in the AR 2018 its something else and for 2019 something else and something different in 2020.
if I take the latest data from the AR of 2020 then that balance will not be the same total for the retained earnings for the AR 2018 and it will also disturb the balance sheet fig. of total reserves and surplus of the AR 2018 and it won’t match with the figure in the balance sheet.
what should I do??
One thing that you can do is restate the balance sheet as per 2020 AR. See if this works.
in the AR the company has given data for only two years that is in the AR of 2020 only data for 2019 and 2020 is given and I am facing problem with the fig. for 2018
Hmm, you will have to juggle around a bit I guess. Maybe check once on screener.com to see how the numbers are stacked?
you mean screener.com or screener.in as on screener.com its directing me to finviz.com and at that site only foreign companies stocks are available
I mean the Indian website.
I checked the figures on screener.in and tickertape the figures for reserves and surplus aren’t matching with the fig. in the balance sheet of the company
Can you touch base with Screener’s support and ask them why the discrepancy is?