Payroll & Cost Allocator

Upload a monthly payroll workbook, receive the two computed reports.

Step 1: Upload

Choose Excel file or drop it here

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

Step 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 shows what each person earned, line by line. Report_2 shows where every dirham is booked: which account code, which employee, which contract. Read this guide once and you will know how to prepare the input and how to read the output.

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.

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 lump sums: 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

One output row for every contracted payroll line, after the split. Designation, location and contract stay exactly as entered. Total Days is the sum of all day types. Total Earnings is the sum of all pay components. Nothing is merged. This is your audit trail.

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

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) = lump × row days ÷ person's total days branch cost(row) = 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 and | 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.

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.