Reserves and surplus schedule How to Build on Excel

  1. Financial Modelling
    1. Financial Modelling Introduction
    2. Financial Modelling Tools & steps
    3. How to Make a Financial Model and choose the best Company and Excel Workbook Setup?
    4. How to build a financial model Step-by-Step Guide to Excel Sheet Setup?
    5. Financial Statements: A Step-by-Step Guide to Extracting Historical Data
    6. Financial modelling excel
    7. Learn financial modelling Balance Sheets, P&L, and Assumptions Know About
    8. What is financial modelling Assumptions and Projections?
    9. Financial modelling and valuation
    10. Investment decision calculation
    11. The balance sheet’s asset side reveals the company’s line items.
    12. Revenue Model & Growth Rate in in P&L Assumptions
    13. Basics of financial modelling CAPEX and Asset Schedule
    14. Financial Analysis: Gross Block and CAPEX
    15. Gross block & Capex: Constructing the Asset Schedule
    16. Depreciation : Connecting P&L and Balance Sheet for Accurate Asset Forecasting
    17. depreciation expense : Exploring Different Methods in Financial Modeling
    18. Debt Management: Connecting P&L and Balance Sheet for Accurate Liability Projection
    19. Interest Rate Calculation & Debt Schedule
    20. Share Capital & Reserves
    21. IPOs and Under subscription : Bata’s Share Capital Dynamics
    22. Reserves & Surplus understanding Bata schedule
    23. Reserves and surplus schedule How to Build on Excel
    24. Financial modelling projections
    25. Balance Sheet Projections and Completing Reserves Schedule
    26. Cash Flow Statements Analysing Operations, Investments, and Financing Activities
    27. What Is Valuation for Investor
    28. Free Cash Flow Key Components, Formulas and How to Calculate?
    29. FCFF and FCFE uses in Mastering Free Cash Flow Calculation
    30. WACC Weighted Average Cost of Capital Analysis
    31. Market Risk Premium analysis
    32. Tax Shield and its Impact on Equity Holder Returns
    33. Weighted Average Cost of Capital and Terminal Growth in Valuation
    34. Terminal Value Understanding Perpetual Cash Flow Projections in DCF Model
    35. Learn Financial Modelling
    36. Free Cash Flow to the Firm (FCFF) Calculation with examples
    37. Stock Valuation DCF Model & Stock Market Value
Marketopedia / Financial Modelling / 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.


    Get the App Now