Executive Summary: Analytical Procedures
This dashboard synthesizes the findings from the Inventory Audit Lab. It addresses key audit inquiries regarding valuation, segment performance, and high-risk inventory items.
Inventory Health Distribution
Breakdown based on Internal Control Logic (Part 1)
Departmental Core Analysis
Manager's Challenge: Segments vs $50k Threshold (Part 5)
Inventory Workpaper
Detailed listing with applied audit formulas.
| Item Detail | Dept Code | Metrics (End/Cost) | Internal Controls | Reconciliation | Manager's Assess |
|---|
Formula Reference Library
This section maps the audit logic to the specific Excel syntax used in the answer key.
Part 1: Internal Controls
=IF(F2 < 10, "Reorder", "Sufficient")
Identifies inventory falling below safety stock levels.
=IF(F2 > 60, "Overstock", IF(F2 < 5, "Critical Low", "Normal"))
Categorizes inventory health into three buckets.
Part 2 & 3: Recon & Enrichment
=IF((Beg + Purch - Sold) = End, "Verified", "Error")
Validates the Ending Stock figure provided by the client.
=XLOOKUP(Name, MasterTable[Name], MasterTable[Code], "Pending")
Links individual items to their Department via Master File.
Part 5: Manager's Challenge (Advanced)
=IF(SUMIFS(TotalSales, DeptCol, CurrentDept) > 50000, "Core Segment", "Non-Core")
Logic Explained:
This formula doesn't just look at the row. It calculates the total sales for the entire department the item belongs to. If that department generates over $50k total, every item in that department gets flagged as "Core Segment".