Formula to project target % needed by month to achieve goal based on current % achievement

[dana072002]

Hi there,
I am new here an hoping someone can help me!
I originally posted in another forum but no one answered?
Maybe I am ding something wrong?
I need this formula for a presentation I have to do next week, please help!!!!

I am in need f a formula to help me project the target % I would need to hit for each remaining month of the year in order to reach my goal of 100%ACH/92%ADH.

Category	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	'18 YE	'19 YTD
YTD ACH %													102.7%	102.1%
YTD ADH %													83.7%	85.8%
ACH %	102.3%	87.5%	90.0%	107.0%	110.3%	101.1%	94.7%	117.0%	?	?	?	?
ADH %	87.3%	78.3%	81.3%	83.7%	92.5%	86.0%	86.7%	85.7%	?	?	?	?

<tbody>
</tbody>

Thanks!

 

[Toadstool]

Hi dana072002,

Can you give the formula for your '19 YTD values as I don't get the same 102.1% and 85.8% as you do?

 

[Eric W]

Welcome to the Board!

How are your percentages calculated? Generally speaking, percentages are based on some underlying data, like this year's sales divided by last year's sales. So in order to calculate a percentage, you need the sales values. As a rule, you can't just average percentages together and get a meaningful value, you need the underlying data. How are your YTD percentages calculated in column O? Also, if we calculate a percentage for Sep-Dec, they will all be the same, unless you have some reason to think that each of those months might have some quantifiable difference.

 

[dana072002]

Hi Toadstool,
it was calculated based on the pivot table results below. Is this what you need?
Thanks!

Row Labels	Sum of Planned Units (MPS)	Sum of Delivered Units	Sum of Adherence Units	Sum of Ach%	Sum of Adh%
Jan	3,441,930	3,519,511	3,006,368	102.3%	87.3%
Feb	3,053,006	2,672,664	2,390,647	87.5%	78.3%
Mar	2,421,805	2,178,589	1,970,033	90.0%	81.3%
Apr	3,413,658	3,650,964	2,856,131	107.0%	83.7%
May	4,888,165	5,392,865	4,520,021	110.3%	92.5%
Jun	3,471,940	3,510,888	2,984,728	101.1%	86.0%
Jul	3,607,004	3,414,082	3,129,008	94.7%	86.7%
Aug	3,133,893	3,665,720	2,685,498	117.0%	85.7%
Grand Total	27,431,401	28,005,283	23,542,434	102.1%	85.8%

<colgroup><col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 3982;"> <col width="184" style="width: 138pt; mso-width-source: userset; mso-width-alt: 6542;"> <col width="153" style="width: 115pt; mso-width-source: userset; mso-width-alt: 5432;"> <col width="161" style="width: 121pt; mso-width-source: userset; mso-width-alt: 5717;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3128;"> <col width="90" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3185;"> <tbody> </tbody>

 

[dana072002]

Hi Eric,
the % are calculated based on units planned vs units atoll received, which is the ACH%, then we have how many of the received units are received in accordance to the plan.
Does this make sense?
Thanks1!

Row Labels	Sum of Planned Units (MPS)	Sum of Delivered Units	Sum of Adherence Units	Sum of Ach%	Sum of Adh%
Jan	3,441,930	3,519,511	3,006,368	102.3%	87.3%
Feb	3,053,006	2,672,664	2,390,647	87.5%	78.3%
Mar	2,421,805	2,178,589	1,970,033	90.0%	81.3%
Apr	3,413,658	3,650,964	2,856,131	107.0%	83.7%
May	4,888,165	5,392,865	4,520,021	110.3%	92.5%
Jun	3,471,940	3,510,888	2,984,728	101.1%	86.0%
Jul	3,607,004	3,414,082	3,129,008	94.7%	86.7%
Aug	3,133,893	3,665,720	2,685,498	117.0%	85.7%
Grand Total	27,431,401	28,005,283	23,542,434	102.1%	85.8%

<colgroup><col width="112" style="width: 84pt; mso-width-source: userset; mso-width-alt: 3982;"> <col width="184" style="width: 138pt; mso-width-source: userset; mso-width-alt: 6542;"> <col width="153" style="width: 115pt; mso-width-source: userset; mso-width-alt: 5432;"> <col width="161" style="width: 121pt; mso-width-source: userset; mso-width-alt: 5717;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3128;"> <col width="90" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3185;"> <tbody> </tbody>

 

[Eric W]

Do you have the values for Sum of Planned Units for Sep-Dec?

 

[dana072002]

No, they won't be done until the start of each new month. we are getting ready to do sept. now. I am trying to do a calculation to estimate what % I would need to achieve each of the remaining months based on the previous months in order to end with a yearly average of 100% ACH and 92% ADH. So, what % would I need to hit Sep-Dec in order to end the year with 100% ACH/92% ADH. I hope I am this properly.

 

[Eric W]

You can't look for a target percentage without knowing (or estimating) the Sum of Planned Units. For example, let's assume that your August Sum of Delivered Units is 3,000,000 instead of 3,665,720. Then your Grand Total (YTD) Sum of ACH% is 99.67%. So you wouldn't expect the target percentage you're looking for would be very much higher, right? Maybe 102%? Now let's say that your Sum of Planned Units for Sep-Dec is all 1 and the Sum of Delivered Units for Sep-Dec is all 2. 200% for all 4 months! But the YTD percentage total? Exactly the same. There will be some difference in decimal places far down the line, but nothing significant. This is an extreme example, but it shows the issue.

This shows you need to estimate the Sum of Planned Units before you can estimate an ACH% needed to hit 100% for the year, which will incidentally give you an estimate for the Sum of Delivered Units. (And the same for Sum of Adherence Units.) I suppose you could estimate the Sum of Planned Units for Sep-Dec by averaging the Jan-Aug values, but there's such a high variability in these numbers (May is twice as high as March), I wouldn't have much confidence in the results.

 

[dana072002]

I understand what you are saying about needing planned, delivered units, etc, but I am only trying to create a formula for projecting what % I would need to achieve for the remaining months in the year to achieve an average of my targets, regardless of my what the planned units are.

For example, I plugged in random % for Sept-Dec. and even if I hit 100% every month Sept-Dec I can only end up with 90.1% ADH, but for my ACH%, I plugged in 99% every month and I am getting 100.5% ACH. I am just using he basic AVG formula for that, but I am trying get a formula to exactly project what % I would need Sep-Dec for ACH order to end the year with 100%. I don't know if it would be an IF statement or what other formula I could use to do this?

 

[Eric W]

I understand what you are saying about needing planned, delivered units, etc, but I am only trying to create a formula for projecting what % I would need to achieve for the remaining months in the year to achieve an average of my targets, regardless of my what the planned units are.

Somehow I'm not getting through. Let's try this.

IT. CAN'T. BE. DONE.

I showed you an example of using 200% as a projection, and it didn't work. You yourself tried 100% every month and still failed to meet your 92% goal. Let me show you an example of how to project a percentage when you start with an estimate of the MPS. I just used the average as I proposed earlier.

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Category	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	'18 YE	'19 YTD
2	YTD ACH %													102.70%	102.10%
3	YTD ADH %													83.70%	85.80%
4	ACH %	102.30%	87.50%	90.00%	107.00%	110.30%	101.10%	94.70%	117.00%	?	?	?	?
5	ADH %	87.30%	78.30%	81.30%	83.70%	92.50%	86.00%	86.70%	85.70%	?	?	?	?
6
7
8
9
10	Row Labels	Sum of Planned Units (MPS)	Sum of Delivered Units	Sum of Adherence Units	Sum of Ach%	Sum of Adh%
11	Jan	3,441,930	3,519,511	3,006,368	102.25%	87.35%			# to achieve 100% ACH			# to achieve 92% ADH
12	Feb	3,053,006	2,672,664	2,390,647	87.54%	78.30%			13,141,819			12090473
13	Mar	2,421,805	2,178,589	1,970,033	89.96%	81.35%
14	Apr	3,413,658	3,650,964	2,856,131	106.95%	83.67%			amount per month			amount per month
15	May	4,888,165	5,392,865	4,520,021	110.32%	92.47%			3285454.6			3022618
16	Jun	3,471,940	3,510,888	2,984,728	101.12%	85.97%
17	Jul	3,607,004	3,414,082	3,129,008	94.65%	86.75%			required % per month			required % per month
18	Aug	3,133,893	3,665,720	2,685,498	116.97%	85.69%			95.82%			88.15%
19	Sep	3,428,925			0.00%	0.00%
20	Oct	3,428,925			0.00%	0.00%
21	Nov	3,428,925			0.00%	0.00%
22	Dec	3,428,925			0.00%	0.00%
23
24	Grand Total	41,147,102	28,005,283	23,542,434	68.06%	57.22%

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula E11 =C11/B11 F11 =D11/B11 B19 =AVERAGE(B$11:B18) B24 =SUM(B11:B22) C24 =SUM(C11:C22) D24 =SUM(D11:D22) E24 =C24/B24 F24 =D24/B24 I12 =B24-C24 I15 =I12/4 I18 =I15/B19 L12 =(SUM(B11:B22)-SUM(C11:C18))*0.92 L15 =L12/4 L18 =L15/B19 <tbody> </tbody>

<tbody>
</tbody>

Given the estimates in B19:B22, you can calculate the number you need to hit 100% for the year (I12). Divide that by 4 to get the number you need each month (I15). Then divide that by the monthly estimate to get the percentage estimate (I18). If you plug in the I12 amount into C19:C22, you'll get the percentage you expect. If you change the estimates in B19:B22, you'll get completely different results in the I and L columns.


I suspect you're thinking of a problem like: if I score 90, 92, 75, 88 on 4 tests, what do I need on the last 2 to average 95% overall? But the difference is that in this problem, each test has a maximum score of 100. But in your problem, there is no defined upper limit.