ABC Analysis & Stock Valuation, FIFO and Average (Excel)
Two questions every year end and every stock audit: what is our closing stock worth, and which items deserve the most control? Enter receipts with their cost and issues with their quantity; the workbook works out closing stock for each item, values it by FIFO (first in, first out) and by weighted average cost side by side, and ranks items by consumption value into A, B and C classes so you know where tight control pays off.
Free · no sign-up · opens in Microsoft Excel, Google Sheets, LibreOffice and WPS Office. Files contain no macros.
Preview
Real view of the file with its sample data (yellow cells are where you type).
| Item code | Item name | Consumption value (INR) | Share | Cumulative before | Cumulative after | Rank | Class | |
| RM-001 | MS round bar 20 mm | 3,12,033 | 46.2% | 0.0% | 46.2% | 1 | A | |
| RM-002 | MS plate 10 mm | 1,41,500 | 21.0% | 46.2% | 67.2% | 2 | A | |
| BO-101 | Ball bearing 6205-2RS | 82,360 | 12.2% | 67.2% | 79.4% | 3 | A | |
| BO-102 | Taper roller bearing 30206 | 47,400 | 7.0% | 79.4% | 86.4% | 4 | A | |
| PK-201 | 5-ply corrugated box 600x400x400 | 40,797 | 6.0% | 86.4% | 92.4% | 5 | B | |
| SP-301 | Hex bolt M12x50 (box of 100) | 10,800 | 1.6% | 98.1% | 99.7% | 8 | C | |
| CN-401 | Cutting oil 20 L | 21471.39 | 3.2% | 92.4% | 95.6% | 6 | B |
What's inside
- Items sheet: item code, name and unit
- Receipts sheet: date, item, quantity, unit cost and reference, with the quantity and value still in stock under FIFO for each receipt lot
- Issues sheet: date, item, quantity and department or job
- Valuation sheet: received, issued and closing quantity, weighted average cost, closing value at average cost, closing value at FIFO and the difference
- ABC sheet: consumption value, share, cumulative share before the item, rank and class, with editable cut-offs (80 and 95 percent to start with)
- ABC summary with number of items and share of value per class, and a bar chart
How to use it
- On the Settings sheet type your currency code and the period the file covers, and check the A and B cut-off percentages.
- List your items on the Items sheet with a unique code.
- On the Receipts sheet enter opening stock first (one row per item dated the first day, reference "Opening") and then every purchase in date order with its quantity and unit cost.
- On the Issues sheet enter every issue or sale in quantity.
- Open the Valuation sheet for closing stock at FIFO and at weighted average cost. Use the method your accountant follows for your books.
- Open the ABC sheet. Class A items take most of the consumption value: count them more often, order them more carefully and negotiate their prices first.
Tips
- Keep the Receipts sheet sorted by date (Data › Sort, oldest first). FIFO assumes the rows below are the newer lots.
- Unit cost should be the landed cost: price plus freight and other costs to bring the goods in, less any tax you can claim back.
- Start a new file each year with the closing FIFO lots of the old file as the opening rows.
- An item with no issues in the period has zero consumption value and is listed as class C: check whether it is slow-moving or dead stock.
Questions and answers
What is ABC analysis?
A way to sort stock items by how much money flows through them. Typically a small share of items (class A) makes up about 80 percent of consumption value, the next group (B) about 15 percent, and the many remaining items (C) the last 5 percent.
What is the difference between FIFO and weighted average?
FIFO assumes the oldest stock is used first, so closing stock is valued at the most recent purchase costs. Weighted average values every unit at the average cost of all units received in the period. When prices rise, FIFO gives a higher closing value.
Which method should I use?
The one your accounting policy and the accounting standards that apply to you allow, used consistently every year. Ask your accountant; this workbook shows both so you can compare.
Why does the average cost here differ from my software's moving average?
This workbook uses a periodic weighted average over the whole period. Software often uses a moving average that changes after every receipt, which can give a slightly different figure.