Nov - Top Inventory Review

Mango Report
Help Center

Search:

Contents

IndexBookmarkPrint

Home > Accuracy Cadence > Nov - Top Inventory Review

Accuracy Cadence - Top Inventory Review

Excel Mango IVA (Inventory Value Analysis)

The Mango IVA (Inventory Value Analysis) workbook is a fantastic tool to understand and improve on your store's system inventory value. Many stores use this tool to help ascertain a realistic inventory value for the purposes of buy/sell and tax auditing. There are separate worksheet tabs which call out the SKU-level detail for each inventory value classification (Errors, Store Supply, Negative Value, Placeholder, Unproductive, Overstock) and a Top 500 tab detailing the top 500 inventory value SKUs. As your store processes its monthly Count Sheets and Exception Reports (completion is shown in cell Overview:C12), it is verifying and improving these classifications.

Your IVA report is located in your store's Excel directory online. Click here to learn how to get online (it's easy).

Steps to process this report:

Look at the Overview worksheet tab
If there is significant value in Cost Errors & Store Supply (Overview:C5) then fix the SKU-level detail in corresponding worksheet tabs: Errors, Store Supply.
Verify Placeholder value (SKU-level detail in Placeholder tab). If fastener value then change Placeholder SKU Class 580 in Inventory Maintenance.
Verify Fasteners, Keys & Bulk value (Overview:F5), use 8% of total inventory value as a guideline.
Look at Unproductive and Overstock values and underlying worksheet tabs. Correct any errors and/or devise strategy to reduce if above benchmark.
Review/correct SKUs listed in Top 500 worksheet tab. These SKUs should have a recent Last Physical Inventory Date (if your store is achieving high Count Sheet Completion).

Note: significant adjustments to inventory value should be made with owner approval as adjustments will have a dollar-for-dollar affect on taxable gross profit.

Overview Tab Detail

B5 RIV: Your store’s total inventory value (QOH times Cost for every SKU) excluding Kit Records and Negative Value.

C5 Cost Errors and Store Supply: SKUs identified by Mango as either a Cost/QOH Error or Store Supply (These are also listed in your Store’s Exception Reports).

D5 Placeholder Inventory: This column shows unclassified Placeholder inventory. Placeholder SKUs are large value SKUs (usually $10,000 to $50,000) which represent an entire class or department of inventory value for which individual SKU Stock Info is not kept. For example, many stores do not keep stock info (QOH's) in their Nuts and Bolts departments (fasteners). However, the entire inventory value of this department is represented using one "placeholder" SKU (usually HILLMAN or FASTENER). Ideally any placeholder SKUs in the fastener and other bulk departments will have a relevant (Ace Catalog) class attached in Inventory Maintenance (such as Class 580) so they can be classified properly into the Fasteners, Keys & Bulk section of this report. This column is for unclassified Placeholder SKUs. For example, an entire Radio Shack store within a store may be represented here with one SKU. Inventory value within this category will have to be independently verified as it is outside of Mango's Count Sheet process to account for the validity of this value.

E5 Unproductive Inventory: Inventory value contained in SKUs that have not sold in two years or more (Obsolete) along with Overstock (more than two years of supply).

F5 Fasteners, Keys & Bulk: Inventory value explicitly in fasteners, Keys or other bulk classes. Non-Ace SKUs must be in one of Ace's Catalog Classes, otherwise these SKUs will be tallied under Unproductive Inventory or Productive Non-Ace. Ideally, all non-Ace Fastener and Bulk SKUs will be classified according to their appropriate Ace Catalog classification:
Key Class: 510
Fastener Classes: 561, 564, 566, 578, 580
"Bulk" Classes: 151, 152, 310, 404, 466, 478, 546, 570, 573, 575, 562

G5 Productive Packaged Ace: This is the value of non-bulk, productive (sales within the last two years) Ace SKU'ed inventory.

G5 Productive Non-Ace: This is the value of non-bulk, productive (sales within the last two years) Non-Ace SKU'ed inventory.

I5 Lumber: The value of SKUs with Record Type "L" - Lumber.

Using the Baseline Inventory Value Worksheet to determine an internal ending inventory value
In this worksheet, adjustments to ending inventory account for errors, store supply, likely total shrinkage and discounts to overstock and unproductive inventory. The resultant value should be used for internal use only and should not be used for tax or other accounting purposes. The worksheet assumes good Count Sheet Completion (reported in C:12) which helps determine and verify shrinkage, overstock and unproductive inventory value.

C14:	Any SKUs containing negative inventory value (negative quantity on hands) are excluded from your starting inventory value.
C15:	This is your system ending inventory calculated by multiplying each SKU's quantity on hand by its cost.
C16:	The value contained within SKUs Mango has classified as a Cost/QOH Error or is Store Supply SKU (these items are not inventory).
C17:	Overstock is recent or recently selling inventory with more than two years of supply on hand. the entire value of your store's Overstock category is subtracted at this point.
C18:	Obsolete inventory value (SKUs that have not sold in over two years) is subtracted.
C19:	Inventory classified as Fasteners, Keys or Bulk which exceeds 8% of your store's total inventory value (C15 - C16) is subtracted here as it is unlikely the physical value exceeds 8% (it would need to be determined independently).
C20:	Totals the deductions from system inventory (C15).

C22:	Shows the resultant "baseline" inventory after the above deductions are taken.
C23:	Calculates the worst case expected shrinkage if the store were entirely counted down. It is based on your store's Count Sheet net dollar variance over the past 3 months. This is "worst case" because Mango's Count Sheets are focusing on the most risky classes of inventory and therefore your true shrinkage would likely be less when counting a less risky population of SKUs.
C24:	Here we are adding back in 1/2 of your store's Overstock value from C17. Effectively discounting this category by 50%.
C25:	Unproductive value (C18) is added back in at a discounted value of 25%.
C26:	This is the net ending inventory value calculated by this method. In many cases, this value will be a reasonable starting point for obtaining a realistic inventory value.

C29:	SKUs trapped as unclassified placeholders need to be independently verified. If they are misclassified fastener placeholders then their value should be added into the Fastener, Keys & Bulk 8% threshold.
C30:	Contains the value in Lumber (record type "L") SKUs. This category is included in the above worksheet values but should be independently verified as lumber tends to be a fast-moving and noisy class of SKUs.