MAS 500 ERP

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

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:

1. “Total Estimated Annual Cost of Carrying Inventory” and
2. “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

If your business deals with inventory and you are going to try to optimize your inventory investment, you will need to be able to identify your good from your bad inventory, and you will certainly want to know about your really ugly inventory. But in order to do that, you need to know some other factors about your own business operations first.

Your NIREP -- Non-Inventory Related Expenses as a Percent (of Revenues)

To calculate your NIREP, you need to gather up (say from your last fiscal year) all of the expenses your organization incurred and separate them into two groups:

• Inventory-related Expenses: That is, those expenses that are related to purchasing, receiving and warehousing inventory. Include all the expenses related to owning or leasing your warehouse space, the expenses related to lease-hold or ownership improvements in the warehouse, the expenses related to leasing or owning warehouse equipment (e.g., racking, lift trucks, conveyors, dock equipment), the expenses related to wages paid to warehouse personnel (prorated, if necessary, when warehouse personnel may serve other purposes -- like manufacturing), and the expenses related to technologies supporting warehouse activities. (A good way to think about this is to ask yourself this question: "If I did not have to carry any inventory -- if I could drop-ship all my products from another vendor or manufacturer -- what expenses could I eliminate entirely?" The resulting list of expenses are your "Inventory-related Expenses".)
  
• Non-Inventory Related Expenses: Once you've come up with the list of Inventory-related Expenses, the expenses (not cost of goods sold) you have left on your profit-and-loss statement are "Non-Inventory Related Expenses".
  

NOTE: It would be possible to create a custom FRx report that would
separate your Inventory-Related Expenses (based on prorata calculations, too)
and then calculate your Non-Inventory Related Expenses for you.

Now, to calculate your NIREP, take that Non-Inventory Related Expense value we calculated above and divide that by sales revenues over the same period. For example, if your Non-Inventory Related Expenses are $3.77 million for last year and your Revenues for last year were $26 million, then your NIREP is $3.77 million divided by $26 million or 14.5%.

Your Adjusted Gross Margin

Many organizations believe that they make the same amount of "margin" on a product regardless of the amount of inventory they are required to keep on hand in support of the sales. However, those that believe this are fooling themselves. Consider the following example:

Product A:

• Annual Sales = $12,500
• Annual Cost of Goods Sold (COGS) = $9,500
• Gross Margin: $12,500 - $9,500 = $3,000/$12,500 (Sales) = 24%

Product B:

• Annual Sales = $12,500
• Annual COGS = $10,000
• Gross Margin: $12,500 - $10,000 = $2,500/$12,500 (Sales) = 20%

Wow! We make a 4% greater margin on Product A than Product B, right?

When we calculate Adjusted Gross Margin, we take into account the expenses incurred for carrying inventory (by product or by product line). The formula is:

(Annual Sales Dollars - Annual COGS - (Average Inventory Value * Carrying Cost %)) / Annual Sales Dollars

The difference between the traditional Gross Margin formula and the Adjusted Gross Margin formula is in red above.

Let's say that for our product examples above, we are driven to carry the following inventories:

Product A:

• Average Inventory = $5,000
• Annual Carrying Cost = 25%

Product B:

• Average Inventory = $2,500
• Annual Carrying Cost = 25%

Now, if you calculate the Adjusted Gross Margin for our Products A and B above, you should come up with the following values:

• Product A's Adjusted Gross Margin = 14%
  
• Product B's Adjusted Gross Margin = 15%

Now we can separate "The Good" from "The Bad" and "The Ugly"

Once you have calculated both your NIREP and your Adjusted Gross Margins by product or product line, you are in a position to separate your inventory into "The Good", "The Bad" and "The Ugly". Simply establish some simple rules such as the following:

• "The Good" have Adjusted Gross Margins at least N% greater than your NIREP.
  
• "The Bad" have Adjusted Gross Margins less than N% greater than your NIREP but greater than your NIREP.
  
• "The Ugly" have Adjusted Gross Margins less than or equal to your NIREP.

Since we calculated a NIREP (by example above) of 14.5%, in our example maybe our rules would look like this:

• "The Good": Adjusted Gross Margins greater than 24.5% (NIREP + 10%)
• "The Bad": Adjusted Gross Margins greater than 14.5% (NIREP) but less than 24.5% (NIREP + 10%)
• "The Ugly": Adjusted Gross Margins less than 14.5% (NIREP)

Please note that there may be valid reasons for keeping some of "The Bad" or even "The Ugly" around in inventory, but the reasons should be carefully considered. For example, some of these less than desirable items may function as loss leaders to attract business for more profitable items. Other items may be necessary to "support" the sales of items in "The Good" category.

With these calculations in your hand, however, at least you are not making decisions with your eyes closed.

(c)2008 Richard D. Cushing