Balance Sheet Projections and Completing Reserves Schedule

  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

Reserves Schedule

We had been limited due to the absence of fresh yearly contributions to the general reserves; however, we can now source that information from the P&L and complete the reserves schedule.

We’ll move the reserves schedule data to the balance sheet, and the model should demonstrate how numbers can flow between sheets.

–  Balance sheet projections

The balance sheet projection is quite analogous to the P&L projection. As in the case of net sales in P&L, gross block is substantially the most significant element in the balance sheet. Many of the balance sheet assumptions are derived from gross block. I shall omit a few line items which I consider straightforward and post a snapshot for your reference –

Current liabilities and provisions are expressed as a percentage of the gross block, with shareholders’ funds and loans calculated independently in different schedules. Additionally, deferred tax liability can be found by computing depreciation percentage from the asset schedule.

Next, let’s focus on the application of funds, starting with inventory. We should dedicate some attention to this item.

The value of inventory on the balance sheet is in Indian Rupees. We determined the ‘Inventory Number of Days’ by assessing the inventory data and including it in the Assumption Sheet. This figure represents the time it takes to convert stock into actual sales.

The inventory number of days can be useful in helping us form an opinion about management’s efficiency, the product’s appeal and market acceptance. Going forward, we use the average of that data for future years.

The balance sheet holds the inventory data in Rupee terms, and the assumption sheet shows the inventory number of days for present and future years. In order to ensure consistency between the two sources, we need to convert this information in the assumption sheet back into values in the balance sheet.

Once the balance sheet data is converted to the inventory number of days, a projection for averages can be made and the inventory number of days can be recomputed back into Rupee value.

To discover the Rupee value of inventory, a simple formula is used: days to Rupees.

Two*(Inventory number of days * (Material consumed/365))-Previous year inventory.

I won’t get into the details of how this formula is calculated; you can find more information online if interested.

I have put the equation to use on a balance sheet in excel, and this is what it looks like.

I’ll create the balance sheet projections in a snap. Making the estimates is easy – all I need to do is follow the balance sheet.

Congratulations – we now have a completed P&L balance sheet minus the cash and bank balance.

We will generate the cash and bank balance in the upcoming chapter by creating a cash flow statement, which is a vital step of our financial model. The resulting figures from this statement will be returned to the balance sheet. With these figures on hand, we can anticipate that the balance sheet would then be equal and balanced.

We’re finishing up this chapter and there are a lot of numbers and predictions at play. It’s inevitable that mistakes will be made – say, two months after constructing the model, I think the gross block number for Y6 is 700Cr instead of 588.77Cr; what should I do? Do I need to modify the entire framework?

Since we are making the model in an unified way, no extra work is required; any changes made will be mirrored across the model. So, don’t be too concerned with precision right now. We can experiement with it as much as desired.