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

tblackwell

New Member
Joined
Oct 24, 2018
Messages
20
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)


ABCDEFGHIJKLMNOP
1Period $ ChangePeriod % Change
2Period Total11,586,57911,587,0905120.00%
3Weekly Subtotal2,388,2672,248,0822,212,1582,329,6192,408,4532,372,6772,344,2862,291,3382,307,0312,271,759115,8661.00% 11,702,445
4115,354
5Week Ending1/8/20191/15/20191/22/20191/29/20192/5/20191/7/20201/14/20201/21/20201/28/20202/4/2020
6
7P1W1P1W2P1W3P1W4P1W5P1W1P1W2P1W3P1W4P1W5
8Store_NumTotal Net Sales WTDTotal Net Sales WTDTotal Net Sales WTDTotal Net Sales WTDTotal Net Sales WTDTotal Net Sales WTDTotal Net Sales WTDTotal Net Sales WTDTotal Net Sales WTDTotal Net Sales WTD
9600123307.119711.3218721.7120915.3620852.320342.309417708.916521525.099420972.861420988.9583
10600219289.6314013.5613670.4516415.4914924.9217121.397914408.406916586.853517366.846117122.4549
11600326350.2222814.2420506.8424964.9825644.9828160.7326445.304126741.672522662.070721082.49
12600418357.815772.2316944.8718257.8719619.8117846.549715484.538118654.821818143.334318396.6408
13600519162.4215005.2415267.7817336.3918421.1918113.292819287.314718948.740718300.035617346.7812
14600630017.9725498.3526161.2528485.6330189.325406.068225406.068228423.802523990.194124916.5099
15600723462.6822524.4817704.3620662.824215.7323660.774823660.774825029.5322144.827519146.4872
16600833073.1727137.8330216.7230279.731712.2933416.143133416.143130327.797523884.607326021.835
17600923660.0422442.9625441.5528043.2327881.3925873.445226560.348828280.442929442.793428460.29
18601029743.8322766.0622139.4425437.6330035.2126371.670527921.131329661.755627603.433627407.8259
19601121599.2819281.1819843.120988.820455.918560.214716949.579317952.547218393.395617845.8451
20601214600.9211388.3812722.6913643.3114548.9514107.412212580.438310834.96212381.596412532.0485
21601328025.6825027.823630.0826714.6227391.9126094.698526094.698525623.862522355.35221475.0974
22601420035.2518796.6817137.5918775.4419499.2319977.059319977.059319768.26517866.685117136.3401
23601531436.4625150.3625754.5427917.5627319.9735345.034635345.034630883.79524633.954924918.7412
24601726259.3523628.2522126.824038.1625452.1124478.084421522.356623864.931724745.488624527.1256
25601827043.3824071.1225959.1527114.9828163.1426227.761424036.705826078.950626462.787626696.946
26601915944.0513965.2513980.7315532.3616027.0315540.676315540.676314091.49514133.596313006.2598
27602128210.9723378.1424129.6125831.2925960.4427015.760423420.983925158.622626414.614925694.6451
28602225364.9723400.2122380.9526094.9324862.622978.159721211.840625047.18624187.892423746.5293
29602323241.8719150.0819511.7721040.3820718.4322673.990419797.191322691.744921612.907321789.1874
30602426275.0523232.3322823.1224196.4625220.0225916.053525862.463526837.268326378.681923797.1073
31602516654.416606.3316238.0417218.4917530.9416222.166114410.699417187.412117650.934516510.5476
32602622471.616042.5917649.9219254.4621435.2623280.072823743.421922891.06524253.522524228.3758
33602723328.9117070.9518710.3121783.6121983.9624150.257520457.989319957.197522597.728422875.6225
34602820519.8817287.4317358.7517892.1519141.5918042.038518307.046119251.462119288.964516603.7008
35602922212.8318942.5420326.6321369.8721891.7821747.911120085.191522195.518321582.699821329.5131
36603122523.8619788.1811820.0221546.8320783.2721322.450818423.825919439.262321393.731220415.01
37603219719.9516291.6618012.9418069.6619306.623215.8923215.8922423.9819424.79511132.8274
38603331037.9728336.2726934.8829060.529298.8428517.038825628.056328232.034528229.857226732.7125
39603423167.2320112.5919338.2321844.3223052.9528171.646728171.646723960.507520532.239520043.7887
40603517380.4614660.6815082.6517306.1317549.218934.888117622.864518602.377518674.012818717.0913
41603629754.8526617.8526827.6228858.2429409.3227624.734424600.436627306.105728444.20926864.2914
42603732466.8327200.5729044.5730752.532204.9129743.877127581.805230749.222530623.916830423.2492
43603823126.8720096.3820907.6721061.8722786.8524268.084124268.084121449.887518954.149319658.5125
44605026493.8327123.3527272.3727157.6429308.0326459.982726595.682125158.852925229.224825859.6062
45605121747.2521345.0520791.6721056.7721133.6323347.402623347.402621008.00520287.512520253.8475
46605230323.6228648.7928665.8828901.2528211.428584.113930280.459828002.997428850.19327337.7266
47605326680.4926908.9226921.5927593.3525711.3126361.591626833.751525648.504625414.270124391.6721
48605431809.3932224.1530799.5332904.1733099.4331093.117330578.287928106.214329451.099729553.0929
49605529168.8628621.6428156.8328526.5430645.229411.190230172.801726572.198428608.012528903.9314
50605722752.4622349.6121510.5221407.3321666.4521146.895620229.207219676.903120450.601319738.4136
51605821855.521654.1122259.2122007.9521891.7620403.931721412.768920100.349321160.070420808.6321
52605922534.5923134.4421798.4222736.422842.321501.716821501.716821574.397622588.5621245.6739
53606019130.0617837.3918708.9118443.6319756.216390.142715870.361815789.614416402.240815793.7036
54606135056.5434419.5233319.7333751.8433692.6633163.414433720.038730880.529532450.122133079.268
55606228886.4327157.9226693.1526770.6728558.3132092.713732092.713730404.47528764.107528609.865
56606325291.2126487.5523520.0925660.9525903.6424283.902523938.864722730.892423909.215523718.9905
57606426172.525863.7224873.324651.7926320.1327876.38127875.09228652.863728857.051128734.6853
58606525357.9824717.2324134.4824700.8625279.1424824.451724405.489723663.554624193.016225000.7345
59606627414.5925864.4526062.726208.626078.5627808.916726792.683226658.088326443.74826658.5624
60606737513.2335988.4234485.534872.1836934.4636861.731635898.62335423.528936017.478937226.9805
61606831035.1230319.192941030716.2429696.4928937.135529889.576129115.557329407.149629730.1044
62606922009.523185.8422804.9523727.3524369.6922391.967722007.30120575.277421739.11421983.4582
63607139678.7238725.4738768.7839398.1440628.5538194.948438403.558338140.765839063.413138554.8659
64607235493.2934900.2533971.3635366.0336424.5535762.373536500.525134877.035936759.674636433.1284
65607321338.5121292.5221592.9220122.6522678.7819916.936418987.231218718.942119339.070219785.5109
66607432025.1332692.7731837.3632317.2434409.5933964.722533964.722529864.7330500.25530428.45
67607520219.4518920.216563.3120571.6819068.1417569.389519623.843318978.8318819.106318149.4118
68607626824.4530724.1231470.9730573.083350326269.443128804.246329393.881630094.590229986.6542
69607735076.434921.6733983.1933171.3135337.3734054.731734741.264432534.451834725.303834686.3201
70607822742.4422590.0520905.2922710.7922428.5121041.772321000.801321186.721622370.386221883.7834
71607929305.1129417.9427850.2529750.0729899.8527481.216426788.272128965.87530089.218531391.2416
72608130462.6733185.332232.1333896.7937517.5240244.647539625.349438573.592337856.664838523.0697
73608229967.5832010.429892.1532416.6431650.4229744.302730533.656129103.734230997.896930237.5455
74608325998.8124372.6724682.8824339.7126047.6623432.498322711.433722884.292323294.550823534.585
75608419970.9319263.4219266.2419709.7720145.0819317.617219595.705318505.885818434.625417707.9117
76608626617.1828792.7526255.8327167.6828403.726008.341826437.327426379.221227014.708427404.8869
77608727962.4529125.4327941.3129009.4729207.1728167.102426872.583626689.706227640.367328198.586
78608818240.2518846.6718460.2818958.5219558.3419024.198118244.894218261.952319043.813118042.4677
79608931892.5631376.7730795.1631541.9632000.131942.290330358.158329747.451430411.331131211.5066
80609030991.2929096.6127618.0729735.4328560.6129965.095628544.013828235.142829612.313629418.5009
81609125497.9225808.6723398.8324852.0425827.0830304.985530222.01230365.847730257.960731497.6979
82609212275.4412773.4412362.7217287.8415838.2714343.827314343.827314332.943914093.25716838.5346
83609326765.7526307.4424899.426080.0326414.0126060.49226362.772426079.480326849.271925923.5841
84609430939.6833455.8531901.1232860.9333823.3730227.159731521.468429362.491330945.452130864.7824
85609528840.4730818.4227551.9529280.3629673.1129889.280829638.172128610.678229694.970430165.9958
86609621772.121975.4120838.522261.0722338.1922478.218322102.007521582.858621575.091721481.3072
87609727906.5328049.9927188.6928406.1227855.829179.094427677.478127165.367127272.330427487.5386
88609822536.4922616.7221628.8721405.9822825.9227455.047627455.047622897.1123044.1322029.09
89609927037.2825962.2926768.2225589.8726826.1226563.411526340.579524768.576924158.770924749.5523
90610029529.9229826.0728264.0228230.231260.2530028.046729659.392828394.193828854.041929061.8597
91610138149.8839838.238431.0738910.8841040.7940732.020542015.240740546.470341473.070842047.3156
92610318747.6720113.6319315.7120131.2922400.1223938.77623938.77619677.768821593.032520674.6724
93610416164.4916877.7415279.1117364.0717172.8814652.849216009.545113592.824314620.991315581.4165
94610522107.8422829.5420914.3321758.5822780.1623615.179623615.179621811.78522045.747520165.3643
95610619023.4418391.717414.9718295.419540.6424533.760724533.760720076.251619601.507918005.3603
96620030566.128188.9928950.6127956.9230725.5232964.360732964.360729836.10527892.36527941.1275
97620135368.4231001.633810.4133712.1337087.3629048.669333185.366227701.899133028.027533562.5737
98620241735.0934362.4737262.1438948.3442071.8634933.082640391.047436529.251940010.101439059.3684
99620333901.7127199.1532107.6131746.4934618.2137239.709435535.739729774.973935003.509728188.9174
100620424532.4120368.2422566.5123281.9424273.0118978.405522438.48920785.855822998.744222633.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
CellFormula
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

Well-known Member
Joined
Oct 17, 2005
Messages
1,149
Office Version
365
Platform
Windows
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

New Member
Joined
Oct 24, 2018
Messages
20
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

Well-known Member
Joined
Jul 8, 2014
Messages
2,447
Office Version
365, 2010
Platform
Windows
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

Well-known Member
Joined
Oct 17, 2005
Messages
1,149
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Jul 8, 2014
Messages
2,447
Office Version
365, 2010
Platform
Windows
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.
 

Forum statistics

Threads
1,081,526
Messages
5,359,287
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top