A calendar calculator template that reflects remaing weeks in a ye

[HR Lost]

Hello!

I am trying to create a calendar calc tool that, when I work on a sheet, it:

1) Automatically loads today's date
2) Automatically loads number of pay periods remaining in the plan year
(using a pop down to allow selection of weekly or bi-weekly).
3) Accepts a deduction amount per pay period.
4) Calculates a total annual payroll contribution during the plan year
(based on number of pay periods remaining in the plan year, multiplied by the
deduction).

For Example:
1) Our Plan year: 08./01/05 - 7/31/06
2) Automatically load of today's date: 9/15/05
3) Drop down prompting choice of weekly or bi-weekly pay period: Weekly
3) Remaining pay periods between 9/15/05 and 7/31/05: 46
4) Weekly deduction: $28.00
5) Estimated Maximum Contribution: $1288.00 ($20 * 46 pay periods).

----------------------------

Along the same lines...let's say the goal of the employee to make an annual
maximum of $5000 a year. He wants to know how much he must contribute a pay
period to reach his goal of $5000 within the plan year. Can I create a small
spreadsheet tool that will automatically tell the employee how much he/she
needs to have deducted from his/her paycheck by 7/31/05 in order to reach the
goal of $5000, beginning with payroll of 9/16/05?

This seems like it should be easy, but I cannot get it to work? If you have
an Excel spreadsheet you can send as a template, or if you can describe to
process of creating this, I would be so appreciative!

 

[flydecoder]

For Example:
1) Our Plan year: 08./01/05 - 7/31/06
In Cell A1 enter the date 1/8/05 or 8/1/05, depending on your format
In Cell A2 enter =A1+364
Now you have a range

2) Automatically load of today's date: 9/15/05
In Cell A3 enter =TODAY()

3) Drop down prompting choice of weekly or bi-weekly pay period:
Weekly
I have a different suggestion for you.
In cell A6 enter "Payroll Frequency" in cell B6 enter "Name"
In cell B7 down to your last associate enter a B ow W for weekly or
Biweekly

3) Remaining pay periods between 9/15/05 and 7/31/05: 46
In cell A4 enter =ROUND((+A2-A3)/7,0) = This will show you how many
weekly paid associate paychecks are left.
In cell A5 enter =ROUND((+A2-A3)/14,0) to count the biweekly associate
pay periods remaining

4) Weekly deduction: $28.00
Enter in cell B1

5) Estimated Maximum Contribution: $1288.00 ($20 * 46 pay periods).
in each row in column C to the right of the asociates name enter
=if(A7="W",$B$1*$a$4,if(A7="B",$B$1*$a$5,"")) and copy formula down

----------------------------

Along the same lines...let's say the goal of the employee to make an
annual
maximum of $5000 a year. He wants to know how much he must contribute
a pay
period to reach his goal of $5000 within the plan year. Can I create a
small
spreadsheet tool that will automatically tell the employee how much
he/she
needs to have deducted from his/her paycheck by 7/31/05 in order to
reach the
goal of $5000, beginning with payroll of 9/16/05?
Lets assume we want this in column D & E next to each associate.
In Cell E7 enter
=if(AND(D7<>"",A7<>""),D7/(if(a7="W",$a$4,if(A7="B",$a$5,""))))
In Cell D7 enter the dollar value target anfd the rest should take care
of itself