Skip to main content
Skip table of contents

Inventory Planning

Prevent inventory shortages. Projecting future stocking based on known and projected stock movements.

To see sample workbook, Click Here Then select:

Data Sources

This is a list of the data sources used to calculate inventory projections.

Qty on Hand Detailed Projection

Qty OH Today

Shows in Today’s date column

Qty On PO

by PO Exp date (Customize to include Mfg or other increase types)
(Customize as to what date to report overdue PO’s)

Qty On SO

by SO Exp Date (Customize to include Mfg or other issue types)
(Customize as to what date to report overdue SO’s)

Qty Proj Sales

Historical sales are pushed forward per customized spec's, then multiplied by the [Proj Sales Growth] parameter for the Projected Sales

Proj Outbound

Greater of [Qty On SO] or [Qty Proj Sales]

Proj Net Change

[Qty OH Today] + [Qty On PO] - [Proj Outbound]

Proj Qty OH

RUNNING_SUM of [Proj Net Change]
(Adds across the dates, per row)

Safety Stock

safety stock or Min OH from ERP. Same number in all date columns.
OR suggested: {fixed [Item ID]:([Proj Outbound])}*0.1

Proj Excess/Short

[Proj Qty OH]-[Safety Stock]

Table calculation

Proj Excess/Short Filter

(color filter)

if [Proj Excess/Short]>=0 then 'Excess' else 'Short' END

Demand

(first column)

Per Item, for the total [Qty Proj Sales] within the selected [Exp Dates] range:
>=1000 then 'High'
elseif >=100 then 'Mid'
elseif >=1 then 'Low'
else 'None'

Tech note: [Exp Dates] is a Context filter, which limits the LOD to the selected dates.

Qty on Hand Projection

Summary of Qty on Hand Detailed Projection

Days of Stock

Days of Stock

if sum([Qty OH Today]) <= 0 and sum([Qty Proj Sales])>0 then -9999999
elseif sum([Qty Proj Sales]) = 0 then 9999999
else sum([Qty OH Today])/(sum([Qty Proj Sales])/max([No of Days in Period])) end

Other reports

are from the regular Sales_Invoice, Sales_Order, Purchase_Order, IN_On_Hand_Today data sources

Common Customizations

  • Reporting date ranges (weeks? months? days? etc)

  • Report how far into the future?

  • How far back to pull the Sales history, how far forward to push it

  • What about late PO’s and SO’s? Lump them all into Today, along with [Qty OH Today]? Push them a certain number of days into the future? Continue to exclude them, but add highlight the late ones in another tab?

Related Pages

Keys: IN_On_Hand_History, Inventory Planning Workbook

JavaScript errors detected

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

If this problem persists, please contact our support.