Human Capital Modelling from Xero Payroll

Description
Most financial models for payroll are overly simplistic, however, to truly model human capital you have to consider all the different types of roles, departments, and companies perhaps (for multiple entities) as well as the overall group headcount and costs relating to staff.


The ability to integrate such a model into actual results from the accounting system is just as important.


This model shows how you can extract payroll data from Xero for each role as well as the associated costs (expenses), liabilities and cash impacts of all the relevant roles in the organisation or across multiple organisations.


During a potential restructure of the business, this model is incredibly valuable and also useful when influencing the structure of the business post the re-organisation.


As an accounting and finance professional being able to accurately calculate the granular details along with the high-level summary outputs by department or by particular type of roles is critical to any business change process.


This is also a great opportunity for the finance team to partner with the people and culture (HR) team. 


The challenges faced by a restructuring are incredibly nuanced almost at an individual personal level as well as an overall department and business strategic decision-making.


The story of what will happen after the change needs to align with the story of changes in the roles being suggested.


What to expect?
Some of the key functionalities contained in this model include:
1. Managing start and termination dates by employee
2. Managing base salaries, short-term incentives (STI%) and salary increases that are based on a particular month (paid in the same month as the salary increase)
3. Varying Superannuation types (Fixed Amount, Statutory or Percentage of Earnings).
4. Job title, employee details (Name, DOB, Status)
5. Discretionary bonuses, commission or redundancy payment amounts
6. Quarterly Tax withholding, liabilities accrual and cash delays
7. Dashboard and outputs on a department and individual role basis.


Modelling human capital is difficult enough, let alone having to build a complex workbook to cope with this.


At least some part of the exercise is streamlined and simplified by this workbook.


Enjoy and refer to the instructions on the front page.


Note: You do not need to use EXL Cloud to use the workbook as it's not locked down at all. It will of course speed up the data ingestion process from Xero. You can use it for other payroll systems provided you fill out the relevant headings.


This model also leverages Power Query, Dynamic Arrays and LAMBDAs


Good luck!