Mobile Repair Job Card System in Excel: Jobs, Parts Stock, Status Flags, Warranty and Dashboard

By Ravindra Bagale · Last updated: 17 October 2026

← All Excel dashboards

Download the template: mobile-repair-job-card-system-template.xlsx: a 7-sheet system with example data and real formulas (Read me, Setup, Parts, Jobs, Report, Job receipt, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Salman Mobile Care, Pune, 1 to 17 October 2026 (made-up data). 30% mark-up on part cost, 2-day standard promise, 30-day repair warranty. Technicians Salman and Amir.

13 jobs brought ₹14,339; 9 are delivered. Amir handled 7 jobs, Salman 6.

2. Build it sheet by sheet

Step 1. Setup and Parts

1. Setup B4 mark-up (30%), B5 standard promise (2 days), B6 warranty (30 days); lists of technicians and statuses in D and E.
2. Parts: Code, Part, Cost, Opening, Reorder level. Code P00 means no part (software work, checks).

Step 2. Jobs: charges and dates

1. A5 ="J-"&TEXT(ROW()-4,"000"). Type date, customer, device, fault and pick a part code.
2. G5 part charge =ROUND(XLOOKUP(F5,Parts!$A$5:$A$10,Parts!$C$5:$C$10,0)*(1+Setup!$B$4),0): a ₹1,800 screen is charged ₹2,340.
3. I5 total =G5+H5, K5 balance =IF(O5<>"",0,I5-J5), N5 promised =B5+Setup!$B$5.
4. R5 warranty till =IF(O5<>"",O5+Setup!$B$6,""), counted from the delivery date.

Step 3. Jobs: flags

1. P5: =IF(O5<>"","Delivered",IF(M5="Ready","Ready, not collected",IF(N5<Setup!$B$3,"Overdue","On track"))).
2. Q5 days taken for delivered jobs =IF(O5<>"",O5-B5,"").
3. Colour Overdue red and Ready, not collected amber with conditional formatting; call those customers first.

Step 4. Parts stock and Report

1. Parts F5 used =COUNTIF(Jobs!$F$5:$F$500,A5), G5 closing =D5-F5, H5 order =IF(A5="P00","-",IF(G5<=E5,"Yes","No")).
2. Report B5 jobs per technician, C5 delivered with COUNTIFS, E5 average days with AVERAGEIFS and F5 revenue.
3. G5 profit on parts: part charges minus part cost for that technician's jobs.

Step 5. Job receipt and Dashboard

1. Job receipt: type a job no in B4; rows 6 to 14 bring customer, device, fault, total, advance, balance, promised date and status. The footer reminds the customer to back up data.
2. KPI cards: jobs, open, overdue, average days =AVERAGE(Jobs!$Q$5:$Q$500), revenue and parts to order. Charts: jobs by flag and revenue by technician.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Warranty from the date in. Count warranty from the delivery date, or the customer loses days the phone sat in the shop.
2. No row for checks and software work. Use part code P00 so the job, labour and technician still count.
3. Status left at Received. Change it to Waiting for part or Ready as soon as it changes; the flags depend on it.
4. Balance kept after delivery. K5 drops to 0 on delivery, so collect before typing the delivered date.
5. Mark-up typed into each charge. Keep the mark-up on Setup; changing one cell then reprices new jobs consistently.

5. More real examples

Example 1: Laptop repair. Add a Serial no column and a longer standard promise (5 days).

Example 2: Electronics service centre. Add an In warranty? column and charge ₹0 when the date is within the maker's warranty.

Example 3: Watch and clock repair. Same layout with batteries, straps and glass as parts.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I make a job card number in Excel?

="J-"&TEXT(ROW()-4,"000") in row 5 gives J-001 and counts up row by row.

How do I add a mark-up to a part cost?

Cost × (1 + mark-up), rounded: =ROUND(cost*(1+Setup!$B$4),0).

How do I show phones ready but not collected?

Check the status first: if it is Ready and no delivery date is typed, show "Ready, not collected".

How do I work out a warranty end date?

Delivery date + warranty days, as in Jobs R5.

How do I find average repair time per technician?

AVERAGEIFS on the days-taken column with the technician and Delivered as conditions.

7. Quick quiz

Ten questions on this page. Sign in to save your score.