stretching a forecast

[MartinL]

Sorry for the lousy title
I have had a request from a different team to see if I could solve a problem for them....I cant.
So I am turning to my friends here to see if you have an ideas.

Basically we have a 13 week promotion table[Col A]
That shows the consumer take up as a percentage [Col B]

What is needed is the percentages stretched out over 17 weeks
How on earth can I even start looking at this as a best guess.

Percentages for this task can be to 4 decimal places I have shown 0dp just for ease of writing this.

Week	%Current
1	1%
2	4%
3	4%
4	6%
5	10%
6	12%
7	12%
8	20%
9	18%
10	9%
11	2%
12	1%
13	1%
14
15
16
17

<tbody>
</tbody>

 

[joeu2004]

I would do the following.

	A	B	C	D	E	F	G	H	I	J	K
1	Orig Distrib​		Orig Lookup Table​		New Distrib​
2	Wk#​	Distrib​		Table#​	Cuml Distrib​	New Wk#​		Wk#​	Distrib​	Cuml Distrib​	Orig Table#​
3				1	0.0000%	0.0000
4​	1	1.0000%		2	1.0000%	1.3077		1	0.7647%	0.7647%	1
5​	2	4.0000%		3	5.0000%	2.6154		2	2.3529%	3.1176%	2
6​	3	4.0000%		4	9.0000%	3.9231		3	3.0589%	6.1765%	3
7​	4	6.0000%		5	15.0000%	5.2308		4	3.1764%	9.3529%	4
8​	5	10.0000%		6	25.0000%	6.5385		5	4.5883%	13.9412%	4
9​	6	12.0000%		7	37.0000%	7.8462		6	6.9412%	20.8824%	5
10​	7	12.0000%		8	49.0000%	9.1538		7	8.3529%	29.2353%	6
11​	8	20.0000%		9	69.0000%	10.4615		8	9.1765%	38.4118%	7
12​	9	18.0000%		10	87.0000%	11.7692		9	9.1764%	47.5882%	7
13​	10	9.0000%		11	96.0000%	13.0769		10	14.3530%	61.9412%	8
14​	11	2.0000%		12	98.0000%	14.3846		11	14.4706%	76.4118%	9
15​	12	1.0000%		13	99.0000%	15.6923		12	12.1764%	88.5882%	10
16​	13	1.0000%		14	100.0000%	17.0000		13	6.8824%	95.4706%	10
17								14	1.9412%	97.4118%	11
18								15	1.0588%	98.4706%	12
19								16	0.7647%	99.2353%	13
20								17	0.7647%	100.0000%	14

<tbody>
</tbody>

E4: =E3+B4
F4: =A4*17/13
I4: =ROUND(J4-SUM($I$3:I3), 6)
J4: =IF(K4=$D$16, 1, FORECAST(H4,OFFSET($E$3,K4-1,0,2,1),OFFSET($F$3,K4-1,0,2,1)))
K4: =MATCH(H4,$F$3:$F$16)

Copy E4:F4 into E5:F16
Copy I4:K4 into I5:K20

The FORECAST formula does a linear interpolation of the cumulative segment that contains the new week number. The MATCH formula finds the low table index of the pairwise segment.

 

[joeu2004]

E4: =E3+B4
F4: =A4*17/13
I4: =ROUND(J4-SUM($I$3:I3), 6)
J4: =IF(K4=$D$16, 1, FORECAST(H4,OFFSET($E$3,K4-1,0,2,1),OFFSET($F$3,K4-1,0,2,1)))
K4: =MATCH(H4,$F$3:$F$16)

Copy E4:F4 into E5:F16
Copy I4:K4 into I5:K20

KISS....

E4:  =E3+B4
F4:  =A4*17/13
I4:  =ROUND(J4-SUM($I$3:I3), 6)
J4:  =FORECAST(H4,OFFSET($E$3,K4-1,0,2,1),OFFSET($F$3,K4-1,0,2,1))
J20: 100%
K4:  =MATCH(H4,$F$3:$F$16)

Copy E4:F4 into E5:F16
Copy I4 into I5:I20
Copy J4:K4 into J5:K19

 

[MartinL]

Hi Joeu2004

That is an awesome piece of analysis, thanks for your time and effort.

Martin