Budget Projections

[rriddleusmc]

This is my first time to post a thread. We have hourly amounts remaining on projects, we want to project cost at 40 hour work weeks and 50 hour work weeks. We simply multiply the 40 hour budgets times the straight time rate, but we need to calculate the over time on the 50 hour budgets.
examples:
600 hours on 40 hour budget at $25 and hour is $15,000. However on a 50 hour budget it would include 480 straight time hours and 120 overtime hours for a budget of $16,500. Our data is hourly bugets in column "A". In column "B" we did =A3*straighttimerate. Is there a formula that can calculate 40hrs straight time then 10hrs over time until the budget runs out? We are working with Microsoft Office Proffesional 2007 with Excel 2007 but need to remain compatible with Excel 2003.
<TABLE style="WIDTH: 254pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=338 border=0><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: yellow" width=103 height=20>Budget (hours)</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 92pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" width=122>straight time ($25)</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 85pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" width=113>over time ($37.5)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>240</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">$6,000.00</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">?</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>4652</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">$116,300.00</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">?</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>152</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">$3,800.00</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">?</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>258</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">$6,450.00</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">?</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>645</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">$16,125.00</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">?</TD></TR></TBODY></TABLE>

 

[c_m]

Welcome to the board!
Try this:
=4/5*B2+1/5*1.5*B2

 

[rriddleusmc]

(=4/5*B2+1/5*1.5*B2) worked about 3 cases out of 6. Here is a case that should have came out to $6500. 4 days of overtime ($1500.00) and 5 days of straight time ($5000.00) for a total of $6500.00.
<TABLE style="WIDTH: 254pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=338 border=0><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 77pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: yellow" width=103 height=20>Budget (hours)</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 92pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" width=122>straight time ($25)</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 85pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow" width=113>over time ($37.5)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>240</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">$6,000.00</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">6600</TD></TR></TBODY></TABLE>

 

[rriddleusmc]

This is my first day to post threads here. I haven't found an answer yet but have another question. After the problem is solved, how does the thread get closed out?

 

[Fazza]

Hi. No close out is the norm. Regards, Fazza

=INT(A2/50)*1375+(A2-INT(A2/50)*50)*25

 

[rriddleusmc]

Where did you get the 1375 figure from? It works great! If you change to a 60 hour budget do you just change the 50's in the formula to 60 or do you have to make other adjustments?

 

[Fazza]

1375 is the weekly cost : 40*25+10*37.5

So, formula could instead be

=INT(A2/50)*(40*25+10*37.5)+(A2-INT(A2/50)*50)*25

Or even,

=INT(A2/50)*(40*25+(50-40)*37.5)+(A2-INT(A2/50)*50)*25

Now change 50 to 60.

 

[Fazza]

I just saw your pm that this is wrong & querying the 25 on the end. Let me have a fresh look. 25 doesn't seem right. 37.5 would seem better there. I'll check it now.

 

[Fazza]

I was away from my desk for an hour just before so had to remember what I'd done. Short memory! And I'd wiped the file.

The 25 should be OK. The second half of the formula is the normal hours @ $25/hr for the partial (<50 hour) week.

So. The formula is doing what I think it should. I must have interpreted incorrectly.

Please can we check the situation?

A 240 hour task using 50 hour weeks. That is 4 full weeks at $1375/wk then 40 hours at $25/hour. So, $6500.

Please, what should it be?

 

[rriddleusmc]

Everything works just as we need it. I was just inquiring to how the formula worked so I could use it in other situations. Thanks for all the help. It is greatly appreciated.