Manufacturer : User Solutions, Inc
OM- Inventory
ManagementExcel based inventory management templates include: Economic order quantity, EOQ with backorders, EOQ with quantity discounts, EOQ for production lot sizes, and Reorder points and safety stocks (ROP).
The purpose of the EOQ model is simple, to find that particular quantity to order which minimizes the total variable costs of inventory. Total variable costs are usually computed on an annual basis and include two components, the costs of ordering and holding inventory. Annual ordering cost is the number of orders placed times the marginal or incremental cost incurred per order. This incremental cost includes several components: the costs of preparing the purchase order, paying the vendor's invoice, and inspecting and handling the material when it arrives. It is difficult to estimate these components precisely but a ball-park figure is good enough. The EOQ is not especially sensitive to errors in inputs.
Why don't we include the purchase cost of the inventory in this calculation? It is not a variable cost. We have to purchase the inventory in any event. The problem is to minimize those costs that vary with the quantity purchased at one time.
Operations Manager
EOQBACK
Overview
Backorders are common in inventories held for resale to customers. The EOQ model can be modified to handle backorders by including one more cost, the cost per unit backordered. This cost is extremely difficult to assess. In theory, the backorder cost should include any special cost of handling the backorder, such as the use of premium transportation, and any cost associated with loss of customer goodwill. As a surrogate for the true backorder cost, most companies use the profit per unit.
The backorder model works well for Ryan because financing the inventory is so expensive. It is much less expensive to incur backorders and fill them when the EOQ arrives than it is to hold inventory. Of course, this is a risky policy and the backorder model must be used with caution. The model assumes that customers are willing to wait on backorders and are not lost to the company. If customers are lost, then the model is inappropriate. There are other models that account for lost customers but they are rarely used in practice because of the risks involved.
Many companies blindly purchase in large quantities to get discount prices without considering all the tradeoffs involved. The costs may well outweigh any savings in purchase price. The EOQDISC model helps you analyze quantity discount offers and make better purchasing decisions.
If you are in a position to bargain with the vendor on prices, use EOQDISC to find the breakeven price. This is the price at which you are indifferent about taking the discount because the annual costs for the standard price and the discount price are the same. You can use the breakeven price as the starting point for negotiating a better discount plan, one that benefits both vendor and customer.
Vendors sometimes offer several different price/quantity plans, so EOQDISC is set up to evaluate four discount plans at once.
Operations Manager
EOQPROD
Overview
Alvin Air Systems in Alvin , Texas, produces repair and maintenance items for commercial air conditioning systems. Alvincannot use the standard EOQ for determining production lot sizes. One problem is that the standard EOQ assumes that an order quantity or lot of material is received all at once. At Alvin, most production lots take some time to complete and parts are placed in stock on a daily basis until the run is over. A related problem is that substantial sales usually occur before the run is over. To solve these problems, Alvinuses the modified EOQ model for production lot sizes. Why? The EOQ must be larger than maximum investment to account for sales made during the production run.
When demand is uncertain, inventory investment from the EOQ model should be supplemented with safety stock. ROP.xls is a worksheet used to compute safety stocks and reorder points.
For over 12 years User Solutions, Inc. has been the easy choice for thousands of companies worldwide.
Full Business Operations Manager
Order
Operations Manager - Saver Package contains many business Excel spreadsheets: inventory management, production planning, job scheduling, quality control, analysis of waiting lines, and more.
Additional Information: Operations Manager |
|||
|
Require Excel 97 or
greater.
30-day money back guarantee. |
FULL Operations Manager - Saver Package |
$129.00 | |
To manage all organization resources, please see Resource Manager for Excel
Business & Finance
Material Resources Planning Systems Accounting Amortization Business Business Plan Automation Finance
Info Managers Inventory Systems Bar Code Fonts Legal Planners/Schedulers Presentation Tools
Project Management Internet Marketing
Management Systems (
Contact Management Document Management Sales Management
Real Estate Vehicles Venture Capital
Business Using Mail, Phone, or Email
Publishing Gem Management Rental Repair Travel )
Software for MS Excel (Accounting Analysis Budgeting Calculators Cars Cash Flow Charting Checks Converters E-Books Finance Forecasting Employment Inventory Investment IRR Management Marketing NPV Planners Project Management Quality Control Schedulers )