Predicted Timescale vs Actual Timescale

[SPalmerUK]

Hi,

Probably an easy question but the answer eludes me.

I have a target that needs to be hit - 48,185.

Weekly, I need to be hitting 2,878.

So, 48,185 / 2,878 = 16.74. This would be the expected number of weeks it should take to hit the overall target.

Now if my actual is less than 2,878 I need to see how much longer it will take to hit the overall target.

Ideas?

Thanks

 

[Momentman]

If your actual is 2000 for example, then the Extension would be

=48185/2000 - 48185/2878

=7.4

Depending on how your sheet is set-up, you can write to formulas to reflect the above

 

[SPalmerUK]

Ok, works fine for the first week, but if I add in a second week (assuming both weeks hit the 2878) i get -8.37.

2 weeks of hitting the weekly target wouldn't halve my overall time frame.

 

[Momentman]

Can you show a small set-up of your sheet and what you really want to achieve?

if I do 2000 in the first week and 2000 in the second week, what should be the extra time required(mathematically) based on your computation logic

 

[SPalmerUK]

			Week 1		Week 2		Week 3
			16/10/2017 to 22/10/2017		23/10/2017 to 29/10/2017		30/10/2017 to 05/11/2017
			Target	Achieved	%	Target	Achieved	%	Target	Achieved	%
		Trucks(Cumulative)	2878	79	3%	5677		0%	8555		0%
		Hauliers	115		0%	230		0%	345		0%
		Trucks(Weekly)	2878	79	3%	2878	0	0%	2878	0	0%
Current Trucks (Benchmark)	51,815
Target Trucks	100,000
Trucks to get	48,185
Weekly Target	2,878
Trucks Signed	79
% Signed	0.16%
Trucks Remaining	48,106
% Remaining	99.84%
Weekly Target Hauliers	115
Current Hauliers (Benchmark)	2,076
Expected Weeks	16.74
Actual Weeks	593.19			46,862.34

<tbody>
</tbody>

These are the first three weeks of the spreadsheet - it's the actual weeks cell that i'm working on

 

[SPalmerUK]

			Week 1
			16/10/2017 to 22/10/2017
			Target	Achieved
		Trucks(Cumulative)	2878	79
		Hauliers	115
		Trucks(Weekly)	2878	79
Current Trucks (Benchmark)	51,815
Target Trucks	100,000
Trucks to get	48,185
Weekly Target	2,878
Trucks Signed	79
% Signed	0.16%
Trucks Remaining	48,106
% Remaining	99.84%
Weekly Target Hauliers	115
Current Hauliers (Benchmark)	2,076
Expected Weeks	16.74
Actual Weeks	593.19			46,862.34

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[SPalmerUK]

The solution I needed was:

B10/AVERAGEIFS(D4:DD4,D3:DD3,"Achieved",D4:DD4,">0")