Forecast Consumption Formula

[BMil8]

Hello, I have a file that I use to track a unit forecast. I need a formula that will adjust the forecast for future weeks, based on if the weeks prior exceeded or fell short of that weeks forecast.

Example: Assume we just finished week 4 and have exceeded the forecast for those 4 weeks by 1650 units. I want the future weeks to start deducting from the Orig FCST line. 1000 units removed from weeks 5, and 650 removed from week 6.

	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9
Orig FCST:	1000	1000	1000	1000	1000	1000	1000	1000	1000
Actual:	1400	1100	1250	1900
New FCST					0	350	1000	1000	1000

Then after each week that passes, it will adjust the New FCST line as needed. Example: After we sell 2300 units in Week 5 the New FCST would look as follows

	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9
Orig FCST:	1000	1000	1000	1000	1000	1000	1000	1000	1000
Actual:	1400	1100	1250	1900	2300
New FCST						0	0	50	1000 ​

You can see that the Orig and (Actual + New FCST) both total 9000, the weeks are just adjusted to account for what has actually happened. Is this possible?

Thanks in advance.

 

[Marcelo Branco]

Try

B4 copied across
=IF(ISNUMBER(B3),"",MIN(MAX(0,SUM($B2:B2)-SUM($B3:B3)),B2))

M.

 

[Jeffrey Mahoney]

I was trying to create a rolling average of the last two weeks.

test varieties.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		Total	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9
2	Orig FCST:	9000	1000	1000	1000	1000	1000	1000	1000	1000	1000
3	Actual:	5650	1400	1100	1250	1900
4	New FCST	11675	1400	1100	1250	1900	1575	1450	1000	1000	1000
Sheet11

Cell Formulas
Range	Formula
B2:B4	B2	=SUM(C2:K2)
C4:D4	C4	=IF(C3<>"",C3,C2)
E4:K4	E4	=IF(E3<>"",E3,MIN(INT(AVERAGE(IF(C3<>"",C3,C2),IF(D3<>"",D3,D2))),$B$2-SUM($C3:D3)))

 

[BMil8]

IF(ISNUMBER(B3),"",MIN(MAX(0,SUM($B2:B2)-SUM($B3:B3)),B2))

Perfect, thank you!

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.