Inventory Control, Ledger Reconciliation & Pivot Cross-Checks

Who this is for

Stage 6: Financial Reporting, Reconciliation & Executive Scorecards — Designed for learners mastering perpetual stock rollforward, reorder points & moq/pack pos, days on hand (doh), obsolescence reserves & abc pareto share, two-system xlookup reconciliation & pivottable cross-tabs, pmt/pv lease schedules, true npv/irr & working capital buffers through interactive spreadsheet examples.

What you will practise

• Compute Book Closing Stock in F2:F5 as Opening (B) + Receipts (C) - Issues (D), then compute Cycle-Count Variance in G2:G5 as Physical Count (E) - Book Closing (F).
• Calculate Reorder Point in F2:F5 as B2 * C2 + D2, then flag "Reorder Now" when On-Hand (E2) <= Reorder Point (F2), else "OK" in G2:G5.
• Compute Raw Replenishment Deficit in G2:G5 (=IF(B2 > C2, 0, D2 - B2)), then compute Suggested PO Qty in H2:H5 using IF, MAX, and CEILING.
• Compute guarded DOH in D2:D5 (=IF(C2 = 0, 999, ROUND(B2 / C2, 1))), then classify Stockout Risk in E2:E5 via IFS (<7 "Critical", <21 "Watch", <=60 "Healthy", TRUE "Excess").
• Compute Days Idle in D2:D5 via DAYS($G$2, B2), calculate Write-Down Reserve in E2:E5 via ROUND(C2 * IFS(...), 2), and sum Total Reserve in E6.
• Compute Cumulative Value Share in D2:D6 via ROUND(SUM($C$2:C2) / SUM($C$2:$C$6), 4), then classify ABC tiers in E2:E6 via IFS (<=0.70 "A", <=0.90 "B", TRUE "C").
• Look up Bank Settled Amount into C2:C5 via XLOOKUP with fallback "Missing", then classify Reconciliation Status in D2:D5 into "Missing", "Matched", or "Amount Diff".
• Compute Days to Cutoff in E2:E5 (=$G$2 - B2), then classify F2:F5 into "Cleared", "In-Transit Timing" (when D2 = 0 AND E2 <= $G$3), or "True Discrepancy".

Your learning path

1. Flag & Count Values NOT Between X and Y (OR & Two-Tail COUNTIF)

   Detect cold-chain, blood-glucose, or machining readings that fall strictly outside an acceptable [Min, Max] window using OR(B2 < MIN(X,Y), B2 > MAX(X,Y)) and count total two-sided excursions via COUNTIF.

2. Combine Duplicate Rows & Sum Values Non-Destructively (UNIQUE, COUNTIF & SUMIFS)

   Consolidate repeated SKU or customer line items into one summary row per key while preserving raw transaction audit trails and reconciling grand totals.

3. Non-Volatile Dynamic N-Period Sum & Average Without OFFSET (SUMIFS & AVERAGEIFS)

   Replace slow volatile SUM(OFFSET(B2,0,0,N,1)) rolling windows with dependency-tracked SUMIFS(B2:B7, A2:A7, "<=" & E2) and AVERAGEIFS for instant multi-sheet recalculation.

4. Highlight & Count Values Between Two Bounds: Order-Safe MIN/MAX & COUNTIFS

   Test whether numbers or dates fall inside [Lower, Upper]—even if a user types Upper before Lower—using AND(B2 >= MIN($E$2,$E$3), B2 <= MAX($E$2,$E$3)) and COUNTIFS.

5. Sum timesheet hours by week and project (7-day window SUMIFS)

   Roll up daily employee time entries into weekly project totals using half-open [WeekStart, WeekStart + 7) date criteria.

6. Two-way summary matrix with SUMIFS and COUNTIFS (live cross-tab table)

   Build a live PivotTable-style Region x Category grid that updates automatically without manual refresh using mixed references ($E2 and F$1).

7. Average & Sum If Companion Column Is Not Blank (and Exclude Zero Placeholders) with AVERAGEIFS

   Average or sum values in column C only where companion column B is populated ("<>") while optionally excluding unrecorded 0 placeholders ("<>0") without triggering #DIV/0!.

8. Find highest & lowest value on a specific day of week (WEEKDAY, MAXIFS & MINIFS)

   Use a Monday-first weekday helper to find maximum and minimum numeric readings, while distinguishing no matching dates from a genuine zero result.