Simple way to monitor Rack and/or Bin progress in Excel during a Syspro Stock Take
Generally, I found that it is difficult to answer the question: “How far are we with the stock take?”
The most commonly used, quick-and-dirty measures are to
- Look at the bottom-line value (or unit) variance on your Syspro or custom report. (Least accurate method)
- Count the number of StockCodes counted (captured) vs. Total number of StockCodes (in the stock take file)
- Count the number of Bins counted (captured) vs. Total number of Bins in the Stock Take file (or at least, the ones with QtyOnHand > 0)
But none of these measures provide an indication of how much of the physical warehouse (space) has been covered.
The following method might be helpful, assuming you make use of Syspro’s MultiBin functionality:
For the purpose of explanation, let’s assume you have (as shown in Figure 1 below):
- 2 x Racks, numbered ‘1’ and ‘2’
- each Rack is 3 x levels high, numbered (level) ‘1’, ‘2’ and ‘3’
- each Rack is 2 x Bins ‘deep’, numbered ‘A’ and ‘B’
Figure 1: Example of Warehouse Rack and Bin layout
So you have 12 x Bins in total, numbered (say) <Rack number><Level number><Depth>: 11A, 12A, 13A, 11B, 12B, 13B and 21A, 22A, 23A, 21B, 22B, 23B
Import data from Syspro into Excel using the following Query:
SELECT Bin, MAX(NumTimesCaptured) AS [Count] FROM dbo.InvStockTake
(one can add filters for specific Warehouses, etc.)
Use Excel’s VLOOKUP function – and Conditional Formatting – to set up the simple visualization below (Figure 2, query data in column A and B).
Figure 2: Excel example of monitoring Racks /Bins
The formula in (say) Cell F3 – which can be copied to the rest of the ‘Bin’ cells (columns F to H):
=IF(ISNA(VLOOKUP($D3&F$2&$E3,$A:$C,2,FALSE)),””,IF(VLOOKUP($D3&F$2&$E3,$A:$C,2,FALSE)=0,$D3&F$2&$E3,VLOOKUP($D3&F$2&$E3,$A:$C,2,FALSE)))
The visualization tells us the following:
- (At least one) StockCode in Bin ’11A’ has been counted once. I used Conditional formatting to show these Bins as ‘green’ or ‘ok’.
- Level 2 of Rack 1 has not been counted yet – or these bins are empty.
- (At least one) StockCode in Bin ’13A’ has been counted twice. This is handy if you’d like to see if a Bin was ‘double checked’ or if everything has to be double counted (e.g. two separate teams).
- ‘Column’ or Depth ‘B’ of Rack 2 has not been counted – or the whole ‘Column’ in Rack 2 is empty.
- Additionally, the Excel formula also caters for instances where the Bin does not exist in Syspro – these calls will be empty (blank). (Not shown in this example)
Hope it helps!
