Excel budget, changes to wages

Didn't find your answer?

I'm currently designing our company budget, and just wondering if there is a clever way in Excel to do the following:

- Sheet One - Have one sheet which lists all of our employees, with start date, end date, current pay, and any payrises

- Sheet Two - Have another sheet that will analyse this by month - so if Joe Bloggs started on 1st Jan, had a payrise after a few months, then left after six months- I'd like the sheet to automatically update when I enter this information in sheet one.  I know it's going to be a horribly complicated 'if' formula, so wondering if anyone has done anything similar!?

Replies (6)

Please login or register to join the discussion.

avatar
By daniel_
27th Mar 2018 17:28

Depending on the number of employees and number of payrises, perhaps the simplest way would be to have a whole sheet per employee with Start Date (colA) and Pay (colB) columns

Name each sheet 'employee'
Use a LOOKUP where the lookup_vector is an INDIRECT reference with the sheet name as 'employee' e.g.

LOOKUP(analysisMonth, INDIRECT("'"employee&"'!"&"A:A"),INDIRECT("'"employee&"'!"&"B:B")

This works because the vector lookup function will match the closest (earlier) date in the list, i.e. the most recent date pay changed.

The indirect referencing allows you to use a cell with the name of the employee as part of the cell reference.

Thanks (0)
Replying to daniel_:
avatar
By daniel_
27th Mar 2018 18:21

Working example shown here:
https://ufile.io/mbn8y

Thanks (0)
avatar
By paulwakefield1
28th Mar 2018 09:03

As ever with Excel, there are multiple ways to achieve a result so an alternative approach to Daniel's:

Assume on Sheet 1:
Column A Employee names
Column B Start date as a month number 1 to 12
Column C End date. Blank if they do not leave else month number 1 to 12
Column D Starting pay
Column E Pay rise in £ per annum
Column F Pay rise date as Month 1 to 12
Assume the first employee is entered In row 2

On Sheet 2:
In row 1, starting in Col B, the numbers 1 to 12
In A2: =Sheet1!A2
In B2: =AND(Sheet1!$B2<=I$1,OR(Sheet1!$C2="",Sheet1!$C2>=I$1))*(Sheet1!$D2+IF(Sheet1!$F2<=I$1,Sheet1!$E2,0))/12
Copy this to all rows and columns.

Some judicious use of controls and data validation would be sensible
A SUMPRODUCT approach would also work.
The above does assume there is only one payrise in the budget period.

Note: the formulae can be simplified if an end date is always entered i.e. no blanks and, although it is better to have the workings on a separate sheet, if the analysis is on the same sheet, the sheet references can be removed.

Thanks (0)
FD4Cast
By James Power
29th Mar 2018 11:20

Perhaps use this free tool as a starting point and then see what is outstanding in terms of MI?

https://www.accountingweb.co.uk/community/blogs/saltimbamba/forecast-emp...

Happy to help you customise if you wish.

Thanks (0)
avatar
By Andrew Burnett
29th Mar 2018 12:30

I suggest placing the data in a Table in Sheet1. Because you may have multiple pay rises, you will need to allow for multiple rows per employee. Fields in the table would be
- Name
- Pay
- Start (or payrise) date
- End date (if applicable, on last record for the employee)
- Last date for this pay rate (calculated column, formula needed)

Sheet2 won't be complicated, you can then do it with a SUMIFS structured formula referring to the table as you have a first and last date for each pay rate on each row of the original table.

This 'Excel database' approach works for most problems involving analysing tabular data.

Thanks (0)
avatar
By nlsta
29th Mar 2018 14:04

Have a look at @sumifs - allows variable column and row headings and data can be in a fairly random form. Not dissimilar to pivot tables but you specify the row & column headings and this can come up with a fixed format

https://support.office.com/en-ie/article/sumifs-function-c9e748f5-7ea7-4...

Thanks (0)