Follow

Inventory Analysis Tool

Summary

Clients appreciate the incredibly useful information the Inventory Audit Report brings to their fingertips, but it always needed something more – and that’s this tool.  By bringing this information into an easy-to-use Excel tool, you can quickly analyze inventory data the way you want it or use our stock graphs and tabs to assist you.  Go ahead and make magic with inventory analysis.

 

Important Notes

Find this tool here: Inventory module > Inventory Reports > Inventory Analysis Tool

The tool opens with the following information, which is important to review before diving in. After thoroughly absorbing the Refresh instructions and Helpful Tips, you can delete the info box and then click File > Save so that it does not appear again.  It'll always be here on the Support site for you to refer back to you.

With those tips in mind, let’s start:

  1. Refresh the workbook (Home > Worksheet Operations > Refresh Workbook)
  2. Refresh all pivot tables (Right-click on the table > Refresh)
  3. This is not Advanced Analytics; we are giving you all the raw data from the Inventory Audit Report. These are simple table dumps to get you started, but note that this tool will be overwritten when your database is upgraded.

 

Breaking Down Each Default Tab

There are 7 standard tabs you’ll start with in the Inventory Analysis Tool.

Note: If you're just looking to review the Inventory Audit Report in an Excel format, the Transactions by Day tab is the one to focus on.

 

Current On-Hand

What was missing from the Inventory Audit Report and Inventory in Warehouse reports was the ability to slice and dice the data. With this tab, you are looking at what you have right now, the value, the number of times you go through that item in a year (turns) and the average weeks you have on hand. 

Filter this chart by:

  • Item Group
  • Warehouse
  • Brand
  • Item

 

Stock in inventory as of the time of the last refresh.

We’ve provided the 2 most common measurements of qualifying inventory stock levels, with columns for both ‘Yearly Turns’ and ‘Average Stock (weeks)’ – number of week’s supply.

__________________________________________________________________________________________

 

Value by WH (Warehouse)

If you are looking to drill into how much the value of inventory was in a particular warehouse on a particular day(s), then look no further than this tab.

Filter by:

  • Warehouse
  • Item Group
  • Brand
  • Item

Use the slider at the Top to define the date range you want to analyze.

This allows you to see the current dollar value on hand in each warehouse over a period in time. When ran for a range of dates, you can see how the value fluctuates throughout the time period.

If you know a little bit of pivot-tabling skills, move the columns & sums around to get right to the data that you need!

______________________________________________________________

 

Transactions by Day

This tab is the most similar to the Inventory Audit Report in Orchestrated. It gives you the starting point for reviewing how inventory transactions have affected your warehouses on a day-to-day basis. This pivot-table view is excellent at tracking down those squirrelly balance sheet numbers at the end of the account period.

Use the timeline slider at the top to define the posting date range you wish to analyze, then use the buttons along the side to define the filters for:

  • Item Group
  • Warehouse
  • Brand
  • Item

  

For any item you can see all the inventory transactions on that item for a selected time period.

Especially useful for researching anomalies where you need to review all the transactions on a particular item.

______________________________________________________________

 

Transactions by Type

This tab is a great way to see a list of Goods Issues, Receipts, Invoices, etc. You can filter by a number of different ways, then see what documents affected your inventory numbers. 

Use the Posting Date slider along the top to define your date range then use the buttons along the side to filter for:

  • Item Group
  • Warehouse
  • Brand
  • Item

Additionally, you can filter for various document types!

 

Similar to the Transaction by Day tab (above), but allows you to see and filter by the document type that created each inventory change.

__________________________________________________________________________________________

 

Counting Adjustments

Inventory variance is one of the most important metrics for a procurement person to have. This chart helps you look at the ups & downs of inventory counts over a period of time. If you have massive inventory count variances, its usually a good candidate for a process improvement somewhere. 

Use the slider at the top to define your range then set the buttons along the side to filter for:

  • Item Group
  • Warehouse
  • Brand
  • Item

Only looks at inventory counts and their adjustments.

Useful for analyzing your counting program over time to spot trends.  Always adjusting in the same direction indicates different fixes than items that are being adjusted in both directions (yo-yo adjusts) on a regular basis.

__________________________________________________________________________________________

 

Item Cost Chart

A nifty chart displaying the various changes in cost of your items over a given period of time. Are you trying to track the costs of your malts? Super easy! Click the Item Group: Packaging button and then the button for your main warehouse (usually A1). 

The slider at the top helps you define the period of time you wish analyze. Filter buttons are available for the following:

  • Item Group
  • Warehouse
  • Brand
  • Item 

This represents the average inventory cost per unit.

It can indicate where manufacturing costs are trending, or if purchase item prices are going up or down.

______________________________________________________________

 

Inventory Value Chart

This handy dandy chart displays the value of your inventory over time. This is incredibly useful when analyzing the levels of inventory at your brewery or distillery. Use the slider at the top to define the date range you wish to analyze. 

The buttons along the side let you filter by:

  • Item Group
  • Warehouse
  • Brand
  • Item

Wish to see the value of all your finished goods in A1? Simply click the buttons for 'FinGood: Bottled' & 'FinGood: Kegged' & Warehouse: A1!

Graphical representation of value and quantity over time.

Shows whether overall levels are trending up or down.

 

Version 4.5.1.0

 

 

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments