Maintenance teams don’t need yet another buzzword—they need a clear workflow that captures requests, plans work, controls cost, and proves results. This article walks you through a lean, Excel-based CMMS design that you can deploy today. It follows a simple but robust flow:
Single source of truth: Assets, work orders, PMs, parts, vendors, technicians—each has a clean, validated table.
Frontline first: A dedicated Requests page makes intake painless and traceable.
Priority & SLA baked in: P1–P4 priorities and hour-based SLAs drive due dates and overdue flags.
Measurable outcomes: A small set of KPIs (Open/Overdue WOs, PM compliance, MTTR, parts below ROP) gives leadership what they need without drowning the team in vanity metrics.
Excel-native: No licenses, fast adoption, easy to customize, and works offline.
2) The data model—what to track and why
2.1 Assets (your registry)
Key fields: Asset ID, Class (e.g., Material Handling), Subclass (Conveyors, Pumps…), Location, Criticality, Warranty, PM next due. Why it matters: A clean asset list unlocks targeted PMs, risk-based prioritization, and faster troubleshooting.
2.2 Requests (frontline intake)
Key fields: Failure Class/Code, Priority (P1–P4), SLA hours, Approval Status (New/Approved/Rejected), Due By (auto), Overdue Days (auto). Why it matters: Separating requests from WOs reduces noise and gives planners time to triage.
2.3 Work Orders (WO)
Key fields: Work Type (Breakdown/Preventive/Calibration/Improvement…), Status (New→Completed/Cancelled), Assigned tech, Downtime (h), Labor hours & rate, Parts cost, Total cost (auto), On-Time? (auto). Why it matters: This is your execution backbone—link to a Request ID when a request becomes a WO.
2.4 PM Plans
Key fields: Frequency Unit (Days/Weeks/Months/Years), Interval, Last Done, Next Due (auto), Days Remaining (auto). Why it matters: PM compliance is your best lever to reduce breakdowns without increasing headcount.
2.5 Parts & Inventory
Key fields: On Hand, Reorder Point, Reorder Qty, Unit Cost, Stock Value (auto). Why it matters: Low stock = longer downtime; excess stock = cash trapped on the shelf. ROP guards both.
2.6 KPI Dashboard
Focus metrics:
Open WOs and Overdue WOs (execution health)
P1 Critical (Open) (risk exposure)
PM Due vs. PM Completed → PM Compliance (preventive discipline)
Parts Below ROP (supply risk)
(Optional)MTTR (mean time to repair), MTBF (mean time between failures) if you log failure dates consistently
3) The operating rhythm (weekly cadence)
Triage Requests
Approve or reject. Set Priority and SLA.
If work is needed, set To WO? = Yes, create a WO, and copy the Request ID into the WO.
Plan Work Orders
Assign technician(s), set Planned Start/End and Due Date (align to SLA if needed).
Prepare parts and permits; add links in the WO “Description/Notes”.
Execute & Close
Record Downtime (h), Labor hours, Parts cost.
Complete Root Cause / Corrective Action / Verification—don’t skip this.
PM Discipline
Review Next Due and Days Remaining.
Hit the PM schedule before it turns overdue.
KPI Stand-Up (15 minutes)
Check Open/Overdue, P1 Critical, PM Compliance, Parts Below ROP.
Agree one or two actions. Keep the loop tight.
4) Setting priorities, SLAs, and statuses that drive behavior
Priority (P1–P4):
P1—Critical: Safety risk, production stop, regulatory breach.
P2—High: Major impact or high probability of escalation.
P3—Medium: Routine but time-sensitive.
P4—Low: Minor issues or opportunistic work.
SLA (hours): Choose targets (e.g., 4/8/24/72/168). In the template, SLA drives Due By automatically on Requests—and Overdue Days once due.
WO Status flow:New → Approved → Scheduled → In Progress → Completed (or Cancelled) Use Waiting Parts when supply blocks execution—this status surfaces in KPI as a constraint to remove.
5) Cost and reliability—what’s “good enough” to start
Labor cost = Labor hours × Hourly rate (auto).
Total cost = Labor cost + Parts cost (auto).
Downtime (h): record only for breakdown WOs; don’t inflate numbers for PMs.
PM compliance: start by measuring this month. Aim for ≥ 90% on critical assets.
Don’t chase perfect precision on day one. Consistent “good enough” data beats sporadic “perfect” data.
6) How to roll it out in 30–60–90 days
Days 1–30 (Stabilize intake & execution)
Lock the Requests → WO pathway.
Train technicians to close WOs with hours and simple root cause.
Review Open, Overdue, P1 daily; clear the backlog.
Days 31–60 (Preventive & parts)
Build PM Plans for your top 20% critical assets.
Define ROP for fast-moving spares; add vendor info.
Start the weekly KPI stand-up.
Days 61–90 (Improve & automate)
Tighten SLA targets by priority.
Add MTTR tracking for top failure modes.
Consider a simple macro or Power Query to automate Request → WO conversion and monthly KPI exports.
7) Example applications
Warehouse equipment (forklifts, dock levelers): P1 for safety or blocked dock; PMs for 500-hour service; parts for tires, forks, seals; vendor response times as an additional KPI.
Process utilities (compressors, HVAC, pumps): P1 for plant-wide outages; PMs on filters and lubrication; log overheating/vibration failures; track trend of downtime hours.
Packaging line (conveyors, palletizers, PLCs): Use failure codes Misalignment/Clogging/Sensor Fault; tie PM compliance to startup scrap and rework.
8) Governance and roles
Requesters (frontline): Submit clear problem statements; choose a reasonable priority.
Planners/Supervisors: Approve, set SLA, convert to WO, schedule, stage parts.
Technicians: Execute, log hours/parts/downtime; write short root cause + corrective action.
Everything becomes P1: Enforce definitions and require justification.
WOs never close: Make closing with hours & parts part of the day’s work, not an afterthought.
PMs always late: Shorten PM scope, increase frequency where appropriate, and schedule during predictable lulls.
Data drift: Freeze column headers; use the built-in dropdowns and tables to avoid typos.
10) Extending the template
Mobile intake: Add a simple Microsoft Forms/Google Forms that writes into the Requests sheet.
Analytics: Use Power BI or Excel PivotCharts for trends (downtime by asset, cost by failure class, aging backlog).
Compliance: Add calibration logs and certificate links for instruments; audit trails in a “Logs” sheet.
Lean CMMS in Excel: From Intake to Impact
A CMMS doesn’t have to be heavy to be effective. With a disciplined Requests → WO → PM cycle, clear priorities and SLAs, and a small set of meaningful KPIs, you’ll reduce downtime, control cost, and build trust with operations—using tools your team already knows
What it is
A lightweight, Excel-based Computerized Maintenance Management System that covers the full flow: Requests → Approval → Work Orders → PM Plans → Parts/Inventory → KPI Dashboard. It’s drop-down driven, works offline, and is easy to customize.
Who it’s for
Maintenance leads, planners, and technicians who need a practical CMMS without complex software—especially in warehouses, utilities, and production lines.
What’s inside (the sheets)
Start: Home page with navigation links and live KPI tiles.
Requests: Simple intake form (Priority P1–P4, SLA hours). Auto-calculates Due By and Overdue Days.
Work Orders: Execution hub with Work Type (Breakdown/Preventive/…), Status (New→Completed/Cancelled), assignee, Downtime (h), Labor/Parts cost, On-Time? flag.
Assets: Register with Class/Subclass, criticality, warranty, Next PM Due and Days to Due.
PM Plans: Frequency-based (Days/Weeks/Months/Years) with auto Next Due + Days Remaining.
Parts & Inventory: Min/Max/On-hand, Reorder Point, vendor, Stock Value (auto) + reorder alerts.
Vendors & Technicians: Master data feeding dropdowns and cost rates.
Parameters: All reference lists (priorities, statuses, work types, SLA targets, asset classes) in one place.
Built-in automation & logic
Auto-IDs: RQ-/WO-/AS-/PM-/PT- prefixes with timestamps.
SLA → Due date: Requests compute Due By from SLA hours; late items show Overdue Days.