Spreadsheets
The EIM Version 6.1 spreadsheet set is the perfect companion to the new Achieving Effective Inventory Management – 7th Edition. This set has upgraded all 11 inventory-analysis spreadsheets, and includes new features designed to help organizations implement the principles of effective inventory management. The enhanced EIM Forecasting/Analysis spreadsheet allows you to analyze up to 100 items at a time by loading your data into a single worksheet.
The EIM Version 6.1 Spreadsheet Set Contains:
- EIM Version 6.1 Spreadsheet Product Documentation: A comprehensive guide for all 11 spreadsheets
- The EIM Forecasting/Analysis Spreadsheet
- Price Break by Item Spreadsheet
- Price Break by Vendor Line Spreadsheet
- Price Break with Freight Allowance and Terms Spreadsheet
- Rebate Analysis
- Value of Lost Material Spreadsheet
- Landed Cost Calculator
- Cost of Filling Order Calculator
- Carrying Cost and Replenishment Cost Calculator
- Replenishment Parameter Analysis
- Price Increase Analysis
Order Now
The spreadsheet templates, instructions, and visuals will be delivered to you electronically. Order online at the Online Store. If you have any questions, contact us at 972.304.3325 or email MeganS@effectiveinventory.com
Details By Spreadsheet
The following are the included spreadsheets with screenshots of some of the features you will receive:
Forecasting/Analysis Spreadsheet
The Graph Worksheet presents detailed information for each entered item:
The Adjusted Margin and other interactive worksheets allow you to perform “what if” analysis to see how different amounts of inventory, as well as changes in sales or cost of goods sold will affect profitability:
The Unusual worksheet highlights significant differences between forecasted demand and actual sales/usage:
The spreadsheet determines which of ten forecast formulas best predicts future demand for each item:
A summary displays the projected customer service level based on your settings and compares your current inventory value to a “target investment”:
This is just a sampling of the information you will find in the many worksheets in this comprehensive inventory analysis tool.
Other spreadsheets in the set include:
Price Break by Item Spreadsheet:
This spreadsheet allows you to compare different quantity breaks for an item and identifies the “best deal”. A graph displays how much of each option is comprised of the cost of the material, the cost of carrying inventory (i.e., the K Cost) and the cost of replenishing or ordering inventory:
Price Break by Vendor Line Spreadsheet
This spreadsheet identifies the best buy among several vendor offers considering both the discounted cost of the material and the cost of carrying inventory. The results also are graphically displayed:
Price Break with Freight Allowance and Terms Spreadsheet
This spreadsheet allows you to consider extended terms, terms discounts, and freight charges in determining how much to buy. As with our other analysis spreadsheets, results are graphically displayed and the “Best Buy” is identified:
Rebate Analysis
This spreadsheet allows you to evaluate vendor rebate offers. It compares the cost of carrying additional inventory you must buy against the additional rebates you might receive. It tells you whether each opportunity is a “Good Deal” or a “Bad Deal”.
Value of Lost Material Spreadsheet
You buy inventory to sell it to other people or use it to produce goods and services that you sell. When inventory is lost, stolen, or damaged, it cannot be used to create income for your organization. This spreadsheet allows you to enter the value of material that was “lost” each month and shows your employees the additional sales your company must generate to make up for the loss. Projected losses for future months (based on past loss history) are also displayed:
Landed Cost Calculator
Most companies make the mistake of deciding on what vendor to deal with based on their quoted “replacement cost”. But you may incur other costs such as freight, custom duties, inspection and repackaging. The Landed Cost Calculator allows you to enter and consider these factors so you can discover your lowest “total cost” of inventory:
Cost of Filling Order Calculator
Does your organization know what it costs to fulfill an outgoing order? With information from your profit and loss statement and ERP system, you can easily determine the cost of filling a one-to-12-line order. This will enable you to better analyze profitability. As with all most of the spreadsheets in the set, results are also graphically displayed:
Carrying Cost and Replenishment Cost Calculator
Warehousing costs can be divided into the cost of filling orders (see above) and the cost of carrying or maintaining inventory. This is also known as the “K Cost”. The K Cost is expressed as a percentage and represents the annual cost of maintaining a dollar’s (or other monetary unit) worth of inventory. This is a vital component in calculating economic order quantities and assessing true profitability:
This spreadsheet also has a worksheet to calculate your cost of replenishing inventory, also known as the “R Cost”. This is the cost of issuing and receiving a line item on a replenishment order. This is a necessary component in calculating economic order quantities:
Replenishment Parameter Analysis
This is a great tool for performing “what if” analysis and training new buyers and inventory planners. You can view how projected inventory movement over the upcoming 12 months will change as you manipulate forecasts, costs, lead times, order cycles, and other inventory-related parameters. Complete documentation is provided that defines each input cell. And the spreadsheet allows you to enter “override” values so you can compare “before and after” results:
Price Increase Analysis
The price increase analysis spreadsheet will suggest what you should buy when a vendor offers you the opportunity to place one more replenishment order before a price increase takes effect. That is the quantity that will minimize your total cost of inventory and maximize net profitability. It suggests the quantity of an item you will need to buy to bring the net available quantity up to a specific number of months’ supply above the line point (also known as the reorder point):
