Versions Compared


  • This line was added.
  • This line was removed.
  • Formatting was changed.

Prevent inventory shortages. Projecting future stocking based on known and projected stock movements. Also known as IN On Hand History Template and IN_On_Hand_History.

To see sample workbook, Click Here Then select:

Data Sources

This is a list of the data sources used to calculate inventory projections.For more see IN On Hand History Template

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


(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


Covered in IN On Hand History Template

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