• Originally published: 11/07/2020 05:27
Last version published: 17/05/2021 07:32
Publication number: ELQ-67817-2
View all versions & Certificate

Inventory Analysis and Management

Inventory balance movements, days inventory outstanding (DIO), turnover, seasonality, aging analysis, planning orders

Description
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 the 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. This analysis includes a horizontal waterfall showing the changes graphically.

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 and giving an idea of how total inventory flows compare to inventory balances.

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.

This Best Practice includes
1 Excel file, 1 pdf file

Discuss

Further information

Objectives

Comprehensive analysis of inventory

Use it if

Applicable to any production or selling business holding an inventory stock

n/a

Reviews

• (last updated: 27/08/2021 20:00)
• (last updated: 02/02/2021 07:28)

See all

See all

Discussion feed for Inventory Analysis And Management

The user community and author are here to help. Go ahead!

• Hi there
I need an inventory stock ageing analysis based on category and multiple SKU'S....Is this possible ?
• Hi Lindokuhle, thank you for your comment.
The model as is has a chart showing inventory ageing by products which can be easily changed to SKUs.
I am currently updating the file to include transformation of raw data into ageing categories. This should take me a few more days, I will let you know when it is published.
Thanks again for your interest in my tools.
Andrei
• Hi Lindokuhle,
I have update the model to calculate inventory ageing based on the inventory list/ledger. It can run the analysis both on a total basis and on multiple SKUs (with a break down by products in both cases).
Let me know if you have any questions or require any further modifications.
Thank you.

• 100%
• -
• -
• -
• -