For a small mobile repair shop, spare parts can disappear into a drawer long before anyone notices. Screens, batteries, charging ports, cameras, speakers, connectors and other components have different purchase costs, suppliers and usage patterns. A simple Google Sheets inventory can give a shop admin a practical way to record what came in, what was used, what is available and what needs to be reordered.
This guide shows how to build a mobile repair shop inventory tracker in Google Sheets, which columns to use, how to record spare-part movements, and how to add simple formulas for stock balance and reorder alerts. It is designed for small repair businesses that want better control before moving to dedicated repair-shop software.
What Should a Mobile Repair Shop Inventory Sheet Track?
Your sheet should answer four basic questions: What part is it? How much do we have? Where did it come from? And where did it go? A useful inventory record can include:
- Part name and category
- Compatible phone model or models
- Part code or SKU
- Supplier name
- Opening stock
- Purchases received
- Parts used in repairs
- Damaged or returned quantity
- Current stock
- Minimum stock level
- Purchase cost
- Storage location
- Last updated date
Why Google Sheets Works for a Small Repair Shop
Google Sheets is useful when the shop has a manageable number of parts and more than one person needs access to the same file. It can be opened from a phone or computer, shared with authorized staff, filtered by columns and extended with formulas. It also makes it easy to keep a backup copy and review changes.
However, a spreadsheet depends on disciplined data entry. Dedicated repair inventory systems can connect parts usage with repair jobs, update stock automatically and provide low-stock alerts. Current repair-management products commonly connect inventory with repair tickets and parts usage, which is useful when a shop outgrows manual tracking.
How to Create a Spare Parts Inventory Sheet
Step 1: Create the main inventory columns
Start a Google Sheet named Mobile Repair Shop Inventory. Create a header row such as:
| Part Code | Part Name | Model | Category | Supplier | Opening | Purchased | Used | Damaged | Current Stock | Min Stock | Cost | Location |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| BAT-IP11 | Battery | iPhone 11 | Battery | Supplier A | 5 | 10 | 7 | 0 | 8 | 3 | 950 | Drawer A2 |
| LCD-RN10 | Display | Redmi Note 10 | Display | Supplier B | 3 | 5 | 6 | 0 | 2 | 2 | 1200 | Rack B1 |
Step 2: Use a unique part code
A part code makes searching easier than relying only on names. For example, a battery for a particular model could have a code such as BAT-IP11. Keep the format consistent and avoid using the same code for different parts.
Step 3: Separate stock movement from the master list
For better accuracy, create a second tab called Stock Movement. Record every purchase, repair usage, adjustment, return or damage there. Suggested columns are Date, Part Code, Part Name, Movement Type, Quantity, Repair Job ID, Supplier, Staff Member and Notes.
This creates an audit trail instead of changing the current stock number without explaining why it changed.
How to Calculate Current Stock in Google Sheets
If your main sheet stores opening stock, purchases, used quantity and damaged quantity, the basic calculation is:
Current Stock = Opening Stock + Purchased − Used − Damaged
For example, if cell F2 contains opening stock, G2 contains purchases, H2 contains used quantity and I2 contains damaged quantity, the current stock formula in J2 can be:
=F2+G2-H2-I2
Copy the formula down the inventory table. If you maintain stock movements in a separate sheet, you can later use SUMIF or SUMIFS to calculate purchases and usage from transaction records rather than typing totals manually.
Add a Low-Stock Alert
A repair shop should know when a commonly used part is approaching its reorder level. Add a Min Stock column and compare it with Current Stock.
A simple status formula is:
=IF(J2<=K2,"REORDER","OK")
You can then use Google Sheets conditional formatting to make the REORDER rows visually prominent. The threshold should be based on actual usage, supplier lead time and how critical the part is to your repairs.
Track Parts Used in Each Repair Job
Inventory control becomes much more useful when every part used is linked to a repair job. For example, a technician replaces an iPhone 11 battery on Job #MR-1042. The stock movement should record the battery code, quantity used and repair job ID.
This helps the admin answer questions such as:
- Which repair consumed this part?
- How many displays were used this week?
- Which technician recorded the usage?
- Why did physical stock differ from spreadsheet stock?
- How much stock value is currently sitting in the shop?
Use Data Validation to Reduce Entry Errors
Do not make staff type every movement type manually. In Google Sheets, use dropdowns for fields such as Movement Type: Purchase, Used, Return, Damage, Adjustment. You can also use dropdowns for categories such as Display, Battery, Charging, Camera, Speaker, Connector and Other.
Standardized entries make filtering and reporting much easier.
Keep Spare Parts Organized Physically
A spreadsheet cannot compensate for an unorganized storeroom. Give every drawer, box or rack a simple location code. For example, A1 can mean the first drawer in the battery section and B2 can mean the second box in the display section.
Store the location code in the inventory sheet so staff can find a part quickly after searching by part code or model.
Useful Reports for a Mobile Repair Shop Admin
Once the sheet is maintained consistently, create simple reports for:
- Low-stock parts
- Fast-moving spare parts
- Slow-moving stock
- Parts purchased by supplier
- Parts consumed by repair jobs
- Damaged or returned parts
- Total estimated stock value
Common Inventory Mistakes to Avoid
- Updating stock only at the end of the week: Record movements as they happen.
- Using free-text part names: Use consistent names and unique part codes.
- Ignoring damaged parts: Record them separately so they are not counted as usable stock.
- Not linking parts to repair jobs: Record the job number whenever a part is consumed.
- Skipping physical stock checks: Periodically compare spreadsheet quantities with actual quantities.
Google Sheets vs Dedicated Repair Shop Inventory Software
| Need | Google Sheets | Dedicated Software |
|---|---|---|
| Basic stock list | Good | Good |
| Simple purchase tracking | Good | Good |
| Multiple users | Possible | Usually built in |
| Repair-job linked parts usage | Manual setup | Usually integrated |
| Automatic stock updates | Requires formulas/scripts | Usually available |
| Low-stock alerts | Possible with formulas | Usually built in |
Google Sheets is a sensible starting point for a small shop. As repair volume, staff count and inventory complexity increase, dedicated repair management software can reduce manual updates and connect inventory with repairs, billing and customer history.
Simple Daily Inventory Routine
- Record every new spare-parts purchase.
- Record every part used in a repair.
- Record damaged, returned or adjusted stock.
- Review parts below the minimum stock level.
- Check unusual stock differences.
- Back up or export important records periodically.
FAQ
Can I manage mobile repair shop inventory in Google Sheets?
Yes. Google Sheets can work well for a small repair shop when the number of parts and users is manageable and staff consistently record stock movements.
What columns should a mobile repair inventory sheet have?
At minimum, track part code, part name, compatible model, supplier, opening stock, purchases, usage, damaged quantity, current stock, minimum stock and storage location.
How do I track spare parts used in repairs?
Use a separate Stock Movement tab and record the date, part code, quantity used and repair job ID each time a technician consumes a part.
How can I create a low-stock alert in Google Sheets?
Compare Current Stock with a Min Stock value using an IF formula and use conditional formatting to highlight parts that need reordering.
When should a repair shop move from Google Sheets to software?
Consider dedicated software when manual updates become difficult, several employees need to use the system, stock errors increase, or you need inventory, repair jobs, billing and customer history connected in one workflow.
Final Takeaway
A well-structured Google Sheet can bring basic discipline to spare-parts management without requiring a complicated system. Use unique part codes, record every stock movement, link parts to repair jobs, set minimum stock levels and review physical stock regularly. When the shop grows beyond what a spreadsheet can reliably handle, a connected repair-shop inventory system can provide a more automated workflow.
