tblackwell
New Member
- Joined
- Oct 24, 2018
- Messages
- 28
- Office Version
- 365
- Platform
- Windows
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)
<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>
<tbody>
</tbody>
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
<thead> </thead><tbody> </tbody> |
<tbody>
</tbody>