Excel — Automating Trend Commenting
In the recent years, I came upon the existence of dedicated report analyst roles that perform monthly up-down commenting of numbers without additional insights. 😅 Disclaimer: despite the emojis peppering in my brain, i maintained a professional facade while introducing this a couple of years ago.😄
These headcounts will manually look at the chart, copy out the numbers they see visually … 😅😅😅
oh Sep’24 is 244.9k, looks like it’s increasing…
and then relook at Aug’24… oh it’s 204.0k…
and then Jul’24, it’s 295.3k…
and make commentaries in powerpoint that looks like this.
Product ABC improved to 244.9k in Sep’24 (295.3k → 204.0k → 244.9k)
And the whole process can take between a few hours to a couple of days depending on the number of charts (& lines). 😅
***
Fortunately, it need not always be a painstaking task done manually but rather it can be easily automated even in simple excel, power bi or tableau. At least, in other organizations more than 10–15 years ago back when I was still an entry level analyst, I had this automated dy haha. 😆 And i am pretty convinced that if i can think of these at that noob career stage, these manual tasks would have probably long been automated elsewhere too. 😄
***
For simplicity, will illustrate how this works in excel. Also, purely because excel would reach out to a wider audience.
***
To get started, we must firstly have a codified version of what the organization define as trend improvement (I will share an example of 3 months); and of course, a sample dataset.
***
[1] Next, setup a simple pivot that filters the last 4 months of data
***
[2] Now, we will move to the first step of compute, to find the differences between the months.
Differences (M0-M-1)
=J9-I9
*M0: Sep’24, M-1: Aug’24 and M-2: Jul’24.
***
[3] And compute the movement category using the differences, whether it’s improving, declining, early risk etc.
Movement (M0 vs M-1)
=IF(M9=0,”Stable”,IF(M9>0,”Improve”,IF(M9<0,”Decline”)))
***
[4] With that, we can now obtain the concatenated 3 Months movement trend.
3 Months Trend:
=P9&”|”&Q9&”|”&R9
***
[5] With the 3 Months Trend ready, we can now vlookup the details for Trend Category, Prepositions, and formulate the Commentaries.
=H9&” “&T9&” “&U9&” “&TEXT(L9,”#,##0.0,k”)&” in “&TEXT(L$8,”mmm’yy”)&” (“&TEXT(J9,”#,##0.0,k”)&” → “&TEXT(K9,”#,##0.0,k”)&” → “&TEXT(L9,”#,##0.0,k”)&”)”
***
***
It’s as simple as that. One need not always be proficient in SQL or even Tableau to add value. All it takes is always a little bit of imagination. And of course, exposure from different organizations, culture and people helps too. Don’t stay static in the same place.
Till next time. Tschüss! 👻
***
