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!

 

[Jerry Sullivan]

Hi Ody,

I do a lot of work with resource planning and project management and what you're asking could be a relatively easy problem or very challenging one depending on the details of your situation.

At the simplest level, you could calculate the "burn rate" of the Actual Hours/ Business day that you have been spending Job-To-Date and then extrapolate that rate to the project's end date to estimate the total anticipated hours.

However, most projects don't incur costs (hours) at a flat rate through the entire project, and depending on your business your costs might be back-loaded, front-loaded or some other pattern.

Hope this helps a little.

 

[Ody]

Thanks for the input JS411.

In my case, I'm simply looking for what you describe as the burn rate. The costs associated with the hours I'm trying to trend are estimated prior to a project start based on requirements, etc..., and we use fixed hourly rates so it makes it easy. I'm just trying to get a handle on whether we're "burning" through our hours too fast so that I can plan accordingly.

What formula(s) would you use to determine the burn rate in this case?

Thanks!

 

[Jerry Sullivan]

Perhaps you could use something like this. Have the Project Manager estimate Finish Date and hours remaining, then use the calculation as a check.

If they are different, the PM can consider if the burn rate is going to increase or decrease from the Job to Date actual rate and adjust the estimate if needed.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <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><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><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></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><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></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4


The NETWORKDAYS function allows you the option of factoring in holidays, but for your purposes that probably isn't necessary.

 

[Ody]

Thanks for the feedback and help!

 

[Ody]

Quick questions...

Using your data, I'm getting -120 for cell F3, why is that?

Also, why are you subtracting 1 in the formula in cell H3?

Thanks...

 

[moonfish]

Using your data, I'm getting -120 for cell F3, why is that?

I got curious and tested his formulae, I get 120 in F3. Are you using mm/dd/yyyy as standard date notation for your operating system?

 

[Ody]

I got curious and tested his formulae, I get 120 in F3. Are you using mm/dd/yyyy as standard date notation for your operating system?

Duh! I had the dates reversed. Time for more coffee...

Still not sure why the formula in H3 is subtracting 1 though? Unless the -1 represents "tomorrow". I think that's it.

 

[Jerry Sullivan]

Still not sure why the formula in H3 is subtracting 1 though? Unless the -1 represents "tomorrow". I think that's it.

Yes, sort of...
The NETWORKDAYS function returns the number of days inclusive of the start day and the finish day.

As an example if the start is 8/1/11 and the finish is 8/3/11 then NETWORKDAYS counts 3 days.
Since we are using three dates (Start, Interim, Finish) to calculate "Work days job to date" and "work days remaining", we need to avoid double-counting this middle day.

<b>Sheet5</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:122px;" /><col style="width:143px;" /><col style="width:110px;" /><col style="width:136px;" /><col style="width:153px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >F</td></tr><tr style="height:32px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Verdana; font-size:10pt; text-align:center; ">Start Date</td><td style="font-family:Verdana; font-size:10pt; text-align:center; ">Actual Hours as of</td><td style="font-family:Verdana; font-size:10pt; text-align:center; ">Finish Date</td><td style="font-family:Verdana; font-size:10pt; text-align:center; ">Work Days JTD</td><td style="font-family:Verdana; font-size:10pt; text-align:center; ">Remaining Work Days</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff99; font-family:Verdana; font-size:10pt; text-align:center; ">Mon, 08/01/2011</td><td style="background-color:#ffff99; font-family:Verdana; font-size:10pt; text-align:center; ">Wed, 08/03/2011</td><td style="background-color:#ffff99; font-family:Verdana; font-size:10pt; text-align:center; ">Fri, 08/05/2011</td><td style="font-family:Verdana; font-size:10pt; text-align:center; ">3</td><td style="font-family:Verdana; font-size:10pt; text-align:center; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:center; ">(Start of Monday)</td><td style="text-align:center; ">(End of Wednesday)</td><td style="text-align:center; ">(End of Friday)</td><td style="text-align:center; ">(Mon-Wed)</td><td style="text-align:center; ">(Thurs-Fri)</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=NETWORKDAYS(A2,B2)</td></tr><tr><td >F2</td><td >=NETWORKDAYS(B2,C2)-1</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

Have you been able to use your new trending tool on your actual projects yet? If so, are you finding it useful?

 

[Ody]

Tentatively, I want to say yes, it will be useful.

I need to request an update to the fields in the report I'm using then build some macros to incorporate the calculations you provided. But at first glance it looks like it's providing me the information I need.

Many thanks.