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 3^rd 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.