Trending Hours

[Ody]

Hello all,

I have a spreadsheet with the following columns:

End Date
Estimated Hours
Actual Hours

I'd like to calculate, based on the End Date, if the trend for Actual Hours used will exceed the Estimated Hours, ideally accounting for business days. I'm a little flummoxed as to how to approach this. I'm thinking some calculation between today's date and the end date, then factor in a percentage of the estimated vs actual hours? I don't know?

Appreciate any advice. Thanks!

 

[Ody]

Hey JS411,

Thanks again for the input on estimating the project hours. I have one follow up if you don't mind. I was wondering if you can elaborate on the column headers for me a little more?

I'd like a little more clarity on the following:
Calculated Est Remaining Hours
Calculated Work Days JTD
Calculated Est Hours/Work Day
Calculated Remaining Work Days

Thanks!

 

[Jerry Sullivan]

Hi Ody,

Sure, I'll try to explain. Referring back to the earlier image:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 86px"><COL style="WIDTH: 86px"><COL style="WIDTH: 123px"><COL style="WIDTH: 128px"><COL style="WIDTH: 113px"><COL style="WIDTH: 97px"><COL style="WIDTH: 109px"><COL style="WIDTH: 143px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">Actual</TD><TD style="TEXT-ALIGN: center">Estimated</TD><TD style="TEXT-ALIGN: center">Actual Hours as of:</TD><TD style="TEXT-ALIGN: center">Manager Estimated</TD><TD style="TEXT-ALIGN: center">Calculated Est.</TD><TD style="TEXT-ALIGN: center">Calculated</TD><TD style="TEXT-ALIGN: center">Calculated Est.</TD><TD style="TEXT-ALIGN: center">Calculated</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">Start Date</TD><TD style="TEXT-ALIGN: center">Finish Date</TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: center">7/1/2011</TD><TD style="TEXT-ALIGN: center">Remaining Hours</TD><TD style="TEXT-ALIGN: center">Remaining Hours</TD><TD style="TEXT-ALIGN: center">Work Days JTD</TD><TD style="TEXT-ALIGN: center">Hours/Work Day</TD><TD style="TEXT-ALIGN: center">Remaining Work Days</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: center">1/17/2011</TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: center">10/14/2011</TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: center">12,000</TD><TD style="BACKGROUND-COLOR: #ffff99; TEXT-ALIGN: center">4,000</TD><TD style="TEXT-ALIGN: center">7,500</TD><TD style="TEXT-ALIGN: center">120</TD><TD style="TEXT-ALIGN: center">100</TD><TD style="TEXT-ALIGN: center">75</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E3</TD><TD>=H3*G3</TD></TR><TR><TD>F3</TD><TD>=NETWORKDAYS(A3,$C$2)</TD></TR><TR><TD>G3</TD><TD>=C3/F3</TD></TR><TR><TD>H3</TD><TD>=NETWORKDAYS($C$2,$B3)-1</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Calculated Work Days JTD (Job-to-Date):
This is simply how many work days there have been so far from the start date of the project until now.

Calculated Est Hours/Work Day:
This the average number of hours per day that have been worked so far. Calculated by dividing Hours to-date by Work Days to date. This could also be called our "Burn rate".

Calculated Remaining Work Days:
This is an estimate of how many work days there are from now until the expected end date of the project.

Calculated Est Remaining Hours:
This is the number we are really interested in finding. We multiply the Calculated Est Hours/Work Day times the Remaining Work Days and this gives us an estimate of how many hours we will work before the project ends.


As noted earlier, this method assumes that the rate of hours per day we will have from now until the end of the project will be close to the amount of hours per day we have had so far.

Let me know if any of this is unclear and I'll try to explain better.