This report runs during the End of Day (EOD) process. You can also run it manually via the Replenish Stock Inventory Based on Sales Rate routine.
During EOD processing, the system examines:
all records in the Advanced Vendor Settings for which the Generate Automatic POs field is active, and then examines
the Build POs field for each vendor to determine if the Automatic PO Replenishment process should occur on that day.
For all eligible vendors, the process selects and reviews product records to determine the order quantity for each product. If the Automatically Hold POs field is selected for the vendor (in the Advanced Vendor Settings), EOD generates a report showing only the detailed calculations with a notation
Purchase Order Number: On Hold
If Automatically Hold POs is not selected, the report creates one purchase order for each vendor for each warehouse for items with a Quantity to Order greater than zero. In this case, the Purchase Order Number field on the report displays a valid purchase order number.
Minimum Weeks Supply (Minimum Stock Days/7) – This number is calculated by taking the minimum stock days from the Advanced Vendor Settings and dividing by 7. For example, assume the minimum stock days is set to 5. The system divides by 7 (that is, one week), equaling .71 weeks.
4-Week Rate – Assume the Weekly Sales Rate Calculation field in the Advanced Vendor Settings is set to 4 weeks. The program calculates the sales rate based on the number of units delivered during a 4-week period. Assuming the total units sold is 8, the program divides 8 by 4, equaling 2 (meaning an average of two were sold per week).
Sales Rate = Total Units Sold (Written or Delivered)/Number of Weeks.
Units Required - This is a calculated field that determines the number of units needed to satisfy normal sales.
Units Required = Minimum Weeks Supply * Sales Rate
In our example, .71*2 = 1.42 rounded up to 2 units
Units Avail – This field represents the units available for sale (at warehouse). This can also include store locations, depending the Include Store Stock Availability field in the Purchasing Control Settings.
Units Avail = Total Units On-Hand – Quantity Committed
In our example, we have 1 on hand and 1 committed, so units available = 0
Net PO – This is a calculated field that displays the net purchase order.
In this example, no open purchase orders exist and 1 sale is uncommitted. Therefore, 0-1 = -1.
Lead Days – The column references the Purchase Lead Days field in the Advanced Vendor Settings.
Addl Required – This is a calculated field that displays the needs for this item based on the purchase lead days. If the item takes a long time to receive from the vendor, then it may be necessary to order it now. Therefore, 14/7*2 + (.05) rounded up = 5
Addl Required = Lead days/7 * Sales Rate + (.05)
Quantity to Order – This column displays the quantity ordered by the Automatic PO Replenishment Process. Therefore, 2 + 5 – 0 – (-1) = 8
Qty to Order = Units Required + Addl Units Required – Units Avail- Net PO
4 Month Avg Unit – This column represents the average number of units sold per month during the number of months defined. This number displays for informational purposes only and is not used for any calculation. In this example, 4 months is defined in the First Average Units Period in the Advanced Vendor Settings. It includes the 4 months prior, not including the current month, and is based on either written or delivered numbers defined in the Purchasing Control Settings.
12 Month Avg Unit – This column represents the average number of units sold per month during the number of months defined. This number displays for informational purposes only and is not used for any calculation. In this example, 12 months is defined in the Second Average Units Period in the Advanced Vendor Settings. It includes the 12 months prior, not including the current month, and is based on either written or delivered numbers defined in the Purchasing Control Settings.
GMROI – This column shows the Gross Margin Return on Investment (in dollars), including the current month’s sales. If less than a year’s history is available, the formula calculates based on the available history.
GMROI = Total Year’s Gross Profit $/ (Year’s Avg Units on Hand * Year’s Avg Unit Cost $)
Total Year’s Gross Profit $ = Year’s sales dollars – Year’s cost dollars.
Year’s Avg Units on Hand = Sum of avg units on hand from each month/number of months available history (up to 1 year).
Year’s Avg Unit Cost $ = Sum of the costs of goods sold for the period of history of available (up to 1 year)/number of units sold for that same period.
Turns – This column shows the inventory turnover ratio. It includes the current month’s sales. If less than a year’s history is available, the formula be calculates based on the available history.
Turns = Year’s Cost of Goods Sold $/ (Year’s Avg Units on Hand * Year’s Avg Unit Cost $)
Year’s Cost of Goods Sold $ = Sum of the cost of merchandise sold over the period of available history (up to 1 year)
Year’s Avg Units on Hand = Sum of avg units on hand from each month/number of months available history (up to 1 year).
Year’s Avg Unit Cost $ = Sum of the costs of goods sold for the period of history of available (up to 1 year)/number of units sold for that same period.