Try the sheet here, on the example
The sheet is open below with sample numbers. Change any white cell and watch the results update, or press the blank-copy button to type your own data from scratch. Nothing you type leaves your browser.
Example from the sheet
| Make | Honda | Type a make and model exactly as they are spelled on the Fitment sheet. Add a year to see only parts whose year range includes it; leave the year empty to see every part for that make and model. Only the first 25 matches are listed. | |||||||
|---|---|---|---|---|---|---|---|---|---|
| Model | Civic | ||||||||
| Year (optional) | 2018 | ||||||||
| Parts that fit | 14 | ||||||||
| # | Part no. | Description | Brand | Type | On hand | Bin | Retail | Trade | Wholesale |
| 1 | OF-1001 | Oil filter, spin-on | CleanFlow | Aftermarket | 32 | A-01-1 | 7.99 | 6.49 | 4.90 |
| 2 | AF-2001 | Engine air filter | CleanFlow | Aftermarket | 40 | A-02-1 | 14.99 | 11.99 | 8.50 |
| 3 | CF-3001 | Cabin air filter | CleanFlow | Aftermarket | 17 | A-02-2 | 12.99 | 10.49 | 7.20 |
| 4 | BP-4001 | Front brake pads, ceramic | ProStop | Aftermarket | 26 | B-01-1 | 49.99 | 39.99 | 30.00 |
| 5 | BP-4002 | Front brake pads, OEM | OE Honda | OEM | 9 | B-01-2 | 84.99 | 70.00 | 55.00 |
| 6 | BR-4101 | Front brake rotor | ProStop | Aftermarket | 14 | B-02-1 | 64.99 | 52.00 | 39.00 |
| 7 | SP-5001 | Spark plug, iridium | Voltix | Aftermarket | 56 | C-01-1 | 11.99 | 9.49 | 6.50 |
| 8 | ST-6002 | Starter motor, remanufactured | Torque Pro | Remanufactured | 4 | D-01-2 | 159.99 | 130.00 | 100.00 |
Example from the “Vehicle lookup” tab, with the numbers and formulas that are in the file.
What is inside
- Dashboard: Stock value at cost and at retail, parts to reorder and out of stock, dead stock, what trade accounts owe and how much of it is over 60 days, gross profit and margin, core deposits held, and four charts: net sales and gross profit by month, what accounts owe by age, net sales by category and stock value by part type.
- Vehicle lookup: Type a make, model and optional year and the parts that fit appear, up to 25, with description, brand, type, on hand, bin, the retail, trade and wholesale price, core charge and stock status. It carries a reminder to check the parts catalogue before selling.
- Parts: One row per part (300 rows): part number, description, brand, type, category, bin, unit cost, retail, trade and wholesale price, core charge, opening quantity, minimum stock and usual supplier. It works out purchased, sold, returns, on hand, stock value, retail margin, last sold, days idle, a status of OK, Reorder or Out of stock, a dead-stock flag and the order quantity.
- Fitment: Which vehicles each part fits (600 rows): part number, make, model, first and last year and a note. It checks for unknown part numbers and reversed years, and marks the rows that match the vehicle typed on Vehicle lookup.
- Sales: Every sale line (1,000 rows): date, invoice, account, part, quantity, price tier, optional price override, discount, core exchanged or charged and the amount paid now. It shows the unit price, net sale, core deposit, total due, cost, margin and margin percentage for each line and what is left on account.
- Purchases: Supplier deliveries (500 rows): date, supplier invoice, supplier, part, quantity, unit cost and amount paid. It shows the line total and what is still owed, and flags a delivery whose cost differs from the cost on the part card.
- Returns: Customer returns, core refunds and returns to suppliers (150 rows), each with the credit per unit. Customer returns put stock back, supplier returns take it out, and core refunds give the deposit back without touching stock.
- Payments: Money received from accounts and paid to suppliers (400 rows), with method and a check for names that are not on the account or supplier lists.
- Accounts: One row per customer account (150 rows) with its type, credit limit and any opening balance. It shows billed, paid at sale, credits, payments received and the balance owed, flags accounts over their limit, and splits the balance into 0-30, 31-60, 61-90 and over 90 days.
- Suppliers: What you owe each supplier (60 rows): opening balance, purchases, paid at purchase, returns credited, payments made, balance owed and purchases in the last 30 days.
- Reorder: Every part at or below its minimum, or out of stock, with the usual supplier, on hand, order quantity and estimated cost, biggest quantities first, with a total. Up to 50 lines.
- Dead stock: Parts you hold that have had no sale or purchase for the number of days you set, or never moved, with bin, quantity, value at cost and at retail, largest cost first. Up to 30 lines.
- Monthly: Twelve months of sale lines, net sales, cost of goods, customer returns, gross profit, margin percentage, purchases and core deposits, plus a day-by-day table for any month and a chart.
- Lists: Your part categories (up to 12) and vehicle makes (up to 30). They feed the dropdowns and the category chart.
- Settings: Business name, currency symbol, the as-of date, days without movement for dead stock, the reorder multiplier, the first month for Monthly and the month shown day by day.
The file opens on a start tab (“Start here”) that explains how to use it.
What makes it different
Most free parts sheets are a flat list of part numbers with a quantity column. This one is built around how a parts counter sells: the same part has a retail, a trade and a wholesale price, a remanufactured part carries a core deposit, the customer asks what fits a 2018 Civic, and the trade accounts pay late. The sample is a small store with 28 parts, 52 sale lines and 18 delivery lines from October and November 2026.
How do I track auto parts inventory in Excel?
Give every part one row on the Parts sheet with a unique part number, its bin and the quantity you hold when you start. After that you only log what moves: deliveries on Purchases, sale lines on Sales and returns on Returns. The sheet adds them up, so you never type a stock balance. In the sample, the spin-on oil filter starts with 40, receives 48 and sells 56, so 32 are on hand and 8 days have passed since its last movement.
The Status column turns to Reorder when on hand reaches the minimum and to Out of stock at zero. The Reorder sheet then lists what to order from which supplier. In the sample, six parts are at or below their minimum and the water pump is out; the suggested order totals 1,604.00 at cost. For the arithmetic behind a minimum, see the reorder point calculator.
How do I look up which parts fit a vehicle?
Fitment is a table with one row for each vehicle a part fits: part number, make, model, first year and last year. Vehicle lookup reads that table. Type Honda, Civic and 2018 and it lists the 14 parts in the sample whose year range includes 2018, each with stock, bin and the three prices. Leave the year empty to see every part for that make and model.
The sample fitment rows are illustrative, not catalogue data. A spreadsheet can only be as right as the rows you enter, so check the parts catalogue or the VIN before you sell a part. Parts with no vehicle, such as oil and fluids, stay out of the lookup unless you give them fitment rows.
How do I set retail, trade and wholesale prices and see my margin?
Each part card holds three prices. An account has a type, Retail, Trade or Wholesale, and its sale lines use that tier unless you pick another or type a price override. Each line then shows net sale, cost and margin. In the sample, two spin-on oil filters to a walk-in customer sell for 15.98 against a cost of 6.40, a margin of 9.58.
Core charges work the same way. Typed once on a remanufactured part, the deposit is added to what the customer owes but is not counted as sales or profit. A walk-in alternator sells for 189.99 plus a 40.00 core, 229.99 due, with a margin of 111.99. When the old unit comes back, log a core refund on Returns. The sample holds 231.00 of core deposits at the as-of date. Across October and November the sample shows net sales after returns of 6,831.23 and gross profit of 2,644.33, a margin of 38.7%. For invoices to print or send, use the invoice template.
How to use it
- On Settings type your business name, currency symbol, today's date (the "as of" date used for ageing and dead stock), the days without movement that make a part dead stock, the reorder multiplier and the first month to report. Check the category and make lists on Lists.
- On Suppliers and Accounts type each supplier and each customer account. An account has a type (Retail, Trade or Wholesale) that sets its default price, a credit limit and, if they already owe you, an opening balance with its date.
- On Parts add one row per part: part number (SKU), description, brand, type (OEM, Aftermarket, Remanufactured or Used), category, bin location, unit cost, the three prices, core charge if any, opening quantity, minimum stock and usual supplier.
- On Fitment add one row for each vehicle a part fits: part number, make, model, first year, last year. Then open Vehicle lookup, type a make and model (and a year if you like) and the matching parts appear with stock, bin and price.
- Log every delivery on Purchases and every sale line on Sales (price tier, discount, core exchanged or charged, amount paid now). Stock goes down on each sale and up on each delivery, and each sale line shows its margin.
- Log customer returns, core refunds and returns to suppliers on Returns, and money received from accounts or paid to suppliers on Payments.
- Read Accounts for each account's balance and ageing, Suppliers for what you owe, Reorder for what to order, Dead stock for what is not moving, Monthly for sales and profit by month and by day, and Dashboard for the whole picture with charts.
Before you start:
- Capacity: 300 parts, 600 fitment rows, 60 suppliers, 150 accounts, 500 purchase lines, 1,000 sale lines, 150 returns and 400 payments. Vehicle lookup shows the first 25 matching parts, Reorder 50 and Dead stock 30.
- On hand = opening quantity + purchased - sold + customer returns - returned to suppliers. Type the opening quantity once, on the day you start using the sheet.
- Fitment rows in this file are illustrative examples, not catalogue data. Always check the parts catalogue (or the VIN) before you sell a part. A part with no fitment rows, such as oil and fluids, never appears in Vehicle lookup.
- Unit price on a sale = the part's retail, trade or wholesale price. If the price tier is blank it follows the account type, and an account with no type pays retail. A price override replaces the list price for that line.
- Core charge is a deposit typed on the part. A sale line charges it unless you mark the core as Exchanged. The deposit is added to what the customer owes but is not counted as sales or profit; a Core refund on Returns gives it back.
- Margin on a line = net sale - quantity x unit cost on the Parts card. When your supplier price changes, update the unit cost on Parts. Purchases flags any delivery whose cost differs from the card.
- Ageing allocates what an account has paid or been credited to its oldest charges first. Buckets are counted from the as-of date on Settings: 0-30, 31-60, 61-90 and over 90 days. An opening balance is aged by its own date.
- A part is dead stock when you hold it and it has had no sale or purchase for the number of days on Settings, or has never moved. Reorder quantity = minimum stock x multiplier - on hand.
- The currency symbol on Settings is only a label. Match make and model spelling between Fitment and Vehicle lookup (for example Silverado 1500).
Still running this on Excel?
This sheet needs someone to copy every invoice and payment into it. In Accountwiro an invoice and a payment are recorded the moment they happen, and the customer's balance shows in a single statement of account.
| In this sheet | In Accountwiro |
|---|---|
| You copy every invoice and payment into the sheet by hand. | It shows the customer's invoices, payments and outstanding balance in a single statement of account. |
| Invoices are typed or copied by hand. | It creates an invoice by scanning a barcode or searching by product name, calculates discounts and taxes, and sends it to the customer over WhatsApp. |
| Stock shortages are discovered late. | It updates quantities after every sale and alerts you when a product drops below the required level. |
| Anyone who opens the sheet sees every number. | Permissions per user: the sales employee issues invoices, and the warehouse employee manages stock without access to financial data. |
Frequently asked questions
Is this auto parts inventory spreadsheet really free?
Yes. Download it, use it in your store and change anything you like. There is nothing to sign up for to open the file.
Does it work in Google Sheets and LibreOffice?
Yes. It uses ordinary formulas such as SUMIFS, COUNTIFS, INDEX and MATCH, so it works in Excel 2016 and later, LibreOffice and Google Sheets.
Is the vehicle fitment data in the sheet correct?
No. The fitment rows are illustrative examples so you can see how the lookup works. Enter your own fitment from your parts catalogue and check the catalogue or the VIN before you sell a part.
How many parts and sales can it hold?
300 parts, 600 fitment rows, 60 suppliers, 150 accounts, 500 purchase lines, 1,000 sale lines, 150 returns and 400 payments. When a table is full, save a copy, enter each part's current on hand as its new opening quantity and clear the sales, purchases, returns and payments.
How does it handle core charges?
A core charge is a typed amount on the part. A sale line charges it unless you mark the core as Exchanged, and a core refund on Returns gives it back. The deposit is tracked as money owed to the customer, not as sales. Check your local rules for how deposits are taxed and reported.
Is the template free?
Yes, with no sign-up and no email address. Use it, edit it and share it as you like.
Does it work in Google Sheets and on mobile?
The sheet uses formulas supported by Excel 2016 and later, Google Sheets and LibreOffice, so you can upload it to Google Drive and open it with Google Sheets on a computer or a phone.
What is the difference between the white and the gray cells?
You type in the white cells; the gray cells are calculated automatically. Don't type in a gray cell, or you will erase its formula.
Can I use the sheet from an AI assistant?
Yes. Add our MCP server (URL https://softwiro.com/api/mcp, no key needed) to your assistant and ask it to do the calculation in this template. The numbers come from the sheet's own formulas, and we do not store your data. The steps and a ready-made prompt are in this article: https://softwiro.com/blog/mcp-excel-templates-ai-agent-guide.html
All the templates in this group: Free Excel templates for cars: showroom, rental, workshop and maintenance
More free templates for: Transport and cars
More free templates
- Car rental fleet tracker (Excel)
- Fleet fuel and maintenance tracker (Excel)
- Car showroom accounts sheet
- Taxi, Uber and ride-hailing driver income sheet (trips, fuel, commission, daily net)
- Personal car maintenance log (oil and filter dates by kilometre and months)
- Parking garage management sheet (hourly and monthly)
- Clothing Store Inventory Spreadsheet (Size and Color) for Excel
- Multi-Location Inventory Spreadsheet for Excel
- Production tracking sheet for home textiles factories
- Fabric requirements and marker sheet
- Customer accounts, credit sales and customer statement sheet
- Cash book and customer accounts sheet
- Student attendance sheet
- Study planner and lesson tracker