How do I redistribute sales fairly if I want to increase the growth percentage?

[tblackwell]

I am working on a sales forecast for 2020. The forecast has 92 locations and I have sales by week by site for Period 1 2019 and a forecast of sales by week for Period 1 2020.

Period 1 2019 Sales Total: $11,586,579
Period 1 2020 Sales Forecast: $11,57,090
Dollar Change in sales 2019 to 2020: +$512
Percent Change in sales 2019 to 2020: +0.00%

Let's say I think the forecast of +0.00% is too conservative and I want to raise the forecast to +1.00%. This would mean that...

Period 1 2019 Sales Total: $11,586,579
Period 1 2020 Sales Forecast: $11,702,445
Dollar Change in sales 2019 to 2020: +$115,886
Percent Change in sales 2019 to 2020: +1.00%

This means that I would need to raise my forecast by $115,354 across 92 locations

What is the best way to distribute the $115,354 fairly across all 92 locations and the best way to do this in a formula in excel?

Appreciate any help you can provide!

(Windows 10 Pro, Office 365)

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1														Period $ Change	Period % Change
2					Period Total	11,586,579						11,587,090		512	0.00%
3	Weekly Subtotal	2,388,267	2,248,082	2,212,158	2,329,619	2,408,453		2,372,677	2,344,286	2,291,338	2,307,031	2,271,759		115,866	1.00%	11,702,445
4														115,354
5	Week Ending	1/8/2019	1/15/2019	1/22/2019	1/29/2019	2/5/2019		1/7/2020	1/14/2020	1/21/2020	1/28/2020	2/4/2020
6
7		P1W1	P1W2	P1W3	P1W4	P1W5		P1W1	P1W2	P1W3	P1W4	P1W5
8	Store_Num	Total Net Sales WTD	Total Net Sales WTD	Total Net Sales WTD	Total Net Sales WTD	Total Net Sales WTD		Total Net Sales WTD	Total Net Sales WTD	Total Net Sales WTD	Total Net Sales WTD	Total Net Sales WTD
9	6001	23307.1	19711.32	18721.71	20915.36	20852.3		20342.3094	17708.9165	21525.0994	20972.8614	20988.9583
10	6002	19289.63	14013.56	13670.45	16415.49	14924.92		17121.3979	14408.4069	16586.8535	17366.8461	17122.4549
11	6003	26350.22	22814.24	20506.84	24964.98	25644.98		28160.73	26445.3041	26741.6725	22662.0707	21082.49
12	6004	18357.8	15772.23	16944.87	18257.87	19619.81		17846.5497	15484.5381	18654.8218	18143.3343	18396.6408
13	6005	19162.42	15005.24	15267.78	17336.39	18421.19		18113.2928	19287.3147	18948.7407	18300.0356	17346.7812
14	6006	30017.97	25498.35	26161.25	28485.63	30189.3		25406.0682	25406.0682	28423.8025	23990.1941	24916.5099
15	6007	23462.68	22524.48	17704.36	20662.8	24215.73		23660.7748	23660.7748	25029.53	22144.8275	19146.4872
16	6008	33073.17	27137.83	30216.72	30279.7	31712.29		33416.1431	33416.1431	30327.7975	23884.6073	26021.835
17	6009	23660.04	22442.96	25441.55	28043.23	27881.39		25873.4452	26560.3488	28280.4429	29442.7934	28460.29
18	6010	29743.83	22766.06	22139.44	25437.63	30035.21		26371.6705	27921.1313	29661.7556	27603.4336	27407.8259
19	6011	21599.28	19281.18	19843.1	20988.8	20455.9		18560.2147	16949.5793	17952.5472	18393.3956	17845.8451
20	6012	14600.92	11388.38	12722.69	13643.31	14548.95		14107.4122	12580.4383	10834.962	12381.5964	12532.0485
21	6013	28025.68	25027.8	23630.08	26714.62	27391.91		26094.6985	26094.6985	25623.8625	22355.352	21475.0974
22	6014	20035.25	18796.68	17137.59	18775.44	19499.23		19977.0593	19977.0593	19768.265	17866.6851	17136.3401
23	6015	31436.46	25150.36	25754.54	27917.56	27319.97		35345.0346	35345.0346	30883.795	24633.9549	24918.7412
24	6017	26259.35	23628.25	22126.8	24038.16	25452.11		24478.0844	21522.3566	23864.9317	24745.4886	24527.1256
25	6018	27043.38	24071.12	25959.15	27114.98	28163.14		26227.7614	24036.7058	26078.9506	26462.7876	26696.946
26	6019	15944.05	13965.25	13980.73	15532.36	16027.03		15540.6763	15540.6763	14091.495	14133.5963	13006.2598
27	6021	28210.97	23378.14	24129.61	25831.29	25960.44		27015.7604	23420.9839	25158.6226	26414.6149	25694.6451
28	6022	25364.97	23400.21	22380.95	26094.93	24862.6		22978.1597	21211.8406	25047.186	24187.8924	23746.5293
29	6023	23241.87	19150.08	19511.77	21040.38	20718.43		22673.9904	19797.1913	22691.7449	21612.9073	21789.1874
30	6024	26275.05	23232.33	22823.12	24196.46	25220.02		25916.0535	25862.4635	26837.2683	26378.6819	23797.1073
31	6025	16654.4	16606.33	16238.04	17218.49	17530.94		16222.1661	14410.6994	17187.4121	17650.9345	16510.5476
32	6026	22471.6	16042.59	17649.92	19254.46	21435.26		23280.0728	23743.4219	22891.065	24253.5225	24228.3758
33	6027	23328.91	17070.95	18710.31	21783.61	21983.96		24150.2575	20457.9893	19957.1975	22597.7284	22875.6225
34	6028	20519.88	17287.43	17358.75	17892.15	19141.59		18042.0385	18307.0461	19251.4621	19288.9645	16603.7008
35	6029	22212.83	18942.54	20326.63	21369.87	21891.78		21747.9111	20085.1915	22195.5183	21582.6998	21329.5131
36	6031	22523.86	19788.18	11820.02	21546.83	20783.27		21322.4508	18423.8259	19439.2623	21393.7312	20415.01
37	6032	19719.95	16291.66	18012.94	18069.66	19306.6		23215.89	23215.89	22423.98	19424.795	11132.8274
38	6033	31037.97	28336.27	26934.88	29060.5	29298.84		28517.0388	25628.0563	28232.0345	28229.8572	26732.7125
39	6034	23167.23	20112.59	19338.23	21844.32	23052.95		28171.6467	28171.6467	23960.5075	20532.2395	20043.7887
40	6035	17380.46	14660.68	15082.65	17306.13	17549.2		18934.8881	17622.8645	18602.3775	18674.0128	18717.0913
41	6036	29754.85	26617.85	26827.62	28858.24	29409.32		27624.7344	24600.4366	27306.1057	28444.209	26864.2914
42	6037	32466.83	27200.57	29044.57	30752.5	32204.91		29743.8771	27581.8052	30749.2225	30623.9168	30423.2492
43	6038	23126.87	20096.38	20907.67	21061.87	22786.85		24268.0841	24268.0841	21449.8875	18954.1493	19658.5125
44	6050	26493.83	27123.35	27272.37	27157.64	29308.03		26459.9827	26595.6821	25158.8529	25229.2248	25859.6062
45	6051	21747.25	21345.05	20791.67	21056.77	21133.63		23347.4026	23347.4026	21008.005	20287.5125	20253.8475
46	6052	30323.62	28648.79	28665.88	28901.25	28211.4		28584.1139	30280.4598	28002.9974	28850.193	27337.7266
47	6053	26680.49	26908.92	26921.59	27593.35	25711.31		26361.5916	26833.7515	25648.5046	25414.2701	24391.6721
48	6054	31809.39	32224.15	30799.53	32904.17	33099.43		31093.1173	30578.2879	28106.2143	29451.0997	29553.0929
49	6055	29168.86	28621.64	28156.83	28526.54	30645.2		29411.1902	30172.8017	26572.1984	28608.0125	28903.9314
50	6057	22752.46	22349.61	21510.52	21407.33	21666.45		21146.8956	20229.2072	19676.9031	20450.6013	19738.4136
51	6058	21855.5	21654.11	22259.21	22007.95	21891.76		20403.9317	21412.7689	20100.3493	21160.0704	20808.6321
52	6059	22534.59	23134.44	21798.42	22736.4	22842.3		21501.7168	21501.7168	21574.3976	22588.56	21245.6739
53	6060	19130.06	17837.39	18708.91	18443.63	19756.2		16390.1427	15870.3618	15789.6144	16402.2408	15793.7036
54	6061	35056.54	34419.52	33319.73	33751.84	33692.66		33163.4144	33720.0387	30880.5295	32450.1221	33079.268
55	6062	28886.43	27157.92	26693.15	26770.67	28558.31		32092.7137	32092.7137	30404.475	28764.1075	28609.865
56	6063	25291.21	26487.55	23520.09	25660.95	25903.64		24283.9025	23938.8647	22730.8924	23909.2155	23718.9905
57	6064	26172.5	25863.72	24873.3	24651.79	26320.13		27876.381	27875.092	28652.8637	28857.0511	28734.6853
58	6065	25357.98	24717.23	24134.48	24700.86	25279.14		24824.4517	24405.4897	23663.5546	24193.0162	25000.7345
59	6066	27414.59	25864.45	26062.7	26208.6	26078.56		27808.9167	26792.6832	26658.0883	26443.748	26658.5624
60	6067	37513.23	35988.42	34485.5	34872.18	36934.46		36861.7316	35898.623	35423.5289	36017.4789	37226.9805
61	6068	31035.12	30319.19	29410	30716.24	29696.49		28937.1355	29889.5761	29115.5573	29407.1496	29730.1044
62	6069	22009.5	23185.84	22804.95	23727.35	24369.69		22391.9677	22007.301	20575.2774	21739.114	21983.4582
63	6071	39678.72	38725.47	38768.78	39398.14	40628.55		38194.9484	38403.5583	38140.7658	39063.4131	38554.8659
64	6072	35493.29	34900.25	33971.36	35366.03	36424.55		35762.3735	36500.5251	34877.0359	36759.6746	36433.1284
65	6073	21338.51	21292.52	21592.92	20122.65	22678.78		19916.9364	18987.2312	18718.9421	19339.0702	19785.5109
66	6074	32025.13	32692.77	31837.36	32317.24	34409.59		33964.7225	33964.7225	29864.73	30500.255	30428.45
67	6075	20219.45	18920.2	16563.31	20571.68	19068.14		17569.3895	19623.8433	18978.83	18819.1063	18149.4118
68	6076	26824.45	30724.12	31470.97	30573.08	33503		26269.4431	28804.2463	29393.8816	30094.5902	29986.6542
69	6077	35076.4	34921.67	33983.19	33171.31	35337.37		34054.7317	34741.2644	32534.4518	34725.3038	34686.3201
70	6078	22742.44	22590.05	20905.29	22710.79	22428.51		21041.7723	21000.8013	21186.7216	22370.3862	21883.7834
71	6079	29305.11	29417.94	27850.25	29750.07	29899.85		27481.2164	26788.2721	28965.875	30089.2185	31391.2416
72	6081	30462.67	33185.3	32232.13	33896.79	37517.52		40244.6475	39625.3494	38573.5923	37856.6648	38523.0697
73	6082	29967.58	32010.4	29892.15	32416.64	31650.42		29744.3027	30533.6561	29103.7342	30997.8969	30237.5455
74	6083	25998.81	24372.67	24682.88	24339.71	26047.66		23432.4983	22711.4337	22884.2923	23294.5508	23534.585
75	6084	19970.93	19263.42	19266.24	19709.77	20145.08		19317.6172	19595.7053	18505.8858	18434.6254	17707.9117
76	6086	26617.18	28792.75	26255.83	27167.68	28403.7		26008.3418	26437.3274	26379.2212	27014.7084	27404.8869
77	6087	27962.45	29125.43	27941.31	29009.47	29207.17		28167.1024	26872.5836	26689.7062	27640.3673	28198.586
78	6088	18240.25	18846.67	18460.28	18958.52	19558.34		19024.1981	18244.8942	18261.9523	19043.8131	18042.4677
79	6089	31892.56	31376.77	30795.16	31541.96	32000.1		31942.2903	30358.1583	29747.4514	30411.3311	31211.5066
80	6090	30991.29	29096.61	27618.07	29735.43	28560.61		29965.0956	28544.0138	28235.1428	29612.3136	29418.5009
81	6091	25497.92	25808.67	23398.83	24852.04	25827.08		30304.9855	30222.012	30365.8477	30257.9607	31497.6979
82	6092	12275.44	12773.44	12362.72	17287.84	15838.27		14343.8273	14343.8273	14332.9439	14093.257	16838.5346
83	6093	26765.75	26307.44	24899.4	26080.03	26414.01		26060.492	26362.7724	26079.4803	26849.2719	25923.5841
84	6094	30939.68	33455.85	31901.12	32860.93	33823.37		30227.1597	31521.4684	29362.4913	30945.4521	30864.7824
85	6095	28840.47	30818.42	27551.95	29280.36	29673.11		29889.2808	29638.1721	28610.6782	29694.9704	30165.9958
86	6096	21772.1	21975.41	20838.5	22261.07	22338.19		22478.2183	22102.0075	21582.8586	21575.0917	21481.3072
87	6097	27906.53	28049.99	27188.69	28406.12	27855.8		29179.0944	27677.4781	27165.3671	27272.3304	27487.5386
88	6098	22536.49	22616.72	21628.87	21405.98	22825.92		27455.0476	27455.0476	22897.11	23044.13	22029.09
89	6099	27037.28	25962.29	26768.22	25589.87	26826.12		26563.4115	26340.5795	24768.5769	24158.7709	24749.5523
90	6100	29529.92	29826.07	28264.02	28230.2	31260.25		30028.0467	29659.3928	28394.1938	28854.0419	29061.8597
91	6101	38149.88	39838.2	38431.07	38910.88	41040.79		40732.0205	42015.2407	40546.4703	41473.0708	42047.3156
92	6103	18747.67	20113.63	19315.71	20131.29	22400.12		23938.776	23938.776	19677.7688	21593.0325	20674.6724
93	6104	16164.49	16877.74	15279.11	17364.07	17172.88		14652.8492	16009.5451	13592.8243	14620.9913	15581.4165
94	6105	22107.84	22829.54	20914.33	21758.58	22780.16		23615.1796	23615.1796	21811.785	22045.7475	20165.3643
95	6106	19023.44	18391.7	17414.97	18295.4	19540.64		24533.7607	24533.7607	20076.2516	19601.5079	18005.3603
96	6200	30566.1	28188.99	28950.61	27956.92	30725.52		32964.3607	32964.3607	29836.105	27892.365	27941.1275
97	6201	35368.42	31001.6	33810.41	33712.13	37087.36		29048.6693	33185.3662	27701.8991	33028.0275	33562.5737
98	6202	41735.09	34362.47	37262.14	38948.34	42071.86		34933.0826	40391.0474	36529.2519	40010.1014	39059.3684
99	6203	33901.71	27199.15	32107.61	31746.49	34618.21		37239.7094	35535.7397	29774.9739	35003.5097	28188.9174
100	6204	24532.41	20368.24	22566.51	23281.94	24273.01		18978.4055	22438.489	20785.8558	22998.7442	22633.9122

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F2 =SUM(B3:F3) L2 =SUM(H3:L3) B3 =SUBTOTAL(9,B9:B111) C3 =SUBTOTAL(9,C9:C111) D3 =SUBTOTAL(9,D9:D111) E3 =SUBTOTAL(9,E9:E111) F3 =SUBTOTAL(9,F9:F111) H3 =SUBTOTAL(9,H9:H111) I3 =SUBTOTAL(9,I9:I111) J3 =SUBTOTAL(9,J9:J111) K3 =SUBTOTAL(9,K9:K111) L3 =SUBTOTAL(9,L9:L111) P3 =F2*(1+O3) N2 =L2-F2 O2 =N2/F2 N3 =P3-F2 N4 =N3-N2 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Rijnsent]

Hi Tblackwell,
if you want to raise total by 1%, you can raise all the underlying values by 1%. So basically add 1% to all shops. Is that what you are looking for?
Cheers,
Koen

 

[tblackwell]

Hi Tblackwell,
if you want to raise total by 1%, you can raise all the underlying values by 1%. So basically add 1% to all shops. Is that what you are looking for?
Cheers,
Koen

Koen - No because I want to evenly distribute the $115,354 across 92 locations. Each location has a different volume, so 1% evenly spread would not equal $115,354.

 

[igold]

I don't have the formula for you but the best way in my opinion is to use a weighted average. Meaning figure out the percentage that each of the 92 locations contributed to the 11,586,579 and distribute the new forecast back to the 92 locations by their respective percentage contribution to the original.

 

[Rijnsent]

Hi tblackwell,
so you want to add 1/92nd of that $115354 to every location? That does mean that locations with a lower sales e.g. row 65 have to grow much more vs e.g. row row 84. If you want that you could add 1/92nd (divided by 5 weeks) to every week. That feels a bit unfair to the lower selling units, but I don't know your business.
Koen

 

[igold]

Hi Rijnsent,

If you are replying to what I suggested then you missed the jist of it. For example, Store 6001 in the first week contributed 23307.01 to the 11,586,579 that is equal to 0.2012%. Multiply the proposed increase by that percentage to figure how much of the increase should be placed upon that store for that corresponding future period.