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?
I have send the msg to screener.in but what about ticker tape
Does the AR data match ticker tape? If yes, then you should take TT. Else, wait to hear from screener.
I just rechecked everything myself and found that in the AR 2019 they have restated the retained earnings fig. for 2018 but the restated fig. has only been considered in the notes and not in the balance sheet and when they have made the AR for 2020 they took the restated figure in the notes not the fig. which was there in the balance sheet and that is why there is a difference between the figures.
and now because of this the problem which arises is that if I take the latest data as per the AR 2020 then the total of other equity will not be the same as in the balance sheet and if take the old data then it would make a difference in the total of the years 2019, 2020, 2021
Yeah, these restated numbers can be an issue. You will have to juggle a bit and take the numbers from the notes. If this your first model? If yes, why don’t you take a simple company whose statements are clear and work with it? Once you are comfortable, you can take up slightly more challenging ones.
and on the ticker tape website they also have different than the figures originally stated in the company’s AR and the figures also doesn’t with the figures on screener
Could be the same problem that you stated. Did you hear from screener?
yes I got the msg from screener but they misinterpreted what I said I was taking about YOY but they they gave me the resolution for QOQ I have raised my query again
Ok, hope you get the right answer.
I also checked the figures with multiple stock screeners like yahoo finance, Economic times, etc each and everyone had different figures only moneycontrol had the exact figures as stated in the balance sheet for reserves and surplus but I was not able to get the expansion for reserve and surplus on their website
For that reason, I prefer taking the numbers directly from the Annual Report.
the screener team is taking too much time to reply. Is there anything else that you would suggest me to do to check the figures
I cant think of any 🙁
I got a reply from screener they said that this is the first time they have come across such thing, its unique 😂. it was because of something related to treasury shares, they will contact their data providers that how did they arrive at those figures.
so what should I do until then
Like I mentioned earlier, if its your first attempt to model, maybe you should take a simpler company which has consistent data 🙂
Hello Sir,
When will you explain PEG, Enterprive Value, then EV/ EBITDA and other ratios used with enterprise value?
Later in this module, Satyam.
Hello Karthik Sir, when do you expect to upload the next chapter and how many further chapters do you think would be needed
Will update the next chapter by next week. At least another 4-5 chapters more.
hey! I dug deeper into the AR of the company and found that from 2020 the company started adapting the AS-115 and they chose year 2018 to start this. and due to this only there was some difference in the retained earnings of the company.
so, what I am thinking of doing is that not to interrupt with the figures of 2018 for the retained earnings let them as it be and simply just take the restated balance of 2018 and simply just write it as the opening balance of 2019.
should I do it
Guessed as much. Yeah, you can do this.
Karthik sir, I just started my venture into the markets and I am NOT relying on sources like google and YouTube but only VARSITY to learn market A to Z, Can I 🙂
Happy learning! Hope you like learning on Varsity 🙂
Karthik Sir, If i brought a call option in OVERNIGHT(NRML)form today and I can sell it tommorrow or some later point of time but before expiry – am I correct Sir?
Yes Muthu, you can do that.
what do you mean by guessed as much ??
I was thinking the same, that it could be due to accounting policy change.
Hi Karthik sir, could you please cover how to consider the foreign exchange impact as well for companies having global operations
Anurag, this is a core treasury operation, I’ll try my best to include (but won’t promise).
Hello sir, when would you post the next chapter. Eagerly waiting for it
Pushkar, hopefully by next week.
Thanks for the great knowledge. You’re doing a great job.
Can you also add some chapters about commodity and currency options in currency and commodity module.
Thanks again.
Check this Akhil – https://zerodha.com/varsity/module/commodities-currency-government-securities/
Hi Karthik,
Hope you are well!
Can you please share when can we expect the new chapter from this module?
Thanks.
I plan to put this up in the next 3 days.
Hey Karthik,
How reliable, according to you is the intrinsic value feature in tickertape? Also how do they calculate it?
I’ve not really looked into that, Shivang. So wont comment 🙂
What according to you is better structuring approach single or multi-worksheet for the model?
Multi worksheet is what I’d prefer.
In Year 3A Share capital is bumped to 17.08, it means company has raised additional capital. Don’t you think securities premium should change as well because company must have raised capital over and above it’s face value if talk in a realistic way. Please correct me if I am wrong, that seems confusing to me. Thanks for your amazing lectures through story telling.
Hmm, so security premium reserve is created at the time of IPO (as far as I know, I need to double check). Does not come into picture when we have FPO etc.
Hi Karthik,
If the fresh shares are issued after IPO,wont the “Securities Premium Amount” increases?
For example,
100 shares issued with IPO price Rs105 with face value of Rs5,share capital=100*5=500
Securities Premium Amount=100*(105-5)=10000
If 50 shares are issued after 3 years with same IPO price and face value,
then wont the “Securities Premium Amount” increases to 100*(155-5)=15000
Share capital = Face value * number of shares
Security Premium amount = The listing premium over and above the face value
Financial Modelling brought me to this course, and I’m glad to have come here. Great work.
I just wanted to point to what looks like a minor typo. Under sub-section 11.2 – Building the schedule, please correct “Initially, the share capital of the company was INR 13.9Crs.” The amount is actually INR 11.39 Crs.
Cheers!
Thanks, Barun. Let me check this again 🙂