Calculating Available Quantities And Costs in Sage MAS 90/200 (becoming Sage 100)
Clients often ask us how the Sage ERP MAS 90 (soon becoming Sage 100) Inventory Management module calculates costs and quantities available. Here we provide the explanation.
Calculating Quantity Available
Here we describe the calculation used for printing the quantity available on Inventory Management and Bill of Materials module reports. The calculation depends on the selections in the Include in Quantity Available in the Inventory Management Options window.
- Due to the differences in vendor lead times from business to business, the quantity on purchase orders can be used when calculating the quantity available. To include quantity on purchase orders in the quantity available calculation, select Purchase Orders.
- Due to the differences in manufacturing lead times, quantity on work order can be used in the quantity available calculation. To include quantity on work orders and quantity required for work orders in the quantity available calculation, select Work Orders.
- To include both quantity on purchase orders, work orders, and quantity required for work orders in the quantity available calculation, select POs and WOs.
- If you prefer that the quantity available calculation not to be considered in either purchase orders or work orders, select No POs and WOs.
The quantity available is calculated using:
[Quantity on Hand + Quantity on Purchase Order + Quantity on Work Order] – [Quantity on Sales Order + Quantity on Back Order + Quantity Required for Work Order].
Inventory Item Cost Hierarchy
The valuation method of an item affects the order in which each type of cost is used in the Sales Order, Purchase Order, and Inventory modules. Below we describe the hierarchy used for each valuation or costing method.
Standard Valuation Cost Hierarchy
The standard cost from the Inventory Masterfile is the first value used for unit cost. If the standard cost is zero, the unit cost defaults to the last cost. If the last cost is zero, the unit cost defaults to the warehouse average cost. If the warehouse average cost is zero, the unit cost defaults to the item average cost.
Average Valuation Cost Hierarchy
The warehouse average cost from the Inventory Masterfile is the first value checked for unit cost. If the warehouse average cost is zero, the unit cost defaults to the item average cost. If the item average cost is zero, the unit cost defaults to the last cost. If the last cost is zero, the unit cost defaults to the standard cost.
FIFO/LIFO/Lot/Serial Valuation Cost Hierarchy
For purchases, the vendor last cost is the first value checked for unit cost. If there is no vendor last cost, the last cost from the item costing file is used. If the last cost is zero, the unit cost defaults to the standard cost. If the standard cost is zero, the unit cost defaults to the warehouse average cost. If the warehouse average cost is zero, the unit cost defaults to the item average cost. Once goods are received, the cost is recorded in the FIFO/LIFO/LOT/Serial cost tier, and the specific cost tier value is used in all transactions.
Other Considerations
In the Sales Order module, the determination of what cost to apply is calculated during the Sales Journal update. When using a pricing method that is a Markup Percentage or a Markup Amount and the standard cost is zero, the cost hierarchy is followed to determine the unit price.
In the Inventory module, the hierarchy determines the cost used in Inventory Transaction Entry for all transaction types.
In the Purchase Order module, the Auto Generate Orders Selection Window uses the following hierarchy when selecting Update Unit Cost With:
- Standard Default or Last Unit Cost: the Primary Vendor’s last cost is used.
- Standard Unit Cost: the item’s standard cost is used.
- Average Unit Cost: the item’s average cost is used.
- If any of the costs are zero for the above selections, the unit cost used follows the cost hierarchy based on the valuation methods listed.
Please call us with your questions.