**Process run through for Excel Simple Payroll Accrual Journal**

This process relates to creating journals for payroll expenses, currently just for fixed term contractors such as John, a Financial controller, and Jane who’s a Financial Analyst (though could include others in the future).

For these individuals, we have a fixed payment as per their contract which is what is automatically booked to our ledger through the ERP. These journals appear in the General Ledger tab.

But the amount we actually pay them each month depends on the hours they work – sometimes it’s the same amount, in which case we don’t need to do anything, but usually it’s a little more than this fixed payment amount, or a little less. And that difference we need to book as a prepayment or accrual in the Balance sheet – an asset or a liability as it’s an amount overpaid or amount owed – along with the corresponding expense or credit in the Income Statement.

On the General Ledger sheet, we have those fixed amounts that have been booked in our ERP each month for both John – a fixed £10k per month – and for Jane who has a fixed £5k per month.

In the bank statement sheet we have the amounts that have been actually paid to John and Jane over the 4 month period from July to October. We can see that the amounts are quite different each month, so we’ll need to journal these differences.

And this is where it gets a little messy. In the staff sheet, we have the list of staff – just these two individuals, and the cost centre and company code that they belong to. This list of staff members is the starting point for the calcs sheet – columns A to F are actually just a direct reference to the staff table.

Then in the columns to the right we look up the GL and bank statement amounts for each individual. It’s a little messy because the GL refers to them by role and the bank statement by name. But we use separate formulas to work the amount owed or overpaid to each staff member so it works.

Now in the journal sheet we need to start with the unique combinations of months, company codes and cost centres. 

We create the journal lines for Income statement and balance sheet separately, because each amount needs to be entered into both and they need to go to different account lines depending on whether the difference is positive or negative -  i.e. whether it isan under or over payment.

So there is a dynamic SUM formula in column I to bring in the total difference in salary payments for that month,cost centre and company code, and then IF formulas in columns E, F and G with the account number, account name and a value in either the debit or credit column, all depending on whether the difference amount is positive or negative.

Then underneath I just copy and paste values from both the income statement and balance sheet sections and check that the total debits equals the total credits – each journal needs to have these amounts being equal or it won’t post!