Skip to main content

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)/2

The traditional method of averaging inventory values

(
Beginning OH (for first month in the selected date range)
+
Ending OH (for last selected month)
)
/ 2

Avg Cost/Qty OH (Beg+End)/Mo

averages each month’s beginning and ending balances, then averages that over the selected date range

(
The sum of all Beginning OH’s (for every month in the selected date range)
+
The sum of all Ending OH’s (for every month in the selected date range)
)
/2
/ number of months in the date range

Avg Cost/Qty OH (Sum)/Mo

Sum of all monthly ending balances (in the selected date range)
/ number of months in the 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 [Avg Cost OH (Sum)/Mo] instead of [Avg Cost OH (Beg+End)/2].

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.

Related Pages

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.