Rolling Wave Capacity Planning

[mporre02]

Hi There,



Hoping to get some help. I have created a resource and capacity tracker with several formulas and conditional formatting built in to detect changes in capacity on a week to week basis.



However, the problem I am trying to solve is, that when I update the utilization(that is in %), I only want it to update for the current week. For instance. If I change someone's utilization from 10% to 20% I would like it to update for that current week, but leave the previous weeks unchanged so I can have a rolling history. I have attached a couple of screen shots of what I mean. Hopefully someone can help.



Thanks in advance.

 

[RobP]

Hi, welcome to the forum.

Would I be correct in thinking that the previous weeks utilisation data are coming from a formula which is using the same % change co9lumn that you plan to amend ? (Col J)

If so, of course all the previous formulas using that % will get adjusted - which is why correctly you've identified it as a problem.

One option would be for you to copy all the previous weeks data, and re-paste themselves over the formulas as absolute values. Thus they don't get updated, and stay in the sheet as absolute values.
Say, on a Monday, you would highlight the previous weeks formulas, and copy / paste back into itself as values ? Then if you ammend Col J (assuming thats the col your formula is linked to, only this week gets modified.

Does that help ?

cheers
Rob