Unfortunately, the calculation of the correct level of safety stock should be conditioned upon more than just lead time and average demand -- which are the limits of the standard approach to safety stock calculations. For example, what if the item is seasonal? What if the item has experienced unusual demand in the "average demand" calculation period? What if the item is subject to forecasts? The the accuracy of prior forecasts should be considered, as well. Yet another consideration is demand trends. Are sales at the facility increasing overall? Is the sales product line or purchase product line increasing or decreasing at a rate different than the overall trend at the facility.
Safety stock is specifically to cover variation in order to achieve specific levels of customer service; therefore, two goals should be held in mind and included in the calculation simultaneously: 1) achieving a specified level of customer service on the item and 2) minimizing inventory on-hand.
Good solutions for safety stock calculations should include replenishment factors that are dynamically maintained including:
1) Average lead time
2) Average daily demand based on working days (not calendar days or months)
3) Order cycle (for purchase product line)
4) Demand adjustments (for unusually high or low actual demand in "average demand" calculation look-back period)
5) Desired customer service level for item (SKU)
6) Trends
Few software solutions accomplish this task successfully.
Safety Stock Calcuations
Demand Deviation Formula for Safety Stock Calculations
There is an important point to note in using "Deviation" formulas in Sage MAS 500 to calculate Safety Stock. The "Deviations" here are the differences between your "Forecast Demand" and your "Actual Demand," and your "Forecast Demand" may include data that could skew the results (e.g., Demand Adjustments). Furthermore, any differences where "Forecast Demand" exceeds "Actual Demand" in any given period are omitted from the averaging process, so negative variations are not considered at all. (I assume that Sage took this approach because they do not use squared values -- as the standard deviation formula would -- which would eliminate negatives, and also, perhaps they deemed that if your Forecast exceeds your Actual Demand, you have covered your requirements.)
Note that a statistical "Standard Deviation" for a sample or population (in this case, one would use the sample formula) is calculated on the variance from the statistical mean of the sample data, so the MAS 500 "Deviation" is not a "Standard Deviation" in "Actual Demand." The result is that setting your "Multiplier" to the value of 2.0000 does not necessarily cover 2 standard deviations in "Actual Demand."
I set up a simulation to compare the results between the MAS 500 "Deviation" formula to calculate Safety Stock and a true standard deviation in "Actual Demand." In my particular simulation (with a look-back over 12 Periods), I found that the MAS 500 formula was slightly more conservative -- tending to estimate below the value suggested by the standard deviation in "Actual Demand" more often than not.
In general I would say that the MAS 500 formula approximates coverage of the Multiplier number of standard deviations in Actual Demand, but it is not precise. Much would depend on the set of data against which the calculations are run and this, of course, means that the results will vary for each Item in a MAS 500 company's inventory.
It may have been helpful if MAS 500 had employed standard deviations against Actual Demand so that, for example, when one selected 2.000 for a multiplier, one could assert that you are setting a 95.45% customer service level on Items that employ that particular Safety Stock Formula (in accordance with the following chart).
CONFIDENCE INTERVAL FACTORS for Various Multiples of Standard Deviations:
zσ = Percentage
=========================
1σ = 68.27%
1.645σ = 90%
1.960σ = 95%
2σ = 95.450%
2.576σ = 99%
3σ = 99.7300%
3.2906σ = 99.9%
4σ = 99.993666%
5σ = 99.99994267%
6σ = 99.9999998027%
7σ = 99.9999999997440%
=========================
I hope this is of some help.
Microsoft Warning of SQL Server Vulnerability
Microsoft has warned of a vulnerability in SQL Server 2000 and 2005 (various editions). Read more about it here.
There is a work-around provided in the Microsoft article to seal off the vulnerability.
MAS 500 1099 Processing Information
For those of you who may have missed the announcement direct from Sage, here it is:
|
With January 31st right around the corner, now is the time to prepare for 1099s. Sage wants your 1099 season to be a successful one. To help ensure this, please take a moment to review the following valuable 1099 processing information. 1099 printing and Electronic Reporting Magnetic Media for 2008 will be available on versions 6.3, 7.0, 7.05, and 7.2 -- prior versions are not supported. This year, we will be providing the 1099s as a separate install, as well as compiling them into a Monthly Update. If you download the standalone 1099 update, you must have applied the December 2007 Monthly Update or Service Pack first. The standalone download will be available towards the end of December, with the Monthly Update available the first week of January via our website at http://support.sagesoftwareonline.com/mas500/support/updates/updates.cfm. Forms MAS 500 supports the following forms: 1099-DIV, 1099-INT, and 1099-MISC. Sage MAS 500 prints 2 vendors or individuals on the 1099-DIV form, 1099-INT form, and 1099-MISC form. If using a local forms provider, please be sure to order forms with the tear-off strip on the right and be prepared to make adjustments to the form in Crystal Reports. Sage supports only LaserJet printing of 1099 forms. You may purchase the appropriate 1099 forms from Sage’s preferred forms provider, Altec. Contact Altec at (800) 765-7751. Electronic Reporting (Magnetic Media)
Entering Vendor 1099 Beginning Balances Use 1099 beginning balances to establish 1099 information tracked outside of MAS 500. This is especially useful when a company implements Sage MAS 500 in the middle of a calendar year. Beginning balance can be entered manually or utilizing the beginning balance import function. Editing or Correcting 1099s BEFORE filing with the IRS Sage MAS 500 enables you to edit or make corrections to 1099 vendor information and amounts prior to releasing or submitting information to the IRS via the 1099 Forms task. You can edit 1099 data before submitting records, as well as un-submit records so that pre-submission corrections can be made. Editing or Correcting 1099s AFTER filing with the IRS Using the IRS 1099 Corrections task, you can enter and flag corrections requested by the IRS. This task is used to make corrections only if you receive notification from the IRS that filing a correction is necessary. Some types of corrections requested by the IRS require submission of both the original (incorrect) 1099 form and the corrected 1099 form. After corrections have been made, you can then re-submit the corrected 1099 forms and original 1099 forms, if required. The correction types that need both the original and corrected copies of the 1099 forms are automatically printed together during the printing process. Additional Information Please refer to Sage MAS 500 Online Help for additional information on processing 1099s. |
© 2008 Sage Software, Inc. All rights reserved. The Sage Software logo and the Sage Software product and service names mentioned herein are registered trademarks or trademarks of Sage Software, Inc, or its affiliated entities. All other trademarks are the property of their respective owners. |
MAS500 Items and their Units of Measure
I've had frequent questions from people trying to build their own views for accessing MAS500 Item information so I thought I'd put it all down in a public place so it can be referred to easily and perhaps assist others that I don't normally work with.
Working with Units of Measure in MAS500 can be a challenge as finding what you see on the screen, in the tables behind the scene is not always cut and dried. Granted, Sage has added numerous Explorer views that make reporting much easier, these views often come with many other tables included and thus lots of extra baggage. So in this discussion, we'll talk about linking to get UOM ID's the quick and low bandwidth way.
In the screen above I've highlighted the section of the Maintain Items screen that shows the Unit of Measure. The section in Red is stored in the timItem table of the MAS500 database. The section in Blue is stored in thetimItemUnitofMeas table.
What you will find in these two tables is not going to be the Unit of Measure text (UOMID) like you see it on the screen. Instead you will find in these tables a column with some type of UOMKey reference. To get the UOMID that you see on the screen, you will need to also work with the table tciUnitMeasure.
The table timItem contains the following UOM fields (easy to find in the schema browser by finding the timItem table and then choosing the Keys link. Any keys that link to tciUnitMeasure.UnitMeasKey are units of measure):
PriceUnitMeasKey - Pricing Unit of Measure
PurchUnitMeasKey - Purchase Unit of Measure
SalesUnitMeasKey - Sales Unit of Measure
StockUnitMeasKey - Stock Unit of Measure
Notice how you have one "key" column for each of the ID fields show in the redbox above.
To get the UOMID for any of the timItem fields, you will need to create a query that joins into the tciUnitMeasure table. For example, if we want to display the ItemID, and the Stocking Unit of Measure ID for an item, we might create a query that looks like this:
SELECT item.CompanyID, item.ItemID , UOM.UnitMeasID AS 'StockUOMID'
FROM dbo.timItem Item
INNER JOIN dbo.tciUnitMeasure uom
ON Item.StockUnitMeasKey = uom.UnitMeasKey
In this case we are joining into tciUnitMeasure on the Item's StockUnitMeasKey and the Unit of Measure table's UnitMeasKey. We can use an INNER JOIN here because MAS500 requires all items to have a Stock UOM entered. If we are going to look at the Purchasing or Sales UOM, we may want to use an OUTER JOIN as not all Item Types require a Purchasing or Sales UOM to be entered. Thus if you wanted to create a report or Explorer view that shows all Items, and if the have it, a Price UOM you might have a query that looks like this:SELECT item.CompanyID
, item.ItemID
, itemTypes.LocalText AS 'ItemType'
, descr.ShortDesc AS 'Description'
, UOM.UnitMeasID AS 'PriceUOMID'
FROM dbo.timItem Item
INNER JOIN dbo.timItemDescription Descr
ON Item.itemkey = Descr.ItemKey
LEFT JOIN dbo.vListValidationString itemTypes
ON Item.ItemType = itemTypes.DBValue
AND itemTypes.TableName = 'timitem'
AND itemTypes.ColumnName = 'ItemType'
LEFT JOIN dbo.tciUnitMeasure uom
ON Item.PriceUnitMeasKey = uom.UnitMeasKey,
When fetching data that refers to rows in the lower section of the Maintain Items screen (shown in blue) you will need to join in an addtional table. The table timItemUnitOfMeas contains one row for each row shown on the grid. This table contains a column called TargetUnitMeasKey which represents the TargetUOM ID shown on the screen. This table also contains the ConversionFactor, the Unit Volumn, Unit Weight, UPC code, and flags for if it can be used for Purchases or Sales.If you wanted to list all the Units of Measure from this table for an item you would create a query that looks like this:
SELECT Item.CompanyID
, Item.itemid
, uom.UnitMeasID AS 'TargetUOM ID'
, iUOM.UnitWeight AS 'Unit Weight'
, iUOM.UPC AS 'UPC Code'
FROM dbo.timItem Item
LEFT JOIN dbo.timItemUnitOfMeas iUOM
ON Item.itemkey = iUOM.itemkey
LEFT JOIN dbo.tciUnitMeasure uom
ON iUOM.TargetUnitMeasKey = uom.UnitMeasKey
Here we are using Left Joins since many items, Misc Items for example, will not have any rows in the timItemUnitofMeas table.Finally, because many UOM columns are stored in one row in the timItem table, it is often necessary to join into the tciUnitofMeasure table multiple times. This is accomplished through creating what are called alias's for tables in your query. In the examples above, I used the alias "item" for the timItem table and "uom" for the tciUnitMeasure table. An alias allows you to reference the same table, in different ways inside a query.
The query below is an example of what might be created if you wanted to see ALL the UOM's assigned to an item, in this case, the item ID used in the screen capture above:
SELECT item.CompanyID
, item.ItemID
, itemTypes.LocalText AS 'ItemType'
, descr.ShortDesc AS 'Description'
, UOM.UnitMeasID AS 'PriceUOMID'
, StockUOM.UnitMeasID AS 'StockingUOMID'
, PurchUOM.UnitMeasID AS 'PurchaseUOMID'
, case
WHEN targetuom.UnitMeasID IS NULL THEN 'Nothing'
ELSE TargetUOM.UnitMeasID
END AS 'TargetUOMID'
, case
WHEN targetuom.UnitMeasID IS NULL THEN ''
ELSE iUOM.UnitWeight
END AS 'Weight per'
FROM dbo.timItem Item
INNER JOIN dbo.timItemDescription Descr
ON Item.itemkey = Descr.ItemKey
LEFT JOIN dbo.vListValidationString itemTypes
ON Item.ItemType = itemTypes.DBValue
AND itemTypes.TableName = 'timitem'
AND itemTypes.ColumnName = 'ItemType'
LEFT JOIN dbo.tciUnitMeasure uom
ON Item.PriceUnitMeasKey = uom.UnitMeasKey
LEFT JOIN dbo.timItemUnitOfMeas iUOM
ON Item.itemkey = iUOM.ItemKey
LEFT JOIN dbo.tciUnitMeasure TargetUOM
ON iUOM.TargetUnitMeasKey = TargetUOM.UnitMeasKey
LEFT JOIN dbo.tciUnitMeasure StockUOM
ON Item.StockUnitMeasKey = StockUOM.UnitMeasKey
LEFT JOIN dbo.tciUnitMeasure PurchUOM
ON Item.PurchUnitMeasKey = PurchUOM.UnitMeasKey
WHERE Item.itemid LIKE '2way%'
AND Item.CompanyID = 'SOA'
Note the use of Left Joins, and all of the joins into tciUnitMeasure. In the future I will try and discuss how to work with the Conversion Factor and how you might link into the tsoSOLine and tsoShipLine to figure out QTY.--Michael Harms
Understanding MAS 500 MRP v. IR
There are some critical differences between how MAS 500's MPR (material requirements planning) and IR (inventory replenishment) function. While both processes use the same underlying data and share many of the same algorithms, there are critical differences that users should understand.
One critical difference is that IR works from a "snapshot," whereas, MRP uses a time-phased approach to the analysis. IR takes a look at your current situation in order to try to determine what replenishment action should be taken at this time. MRP, on the other hand, places calculated data into a series of time-phased planning "buckets." The controlling factors for these MRP planning "buckets" are found (primarily) in the "Initialization Criteria" on the Options tab of the Material Requirements Planning Generation screen.
The range of dates for which an MRP version will plan is governed by the "Start Date" and the "Cut-off Date", but the size of each planning "bucket" is determined by the "Days in Planning Period" parameter. (Exceptions to this may be set on the MRP Options tab in Maintain Routings. These exceptions will force specificed Routings to be planned in different sized "buckets.")
If you see differences between IR "Suggested Order" quantities and MRP "Planned Orders" or other appropriate categories of replenishment, here's why:
- For Items with a Reorder Method of "Min/Max," IR will not include Projected Demand in its calculations for "Suggested Orders". The reason for this is that IR, unlike MRP with its "planning buckets" by date, doesn't look into the future. IR assumes that "Min/Max" configuration is designed to cover your Projected Demand. MRP, on the other hand, will place Projected Demand into the appropriate time-phased "buckets" and include it in its calculations.
- Also for Items with a Reorder Method of "Min/Max", IR will not included Safety Stock in its calculations. IR assumes that any "Minimum" stock level already includes any Safety Stock Requirements. On the other hand, MRP will calculate "Minimum" as minimum over Safety Stock quantities if there is a quantity in the Safety Stock parameter for the Inventory record.
- One other factor that should be considered: IR does not do anything with Routings. That is to say, IR does not "blow down" through Routings to determine lower-level requirements or generate "Planned" Work Orders. Therefore, if an Item is component on a Routing and is required in the production of some other higher-level product, IR will not see the demand coming from a "Planned Work Order." MRP, on the other hand, will present these data as "Planned Material" entries.
I hope this helps clarify some of differences between MAS 500's MRP and IR calculations. You should give careful consideration as to whether you need Inventory Replenishment or MRP to meet your specific requirements.
-- Richard D. Cushing
Calculating the True Cost of Carrying Inventory
What are your true costs associated with carrying inventory?
Imagine a business—your business—where you never had to carry a single item of inventory. Perhaps you could do this because you functioned more like a broker than a manufacturer or distributor. In such a case, you would take a customer’s order, and then have the items drop-shipped directly from the manufacturer or distributor that supplies each item.
Under such a scenario, what costs would be eliminated for your enterprise? If you find the answer to this question, you also discover the costs that are, in fact, the costs of carrying the inventory when doing so is required by the business model. Here is a suggest list:
- The cost of the physical warehouse space
- Insurances on the warehouse facilities
- The cost of information technologies (IT) systems and support for the warehouse
- The costs associated with heating, cooling, ventilating, telephone, water, sewer, and other utilities provided for the warehouse space
- The employees (FTEs or full-time equivalents) that are involved in handling inventory between receipt of goods and shipping (prorated, if necessary, among other duties)
- Moving inventory
- Replenishing inventory within the warehouse, such as the replenishment of forward pick locations
- Cycle counting
- Disposal of damaged, obsolete, or other inventory
- Warehouse to warehouse transfer handle - The costs associated with owning or leasing and maintaining warehouse equipment
- Racking
- Signage
- Materials handling equipment such as forklifts, picking carts, and similar - The cost of shrinkage and write-downs
- Theft or other unidentified losses
- Inventory damaged in handling
- Shelf-pulls and shop-worn write-downs - The cost of losses due to obsolescence
- The costs related to the capital tied up in inventory
- The costs related to taxes on inventory (where applicable)
Calculating the Estimated Annual Cost of the Warehouse Space
Many different approaches may be taken here. If you are already renting or leasing your warehouse space, then this value will be obvious. If you own your own warehouse space, you could calculate your true cost of ownership of the property. However, the quick and simple approach we took for this spreadsheet is to use a current annual lease rate for a similar property. Obtaining the lease rate equivalent should be as simple as making a call to one or two commercial property management firms in your area to find out the rental value of warehouse space similar to your own.
When calculating the number of square-feet (SF) or cubic-feet (CF) in your warehouse, be sure to exclude:
- Areas used for offices within the warehouse (these will be included later for other reasons); and
- Areas used for purposes other than inventory storage and handling (such as those for shipping or manufacturing).
Calculating the Estimated Annual Cost of Insurances on the Warehouse Facilities
Typically, a call to your insurance agent will get you a pretty accurate cost on the insurances covering your warehouse facilities. The information from your insurance agent may be in dollars-per-square-foot. Your insurance agent may also be willing to calculate your annual insurance cost for your warehouse as a simple dollar total.
Calculating the Annual Utilities Cost for Your Warehouse Facilities
Depending upon whether your utilities are metered separately for your warehouse facilities, calculating your warehouse utilities costs will be either an art or a science. Separate meters will make it a science as you should be able to review your utility bills and simply add up the values over a twelve-month period.
If separate meter information is not available, you will be left with working out some reasonable allocation of utility bills that include other areas of your properties.
Also, remember that even if your warehouse is metered separately, you will need to back out some allocation of utilities costs for warehouse space that you are not including in the cost of carrying inventory as in section “Calculating the Estimated Annual Value of the Warehouse Space” above.
Calculating the Estimated Annual Cost of IT Systems for the Warehouse
The estimated cost of IT (information technology) systems for your warehouse is simply the sum of the value of the computers, routers, cabling, access points, handheld devices, printers, and so forth used in the warehouse divided by the average depreciation or replacement cycle for such equipment. If IT systems are replaced (to keep them current) before they are fully depreciated and there is recoverable salvage value for the items, then you may net the salvage value out of the calculated total cost of such equipment.
Calculating the Estimated Cost of Personnel for Inventory Handling
To arrive at the value of personnel involved in handling inventory (between receipt of goods and shipping), count up and prorate the number of FTEs (full-time equivalents) involved in processes such as cycle counts, moving, transferring, within warehouse replenishment, and other materials handling activities. The FTEs should include allowances for supervision and management of personnel involved in such activities. This will give you the number of “FTEs for Handling”.
Next, you will need to calculate the “Average Hourly Rate” for the FTEs applied to the tasks of inventory handling. To do this you may use a weighted average of the actual personnel used in determining your FTE value.
Lastly, you will need to calculate the “Average Burden Rate” for the personnel. The “burden rate” is the sum of all of the payroll-related costs the firm must pay when employees are paid. A list of potential burden costs would include:
- Cost of payroll preparation – internal (timekeeping, accumulation, verification, etc.)
- Cost of payroll preparation – external (payroll service costs)
- State and federal unemployment insurance costs (SUTA and FUTA)
- Employer portion of costs for fringe benefits such as
- Health, life, and other insurances
- Retirement account employer contributions - Employer paid Social Security contributions
- Costs related to management and processing of mandated special payroll handling such as
- Garnishments
- Child support payments - Other employer paid costs and expenses related directly to wages and payroll
Having calculated the “Average Burden Rate", you are prepared to calculate the total “Estimated Personnel Costs” typically based on 52 weeks at 40 hours per week times the “Average Hourly Rate” with the “Average Burden Rate” added. This mode of calculation automatically covers any vacation or other paid time-off (PTO), so do not include the cost of vacation or PTO in the “Average Burden Rate” factor.
Calculating the Estimated Annual Cost of Warehouse Equipment
If your firm did not need to carry inventory, then it would also not need warehouse equipment like
- Racking,
- Shelving,
- Containers,
- Forklifts,
- Hoist,
- Conveyors, and
- Similar equipment.
Therefore, you should calculate the “Estimated Annual Cost of Warehouse Equipment” by adding up the value of all such equipment and dividing the result by the “Average Depreciation Cycle (Years)”.
Calculating Other Costs Associated with Carrying Inventory
Other costs associated with carrying inventory are simply factors that generally relate to the total “Average Value of Inventory On-Hand”. To arrive at the average value of inventory on-hand, simply add up the ending inventory on-hand at period end for each accounting period over a twelve month interval.
Next, simply calculate historic factors for
- Shrinkage and write-downs,
- Obsolescence,
- Opportunity or Finance Rate for Capital, and
- Average Property Tax Rates on Inventory (if applicable).
After these are calculated, factor them into your total.
Calculated Totals and the Average Carrying Cost as a Percent
By the time you have completed all of the steps above, you should have two critical factors calculated in summary. These factors are:
- “Total Estimated Annual Cost of Carrying Inventory” and
- “Average Carrying Cost as a Percent of Inventory Value”.
The “Average Carrying Cost as a Percent of Inventory Value” is the “Carrying Cost” used by MAS 500 when calculating Economic Order Quantities (EOQs). Most organizations will end up with Carrying Cost Percents ranging from 18 or 20% into the upper 20's as a percent. However, if your business requires specialized warehousing (e.g., high security, refrigeration, special containment, bulky or "high-cube" items), then your Carrying Cost Percent is likely to be higher -- even up to 40% would not be out of the question.
Another thing that you should consider is that not all inventory may have the same Carrying Cost Percent. If your products are a mix of refrigerated and non-refrigerated items, then you will likely need to calculate separate Carrying Cost Percents by product line or Warehouse, for example.
If you'd like to have a Microsoft Excel workbook to aid you in performing these calculations, contact me (Richard D. Cushing).
(c)2008 Richard D. Cushing
Search
Categories
- 1099 (1)
- accounting (2)
- Accounts Payable (1)
- ADMS (1)
- AP (1)
- automated document management (1)
- BI (1)
- big-box (1)
- Budget (1)
- Budgeting (1)
- business (1)
- Business forms automation (2)
- Business Insights Analyzer (1)
- Business Insights Explorer (1)
- business intelligence (1)
- Calendar (1)
- Configuration (1)
- Cost (1)
- cost of carrying (2)
- customer service level (1)
- data views (1)
- Demand Deviation (2)
- Distribution (2)
- EDI (1)
- environment (1)
- EOQ (1)
- Expenses (1)
- Fiscal Period (1)
- Formula (1)
- FRx (1)
- General Ledger (1)
- gross margin (1)
- IM (6)
- Inventory Management (8)
- Inventory Period (2)
- Inventory Replenishment (6)
- Invoice (1)
- IR (2)
- ItemID (1)
- Items (2)
- lead time (1)
- Manufacturing (5)
- MAS 500 (8)
- MAS500 (8)
- Material Requirements Planning (1)
- MICR printing (1)
- Microsoft (1)
- Min/Max (1)
- MRP (1)
- NIREP (1)
- order cycle (1)
- paperless office (1)
- Part Numbers (1)
- Positive Pay (1)
- Purchase Orders (1)
- Purchasing (2)
- Quotes (1)
- RFID (1)
- Safety Stock (2)
- Sales Orders (1)
- savings (1)
- Security (1)
- Setup (1)
- SQL Server (2)
- Statements (1)
- T-SQL (1)
- Unit of Measure (1)
- UOM (1)
- Vulnerability (1)
- Warning (1)
- Year-End Processing (1)