Inventory Management and Analysis best practice with dynamic charts

Inventory Management and Analysis best practice. Useful to the Management team as well as to external financial analyst.

by Pierre-Alexandre HEURTEBIZE
Consultant - M&A, Modelling and Financial analyst - M&A director at HoriZen CapitalFollow 56

1 review1,486 views|1 comment|Bookmark Add to cart - $30.00

Description
This best practice offer a nice visual way to track and analyse inventory in different ways. The model is split in three sections, Graphs, Computation and Source. It starts with an aggregate view of inventory by type of product on a monthly basis, shown on an inventory value basis but also on a number of units basis. Monthly breakdown is shown in absolute value and on a % over total basis to give a better idea of the evolution of inventory mix over time.
The model then moves on to a nice and simple bridge showing the movement of Inventory value in the last 3 month, broken down on a graph by type of product. The following page works on the same principle but show the movement of inventory by type of product over the last 3 years.
The next tab allows the user to flexibly and easily analyse monthly inventory product by product. The dynamic chart shows the evolution of inventory along with the evolution of inventory / sales for a chosen product family. Thanks to a drop down list, the user can easily analyse the product category he is most interested in.
We then move on to the analysis of Inventory seasonality for the top 5 product family. This will allow the user to effectively see how inventory levels by product category have changed year on year for each month of the year.
The following tab of the model does a DIO analysis based on Countback method. The model calculate DIO both based on past COGS and on Future COGS to adapt to the different use and objectives the user may have. We have also included in the model an explanation of how the Countback method works and on why and when it is useful to use past COGS or future COGS. We included a dynamic chart in this tab to allow the user to focus on one product family thanks to a dropdown list.
Finally, we have included an Ageing analysis chart that will allow you to effectively track outdated inventory and spot potential needs to depreciate stock.

In the calculation tab are included the two DIO calculation. We've put a lot of effort to come up with a formula which has the right balance of simplicity and flexibility. It does require a bit of mental gymnastic to understand but we believe that it can easily be understood by any financial analyst used to work on excel. We have also tried to apply programming best practice in terms of code readability to present the excel formula in a more understandable way

This Best Practice includes
1 Excel model with 14 tabs