I am trying to solve this LFL this for a long time but not able to implement properly.
I need to calculate 2 LFL's:
1) LFL Same Week Last Year = (Curent Full Week Sales - Same Full Week Last Year Sales / Same Full Week Last Year Sales)
2) LFL Previous Week = (Current Full Week Sales - Previous Full Week Sales / Previous Full Week Sales)
But there is an important condition for this:
If for current full week, a sale is made by a particular restaurant, only that restaurants same full week last year sales should be taken into account.
Current Full Week Same Full Week Last Year
A $100 $34
B $34 $32
D $234 $400
Here C and E same full week last year shouldn't be considered as there are sales for current full week.
So the Current full week sales should be (100+34+234) = $368
Same full week last year sales should be (34+32+400) = $466
LFL (same full week last year) = (368-466)/ 466
I have attached sample data (In that data, store names AUBU and MOUNTY same week last year sales shouldn't be included).If you have any queries, plese feel free to ask.
Moreover, the important issue for me is to get a graph for year 2015 and 2016.
Please guys help me with this!
LFL LAT.xlsx 16.0 KB