A restaurant chain · Hong Kong

Clock-in CSV to one payslip, with labour cost split by branch

Clock-in app CSV and roster Excel: staff moving between branches receive one payslip, while each branch keeps its own labour cost.

157.5 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.

A restaurant chain has 5 branches and 90 staff. The clock-in app exports CSV, while planned shifts remain in Excel. The office needs to combine a person's work across branches without losing where the hours belong.

At a glance

The files they already use

  • Clock-in app export CSV
  • Roster Excel
AI systembuilt around their rules
  • Match the same employee across branches
  • Round hours to 15-minute units
  • One payslip; branch cost stays separate
  • Employee MPF exempt below HK$7,100

Done automatically

  • Payslip and branch labour-cost sheet
  • Exceptions for review

The payroll clerk confirms the hours and approves the payslip before payment.

How they used to do it

The clerk opens the clock-in export beside the roster, searches for staff who appear at different branches and copies their hours onto a pay sheet. A name appearing on several branch lists does not mean several separate employees.

The combined wage then has to be separated again for branch reporting. If the office checks only the payslip total, a branch can carry another branch's hours; checking only branch totals can leave the employee with fragmented pay records.

The difficulties

  • Combine branch records The clerk searches each branch export for the same employee and adds the hours manually.
  • Check the roster Differences between planned shifts and clock-in records are buried in separate files.
  • Calculate wages Hours and the hourly rate are copied into a separate wage formula.
  • Split branch cost The office rebuilds branch totals after completing the employee's pay.
  • Review contributions Contribution checks happen away from the source hours.

Their formula, broken down

Part-timer Chan works at Mong Kok, Kwun Tong and Tsuen Wan at HK$68 an hour. The payslip combines the hours; the branch sheet keeps the wage cost separate.

Their formulaWage = combined branch hours × hourly rate; contributions use relevant income
  1. Combine the hours

    Keep the branch reference on every approved hour entry.

    Mong Kok 42.5 h + Kwun Tong 30 h + Tsuen Wan 18.25 h90.75 h

  2. Calculate the wage

    Use Chan's own hourly rate.

    90.75 h × HK$68HK$6,171.00

  3. Retain the branch split

    Each branch carries only the wage attached to its hours.

    Mong Kok HK$2,890.00 + Kwun Tong HK$2,040.00 + Tsuen Wan HK$1,241.00HK$6,171.00

  4. Apply MPF

    Relevant income is below HK$7,100, so employee MPF is HK$0. Employer MPF is 5%.

    HK$6,171.00 × 5%HK$308.55

Wage payable to ChanHK$6,171.00

What the system produces

sample.alveek.ai/payroll-restaurantSample data

Source records

Clock-in app export CSVRoster Excel
SourceRecord
Mong Kok42.5 h
Kwun Tong30 h
Tsuen Wan18.25 h

Payslip and branch labour-cost sheet

Review sheet (Excel)Output for approval (PDF)
ItemResult / basis
Combined wage90.75 × HK$68 = HK$6,171.00
Branch wage costsHK$2,890.00 / HK$2,040.00 / HK$1,241.00
Employee / employer MPFHK$0 / HK$308.55
Wage payableHK$6,171.00

How it was calculated

Wage = combined branch hours × hourly rate; contributions use relevant income

HK$6,171.00

Flagged for a person to confirm

  • A clock-in record without a matched employee or branch waits for confirmation; the system does not guess the branch allocation.

In this example: The output retains the source records and the rule behind each item. The payroll clerk confirms the hours and approves the payslip before payment.

How we helped

We connect the existing source files to a review sheet, with the company's rules recorded beside the results. The payroll clerk confirms the hours and approves the payslip before payment.

  1. Combine branch records Clock-in CSV is matched to the roster and employee record, so cross-branch hours feed the same payslip.
  2. Check the roster Unmatched records are listed beside the roster for the branch manager to confirm.
  3. Calculate wages Confirmed hours are handled in 15-minute units and multiplied by the employee's recorded hourly rate.
  4. Split branch cost The same hours produce a combined wage and a separate wage cost for each branch.

What the system handles

Combine branch records

Clock-in CSV is matched to the roster and employee record, so cross-branch hours feed the same payslip.

Check the roster

Unmatched records are listed beside the roster for the branch manager to confirm.

Calculate wages

Confirmed hours are handled in 15-minute units and multiplied by the employee's recorded hourly rate.

Split branch cost

The same hours produce a combined wage and a separate wage cost for each branch.

Review contributions

The review sheet shows relevant income, employee exemption and employer contribution beside the wage calculation.

Before and after

TopicBeforeAfter
Combine branch recordsThe clerk searches each branch export for the same employee and adds the hours manually.Clock-in CSV is matched to the roster and employee record, so cross-branch hours feed the same payslip.
Check the rosterDifferences between planned shifts and clock-in records are buried in separate files.Unmatched records are listed beside the roster for the branch manager to confirm.
Calculate wagesHours and the hourly rate are copied into a separate wage formula.Confirmed hours are handled in 15-minute units and multiplied by the employee's recorded hourly rate.
Split branch costThe office rebuilds branch totals after completing the employee's pay.The same hours produce a combined wage and a separate wage cost for each branch.
Review contributionsContribution checks happen away from the source hours.The review sheet shows relevant income, employee exemption and employer contribution beside the wage calculation.

How much time it saves

  • Existing payroll work: 90 staff × 2 h = 180 h a month.
  • Review with the system: 90 × 15 min = 22.5 h.
  • Estimated time saved: 180 − 22.5 = 157.5 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.
  • 157.5 h × HK$110 = HK$17,325, about HK$17,300 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

  • Do staff need a different clock-in app?
    The source remains the existing app's CSV and the roster Excel. Its fields are mapped before payroll is calculated.
  • Does a person working at several branches get several payslips?
    No. The employee receives a combined payslip; the branch sheet retains the wage split.
  • Who resolves a roster mismatch?
    The branch manager confirms the record, and the payroll clerk approves the resulting pay.

The results

  • Clock-in CSV is matched to the roster and employee record, so cross-branch hours feed the same payslip.
  • Unmatched records are listed beside the roster for the branch manager to confirm.
  • Confirmed hours are handled in 15-minute units and multiplied by the employee's recorded hourly rate.
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 →