# VIP HOLIDAYS & TRAVEL SDN BHD — Chauffeur & Car Booking System

## Phase 1 analysis of `VIP_HOLIDAYS_Booking_Dispatch_TEXT_ONLY_PRICE_BLANK.xlsx`

Date: 15 Nov 2026 · Status: **analysis only, nothing changed, waiting for your approval**

Every finding below comes from the workbook itself: each cell, formula, dropdown list and colour rule was read. All customer, driver and vehicle records in the file are marked `(SAMPLE)`.

---

## A. Complete Excel understanding

### A.1 Sheet overview

| # | Sheet | Visible | Contents |
|---|---|---|---|
| 1 | BOOKING MASTER | Yes | 3 sample bookings (rows 2–4), lookup formulas copied down to row 501 |
| 2 | DAILY DISPATCH | Yes | One Google Sheets array formula (A6) driven by the date in B1 |
| 3 | DRIVER MASTER | **Hidden** | 3 drivers (P, C, B): code, name, WhatsApp phone, status, notes |
| 4 | VEHICLE MASTER | **Hidden** | 3 vehicles: plate, model, seat capacity, status, notes, colour |
| 5 | COPY TEXT | Yes | Copy instructions and A6 = `'MESSAGE CENTRE'!B7` |
| 6 | MESSAGE CENTRE | Yes | Booking ID (B3) + recipient type (B4: Driver / Customer) → message in B7 |
| 7 | DASHBOARD | Yes | 9 metrics for the report date in B2 |
| 8 | AUDIT LOG | Yes | Headers only, no rows, no automation |
| 9 | READ ME | Yes | Operating notes and limitations |
| 10 | DRIVER & VEHICLE MASTER | Yes | 3 drivers, each with a fixed vehicle (the combined master) |

No sheet is protected. There are no named ranges and no macros or Apps Script.

### A.2 BOOKING MASTER (columns A–X)

| Col | Header | How it is filled |
|---|---|---|
| A | Booking ID | Typed, e.g. `VIP-261017-001` (the date part matches the **service** date, 17 Oct 2026, not the created date) |
| B | Created At | Typed date-time |
| C | Service Date | Typed date |
| D | Pickup Time | Typed time |
| E | Customer Name | Typed |
| F | Customer Phone | Typed as text with country code (+60 / +65) |
| G | Pickup Location | Typed (the messages call it "Pick up address 1") |
| H | Destination | Typed |
| I | Pax | Typed number |
| J | Price (SGD) | Typed. Sample rows: 180, 220, 260 |
| K | Price (RM) | Typed. Blank in every sample row |
| L | Booking Notes | Typed. **Carries the driver code dropdown.** |
| M | Driver Code | **Formula** (should be the input) |
| N | Driver Name (Auto) | Formula |
| O | Driver Phone (Auto) | Formula |
| P | Vehicle Plate | **Typed by hand.** Carries the Order Status dropdown and the status colouring. |
| Q | Order Status | Typed. Carries the *message* status dropdown. |
| R | Driver Message Status | Dropdown (Not Generated / Ready / Opened / Sent Confirmed / Needs Regeneration) |
| S | Customer Message Status | Typed, **no dropdown** |
| T | Updated At | Typed date-time, no automation |
| U | Pickup Address 2 | Typed (optional second pickup) |
| V | Car Type | Formula |
| W | Vehicle Model (Auto) | Formula |
| X | Vehicle Colour (Auto) | Formula |

Order Status values (from the dropdown): **Pending Confirmation, Confirmed, Completed, Cancelled**.

### A.3 The one-column shift (the root cause of most errors)

The formulas, dropdowns and colour rules were written for a layout **without** the `Price (RM)` column. Column K was inserted later, and nothing that depended on the columns to its right was moved with it. Every BOOKING MASTER lookup still keys off **column L**, which is now Booking Notes:

| Cell | Formula actually returns | Header says |
|---|---|---|
| M | Driver **name** (`VLOOKUP(L, D&V MASTER, 2)`) | Driver Code |
| N | Driver **phone** (col 3) | Driver Name |
| O | Vehicle **plate** (col 5) | Driver Phone |
| V | Vehicle **model** (col 6) | Car Type |
| W | Vehicle **colour** (col 8) | Vehicle Model |
| X | Colour looked up from O in the hidden VEHICLE MASTER | Vehicle Colour |

The dropdowns and colouring are also one column off:

| Rule | Applied to | Should be on |
|---|---|---|
| Driver code list | L (Notes) | M |
| Order Status list + green/red colouring | P (Vehicle Plate) | Q |
| Message status list | Q:R | R:S |

**What staff see today:** L holds note text, not a driver code, so every lookup returns blank. The driver, phone, car type, model and colour lines in both WhatsApp messages come out empty. If a staff member picks a driver code from the dropdown in L, they overwrite the booking notes. If they type a code into M, they delete its formula.

Further evidence of later edits: READ ME items 2 and 3 say "DRIVER & DRIVER & VEHICLE MASTER" and "DRIVER & VEHICLE MASTER (plate numbers)". This looks like a find-and-replace that renamed DRIVER MASTER and VEHICLE MASTER.

### A.4 DAILY DISPATCH

- B1 is the dispatch date (sample: 17 Oct 2026).
- A6 holds `SORT(FILTER({BM!A, C:S …}, date = B1, BM!P <> "Cancelled", ID <> ""), 3, TRUE)`. It returns 18 columns sorted by pickup time, or "No bookings for this date".
- The columns it pulls **do** match the current layout, because this formula was updated after the insert.
- The "exclude cancelled" test reads **P (plate)**, not Q (status), so **cancelled bookings still appear**.
- `{range, range}` is Google Sheets syntax. Desktop Excel will not calculate it (READ ME item 4 says so).
- Pickup Address 2, Car Type, Vehicle Model and Colour are not shown.

### A.5 DRIVER MASTER / VEHICLE MASTER (hidden) and DRIVER & VEHICLE MASTER

- The combined master (A–J): Driver Code, Driver Name, Driver Phone, Driver Status, Vehicle Plate, Vehicle Model, Seat Capacity, Vehicle Colour, Vehicle Status, Notes.
- The hidden sheets hold the **same** 3 drivers and 3 vehicles a second time (e.g. driver P → VAA1234, MPV, 7 seats, White).
- BOOKING MASTER M/N/O/V/W read the combined master, while X reads the hidden VEHICLE MASTER. There are two sources for the same facts, and nothing keeps them in step.
- **Seat Capacity, Driver Status and Vehicle Status are not used by any formula.** No capacity check and no active check exist.

### A.6 MESSAGE CENTRE and COPY TEXT

- B3 = Booking ID, B4 = Driver / Customer (dropdown).
- B5/B6 (recipient name and phone): for a customer they read E/F (correct). For a driver they read columns 13/14 = **M/N**, which are Driver Code and Driver Name, not name and phone. **The driver's phone number shown is wrong.**
- B7 builds the message from the **current** columns (N, O, P, U, V, W, X, J, K, L), so this formula is correct once BOOKING MASTER is fixed.
- **Customer message** (format to preserve):
  ```
  Book Your Transport Here 🚗

  Name and contact : <name>, <phone>

  Date : 17 Oct 2026 (Sat)
  Time : <time>

  1) Pick up address : <pickup>
  2) Pick up address : <address 2>      ← only if filled

  Destination : <destination>

  Pax : <pax>
  Car type : <car type>
  SGD <price>                           ← each currency line only if filled
  RM <price>

  Driver : …
  Phone number : …
  Car number : …
  Car model : …
  Colour : …

  Booking confirmed, thanks for your support 😊
  ```
- **Driver job order:** "VIP HOLIDAYS & TRAVEL SDN BHD / DRIVER JOB ORDER", then Booking ID, Date, Time, Customer, Customer phone, Pick-up address 1 (and 2 if filled), Destination, Pax, Car type, prices, Driver, Phone number, Car number, Car model, Colour, and Notes if filled.
- Formatting faults in B7:
  - Time format `hAM/PM` drops the minutes: **08:30 prints as "8AM"**. This is wrong information sent to a customer.
  - Price format `0.##` prints **180 as "180."**, with a trailing dot.
  - When no driver is assigned, the customer message still prints the empty "Driver :" lines. Nothing stops it being sent.
- "WhatsApp URL (sample)" (A9) has no link formula. B9 is only the merged instruction text.
- COPY TEXT A6 mirrors B7. Its steps (Name Box → F2 → Ctrl+A → Ctrl+C) differ from the shorter steps in MESSAGE CENTRE B8.

### A.7 DASHBOARD (date in B2)

| Metric | Reads | Should read | Effect today |
|---|---|---|---|
| Total Bookings | C, A | — | Correct |
| Confirmed / Pending / Cancelled | **P** (plate) | Q | Always 0 |
| Not Yet Assigned Driver | **L** (notes) blank | M blank | Counts bookings with no notes |
| Driver Message Pending | **Q** (order status) | R | Counts nearly everything |
| Customer Message Pending | **R** (driver msg) | S | Shows the driver figure |
| Booking Value SGD / RM | J / K, excluding P = "Cancelled" | exclude Q = "Cancelled" | Cancelled bookings are included in the value |

The note in A14 is correct and should be kept: booking value is the listed price, not cash collected or profit.

### A.8 AUDIT LOG

Columns: Timestamp, User Email, Booking ID, Action, Field Changed, Old Value, New Value, Notes. The sheet is empty. A workbook cannot fill it by itself. It needs a script (Apps Script) or an application.

### A.9 READ ME — rules stated by the business

1. Phones are stored as text with the country code (+60 / +65).
2. Service Date and Pickup Time are separate fields. Staff must confirm the local SG/MY date and time.
3. Opening WhatsApp ≠ sent. Staff check, send by hand, then mark **Sent Confirmed**.
4. **Each driver has a fixed vehicle.** Staff pick a driver code, and name, phone, plate, model and colour follow.
5. Buttons, audit logging and dynamic sending are *not* built. The workbook is a starter only.

---

## B. Booking workflow (receipt → completion)

| Step | Who | What happens | Status after |
|---|---|---|---|
| 1 Receive | Staff | Enquiry arrives (WhatsApp / phone / agent) | — |
| 2 Record | Staff | Enter customer, phone, date, time, pickup 1 (+2), destination, pax, notes, SGD and/or RM price | **Pending Confirmation** |
| 3 ID | System | Issue the next Booking ID: `YYMM` + running number, restarting monthly (D2) | — |
| 3a Car Type | Customer picks, or system suggests | Only car types with seats ≥ pax are offered (D1) | — |
| 4 Validate | System | Required fields, phone format, pax > 0, date not in the past, duplicate check | — |
| 5 Confirm | Staff | Price and details agreed with the customer | **Confirmed** |
| 6 Assign | Staff picks driver; system fills the rest | Name, phone, plate, model, colour, capacity filled from the master. Blocks inactive driver or vehicle, pax > seats, clashing jobs | Confirmed + driver |
| 7 Customer message | System builds, staff reviews | Customer confirmation text | Customer msg **Ready** |
| 8 Driver job order | System builds, staff reviews | Job order text | Driver msg **Ready** |
| 9 Send | Staff | Opens WhatsApp (status **Opened**), sends by hand, then ticks **Sent Confirmed** | Sent Confirmed |
| 10 Change | Staff | Any change to time, address, pax, price or driver after a message was ready sets that message to **Needs Regeneration** | Needs Regeneration |
| 11 Dispatch | Ops | Day board by pickup time, with alerts for unassigned, unsent and clashing jobs | — |
| 12 Close | Staff | After the trip → **Completed**; or **Cancelled** (with reason) at any point | Completed / Cancelled |

**Staff decide:** confirmation, price, which driver, reviewing and sending messages, Sent Confirmed, completion and cancellation.
**The system does:** the ID, every lookup, every check, message text, status resets after edits, dispatch list, dashboard and audit trail.

---

## C. System flow diagram

```
                  ┌──────────────────────────────┐
                  │ DRIVER & VEHICLE MASTER      │  (single master — replaces
                  │ driver ⇄ fixed vehicle       │   hidden DRIVER / VEHICLE MASTER)
                  └──────────────┬───────────────┘
                                 │ lookup by Driver Code
 Customer enquiry                ▼
 ───────────────►  ┌──────────────────────────────┐  every change   ┌────────────┐
   staff entry     │ BOOKING MASTER               │ ───────────────►│ AUDIT LOG  │
                   │ one row per booking          │                 └────────────┘
                   └───┬──────────────┬───────────┘
          by date      │              │ by Booking ID
          ┌────────────┘              └──────────────┐
          ▼                                          ▼
 ┌──────────────────┐   ┌──────────────┐   ┌───────────────────┐
 │ DAILY DISPATCH   │   │ DASHBOARD    │   │ MESSAGE CENTRE    │
 │ day board, by    │   │ counts and   │   │ customer / driver │
 │ pickup time      │   │ booking value│   │ text → COPY TEXT  │
 └──────────────────┘   └──────────────┘   └─────────┬─────────┘
                                                     ▼
                                       WhatsApp (sent by hand)
                                                     │
                                  staff marks Sent Confirmed
                                                     ▼
                                    BOOKING MASTER R / S status
```

---

## D. Data relationship design

```
CUSTOMER 1 ──< BOOKING >── 0..1 DRIVER 1 ── 1 VEHICLE
                  │
                  └──< AUDIT ENTRY
                  └──< MESSAGE LOG (customer / driver, status, who, when)
```

| Entity | Key | Main fields |
|---|---|---|
| Customer *(new; today the name and phone are retyped on every booking)* | Phone | Name, phone, notes |
| Booking | Booking ID | Customer, service date, pickup time, pickup 1/2, destination, pax, car type, SGD, RM, notes, driver code, order status, two message statuses, created/updated |
| Driver | Driver Code | Name, phone, status, **assigned vehicle plate** |
| Vehicle | Plate | Model, seat capacity, colour, status |
| Audit entry | auto | Time, user, booking, action, field, old, new, notes |

Rules:
- One driver → one fixed vehicle. The vehicle is never typed on a booking; it comes from the driver. A vehicle change is a master-data change by an authorised user, and it is logged.
- The booking stores the driver code. It should also keep a **copy** of the plate, model and colour at the time of sending, so old bookings and sent messages still show what the customer was told.
- SGD and RM are two separate inputs. **No conversion.**

---

## E. Error and risk analysis

| # | Severity | Finding | Fix |
|---|---|---|---|
| 1 | **Critical** | All lookups key off L (Notes) instead of M (Driver Code) | Re-point the key to M |
| 2 | **Critical** | M (Driver Code) holds a formula, so entering a code destroys it | M becomes a plain dropdown input |
| 3 | **Critical** | N, O, V, W return the wrong fields (phone in Name, plate in Phone, …) | Re-map: N = col 2, O = col 3, P = col 5, W = col 6, X = col 8 |
| 4 | **Critical** | Vehicle Plate (P) is typed by hand, which breaks the fixed-vehicle rule (row 3 has a plate but no driver) | P becomes a lookup from the driver |
| 5 | **Critical** | Order Status dropdown and colours sit on P; Q has the message list; S has no list | Move the lists to Q, R, S |
| 6 | **High** | Dashboard status, driver, message and value metrics read the wrong columns | P→Q, L→M, Q→R, R→S |
| 7 | **High** | Daily Dispatch shows cancelled jobs (tests P) | Test Q |
| 8 | **High** | MESSAGE CENTRE driver recipient name/phone read M/N | Read N/O |
| 9 | **High** | Customer message time drops the minutes (8:30 → "8AM") | `h:mm AM/PM` |
| 10 | Medium | Price shows "180." | Format such as `0.00` |
| 11 | Medium | Drivers and vehicles are stored twice (hidden sheets plus the combined master) | Keep the combined master, retire the hidden two |
| 12 | Medium | No capacity, active-status or clash checks; capacity is unused | Add validation |
| 13 | Medium | Dispatch formula is Google Sheets only | Rebuild for the chosen platform |
| 14 | Medium | Booking ID, Created At and Updated At are typed by hand, so duplicates are possible | Generate automatically |
| 15 | Medium | Audit Log has no automation | Script or application |
| 16 | Low | Customer message can be sent with blank driver/vehicle lines | Block "Ready" until a driver is assigned |
| 17 | Low | Car Type is a formula returning vehicle model | Becomes a booking input (D1): customer choice or suggestion with seats ≥ pax |
| 18 | Low | READ ME labels mangled by find-and-replace; two different copy instructions | Rewrite |
| — | Risk | Customer phone numbers sit in an unprotected, shareable file | Access control or a protected platform |
| — | Risk | Clash detection needs a trip length; no duration or end time field exists | Fixed default hours per trip (D3) |

---

## F. Recommended system design

| Option | Strengths | Limits |
|---|---|---|
| Fix the Excel / Google Sheet | Fastest; staff already know it | Weak validation, no real audit, formulas break when columns move (as happened here), customer data easy to copy |
| Google Sheets + Apps Script | Online, multi-user, buttons, audit, auto-ID | Script quotas, fragile sheet layout, hard to grow (customers, reports, driver app) |
| **Web app + database (Laravel + MySQL)** | Real validation and clash checks, user logins, full audit, mobile screens for staff and drivers, WhatsApp click-to-chat links, reports | Build effort; needs online hosting (a local XAMPP machine is not reachable by drivers or remote staff) |

**Recommendation: two steps.**
1. **Now (short):** correct the existing workbook (section E items 1–11) so daily work is accurate while the system is built.
2. **Target:** a web-based Booking Control Centre on Laravel + MySQL, the stack already used here. Screens: New Booking · Manage Booking · Driver Assignment · Drivers & Vehicles · Daily Dispatch · Customer WhatsApp · Driver WhatsApp · Dashboard · Reports · Audit History. It can be a standalone app for VIP Holidays or a new company/module on Nexora (one database per company already exists there). That choice is yours.

WhatsApp stays **human-sent**: the system builds the text and a click-to-chat link, and the staff member confirms sending.

---

## G. Development roadmap

| Phase | Work | Done when |
|---|---|---|
| 0 | Approve this analysis; answer the open questions | Rules signed off |
| 1 | Fix the workbook (E1–E11) | Sample bookings produce correct messages and dashboard |
| 2 | Database and master data: drivers, vehicles, customers; import from the workbook | One master, no duplicates |
| 3 | Booking entry: auto-ID, validation, statuses, audit | Invalid bookings rejected with clear messages |
| 4 | Driver assignment: fixed-vehicle lookup, active, capacity and clash checks | Wrong assignments blocked |
| 5 | Messages: both templates, click-to-chat, Ready → Opened → Sent Confirmed, Needs Regeneration on edit | Messages match the agreed format |
| 6 | Daily Dispatch board with alerts | Ops runs a day from it |
| 7 | Dashboard and reports (daily / weekly / monthly, by driver, by status) | Figures match the bookings |
| 8 | Testing with real-world scenarios, then staff training | UAT signed off |
| 9 | Go-live; workbook kept read-only as history | Live |

---

## H. Staff operating procedure (daily)

**Start of day**
1. Open Daily Dispatch for today and tomorrow.
2. Clear the alerts: unassigned driver, message not Sent Confirmed, pending confirmation, schedule clash.

**New booking**
1. New Booking → enter the customer (search by phone first), journey, pax, notes and price in SGD and/or RM. Never convert between them.
2. Save. The status is *Pending Confirmation*.
3. When the customer agrees → *Confirmed*.

**Assign and notify**
1. Pick the driver. Check that the vehicle details shown are right and that no warning appears.
2. Open the Customer message, read it fully, send it in WhatsApp, then mark **Sent Confirmed**.
3. Do the same for the Driver job order.
4. Only mark Sent Confirmed after you have seen the message delivered to the correct number.

**Changes**
- Edit the booking. If a message shows *Needs Regeneration*, resend it and confirm again.
- To cancel, set *Cancelled* with a reason and tell both the customer and the driver.

**End of day**
- Mark finished trips *Completed*.
- Check tomorrow's board: every job is confirmed, assigned and both messages are sent.

---

## Decisions agreed (15 Nov 2026)

| # | Topic | Decision |
|---|---|---|
| D1 | **Car Type** | Car Type is a booking field. The customer can pick it, or the system suggests it from pax. The rule is **seats ≥ pax**: 6 pax allows only 6-seat cars and up, never a 4-seater. Seat capacity comes from the vehicle master. |
| D2 | **Booking ID** | `YYMM` + running number, which **restarts every month** (e.g. Nov 2026 → `2611` + `001`, `002` …). Replaces the sample `VIP-YYMMDD-NNN`. |
| D3 | **Schedule clash** | Each trip blocks the driver for a **fixed number of hours** by default. A driver is in clash when a new pickup falls inside another job's block. |

What these decisions change in the design:
- **D1:** Car Type becomes a list kept in the master (each type with its seat count), and every vehicle gets a Car Type. The booking stores the requested Car Type. The check "pax > seats" now applies twice: when Car Type is chosen, and when the driver (and so the vehicle) is assigned.
- **D2:** The ID is generated by the system, never typed. Workbook error E14 is resolved by this rule.
- **D3:** The clash check works from Pickup Time + the default hours. No end-time field is needed for now.

**Update 15 Nov 2026:** the questions below are answered. Car types are Vios 4 / Alphard 7 / Vellfire 7 / Staria 7 / Van 10, and a bigger car may be assigned. The ID is `VIP-YYMM-NNNNNN` by booking month, 6 digits. The block is fixed hours, changeable per booking. The project plan and prototype now live in `docs/VIP_BOOKING_MODULE.md` (module **VIP Booking** in 08).

### Still to confirm (answered — kept for the record)

- **D1:** What is the list of car types and seats (e.g. Sedan 4, MPV 7, Staria 7, Van 10)? If a customer books a 7-seater, may staff assign a bigger car (a 10-seat van)?
- **D2:** Is YYMM taken from the **booking (created) month** or the **service month**? Does the ID keep a prefix (`VIP-2611-001`)? How many digits for the running number (3 = up to 999 a month)?
- **D3:** How many hours is the default block? May staff change it on a single booking (e.g. a full-day hire)?

## Open questions (need your answer before Phase 1)

1. ~~Car Type~~ — answered, see D1.
2. ~~Booking ID~~ — answered, see D2.
3. ~~Schedule clash~~ — answered, see D3.
4. **Price** — is at least one of SGD / RM required to confirm a booking? May both be filled with different amounts?
5. **Users** — how many staff, and should drivers log in (to see their jobs), or only receive WhatsApp?
6. **Platform** — after the Excel fix, a standalone web app, or a VIP Holidays company/module on Nexora? Where will it be hosted?
7. **Out of scope?** — payment collected, deposits, driver commission and costs. These are not in the workbook, so no profit figures will be shown unless you provide them.