While Sage 500 ERP and other ERP systems generally provide several options (such as running Inventory Replenishment or MRP) for determining suggested replenishment quantities, they frequently lack the visual feedback that might help you set replenishment priorities more quickly and accurately.
Many ERP systems also do not always make it easy or convenient to see date-priorities related to the sources of the demand to which it is responding (such as the customer order dates or promised or scheduled ship dates).
The spreadsheet below will help you to set replenishment priorities. It is from a Microsoft Excel workbook connected directly to a SQL data view, so no export-import is required and the data can be refreshed every time the workbook is opened (or, on demand).
Using Excel’s native capabilities, the spreadsheet can be filtered by:
- Warehouse ID
- Item ID
- Delivery Method (e.g., Ship, Will Call)
- Ship Priority
- Order Date or Ship Date (including Month, for example)
- Quantity Short (so you only need to look at the data where you are short quantities to meet current demand)
Rows are highlighted in red where the quantity short is less than existing replenishment from purchase orders, inbound transfer orders (net), and production work orders (net).
In the example given, the supply chain manager can easily see (top three rows) that three items from sales order number 28191 are short. Two are out of stock complete, while a third has an open order for 50 units with only 19 units available. Purchase orders should be expedited for Item IDs ‘ENCNWLA’ and ‘SCM’, while the existing Work Order (WO) for Item ID ‘SCS’ should be expedited to produce the 161 units to meet the demand for this sales order.
Of course, resorting or changing the filters on the spreadsheet would allow the supply chain manager to see demand for any one item across all dates or all warehouse, or both simultaneously.
The nice thing about using Excel in this way is that 1) almost every company already owns the software, so there is not extra expense to acquire the tool, and 2) almost every user either knows how to use Excel functions to manipulate data, or can be readily taught to do so (frequently by in-house personnel or by buying a book at any good bookstore).
Here is the SQL script to create the SQL data view used for this example:
if object_ID(N'dbo.vdvInventoryPriorities_RKL','V') is not null
drop view dbo.vdvInventoryPriorities_RKL
go
create view dbo.vdvInventoryPriorities_RKL
as
select i.CompanyID
, so.TranID
, so.TranDate
, case i.ItemType
when 1 then 'Misc Item'
when 2 then 'Service'
when 3 then 'Expense'
when 4 then 'Comment Only'
when 5 then 'Finished Good'
when 6 then 'Raw Material'
when 7 then 'BTO Kit'
when 8 then 'Assembled Kit'
end 'ItemType'
, coalesce(ppl.PurchProdLineID,'None') 'PurchProdLine'
, w.WhseID
, i.ItemID
, case sold.DeliveryMeth
when 1 then 'Ship'
when 2 then 'Drop Ship'
when 3 then 'Counter Sale'
when 4 then 'Will Call'
end 'DeliveryMeth'
, sold.ShipPriority
, sold.ShipDate
, sold.QtyOpenToShip
, um.UnitMeasID
, case
when qa.QtyAvail < 0 then 0
else qa.QtyAvail
end 'QtyAvail'
, dbo.fnIMItemUOMConv
( sol.ItemKey
, iv.QtyOnPO
, i.StockUnitMeasKey
, sol.UnitMeasKey ) 'QtyOnPO'
, dbo.fnIMItemUOMConv
( sol.ItemKey
, iv.QtyOnTrnsfr
, i.StockUnitMeasKey
, sol.UnitMeasKey
) 'QtyOnTrnsfr'
, dbo.fnIMItemUOMConv
( sol.ItemKey
, iv.QtyOnWO
, i.StockUnitMeasKey
, sol.UnitMeasKey
) 'QtyOnWO'
, dbo.fnIMItemUOMConv
( sol.ItemKey
, iv.QtyReqForTrnsfr
, i.StockUnitMeasKey
, sol.UnitMeasKey
) 'QtyReqForTrnsfr'
, dbo.fnIMItemUOMConv
( sol.ItemKey
, iv.QtyReqForWO
, i.StockUnitMeasKey
, sol.UnitMeasKey
) 'QtyReqForWO'
from dbo.tsoSOLine sol with (nolock)
join dbo.tsoSOLineDist sold with (nolock)
on sol.SOLineKey = sold.SOLineKey
and sold.QtyOpenToShip > 0
join dbo.timInventory iv with (nolock)
on sol.ItemKey = iv.ItemKey
and sold.WhseKey = iv.WhseKey
join dbo.timItem i with (nolock)
on sol.ItemKey = i.ItemKey
and i.ItemType in (5,6,8) -- No BTO Kits or Non-Inventory Types
left outer join dbo.timPurchProdLine ppl
on i.PurchProdLineKey = ppl.PurchProdLineKey
join dbo.tsoSalesOrder so with (nolock)
on sol.SOKey = so.SOKey
and so.[Status] = 1 -- Open
join dbo.tciUnitMeasure um with (nolock)
on sol.UnitMeasKey = um.UnitMeasKey
join dbo.timWarehouse w with (nolock)
on sold.WhseKey = w.WhseKey
join ( select sum( dbo.fnIMGetBinQtyAvailable
( iv.ItemKey
, wb.WhseBinKey
, sol1.UnitMeasKey
, so1.TranType )
) 'QtyAvail'
, sol1.SOLineKey
from dbo.timInventory iv with (nolock)
join dbo.timWhseBin wb with (nolock)
on iv.WhseKey = wb.WhseKey
join dbo.tsoSOLineDist sold1 with (nolock)
on wb.WhseKey = sold1.WhseKey
join dbo.tsoSOLine sol1 with (nolock)
on sold1.SOLineKey = sol1.SOLineKey
and sol1.ItemKey = iv.ItemKey
join dbo.tsoSalesOrder so1 with (nolock)
on sol1.SOKey = so1.SOKey
group by sol1.SOLineKey
) qa
on qa.SOLineKey = sol.SOLineKey
where sol.[Status] = 1 -- Open
and so.TranType = 801 -- Sales Orders only, no Quotes
and sold.DeliveryMeth in (1,4) -- Shipments and WillCalls only
--and sold.QtyOpenToShip > qa.QtyAvail
go
grant select, references on dbo.vdvInventoryPriorities_RKL to public
grant select, references on dbo.vdvInventoryPriorities_RKL to ApplicationDBRole
go
Additional Enhancements
Much more could be done with this concept. One could aggregate demand rather than show it by Sales Order. One could display the Purchase Produce Line (PPL) in the spreadsheet so that sorting or filtering by PPL could be readily done. Dollar amounts could be added to show the revenues or Throughput being delayed by these stock shortages.
These are just a few of the tools at your disposal when leveraging Microsoft Excel with Sage 500 or other ERP databases to provide insights into your enterprise that may not be available readily in the out-of-the-box solution.
We can help you get there, if you need assistance. Please feel free to contact us when you are ready to reap the benefits if truly low-cost business intelligence opportunities through Microsoft SQL Server and Microsoft Excel working together.
