Excel Inventory Template: Stock, Purchase Order and Sales Tracker

What's inside

The workbook has 7 sheets. You only type on a few of them; the rest calculate.

Before you start

The template works as soon as you open it. Cell colors tell you what each cell is for:

1. Open the file. If Excel shows a yellow Protected View bar, click Enable Editing. Files downloaded from the internet always open this way.
2. Save your own copy with File› then Save As so you always have the original to start over from.
3. Clear the sample data. Replace the three sample products on Products with your own, then clear the sample rows on Stock Movements, Sales and Purchase Orders. Only clear the pale-yellow cells; the green ones recalculate.

Step-by-step guide

Work through these in order the first time. After that, you'll mostly repeat the daily or monthly entry steps.

1. Set the report month

   On Start Here, type the first day of the month in B6. It controls Monthly Sales on the dashboard. Stock levels are always current, whatever month you choose.

2. Build your product list

   On Products, add one row per item:

   • SKU: a short unique code such as MUG-01. The other sheets pick SKUs from this list with a dropdown.
   • Product, and a Category from the dropdown (edit the categories on Lists).
   • Unit Cost (what you pay) and Price (what you charge).
   • Opening Stock: how many you have on the day you start using the template.
   • Reorder Point: the quantity at which you want to reorder.

   On Hand, Inventory Value and Status then calculate. Status shows Reorder, highlighted in red, as soon as On Hand is at or below the reorder point.

3. Log stock coming in and corrections

   Use Stock Movements for anything that changes stock other than a sale:

   • Purchase: stock received from a supplier. Enter a positive quantity.
   • Adjustment: corrections after a count, damage or loss. Use a negative quantity to remove stock (for example -2) or a positive one to add it.

   Fill in Date, pick the SKU and Type from the dropdowns, then enter Quantity and Unit Cost. Value (quantity × cost) calculates.

4. Record sales

   On Sales, add one row per order: Date, Order #, SKU (from the dropdown), Customer and Quantity.

   Unit Price is looked up from the Price column on Products, and Revenue is quantity × price.

   Set Status to Open, Shipped or Cancelled to keep track of fulfilment.

5. Manage purchase orders

   When you order from a supplier, add a row on Purchase Orders: PO #, supplier, SKU, quantity, unit cost, order date, expected date and Status Open. Total calculates, and all open POs add up to Open PO Value on the dashboard.

   When the delivery arrives:

   • Change the PO's Status to Received.
   • Add a Purchase row on Stock Movements for the quantity received. This is the step that actually adds stock; the PO on its own does not.

6. Watch for low stock

   On Products, filter the Status column to Reorder to see what needs ordering. The Low Stock Items count on Start Here shows how many there are.

7. Read the dashboard

   Start Here shows Inventory Value (on hand × unit cost), Low Stock Items, Monthly Sales, Open PO Value and total Units On Hand.

   Next to the navigation links there's a count and chart of sales orders by status, so open orders don't get forgotten.

8. Review the Checks sheet

   Checks looks for low stock, negative stock (usually a missing purchase entry), sales without a SKU, and reorder items that have no open purchase order. Where to Fix tells you which sheet to open.

9. Customize the dropdowns

   Movement types, sales and PO statuses and product categories come from the Lists sheet. Type a new item in the next empty cell of a column and it appears in that dropdown.

   The SKU dropdowns on Stock Movements, Sales and Purchase Orders list the SKUs on Products, so a new product is ready to use as soon as you add it.

How the formulas work

You don't need to understand the formulas to use the template, but they're a good way to learn. Click any cell in Excel to see its formula in the formula bar.

=IF(A5="", "", F5
   + SUMIFS('Stock Movements'!$D$5:$D$104, 'Stock Movements'!$B$5:$B$104, A5, 'Stock Movements'!$C$5:$C$104, "Purchase")
   + SUMIFS('Stock Movements'!$D$5:$D$104, 'Stock Movements'!$B$5:$B$104, A5, 'Stock Movements'!$C$5:$C$104, "Adjustment")
   - SUMIFS(Sales!$E$5:$E$104, Sales!$C$5:$C$104, A5, Sales!$H$5:$H$104, "<>Cancelled"))

=IF(A5="", "", IF(H5<=G5, "Reorder", "OK"))

=IF(C5="", "", IFERROR(VLOOKUP(C5, Products!$A$5:$E$54, 5, FALSE), 0))

=COUNTIF(Products!$J$5:$J$54, "Reorder")

Tips and limits

• Capacity: 50 products, 100 stock movements, 100 sales and 50 purchase orders. To extend, press Ctrl+H, set Within to Workbook and Look in to Formulas, and replace $104 with $1004 (and $54 with $504).
• Do a physical count now and then. If the count differs from On Hand, add an Adjustment row for the difference rather than changing Opening Stock.
• Change a product's Price on Products and new sales use the new price. Older sales rows also update, so for exact history, type the old price over the green Unit Price cell on those rows.