Inventory tracker

What you will build

Build a small inventory tracker for three SKUs. You will calculate Closing stock and a Reorder status while keeping the source values intact.

Learning objective

Use row-level arithmetic and IF logic to calculate current stock, flag items at or below their reorder level, and verify that the formulas react correctly when an input changes.

Prepare the sample data

Open the practice workbook or make a copy. On the Example sheet, confirm that A1:E4 matches the sample below before entering any formulas.

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

The Example sheet shows the three-SKU source table in A1:E4 before formulas are added.

Calculate closing stock

In F1 enter Closing stock. In F2 enter the formula below, then fill it down through F4 so each SKU uses its own row values.

=B2+C2-D2

How this formula works=B2+C2-D2

Overall meaning of the whole formula
- Calculate ending inventory as beginning inventory plus incoming quantity minus outgoing quantity for the current row.
Cell references and operators
- The expression `=B2+C2-D2` uses cell references and operators directly; each reference first supplies the current value of its cell.
- Addition and subtraction follow normal expression evaluation; there is no hidden function or extra calculation step.
Whole-expression evaluation
- Opening stock in B2 is the starting balance for the SKU.
- Received stock in C2 is added and Sold stock in D2 is subtracted.
- The relative row references let the same balance formula be filled down for P02 and P03.
Why the displayed final result appears
- The relative row references let the same balance formula be filled down for P02 and P03.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Flag reorder items

In G1 enter Reorder status. In G2 enter the formula below, then fill it down through G4. The comparison includes items that are exactly at the reorder level.

=IF(F2<=E2,"Reorder","OK")

Cell G2 is selected with the IF formula visible in the formula bar and the status shown as OK.

How this formula works=IF(F2<=E2,"Reorder","OK")

Overall meaning of the whole formula
- Compare current stock F2 with threshold E2: return "Reorder" when F2 is at or below the threshold; otherwise return "OK".
IF(logical_expression, value_if_true, [value_if_false])
- `logical_expression` is required; it accepts an expression that evaluates to TRUE or FALSE. In this lesson the argument passed is `F2<=E2`.
- `value_if_true` is required; it accepts the value or expression returned when the test is TRUE. In this lesson the argument passed is `"Reorder"`.
- `value_if_false` is optional; it accepts the value or expression returned when the test is FALSE. In this lesson the argument passed is `"OK"`.
Whole-expression evaluation
- F2 is the calculated closing stock for the row.
- E2 is the reorder level for the same SKU.
- <= treats both below-threshold and exactly-at-threshold stock as Reorder; otherwise IF returns OK.
Why the displayed final result appears
- <= treats both below-threshold and exactly-at-threshold stock as Reorder; otherwise IF returns OK.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Checkpoint

SKU	Opening	Received	Sold	Reorder level	Closing stock	Reorder status
P01	20	10	12	10	18	OK
P02	15	0	9	8	6	Reorder
P03	8	6	7	5	7	OK

After filling the formulas through row 4, these are the expected values. P02 is the only sample item that needs reordering.

Test that the formulas react

Temporarily change Received for P01 in C2 from 10 to 0. Closing stock changes from 18 to 8 and the status changes from OK to Reorder. Restore C2 to 10; the results return to 18 and OK.

Common mistakes and fixes

Independent practice

Add a fourth SKU in row 5 with realistic Opening, Received, Sold and Reorder level values. Fill the formulas into F5 and G5, then explain why the item shows Reorder or OK.

Key takeaways

✓Keep the original inventory inputs separate from calculated columns.
✓Closing stock is Opening + Received - Sold.
✓Use <= when the reorder rule should include the threshold itself.
✓Fill formulas down so each SKU uses its own row references.
✓Change one input to test recalculation, then restore the sample before finishing.

Continue learning

Previous lesson: Weekly task planner. Next lesson: Simple CRM spreadsheet.

What you will build

Build a small inventory tracker for three SKUs. You will calculate Closing stock and a Reorder status while keeping the source values intact.

Learning objective

Use row-level arithmetic and IF logic to calculate current stock, flag items at or below their reorder level, and verify that the formulas react correctly when an input changes.

Prepare the sample data

Open the practice workbook or make a copy. On the Example sheet, confirm that A1:E4 matches the sample below before entering any formulas.

HANDS-ON PRACTICE

Practice in your own copy

Make your own Google Sheets copy with the same data, formulas, formatting, filter and frozen header. The original remains unchanged.

Make a Google Sheets copy (access required) ↗View original

Sign in to Google. If the original is not publicly shared, download the full workbook and import it into Google Sheets, or paste the example data into your own sheet.

Calculate closing stock

In F1 enter Closing stock. In F2 enter the formula below, then fill it down through F4 so each SKU uses its own row values.

=B2+C2-D2

How this formula works=B2+C2-D2

Overall meaning of the whole formula
- Calculate ending inventory as beginning inventory plus incoming quantity minus outgoing quantity for the current row.
Cell references and operators
- The expression `=B2+C2-D2` uses cell references and operators directly; each reference first supplies the current value of its cell.
- Addition and subtraction follow normal expression evaluation; there is no hidden function or extra calculation step.
Whole-expression evaluation
- Opening stock in B2 is the starting balance for the SKU.
- Received stock in C2 is added and Sold stock in D2 is subtracted.
- The relative row references let the same balance formula be filled down for P02 and P03.
Why the displayed final result appears
- The relative row references let the same balance formula be filled down for P02 and P03.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Flag reorder items

In G1 enter Reorder status. In G2 enter the formula below, then fill it down through G4. The comparison includes items that are exactly at the reorder level.

=IF(F2<=E2,"Reorder","OK")

How this formula works=IF(F2<=E2,"Reorder","OK")

Overall meaning of the whole formula
- Compare current stock F2 with threshold E2: return "Reorder" when F2 is at or below the threshold; otherwise return "OK".
IF(logical_expression, value_if_true, [value_if_false])
- `logical_expression` is required; it accepts an expression that evaluates to TRUE or FALSE. In this lesson the argument passed is `F2<=E2`.
- `value_if_true` is required; it accepts the value or expression returned when the test is TRUE. In this lesson the argument passed is `"Reorder"`.
- `value_if_false` is optional; it accepts the value or expression returned when the test is FALSE. In this lesson the argument passed is `"OK"`.
Whole-expression evaluation
- F2 is the calculated closing stock for the row.
- E2 is the reorder level for the same SKU.
- <= treats both below-threshold and exactly-at-threshold stock as Reorder; otherwise IF returns OK.
Why the displayed final result appears
- <= treats both below-threshold and exactly-at-threshold stock as Reorder; otherwise IF returns OK.
- The final output must be read against the lesson’s visible sample data; this explanation does not change the formula, references, or criteria used in the lesson.

Checkpoint

SKU	Opening	Received	Sold	Reorder level	Closing stock	Reorder status
P01	20	10	12	10	18	OK
P02	15	0	9	8	6	Reorder
P03	8	6	7	5	7	OK

After filling the formulas through row 4, these are the expected values. P02 is the only sample item that needs reordering.

Test that the formulas react

Temporarily change Received for P01 in C2 from 10 to 0. Closing stock changes from 18 to 8 and the status changes from OK to Reorder. Restore C2 to 10; the results return to 18 and OK.

Common mistakes and fixes

Independent practice

Add a fourth SKU in row 5 with realistic Opening, Received, Sold and Reorder level values. Fill the formulas into F5 and G5, then explain why the item shows Reorder or OK.

Key takeaways

✓Keep the original inventory inputs separate from calculated columns.
✓Closing stock is Opening + Received - Sold.
✓Use <= when the reorder rule should include the threshold itself.
✓Fill formulas down so each SKU uses its own row references.
✓Change one input to test recalculation, then restore the sample before finishing.

Continue learning

Previous lesson: Weekly task planner. Next lesson: Simple CRM spreadsheet.

Inventory tracker

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Calculate closing stock

Flag reorder items

Checkpoint

Test that the formulas react

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments

Inventory tracker

What you will build

Learning objective

Prepare the sample data

Practice in your own copy

Calculate closing stock

Flag reorder items

Checkpoint

Test that the formulas react

Common mistakes and fixes

Independent practice

Key takeaways

Check your understanding

Continue learning

What should you learn next?

Comments