Jump to content
The Wifcon Forums and Blogs
Sign in to follow this  
tjsmith1957

How to Calculate Weighted Average for Escalation

Recommended Posts

38 minutes ago, tjsmith1957 said:

I need to do a weighted average of 2% over 5 years for a proposal I am working.

I don't understand what this means.  Can you be more specific as to what the issue is?

Share this post


Link to post
Share on other sites

I am working a cost proposal and it has a base period of 5 years. I want to utilize the 2% escalation. SO what I am trying to work is a weighted escalation for the 5 year period based off of 2%. 

Share this post


Link to post
Share on other sites

First, what is your starting point? Is it January 1 of the year, or day 1 of the contract year, or what?

Second, how do costs increase during the year? Do they increase evenly day-by-day, or one quarter per quarter, or is there a midpoint?

Third, do you know the element of cost(s) to which you are applying the escalation, and do you have appropriate support for your chosen escalation rate for that element of cost?

Share this post


Link to post
Share on other sites

My starting point is Jan 1, it will be based on our FY. I am trying to work escalation for individual labor categories and associated rates. So for example for the first year of the 5 years I have 24 labor cateogires with associated rates, then I will need to escalate out on a weighted average the 2% for years 2 -5.

 

I do have adequate support for the years 2 - 5 for the 2% escalation, and I have spoken in my narrative that we will do a weighted average of the 2% over the base year (Years 2 0 5)

Share this post


Link to post
Share on other sites

I'm still not clear whether you are escalating each of 24 labor categories independently or if you are combining them into one direct labor number. If each is individual then the weighting takes into account the percentage of unescalated labor each year and the percentage of escalated labor each year. So, if raises are issued July 01, then you have an even weighting between first half and second half. However, if you are aggregating all categories into a bottom line labor number, then you are going to weight them by both hourly rate and by number of labor hours incurred (or estimated to be incurred).

Apologies but this is the best I can do from the other side of the screen. Try setting up an Excel worksheet for each year and playing with it to see how weighting impacts the values.

Share this post


Link to post
Share on other sites

So, if I understand it correctly, you are proposing a base year with rates unchanged for year 1.  Then you want to escalate rates for years 2 through 5 so the overall aggregate increase for the entire amount is 2%?  
 

If so, increasing annually by 0.5% gets you close but not exact because the annual amounts are compounded.  It will be more than 2.0% - something like 2.02%

Share this post


Link to post
Share on other sites
8 hours ago, tjsmith1957 said:

I am thinking ot make it easy I would do .5 for each of the 4 out years. Thoughts?

No, that's not a 2% escalation. That's a 0.5% escalation. You need to do it annually. You also need to apply the escalation to each labor category individually. If somebody makes $20.00 per hour, a 2% escalation results in $20.40 per hour; whereas, if somebody makes $50.00 per hour then a 2% escalation results in $51.00 per hour. You need to escalate from 01 January to the contract start date, and also from there to the midpoint of the contract year. If the contract year crosses calendar years, you have to apply the escalation twice: once to the first year and then again (plus 2% escalation) to the labor in the second year.

Finally, you need to multiply the escalated rates by the quantity of labor hours proposed for each labor category each contract year to calculate the estimated direct labor dollars.

This is not particularly hard to do if you have decent Excel skills, but I get the concepts can be challenging. Is there anybody in your workplace to whom you would feel comfortable asking to review your work?

Share this post


Link to post
Share on other sites

This is getting more confusing all the time.  Do you want to annually increase rates by 2% or do you want the overall increase over five years to be 2% (about 0.5% each year)?

2% annual escalation results in the last year rates being 8.25% more than the base year.  0.5% annual escalation makes the last year rates 2% more than the base.

Which is it?

Share this post


Link to post
Share on other sites

Hopefully this helps. Assume your first labor category is Software Development at $100 per hour. Yay for the rate!  If you escalate 2% then in year 2 that is $102/r and in year 3 it's not $104/hr but it is $102 x 1.02 = $104.04 per hour. In other words you must escalate the previous year's escalation. That is why formerfed calculates year 5 as 1.02 x 1.02 x 1.02 x 1.02 = 1.08243 or 8.24% escalation over five years. The math here is that year-n rates are equal to the year 1 rates x ( 1 + annual_escalation_% ) ^ n, where that funny hat ^ symbol means "to the power of".

So to achieve a total escalation of 2% in year 5, assuming exactly four escalations in years 2, 3, 4 & 5 you would need the annual escalation of rate increases to be 0.496% instead of 0.5% 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...