Question about forecasting payroll

[Herlengol]

Hi all,

I am trying to forecast hourly teachers salary for the rest of the year. This particular teacher works 32 hours per week, 45 weeks out of the year and paid hourly wage of $20/hour. How do I write a formula so that it calculates monthly salary for the whole year. This teacher does not work during the summer. Thank you!

 

[shg]

=32*45*20

 

[Herlengol]

=32*45*20

Thank you for the reply but I need to forecast per month, some months have 23 working days, some of them have 21 days

 

[shg]

So a clear example of what you want is ...?

 

[Herlengol]

[TABLE="width: 1833"]
<colgroup><col span="9"><col><col><col span="4"><col span="2"><col span="9"><col><col></colgroup><tbody>[TR]
[TD]SHORT TITLE[/TD]
[TD]# Staff[/TD]
[TD]NAME[/TD]
[TD]FLSA Status[/TD]
[TD]Location[/TD]
[TD]Est. Hours/ Week[/TD]
[TD]Est. Weeks/ Yr[/TD]
[TD]Est. Hours/ Yr[/TD]
[TD] Annual-ized FTE [/TD]
[TD]Hire Date[/TD]
[TD] Term Date [/TD]
[TD] Rate as 3.20.2018 [/TD]
[TD] Proposed Rate [/TD]
[TD] Annualized as of 3.20.2018 [/TD]
[TD] Proposed annual rate [/TD]
[TD]11/1/2017[/TD]
[TD]12/1/2017[/TD]
[TD]1/1/2018[/TD]
[TD]2/1/2018[/TD]
[TD]3/1/2018[/TD]
[TD]4/1/2018[/TD]
[TD]5/1/2018[/TD]
[TD]6/1/2018[/TD]
[TD]7/1/2018[/TD]
[TD]8/1/2018[/TD]
[TD]9/1/2018[/TD]
[TD]10/1/2018[/TD]
[TD] TOTAL 2018 [/TD]
[/TR]
[TR]
[TD]xxxx[/TD]
[TD]1[/TD]
[TD]xxxx[/TD]
[TD]PT[/TD]
[TD]BHED[/TD]
[TD]40[/TD]
[TD]42[/TD]
[TD="align: right"]2080[/TD]
[TD] 1.00[/TD]
[TD]1/24/2014[/TD]
[TD]12/31/2019[/TD]
[TD] 15.55[/TD]
[TD] 15.55[/TD]
[TD] 32,344[/TD]
[TD] 26,124[/TD]
[TD] 2,721.26[/TD]
[TD] 2,596.86[/TD]
[TD] 2,884.53[/TD]
[TD] 2,488.01[/TD]
[TD] 2,713.48[/TD]
[TD] 2,616.31[/TD]
[TD] 2,861.20[/TD]
[TD] 2,612.40[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] 2,488.00[/TD]
[TD] 2,861.20[/TD]
[TD] 26,843.25[/TD]
[/TR]
</tbody>[/TABLE]

 

[Herlengol]

[TABLE="width: 1833"]
<colgroup><col span="9"><col><col><col span="4"><col span="2"><col span="9"><col><col></colgroup><tbody>[TR]
[TD]SHORT TITLE[/TD]
[TD]# Staff[/TD]
[TD]NAME[/TD]
[TD]FLSA Status[/TD]
[TD]Location[/TD]
[TD]Est. Hours/ Week[/TD]
[TD]Est. Weeks/ Yr[/TD]
[TD]Est. Hours/ Yr[/TD]
[TD] Annual-ized FTE [/TD]
[TD]Hire Date[/TD]
[TD] Term Date [/TD]
[TD] Rate as 3.20.2018 [/TD]
[TD] Proposed Rate [/TD]
[TD] Annualized as of 3.20.2018 [/TD]
[TD] Proposed annual rate [/TD]
[TD]11/1/2017[/TD]
[TD]12/1/2017[/TD]
[TD]1/1/2018[/TD]
[TD]2/1/2018[/TD]
[TD]3/1/2018[/TD]
[TD]4/1/2018[/TD]
[TD]5/1/2018[/TD]
[TD]6/1/2018[/TD]
[TD]7/1/2018[/TD]
[TD]8/1/2018[/TD]
[TD]9/1/2018[/TD]
[TD]10/1/2018[/TD]
[TD] TOTAL 2018 [/TD]
[/TR]
[TR]
[TD]xxxx[/TD]
[TD]1[/TD]
[TD]xxxx[/TD]
[TD]PT[/TD]
[TD]BHED[/TD]
[TD]40[/TD]
[TD]42[/TD]
[TD="align: right"]2080[/TD]
[TD] 1.00[/TD]
[TD]1/24/2014[/TD]
[TD]12/31/2019[/TD]
[TD] 15.55[/TD]
[TD] 15.55[/TD]
[TD] 32,344[/TD]
[TD] 26,124[/TD]
[TD] 2,721.26[/TD]
[TD] 2,596.86[/TD]
[TD] 2,884.53[/TD]
[TD] 2,488.01[/TD]
[TD] 2,713.48[/TD]
[TD] 2,616.31[/TD]
[TD] 2,861.20[/TD]
[TD] 2,612.40[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] 2,488.00[/TD]
[TD] 2,861.20[/TD]
[TD] 26,843.25[/TD]
[/TR]
</tbody>[/TABLE]

I am trying to find a formula that will automatically calculate or skip over July and August she works 42 weeks out of 52 weeks.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][td="bgcolor:#C0C0C0"]

G​

[/td][td="bgcolor:#C0C0C0"]

H​

[/td][td="bgcolor:#C0C0C0"]

I​

[/td][td="bgcolor:#C0C0C0"]

J​

[/td][td="bgcolor:#C0C0C0"]

K​

[/td][td="bgcolor:#C0C0C0"]

L​

[/td][td="bgcolor:#C0C0C0"]

M​

[/td][td="bgcolor:#C0C0C0"]

N​

[/td][td="bgcolor:#C0C0C0"]

O​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td]

11/1/17​

[/td][td]

12/1/17​

[/td][td]

1/1/18​

[/td][td]

2/1/18​

[/td][td]

3/1/18​

[/td][td]

4/1/18​

[/td][td]

5/1/18​

[/td][td]

6/1/18​

[/td][td]

7/1/18​

[/td][td]

8/1/18​

[/td][td]

9/1/18​

[/td][td]

10/1/18​

[/td][td="bgcolor:#F3F3F3"]

Total​

[/td][td="bgcolor:#909090"][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

15.55​

[/td][td="bgcolor:#CCFFCC"]

2,736.80​

[/td][td="bgcolor:#CCFFCC"]

2,612.40​

[/td][td="bgcolor:#CCFFCC"]

2,861.20​

[/td][td="bgcolor:#CCFFCC"]

2,488.00​

[/td][td="bgcolor:#CCFFCC"]

2,736.80​

[/td][td="bgcolor:#CCFFCC"]

2,612.40​

[/td][td="bgcolor:#CCFFCC"]

2,861.20​

[/td][td="bgcolor:#CCFFCC"]

2,612.40​

[/td][td][/td][td][/td][td="bgcolor:#CCFFCC"]

2,488.00​

[/td][td="bgcolor:#CCFFCC"]

2,861.20​

[/td][td="bgcolor:#E5E5E5"]

26,870.40​

[/td][td="bgcolor:#CCFFCC"]B2: =$A2 * 8 * NETWORKDAYS(B1, EOMONTH(B1, 0))[/td][/tr]
[/table]