RKL eSolutions Blog Trends and Insights

Using Excel with Sage 500 to View Supply Chain Replenishment Priorities

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).

CUSTOM Inventory Replenishment Priorities spreadsheet

Using Excel’s native capabilities, the spreadsheet can be filtered by:

  1. Warehouse ID
  2. Item ID
  3. Delivery Method (e.g., Ship, Will Call)
  4. Ship Priority
  5. Order Date or Ship Date (including Month, for example)
  6. 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.

RKL Team

Written by RKL Team

Since 2001, RKL eSolutions has helped growing companies maximize their technology resources and investment. Over the years, we have helped hundreds of small and medium sized businesses as their strategic business partner. We specialize in the needs of Entertainment, Software & SaaS, Professional Services, Manufacturing, and Non Profit organizations. Our experienced consultants have a passion for making every facet of your business successful and are intent on building a long-term relationship with every client.