Reservation Server Failure on Advance Booking Day
A TGSRTC depot opens advance booking for three days of festival travel. At 09:10 the central reservation server becomes unreachable from all four counters. Each counter has a standalone desktop, a printer and a shared spreadsheet containing the seat layout, fare table and a backup list of bookings taken before 09:10. About 212 passengers are waiting, and more are arriving. Two booking clerks have never used spreadsheet filters or formulas; one clerk has a basic knowledge of Excel. The server is expected to be restored by 13:00, but the divisional office warns that bookings already taken offline must not be lost or duplicated when the server returns. The depot manager must decide whether to continue manual booking, how to prevent double booking, and how to reconcile the manual records later.
1. Set out an immediate manual booking procedure that keeps issuing tickets while preventing double booking and fare errors.
Read the worked answer
Continue manual booking only under a controlled procedure:
- Freeze the seat map: the clerk with Excel knowledge opens the backup spreadsheet, sorts by service and seat number, and marks every seat already booked before 09:10 as 'B'. This file becomes the single master copy; no other copy may be edited.
- Issue pre-numbered manual tickets: use the depot's manual ticket stock in serial order. Each ticket must show service, date, seat number, passenger name or count, fare, counter number and clerk initials.
- One seat, one entry: after each ticket, the clerk immediately enters the seat number and ticket serial in the master sheet and changes the seat status to 'B'. The next clerk checks the sheet before issuing a ticket. Do not rely on memory or on a paper list kept at another counter.
- Fare check: the fare table is printed and displayed at each counter. The supervisor spot-checks three tickets per counter every hour.
- Reconciliation at 13:00: before the server is used again, total the manual tickets by service and compare with the master sheet. Enter the manual serial numbers and seat numbers into the server, then run a duplicate check. Only after the reconciliation is signed should normal online booking resume.
2. Explain how the depot can use spreadsheet formulas and features to reconcile the manual tickets against the server records.
Read the worked answer
The reconciliation can be done in a simple workbook with separate sheets for 'Manual' and 'Server':
- Duplicate seat check: in the Manual sheet, use
=COUNTIF($C$2:$C$500,C2)where column C is the seat number; any result above 1 is a duplicate. Apply the same formula to the Server sheet. - Match manual to server: in a reconciliation column use
=IFERROR(VLOOKUP(A2,Server!$A$2:$D$500,4,FALSE),"MISSING")where A2 is the ticket serial. 'MISSING' means a manual ticket was not entered into the server. - Revenue check:
=SUMIF(Manual!$B$2:$B$500,"1A",Manual!$E$2:$E$500)totals fares for service 1A; compare with the server's sum for the same service. - Count check:
=COUNTA(Manual!$A$2:$A$500)gives the number of manual tickets; compare with the number of entries added to the server.
Use Sort and Filter to isolate mismatches, and Conditional Formatting to highlight duplicate seat numbers in red. The supervisor should save the reconciled workbook with a date-stamped file name and print the mismatch list for signature.
3. What backup, communication and restoration steps should the manager take before the server returns?
Read the worked answer
The manager should treat the offline period as a data-integrity exercise, not just a ticketing problem:
- Protect the master file: save the master spreadsheet on the local desktop and on a USB drive kept with the supervisor; do not overwrite the pre-09:10 backup. Record the time of each save.
- Preserve paper: keep all manual ticket counterfoils in serial order in a sealed envelope at the end of each shift. The counterfoil is the legal record if the spreadsheet is corrupted.
- Inform the divisional office: report the outage time, number of manual tickets issued, services affected and the expected reconciliation time. Ask whether the division wants manual booking stopped at a specified volume.
- Prepare the server entry: before 13:00, create a clean import list with ticket serial, service, date, seat number and fare. Enter it only after the server is confirmed stable, and run the duplicate check immediately.
- Post-restoration audit: compare manual and server totals, investigate every mismatch, and record the outcome in the depot register. If a duplicate is found, cancel the later ticket, inform the passenger and refund the fare according to procedure. The manager should also arrange basic spreadsheet training for the two clerks so the same bottleneck does not recur.