tjsmith1957 Posted April 10, 2020 Report Share Posted April 10, 2020 I need to do a weighted average of 2% over 5 years for a proposal I am working. How would I calculate this? Link to comment Share on other sites More sharing options...
Retreadfed Posted April 10, 2020 Report Share Posted April 10, 2020 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? Link to comment Share on other sites More sharing options...
tjsmith1957 Posted April 10, 2020 Author Report Share Posted April 10, 2020 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%. Link to comment Share on other sites More sharing options...
here_2_help Posted April 10, 2020 Report Share Posted April 10, 2020 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? Link to comment Share on other sites More sharing options...
tjsmith1957 Posted April 10, 2020 Author Report Share Posted April 10, 2020 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) Link to comment Share on other sites More sharing options...
here_2_help Posted April 10, 2020 Report Share Posted April 10, 2020 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. Link to comment Share on other sites More sharing options...
tjsmith1957 Posted April 13, 2020 Author Report Share Posted April 13, 2020 I need to escalate each of the labor categories independently. Escalation will happen Jan 1 of each year. So would my weighting be .5 each year? Link to comment Share on other sites More sharing options...
formerfed Posted April 13, 2020 Report Share Posted April 13, 2020 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% Link to comment Share on other sites More sharing options...
tjsmith1957 Posted April 13, 2020 Author Report Share Posted April 13, 2020 I am thinking ot make it easy I would do .5 for each of the 4 out years. Thoughts? Link to comment Share on other sites More sharing options...
here_2_help Posted April 13, 2020 Report Share Posted April 13, 2020 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? Link to comment Share on other sites More sharing options...
formerfed Posted April 14, 2020 Report Share Posted April 14, 2020 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? Link to comment Share on other sites More sharing options...
Richard Minney Posted May 9, 2020 Report Share Posted May 9, 2020 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% Link to comment Share on other sites More sharing options...
Recommended Posts