Inventory Profiling
Value Resource Group, Inc. specializes in asset-based lending appraisal, liquidation, auction, and consulting services. After 30 years of serving clients throughout the world, we’ve recognized the need for a valuation service not previously offered in our corner of the industry: inventory profiling.
Profiling is often referred to as the halfway point between “desktop opinions” and “complete, narrative appraisals.” As such, it’s primarily used when neither of the former valuation levels is feasible due to time constraints, cost, borrower reservations, or any number of mitigating factors. Our impetus in offering this service is based on our understanding of the existing system’s inherent challenges, specifically the reliance upon phone polling with limited information.
Thanks to our proprietary software, profiling is seamless in migrating borrower data over the internet. It provides an inventory profile with no more effort than the simple inquiry method currently used. Most importantly, profiling provides user-friendly and accurate sets of tools for collateral and/or field examiners with little time to examine inventory records. It allows for complete and thorough analyses of perpetual inventory at the line-item level.
For example, a given borrower’s perpetual inventory may contain around 100,000 unique part numbers. In situations like these, it’s virtually impossible for collateral and/or field examiners to analyze such an immense database. In most cases, however, our profiling software can analyze that inventory in a matter of hours. The advantage to the lender is undeniable.
When in the simple profile summary format, the collateral monitoring process is quick, inexpensive, and effective. It’s also far more efficient when compared with any of the current methods available.
From the perspective of the relationship manager, it should be emphasized that profile data requests are merely simple amendments to the data requests utilized in standard audits. Further, they combine the advantage of drawing on field observations from collateral and/or field examiners while utilizing the breadth of experience and actual liquidation background of the appraiser. This combination of efforts (and the resulting profile) can be used and adjusted periodically by the collateral and/or field examiner without additional effort or expense.
Profile Requirements
Generally, the scope of a profile is based on the size and complexity of the inventory. It initially includes the following:
- Requesting data from the borrower for data transfer into VRG software
- Secondary consultation and submission of a detailed slow-moving/obsolete inventory to the borrower
- Consultation with the collateral and/or field examiner, or if requested, the relationship manager on existing accounts
- Segregation of inventory into appropriate classifications
- Calculation of liquidation value recoveries for each class
- Estimation of liquidation expenses for determination of the net orderly liquidation value (OLV)
- Compilation of the Profile format
- Profile downloads in Excel format
- Profile transfers to the lender
Elements Unique to Each Job
- Data Set-Up Time
- Turn Analysis
- Profiling
Pre-Qualifying Factors
- Data File Size
- Data Cleanliness
- Number of Companies Involved
- Borrower Cooperation (Response Time Critical)
- Inventory Profiling Requirements Case Study
A Case Study in Inventory Profiling Requirements
The subject data shown as an example is a current borrower, “APSP,” an aircraft parts company. The original inventory data consisted of seven original Excel inventory data, sales, and identification files.
Inventory Data File Layout Example:
PART NO | DESCRIPTION | CATEGORY | W/H | LOC | QTY | UM | COND | RECV’D | UNIT CST | UM | EXT CST |
6842683 | SPACER ASSY | GENERAL | 1 | D-E3-04-02 | 1 | EA | AR | 7/25/03 | $1,480.00 | EA | $1,480.00 |
6842683 | SPACER ASSY | GENERAL | 1 | D-E3-04-02 | 1 | EA | AR | 7/25/03 | $1,480.00 | EA | $1,480.00 |
6842683 | SPACER ASSY | GENERAL | 1 | D-E3-04-02 | 1 | EA | AR | 7/25/03 | $0.00 | EA | $0.00 |
6842683 | SPACER ASSY | GENERAL | 1 | D-E3-04-02 | 1 | EA | AR | 7/25/03 | $1,480.00 | EA | $1,480.00 |
6842683 | SPACER ASSY | GENERAL | 1 | D-E3-04-02 | 1 | EA | AR | 7/25/03 | $1,480.00 | EA | $1,480.00 |
6842683 | SPACER ASSY | GENERAL | 1 | D-E3-04-02 | 1 | EA | AR | 7/25/03 | $1,480.00 | EA | $1,480.00 |
Inventory Sales File Layout Example:
PROD NO | 2003 SLS |
02-10719 | 26 |
02-11084 | 1 |
02-11550 | 4 |
02-11929 | 5 |
02-11930 | 2 |
02-11931 | 1 |
After we reviewed each file to address any obvious layout or content errors, we imported each of the Excel files into our proprietary inventory/profiling program. Then, each file was restructured for conformity to program layout standards.
- C2 and with a length of 9 digits
- A6 and with a length of 8 digits
- 4 and with a length of 5 digits
- 68 and with a length of 6 digits
- 23 and with a length of 7 digits
PMA Identification File Layout Example:
PROJ NO | PRIORITY | PART NO | FAA STATUS | CONFORMITY STATUS | ACTION REQUIRED | COMPETITIVE STATUS | ||
* | 6816047CM | AE3007 | Gasket | Approved 7/15/03 | Parts in STK | |||
RV1235NY-T | * | CH2039A0054 | EMB135 | Piston repair | Approved 3/9/04 | Done | TC get PO | |
* | DN1324M13P04 | CFM56 | Bushing | Approved 12/7/01 | Parts in STK | |||
* | DN1348M66P04 | CFM56 | Gasket | Approved 12/14/01 | Parts in STK | |||
* | DN1359M84P06 | CFM56 | E SEAL | Approved 4/29/04 | Parts due Jul/Aug | |||
* | DN1361M24P02 | CFM56 | Seal-Face | Approved 6/12/03 | Parts in STK |
Compressor Identification File Layout Example:
PART NO. 6875455 | INV CST - 9K | |||
P/N | COMP NO | QTY | COND | COST |
23058592 | AE103836 | 1.00 | AR | 0.00 |
6827847 | AE103836 | 1.00 | AR | 0.00 |
6841151 | AE103836 | 1.00 | AR | 0.00 |
6845945 | AE103836 | 1.00 | AR | 0.00 |
6850503 | AE103836 | 1.00 | AR | 0.00 |
6870209 | AE103836 | 2.00 | AR | 0.00 |
Aircraft parts inventories also require a condition code that is applied to each part. VRG uses a set of industry-standard abbreviations for these codes. APSP uses a combination of these same industry-standard abbreviations, as well as their own internally created abbreviations. For this aspect, we evaluated the company’s internal abbreviations one by one to convert them to their equivalent of the industry standard. In addition to any company-specific tagging, VRG also uses its program to identify particular segments of any inventory that could affect value, including but not limited to: packaging, supplies, obsolete items, soft costs, etc. We also tag for “new product,” which is defined as any part with an entry date that falls within the previous twelve months of the effective date of an appraisal. With APSP, we not only used the date entered but also verified that the same part number was not listed in the inventory with the previous date. This is because the new product is only defined as such if it has not been carried in the inventory prior to the twelve-month period preceding the appraisal.
PART NO | DESCRIPTION | CATEGORY | W/H | LOC | QTY | UM | CC | RECEIVED DATE |
2-200-070-23 | TORQUEMETER KIT | TEXTRON | XX | G4-05-05 | 2 | EA | OH | 3/25/02 |
2-121-140-44 | BLADE | TEXTRON | XX | G4-06-03 | 34 | EA | NE | 2/27/02 |
74911 | PLATE & BRG ASSY | XX | D1-03-01 | 14 | EA | NS | 2/27/02 | |
75866 | COMPENSATING BELLOW | CECO FUEL | NO | G2-05-02 | 4 | EA | NE | 11/19/01 |
2-100-040-16 | VANE ASSY | TEXTRON | NO | G2-05-08 | 1 | EA | NE | 11/19/01 |
1-030-192-04 | GEARSHAFT | AVCO | NO | G2-05-02 | 1 | EA | NE | 11/19/01 |
75019 | VALVE | CECO FUEL | NO | G2-05-05 | 1 | EA | NE | 11/19/01 |
23009386 | VANE | GENERAL | 8 | M3-02-03 | 6 | EA | OH | 7/30/03 |
23058584 | BEARING, ROLLER, | GENERAL | 8 | H2-02-01 | 1 | EA | NE | 6/2/03 |
T23055591 | BEARING, ROLLER-CYL 55 X | GENERAL | 8 | L2-01-01 | 1 | EA | FN | 7/22/03 |
150-022-002 | FUEL FLOW TRANSMITTER | F16 AIRCRAFT | 5 | INSP | 1 | EA | OH | 6/9/04 |
PART NO |
UNIT COST | EXT COST | DAYS SALES
ON-HAND |
ELIGIBLE QTY | ELIGIBLE
EXTENDED COST |
EXCESS QTY | EXCESS
EXTENDED COST |
2-200-070-23 | $17,284.85 | $34,569.70 | 0 | $0.00 | 2 | $34,569.70 | |
2-121-140-44 | $575.30 | $19,560.20 | 0 | $0.00 | 34 | $19,560.20 | |
74911 | $1,026.81 | $14,375.34 | 7,210 | 1 | $1,026.81 | 13 | $13,348.53 |
75866 | $3,969.00 | $15,876.00 | 0 | $0.00 | 4 | $15,876.00 | |
2-100-040-16 | $4,752.00 | $4,752.00 | 0 | $0.00 | 1 | $4,752.00 | |
1-030-192-04 | $2,377.10 | $2,377.10 | 0 | $0.00 | 1 | $2,377.10 | |
75019 | $1,963.86 | $1,963.86 | 0 | $0.00 | 1 | $1,963.86 | |
23009386 | $1,400.00 | $8,400.00 | 1,803 | 2 | $2,800.00 | 4 | $5,600.00 |
23058584 | $2,400.00 | $2,400.00 | 0 | $0.00 | 1 | $2,400.00 | |
T23055591 | $2,105.11 | $2,105.11 | 0 | $0.00 | 1 | $2,105.11 | |
150-022-002 | $5,000.00 | $5,000.00 | 0 | $0.00 | 1 | $5,000.00 |
Once turn was established, we created a slow-moving report for APSP’s review, which contained every part number where at least one unit was considered to be slow-moving based on the parameters set for turn by VRG. The company then reviewed the slow-moving report and identified any part numbers where they disagreed with the slow-moving report, with the basis for this determination. For example:
NOTE 6:
Order for Directorate of Aeronautical Engineering (Royal Thai Air Force)
NOTE 7:
Will be used by Samsung in August. Samsung awaiting final Korean government approval on fuel control overhaul. They have notified us of the pending order.
VRG’s appraisers reviewed APSP’s reasoning for changing the status of a part number from slow-moving to current and when in concurrence, amended the inventory file by making the necessary adjustments to the inventory turn using a series of scripts and query statements. A subsequent summary of inventory was created for the appraiser's review and valuation. Once the appraiser provides his opinion of the inventory’s value, the inventory staff then proceeds with another series of scripts to apply the assigned values. A “rough draft” summary of inventory is then issued to the appraiser for further review and consideration. After the appraiser has approved the final summary on an assignment, the necessary reports needed to produce the formal appraisal are created.
In the appraisal of APSP, VRG analyzed one (1) original inventory file, six (6) original sales files, and one (1) original reference file. Through a series of table restructuring, marriage, cross-referencing, query statements and scripts, approximately one hundred and twenty (120) additional working files were created in order to formulate the final summary and supporting reports that are clear, concise, and quick to review.
ORDERLY LIQUIDATION VALUE
Effective Date: May 31, 2004
PRODUCT CATEGORY
DESCRIPTION |
EXTENDED COST |
WEIGHTED PERCENT |
EXTENDED VALUE |
*SLOW-MOVING EXTENDED COST | ||||||
PARTS | $397,425.75 | 64.84% | $257,708.44 | $1,089,971.19 | ||||||
HIGH PROBABLITY SALES PARTS | $277,368.15 | 48.07% | $133,317.70 | |||||||
CF6-CFM ENGINE PARTS | $25,217.02 | 66.23% | $16,700.00 | |||||||
F100 ENGINE PARTS | $12,159.82 | 71.87% | $8,739.40 | $139,280.31 | ||||||
T53 ENGINE PARTS | $63,283.20 | 46.84% | $29,644.30 | $171,971.27 | ||||||
T55 ENGINE PARTS | $11,397.38 | 46.60% | $5,311.65 | $107,898.12 | ||||||
T56 ENGINE PARTS | $1,026,708.98 | 70.75% | $726,361.00 | $354,332.98 | ||||||
T56 HIGH PROBABILITY ENGINE PARTS | $28,512.76 | 68.39% | $19,500.00 | |||||||
COMPRESSORS | $34,197.80 | 73.10% | $25,000.00 | |||||||
SOLE SOURCE PMA'S | $234,626.23 | 84.48% | $198,221.25 | |||||||
CF6/CFM SOLE SOURCE PMA'S | $187,942.59 | 87.71% | $164,838.75 | |||||||
WRITTEN DOWN ON BOOKS | $70,703.09 | |||||||||
NON RECURRING EXPENSES | $27,825.00 | |||||||||
TOTAL ELIGIBLE: | $2,298,839.68 | |||||||||
GROSS RECOVERY: | 68.96% | $1,585,342.49 | $1,961,981.96 | |||||||
Less Liquidation Expenses (Based on a Six-Month Period): | ($251,748.50) | |||||||||
NET RECOVERY: | 58.01% | $1,333,593.99 | ||||||||
GRAND TOTAL COST: | $4,260,821.64 | |||||||||
*EXEMPT INVENTORY SALVAGE VALUE ESTIMATED AT APPROXIMATELY 15% - 20% RECOVERY
At the time of the second appraisal of APSP, the company had added an application field to its inventory data reports. This made identification of aircraft/engine type more streamlined for both the APSP and VRG. It also made it possible for the examiner to portion out the inventory in a manner consistent with the summary requirements for inventory breakdown. This portioning is not always exact with that listed on the summary due to the multiplicity of applications an aircraft parts distributor uses. The summary of inventory is usually compressed to show only the main areas of inventory, while combining smaller portions of inventory under a general category, i.e., miscellaneous, general, etc. This field also identifies whether or not a part is PMA.
Once a company has been profiled by VRG, all information, and programming used on a job to facilitate any subsequent profiling are kept in our master files for future reference by any of the parties involved. While there are portions of the inventory data, such as structuring, that needs to be accomplished each time we profile a company, other portions of the appraisal, such as scripts, are reviewed for applicability and conformity and can be re-used to streamline the process. Also, with multiple appraisals over a period of time, VRG can track the changes in inventory using graphing. This allows an examiner to see any changes that may affect the inventory either positive or negative with a single sheet.
In the case of APSP, we have appraised this company four times in the last two (2) years. Using VRG’s slow-moving parameters allows the company to see the portion of their inventory that is considered slow-moving and facilitates the release of these inventories to clean their shelves of dead inventory. This is demonstrated in the provided charts below.
APSP INVENTORY PROFILE 05/04 - 07/05
EFFECTIVE DATE | ELIGIBLE | EXEMPT | TOTAL | INVENTORY PROFILE |
5/31/2004 | $2,298,840 | $1,961,982 | $4,260,822 | 46.05% |
9/30/2004 | $2,234,496 | $1,898,217 | $4,132,713 | 45.93% |
1/31/2005 | $2,723,038 | $1,476,497 | $4,199,535 | 35.16% |
7/15/2005 | $3,444,863 | $1,047,304 | $4,492,168 | 23.31% |
APSP SOLE SOURCE PMA CF6/CFM 56
RELATIVE TO OVERALL ELIGIBLE INVENTORY
EFFECTIVE DATE | SOLE SOURCE CF6/CFM56 | TOTAL ELIGIBLE | INVENTORY PROFILE |
5/31/2004 | $187,943 | $2,298,840 | 8.18% |
9/30/2004 | $235,556 | $2,234,496 | 10.54% |
1/31/2005 | $655,831 | $2,723,038 | 24.08% |
7/15/2005 | $1,242,398 | $3,444,863 | 36.07% |
VRG identified the slow-moving inventory of more than three-quarters of a million dollars that the company had not identified. In response, the company added fields to its inventory system to identify certain portions of their inventory for better turn tracking. In addition to building its inventory, the company has also improved its inventory mix, focusing their attention on their CF6/CFM56 PMA inventory. Slow-moving inventory percentages have been halved. In May 2004, 46% of their inventory was considered slow-moving. In July 2005, slow-moving accounted for only 23% of their inventory.
In this case, the company stated clearly that they had changed their entire set of inventory management practices based on their reporting on a regular basis to VRG. This was based on a determination--one that essentially said, “If I can’t borrow against it, why keep it?” Monitoring this particular collateral with regular profiling helps ensure that the tools stay sound. Additionally, if there is any significant departure, it will be immediately identified and reported to the lender.
As Confidentiality of Borrower data is endemic to the Profile process, VRG would propose execution of this Agreement, which will, subject to review, apply globally to all Borrowers subject to the Profile process.
THIS CONFIDENTIALITY AGREEMENT (“this Agreement”) is made effective as of October 1, 2005 by and between LENDER Bank (LENDER), and Value Resource Group, Inc. (“VRG”).
R E C I T A L S
WHEREAS, VRG has engaged to profile the value of a LENDER client’s inventory (the “Profile”);
WHEREAS, VRG, in its profile capacity on the behalf of its assignments for LENDER, in its Appraisal, Liquidation, and Profile (Services), will acquire certain confidential and proprietary information, including any trade secrets, pricing information, marketing materials, customer lists, product and service specifications, and financial data that derives economic value, actual or potential, from not being readily known to other persons who can obtain economic benefit from its use (the “Confidential Information”);
WHEREAS, LENDER agrees to facilitate the Services and VRG agrees to facilitate its Services and agrees to protect any of LENDER’s clients’ confidential information;
WHEREAS, LENDER and VRG enter into this Agreement in order to set forth the terms and conditions under which any LENDER client shall furnish and disclose to VRG the Confidential Information solely in connection with conducting the Service.
NOW, THEREFORE, in order to induce an LENDER client to make the Confidential Information available to VRG, and in consideration of an LENDER client’s agreement to disclose the Confidential Information to VRG, VRG hereby irrevocably agrees with LENDER as follows:
- Use of Information. For the purposes of conducting the Service, it is desirable for VRG to have access to the Confidential Information.
- Disclosure of Information. Without LENDER client’s prior written approval, VRG will not at any time disclose any Confidential Information to any person, firm, or entity other than authorized representatives of the Receiving Party (“Authorized Representatives”). As used herein, the term “Authorized Representatives” shall include officers, directors, or employees of VRG and VRG’s legal counsel, consultants, representatives, and independent accountants.
- Use of Information. Without LENDER client’s prior written approval, VRG will not at any time use any of the Confidential Information or permit the use of the Confidential Information for any reason or purpose other than for the Profile.
- Duration of Restriction. Unless otherwise agreed to in writing by LENDER for a period of five (5) years from the Effective Date, VRG shall treat as confidential and shall not use, disclose, or otherwise make available any Confidential Information of a LENDER client to any person other than Authorized Representatives of VRG who have a need to know such Confidential Information.
- Standard of Care. VRG shall instruct its Authorized Representatives and any employees, attorneys, consultants, and other affiliates of any of the Authorized Representatives who have access to the Confidential Information to keep the same confidence by using at least the same care and discretion that either LENDER or VRG uses with respect to its own confidential information and trade secrets. All copies, reproductions, disclosures, summaries, and distributions of Confidential Information shall contain and state the same confidential or proprietary notices or legends, if any, that appear on the original. The Receiving Party agrees to reasonably segregate all tangible forms of Confidential Information from the confidential materials of others.
- Unauthorized Use or Disclosure. The Receiving Party shall notify VRG immediately upon discovery of any material unauthorized use or disclosure of Confidential Information and shall reasonably cooperate with VRG to regain possession of the Confidential Information and prevent its further unauthorized use.
- This Agreement creates a relationship of trust and confidence between LENDER and VRG and will be construed and enforced accordingly. If VRG or any Authorized Representatives breach or threaten to breach any provision hereof, this Agreement may be enforced by LENDER in an action for damages or injunctive relief, or both, it being recognized and agreed by VRG that LENDER’s remedies at law may be inadequate under the circumstances.
- Rebuttal of Implied License. Nothing in this Agreement shall be deemed to constitute an implied license in favor of VRG Party of Confidential Information or of any other intellectual property or proprietary rights of LENDER.
- The confidentiality provisions of this Agreement shall not apply to the following: (i) Confidential Information already known by VRG without an obligation of confidentiality; (ii) Confidential Information that is or becomes publicly known; (iii) Confidential Information rightfully received by VRG from a third party who is under no obligation of confidence to LENDER (iv) Confidential Information that LENDER has disclosed to other parties without similar obligations of confidentiality; (v) Confidential Information that VRG is required to disclose pursuant to an order of a governmental agency or court of competent jurisdiction, provided that VRG gives LENDER prompt written notice of such request and that VRG only discloses that portion of the Confidential Information which, in the opinion of VRG’s counsel, VRG is compelled to disclose; and (vi) information that is communicated to a third party with the express written consent of LENDER.
(i) In the event any provisions hereof shall be modified or held ineffective by any Court in any respect, such adjudication shall not invalidate or render ineffective the balance of the provisions hereof, and the provisions hereof shall be enforced to the maximum extent allowed by law.
(ii) This Agreement shall be governed by the laws of the State of Texas and shall be enforceable in Dallas County, Texas.
(iii) This Agreement shall be binding upon and inure to the benefit of the parties hereto and their respective successors and assigns.
(iv) The parties hereto have read the terms and conditions of this Agreement before signing the same, and hereby agree that no statement, agreement, or understanding whether oral or written, not contained herein will be recognized or enforced.
(v) This Agreement may not be amended except by a written agreement executed by the parties hereto, which makes specific reference to this Agreement.
(vi) This Agreement may be executed in counterparts, each of which shall be an original, but all of which together shall constitute one and the same original. A facsimile copy of the signature of a person executing this Agreement shall be effective as an original signature.
IN WITNESS WHEREOF, the parties hereto have caused this Agreement to be executed as of the Effective Date.
VALUE RESOURCE GROUP, INC.
By: ______________________________
LENDER BANK
By: ______________________________
Advanced Encryption Standard
AES provides strong encryption and has been selected by NIST as a Federal Information Processing Standard in November 2001 (FIPS-197). In June 2003, the NSA announced that AES is secure enough to protect classified information up to the Top Secret level, which is the highest security level and defined as information which would cause "exceptionally grave damage" to national security if disclosed to the public. The AES algorithm uses one of three cipher key strengths: a 128-, 192-, or 256-bit encryption key (password). Each encryption key size causes the algorithm to behave slightly differently. As such, the increasing key sizes not only offer a larger number of bits with which you can scramble the data but also increases the complexity of the cipher algorithm. With the onslaught of viruses, spam, and spyware problems, a large portion of email servers use software to peer into compressed files to view the index of file names and their extensions. This is much like opening a file drawer to read file folder titles. To keep these servers from identifying the type of file contained within the encrypted compression file, VRG will again compress the encrypted compressed file. The name of this secondary compression will be a random set of characters chosen by VRG. If a server does peer into this compressed file, it will only see another compressed file with a name (that of the control number given by VRG), which will be unintelligible to any party except for VRG and the user to whom the files have been made available under this license.Contact Value Resource Group, Inc. with questions or concerns about our original approach to inventory profiling.