As a variation in seasonality analysis (cfr. my post on Seasonal Index) I want to calculate if for a certain product, or product category, 80% or more of the Sales has happened in 20% or less of the year.
So I have my table with
SalesDate,
Product,
Product Category level 1
Product Category Level 2
Product Category Level 3
Product Category Level 4
Customer and
Amount(€)
for a range of products, spanning several years.
And a second table with Product and Launch Date.
Ideally, I can input the 80% and 20% as variables in my sheet, and then have Seasonal 0 or 1 as a filter. I can then drill down from Product Category Level 1 to Level 2 and so on to Product, and each time the Pareto is recalculated so that I can choose to only see Sales data from Product Categories or Products that comply with the Pareto criterium (80% of Sales of whatever is selected is sold in max. 20% of the year).
Special consideration should be taken with incomplete years (meaning: the year in which the Launch Date took place, and the current, running year). Ideally, these are barred from the calculation. But I'm not sure how to handle this in the Product Category dimension, since a Product Category will contain products with Launch Dates from different years.
Any and all help from you experts will be greatly appreciated!
You may want to pare down the request, and post some sample data and what you expect to achieve. As it sits now, this isn't a question to be answered but a whole job.
Yes, fair enough, sorry... Boiled it down to its essence in the simplified Excel attached.
Basically, I need to compare the sum of sales of consecutive sets of months (month 1 + 2 + 3, month 2 + 3 + 4, ... month 10 + 11 + 12) each with 80% of the year total. If in one of these comparisons the 3-month sum is greater than 80% of year total, then the product gets a flag "seasonal".
