Payroll Reconciliation (Employer of Record vs Actual Pay) Using Excel/Power Query
Budget: $750 – $1,500 AUD
Project Overview
Varicon uses an Employer of Record (EOR) service in Nepal. We receive monthly invoices showing charges per employee, and we maintain internal templates containing each employee’s contract data (often with multiple contract periods).
I require a once-off reconciliation comparing:
• What the EOR charged us
• What employees should have received based on their contracts
• The amounts converted into AUD using Airwallex exchange rates
• Any differences or variances
This is a one-time project, not an ongoing monthly model.
Data Provided
You will receive four Excel files:
1. Exchange Rates (Airwallex – one per invoice/month)
2. Raw EOR Charges (includes Month, Invoice Number, Employee, Monthly Charge, Festival Bonus, Overtime, Other Bonuses)
3. Employee Contract Templates (up to four historical contracts per employee, including start date, monthly salary, festival bonus)
4. My initial summary attempt (“000 Summary.xlsx”)
Scope of Work
1. Clean and merge all source data (Power Query strongly preferred)
* Standardise employee names
* Format dates consistently
* Merge Raw Charges, Contracts, and Exchange Rates
* Map each invoice month to the correct contract period
2. Apply reconciliation logic
* For each employee and month, determine the applicable contract
* Pull salary and festival bonus amounts from the contract
* Convert contract amounts into AUD using the correct exchange rate
* Compare contract-based expected amounts to EOR charges
* Calculate variances
* Complete in a way that I can add any missing contracts later on
3. Handle periods with missing contract data
* Apply a provided markup assumption for any gaps
* Ensure mapping logic is clearly documented
4. Produce clear outputs
* Per-employee monthly reconciliation
* Festival bonus comparison
* Variance summary
* A simple exceptions list for months that require review
5. Deliver a single Excel workbook
* Does not need to be refreshable or automated
* Should be clear, structured, and easy to understand
* A brief note describing assumptions used (one paragraph is fine)
**Required Skills**
• Strong Power Query skills (essential)
• Advanced Excel (XLOOKUP, SUMIFS, date logic, pivot tables)
• Ability to merge and interpret multi-source datasets
• Attention to detail
• Accounting background is not required
**What Success Looks Like**
• For every employee and month, expected pay vs EOR charge is clearly shown in AUD
• Variances are easy to identify
• Contract periods are mapped accurately
• Missing contract periods are handled using the provided assumptions
• The final workbook is accurate, readable, and self-contained
All documents supplied after signing NDA
Varicon uses an Employer of Record (EOR) service in Nepal. We receive monthly invoices showing charges per employee, and we maintain internal templates containing each employee’s contract data (often with multiple contract periods).
I require a once-off reconciliation comparing:
• What the EOR charged us
• What employees should have received based on their contracts
• The amounts converted into AUD using Airwallex exchange rates
• Any differences or variances
This is a one-time project, not an ongoing monthly model.
Data Provided
You will receive four Excel files:
1. Exchange Rates (Airwallex – one per invoice/month)
2. Raw EOR Charges (includes Month, Invoice Number, Employee, Monthly Charge, Festival Bonus, Overtime, Other Bonuses)
3. Employee Contract Templates (up to four historical contracts per employee, including start date, monthly salary, festival bonus)
4. My initial summary attempt (“000 Summary.xlsx”)
Scope of Work
1. Clean and merge all source data (Power Query strongly preferred)
* Standardise employee names
* Format dates consistently
* Merge Raw Charges, Contracts, and Exchange Rates
* Map each invoice month to the correct contract period
2. Apply reconciliation logic
* For each employee and month, determine the applicable contract
* Pull salary and festival bonus amounts from the contract
* Convert contract amounts into AUD using the correct exchange rate
* Compare contract-based expected amounts to EOR charges
* Calculate variances
* Complete in a way that I can add any missing contracts later on
3. Handle periods with missing contract data
* Apply a provided markup assumption for any gaps
* Ensure mapping logic is clearly documented
4. Produce clear outputs
* Per-employee monthly reconciliation
* Festival bonus comparison
* Variance summary
* A simple exceptions list for months that require review
5. Deliver a single Excel workbook
* Does not need to be refreshable or automated
* Should be clear, structured, and easy to understand
* A brief note describing assumptions used (one paragraph is fine)
**Required Skills**
• Strong Power Query skills (essential)
• Advanced Excel (XLOOKUP, SUMIFS, date logic, pivot tables)
• Ability to merge and interpret multi-source datasets
• Attention to detail
• Accounting background is not required
**What Success Looks Like**
• For every employee and month, expected pay vs EOR charge is clearly shown in AUD
• Variances are easy to identify
• Contract periods are mapped accurately
• Missing contract periods are handled using the provided assumptions
• The final workbook is accurate, readable, and self-contained
All documents supplied after signing NDA