An IT maintenance company · Hong Kong
Contract Excel and ticket logs to a recurring billing calendar
Contract-register Excel and ticket logs drive pro-rata first bills, renewal notices 30 days ahead and charges for extra on-site hours.
Representative example — a composite of typical Hong Kong workflows, written from the duties in current job ads. Company names, documents and figures are sample data.
An IT maintenance company manages 260 contracts. The contract register Excel records billing terms, while the ticket log records support work. The office needs a calendar that joins recurring charges to start dates, renewals and extra on-site hours.
At a glance
The files they already use
- Contract register Excel
- Support ticket log
- Pro-rate the first month by the contract's start date
- Draft renewal notices 30 days before expiry
- List debts over 45 days overdue for the manager
- Extra on-site hours use the contract's own terms
Done automatically
- Billing calendar, invoice drafts and manager's overdue list
- Exceptions for review
Accounts staff approve invoices; the manager alone decides any service hold, and a person sends the notices.
How they used to do it
The clerk filters the contract register for invoices and renewals, then checks the ticket log for on-site work outside the included service. A recurring monthly fee alone does not tell the clerk what belongs on the first invoice.
Overdue balances and extra hours live in different lists. The office prepares statements and renewal messages separately, while the manager needs a clear list of older debts before deciding whether any service should be held.
The difficulties
- Build the billing calendar Recurring charges are prepared by filtering the register manually.
- Pro-rate first bills The clerk calculates first-month coverage separately.
- Prepare renewals Renewal dates have to be searched in the register.
- Review extra on-site work Extra hours in tickets can be left out of the invoice list.
- Review payments and overdue accounts Statements, bank matching and overdue follow-up use separate lists.
Their formula, broken down
A client starts on 12 Sep at HK$2,400 a month. The sample first invoice covers 19 of the month's 30 days; renewal and overdue actions remain separate workflow checks.
Read the contract start
Start date and monthly fee come from the contract register.
12 Sep; HK$2,400/month19/30 of the month
Pro-rate the first invoice
Show the fraction beside the monthly fee.
19/30 × HK$2,400HK$1,520.00
Schedule renewal review
Prepare the renewal notice before expiry for a person to send.
Expiry − 30 daysRenewal draft ready
Separate overdue decisions
The manager receives the list; service holds are never automatic.
More than 45 days overdueManager review
What the system produces
Source records
| Source | Record |
|---|---|
| Start date | 12 Sep |
| Monthly fee | HK$2,400 |
| First-month fraction | 19/30 |
Billing calendar, invoice drafts and manager's overdue list
| Item | Result / basis |
|---|---|
| First invoice basis | 19/30 × HK$2,400 |
| Renewal notice | 30 days before expiry; drafted |
| Overdue review | >45 days: list for manager |
| Extra on-site hours | Ticket log checked against contract |
| First invoice total | HK$1,520.00 |
How it was calculated
First invoice = included days ÷ days in month × monthly fee; extra hours follow the contract
HK$1,520.00
Flagged for a person to confirm
- An extra-hours ticket without confirmed contract coverage is held for review. An overdue flag never suspends service by itself.
In this example: The output retains the source records and the rule behind each item. Accounts staff approve invoices; the manager alone decides any service hold, and a person sends the notices.
How we helped
We connect the existing source files to a review sheet, with the company's rules recorded beside the results. Accounts staff approve invoices; the manager alone decides any service hold, and a person sends the notices.
- Build the billing calendar The contract register feeds the invoice calendar with start and renewal terms retained.
- Pro-rate first bills The first invoice shows the included days and the contract's monthly fee basis.
- Prepare renewals Renewal notice drafts are prepared 30 days before expiry for staff to check and send.
- Review extra on-site work On-site ticket hours are checked against included service and the contract's extra-hour terms.
What the system handles
Build the billing calendar
The contract register feeds the invoice calendar with start and renewal terms retained.
Pro-rate first bills
The first invoice shows the included days and the contract's monthly fee basis.
Prepare renewals
Renewal notice drafts are prepared 30 days before expiry for staff to check and send.
Review extra on-site work
On-site ticket hours are checked against included service and the contract's extra-hour terms.
Review payments and overdue accounts
Reviewed payment records feed statements; debts over 45 days overdue are listed for the manager's decision.
Before and after
| Topic | Before | After |
|---|---|---|
| Build the billing calendar | Recurring charges are prepared by filtering the register manually. | The contract register feeds the invoice calendar with start and renewal terms retained. |
| Pro-rate first bills | The clerk calculates first-month coverage separately. | The first invoice shows the included days and the contract's monthly fee basis. |
| Prepare renewals | Renewal dates have to be searched in the register. | Renewal notice drafts are prepared 30 days before expiry for staff to check and send. |
| Review extra on-site work | Extra hours in tickets can be left out of the invoice list. | On-site ticket hours are checked against included service and the contract's extra-hour terms. |
| Review payments and overdue accounts | Statements, bank matching and overdue follow-up use separate lists. | Reviewed payment records feed statements; debts over 45 days overdue are listed for the manager's decision. |
How much time it saves
- Invoices: 260 × 12 min = 52 h; renewals: 22 × 1.5 h = 33 h; overdue: 70 × 20 min ≈ 23.3 h.
- Statements: 260 × 8 min ≈ 34.7 h; bank matching: 260 × 6 min = 26 h; on-site tickets: 140 × 15 min = 35 h; existing total = 204 h.
- Review takes 20 h; estimated time saved: 204 − 20 = 184 h a month.
- The estimate values office time at HK$110 an hour (about HK$19,000 a month for a clerk, MPF included); it is not an audited cash return.
- 184 h × HK$110 = HK$20,240, about HK$20,200 a month.
These are the working assumptions behind the figure, not an audited result.
How a project like this runs
- Read the actual source files. We review the existing inputs with the staff who prepare them and identify where each field comes from.
- Record the rules. We turn the agreed rules into visible checks, using completed records to compare the output with the office's own calculation.
- Run alongside the existing process. The office keeps its normal process while the system handles the same files. Staff compare the results and resolve differences before switching.
- Use and maintain it. Once the results agree, staff use the review sheet and handle its flags. Changes to source formats or company rules are checked before they are applied.
Questions owners usually ask
- Does overdue payment suspend service automatically?
No. Debts over 45 days overdue are listed for the manager. Any service hold is a person's decision. - How are extra on-site hours billed?
Tickets are checked against the contract's included hours and charging terms; unconfirmed items wait for review. - Who sends renewal notices?
The system prepares drafts 30 days before expiry. Staff check and send them.
The results
- The contract register feeds the invoice calendar with start and renewal terms retained.
- The first invoice shows the included days and the contract's monthly fee basis.
- Renewal notice drafts are prepared 30 days before expiry for staff to check and send.
Representative example — a composite of typical Hong Kong workflows, written from the duties in current job ads. Company names, documents and figures are sample data.
Related case studies
Also useful: Back-office outsourcing · Try a working demo · Free tools · All case studies
Does your company have a formula like this?
Send us one real example — a quotation, an invoice or a month-end statement — and we will show you how the system would work it out. The assessment is free.
Get a free assessment →