Track Inventory levels against past Sales History
The Inventory v Sales Workbook reports on All Items in Inventory v Items that have sold over the past 12 months.
To help with reorders it compares the Qty sold Monthly to the:
- On hand
- - Committed (SO)
- = Available Now (On Hand Less committed on Sales Orders) New
- + On Order (PO)
- = Available Qty
- Monthly Sales Qty for up to 12 months
- Average Monthly Sales Qty (over Last 12 months)
- Number of weeks since item last sold
- Number of weeks since item was last purchased
This Article covers the following:
- Run the Workbook Inventory v Sales
- Manage Inventory better - Ensure Purchase Orders Issued
- Edit to see a longer or shorter period of Sales History (up to 12 Months or last 6 weekly)- Add more Columns
- Track Current Inventory levels against past Seasonal Demand
- Check Inventory against Minimum Stock Levels
- Identify Inventory that is Not Selling
- Identify Inventory that has Not been Purchased recently
-
Run the Workbook Inventory v Sales
The report tab Sales History by Item shows Qty Sold for:
- This Month
- Each of the Last 3 months - Mth-1, Mth-2, Mth-3
- Total for the Last 12 Months - 12 Mth Total
- Average for the Last 12 Months (12 Mth Total/12) - 12 Mth Avg
- No. weeks since item was Last Sold - Last Sold Wks
- No. weeks since item was Last Purchased - Last Purchased Wks
-
Manage Inventory better - Ensure Purchase Orders Issued
Manage Inventory levels by ensuring Purchase Orders are placed to meet all Sales Orders:
- Available Now (On hand minus Committed on Sales Orders); &
- Available Inventory (including On PO).
-
Edit to see a longer or shorter period of Sales History (up to 12 Months or last 6 weekly) - Add more Columns
- Click on the Edit Icon to access the Report Designer (Only available when in Edit Mode)
- Add a new Mapping Field - e.g. extend to see up to 6 months history
- Drag and drop Fields reorder the Columns
The report now shows 6 months past Sales History
-
Track Current Inventory levels against past Seasonal Demand
If your business is Seasonal then recent sales history is not so useful.
What you may want to see is what sold last year, for the upcoming season.
To achieve this you can set the Sales to pick up Mth-12, Mth-11, Mth-10 and Mth-9. This will show the Sales for this time last year and the next 3 months, last year.
-
Check Inventory against Minimum Stock Levels
For core products that you never want to run out of set Minimum Stock levels:
- Click on the Edit Icon to access the Report Designer (Only available when in Edit Mode)
- Add a new Mapping Field - e.g. Min Level, ReOrder Qty, SOH Below minimum
- Drag and drop Fields to reorder the Columns
- Filter to only see Items where the SOH is Below Minimum is negative
- Save the report to auto email as an Alert (Enterprise version) -
Identify Inventory that has Not Sold recently
Filter on the Last Sold Weeks column to focus on inventory that has not sold for X weeks
6.1 Slow by OH Cost Report
See the prebuilt Slow by OH Cost worksheet. this is Filtered by
- Inventory On Hand is >0; and
- Last Sold Weeks is between 15 and 9995 weeks
6.2 Apply Last Sold Weeks to other Reports
Set a filter to see where Items were say Last Sold > 15 weeks ago.
Note: Recommend you also remove the Item is Active Filter to see all Inventory on hand.
-
Identify Inventory that has Not been Purchased recently
Filter on the Last Purchased Weeks column to focus on inventory that has not been purchased for over say 13 weeks. It could be time to re-order.
-
Low Inventory Alert - Know when to Reorder
Use Expressions to calculate your own Low Inventory Alert warnings.
See the Low Inventory worksheet, we have displayed the Last 6 Weeks Sales as a column, then added a new calculated Expression (formula) that calculates a Low Inventory Alert column based on where:
- Available Qty minus Last 6 Weeks Sales
- Filtered for where this is negative
See the Inventory Cover report for more advanced Low inventory analysis
-
Save your Workbook to re-use again