Forecasting with Waterfall Diagram

[Antoan]

Hello everyone!


As obvious from the topic above I am supposed to create a waterfall diagram forecast(You can find below) based on the given data for those products. Has anyone ever worked with such kind of data and if yes could you let me know how i should organise it properly?

VIA FC competition output 2022 - week 6 (1).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ	FA	FB	FC	FD	FE	FF	FG	FH	FI	FJ	FK	FL	FM	FN	FO	FP	FQ	FR	FS	FT	FU	FV	FW	FX	FY	FZ	GA	GB	GC	GD	GE	GF	GG	GH	GI	GJ	GK	GL	GM	GN	GO	GP	GQ	GR	GS	GT	GU	GV	GW	GX	GY	GZ	HA	HB	HC	HD	HE	HF	HG	HH	HI	HJ	HK	HL	HM	HN	HO	HP	HQ	HR	HS	HT	HU	HV	HW	HX	HY	HZ	IA	IB	IC	ID	IE	IF	IG	IH	II	IJ	IK	IL	IM	IN	IO	IP	IQ	IR	IS	IT	IU	IV	IW	IX	IY	IZ	JA	JB	JC	JD	JE	JF	JG	JH	JI
5			2017																																																				2018																																																				2019																																																				2020																																																					2021																																																				2022
6	VarenummerAX	WMSvarenavn	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16	Week 17	Week 18	Week 19	Week 20	Week 21	Week 22	Week 23	Week 24	Week 25	Week 26	Week 27	Week 28	Week 29	Week 30	Week 31	Week 32	Week 33	Week 34	Week 35	Week 36	Week 37	Week 38	Week 39	Week 40	Week 41	Week 42	Week 43	Week 44	Week 45	Week 46	Week 47	Week 48	Week 49	Week 50	Week 51	Week 52	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16	Week 17	Week 18	Week 19	Week 20	Week 21	Week 22	Week 23	Week 24	Week 25	Week 26	Week 27	Week 28	Week 29	Week 30	Week 31	Week 32	Week 33	Week 34	Week 35	Week 36	Week 37	Week 38	Week 39	Week 40	Week 41	Week 42	Week 43	Week 44	Week 45	Week 46	Week 47	Week 48	Week 49	Week 50	Week 51	Week 52	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16	Week 17	Week 18	Week 19	Week 20	Week 21	Week 22	Week 23	Week 24	Week 25	Week 26	Week 27	Week 28	Week 29	Week 30	Week 31	Week 32	Week 33	Week 34	Week 35	Week 36	Week 37	Week 38	Week 39	Week 40	Week 41	Week 42	Week 43	Week 44	Week 45	Week 46	Week 47	Week 48	Week 49	Week 50	Week 51	Week 52	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16	Week 17	Week 18	Week 19	Week 20	Week 21	Week 22	Week 23	Week 24	Week 25	Week 26	Week 27	Week 28	Week 29	Week 30	Week 31	Week 32	Week 33	Week 34	Week 35	Week 36	Week 37	Week 38	Week 39	Week 40	Week 41	Week 42	Week 43	Week 44	Week 45	Week 46	Week 47	Week 48	Week 49	Week 50	Week 51	Week 52	Week 53	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16	Week 17	Week 18	Week 19	Week 20	Week 21	Week 22	Week 23	Week 24	Week 25	Week 26	Week 27	Week 28	Week 29	Week 30	Week 31	Week 32	Week 33	Week 34	Week 35	Week 36	Week 37	Week 38	Week 39	Week 40	Week 41	Week 42	Week 43	Week 44	Week 45	Week 46	Week 47	Week 48	Week 49	Week 50	Week 51	Week 52	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6
7	170209	REMA 1000 Toiletpapir 3 lags 6	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1,485	3,212	3,674	3,872	3,784	3,718	3,454	3,124	3,146	2,816	2,948	2,981	1,969	4,763	1,859	2,464	2,321	2,266	2,013	2,673	3,135	3,003	3,377	3,509	3,333	3,443	2,816	3,234	4,620	3,674	4,411	4,367	3,630	2,871	3,201	3,520	3,388	1,551	4,455	2,519	2,684	2,882	2,948	3,531	3,674	2,959	3,564	4,928	4,389	3,938	3,157	3,091	1,221	1,067	1,430	2,585	2,640	3,432	3,443	3,520	3,718	3,751	3,916	3,773	1,969	5,720	2,629	3,047	3,322	3,432	3,509	3,564	3,454	3,476	3,916	3,586	3,157	1,298	4,290	2,739	4,576	3,355	3,300	4,378	3,586	3,619	3,828	3,828	3,509	1,617	5,005	3,190	12,056	10,604	4,554	4,367	3,685	3,355	3,916	4,444	4,147	3,927	4,059	3,883	3,575	3,751	3,938	3,806	4,136	3,938	3,729	3,685	1,716	7,029	2,420	3,146	3,839	3,773	4,180	4,213	4,466	3,663	3,663	2,024	2,827	3,080	3,993	4,191	3,960	3,729	4,158	4,532	6,160	3,828	1,287	7,612	2,321	3,388	3,971	4,279	4,466	4,444	4,037	4,488	4,598	4,576	4,114	3,366	4,103	4,664	5,104	1,166	4,796	2,585	3,894	3,718	4,290	4,246	4,686	4,587	4,862	4,653	4,576	4,378	4,268	4,829	4,246	4,147	4,554	4,609	4,444	4,378	4,477	4,180	4,169	4,136	2,156	2,871	3,685	3,762	3,718	3,663	4,411	4,565	7,073	6,028	4,367	4,334	4,884	4,818	4,136	4,609	4,499
8	210000	Red Bull, dåse	1,610	1,706	2,354	2,853	3,120	3,127	2,494	3,075	2,391	2,187	2,657	2,935	3,108	4,477	3,412	2,627	2,875	3,615	3,486	3,815	3,501	3,205	3,260	2,779	3,212	2,787	2,994	3,345	3,275	3,068	3,497	3,996	3,871	3,356	3,730	3,589	3,271	3,134	3,090	3,519	3,527	3,134	2,705	3,404	3,046	2,842	2,109	2,257	3,523	2,072	2,553	1,943	2,006	1,662	1,728	2,424	2,735	2,805	3,105	2,750	2,198	2,313	2,598	2,590	2,664	2,454	2,461	2,872	2,805	2,798	3,575	3,756	3,397	3,108	3,464	2,409	2,709	2,627	3,190	3,142	3,090	2,957	3,689	3,312	2,757	2,017	2,109	2,909	2,687	2,553	2,372	2,657	2,890	2,609	2,613	2,361	2,831	2,872	2,983	2,886	3,264	2,557	2,979	1,902	1,640	1,636	1,806	1,495	1,669	1,462	1,865	2,202	2,454	2,387	2,380	2,576	2,627	2,698	3,001	2,357	3,989	2,317	2,553	2,520	3,060	1,869	2,135	1,643	2,294	2,354	2,783	2,672	2,757	2,454	2,413	2,505	2,072	1,751	2,350	2,761	2,916	2,694	6,535	2,161	2,853	2,576	2,550	2,664	2,657	2,631	2,291	2,024	1,836	1,688	2,909	2,727	2,639	2,727	2,357	2,583	2,546	2,461	2,579	2,265	2,635	3,393	2,202	1,577	2,135	2,017	2,483	2,317	2,602	1,880	2,727	2,594	2,964	3,256	4,637	2,960	3,793	3,541	3,201	3,556	2,794	3,131	2,387	3,460	3,249	2,624	2,516	3,197	3,471	2,842	2,898	2,627	3,530	2,553	2,409	2,487	3,223	3,027	3,116	2,516	2,494	2,698	2,676	1,680	1,943	2,061	2,391	3,031	3,504	3,371	3,286	3,760	3,841	4,592	3,763	2,054	2,949	2,824	3,682	2,605	3,268	2,650	3,416	3,279	3,700	3,249	4,104	4,703	3,871	3,527	4,337	4,348	3,523	2,246	2,357	2,949	3,031	2,986	2,746	3,541	3,601	3,182	2,579	2,905	3,645	3,675	2,328	2,683	3,804	2,883	2,457	2,420	2,631	2,098	2,409	2,664	2,450	2,309	1,947	2,620	2,883	2,072	1,947
9	411669	3 Stjernet kødpølse	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	256	258	255	199	195	197	200	193	157	267	196	235	156	223	194	173	159	143	209	214	239	245	251	241	178	127	260	202	211	245	101	114	155	168	243	183	193	201	209	260	232	227	248	228	155	160	183	215	162	278	205	235	185	235	250	213	226	242	228	207	255	223	252	243	251	246	250	121	100	108	168	249	242	266	266	240	197	242	78	175	223	247	245	247	202	86	159	232	221	228	261	240	193	230	266	252	228	210	203	217	110	232	232	206	210	173	209	192	238	280	239	216	161	302	235	257	260	259	155	207	199	130	230	373	396	358	296	275	180	247	224	122	311	157	143	207	153	204	178	182	194	222	208	211	185	224	211	213	206	192	183	97	161	181	168	223	235	149	263	147	159	207	215	206	233	202	226	225	137	177	171	205	209	121	167	260	214	175	87	135	150	187	210	228	223	222	201	93	113	184	237	222	255	216	230	214	151	324	204	216	177	181	187	200	205	145	299	132	186	213	227	135	174	170	193	224	195	199	224	136	218	181	201	121	143	103	155	256	256	208	131	269	191
10
11	60130	Napoli brød	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	324	2,368	2,311	2,676	2,619	1,782	2,047	2,374	1,565	2,779	1,525	1,702	1,976	3,067	1,923	2,221	2,401	2,635	2,411	3,743	3,706	3,107	2,649	2,732	2,605	3,030	3,284	3,498	3,120	3,371	3,458	3,515	3,378	3,321	2,023	2,458	1,525	2,425	3,174	2,940	1,849	2,073	1,679	1,966	2,435	2,903	2,629	2,468	5,224	5,729	6,425	5,358	5,214	4,144	4,395	4,545	4,626	4,455	2,890	2,197	3,716	3,542	4,656	6,536	6,041	4,846	6,462	5,960	5,870	4,766	3,639	3,378	4,559	4,328	5,178	4,659	5,047	5,154	6,158	4,151	4,305	4,024	4,676	4,887	4,813	5,586	4,873	3,094	1,411	2,485	4,094	4,375	4,696	4,917	4,408	2,515	2,030	3,769	2,518	3,064	6,485	5,806	5,689	6,422	6,218	5,897	5,519	5,984	6,241	5,254	3,308	2,311	1,946	3,595	5,669	8,211	4,462	5,847	5,378	5,709	2,829	4,482	4,833	5,157	5,238	2,956	2,528	3,589	4,090	4,191	4,823	6,144	5,432	5,214	3,124	2,923	3,204	4,090	4,351	6,017	4,726	3,562	4,672	5,077	4,820	4,833	4,646	4,485	4,810	4,074	1,990	1,043	1,364	1,746	5,796	4,191	4,773	4,927
12	60086	Rema 1000 naanbrød m/hvidløg	1,137	1,565	1,749	1,481	1,391	1,873	1,364	1,679	1,408	1,639	1,495	1,521	879	1,297	812	1,010	1,371	1,605	1,418	1,501	1,555	1,742	1,532	1,655	1,986	1,404	1,585	1,568	1,829	1,672	1,618	1,742	1,689	1,491	1,629	1,511	1,424	1,588	1,511	1,518	1,465	1,307	1,491	1,374	1,421	1,311	1,314	1,287	1,307	1,220	1,264	1,057	1,036	1,247	1,699	2,013	1,990	2,087	1,896	2,100	2,120	2,043	2,027	2,167	1,204	1,699	1,873	2,471	2,612	1,976	2,803	2,415	2,572	2,970	3,064	2,759	2,488	2,448	2,348	2,157	2,415	2,304	2,097	2,528	2,251	2,291	2,291	2,124	2,144	2,298	2,361	2,284	2,080	1,889	2,160	2,388	2,083	2,100	2,073	2,027	2,201	2,013	2,087	1,575	1,414	1,876	2,679	3,064	2,960	3,033	1,411	1,642	2,007	2,294	2,706	2,445	2,752	2,916	2,766	1,839	2,057	2,649	2,813	2,398	2,448	2,060	2,368	1,996	2,990	2,963	2,455	2,555	2,261	2,384	2,592	2,508	2,522	2,997	2,930	2,599	3,458	2,990	2,846	3,023	2,635	2,308	2,783	3,007	2,649	2,378	2,572	2,445	2,502	2,304	2,568	2,331	1,766	3,595	3,515	3,495	4,458	4,261	3,003	3,452	3,368	3,435	5,074	3,331	2,351	3,094	2,502	3,241	4,218	3,933	3,472	3,870	3,605	3,866	3,127	3,512	3,619	3,950	2,993	3,010	3,488	3,672	3,010	3,963	3,351	3,361	3,251	3,140	3,251	3,047	3,468	3,157	3,184	2,676	3,224	3,144	3,274	2,736	1,582	2,120	2,354	2,287	2,739	1,920	2,117	4,024	3,840	2,843	3	5,649	4,596	4,010	4,408	5,241	3,980	3,625	3,468	1,993	2,619	3,465	5,027	2,625	3,227	2,793	2,833	2,535	3,532	2,970	2,849	2,625	2,665	2,438	2,886	2,388	2,783	2,987	3,308	2,913	3,027	2,970	2,813	2,615	2,716	2,699	2,863	2,816	2,107	2,676	2,729	2,562	2,635	2,629	2,448	2,612	2,244	1,521	1,782	2,806	3,177	3,334	3,294	2,910	3,294
Ark1

Cell Formulas
Range	Formula
A11:B11	A11	='C:\Users\rasmfr\Desktop\divs\[VIA forecasting competition.xlsx]Ordered'!A16
A12:B12	A12	='C:\Users\rasmfr\Desktop\divs\[VIA forecasting competition.xlsx]Ordered'!A15

Here is the forecasting waterfall diagram that i need to fill in accordingly with the data above:

Forecasting Competition Waterfall - week 7.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	Product 1: REMA 1000 Toiletpapir 3 lags 6
3	Date	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13	Week 14	Week 15	Week 16
4	Actual sales	4213
5	Origin												Squared Error
6	Week 7	11160										Forecast 1	48260809
7	Week 8											Forecast 2	0
8	Week 9											Forecast 3	0
9	Week 10											Forecast 4	0
10	Week 11											Forecast 5	0
11	Week 12											Forecast 6	0
12	Week 13											Forecast 7	0
13	Week 14											Forecast 8	0
14	Week 15											Forecast 9	0
15	Week 16											Forecast 10	0
16												RMSE	2196.83
17
18	Product 2: Red Bull, dåse
Forecasts

Cell Formulas
Range	Formula
M6	M6	=(B4-B6)^2
M7	M7	=(C4-C7)^2
M8	M8	=(D4-D8)^2
M9	M9	=(E4-E9)^2
M10	M10	=(F4-F10)^2
M11	M11	=(G4-G11)^2
M12	M12	=(H4-H12)^2
M13	M13	=(I4-I13)^2
M14	M14	=(J4-J14)^2
M15	M15	=(K4-K15)^2
M16	M16	=SQRT(AVERAGE(M6:M15))

Looking forward to hearing from you!


Thank you in advance for your help!