Inventory Reports for Manufacturers

In manufacturing companies, it’s very common for non-accounting staff to want to do some analysis and research into inventory levels.  Typically this is the supply chain group, who want to see the ins and outs of inventory over time, often to analyze and do some statistical analysis.

In my other Blog Post on Analyzing Business Central Data with Odata, I discuss importing data into an Excel workbook to run analysis.  We’re going to do that with Item Ledger Entries today, and do some analysis in Excel to calculate a safety stock level (very high level).

Step 1: Create Item Ledger Entries Web Service (or use one that is there)

To do this analysis, you will need to create a Web Service for Page 38 – Item Ledger Entries.  This is a list page that shows all the item ledger entries, their posting dates and quantities in and out.  If you want to do some weighted calculations involving inventory value – the report will also bring in the inventory values.

In Business Central “out of the box” you may also find a pre-existing Query object called ItemLedgerEntries in Web Services.  Feel free to use this.

Otherwise you can add a web service.  This can be done in Dynamics NAV and Business Central.  If you don’t already have a ODATA web service for Item Ledger Entries, follow these steps:

Start by creating the web service “my_item_ledger_entries” following the instructions in the previous blog.

Step 2 : In Excel, create a relationship between the data 

We’re going to use Excel to load the Item Ledger Entries into a Sheet (Items) and we’re going to use some magic to create a list of Weeks in a 2nd Sheet (Weeks).  

In the Weeks sheet, we’re going to create 3 columns.  The first is a list of dates.  I copied about 1000 rows, each of which has a different date.  My sample item data starts with a first posting date of 01/01/2020 – so my Weeks sheet is similar.

In Cell B2 I put in the formula =WEEKNUM(A2) and in Cell C2 I put in the formula =YEAR(A2)

I copied these formulas down for each date.  I’ll format this as a Table.  My sheet now looks like this:

In this sheet, for every date, I have a Week No. and a Year associated with it.  We’re going to use this with a Power Query.  

Step 3 : Join our data with Power Query

Power Query is a feature that you can use to join data together to form a “joined” set of data.  If you have used VLOOKUP before, this is a more efficient and easy way to create a pivot table using 2 sources of information.

Go to the Data tab, and click on Relationships:

You may be prompted to activate the Analysis Tools in Excel.  Go ahead.  When you do so, you might notice a new ribbon has been added called Power Pivot.  Now you can create a relationship between the Query we imported via ODATA and the Table we created with dates.  I didn’t change the names of these two areas, so they are Query2 and Table3 .

My Relationship looks like this:

I am joining the Document Date from the Item Ledger Entries and the Date from the Weeks list I created.

Step 4 : Create a Pivot Table

If we go under the Data ribbon again, we can click on the green Manage Data Model icon:

This opens up our Power Query workbook, which shows the data we have joined together: 

Notice the Query2 and Table3 tabs below the data.  If we look at the Diagram view of this data we see something that should be familiar to most people who have worked with databases like Access.

Let’s create a really simple Pivot Table.  In the Power Query Home ribbon, select Pivot Table and create a Pivot Table on a new sheet:

You can add fields from either Query2 or Table3 (or both):

Step 5 : Analyze Data

Let’s create a really simple Pivot table.  Under Rows let’s add (from Query 2) Item No and (from Table 3) Week No.

Then Let’s add a Values of Quantity (it will sum automatically).  Our Pivot Table looks like this:

This is giving us the Weekly Quantity of inventory transactions grouped by Item No.

We’ll remove the Grand Totals by going to the Ribbon, Selecting Pivot Table Tools, Design and setting Grand Totals off:

I like slicers, so in the Pivot Table Tools in the Ribbon, under Analyze add a Slicer for Item No. and another Slicer for Document Type.

Finally I am going to add 2 calculations.  First, I added =STDEV.P(C:C) (where C is the column with my quantities) which calculates a weekly Standard Deviation for the entire column C.  Then, I add =AVERAGE(C:C) which will calculate the average weekly transactions.  I’ll add some text so I can tell what I’m looking at.

Now I have this spreadsheet:

Not a lot of my items have decent data (most of them are not sold weekly) but if I use item 1896-S and select Sales Shipments, Transfer Shipment and Sales Return Receipt I get some decent sample data that might look like what you would use.  I chose these Document Types because I want to look at Demand, not Supply.  My demand drives what my safety stock should be. 

What I am looking at here is an item that has been sold, returned or transferred in and out of inventory (a Finished Good I sell) 3 weeks in a row.  The average consumption is 2.33 items per week (the negative number means that it was removed from inventory, or sold).

The Standard Deviation is 3.68 units. That’s a lot.  It means that from one week to the next, there is more variation in the quantity than the average.

The general rule of thumb is that 2x your Standard Deviation should cover 95% of cases.  So a good Safety Stock might be 1 week’s average consumption + 2x the Standard Deviation or:

2.3 + 2 x 3.7 = 9.7 which we can round up to 10 units.

Conclusion

This example is a very basic analysis for looking at my item demand, grouped weekly and then broken down to calculate a standard deviation and average usage.  My example of calculating a Safety Stock is totally unscientific, but it is much better than trying to guess what it should be based on averages, feel, ouija boards etc…