IN On Hand History Template
Also known as Inventory On Hand History. Template creates Tableau data source named _IN_On_Hand_History.
Overview
This data source reports the monthly beginning and ending inventory quantity and cost on hand for every item by every storage location (warehouse, location, site, facility, bin, etc). It also reports the monthly total Cost of Sales and overall net changes to inventory values. Together, these form the basis of average on hand calculations and turns.
Reports can include trends across the months (or groups of months), showing on hand (or average on hand if multiple months) and turns. Individual report values can be for one item at one location, or the totals and averages for summed groups of items.
Average On Hand
The data source provides a variety of methods for calculating the average quantity and cost on hand values. Be aware that all averages are based on monthly beginning and/or ending values. For the sake of data processing times and report performance on hand averages are not based on the daily fluctuations during the month.
The default (out of the box) [Avg Cost OH] is the traditional formula for average on hand over a time period:
(Beginning Inventory + Ending Inventory)/2
Additional averaging variations are in the Work: Base Formulas folder at the top of the data source. See Measure Definitions below.
Turns
Default: [Inventory Turns] for a selected time period are: SUM([Cost Of Sales] / [Avg Cost OH].
By default, [Avg Cost OH] is the traditional [Avg Cost OH (Beg+End)/2].
Optional alternative: [Monthly Turns By Avg Month End] (in the Work: Base Formulas folder) uses [Avg Cost OH (Sum)/Mo] instead of [Avg Cost OH (Beg+End)/2].
Customizing Turns
The Turns calculation can be edited to use one of other averaging methods (Use Tableau Desktop to edit the data source, which will affect all reports using the edited measures OR while in report editing mode create another calculation which will be available for only that workbook) .
Editing [Inventory Turns] in the Tableau data source in Tableau Desktop
Edit [Avg Cost OH] to select a different averaging method. This is the measure used in [Inventory Turns].
Also edit [Avg Cost OH] to the same method for the sake of consistency.
Creating a new measure
Use [Inventory Turns] as a model for creating a different Turns calculation.
Measures Definitions
Measure | Meaning |
|---|---|
Avg Cost/Qty OH (Beg+End)/2The traditional method of averaging inventory values | ( |
Avg Cost/Qty OH (Beg+End)/Moaverages each month’s beginning and ending balances, then averages that over the selected date range | ( |
Avg Cost/Qty OH (Sum)/Mo | Sum of all monthly ending balances (in the selected date range) |
Avg Monthly Cost/Qty Sold | Sum of Sales / number of months |
Beginning Cost/Qty OH | The On Hand at the beginning of the selected date range |
Beginning Cost/Qty OH - Month | The OH at the beginning of every month in the selected date range. Using this in a column would sum all the months in that column. |
Cost/Qty OH | On Hand at the end of the selected date range. |
Cost/Qty OH - Month | On Hand at the end of every month in the selected date range. Using this in a column would sum all the months in that column. |
Cost of Sales / Qty Sold | The monthly COGS or Qty, which will sum across the selected date range. Multiplied by -1 so that it displays as positive number. |
Cost/Qty Tran | The net of all transactions, which will sum across the selected date range. |
Monthly Turns By Avg Month End | Alternative turn: uses |
Number of Months | Count of the months in the selected date range. Used in averaging calculations. |
Source of Inventory Data
Consult your DataSelf provider for more information about where your ERP stores its inventory history data. Some ERP’s have sales totals included in their monthly inventory history summary table.
But for ERP’s without monthly sales already summarized, DataSelf sums the sales type records from the inventory transaction history. For these ERP’s the SQL table zLists_ERPName is a manually-editable list assigning inventory transaction types to sales and other groupings.