Reserves and surplus schedule How to Build on Excel

This last chapter introduced us to the building of a reserves schedule for a particular business – in this instance, Bata India Limited. We discussed some very important concepts such as share capital, security premium reserve, capital reserve and general reserve. We also took a closer look at how the P&L statement contributes to the bottom line on the balance sheet and showed how it links up with said balance sheet.

This chapter will focus on constructing the reserves schedule for the primary model we are creating. Unfortunately, we are unable to access the company’s balance sheet and its related notes; therefore, we must make use of raw data. You’ll notice that our reserves schedule resembles Bata India’s very closely.

This chapter will provide insight into constructing the reserve schedule with no complicated definitions. If you are familiar with the concept already, then you can ignore this segment. Otherwise, it is worth skimming through to revise what you have read previously.

Setting up the reserves schedule on excel is easy, but let me take this chance to introduce a timesaving tip. The reserves schedule sheet in excel will look like the other schedules we’ve constructed, each column representing the same years. Thanks to the model’s consistent structure, we can copy any of the schedules (debt or asset) and adjust it accordingly.

In order to make a copy, one should select the debt schedule tab and use their right mouse button.

Choose ‘Move or Copy’ and select the sheet you’d like to duplicate.

Selecting the ‘Create a copy’ option will generate a replica of the chosen debt schedule sheet. Here, this is what it looks like –

The move and copy technique is an efficient shortcut that helps set up our sheet in a fraction of the time compared to going through several steps.

– Building the schedule

The company’s initial share capital was INR 13.9Cr, increasing to 17.08Cr when equity was raised in the 3rd year. Presently, it is expected that no further funds will be sought and the figure will remain unchanged.

The distribution of reserves is represented here; no relevant notes are available, so this should be considered the actual data.

The company has Capital reserves of Rs. 11,500/-. Even though it is a meagre amount, the company is likely keeping it for appearances.

The security premium reserve is at INR 31.19 Cr across each year. Year 1 general reserves of the business begin with a value of INR 83.81Cr which, then, have an annual addition as shown in the Profit & Loss statement, which can be used to build up the reserves schedule.

The net result of PAT goes directly to the additional component of the ‘Reserves and Surplus’ segment.

Using this information, we can construct a reserves plan. This is what the sheet looks like –

It is clear that I have connected the yearly contributions to the general reserves from the Profit and Loss account (P&L). As mentioned previously, the excess in the P&L is in fact the Profits After Tax (PAT).

In order to finalize our reserves forecasts, we must project what additional general reserves will be during the year. This can be done by either utilizing the assumption sheet to create a new projection, or inputting them directly into the P&L.

We can see that the appropriation to general reserves is reliant on PAT, which in turn relies on revenue and costs. In the next chapter, let us put together all our work and estimate both the balance sheet and P&L. We will then finish off with the reserves schedule.