Investment decision calculation

  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 / Investment decision calculation

Dealing with inventory

The deferred tax assumption is finalised, and the liabilities side is taken care of. Share capital and borrowings will be handled separately; creating ‘schedules’ for these items is necessary.

We now turn to the asset side of the balance sheet. The first line item is inventory.

Examining the inventory information in the balance sheet showcases the company’s stock value. For example, Y1 had a total of 92.17 Cr; in Y2, this sum rose to 194.33 Cr, while in Y3 it was recorded as 160.83 Cr, and so forth.

Manufacturing companies will have inventories in their balance sheet, which are essentially finished goods. The objective is to sell these stock items as quickly as possible. A company’s success could be measured by how short the period of time for selling its inventory is.

Every enterprise has its own timeline when it comes to transforming inventory into sales. It depends on the character of the firm.

For instance, a company manufacturing pressure cooker may take 30 days to convert the inventory into sales, whereas, the same for a car manufacturer might extend to 75 days.

Regarding the inventory assumptions, we adopt the following approach –

To figure out the number of days it takes the company to transform inventory into sales, calculate its Rupee value.

Calculate the mean length of days for upcoming years.

Calculate the Rupee value for future years using the average number of days.

Let’s give these steps a go and observe the results – you’ll soon find it simple enough.

Why bother with all of the work mentioned before? Wouldn’t it be simpler to just get the inventory growth rate and average, as we did when discussing deferred tax in the last chapter?

By converting the Rupee value of inventory into the number of days to sales, it provides greater insight into the company. This can assist you in making well-informed investment decisions. For example, if two companies manufacturing cameras have similar features, yet one takes 40 days and the other 70 days to turn inventory into sales, then this information tells you something about their respective strengths and weaknesses.

It appears that Company A has more effective inventory management.

It could be said that Company A has created a superior product, and this is the reason why the market favours their cameras.

It is possible that Company B’s sales incentives are not as appealing to merchants, thus encouraging them to favor Company A.

It’s quite likely that company A has effective management, with meticulous planning.

It’s clear that insights can be plentiful, making it practical to take the extra effort to calculate the inventory number of days – let’s get started now.

Excel makes it effortless to calculate the inventory number of days using a formula which I refer to as the conversion formula, since it converts Rupee value of inventory into this quantity.

For Years 1 and 2, the inventory value is 92.17 Cr and 194.33 Cr, respectively. Using the following formula to transform this information –

= (Average inventory of Y1 & Y2 / Materials consumed for Y2) * 365

You might wonder why we use the materials consumed in Y2 in the denominator instead of Y1. This is because we are finding out the inventory number of days for Y2. To do this for Y1, the formula would be:

= (Average inventory of Y0 & Y1 / Materials consumed for Y1) * 365

We’ll begin our analysis with Y2 as Y0 information is not available.

Therefore, this formula can be used for both Y1 and Y2.

= Average (92,17, 194.33)

= 143.25

The materials consumed for Year 2 (data available in the Profit and Loss statement) is 762.86 Crores.


= 0.18778

Finally, we use 365 to multiply the calculated result in order to obtain the number of days in inventory.

= 0.18778 *365

= 68.53

This company takes an estimated 68 days to convert 143.25Cr worth of inventory into sales.

You can easily accomplish this in Excel in a single step.

I advise that you enter the inventory number of days in the assumption sheet and apply the conversion formula directly in your excel sheet.

By computing the inventory number of days for Y2, I can transfer the Excel to rows Y3, Y4 and Y5 to acquire the relevant values.

The inventory number of days fluctuates between 68 to 78 days. To gauge the benefits or detriments of this, it’s important to evaluate it against companies that do similar business and have a similar size. Take Bajaj Auto and Hero Motors – they can be used as a comparison point.

Going forward, for the years 6 through 10, we can take an average of the inventory’s number of days.

We have determined the past and projected future inventory number of days.

The same steps can be applied to Sundry Debtors/ Account Receivables too; by converting receivables from their Rupee value to the number of days they are due and then back again to their monetary value.

In the following section, I’ll likely go over the procedure for utilizing the aid device.

Let’s move forward with additional balance sheets and profit and loss calculations.