Weighted-Average Kardex Template in Excel (Stock Ledger)
Weighted-Average Kardex Template in Excel (Stock Ledger)
A kardex (stock ledger) that only counts units answers half the question; one that also carries value answers the other half. This weighted-average kardex template in Excel is a ready-to-use workbook that records every entry and exit of a product and works out, with each purchase, the weighted-average cost, the cost of goods sold for the period and the value of the closing inventory.
The file has two sheets: the valued kardex itself and an instructions sheet with the step-by-step guide and a worked example. You only type the date, the movement type, the document and the quantities; every other column is calculated by protected formulas. It is set up for A4 landscape printing and uses business grays only, with no colors and no currency symbol, so the same file works with pesos, soles, dollars or euros.
⬇ Download the weighted-average stock ledger template (.xlsx)
What a weighted-average stock ledger is, and why the average cost matters
A stock ledger is the ordered record of everything that comes into and goes out of a product: the date, the document, the quantity and the balance. When value is added to that record — what each unit costs and what the remaining stock is worth — you have a valued stock ledger. Units tell you how much is there; value tells you what is there is worth.
The piece that links both sides is the weighted-average cost. If the supplier changes the price and you buy at a different cost, at what cost should the next sale leave? The weighted average answers with a simple rule: it is recalculated only when goods come in, by dividing the total value by the total units. With that, the cost of sales stops being a hand calculation and the closing inventory value is ready to reconcile with the accounts.
One note on scope, right at the start: the template handles one product per file, with its own header and its own sheet. For a small warehouse, a spare-parts room or a short list of high-value references, that is usually more than enough. If you later need to run hundreds of references at the same time, the last section gives you an honest guide on when to move to software.
What the template includes
| Component | What it is for |
|---|---|
| Stock ledger sheet | The movement register plus the product valuation summary. |
| Instructions sheet | Step-by-step guide, column map, worked example and usage tips. |
| Header block | Company, business unit, product, code, valuation method and location. |
| Opening balance row | The units and unit cost the period starts from. |
| Movement table | 192 ready rows with the stock ledger's 12 columns, up to row 200. |
| Type dropdown | Nine options: purchase, sale, purchase return, sales return, entry adjustment, exit adjustment, transfer, shrinkage or damage, and opening balance. |
| Protected formulas | Totals, balances and average cost calculate themselves; only the input cells are meant to be edited. |
| Valuation panel | Period purchases (value), cost of goods sold, closing units and inventory value. |
| Print setup | A4 landscape fitted to the table width, with the header row frozen. |
Because values carry no currency symbol and use a thousands separator, the same file works in any country. It is worth spending a minute on the header before you start: if the valuation method is written on the sheet, anyone who opens it months later immediately understands how the inventory was valued and on what basis the costs were calculated.
The twelve columns of the movement table
The movement table is the heart of the sheet. These are its columns and what each one does:
| # | Column | What you type, what it calculates |
|---|---|---|
| 1 | Date | Date of the movement, in day, month and year format. |
| 2 | Type | Chosen from the dropdown; it defines whether the movement is an entry, an exit or an adjustment. |
| 3 | Document | Invoice, receipt, record or note that supports the movement. |
| 4 | Qty in | Units coming in. Typed by the user. |
| 5 | Unit cost in | Cost of each unit coming in. Typed by the user. |
| 6 | Total in | Quantity times unit cost. Calculated. |
| 7 | Qty out | Units going out. The only figure you type for an exit. |
| 8 | Unit cost out | Taken from the current average cost. Calculated. |
| 9 | Total out | Value of the units sold or consumed. Calculated. |
| 10 | Balance qty | Unit balance after the movement. Calculated. |
| 11 | Average cost | Current weighted-average cost. Updated only when goods come in. |
| 12 | Balance value | Balance units times average cost: the value of what is left. Calculated. |
The golden rule of the sheet is simple: on entries you type the quantity and the unit cost; on exits you type the quantity only, because the cost and the value of that exit come from the current average. Follow that rule and the valuation stays consistent on its own.
Two practical details keep mistakes away. The Date column accepts the day, month and year format of your regional settings, and the Type column carries a dropdown with the nine usual options; if a movement fits none of them, it is most likely two different movements, and it is better to record them in two separate rows. Columns with a very light gray background are calculation cells: they are not typed into and should not be modified.
How the average cost is calculated
The weighted average is recalculated at a single moment: when goods come in. The formula is the one taught in any cost accounting course, but here the sheet applies it, so nobody has to copy it row by row.
New average cost = (previous value + purchase value) ÷ (previous units + purchased units)
On exits the average is not touched: the last calculated average is used to value what leaves and to value the balance that remains. That is why a sale never moves the unit cost and only purchases update it. If the product has no entries during the period, the average stays the same as the opening balance.
There is a practical reason for calculating it this way. The weighted average spreads the effect of price changes across all available units, instead of charging the first sale with the most expensive or the cheapest cost. It is the most widespread method for homogeneous merchandise inventories and the one that requires the fewest assumptions: you do not have to identify which physical unit was sold, only how many came in and how many went out.
Worked example with numbers
This is the same case the instructions sheet carries: an opening balance of 10 units at 1,000, a purchase of 5 units at 1,200 and a sale of 4 units.
| Movement | Qty | Unit cost | Total | Balance qty | Average cost | Balance value |
|---|---|---|---|---|---|---|
| Opening balance | 10 | 1,000.00 | 10,000.00 | 10 | 1,000.00 | 10,000.00 |
| Purchase | 5 | 1,200.00 | 6,000.00 | 15 | 1,066.67 | 16,000.00 |
| Sale | 4 | 1,066.67 | 4,266.67 | 11 | 1,066.67 | 11,733.33 |
The new average is obtained like this: (10,000 + 6,000) ÷ (10 + 5) = 1,066.67. The sale of 4 units is valued at that cost, that is 4 × 1,066.67 = 4,266.67, and that is the cost of goods sold for the period. At the end, 11 units remain which, valued at the average, are worth 11 × 1,066.67 = 11,733.33.
If a more expensive purchase arrives tomorrow, the average will rise and the following exits will be valued at the new cost; exits already recorded do not change, because their value was fixed with the average in force at that moment. That is precisely the strength of the method: every movement was valued with the information available when it happened.
Step by step
- Download the file and save one copy per product, writing the item name in the file name so you do not mix them up.
- Fill in the header: company, business unit, product, code, valuation method and location.
- Record the opening balance in the row provided for it: units and unit cost. The sheet calculates the value of that balance.
- Gather the documents for the period (purchase invoices, exits, notes, records) and sort them by date before typing.
- Record each entry with its date, type, document, quantity and purchase unit cost.
- Record each exit with its date, type, document and the quantity only: the cost and the value appear on their own.
- Compare the unit balance and the inventory value with the physical count and, if there is a difference, record it as an adjustment in a new row.
- Review the summary panel, save the file and, if you need it on paper, print it in A4 landscape.
Common mistakes
- Typing the cost on an exit. The unit cost of an exit comes from the current average; typing over it makes the valuation unreliable and throws the balance value off.
- Mixing two products in the same file. Each product has its own average; two references on one sheet produce a cost that belongs to neither of them.
- Entering movements out of order. The average is built row by row, so recording a sale before the purchase that happened first changes the cost of sales for the period.
- Leaving the opening balance at zero. If the product already had stock and it is not recorded with its cost, the inventory value is understated from day one.
What the valuation summary is for
The side panel turns the movement table into four figures that are usually requested at month end. There is nothing to calculate by hand: they update with every row you type.
| Summary figure | What it answers | In the example |
|---|---|---|
| Period purchases (value) | How much was bought in the period, valued. | 6,000.00 |
| Cost of goods sold | What it cost to sell or consume what went out. | 4,266.67 |
| Closing units | How many units are left in the storeroom. | 11 |
| Inventory value | What the remaining inventory is worth. | 11,733.33 |
With those four figures you can answer the usual closing questions: how much was purchased, what it cost to sell what went out, how many units remain and what that balance is worth. The second figure is the cost of goods sold that feeds the income statement, and the fourth is the closing inventory that goes to the balance sheet. Having both calculated on the same basis avoids the gaps that appear when each area values inventory its own way.
One internal-control tip: compare the inventory value with the last physical count. If the stock ledger units do not match what was counted, adjust the difference with an adjustment movement in a new row, never by editing a previous row. That way the product's history stays auditable and you can explain where every figure came from.
When to move to inventory software
The template is an excellent starting tool, but it has the natural limits of a spreadsheet. It is time to make the move when any of these situations shows up:
- The storeroom handles dozens or hundreds of references and opening one file per product is no longer practical.
- Several people need to record movements at the same time and the file starts circulating by email in different versions.
- Purchases and sales are already invoiced in a program and the same data is typed twice: once on the invoice and once in the stock ledger.
- You need to check stock and value in real time, including from a phone and outside the office.
- The business needs to control lots, expiry dates or serial numbers, or to know the exact cost of goods sold at the moment the invoice is issued.
That is the ground Kardex Tauro covers: the same weighted average you learn in this template, but with the full product catalog, entries and exits connected to invoicing, and the stock ledger updated without typing the same information twice.
If your operation still fits in a spreadsheet, stay with the template: it is free, transparent and sufficient. The moment to change is not set by the size of the business but by how often information is typed twice or how often two people work with different data.
In short
Download the file, record the opening balance of one product and try it with a real month of movements. After forty rows you will see whether the weighted average reflects your costs properly and whether the summary figures match what you expected. If the exercise works for you, keep it as the storeroom's official format; and if it falls short, you will know exactly what to ask for in inventory software.
⬇ Download the weighted-average stock ledger template (.xlsx)
