This set of tools explains how to analyze inventory, communicate findings of your analysis and make practical use of them. The main areas covered by this analysis are:

1. Understand what drives your inventory balances. This includes analyzing the movements of inventory by product and seeing which products affected the change to the largest extent.

Inventory balances are reduced by the sales and increased by the purchases. There is another visual tool, also based on horizontal waterfall chart, showing the effect of these two factors.

2. Days Inventory Outstanding. Unlike many other calculations which take inventory balance at some date and COGS (or revenues) in that period, I am using a formula taking the actual COGS in prior (or subsequent) periods and returning the actual number of days covered by the inventory balance. Writing this formula is clearly an Excel challenge which is solved in a smart way using the OFFSET, MMULT and other functions.

I am giving two alternative calculations here: based on past and future sales.

3. Inventory seasonality – many business are prone to seasonal changes in sales which affects inventory balances, turnover and cost of sales.

4. Ageing of inventory – this part explains how to transform inventory list into ageing groups, calculate inventory reserve and demonstrates inventory balances by products and by age groups in a professionally designed chart.

5. Planning orders taking into account inventory requirements and delivery time.

Many of the methods used in this publication can be applied to other types of financial analysis (e.g. making aging of accounts receivable, planning cash receipts based on sales payment terms).

As a bonus tip, the publication explains in detail how to create a horizontal waterfall chart. This is a smart and efficient type of chart visualizing variances between the numbers. It will surely make your presentations look professional and your reports - impress your clients, managers and colleagues. This chart is compatible with all versions of Excel.

Reviews Add a review
No reviews yet

More From Andrei Okhlopkov

Browse our top rated business templates. See All
HR Analytics and Dashboard
This publication contains a set of tools to perform employee analysis and communicate key statistics in a concise, efficient and…
Startup Cap Table
Most startups are initially financed by their founders. They contribute relatively small amounts of money for the company to make…
Cap Table with Investor Returns, Derivative Instruments and Charts
Most startups are initially financed by their founders. They contribute relatively small amounts of money for the company to make…
Cost-Volume-Profit (Breakeven) Analysis
Cost-Volume-Profit analysis is one of the fundamental types of analysis which deals with product profitability. It classifies the costs into…
“Budget-versus-Actual” Charts
This file contains magazine-quality charts in Excel to illustrate variances between budgeted and actual figures.  Professionally designed charts in business…
Due Diligence Tools and Methods
A prospective investor is concerned about the target’s: 1. Quality of earnings 2. Adequacy of working capital 3. Unrecorded or…
See All