Inventory Cycle Count Audit Workbook

Build a multi-sheet Excel workbook with VLOOKUP, variance flags, and IRA dashboard.

Introduction

30 Second Summary

Every warehouse worker knows the frustration of the system saying there should be 120 parts on the shelf, but the physical count says 115. Finding those discrepancies, figuring out why they happened, and proving your counts are accurate is what separates a reliable inventory operation from one that is always behind.

In this project, you will build a professional Inventory Cycle Count Audit Workbook in Excel that mirrors the logic of enterprise systems like SAP. You will use VLOOKUP formulas to auto-populate part details, flag discrepancies with conditional formatting, and track your facility's accuracy on a live dashboard.

What You'll Build

You enter a part number on the count sheet, and the workbook instantly pulls the part name, storage location, and system quantity from your master data, calculates variances against your physical count, flags items needing recounts in red, and displays your facility's Inventory Record Accuracy percentage on a live dashboard.

By the end of this project, you'll have:

  • A count sheet where you enter a part number and watch VLOOKUP auto-populate the part name, storage location, and system quantity from your Parts Master sheet.
  • Automatic variance calculations with conditional formatting that flags discrepancies in red and marks items for recount.
  • A live accuracy dashboard showing your facility's Inventory Record Accuracy percentage, reason-code breakdown, and an investigation log that mirrors SAP's discrepancy workflow.
  • Secret Mission: Add ABC classification with automated recount frequency recommendations that prioritize high-value, fast-moving parts.

Are there any prerequisites?

You should be comfortable with basic Excel navigation (rows, columns, and formatting). Familiarity with inventory counting concepts like physical counts and system quantities will help, but is not strictly required.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters for your daily work as an Inventory Specialist.

Create the Workbook and Parts Master

Your cycle count workbook needs a foundation before any formulas or lookups can work. That foundation is a Parts Master, a single source of truth that holds every part number, name, location, and quantity in one place.

Without a clean master list, VLOOKUP formulas return errors and physical counts become disconnected from system data. In this step, you'll create the workbook structure and populate the Parts Master with realistic warehouse data.

In this step, get ready to:
  • Create and save the Cycle_Count_Audit workbook with a Parts_Master sheet.
  • Populate the Parts_Master with headers and 15 rows of sample auto parts data.
  • Set up three additional sheets for the count workflow.
Create a new workbook and name the first sheet

Every sheet in your workbook will serve a specific purpose. The first one holds your master parts data, the single reference point all other sheets will pull from.

  • In Excel Online (or Excel Desktop), click Blank workbook to create a new file.
  • Rename the workbook to Cycle_Count_Audit by clicking the default name (usually Book or Book1) in the top title bar and typing the new name.
  • At the bottom of the screen, double-click the Sheet1 tab.
  • Type Parts_Master and press Enter.

You should see the tab at the bottom now reads Parts_Master instead of Sheet1.

Tab not renaming?

Make sure you double-clicked directly on the tab text at the bottom of the screen. If you single-clicked, it just selects the sheet without entering rename mode. Try right-clicking the tab and selecting Rename instead.

help me rename my Excel sheet tab

Set up headers and enter parts data

Your headers define the structure of the master list. Each column captures one piece of information that SAP would store in a material master record.

  • Click cell A1 and type the following headers across Row 1, one per cell:
A1: Part_Number
B1: Part_Name
C1: Location
D1: Unit_Cost
E1: System_Qty
F1: ABC_Class
  • Select all six header cells (A1:F1).
  • Press Cmd+B (macOS) or Ctrl+B (Windows) to bold them.
  • With the headers still selected, click the Data tab in the ribbon.
  • Click Filter to add dropdown arrows to each header cell.

You should see small dropdown arrows appear on each header cell. The headers are bold and ready to filter.

Why add filters now?

Filters let you sort and narrow down your parts list by any column. When you have 15 parts (or hundreds in a real warehouse), you can quickly show only A-class items or only parts in a specific location.

Now enter the 15 rows of sample parts data. This data represents a realistic mix of auto parts classified by ABC inventory classification: high-value fast-movers (A), moderate items (B), and slow-moving parts (C).

  • Starting in cell A2, enter the following data row by row, using the reference table below:
BRK-2010    Brake Pad Set Front     A-01-03    45.99    120    A
BRK-2011    Brake Pad Set Rear      A-01-04    42.50    95     A
FLT-3001    Oil Filter Standard     B-02-01    8.99     340    A
FLT-3002    Air Filter Panel        B-02-02    12.50    280    A
SPK-4010    Spark Plug Iridium      C-03-01    6.75     500    A
BLT-5001    Serpentine Belt         A-02-01    32.00    75     B
BLT-5002    Timing Belt Kit         A-02-02    89.99    40     B
CLT-6001    Coolant Thermostat      B-03-01    18.50    60     B
CLT-6002    Radiator Hose Upper     B-03-02    15.99    55     B
WPR-7001    Wiper Blade 22in        C-01-01    11.99    150    B
GSK-8001    Valve Cover Gasket      D-01-01    24.99    30     C
GSK-8002    Head Gasket Set         D-01-02    125.00   12     C
SEN-9001    O2 Sensor Upstream      D-02-01    65.00    20     C
SEN-9002    Coolant Temp Sensor     D-02-02    19.99    35     C
MNT-1001    Engine Mount Front      D-03-01    55.00    18     C

What does this data represent?

Each row is one stock-keeping item in a parts warehouse. The columns capture what SAP would call the material number, description, storage bin, standard cost, book quantity, and ABC indicator.

A-class items (like brake pads and filters) move fast and get counted most frequently. C-class items (like gaskets and sensors) move slowly and need less frequent verification.

  • After entering all 15 rows, scroll from row 2 to row 16 to confirm no cells are empty or misaligned.

You should see 15 complete rows of data filling columns A through F, with no blank cells.

Data looks misaligned?

If values ended up in the wrong columns, check that you pressed Tab to move between cells in a row and Enter to move down to the next row. Each value belongs in its own cell.

help me fix misaligned data in my Excel Parts Master

Create the remaining sheets

Your workbook needs three more sheets for the count workflow, investigation tracking, and performance metrics.

  • Click the + icon next to the Parts_Master tab at the bottom of the screen to add a new sheet.
  • Double-click the new tab and rename it to Count_Sheet.
  • Click the + icon again to add another sheet.
  • Double-click the new tab and rename it to Investigation_Log.
  • Click the + icon one more time to add a third sheet.
  • Double-click the new tab and rename it to Dashboard.
  • Save your workbook by pressing Cmd+S (macOS) or Ctrl+S (Windows).

Excel Online saves automatically

If you're using Excel Online, your work saves to OneDrive automatically. The Ctrl+S shortcut still works but is optional.

Before you move on, what do you expect to see at the bottom of your screen right now?

You should see four tabs in this order: Parts_Master, Count_Sheet, Investigation_Log, Dashboard. Click on Parts_Master and scroll through all 15 rows. Every row should have six values filled in with no gaps.

Missing a sheet tab?

If you only see three tabs, you may have forgotten to click + for one of the sheets. Click it again and rename the new tab to the missing name. The order of tabs does not affect formulas, so don't worry if they appear in a different sequence.

help me add and rename sheet tabs in my Excel workbook

✔️ Awesome, I've got everything!

Great. Make sure your workbook is saved and you can see all four tabs at the bottom.

ⓧ I'd like to double check the full data

Click on your Parts_Master tab and verify your data matches this reference. Confirm you have these columns in Row 1 (bold, with filter arrows):

Part_Number | Part_Name | Location | Unit_Cost | System_Qty | ABC_Class

Then confirm your 15 data rows (rows 2-16) match:

BRK-2010    Brake Pad Set Front     A-01-03    45.99    120    A
BRK-2011    Brake Pad Set Rear      A-01-04    42.50    95     A
FLT-3001    Oil Filter Standard     B-02-01    8.99     340    A
FLT-3002    Air Filter Panel        B-02-02    12.50    280    A
SPK-4010    Spark Plug Iridium      C-03-01    6.75     500    A
BLT-5001    Serpentine Belt         A-02-01    32.00    75     B
BLT-5002    Timing Belt Kit         A-02-02    89.99    40     B
CLT-6001    Coolant Thermostat      B-03-01    18.50    60     B
CLT-6002    Radiator Hose Upper     B-03-02    15.99    55     B
WPR-7001    Wiper Blade 22in        C-01-01    11.99    150    B
GSK-8001    Valve Cover Gasket      D-01-01    24.99    30     C
GSK-8002    Head Gasket Set         D-01-02    125.00   12     C
SEN-9001    O2 Sensor Upstream      D-02-01    65.00    20     C
SEN-9002    Coolant Temp Sensor     D-02-02    19.99    35     C
MNT-1001    Engine Mount Front      D-03-01    55.00    18     C

Finally, confirm you have three additional empty sheets named Count_Sheet, Investigation_Log, and Dashboard.

Your single source of truth is ready. Next up, you'll build VLOOKUP formulas on the Count Sheet that pull part details from this master automatically.

Build the Count Sheet with VLOOKUP

Your Parts_Master sheet is locked in with 15 rows of clean data. That's your single source of truth for every part in the warehouse.

Now you need a sheet where you actually record physical counts. The problem is that manually copying part names and locations for every count line is slow and error-prone. VLOOKUP eliminates transcription errors by pulling data directly from the master, the same way SAP auto-populates fields when you enter a material number.

In this step, get ready to:
  • Set up the Count_Sheet with nine column headers.
  • Build VLOOKUP formulas that auto-populate part details from the master.
  • Enter count data and verify the lookups work.
Set up the Count_Sheet headers

The Count_Sheet is where you record every physical count. Each column serves a specific purpose in the audit workflow.

  • Switch to the Count_Sheet tab at the bottom of your workbook.
  • Click cell A1 and type Count_Date.
  • In B1, type Part_Number.
  • In C1, type Part_Name.
  • In D1, type Location.
  • In E1, type System_Qty.
  • In F1, type Physical_Qty.
  • In G1, type Variance.
  • In H1, type Variance_Pct.
  • In I1, type Recount_Flag.
  • Select cells A1:I1, then click Bold in the Home tab (or press Cmd+B on macOS, Ctrl+B on Windows).
  • With A1:I1 still selected, click the Data tab, then click Filter.

You should see dropdown arrows appear on each header cell. This confirms your headers are set up and filterable.

Why nine columns?

Columns A through E capture the count context (when, what, and what the system expects). Column F is where you record what you physically counted. Columns G through I calculate whether there's a problem.

You'll build the variance formulas (G, H, I) in the next step. For now, focus on making columns C, D, and E auto-populate from your master data.

Write VLOOKUP formulas to pull part details

VLOOKUP searches for a value in the first column of a range and returns a value from a specified column in the same row. You'll use it to look up a part number and return its name, location, and system quantity.

  • Click cell C2 on the Count_Sheet.
  • Enter this VLOOKUP formula to pull the Part_Name from Parts_Master:
=VLOOKUP(B2,Parts_Master!$A$2:$F$16,2,FALSE)

What does this formula do?

  • B2 is the lookup value. Whatever part number you type in B2, VLOOKUP searches for it.
  • Parts_Master!$A$2:$F$16 is the table range. The dollar signs lock the reference so it doesn't shift when you copy the formula down.
  • 2 is the column index. Column 2 in the range (A through F) is Part_Name.
  • FALSE means exact match. The part number must match exactly, just like entering a material number in SAP.
  • Press Enter to confirm the formula.

You'll see a #N/A error in C2. This is expected because B2 is still empty and there's nothing to look up yet.

  • Click cell D2 and enter this formula to pull the Location (column index 3):
=VLOOKUP(B2,Parts_Master!$A$2:$F$16,3,FALSE)
  • Click cell E2 and enter this formula to pull the System_Qty (column index 5):
=VLOOKUP(B2,Parts_Master!$A$2:$F$16,5,FALSE)

Why column index 5 instead of 4?

The column index counts from the first column in your table range. In Parts_Master columns A through F, column 1 is Part_Number, column 2 is Part_Name, column 3 is Location, column 4 is Unit_Cost, and column 5 is System_Qty. You skip column 4 because you don't need Unit_Cost on the count sheet.

  • Select cells C2:E2.
  • Copy them with Cmd+C (macOS) or Ctrl+C (Windows).
  • Select the range C3:E16 and paste with Cmd+V (macOS) or Ctrl+V (Windows).

All of rows 2 through 16 now have VLOOKUP formulas waiting for part numbers. The dollar signs in your formula keep the table range locked while the B2 reference shifts to B3, B4, and so on.

Enter count data and verify the lookups

Time to test whether your VLOOKUP formulas actually pull the right data. Think of this like entering a material number in SAP's physical inventory screen and watching it auto-fill.

  • Click cell A2 and enter today's date.
  • Click cell B2.

Before you type anything, predict what will happen when you enter a valid part number. What do you expect to see in columns C, D, and E?

  • Type BRK-2010 in B2 and press Enter.

Columns C, D, and E instantly populate with Brake Pad Set Front, A-01-03, and 120. Your VLOOKUP found the part number in the master and pulled the corresponding details automatically.

This mirrors SAP's auto-populate

When you create a physical inventory document in SAP, entering the material number auto-fills the description and storage bin. Your VLOOKUP does the same thing. One input, multiple fields populated.

  • Enter today's date in cells A3 through A6.
  • In B3, type FLT-3001.
  • In B4, type BLT-5001.
  • In B5, type CLT-6001.
  • In B6, type GSK-8001.

Each row should now show the part name, location, and system quantity pulled from Parts_Master. Confirm that each row populated correctly before moving on.

  • In F2, type 120 (matches the system quantity for BRK-2010).
  • In F3, type 335 (differs from the system quantity of 340 for FLT-3001).
  • In F4, type 75 (matches the system quantity for BLT-5001).
  • In F5, type 58 (differs from the system quantity of 60 for CLT-6001).
  • In F6, type 30 (matches the system quantity for GSK-8001).

Why mix matching and non-matching counts?

In a real cycle count, some parts match perfectly and others don't. By entering a mix, you'll be able to test your variance formulas and conditional formatting in the next step. Parts with discrepancies are what trigger investigations.

  • Save your workbook.

Seeing #N/A instead of part details?

  • Check that the part number in column B matches the Parts_Master exactly, including the dash. BRK-2010 is not the same as BRK2010.
  • Verify your formula references Parts_Master!$A$2:$F$16 with dollar signs. Without them, the range shifts when you copy down and points at the wrong rows.
  • Make sure you used FALSE as the last argument. Without it, VLOOKUP defaults to approximate match and can return wrong results.

Help me fix my VLOOKUP formula that's returning #N/A

✔️ Awesome, I've got everything!

Great. Double check you've saved your workbook before continuing.

ⓧ I'd like to double check the full formulas

Here are all three VLOOKUP formulas that should be in Row 2 of your Count_Sheet:

Cell C2: =VLOOKUP(B2,Parts_Master!$A$2:$F$16,2,FALSE)
Cell D2: =VLOOKUP(B2,Parts_Master!$A$2:$F$16,3,FALSE)
Cell E2: =VLOOKUP(B2,Parts_Master!$A$2:$F$16,5,FALSE)

These formulas should be copied down through row 16 in each column (C2:C16, D2:D16, E2:E16).

Your Count_Sheet now auto-populates three fields the moment you enter a part number. No more copying from the master, no more typos in part names. Next up, you'll add error handling and visual flags so discrepancies jump off the page instead of hiding in rows of data.

Handle Errors and Flag Discrepancies

Your Count_Sheet pulls data from the Parts_Master instantly. But in a real warehouse, typos happen constantly.

A single missing dash in a part number causes VLOOKUP to return a #N/A error, which then breaks every downstream formula. In this step, you will add error handling so bad lookups fail gracefully, build variance formulas to quantify discrepancies, and apply conditional formatting so problems jump off the screen instead of hiding in rows of data.

In this step, get ready to:
  • Wrap VLOOKUP formulas in IFERROR so typos show "NOT FOUND" instead of crashing.
  • Build variance and recount-flag formulas that auto-calculate discrepancies.
  • Apply conditional formatting to highlight problem rows in red and yellow.
Trigger the #N/A error and fix it with IFERROR

In SAP, material numbers must be exact. A single character off and the system rejects the entry. Your VLOOKUP behaves the same way. Let's see what happens when a part number is slightly wrong.

  • Switch to the Count_Sheet tab in your workbook.
  • Click on cell B7.
  • Type BRK2010 (notice the missing dash) and press Enter.

Before you look at columns C, D, and E in row 7, what do you think will happen when VLOOKUP tries to find a part number that does not exist in the Parts_Master?

You should see #N/A errors in cells C7, D7, and E7. That is exactly the problem. One typo and your formulas break visibly. If you had variance calculations downstream, they would break too.

Why does this matter?

In a real count session, you might enter dozens of part numbers quickly. A single #N/A cascading through your formulas could corrupt your entire variance analysis without you noticing until it is too late.

The fix is IFERROR. This function wraps any formula and returns a friendly fallback value if the formula produces an error.

  • Click on cell C2. In the formula bar, you will see your existing VLOOKUP:
=VLOOKUP(B2,Parts_Master!$A$2:$F$16,2,FALSE)
  • Replace the formula in C2 with this IFERROR-wrapped version:
=IFERROR(VLOOKUP(B2,Parts_Master!$A$2:$F$16,2,FALSE),"NOT FOUND")

What does this formula do?

IFERROR takes two arguments. The first is the formula to try (your VLOOKUP). The second is what to display if that formula errors out. Here, a failed lookup shows the text "NOT FOUND" instead of a cryptic #N/A.

  • Now click on cell D2 and replace its formula with:
=IFERROR(VLOOKUP(B2,Parts_Master!$A$2:$F$16,3,FALSE),"NOT FOUND")
  • Click on cell E2 and replace its formula with:
=IFERROR(VLOOKUP(B2,Parts_Master!$A$2:$F$16,5,FALSE),"NOT FOUND")
  • Select cells C2, D2, and E2 together.
  • Copy them with Cmd+C (macOS) or Ctrl+C (Windows).
  • Select the range C3:E16 and paste with Cmd+V (macOS) or Ctrl+V (Windows).

Now look at row 7 where you typed the wrong part number. Cells C7, D7, and E7 should display "NOT FOUND" instead of #N/A.

Still seeing #N/A in row 7?

Make sure you pasted the updated formulas down through row 16. If only C2:E2 were updated but the paste did not cover row 7, the old formula without IFERROR is still there.

Double-check that the fallback text is in quotes: "NOT FOUND". Missing quotes will cause a #NAME? error instead.

Need more help?

Help me fix my IFERROR formula that is still showing #N/A

Build the variance formulas

Error handling is in place. Now you need formulas that compare what you physically counted against what the system says should be there. This is the core of any cycle count audit.

  • Click on cell G2. Enter the Variance formula:
=F2-E2

What does this calculate?

Physical_Qty minus System_Qty. A positive number means you found more items than expected. A negative number means items are missing. Zero means a perfect match.

  • Click on cell H2. Enter the Variance Percentage formula:
=IFERROR((G2/E2)*100,0)

Why wrap this in IFERROR too?

If System_Qty (E2) is zero or shows "NOT FOUND" from our earlier error handling, dividing by it would produce a #DIV/0! or #VALUE! error. IFERROR catches that and returns 0 instead.

  • Click on cell I2. Enter the Recount Flag formula:
=IF(ABS(H2)>5,"RECOUNT","OK")

What does ABS do here?

ABS returns the absolute value, ignoring whether the variance is positive or negative. A 10% over-count is just as concerning as a 10% under-count. If the absolute variance percentage exceeds 5%, the item gets flagged for a recount.

  • Select cells G2, H2, and I2 together.
  • Copy them with Cmd+C (macOS) or Ctrl+C (Windows).
  • Select the range G3:I16 and paste with Cmd+V (macOS) or Ctrl+V (Windows).

Look at column I for rows 2 through 6. Rows where the physical count differed from system quantity by more than 5% should show "RECOUNT". Rows that matched or were close should show "OK".

Seeing errors in columns G, H, or I?

If G2 shows an error, confirm that F2 has a numeric physical count value. If you left Physical_Qty blank for some rows, the subtraction will error. Enter a number in column F for any row that has a part number.

If every row shows "RECOUNT", double-check that your H2 formula multiplies by 100. Without it, a 3-unit difference on 120 units appears as 0.025, not 2.5%.

Help me troubleshoot my variance formulas in Excel

Apply conditional formatting to flag problem rows

Numbers alone are easy to miss when scanning a long count sheet. Conditional formatting turns your data into a visual alert system. Red means "stop and investigate." Yellow means "something is off."

  • Select the range I2:I16 (the Recount_Flag column).
  • Click Home in the ribbon.
  • Click Conditional Formatting.
  • Select Highlight Cell Rules, then Text that Contains.
  • Type RECOUNT in the text field.
  • Select a red fill format from the dropdown, then click OK.

Cells in column I containing "RECOUNT" should now have a red fill. Next, apply the same approach to the Variance column.

  • Select the range G2:G16 (the Variance column).
  • Click Home > Conditional Formatting > Highlight Cell Rules.
  • Select Not Equal To.
  • Type 0 in the value field.
  • Select a yellow fill format from the dropdown, then click OK.

Before you review your sheet, what do you expect to see in the rows where your physical count matched the system quantity versus the rows where it differed?

Rows with variances now stand out in yellow in column G. Rows that exceed the 5% threshold also show a red "RECOUNT" flag in column I. Your discrepancies are impossible to miss.

Not seeing any colors?

Make sure you selected the correct range (I2:I16 for red, G2:G16 for yellow) before applying the rule. If you selected the headers by mistake, the rule may be applied to row 1 only.

Confirm that at least one row in your data actually has a variance. If all your Physical_Qty values in F2:F6 happen to match System_Qty exactly, there will be nothing to highlight.

Help me fix my conditional formatting in Excel

✔️ Awesome, I've got everything!

Great. Double-check you have saved your workbook before moving on.

ⓧ I'd like to double check the full code

Here are all the formulas that should be on your Count_Sheet at this point.

Cell C2 (copied to C2:C16):
=IFERROR(VLOOKUP(B2,Parts_Master!$A$2:$F$16,2,FALSE),"NOT FOUND")

Cell D2 (copied to D2:D16):
=IFERROR(VLOOKUP(B2,Parts_Master!$A$2:$F$16,3,FALSE),"NOT FOUND")

Cell E2 (copied to E2:E16):
=IFERROR(VLOOKUP(B2,Parts_Master!$A$2:$F$16,5,FALSE),"NOT FOUND")
Cell G2 (copied to G2:G16):
=F2-E2

Cell H2 (copied to H2:H16):
=IFERROR((G2/E2)*100,0)

Cell I2 (copied to I2:I16):
=IF(ABS(H2)>5,"RECOUNT","OK")

Conditional formatting rules:

  • Column I (I2:I16): cells containing "RECOUNT" have a red fill.
  • Column G (G2:G16): cells not equal to 0 have a yellow fill.

Cell B7 should contain BRK2010 (no dash), and C7, D7, E7 should all display "NOT FOUND".

Your Count_Sheet now catches errors gracefully, calculates variances automatically, and visually flags anything that needs attention. Next up, you will build an Investigation Log that tracks why discrepancies happen and pulls data straight from this sheet using another VLOOKUP pattern.

Build the Investigation Log

Your Count_Sheet now catches errors gracefully and highlights discrepancies in red and yellow. The next question is: what do you do about those flagged variances?

In SAP, every variance gets investigated with a reason code and an assigned owner. Your Investigation Log mirrors this workflow, using VLOOKUP to pull existing data from both Parts_Master and Count_Sheet so you never re-type values that already exist.

In this step, get ready to:
  • Set up headers on the Investigation_Log sheet.
  • Add VLOOKUP formulas that pull Part_Name and Variance automatically.
  • Create dropdown menus for reason codes and status tracking.
Set up the Investigation_Log headers

The Investigation Log needs to capture who investigated what, why it happened, and whether it has been resolved. Each column maps to a field you would find in SAP's physical inventory document workflow.

  • Switch to the Investigation_Log sheet tab at the bottom of your workbook.
  • Click cell A1 and type the following headers across Row 1, one per cell from A to I:
Date_Investigated | Part_Number | Part_Name | Variance_Qty | Reason_Code | Root_Cause_Notes | Assigned_To | Status | Resolution_Date

What do these columns track?

  • Date_Investigated and Part_Number are your manual entry points. Everything else auto-fills or uses dropdowns.
  • Reason_Code mirrors SAP's root-cause categories: receiving errors, picking errors, bin transfers, damage, or other.
  • Status tracks whether the investigation is open, in progress, or resolved.
  • Select all headers in Row 1 (cells A1 through I1).
  • Bold them by pressing Cmd+B (macOS) or Ctrl+B (Windows).
  • With Row 1 still selected, click the Data tab, then click Filter to add filter arrows.

You should see nine bold headers across Row 1 with small filter arrows on each cell.

Add VLOOKUP formulas to pull part and variance data

When you type a part number into column B, you want Excel to automatically fill in the part name and the variance amount. This is VLOOKUP pattern number three in this project: pulling data across sheets so you never copy values by hand.

  • Click cell C2 and enter this formula to pull the Part_Name from Parts_Master:
=IFERROR(VLOOKUP(B2,Parts_Master!$A$2:$F$16,2,FALSE),"")

What does this formula do?

This looks up whatever part number you type in B2, searches the Parts_Master sheet, and returns the Part_Name (column 2). The IFERROR wrapper returns an empty cell instead of an error if B2 is blank or the part number does not exist.

Notice the fallback here is "" (empty) instead of "NOT FOUND" like on Count_Sheet. Since Investigation_Log rows start empty, blank cells look cleaner than 15 rows of "NOT FOUND" text.

  • Now click cell D2 and enter this formula to pull the Variance amount from Count_Sheet:
=IFERROR(VLOOKUP(B2,Count_Sheet!$B$2:$G$16,6,FALSE),"")

What does this formula do?

This searches for B2's part number in Count_Sheet column B, then returns the value from column 6 of that range. Column 6 in the range $B$2:$G$16 is column G, which holds your Variance values.

This is a new VLOOKUP pattern: pulling data from a different working sheet (Count_Sheet) rather than the master data. Now your Investigation Log links directly to the live count results.

  • Test the formulas by typing a part number you already counted in cell B2. Use BRK-2010 (one of the parts on your Count_Sheet).

You should see the Part_Name ("Brake Pad Set Front") appear in C2 and the Variance number from your Count_Sheet appear in D2.

Seeing an empty D2 cell?

If C2 shows the part name but D2 stays empty, the part number you typed may not exist on Count_Sheet. Switch to Count_Sheet and confirm BRK-2010 is in column B. The lookup only works for parts you already counted.

If both C2 and D2 are empty, double-check that B2 contains the part number with the dash (BRK-2010 not BRK2010).

Help me fix my Investigation_Log VLOOKUP formulas

  • Now select cells C2 and D2 together.
  • Copy them with Cmd+C (macOS) or Ctrl+C (Windows).
  • Select the range C3:D16 and paste with Cmd+V (macOS) or Ctrl+V (Windows).

The formulas now cover rows 2 through 16. Since only B2 has a part number, the other rows should show empty cells (not errors).

Add data validation dropdowns and enter investigation records

Reason codes ensure every investigation uses consistent categories that match SAP's root-cause tracking. A dropdown prevents typos and keeps your data filterable.

  • Select the range E2:E16 (the Reason_Code column below the header).
  • Click the Data tab, then click Data Validation.
  • Under Allow, select List.
  • In the Source field, type:
RCV,PICK,BIN,DMG,OTH
  • Click OK to apply the dropdown.
  • Click cell E2 and confirm you see a small dropdown arrow. Click it and verify the five reason codes appear.

What do the reason codes mean?

  • RCV = Receiving error (item scanned in but placed in wrong bin).
  • PICK = Picking error (wrong quantity pulled from bin).
  • BIN = Bin transfer (item moved but system not updated).
  • DMG = Damaged or lost inventory.
  • OTH = Other (requires notes in Root_Cause_Notes).
  • Now select the range H2:H16 (the Status column below the header).
  • Click Data tab, then Data Validation.
  • Under Allow, select List.
  • In the Source field, type:
Open,In Progress,Resolved
  • Click OK to apply.
  • Click cell H2 and confirm the dropdown arrow appears with three status options.

Now fill in 2-3 investigation records for parts that showed variances on your Count_Sheet.

  • In A2, enter today's date.
  • In B2, you should already have BRK-2010 from your earlier test. If not, type it now.
  • In E2, select PICK from the dropdown.
  • In F2, type a brief note like: Picker pulled wrong qty on 2nd shift.
  • In G2, type your name or initials.
  • In H2, select Open from the dropdown.
  • Repeat for rows 3 and 4 using two other part numbers from your Count_Sheet that had variances. Choose different reason codes for each (try BIN and RCV).

Before you check the results, predict what you expect to see: do you think the Part_Name and Variance_Qty columns auto-populated for rows 3 and 4 as soon as you entered the part numbers?

Check cells C3, D3, C4, and D4. Each should show the part name and variance value pulled automatically by your VLOOKUP formulas.

Dropdown not appearing?

If you do not see the dropdown arrow when clicking a cell in column E or H, confirm you applied Data Validation to the correct range. Click the cell, go to Data > Data Validation, and check that the Source field contains your comma-separated list.

If your region uses a semicolon as the list separator, try replacing commas with semicolons in the Source field.

Help me set up Data Validation dropdowns in my Investigation_Log

✔️ Awesome, I've got everything!

Great. Double check that your formulas are saved and your dropdowns work in both columns E and H.

ⓧ I'd like to double check the full code

Here are all the formulas on your Investigation_Log sheet:

Cell C2 (copy down to C16):
=IFERROR(VLOOKUP(B2,Parts_Master!$A$2:$F$16,2,FALSE),"")

Cell D2 (copy down to D16):
=IFERROR(VLOOKUP(B2,Count_Sheet!$B$2:$G$16,6,FALSE),"")

Column E Data Validation (E2:E16):
Allow: List
Source: RCV,PICK,BIN,DMG,OTH

Column H Data Validation (H2:H16):
Allow: List
Source: Open,In Progress,Resolved

Row 1 headers (bold, filtered): Date_Investigated (A), Part_Number (B), Part_Name (C), Variance_Qty (D), Reason_Code (E), Root_Cause_Notes (F), Assigned_To (G), Status (H), Resolution_Date (I).

Your Investigation Log now tracks every discrepancy with a reason code, an owner, and a status. Next up, you will build a Dashboard that rolls all of this data into accuracy metrics your supervisor can see at a glance.

Build the Accuracy Dashboard

Your Investigation Log captures why discrepancies happen. But individual investigations don't answer the bigger question your supervisor cares about: how accurate is the warehouse overall?

A dashboard answers that question with a single metric: Inventory Record Accuracy (IRA). This is the number auditors look at. Building it proves you think beyond individual counts and understand how the whole facility performs.

In this step, get ready to:
  • Create core accuracy metrics using COUNTA and COUNTIF formulas.
  • Build a reason code breakdown that counts each root-cause category.
  • Format the dashboard and verify it updates live when count data changes.
Create the core accuracy metrics

The dashboard needs to answer three questions at a glance: how many parts were counted, how many matched, and what percentage hit the mark.

  • Switch to the Dashboard sheet tab at the bottom of your workbook.
  • Click cell A1 and type CYCLE COUNT AUDIT DASHBOARD.
  • Enter the following labels in column A:
A3: Total Parts Counted:
A4: Parts with Zero Variance:
A5: Parts with Discrepancy:
A6: Inventory Record Accuracy (IRA):
  • Now enter the counting formulas in column B. Click cell B3 and type this formula:
=COUNTA(Count_Sheet!B2:B16)
  • Press Enter. You should see a number appear (the count of parts you entered on the Count_Sheet).
  • Enter the remaining three formulas in cells B4, B5, and B6:
Cell B4: =COUNTIF(Count_Sheet!G2:G16,0)
Cell B5: =COUNTIF(Count_Sheet!G2:G16,"<>0")
Cell B6: =IFERROR(B4/B3*100,0)

What do these formulas do?

Each formula pulls live data from your Count_Sheet without you needing to count anything manually.

  • COUNTA counts non-empty cells. It tells you how many parts have been counted so far.
  • COUNTIF with 0 counts parts where variance is zero (perfect matches).
  • COUNTIF with "<>0" counts parts with any variance (mismatches).
  • The IRA formula divides perfect matches by total counted, wrapped in IFERROR so it shows 0 instead of an error when no parts have been counted yet.
  • Select cell B6 and format it as a percentage: go to Home > Number Format and choose the percentage style, or leave it as a number that represents percentage (e.g. 80 means 80%).

You should see numbers in B3 through B6. B6 shows your IRA percentage based on the count data you entered earlier.

What is IRA?

Inventory Record Accuracy (IRA) measures what percentage of counted parts matched the system quantity exactly. Warehouses running structured cycle counts target 97% or higher per WERC (Warehousing Education and Research Council) benchmarks.

This is the same metric SAP's Difference Analyzer tracks when reviewing physical inventory results before posting to ERP.

Seeing 0 or an error in B3?

  • Make sure you typed the sheet reference exactly as Count_Sheet!B2:B16 with the underscore and exclamation mark.
  • Check that your Count_Sheet has part numbers entered in column B (rows 2 onward). If those cells are empty, COUNTA returns 0.
  • Confirm the sheet tab is named exactly Count_Sheet (with the underscore). A mismatch causes a #REF! error.

Help me fix my COUNTA formula on the Dashboard

Add the reason code breakdown

Knowing your IRA is 80% tells you there is a problem. Knowing that most variances come from picking errors tells you where to fix it. The reason code breakdown gives that visibility.

  • Click cell A8 and type Reason Code Breakdown.
  • In cells A9 through A13, enter each reason code on its own row:
A9: RCV
A10: PICK
A11: BIN
A12: DMG
A13: OTH
  • Now enter COUNTIF formulas in B9 through B13 to count each code from the Investigation_Log:
Cell B9: =COUNTIF(Investigation_Log!E2:E16,"RCV")
Cell B10: =COUNTIF(Investigation_Log!E2:E16,"PICK")
Cell B11: =COUNTIF(Investigation_Log!E2:E16,"BIN")
Cell B12: =COUNTIF(Investigation_Log!E2:E16,"DMG")
Cell B13: =COUNTIF(Investigation_Log!E2:E16,"OTH")

What do these formulas do?

Each COUNTIF looks at the Reason_Code column (E) in your Investigation_Log and counts how many times that specific code appears. This mirrors how SAP's Difference Analyzer categorizes variances by root cause before posting adjustments.

You should see numbers appear in B9 through B13. The codes you used in your investigation records (from the previous step) show their counts. Codes you haven't used yet show 0.

All reason code counts showing 0?

  • Check that the sheet reference says Investigation_Log!E2:E16 with the underscore between Investigation and Log.
  • Verify that your Investigation_Log has reason codes selected in column E. If those cells are empty, COUNTIF correctly returns 0.
  • The reason code text must match exactly. RCV in the formula must match RCV in the dropdown (case-sensitive).

Help me debug my reason code COUNTIF formulas

Add recounts metric, format, and verify live updates

One more metric ties the dashboard back to daily action: how many parts currently need a recount. Then you will format the dashboard so the key numbers stand out at a glance.

  • Click cell A15 and type Recounts Required:.
  • In cell B15, enter this formula:
=COUNTIF(Count_Sheet!I2:I16,"RECOUNT")

What does this formula do?

This counts how many parts on your Count_Sheet have a Recount_Flag of "RECOUNT" (meaning their variance exceeded 5%). It gives you an instant to-do list for the next count cycle.

You should see a number in B15 matching how many of your counted parts had variances over 5%.

  • Now format the dashboard. Select all label cells in column A (A1, A3 through A6, A8, A9 through A13, A15) and apply Bold from the Home ribbon.
  • Select cell B6 (your IRA percentage) and increase the font size to 14 or 16 so it stands out as the headline metric.
  • Select all metric cells in column B (B3 through B6, B9 through B13, B15) and add borders: go to Home > Borders > All Borders.
  • Save your workbook.

Your dashboard should now look clean and scannable with bold labels, a prominent IRA percentage, and bordered metric cells.

Before you test the live update, predict: if you go back to the Count_Sheet and change a Physical_Qty to match the System_Qty exactly, which dashboard numbers do you expect to change?

  • Switch to the Count_Sheet tab.
  • Find a row where the Physical_Qty (column F) differs from the System_Qty (column E). Change the Physical_Qty value to match the System_Qty exactly.
  • Switch back to the Dashboard tab.

You should see the "Parts with Zero Variance" count increase by one, the "Parts with Discrepancy" count decrease by one, and the IRA percentage go up. The "Recounts Required" number also drops if that part's flag changed from RECOUNT to OK.

Why does this matter?

Live-updating metrics mean you never have to manually recalculate accuracy. As your team completes counts throughout the day, you can open this dashboard and instantly see where the warehouse stands. This is the same principle behind SAP's real-time inventory analytics.

Dashboard numbers not updating?

  • Make sure you pressed Enter after changing the Physical_Qty value. The cell must be confirmed, not still in edit mode.
  • Check that the Variance formula in column G still works for that row. The variance should now show 0 for the row you changed.
  • If using Excel Desktop, press Ctrl+Shift+= or F9 to force a full recalculation if automatic calculation is turned off.

Help me fix my Dashboard formulas that are not updating

✔️ Awesome, I've got everything!

Great. Double check you've saved your workbook with all the Dashboard formulas and formatting in place.

ⓧ I'd like to double check the full code

Here is everything your Dashboard sheet should contain:

A1: CYCLE COUNT AUDIT DASHBOARD

A3: Total Parts Counted:              B3: =COUNTA(Count_Sheet!B2:B16)
A4: Parts with Zero Variance:         B4: =COUNTIF(Count_Sheet!G2:G16,0)
A5: Parts with Discrepancy:           B5: =COUNTIF(Count_Sheet!G2:G16,"<>0")
A6: Inventory Record Accuracy (IRA):  B6: =IFERROR(B4/B3*100,0)

A8: Reason Code Breakdown
A9: RCV     B9: =COUNTIF(Investigation_Log!E2:E16,"RCV")
A10: PICK   B10: =COUNTIF(Investigation_Log!E2:E16,"PICK")
A11: BIN    B11: =COUNTIF(Investigation_Log!E2:E16,"BIN")
A12: DMG    B12: =COUNTIF(Investigation_Log!E2:E16,"DMG")
A13: OTH    B13: =COUNTIF(Investigation_Log!E2:E16,"OTH")

A15: Recounts Required:  B15: =COUNTIF(Count_Sheet!I2:I16,"RECOUNT")

Formatting: All labels bold, B6 font size 14-16, borders on all B column metric cells.

Secret mission

Add ABC Priority and Recount Scheduling

SAP assigns cycle count frequency based on ABC classification. A-items get counted every 5 working days, while C-items only get counted every 20. Can you add automated scheduling logic that tells you which parts are due for counting this week?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely in Excel, so there are no ongoing costs or cloud resources to worry about. Decide whether to keep your workbook as-is, save a backup, or delete it entirely.

Resources you used:

  • The Cycle_Count_Audit Excel workbook (4 sheets: Parts_Master, Count_Sheet, Investigation_Log, Dashboard).
  • Excel Online (via OneDrive) or Excel Desktop application.

Keep everything running

No action needed. Choose this if you plan to continue refining the workbook or want to bring it into work as a reference.

  • Your Cycle_Count_Audit workbook stays saved in OneDrive (if using Excel Online) or in the folder where you saved it locally.
  • Consider renaming the file with today's date (e.g. Cycle_Count_Audit_2026-07-23) so you can find it later.

Pause - I'll come back to this later

Save a clean copy and close. Since this is a local file with no running processes, pausing just means making sure your work is saved.

  • Save the workbook one final time to make sure all changes are captured.
  • If you are using Excel Online, your file auto-saves to OneDrive. Confirm you can see it in your recent files.
  • If you are using Excel Desktop, note the file path so you can find the workbook when you return.

Delete - I don't want to use this again

Remove the workbook file entirely. This frees up space and removes the practice data.

  • If using Excel Online, open OneDrive in your browser. Right-click the Cycle_Count_Audit file and select Delete.
  • If using Excel Desktop on macOS, open Finder, navigate to the folder where you saved the file, right-click Cycle_Count_Audit.xlsx, and select Move to Trash.
  • If using Excel Desktop on Windows, open File Explorer, navigate to the folder where you saved the file, right-click Cycle_Count_Audit.xlsx, and select Delete.

Nice Work!

Nice Work!

You just built a professional Inventory Cycle Count Audit Workbook from scratch. This is the kind of tool that turns daily counting into structured, data-driven auditing.

You've learned how to:

  • Use VLOOKUP in four different patterns: pulling part details from a master, cross-referencing count data, handling errors with IFERROR, and linking investigation records across sheets.
  • Create automatic variance flagging with conditional formatting that mirrors SAP's discrepancy workflow, so problems jump out visually instead of hiding in rows of data.
  • Build a live accuracy dashboard with an IRA percentage and reason-code breakdown that updates automatically when count data changes.
  • Secret Mission: Added ABC-based recount scheduling that automates count prioritization based on item classification and frequency intervals.

Ready to quiz yourself?