Some help creating a tracker...

[BarneyLTD]

Hi all, hope someone can help here.

I supply a retail chain with stock and receive data from a sales cube.
I want to look at sales for the past 4 weeks and if they are in decline or don't meet a target I would like excel to trigger a 'Visit' so I can go to/contact the store to see whats up.

Is there anything out there I can adjust to do this or can anyone help me put something together?
I'm quite the novice with excel but learn quick.

A weekly breakdown of 10 stores is below.

Sale Qty	Column Labels
Row Labels	2019/01	2019/02	2019/03	2019/04	2019/05	2019/06	2019/07	2019/08	2019/09	Grand Total
SKU CAT	120221	108986	111636	121503	122547	115257	113186	103477	42344	959157
STORE 1	78	31	54	81	46	54	72	46	21	483
STORE 2	107	116	70	93	106	93	111	126	57	879
STORE 3	47	44	49	42	55	70	52	66	21	446
STORE 4	80	130	118	99	138	120	100	58	48	891
STORE 5	127	173	131	127	112	129	113	96	54	1062
STORE 6	165	129	85	123	102	129	53	71	18	875
STORE 7	150	131	100	186	133	123	84	87	48	1042
STORE 8	342	343	310	262	221	242	186	206	64	2176
STORE 9	221	145	115	128	112	101	153	116		1091
STORE 10	243	180	182	171	168	244	153	165	48	1554

<colgroup><col span="10"><col></colgroup><tbody>
</tbody>

 

[oldbrewer]

it is difficult statistically to prove a genuine trend - eg if current month is 99 and previous three were 88,99,110

even worse if there is any seasonality in the data

however managers like traffic lights flashing red and green so you are in a difficult position - a formula to compare june with the average of march april may would be (data in row 5 so in J6)

=if((sum(g5:I5)/3)>J5,"red","green")