Salary Tracker

[Jblackbelt]

Hi,

I am trying to build a spreadsheet so that I can track my salary / headcount expenses throughout the year, and know what I have actually spent anytime during the year on any given day.

My columns are:

Position - i.e title of person
Employee ID - unique identifier
Budgeted? - i.e was it budgeted for, is this a replacement for an employee who left, or just out of the original budget scope.
Name - Employee Name
Hire Date - date the person was hired
Termination or Transfer Date - Date person left or transferred to another department in the company
Budgeted Salary at 1/1/Year - what was my budget at the beginning of the year
Adjustment - Amount of a mid year adjustment
Date Adjustment Effective - The date the mid year adjustment was effective
Actual Current Salary (original budget plus adjustment)
Year % completed (i.e the % of time we are through the current calendar year via the number of working days)
Year % remaining (i.e the % of time we have remaining in the current calendar year via the number of working days)
Year Budget Used to Date (i.e how much of my original budgeted amount have I spent)
Year Actual Cost to date (how much have we actually spent based on the year % completed)
Next Year projected run rate (actual current salary + projected merit increases)

What I am having trouble with is figuring out what formula to use in a few columns:
Year % completed (i.e the % of time we are through the current calendar year via the number of working days)
Year % remaining (i.e the % of time we have remaining in the current calendar year via the number of working days)
Year Budget Used to Date (i.e how much of my original budgeted amount have I spent)
Year Actual Cost to date (how much have we actually spent based on the year % completed)

I have been playing around with the yearfrac formula to calculate the % of the year completed, but there is a few things I can't figure out.
1) how to make it calculate only on the # of working days. for 2016 that is 262 as we still pay our employees on holidays. For 2017 it will be 260 days, and I am sure it will change every year there after.
2) also have not been able to get an if statement to work correctly to look at the hire date. if the hire date is on or before 1/1/16 then it looks at the entire year, but if it is after 1/1/16 (example 4/14/16) then I only need to calculate what their cost for 2016 was from that date forward (and ignore anything from 1/1/16 to 4/13/16).
3) if we make a midyear adjustment to someones salary, I need the formula to take that into consideration when calculating the cost for the year.

The plan is also to copy the spreadsheet at 1/1/17 and use the same formulas for 2017, 2018, and so on.

Anyone already have anything made that does this or can anyone assist me?

Thanks,

J

 

[burns14hs]

I'm going to preface my answer by saying that I'm relatively good at making formulas work for me, but I'm not the best at making them as simplistic or pretty as they could be so although the formulas may be functional, they can get long:

First, I created 2 Reference cells for the Beginning and End of the Year -

I put the Begin Date for the Year in B1 and input the following formula -

="1/1/" & YEAR(TODAY())

I put the End Date for the Year in B2 and input the following formula -

="12/31/" & YEAR(TODAY())

Basically those are just saying the the beginning of the year is 1/1 and the end of the year is 12/31 and the year is always the current year.

Then, to calculate the number of workdays in a year (assuming all M-F days are workdays) I input the following formula:

=SUM(INT((WEEKDAY($B$1-2)-$B$1+TODAY())/7),INT((WEEKDAY($B$1-3)-$B$1+TODAY())/7),INT((WEEKDAY($B$1-4)-$B$1+TODAY())/7),INT((WEEKDAY($B$1-5)-$B$1+TODAY())/7),INT((WEEKDAY($B$1-6)-$B$1+TODAY())/7))/SUM(INT((WEEKDAY($B$1-2)-$B$1+$B$2)/7),INT((WEEKDAY($B$1-3)-$B$1+$B$2)/7),INT((WEEKDAY($B$1-4)-$B$1+$B$2)/7),INT((WEEKDAY($B$1-5)-$B$1+$B$2)/7),INT((WEEKDAY($B$1-6)-$B$1+$B$2)/7))

This long formula is basically finding the number of Mondays, Tuesdays, etc. that we have experienced until now and adding them together. Then it divides by the number of Mondays, Tuesdays, etc. that are in the date range of 1/1-12/31 to find a percentage.

% Year Remaining: =1-(Cell Reference of the % year complete formula)

That's as far as I've gotten for you thus far. If you are planning on making a new spreadsheet each year, then instead of the formulas in the cells for Start/End year dates, you can just hardcode 1/1/2016 and 12/31/2016 instead.

Question for your Cost/Budget stuff... Is the cost calculated per employee? Is the budget calculated per employee?

 

[Jblackbelt]

Thanks! I will give that a shot. Yes both budget and cost is based on each employee

 

[Jblackbelt]

That formula worked beautifully for calculating the start date for a full year. I was able to modify it into an if statement so that if someone's start date was 4/14 rather than 1/1 it would calculate the % completed since they started. Unfortunately that makes the 1-Year complete formula inaccurate. I'm trying to toy with that now as well as toy with adding additional if statements in case their is an adjustment mid year.

 

[burns14hs]

% year complete is always (1-% Incomplete) and vice versa. If that is no longer accurate I would guess there is another problem with the % Complete formula. Do you have an example you could show me for an instance that isnt working?