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.



54 comments

  1. Shivansh Agarwal says:

    when will the next ch come

  2. Shivansh Agarwal says:

    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??

  3. Shivansh Agarwal says:

    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

    • Karthik Rangappa says:

      Hmm, you will have to juggle around a bit I guess. Maybe check once on screener.com to see how the numbers are stacked?

  4. Shivansh Agarwal says:

    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

  5. Shivansh Agarwal says:

    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

  6. Shivansh Agarwal says:

    I have send the msg to screener.in but what about ticker tape

  7. Shivansh Agarwal says:

    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

    • Karthik Rangappa says:

      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.

  8. Shivansh Agarwal says:

    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

  9. Shivansh Agarwal says:

    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

  10. Shivansh Agarwal says:

    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

  11. Shivansh Agarwal says:

    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

  12. Shivansh Agarwal says:

    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

    • Karthik Rangappa says:

      Like I mentioned earlier, if its your first attempt to model, maybe you should take a simpler company which has consistent data 🙂

  13. Satyam says:

    Hello Sir,

    When will you explain PEG, Enterprive Value, then EV/ EBITDA and other ratios used with enterprise value?

  14. Anurag says:

    Hello Karthik Sir, when do you expect to upload the next chapter and how many further chapters do you think would be needed

  15. Shivansh Agarwal says:

    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

  16. MS says:

    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 🙂

  17. Muthu Suriya says:

    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?

  18. Shivansh Agarwal says:

    what do you mean by guessed as much ??

  19. Anurag says:

    Hi Karthik sir, could you please cover how to consider the foreign exchange impact as well for companies having global operations

  20. Pushkar says:

    Hello sir, when would you post the next chapter. Eagerly waiting for it

  21. Akhil Goyal says:

    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.

  22. Akash says:

    Hi Karthik,

    Hope you are well!

    Can you please share when can we expect the new chapter from this module?

    Thanks.

  23. Shivang Tomar says:

    Hey Karthik,
    How reliable, according to you is the intrinsic value feature in tickertape? Also how do they calculate it?

  24. Shivang Tomar says:

    What according to you is better structuring approach single or multi-worksheet for the model?

  25. Gautam says:

    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.

    • Karthik Rangappa says:

      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.

  26. Tamizh says:

    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

    • Karthik Rangappa says:

      Share capital = Face value * number of shares
      Security Premium amount = The listing premium over and above the face value

  27. Barun Chakravarty says:

    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!

Post a comment