Here is my interpretation of using Animal Segmentation to maintain a portfolio of products according to their trading behaviour, i.e. a Jack Rabbit, low volume, high volatility is up and down and exhibits erratic behaviour, whereas a Horse exhibits high volume and low volatility, a good runner - it is a fun and a little more memorable than using ABCD XYZ Analysis. All that is required is the last 12 Months of Actuals by Product and the formula for Total Actuals (SUM, Sorted Descending), Volatility (COV, the Coefficient of Variation), Share (Percentage of the Total Actuals divided by the SUM of all Actuals), Volume (Portfolio Share which is a cumulative SUM of the Share) and Animal Segmentation (a logical Decision Matrix). This is an example by Product but by adding one-to-one additional data like Category and Pack Type, you can then slice your Portfolio any way that you like to see the Animal Segmentation at that level. You should Segment for the Portfolio that you Plan i.e. ALE, LAGER, CIDER, GAS, SNACKS, SUNDRIES, Large Pack, Small Pack, CASK, KEG, Bottle, CAN etc. but if reporting for a Business Sector or an entire Business Unit you should Segment at that level by SKU

Here are the Animals I will consider in the order of my decision matrix with some context Back · Purchase

Mammoth: No Sales in the last 4 Months - can include phased out products
Kangeroo: Intermittent Monthly Sales - unpredictable
Puppy: Sales history shorter than 12 Months - can be classed as NPD's (New Products under Development)
Horse: High Volume (up to 80% share), Low Volatility (<70%) - speed and endurance
Mad Bull: High Volume (up to 80% share), High Volatility (>70%) - unpredictive actions
Mule: Low Volume (last 20% of share), Low Volatility (<0%) - slow and predictable
Jack Rabbit: Low Volume (up to 80% share), High Volatility (>70%) - up and down, erratic

Here are my Actuals by Product for the last 12 Months - I have chosen Products from a Category that exhibit all of the Animal behaviours mentioned previously. You can do a rolling year i.e. Feb to Jan but for brevity I have gone with Jan to Dec. Highlight and Copy & Paste all of the values below into an Excel Worksheet into Cells C16:Q23

Product	Description	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
83871	Product P-0835	303	2364	256	1110	281	2188	1101	306	2249	556	558	2497
82556	Product J-0827	800	712	746	851	716	733	874	750	824	400	860	753
84958	Product Z-0845	0	0	0	349	256	300	254	484	248	673	436	379
85670	Product Z-0855	35	139	224	160	121	166	112	226	174	252	258	257
91691	Product C-0919	3	21	4	22	5	21	6	22	2	21	2	28
94650	Product Q-0949	2	0	12	0	3	0	11	0	2	0	12	0
95553	Product U-0957	0	0	0	0	0	2	1	1	0	0	0	0

I start by calculating the Actuals Total. In Cell Q16 add the Header TOTAL. Now copy the Formula below into Cell Q17 and then extend down the Range until Cell Q23

=SUM($E17:$P17)

Then I calculate the Volatility. For this I will use the Coefficient of Variation. This is calculated by Standard Deviation divided by the Mean (Average). In Cell R16 add the Header Volatility. Now copy the Formula below into Cell R17 and then extend down the Range until Cell R23. Change the formatting to percentage and 1 decimal place

=IFERROR(STDEVP($E17:$P17)/AVERAGE($E17:$P17),0)

Now I calculate the initial Share of the Volume. This is simply the Volume by Product divided by the Total Volume of all of the Products. I will use a SUBTOTAL Function that can be used in Tables. I will use an arbitrary Row of 23 to cover the Range - you will need to extend this if you have more Products. In Cell S16 add the Header Volume Share. Now copy the Formula below into Cell S17 and then extend down the Range until Cell S23. Change the formatting to percentage and 1 decimal place

=IFERROR($Q17/SUBTOTAL(9,$Q$17:$Q$23),0)

Next I calculate the cumulative Share or Portfolio Share that we will call the Volume. I will use an AGGREGATE() Function that can be used in Tables. In Cell T16 add the Header Volume. Now copy the Formula below into Cell T17 and then extend down the Range until Cell T23. Change the formatting to percentage and 1 decimal place

=AGGREGATE(9,5,$S$17:$S17)

In Cells C13 and C14 enter 0.8 and 0.7. These are the Volume and Volatility Settings - you can use Named Ranges if you prefer but if you do, then you will need to adapt the Formula

Okay so now we have everything that we need to determine the Animal Segmentation apart from a Decision Matrix Formula. In Cell U16 add the Header Animal Segmentation. Now copy the Formula below into Cell T17 and then extend down the Range until Cell T23. Change the formatting to percentage and 1 decimal place

=IF(SUM($O17:$R17)<=0,"Mammoth",
 IF(AND($G17>0,COUNTIF($G17:$R17,">0")<=10),"Kangeroo",
 IF(COUNTIF($G17:$R17,">0")<12,"Puppy",
 IF(AND($V17<$C$13,$T17>$C$14),"Mad Bull",
 IF(OR(AND($V17<$C$13,$T17<$C$14),AGGREGATE(3,5,$C$17:C17)=1),"Horse",
 IF(AND($V17>$C$13,$T17<$C$14),"Mule",
 IF(AND($V17>$C$13,$T17>$C$14),"Jack Rabbit","Unkown")))))))

Chart demonstrating four of the Animal Segmentation Trading Shapes across the Year

Chart demonstrating the other three Animal Segmentation Trading Shapes across the Year

Conclusion: It may be worth setting any Products that are Out-phasing or Closed as Mammoth directly before running down the Decision Matrix so simply add in another IF Statement as a first decision in the Animal Segmentation Formula

---