Car Rental Booking
Management System
A mid-size Indian car rental company operating 85 cars across 3 cities was losing customers every week due to double bookings, cars being given out that were actually in the garage for service, and a booking process done entirely through phone calls and Excel sheets. This is the full BA journey from understanding the chaos to delivering a working booking management system.
Business Problem
A car rental company running its entire operation on phone calls, WhatsApp messages, and Excel sheets — with no way to know in real time which cars were available.
An Indian car rental company with 85 cars across 3 city branches was managing all bookings manually. Customers would call, a branch agent would check an Excel sheet, confirm the car was available, and write down the booking. But the Excel sheet was only updated at that one branch — the other two branches couldn't see it. Cars going into the garage for service were sometimes still showing as available. And fleet managers had no way to see which cars were idle at which branch.
Problem 1 — Double Bookings Happening Every Week
When two agents at different branches both confirmed the same car to two different customers (or even two agents at the same branch checking a shared Excel at the same time), the car would get double-booked. The customer would arrive at the counter, wait 20+ minutes, and then be told no car was available. This happened on average 23 times per month. Each incident led to a complaint, a refund, and a customer who would likely never return.
Problem 2 — Cars in the Garage Still Showing as "Available"
When a car went in for service or repair, the Fleet Manager would inform the Branch Manager over WhatsApp. The Branch Manager would then manually update the Excel sheet. But this didn't always happen — the message got missed, or the sheet wasn't updated immediately. Agents then booked that car, and the customer arrived to find it was not ready. This happened across all 3 branches regularly.
Problem 3 — Fleet Sitting Idle Because Nobody Could See It
Fleet utilization was only 58% — meaning 4 out of every 10 cars were not being rented on any given day. The Operations Manager had no central view of which cars were available at which branch. If Branch A had 8 idle cars and Branch B had no cars available, there was no system to know this and redirect bookings. Customers would be turned away from one branch while another had unused cars just 20 km away.
| What Was Wrong | The Real Impact |
|---|---|
| Same car being booked by two agents at the same time on the same day | Customer arrives at the branch, no car is available. Complaint, refund, lost customer. |
| Cars in the garage marked as available because Excel wasn't updated in time | Agents booking cars that are physically not ready. Customer finds out only on arrival. |
| No central view — each branch only sees its own Excel sheet | Operations Manager can't see idle cars at Branch A being wasted while Branch B turns customers away. |
| No customer database — bookings had no history; same customer would fill a form every time | Agents manually re-typing the same customer's name and ID every single booking. |
| No digital record of car returns — verbal handover only | No accountability if a car is returned damaged. No record of who had the car and when. |
Stakeholder Identification
Mapping every person who was affected by the problem or needed to be involved in building the solution.
This company had 3 branches in 3 different cities. Each branch had its own team. The head office had an Operations Manager and a Finance Manager who needed visibility across all branches. I mapped everyone who would either provide information, approve decisions, or use the final system — and decided how to engage with each of them.
| Designation | What They Needed | Why They Matter | Power | Interest |
|---|---|---|---|---|
| Operations Manager (Head Office) | A single dashboard showing all 3 branches — total bookings, idle cars, revenue, complaints | Final decision maker; project sponsor; approved budget | High | High |
| Branch Manager (×3) | Real-time booking screen, ability to see which cars are available right now at their branch | Day-to-day users of the new system; critical for adoption | Med | High |
| Fleet Manager | A way to mark a car as 'In Service' or 'Back from Service' that immediately shows for everyone | Controls vehicle availability — most impacted person by the old manual system | Med | High |
| Customer Service Agents (×6) | A simple booking screen — enter customer name, date, car type, confirm. Fast and easy. | The people who create every booking; heavy daily users of the system | Low | High |
| Finance Manager | Monthly revenue report per branch; pending payments; total bookings vs. cancellations | Needs financial data; currently receives a manually compiled Excel from each branch | High | Med |
| IT Manager | Clear technical specifications before development begins | Will build and maintain the system; must be involved from early design | Med | Med |
Power / Interest Grid
Manage Closely — involve in every decision
· Operations Manager — project sponsor and final approver
· Finance Manager — budget and reporting owner
Keep Informed — update at each milestone
· Branch Managers (×3) — must buy in for adoption to succeed
· Fleet Manager — owns the most critical data field (car status)
Keep Satisfied — involve in UAT
· IT Manager — needs specs, not status updates
· Customer Service Agents — should test before go-live
Requirement Gathering
Understanding the problem from every angle — using different techniques because what an agent experiences at the counter is different from what the Operations Manager needs at the head office.
I used 4 techniques across 2 weeks. The most important one was process observation — sitting at the busiest branch counter for a full day and watching how agents actually handled bookings. What I saw in person was very different from what the stakeholders had described in their interviews.
| Technique | How I Did It | What I Found |
|---|---|---|
| 1:1 Interviews | 30–40 minute sessions with Operations Manager, Finance Manager, all 3 Branch Managers, and the Fleet Manager. Used structured questions each time. | Everyone described a different version of the same problem. The Branch Manager was worried about double bookings. The Fleet Manager was frustrated that no one updated the Excel when cars went to service. The Operations Manager just wanted one number: 'How many cars do I have available right now across all 3 branches?' |
| Process Observation | Spent one full working day (10am to 8pm) at the busiest branch, watching every booking being made, every phone call, every update to the Excel sheet. | In 10 hours, 14 bookings were made. 9 of those required the agent to first call the other branch to double-check they weren't booking the same car. Average booking call time: 18 minutes. Two near-misses where the same car was almost double-booked within the same hour. |
| Document Analysis | Reviewed 3 months of booking Excel files from all 3 branches, complaint logs, and the maintenance WhatsApp group chat history. | Found 23 confirmed double-booking incidents in 1 month. Found 11 instances where a car was booked that was in the service log — meaning 11 customers arrived to find their car wasn't ready, just in the last 3 months. Complaint log: 78% of complaints were about 'car not available on arrival'. |
| Group Workshop | 2-hour session with all 3 Branch Managers together, run at the head office. Used a whiteboard to map the current booking process step by step. | The 3 Branch Managers described 3 slightly different processes — each branch had informally evolved its own workflow over time. This explained why cross-branch coordination was so difficult. There was no standard process. |
Sample Interview Questions — Branch Manager
Walk me through exactly what happens from the moment a customer calls to book a car until they pick it up.
How do you currently know which cars are available right now at your branch?
When you find out a car you booked has already been booked by another agent, what do you do?
How do you find out that a car has gone to the garage for service? Who tells you and how?
What is the most frustrating part of your current booking process?
As-Is Process Analysis
A step-by-step map of exactly how a booking is handled today — from the first phone call to the customer driving away. Every problem is hidden inside this process.
| Step | What Happens | Who Does It | The Problem |
|---|---|---|---|
| Step 1 | Customer calls the branch to ask about car availability for specific dates | Customer Service Agent | Agent has no system — has to mentally recall bookings or look at a paper register first |
| Step 2 | Agent checks the branch Excel sheet for that date to see if any car of the required type is available | Customer Service Agent | Excel is only this branch's data. Agent cannot see if a car was booked at another branch and shifted here. |
| Step 3 | Agent calls the other 2 branches to double-check no one else has that car booked | Customer Service Agent | Takes 10–15 minutes. Sometimes agents at other branches don't pick up. Booking gets confirmed without full verification. |
| Step 4 | If available, agent verbally confirms with customer and manually writes booking in the Excel | Customer Service Agent | Two agents checking at the same time can both see 'available' and both confirm to different customers — classic race condition. |
| Step 5 | No confirmation sent to customer — customer is told to 'just come at your time' | — | Customer has no proof of booking. Disputes arise at the counter. |
| Step 6 | Car goes for service — Fleet Manager sends a WhatsApp to the Branch Manager | Fleet Manager | WhatsApp message can be missed or delayed. Branch Manager may not update Excel immediately. |
| Step 7 | If Excel not updated, agent books the service car — customer arrives to find it not ready | Customer Service Agent | Happens 4–5 times per month across all 3 branches. Major source of complaints. |
| Step 8 | Customer arrives for pickup — agent manually fills a paper rental agreement form | Customer Service Agent | Takes 18–22 minutes of data entry per customer. Customer waits at the counter. |
| Step 9 | Customer returns the car — verbal handover, agent checks it and writes notes on paper | Customer Service Agent | No digital record. If damage discovered later, no documentation to prove which customer caused it. |
| Step 10 | End of month: each branch compiles a revenue and booking summary in Excel and emails it to head office | Branch Manager | Head office receives 3 different Excel files with different formats. Finance Manager spends 4–5 hours combining them. |
Root Cause Analysis
Using the 5 Whys technique to find the actual root cause behind double bookings and low fleet utilization — so the solution fixes the real problem, not just the surface symptom.
5 Whys — Problem 1: Why do double bookings keep happening?
Why 1
Why do double bookings happen?
Two agents confirm the same car to two different customers at the same time, because both can see the car as 'available' in the Excel simultaneously.
Why 2
Why can both agents see the car as available at the same time?
Excel has no locking mechanism. When one agent is in the middle of confirming a booking, the file does not show 'in progress' to anyone else.
Why 3
Why is Excel being used for real-time booking management?
The company started with 5 cars and 1 branch. Excel worked fine then. As the business grew to 85 cars across 3 branches, no one upgraded the system.
Why 4
Why was the system not upgraded as the business grew?
There was no Operations Manager role until 18 months ago. Decisions about systems were made by individual Branch Managers who were too busy with day-to-day operations to step back and identify the problem.
Why 5
Why was there no one responsible for identifying and fixing operational system gaps?
The company grew informally. No structured review of operational processes was ever done. Problems were handled one at a time as they came up — not addressed at the root.
Root Cause Found
The root cause is not careless agents — it is using a tool (Excel) that is incapable of handling real-time concurrent bookings across multiple users and branches. No amount of training or process improvement can fix a structural limitation of the tool itself. The only fix is a booking system where confirming a booking immediately blocks that car for everyone else — in real time.
5 Whys — Problem 2: Why is fleet utilization only 58%?
Why 1
Why are 42% of cars idle on any given day?
Agents at Branch A don't know that Branch B has cars available. They simply tell customers 'we have no cars' — even though there are 8 available 20 km away.
Why 2
Why don't agents at Branch A know about available cars at Branch B?
There is no cross-branch view. Each branch's Excel file is a standalone document only that branch can edit or view.
Why 3
Why is there no cross-branch view?
Sharing a single Excel across 3 branches was tried but caused file corruption and merge conflicts. The branches went back to separate files.
Why 4
Why was no proper multi-user system considered when shared Excel failed?
The problem was escalated to the Operations Manager, but the immediate fix (separate files) was implemented and the underlying issue was never revisited.
Why 5
Why was the underlying issue never revisited?
No formal process exists for reviewing and improving operational systems. Problems get solved urgently and then forgotten.
| Category | Root Causes Found |
|---|---|
| People | Agents solving problems in silos — no cross-branch communication or shared ownership of fleet visibility |
| Process | No standard booking process across branches; no formal escalation for system issues; no operational review cycle |
| Technology | Excel is not designed for concurrent multi-user, multi-location booking management. File locking is not possible. |
| Data | No central customer database; no maintenance log integrated with availability; no historical booking data for capacity planning |
Gap Analysis
Comparing what the business has today against what it needs — making the scope of the new system completely clear before design begins.
| What the Business Needs | What Exists Today | The Gap | Priority |
|---|---|---|---|
| Real-time car availability — any agent sees the same up-to-date availability at any moment | A branch-specific Excel updated manually by agents at each branch | No live/shared view; availability 30 min to 24 hrs stale depending on when Excel was last updated | Critical |
| Double-booking prevention — once a car is confirmed, it must be locked for everyone else | No locking in Excel — two agents can see the same car as available simultaneously | Structurally impossible to prevent with the current tool. Only a proper DB system can do this. | Critical |
| Fleet Manager to update car status ('In Service') directly in the booking system | Fleet Manager sends a WhatsApp to Branch Manager who may or may not update Excel | 3–4 service car bookings per month happen because the update chain breaks | Critical |
| Cross-branch fleet view — Operations Manager sees all 3 branches at once | 3 separate Excel files; head office gets a combined report once a month | Operations Manager sees status only once a month, 30 days old | Critical |
| Digital check-in — customer details stored once, reused for every future booking | Paper form filled manually at the counter for every booking | 22-minute check-in per customer; same data re-entered every time; no customer history | High |
| Automated SMS confirmation sent to customer when booking is confirmed | No confirmation; customer told verbally to 'just come on the day' | No proof of booking for customer; disputes at counter; trust issues | High |
| Automated revenue report consolidated across all branches for Finance Manager | Finance Manager manually combines 3 Excel files every month (4–5 hours) | 4–5 hours of finance team time wasted monthly on data consolidation | Medium |
Business Requirements Document (BRD)
The BRD captures every business requirement in plain language — agreed and signed off by the Operations Manager and Finance Manager before any development begins.
| ID | Priority | What the System Must Do | Problem It Fixes | How We'll Know It's Done |
|---|---|---|---|---|
| BR-001 | Must | Show real-time car availability across all 3 branches in one single screen | Cross-branch visibility gap | Agent at Branch A can see available cars at Branches B and C without calling them |
| BR-002 | Must | When a booking is confirmed, the car must be immediately locked — no other agent can book it | Double bookings | No double booking in 1 month of testing with concurrent users |
| BR-003 | Must | Fleet Manager must be able to mark a car as 'In Service' from the system — instantly visible to all | Service car still bookable | Within 5 minutes of Fleet Manager updating status, no agent can book that car |
| BR-004 | Must | Store customer details once — when the same customer books again, auto-fill their information | 22-min manual check-in | Returning customer check-in completed in under 5 minutes |
| BR-005 | Must | Operations Manager must see a live dashboard: total bookings today, fleet status, revenue this month | Head office blind spot | Dashboard shows numbers updated within 15 minutes of any booking or change |
| BR-006 | Should | Send an automated SMS to the customer when their booking is confirmed — includes booking ID, car type, date and time | No confirmation issue | Customer receives SMS within 2 minutes of booking confirmation |
| BR-007 | Should | Generate an automated monthly revenue and booking report for Finance Manager — no manual compilation needed | 4–5 hr monthly effort | Finance Manager receives report automatically on the 1st of each month |
| BR-008 | Could | Allow customers to make bookings online through the company website | Reach and convenience | Online booking form live; customer can book and receive SMS without calling |
To-Be Process Design
How the same booking process will work after the new system is in place — comparing each old step to the new way.
The goal of the To-Be design was that any agent at any branch should be able to make a booking in under 5 minutes, with zero risk of a double booking, and with the customer getting a confirmation SMS automatically — without any manual coordination between branches.
New Booking Flow
| Old Way (AS-IS) | New Way (TO-BE) | Time Saved |
|---|---|---|
| Agent manually checks branch Excel for availability | System shows real-time availability across all 3 branches on one screen | 5–10 min saved per booking |
| Agent calls other branches to verify the car isn't booked there too | No need to call — system locks the car at the point of confirmation for everyone | 10–15 min saved per booking |
| Agent manually writes booking in Excel — risk of double booking | Booking saved to database — car instantly locked; zero risk of double booking | Error eliminated |
| No confirmation sent to customer | Automated SMS sent to customer within 2 minutes of booking | Counter disputes eliminated |
| Fleet Manager WhatsApps Branch Manager → Branch Manager updates Excel | Fleet Manager updates car status directly in system — visible immediately to all agents | Update delay eliminated |
| Customer fills paper form at counter every booking (18–22 min) | Returning customer auto-filled from database; new customer entered once (4–5 min) | 14–18 min per returning customer |
| Finance Manager combines 3 Excel files every month (4–5 hours) | Automated report generated on 1st of every month — Finance Manager downloads in 1 click | 4–5 hours/month saved |
Functional & Non-Functional Requirements
Functional requirements describe what the system must do. Non-functional requirements describe how well it must do it.
| ID | What the System Must Do (Functional) | From BR | Priority |
|---|---|---|---|
| FR-001 | Show all available cars across all 3 branches for a searched date range — with car type, branch location, and daily rate | BR-001 | Must |
| FR-002 | When an agent confirms a booking, the selected car must be locked in the system immediately — no other agent can select it | BR-002 | Must |
| FR-003 | Fleet Manager can update car status: Available / In Service / Under Repair / Retired. Change reflects immediately for all agents. | BR-003 | Must |
| FR-004 | Customer database: store name, phone number, ID proof type and number. Auto-fill when the same phone number is entered again. | BR-004 | Must |
| FR-005 | Operations dashboard: total bookings today (all branches), number of cars available (all branches), total revenue this month, last 7-day trend | BR-005 | Must |
| FR-006 | Booking confirmation SMS sent automatically: customer name, booking ID, car type, pickup date and time, branch address | BR-006 | Should |
| FR-007 | Generate and email a consolidated monthly report to Finance Manager: bookings, revenue, cancellations, per branch breakdown | BR-007 | Should |
| FR-008 | Car return flow: agent marks car as 'Returned' in system, records fuel level and any damage notes with a photo upload option | BR-002 | Should |
| FR-009 | Booking history per car — all past bookings for a specific car, with customer name, dates, and any damage notes | BR-004 | Could |
| ID | How the System Must Behave (Non-Functional) | Measured By | Priority |
|---|---|---|---|
| NFR-001 | Car availability screen must load within 2 seconds after search — agents cannot wait longer than this during a live customer call | Load test with 10 concurrent users | Must |
| NFR-002 | The car locking must happen within 1 second of booking confirmation — the smaller the window, the lower the double-booking risk | Test with 2 agents confirming same car simultaneously | Must |
| NFR-003 | System must be accessible from any desktop browser — agents use different computers at different branches | Test on Chrome, Firefox, Edge | Must |
| NFR-004 | Only employees with a valid login can access the system — no public access | Try accessing without login; should redirect to login | Must |
| NFR-005 | SMS must be sent within 2 minutes of booking confirmation | Time from confirmation to SMS delivery logged for 20 test bookings | Should |
User Stories & Acceptance Criteria
Each user story describes one feature from the perspective of the person who will use it — so the developer understands who they're building for and why, not just what to build.
Epic 1 — Real-Time Booking & Conflict Prevention
As a Customer Service Agent, I want to see all available cars across all 3 branches when I search for a date, so that I can book a car for the customer immediately without calling other branches.
As a Customer Service Agent, I want the car to be automatically locked the moment I confirm a booking, so that no other agent can book the same car even if they are confirming at the exact same time.
As a Customer, I want to receive an SMS immediately after my booking is confirmed, containing my booking ID and pickup details, so that I have proof of my booking and know exactly when and where to come.
Epic 2 — Fleet & Vehicle Status Management
As a Fleet Manager, I want to mark a car as 'In Service' directly in the system, so that it is immediately removed from available cars and agents cannot book it — without needing to call or WhatsApp anyone.
As a Fleet Manager, I want to see the full service history of any car — all past service dates, mileage at service, and what work was done — so that I can plan upcoming maintenance before a car breaks down.
Epic 3 — Operations & Finance Dashboard
As a Operations Manager, I want a single screen showing me — right now — how many cars are available, booked, in service, and idle across all 3 branches, so that I can spot idle cars at one branch and redirect bookings there.
As a Finance Manager, I want to automatically receive a monthly revenue report on the 1st of every month showing total bookings, revenue, and cancellations per branch, so that I do not have to compile it myself from 3 separate Excel files.
Acceptance Criteria — US-002 (Car Locking on Confirmation)
Given Car XYZ is showing as available in the system for 14th July
When Agent 1 and Agent 2 both try to confirm a booking for Car XYZ for 14th July at the exact same time
Then Only one booking goes through. The other agent sees an error message: 'This car was just booked. Please select another car.' Car XYZ is now locked and shows as Booked.
Given A car has been marked as 'In Service' by the Fleet Manager
When Any agent searches for available cars for today's date
Then The car marked 'In Service' does not appear in the search results at any branch. It is invisible to agents until Fleet Manager marks it as 'Available' again.
Given An agent is halfway through a booking (car selected but not yet confirmed)
When Another agent at a different branch selects and confirms the same car first
Then The first agent's screen shows: 'Car no longer available — it was just booked.' The first agent must go back and select a different car.
Process Flows & BPMN
BPMN diagrams visually show the step-by-step flow of each process — making it easy for developers, agents, and managers to all understand the same process in the same way.
I created 3 BPMN diagrams: the New Booking Flow, the Vehicle Return Flow, and the Car Maintenance Update Flow. The maintenance flow was the most critical — it was the process that caused service cars to be booked by accident, and needed the clearest redesign.
| Flow Name | Start Event | Key Decision Points | End Event |
|---|---|---|---|
| New Booking Flow | Customer requests booking (phone/walk-in) | Is customer returning? (Yes = auto-fill) → Is selected car still available? (checked again at confirmation) → Booking confirmed or select different car | Car locked in system, SMS sent to customer, booking ID generated |
| Vehicle Return Flow | Customer arrives to return car | Is car returned on time or delayed? → Does agent note any damage? → Is fuel level as agreed? | Car marked 'Available' in system, digital return record saved with agent's notes and timestamp |
| Car Maintenance Update Flow | Fleet Manager decides a car needs service | Fleet Manager marks car 'In Service' in system → System checks: are there any existing confirmed bookings for this car? → If yes, Branch Manager is alerted to contact those customers | Car removed from availability pool; if future bookings exist, Branch Manager notified to arrange alternatives |
Key Design Decisions Made During BPMN Design
Double-check availability at the moment of confirmation (not just at selection)
Even if a car shows as available when the agent selects it, the system checks one more time at the exact moment the agent clicks 'Confirm'. This handles the race condition — two agents selecting the same car at almost the same time. Only one can confirm it.
Alert before marking a car as 'In Service' if it has future bookings
If the Fleet Manager marks a car as 'In Service' and there are future confirmed bookings for that car, the system alerts the Branch Manager: 'Car ABC has 2 upcoming bookings on 15th and 18th July — please arrange alternatives.' Prevents customers from being surprised at pickup.
Return process is digital with a timestamp and agent ID
When an agent processes a car return, the system records: which agent did it, at what time, fuel level entered, and any damage notes. This creates an audit trail that previously did not exist — critical for damage disputes.
No online booking in Phase 1
The Operations Manager and I agreed to keep Phase 1 internal-only. Getting the internal booking process right is the prerequisite for exposing it to customers online. Online booking is in the Phase 2 backlog.
Data Analysis & Data Mapping
Before any system is built, I mapped every piece of data the system needs to store, where it comes from, and how different pieces connect to each other.
| Data Entity | What It Stores | Key Fields | Connects To |
|---|---|---|---|
| Vehicle Master | Details of every car in the fleet | Vehicle ID, Registration Number, Car Type (Hatchback/SUV/Sedan), Make, Model, Year, Branch ID, Status (Available/Booked/In Service/Retired), Daily Rate (₹) | Booking table, Maintenance table, Branch table |
| Customer | Details of every customer who has ever booked | Customer ID, Full Name, Phone Number, ID Proof Type, ID Proof Number, Email (optional), City | Booking table |
| Booking | Every booking ever made | Booking ID, Customer ID, Vehicle ID, Pickup Date, Return Date, Total Days, Total Amount (₹), Agent ID, Branch ID, Status (Confirmed/Active/Completed/Cancelled), Booking Timestamp | Customer, Vehicle, Agent, Branch |
| Branch | The 3 operating locations | Branch ID, City Name, Address, Phone Number, Branch Manager Name | Vehicle, Booking, Agent |
| Maintenance Log | Every service or repair event for every car | Log ID, Vehicle ID, Start Date, Expected Return Date, Garage Name, Work Description, Cost (₹), Updated By (Fleet Manager ID) | Vehicle Master |
| Agent / User | All system users and their roles | Agent ID, Name, Branch ID, Role (Agent / Branch Manager / Fleet Manager / Finance / Operations / Admin), Login ID | Booking, Branch |
-- Find all cars available for a given date range (no overlapping bookings, not in service)
-- This query is at the heart of the availability search screen
SELECT
v.vehicle_id,
v.registration_number,
v.car_type,
v.make,
v.model,
b.city_name AS branch,
v.daily_rate
FROM vehicles v
JOIN branches b ON v.branch_id = b.branch_id
WHERE v.status = 'Available'
AND v.vehicle_id NOT IN (
-- Exclude cars that have an overlapping confirmed or active booking
SELECT vehicle_id
FROM bookings
WHERE status IN ('Confirmed', 'Active')
AND pickup_date < '2024-07-20' -- requested return date
AND return_date > '2024-07-14' -- requested pickup date
)
ORDER BY b.city_name, v.car_type, v.daily_rate;-- Fleet utilization report: what % of each car's available days was it actually booked?
-- Used in the Operations Manager dashboard
SELECT
v.vehicle_id,
v.registration_number,
v.car_type,
br.city_name,
COUNT(bk.booking_id) AS total_bookings,
SUM(bk.total_days) AS total_rented_days,
DATEDIFF(day, '2024-01-01', GETDATE()) AS total_possible_days,
ROUND(
100.0 * SUM(bk.total_days)
/ NULLIF(DATEDIFF(day, '2024-01-01', GETDATE()), 0),
1
) AS utilization_pct
FROM vehicles v
JOIN branches br ON v.branch_id = br.branch_id
LEFT JOIN bookings bk ON v.vehicle_id = bk.vehicle_id
AND bk.status IN ('Completed', 'Active')
AND bk.pickup_date >= '2024-01-01'
GROUP BY v.vehicle_id, v.registration_number, v.car_type, br.city_name
ORDER BY utilization_pct DESC;Wireframes & Prototypes
Simple mockups of every screen reviewed with the actual users before a single line of code was written. This ensured the developer knew exactly what to build.
Screen 1
Booking Search
Main Question It Answers
Which cars are available for the dates the customer wants?
For: Customer Service Agent
·Date picker: pickup date + return date
·Car type filter (Any / Hatchback / Sedan / SUV)
·Results: list of available cars showing branch, type, rate
·One-click to start booking — customer details auto-filled if returning customer
Screen 2
Fleet Status Board
Main Question It Answers
What is the current status of every car right now?
For: Fleet Manager
·All 85 cars listed with their current status (colour-coded)
·Available (green) / Booked (blue) / In Service (amber) / Retired (gray)
·One click to change status — with a mandatory reason note for In Service
·Filter by branch, car type, or status
Screen 3
Operations Dashboard
Main Question It Answers
How is the entire fleet performing across all 3 branches right now?
For: Operations Manager
·Live summary: total available / total booked / total in service (all branches)
·Branch-wise breakdown in 3 columns side-by-side
·Today's bookings count and this month's revenue
·Utilization % — which branch has the most idle cars
Screen 4
Booking Detail & Return
Main Question It Answers
Full record of a specific booking — used at pickup and return
For: Customer Service Agent
·Booking ID, customer name, car details, dates, total amount
·Pickup confirmation: agent marks 'Car Given Out' with timestamp
·Return: agent enters fuel level, damage notes, uploads photos if needed
·Status automatically updates to 'Completed' and car becomes Available
Requirement Validation
Every requirement formally reviewed and approved before development starts — so there are no surprises during the build.
| Validation Step | When | Who | What Was Reviewed | Result |
|---|---|---|---|---|
| BRD Review Meeting | End of Week 3 | Operations Manager + Finance Manager | All 8 business requirements confirmed; acceptance criteria reviewed | Approved — BR-008 (online booking) moved to Phase 2 |
| Stakeholder Walkthrough | Week 4 | All 6 stakeholders | Full requirements summary; each person confirmed their use case was covered | Approved — Fleet Manager added one new request: alert when service car has upcoming bookings |
| FRS Technical Review | Week 5 | IT Manager + Operations Manager | All 9 functional requirements reviewed for technical feasibility and effort | FR-009 (booking history per car) moved to Phase 2 — complexity was too high for current timeline |
| Wireframe Review Round 1 | Week 6 | Branch Managers + Customer Service Agent | All 4 screens walked through; feedback collected | 5 change requests — default to own branch, simplify date picker, add car photo on booking screen |
| Wireframe Review Round 2 | Week 7 | Operations Manager + Branch Manager | Updated wireframes reviewed after Round 1 changes | Approved. IT Manager given green light to begin development. |
Backlog Creation & Prioritization
The full list of everything to be built, sorted by what is most critical to deliver first — using MoSCoW prioritization.
| Priority | Stories | What Gets Built | Sprint |
|---|---|---|---|
| Must Have | 10 stories — 61 pts | Vehicle database, customer database, real-time availability search, car locking on confirmation, Fleet Manager status update, fleet status board, basic booking creation and confirmation | Sprint 1 & 2 |
| Should Have | 6 stories — 31 pts | SMS confirmation, automated monthly finance report, car return process with damage notes, Operations Manager dashboard, agent login and role-based access | Sprint 3 |
| Could Have | 4 stories — 18 pts | Fleet utilization chart on Operations dashboard, car photo on booking screen, maintenance alert for upcoming bookings when car marked 'In Service' | Sprint 4 |
| Won't Have | 3 stories — — | Online customer booking portal, payment gateway integration, WhatsApp booking bot (Phase 2 backlog) | Phase 2 |
Sprint Plan — 4 Sprints of 2 Weeks Each (10 Weeks Total)
Database setup: vehicles, customers, bookings, branches, agents. Basic login and role access. Car availability search with real-time results across all branches.
Booking confirmation with car locking mechanism. Fleet Manager status update screen (Available / In Service). Car return flow with damage notes. Core system usable end-to-end.
Operations Manager dashboard (live fleet summary all 3 branches). SMS confirmation integration. Automated monthly finance report. Role-based access polished per user type.
Fleet utilization chart. Maintenance alert for upcoming bookings. UAT defect fixes. Performance testing. Go-live preparation and user training.
Agile Sprint Execution
Running 2-week sprints — building and testing a working piece of the system every sprint, rather than trying to build everything at once.
| Agile Ceremony | How Often | What I Did as BA |
|---|---|---|
| Sprint Planning | Start of each sprint (Monday) | Walked the IT Manager through the top-priority user stories for the next 2 weeks; clarified every acceptance criteria before any coding started; confirmed what 'done' means for each story |
| Daily Stand-Up | Every day — 10 minutes | Flagged any open questions coming from development; answered ambiguity on the spot; raised blockers to the Operations Manager if needed (e.g., IT Manager needed access to SMS gateway account) |
| Sprint Review | End of each sprint (Friday) | Demonstrated the completed features to Branch Managers and Operations Manager using real test data; captured feedback; added new items to backlog if needed |
| Sprint Retrospective | End of each sprint (Friday) | Team discussion: what went well, what was slow, what to change next sprint — kept these short (20 min) but consistently applied |
| Backlog Refinement | Mid-sprint (Wednesday, biweekly) | Reviewed upcoming stories with IT Manager; broke large stories into smaller tasks; flagged technical risks before they became sprint blockers |
Development Support
The BA's role during development — handling requirement questions, managing mid-build changes, and preventing scope creep from derailing the timeline.
| Situation | What Happened | What I Did as BA | Outcome |
|---|---|---|---|
| Requirement ambiguity | IT Manager asked: when a car is returned, should its status change to 'Available' immediately or only after the agent inspects it? | Raised with Branch Manager and Fleet Manager — both said inspection happens immediately at return. Status should update to Available only after agent clicks 'Inspection Done'. Documented this in the FRS. | Developer built the correct flow; no rework needed |
| Stakeholder change request | Operations Manager asked mid-Sprint 3 to add a 'Download as PDF' button to the monthly finance report | Assessed impact: 1 additional day of work. Agreed with Operations Manager to add to Sprint 4. Finance Manager confirmed email format was fine for now. | Sprint 3 not disrupted; PDF added in Sprint 4 |
| Data migration question | All 3 branches had active bookings in Excel that needed to be in the new system from Day 1 — or agents would have to check two systems for the first few days | Worked with each Branch Manager to extract all active bookings into a template. IT Manager imported them into the database in a 2-hour migration session before go-live. | New system had all active bookings on Day 1; agents never needed to use old Excel in parallel |
| SMS gateway setup | The company had no SMS service account — required to send booking confirmations | Raised with Operations Manager in Week 3 when I identified this gap in the technical review. Operations Manager signed up for an SMS gateway (Textlocal) within 2 days. Did not become a blocker. | SMS feature was ready on time for Sprint 3 |
Testing Support
Designing the test plan and helping the team catch bugs before UAT — because finding a problem before users test it is much cheaper than finding it during UAT.
| What I Did | Details |
|---|---|
| Wrote the UAT Test Plan | Created a document with 40 test cases, expected result for each, who would run it, and the pass/fail criteria — before testing started. Shared with IT Manager and Branch Managers 1 week before UAT. |
| Tested the locking mechanism specifically | Opened two separate browser windows logged in as two different agents. Both searched for the same car on the same date. Timed clicking 'Confirm' at almost exactly the same time 20 times. Car was successfully locked to the first confirmation in all 20 tries. |
| Designed edge case tests | Added tests for: What if both pickup and return date are the same day? What if the Fleet Manager marks a car 'In Service' while an agent is mid-booking? What if an agent logs in from Branch A but tries to book a car at Branch B? |
| Classified defects during triage | Attended daily triage sessions with IT Manager during test week. For each defect found: is it a requirements issue (my responsibility), a build issue (IT Manager's responsibility), or a data issue (migration)? |
User Acceptance Testing (UAT)
Real users testing the system using realistic scenarios — confirming it does exactly what was agreed in the BRD before go-live approval is given.
| What Was Tested | Test Cases | Tested By | Passed | Failed | Status |
|---|---|---|---|---|---|
| Booking creation and real-time availability | 10 | Branch Manager + Customer Service Agent (×2) | 9 | 1 | 1 Fixed |
| Car locking — no double bookings possible | 6 | IT Manager + Branch Manager | 6 | 0 | All Pass |
| Fleet Manager status update (In Service / Available) | 6 | Fleet Manager | 5 | 1 | 1 Fixed |
| Operations Manager dashboard — all branches | 8 | Operations Manager | 8 | 0 | All Pass |
| SMS confirmation — customer receives within 2 min | 5 | Customer Service Agent | 5 | 0 | All Pass |
| Car return flow with damage notes | 5 | Customer Service Agent + Branch Manager | 4 | 1 | 1 Fixed |
Defects Found in UAT & How They Were Fixed
When a Customer Service Agent searched for cars at Branch A, the results also showed cars that were currently In Service (marked by Fleet Manager). Root cause: the availability query was not filtering by vehicle status — only checking for booking conflicts but not the status field. Fixed by IT Manager in 3 hours. Re-tested and confirmed.
When Fleet Manager marked a car 'In Service' and that car had a booking starting in 2 days, the Branch Manager did not receive the alert. Root cause: the alert trigger was checking for bookings starting 'today' only — not future dates. Fixed to check all future confirmed bookings for that car.
On the car return screen, the damage notes text box only allowed 100 characters — agents needed more space to describe damage in detail. Changed to 500 characters with a character counter.
Deployment / Go-Live
A phased rollout — starting with one branch first to catch any real-world issues before all 3 branches go live on the same day.
| Day | What Happened | Who Was Responsible |
|---|---|---|
| Day –2 | Data migration: all active bookings from all 3 branch Excel files imported into the new system database | IT Manager + BA |
| Day –1 | Final system check: all 85 vehicles loaded into the database with correct status; all agent logins tested and confirmed working | IT Manager + BA |
| Day 1 AM | Branch A (busiest branch) goes live. Excel officially archived. Agents use the new system for all bookings from this point forward. | Branch Manager A |
| Day 1 PM | BA present at Branch A from 10am to 6pm — available for any questions, taking notes on any usability issues observed in real use | BA |
| Day 2 AM | After 1 day of smooth operation at Branch A, Branches B and C go live simultaneously | IT Manager + Branch Managers B and C |
| Day 3 | First real cross-branch booking tested: customer at Branch B needed a car; Branch B had none; agent found and booked a car at Branch A in 3 minutes without calling | Customer Service Agent |
| Week 2 | Hypercare period: IT Manager and BA both available daily for quick response to any system issue. Two minor display issues reported and fixed within 24 hours each. | IT Manager + BA |
| Day 32 | First automated monthly finance report generated and emailed to Finance Manager on the 1st of the month — no manual action | System (automatic) |
Training & Change Management
The booking system is only useful if the agents actually use it. Training focused on making each person confident in exactly the screens they use — nothing more.
| Who Was Trained | Format | Duration | What Was Covered |
|---|---|---|---|
| Customer Service Agents (×6) | Hands-on session at the branch counter using the live system | 45 min each | Booking search, creating a new booking, looking up a returning customer, confirming a booking, processing a return with damage notes |
| Branch Managers (×3) | 1-on-1 session with each Branch Manager at their own branch | 30 min each | All the above + branch-level performance view, how to check booking history for a specific car, how to handle a booking if a car becomes unexpectedly unavailable |
| Fleet Manager | 1-on-1 session | 30 min | How to mark a car In Service and Available, the maintenance alert system, how to view and update the maintenance log |
| Operations Manager | 1-on-1 session at head office | 30 min | Operations dashboard — how to read it, which numbers to track daily, how to use fleet utilization data to redirect bookings from idle branches |
| Finance Manager | 15-minute walkthrough | 15 min | How to download the auto-generated monthly report; how to filter by branch; what each column means |
Change Management Actions
Excel archived on Day 1 — not kept as backup
Deliberately archiving the old Excel files on the day of go-live removed the option to fall back to the old system. Agents who were unsure of the new system had no choice but to ask for help and learn it — which they did quickly. The Branch Managers championed this decision.
Branch Manager trained first, trained their own team
Each Branch Manager was trained 2 days before their branch went live. They then ran a 20-minute demo for their own agents. Having their own manager explain the system was more trusted than an outside trainer.
1-page quick reference card printed at each counter
A laminated A5 card with 4 steps: How to search for a car, How to confirm a booking, How to process a return, Who to call if something goes wrong. Still hanging at the counter 2 months later.
BA available at Branch A for the full first day
Physical presence on go-live day removed agent anxiety. The agents knew someone was there to help immediately if anything went wrong. No issues required escalation — but the presence itself made adoption much smoother.
Post-Implementation Review
Reviewed 4 weeks after go-live — did the system deliver what was promised? What worked, what didn't, what to improve?
| Finding | Type | Detail |
|---|---|---|
| Double bookings: 23/month → 0 in 4 weeks post go-live | Positive | The car locking mechanism completely eliminated double bookings. Not a single incident in 4 weeks of full operation across all 3 branches. |
| Customer check-in time reduced from 22 minutes to under 6 minutes | Positive | Returning customers auto-filled from the database. New customers entered once. Agents confirmed the new check-in was dramatically faster and less stressful. |
| Operations Manager now checks dashboard every morning instead of calling branches | Positive | Operations Manager confirmed they no longer make the daily branch call. 'I open the dashboard and I can see everything in 2 minutes.' |
| Fleet utilization improved from 58% to 72% in first month | Positive | Agents now see idle cars at other branches and redirect customers there instead of turning them away. Still growing as agents get more comfortable using the cross-branch view. |
| One agent at Branch C still calling other branches sometimes | Improvement | One agent had formed the habit strongly over 4 years. Their Branch Manager is doing additional 1-on-1 coaching. Will monitor in the next review. |
| SMS delivery occasional delay on weekends — 5–8 minutes instead of 2 | Learning | SMS gateway has lower throughput on weekends. Raised with the SMS provider. They confirmed it is a plan limitation — Operations Manager considering upgrading the SMS plan. |
3 Key Lessons from This Project
The right tool solves the problem; process improvements alone cannot
No amount of agent training or new Excel guidelines could have prevented double bookings — because the problem was structural. Excel simply cannot lock a record for concurrent users. Recognising that the solution needed to be a technology change (not a process change) was the most important early decision.
Phased rollout is worth the extra day
Starting with Branch A on Day 1 instead of all 3 simultaneously caught a real issue (agent incorrectly marking a returned car as In Service) while it only affected one branch. Small risk window = faster fix, lower impact. Always worth the extra day.
Migrate live data before go-live day — never run two systems in parallel
Importing all active bookings from Excel before go-live meant agents used only one system from Day 1. Projects that keep the old system running 'just in case' create confusion, split attention, and typically take twice as long to fully adopt the new system.
Business Impact Measurement
Measured at 8 weeks post go-live — comparing actual results against what was promised in the BRD.
| What Was Promised in BRD | Target | Actual Result (8 Weeks) | Status |
|---|---|---|---|
| Real-time car availability across all 3 branches in one screen | Single screen, all branches, live data | Working; agents at all 3 branches using it daily | Done |
| Car locked immediately on booking confirmation — no double bookings | Zero double bookings in 4 weeks of testing | Zero double bookings in 8 weeks of live operation | Done |
| Fleet Manager marks car 'In Service' directly — immediately visible to all | Status change visible in under 1 minute | Status change visible in under 5 seconds in all tests | Done |
| Returning customer check-in: auto-fill from database | Check-in under 5 minutes for returning customers | Average 4.2 minutes for returning customers | Done |
| Operations dashboard: all 3 branches live, updated within 30 minutes | Dashboard live, refreshes every 30 min | Dashboard running; Operations Manager checks it every morning | Done |
| SMS confirmation to customer within 2 minutes of booking | SMS delivered within 2 minutes | Weekdays: avg 80 seconds. Weekends: avg 6 minutes (gateway limit) | Partial |
| Automated monthly finance report — no manual compilation | Report auto-generated on 1st of month | Finance Manager received report automatically for 2 months running | Done |
“Before this system, I would start each day calling all 3 branches to find out what cars we had available. Now I open one screen. It shows me everything — which branch has idle cars, how many bookings came in yesterday, what the revenue looks like. I make better decisions in the morning than I used to make all day.”
— Operations Manager · 8 weeks post go-live