Payroll Cost Allocator

1 Upload

Choose Excel file or drop it here

Must contain: Payroll, Other Direct Cost(employee), Other Direct Cost (Branch), Other Direct Costs (Contract)

Download the input template to see the required layout.

2 Results

Guide: the input sheets, the math, and the two reports

What this tool does. You give it the monthly payroll workbook. It gives you back two reports: Report_1, what each person earned line by line, and Report_2, where every dirham is booked.

You uploadThe four input sheets: payroll plus three cost sheets

→

The splitOff-day rows spread over each person's worked contracts

→

Report_1Earnings detail, line by line. Your audit trail

→

Report_2One row per person and contract, fully costed and classified

The problems it solves.

Problem	What the tool does	Why it matters to the business
Off days belong to no site, yet their cost is real.	Spreads every off-day row over the person's worked contracts by day share.	Each contract carries the true cost of its manpower. Site profit is real, not flattered.
Earnings arrive as one total, but the ledger books them by type.	Classifies pay into 4101 regular, 4103 extra and weekly off OT, 4108 Ramadan and holiday OT.	Correct account booking, and overtime becomes visible and controllable.
Per-person monthly costs, insurance, visa, EOSB, arrive as lump sums.	Splits each person's amounts across their contracts by day share.	Contract profitability includes the full cost of its people, not just wages.
Branch costs have no single owner.	Spreads each branch pool across every row in the branch by day share.	No contract silently subsidizes another.
Allocating 3,000+ rows by hand takes hours and invites mistakes.	Computes everything in seconds, by the same rules every month.	Finance time goes to review and decisions, not arithmetic.
Errors and stale source data can hide inside big numbers.	Re-checks every employee's totals to within 0.01 and lists every unmatched amount.	Output can be trusted, and data problems become visible action items.
Payroll data is confidential.	Processes everything inside your browser. Nothing is uploaded or stored anywhere.	No data custody risk. The offline version needs no internet at all.

Part 1. The four input sheets

The workbook you upload must contain these four sheets, with these exact names. Keep the column layout the same every month. The number of rows can change freely. Download the input template to start from the correct layout. It is pre-filled with the example guard from this guide, so you can upload it right away and watch these exact numbers appear. The tool locates the header rows automatically and ignores any extra sheets.

Sheet	One row means	The tool reads
Payroll	One person on one site for the month. A row with a blank Contract NR means days that belong to no site, normally the weekly off days.	Branch, Employee Id, Name, Designation, Location, Contract NR, the day columns, the earnings columns.
Other Direct Cost(employee)	One person and their monthly amounts: 4121 Leave Salary, 4131 EOSB, 4211 Medical, 4513 Uniform, 4531 Visa, 4532 Training, 4564 Transport, 4631 Travel, 4632 Accommodation, Others 1 to 3.	Employee Id plus the twelve amounts. A TOTAL line at the bottom is recognised and skipped.
Other Direct Cost (Branch)	One branch and its shared pools: Others-4, Others-5, Others-6.	Branch code plus the three amounts.
Other Direct Costs (Contract)	A benefit amount granted to one person on one specific contract.	Employee Id, the amount, Contract NR. Must match the payroll exactly.

Part 2. The weekly off split

Off days belong to no single site, but their cost is real. So the tool spreads each blank-contract row across that same person's worked contracts, in proportion to each contract's total days. All day types count: worked, Ramadan, leave. And every figure in the off-day row is spread, each day type and each pay component.

share(contract) = (contract total days) ÷ (sum of the person's contracted total days) new value(contract) = value(contract) + ( off-row value × share(contract) )

Meet our guard. He worked 26 days this month and rested 4.

Payroll line	Contract NR	Days	Earnings
Project A	PROJ-A	13	1,300
Project B	PROJ-B	13	1,300
Weekly off	blank	4	400

Each project holds 13 of his 26 contracted days, so each takes half the off row:

After the split	Days	Earnings
Project A	13 + 2 = 15	1,300 + 200 = 1,500
Project B	13 + 2 = 15	1,300 + 200 = 1,500

One site only? That site takes all 4 days. No off days? Nothing changes. Only off days and no worked site at all? The row stays as it is, with a blank contract, flagged for your review. Day values keep 6-decimal precision so nothing is lost to rounding.

Part 3. Report_1, the earnings detail

Question	Report_1 answer
What is it?	The earnings detail: one row for every contracted payroll line, after the off-day split. Nothing is merged.
What is it for?	The audit trail. Any number in Report_2 traces back to rows here, and from here back to the payroll.
What changed vs the payroll?	Only the off-day redistribution. Designation, location and contract stay exactly as entered.

Total Days = sum of all day type columns Total Earnings = sum of all pay component columns

Our guard's two payroll lines stay two lines. Project A now reads 15 days and 1,500 AED. Project B the same. His off-day line is gone, because its value now lives inside those two rows.

Part 4. Report_2, the cost allocation

Report_1 grouped to one row per employee and contract. Earnings land in three ledger buckets:

4103 = (Extra OT) + (Weekly Off OT) 4108 = (Ramadan OT) + (Ramadan OT-OT68) + (Public Holiday OT) 4101 = Total Earnings − (4103 + 4108) check: 4101 + 4103 + 4108 = Total Earnings

Bucket	What goes in	Guard, Project A
4103	Extra overtime + weekly off overtime pay	100
4108	Ramadan overtime (both kinds) + public holiday overtime pay	50
4101	Everything else: basic, allowances, leave pay, benefits. Computed as Total Earnings minus 4103 minus 4108.	1,350
	Always true: the three add back to Total Earnings	1,500

The columns of Report_2, grouped by what they tell you:

Group	Columns	Where the number comes from
Who and where	Branch, Employee Id, Name, Designation, Working location, Contract NR	Straight from the payroll
Effort	Total Days	Report_1 days, summed per person and contract
Earnings, classified	4101, 4103, 4108	Total Earnings, split by the bucket formulas above
Personal costs	4121, 4131, 4211, 4513, 4531, 4532, 4564, 4631, 4632, Others-1 to 3	Employee cost sheet, day share (Part 5)
Branch share	Others-4, 5, 6	Branch sheet, day share within the branch (Part 5)
Contract benefit	4101- Benefits	Contract sheet, exact copy, no splitting (Part 5)

Read one row left to right and it tells a complete story: this person, on this contract, gave these days, earned this money classified this way, and carried this share of the shared costs.

Part 5. How shared costs find their row

Cost	Rule	Guard's numbers
Per-employee lumps (4121... Others-3)	Split across that person's contracts by total days, measured after the weekly off split.	Medical 300 AED, 15 days each side: 150 + 150
Branch pools (Others-4/5/6)	Split across every employee-contract row in the branch by day share.	Pool 10,000 over 1,000 branch days. His 15-day row: 150
Contract benefits (4101- Benefits)	No allocation. Copied to the exact employee and contract it names.	190 granted on Project A lands on Project A. Project B shows 0.

employee cost(row) = monthly amount × ( row days ÷ person's total days ) branch cost(row) = branch pool × ( row days ÷ branch total days ) benefit(row) = exact copy on matching (employee, contract), else 0

Part 6. The safety net

After generating, the tool re-checks every employee: total days and total earnings must still equal the source payroll, to within 0.01. Money is moved, never created, never lost.

per employee: | output days − payroll days | ≤ 0.01 per employee: | output earnings − payroll earnings | ≤ 0.01

Guard's check: 15 + 15 = 30 days, same as his 26 + 4. Earnings 1,500 + 1,500 = 3,000, same as his 2,600 + 400. Both pass.

And if an input amount points to an employee and contract that never appear in this month's payroll, it cannot land anywhere. It is never dropped silently. The amber panel lists each one, for example: "1 unmatched record, 190 AED not allocated", so finance can investigate the source data.

Part 7. Every code, in plain words

Each amount in Report_2 sits under an account code. This is what each code means and how its value is decided. "Day share" means: split across rows in proportion to days, using the formulas above.

Code	Name	What it is	How its value is decided
4101	Regular earnings	Everything the person earned except the two overtime groups: basic pay, site basic, allowances, leave pay, service benefits.	Computed: Total Earnings − (4103 + 4108)
4103	Extra and weekly off overtime	Pay for extra overtime hours and for overtime worked on weekly off days.	Computed: (Earned Extra OT) + (Earned Weekly Off OT)
4108	Ramadan and public holiday overtime	Pay for overtime during Ramadan (both kinds) and on public holidays.	Computed: (Earned Ramadan OT) + (Ramadan OT-OT68) + (Earned Public Holiday OT)
4121	Leave Salary	Money set aside for the person's paid leave.	From the employee cost sheet, day share
4131	EOSB	End of Service Benefits, the gratuity that builds up for each person.	From the employee cost sheet, day share
4211	Medical Insurance	The person's medical insurance premium for the month.	From the employee cost sheet, day share
4513	Uniform	Cost of the person's uniform.	From the employee cost sheet, day share
4531	Visa	Visa and permit costs for the person.	From the employee cost sheet, day share
4532	Training	Cost of training the person.	From the employee cost sheet, day share
4564	Transport	The person's transport cost.	From the employee cost sheet, day share
4631	Travel	The person's travel cost.	From the employee cost sheet, day share
4632	Accommodation	The person's housing cost.	From the employee cost sheet, day share
Others-1, 2, 3	Other employee costs	Any other cost recorded against one person that does not fit the named codes.	From the employee cost sheet, day share
Others-4, 5, 6	Branch shared pools	Costs that belong to a whole branch, not one person, such as shared facilities.	From the branch sheet, day share across every row in the branch
4101- Benefits	Contract benefit	A benefit granted to one person on one named contract.	From the contract sheet, copied to that exact employee and contract, no splitting

That is the complete rule set. Thirteen rules come from the client workbook itself, and two were added for safety: the review flag for a person with only off-days, and the unmatched panel above. Nothing else happens to your numbers. Every rule on this page is exactly what the tool computes.

Offline / Production version: run without internet

This entire application is one self-contained file. For production use on a desktop with no internet connection:

Download the offline package (zip)
Unzip anywhere on the PC. It contains SecuritasAllocator.html and instructions
Double-click the HTML file. It opens in your browser and works fully offline

The offline copy is byte-identical to this page. In both cases your payroll file is processed only inside your browser's memory. Nothing is uploaded, nothing is stored on any server, and there is no database. Closing the tab erases everything.

Payroll & Cost Allocator

1 Upload

2 Results