Mango Report
Help Center
Search:
Contents
:
IndexBookmarkPrint

Home > Excel Reports > Mango IVA

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:
  1. Look at the Overview worksheet tab
  2. 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.
  3. Verify Placeholder value (SKU-level detail in Placeholder tab). If fastener value then change Placeholder SKU Class 580 in Inventory Maintenance.
  4. Verify Fasteners, Keys & Bulk value (Overview:F5), use 8% of total inventory value as a guideline.
  5. Look at Unproductive and Overstock values and underlying worksheet tabs. Correct any errors and/or devise strategy to reduce if above benchmark.
  6. 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

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.