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.

184 hoursestimated time saved a month
TargetTraceablerules shown with the output
TargetReviewedexceptions reach a person

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
AI systembuilt around their rules
  • 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.

Their formulaFirst invoice = included days ÷ days in month × monthly fee; extra hours follow the contract
  1. Read the contract start

    Start date and monthly fee come from the contract register.

    12 Sep; HK$2,400/month19/30 of the month

  2. Pro-rate the first invoice

    Show the fraction beside the monthly fee.

    19/30 × HK$2,400HK$1,520.00

  3. Schedule renewal review

    Prepare the renewal notice before expiry for a person to send.

    Expiry − 30 daysRenewal draft ready

  4. Separate overdue decisions

    The manager receives the list; service holds are never automatic.

    More than 45 days overdueManager review

First invoiceHK$1,520.00

What the system produces

sample.alveek.ai/it-contractsSample data

Source records

Contract register ExcelSupport ticket log
SourceRecord
Start date12 Sep
Monthly feeHK$2,400
First-month fraction19/30

Billing calendar, invoice drafts and manager's overdue list

Review sheet (Excel)Output for approval (PDF)
ItemResult / basis
First invoice basis19/30 × HK$2,400
Renewal notice30 days before expiry; drafted
Overdue review>45 days: list for manager
Extra on-site hoursTicket log checked against contract
First invoice totalHK$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.

  1. Build the billing calendar The contract register feeds the invoice calendar with start and renewal terms retained.
  2. Pro-rate first bills The first invoice shows the included days and the contract's monthly fee basis.
  3. Prepare renewals Renewal notice drafts are prepared 30 days before expiry for staff to check and send.
  4. 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

TopicBeforeAfter
Build the billing calendarRecurring charges are prepared by filtering the register manually.The contract register feeds the invoice calendar with start and renewal terms retained.
Pro-rate first billsThe clerk calculates first-month coverage separately.The first invoice shows the included days and the contract's monthly fee basis.
Prepare renewalsRenewal 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 workExtra 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 accountsStatements, 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

  1. Read the actual source files. We review the existing inputs with the staff who prepare them and identify where each field comes from.
  2. Record the rules. We turn the agreed rules into visible checks, using completed records to compare the output with the office's own calculation.
  3. 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.
  4. 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.
PythonClaudeExcelWhatsAppEmail

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.

← Back to 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 →