By Kevin W. Goyette
ABA Inventory: Series 3
Obtaining the right reports is critical and is only valuable if you can read and understand the reports. Reading inventory reports is a relatively simple undertaking. Most descriptions and categories outlined by your borrower make sense once the borrower explains them to you. Categories will be basic with details like colors, sizes, makes, models combined underneath these categories. Understand the simple categories first. Is this lumber? Okay what are the categories? Plywood, studs, planks? Is this apparel categories? Men’s pants, lady’s pants, men’s shirts etc.
Inventory Information Request
Below is a list of reports or information you need to consider when requesting inventory information.
- Inventory stock status report(s) as of the latest month end segregated between raw materials, work-in-process and finished goods or other classes of inventory as currently maintained by the Company. With this report you can find out where excesses are located and with some probing questions you can find out if the areas that appear to be over inventories are permanent or temporary.
- High level inventory summary by geographical location. If you have multiple locations you have multiple overhead considerations. Looking at all locations the same is a mistake and not all locations consolidate well. Each location may also have different landlords which may not all be cooperating with you without a landlord waiver. If no landlord is there a mortgage and will you be capturing those expenses when you look at the “Net” reports We haven’t listed them here, but they related to the expenses you incur while liquidating inventory. For example; rent, mortgage payments, insurance, consolidation costs, warehouse employees, field liquidation agents etc.
- Sales information for the finished goods inventory. This report should contain item number, quantity sold, extended sales dollars, and cost of goods sold. This information helps you understand how fast finished goods are being sold, how much above cost they are being sold for and whether a material handling charge is included in the cost of goods sold.
- Sales Forecast/open order report for the inventory. In this report you should be checking to see if you have a good steady reliable sales channel for the finished goods. If you are a tire distributor to some auto repair garage, then you have a steady open order report and you need to look at your ability to fulfil these orders and whether the buyers can easily replace your subject Company with another provider.
- Reconciliation of submitted inventory reports to the general ledger/financial statements as of the most recent month end. Showing any applicable reserves on the inventory along with an explanation for the reserve and the basis upon which it is established is important to show. If these reserves are maintained on a detailed basis and by inventory item, you have a strong indication that your borrowers inventory is being well maintained. In addition, you may have a large amount of reserves for returns or damages that you may need consider as ineligible as returns and damages are returned for credit and not cash. In general, those are your borrower’s vendors and are likely owed money therefore returns and damages need to not be accumulating and those you have on the books should be labeled ineligible.
- Summary of location codes, category codes, and product line codes used in the inventory listings. These codes all have some nomenclature that you can learn and follow with a little help from a key report which outlines what the codes stand for, then with a little work on excel you’ll be able to categorize an inventory report in just a few clicks. Apply a vlookup formula and you’ll have a detailed inventory summarized in a snap!
- Copy of the latest interim financial statements, and prior two (2) year’s annual statements (audited/reviewed preferred), including balance sheet and consolidated income statements. In addition, if available income statements broken down by product line or division as this is customarily reported.
- Explanation of inventory costing methodology including method of costing (FIFO or LIFO), basis of costing (i.e. average, standard) and items included in costing (i.e. freight, direct labor, landing costs). Include detailed explanation of any lower cost market (“LCM”) methodology including source of market pricing levels used. This can be verbally explained during the site visit.
- Copy of last physical inventory book-to-physical adjustments (or cycle count reports if physical counts are not conducted). Summary is fine as opposed to detail by item.
- List of 20 largest customers for the past two years including sales dollars. Also, your borrower should be able to indicate the total number of active customers along with typical payment terms for each of the 20 largest customers for the most recent period. In addition, you should identify if there are any contracts established with the customers.
- List of 20 largest vendors during the past 12 months including products purchased and purchase dollars.
- List of top 10 competitors and their market share, as well as, the Company’s best estimate of the total size of the market they compete in. It is also very good to get copies of any available market studies (if available) or industry-published studies supporting market size or market share information.
- Information on returns, credits, allowances, volume rebates, warranties, guarantees and/or licensing agreements. Provide a summary of these expenses, by product line/division as applicable for year-to-date and prior year period.
- Licensing – Copies of all current licensing/royalty agreements and a two-year (or CY and prior year) history of payments made under these agreements, by product. If not applicable, have the borrower memorialize that the Company does have not related expense in this area.
- Summary of sales and inventory levels by month for the past 24 months.
- Copies of product line brochures and price lists, specification sheets, etc. (as applicable)
All these reports help you provide a gross understanding of what you should possibly lend against.
Sorting the inventory to figure out the bulk of your inventory helps lead to better questions. If you can understand what is moving fast or slow by applying sales history is the next step.
Inventory Sales Reports
Generally, I look at a two-year history of sales by Stock Keep Unit (SKU). A sales report should be able to tie to inventory reports by a common stock keeping unit number. These are long and may have built in categories.
Ideally tie sales to inventory by unit versus cost or selling price. Quantities on hand versus quantiles sold are the best method of calculating month on hand. Inventory turnover is the velocity I can sell my finished inventory at and fast selling items have historical better recovery values as they relate to normal selling price.
At this point you’ve completed a basic understanding of the inventory, you’ve identified the inventory classes, you developed an inventory turnover. By doing so you can see how much is moving fast and how much of the inventory is moving slow. More advanced reviews will get into understanding selling price, discounts, the impact on recovery factors should the company decide to end that business for any reason. When you get past the basics you begin to understand what factors can positively or negatively impact your potential advance rates.
Remember you can contact Cobblestone Management to review your existing borrowers’ and new prospects’ inventories for an in-depth inventory review or inventory appraisal.
 Material handling charges can be a hidden cost that some buyers won’t pay, for example if this was a distributor of Widgets and a competitor who distributes the same item both pay $10, and one adds $1 for handling but only competitors are likely to buy the item you must immediately strip out the $1 material handling charge.
 VLOOKUP is an excel formula that takes vertical data and looks them up on a table, i.e. Column of stock keeping units tied to a table of codes.