Percent Goal Spent based on start and end dates

[rz6657]

I've been trying to have a formula that will calculate how much ideally the project should have spent based on the start and end dates... I have a budget, actuals, variance and unspent %. I just need a % goal of where we should be at (spent so far) based the aforementioned dates. Please see attached. The Start date (K78) will be in that cell it's now, the M9 (K80) is the end date. The formula will be in cell Q80... thx

[chrisa86]

Would something like =(NOW()-K78)/(K80-K78) be all you need? If I've understood correctly and you're just after knowing "well it's day 100 of 300" so we should have aimed to have spent no more than 1/3 of the budget? Obviously, at the mo, it's evaluating it as greater than 1 as the end date has passed so could change to =MIN((NOW()-K78)/(K80-K78),1) so it'll output 100% at most...

[6StringJazzer]

chrisa86 has the correct answer to the question you asked--it gives the time passed as a percent of the total time scheduled, which you can multiply times the total budget, and compare the result to how much was actually spent. This will tell you if you are spending money at the rate planned.

From a management standpoint this method has flaws. Suppose that half of the time has passed, and you have spent half of the budget. It makes it look like you are right on target. But all you are on target for is spending money; it's easy to spend money. It's hard to get work done. Best practice is to also measure the work performed during that period, and determine if you have performed half of the work. It's more effort to measure work performed than to measure how much you spend, but it gives real insight as to whether you're on schedule and budget. There is a whole methodology around this concept called Earned Value Management (EVM). You don't have to do full EVM but consider whether you should also be measuring results, not just money.

[rz6657]

Chrisa86 - thx, just what I needed...

6StringJazzer - you are totally right; however, we are tracking the milestones and work separately. I just need this as a high level gauge to force my team to look into our spend and do an analysis...


thx