(This entry is being submitted for Summer Saga 2014 contest conducted by InsideIIM)
The Introduction:
Summer is a tough time in India, especially for fieldwork. I was doubly lucky in this regard to have an internship in a Financial role and then to have it in my home city. It was an intense spreadsheet job in an air-conditioned office cubicle (with a telephone at desk also!). Why did I put up a chemical analysis image then ? Of course there is a relationship: both chemical and financial analysis need Formulas! Besides chemicals and research are core to a giant Pharmaceutical firm. I am sure you will find the narrative very helpful. Also on my way I will use arbitrary variables such as
x, y, z, etc. where I feel actual value/s should not be disclosed. With this clarification,
lemme begin
my story !
The Disclaimer:
Resemblance of any data/values in this write-up to any number: real or imaginary, any customer: alive or dead OR any product: existing or potential is purely co-incidental.
Author does not bear any risk arising out of believing the data to be true. The sole purpose of constructing it is to facilitate understanding of analysis at a conceptual level.
The Narrative:
Merck is not just the oldest pharmaceutical company in the world but one of the biggest too even at the present date. So, financial data on just a small part of its entire business that too of a small portion of time in its long history is sufficient to keep an intern well-occupied over a stretch of entire 2 months. From its worldwide business, I just focused on
CHE business of its India operations. The financial analysis was performed for all the 7 divisions of this business for the two years: 2012 and 2013.
The data for analysis was derived from the invoices of the 3 legal entities of Merck operating in India:
ABC,
DEF and the recent acquisition
ACQ. This data was first combined and then segregated into different divisions. It must be noted that while combining the data from different sources their compatibility and uniformity must be ensured. Such integration is especially important for an acquired entity to bring all on a common platform. There is a significant amount of re-engineering of business processes, information systems which is required to achieve this match. While this process is underway or until it’s complete it’s difficult to perform analysis in same terms. For this I made certain reasonable assumptions for the missing or conceptually mismatching data and went ahead.
The analysis was performed for each division separately, for both years. Since every division has many customers, my analysis lends itself to only top 50 customers by contribution. This handful chunk contributed more than the threshold
x% of the total of most divisions and so it was worth to maintain focus on them.
So first, top customers were identified for each division and then Top 25 products were identified for each of the top customers. Depending on the division the number of top customers chosen to examine products also, varied. Thus the profitability analysis is division-wise, customer-wise and product-wise: top products for each top customer for each division.
Some customers give very good margins but are still under-ranked because of lower sales. They will rise in ranking if higher sales are made to them while maintaining the margin percentage, increasing our total contribution (which is a primary goal in profitability improvement).
Yet on the other side of spectrum are customers who give loads of sales but very low margin relatively. For such customers the challenge is to increase contribution per unit sales while maintaining the high sales level.
There are some customers who appear regularly in top 50. Their purchasing patterns could help us customize product mix for them and optimize sales strategy. Also we could unearth the factors that keep them in top, through analysis. Some customers are new entrants in top 50 club while some drop out of it. Such customers may have cyclic or one time surges in demand, and may be paid less attention.
Some customers demand a varied portfolio of products while some purchase very high quantities of just one-two products. The latter types typically have a very high share in such product’s total sales.
Now coming to discounts, their basic purpose is to push sales. But too high a discount can eat into your margins. Ideally sales would grow at the average demand growth rate. So any sales above that can be attributed to stimulation provided by discounts. Similarly, such additional sales should not reduce margin. This is an important assumption that I have used to evaluate if discounts are really doing good.
It’s on such observations that I based my analysis. These can help to decide future course of action for different customers. For example customers appearing frequently in the top can be offered attractive credit terms. Customers featuring sporadically in the top can be offered frequency discounts. My modest analysis was designed to enable concerned authorities to take effective decisions.
An important achievement of this endeavour was the 300 page analysis report submitted to the company. This was split into 7 booklets, one for each division designed to serve some critical insights for the division heads. I broke up my entire project-work into following objectives:
1. Identifying top 50 customers providing maximum contribution margin to each of the 7 divisions of the
CHE business for both the years 2012 vs. 2013
2. For each selected top customer of each division, identifying their top 25 products
3. Understanding which customers and products are really profitable in terms of giving high margins in absolute terms as well as relative to sales value and in terms of other such parameters
4. Finding if products are being sold at a reasonable discount to the customers (Highlighting products sold at higher than average discount percentage for that product)
5. Finding if products sold to customers are providing a reasonable contribution margin (Highlighting products sold at lower than average contribution margin percentage for that product)
6. Determining if a customer’s margin is growing at a rate commensurate with sales growth rate (Designing a new parameter called
PPGF which measures this aspect in all cases)
7. Preparing division-, customer-, product- wise reports that will facilitate such and related analysis
8. Submitting insightful observations and recommendations for each division
Now explaining how I achieved each of the above would make the story very long and boring. So I will narrate to you just a couple interesting things, which will be followed by my takeaways before I wind up.
1) Analysis Part: The analysis provided for each division attempted to capture some pertinent categorized observations.
a) The general observations were typically of the following form:
(Note that accompanying data cannot be provided and this is the commentary following it just to expose you to the aspects that were looked for in general)
These observations are for the top 50 customers.
|
Sr.No.
|
Parameter
|
Count
|
| 1 |
Number of common customers appearing in both years |
26 |
| 2 |
Number of customers (in row 1 above) maintaining their rank (by CM) |
0 |
| 3 |
Number of customers (in row 1) improving their rank (by CM) |
14 |
| 4 |
Number of customers (in row 1) falling in rank (by CM) |
12 |
| 5 |
Number of profitable customers (from those in row 1) according to PPGF |
11 |
| 6 |
Number of customers present in only one of the two years |
24 |
(CM = Contribution Margin by value)
Customer26 is the star performer rising by whopping 20 ranks (by CM) from 39 in 2012 to 19 in 2013. On the other hand,
Customer12 is the worst performer falling by 26 notches from 13 to 39. Although the number of customers repeating in the top 50 is little above 50%, still this is low compared to other divisions. More the number of repeated customers, usually the better it is because there is better trust, understanding and familiarity with their needs and it’s easier to have tailor-made strategies directed toward them.
Recommendation: Repetition of customers should be encouraged at the top by giving top customers loyalty and frequency based incentives on sales.
b) Other observations:
These identified among other things:
i)Customers who were highly profitable, but with lower sales vale
ii)Customers who had very high sales but didn’t provide enough margins
iii) Discount percentages corresponding to customers who gave margins beyond the division’s Year-on-Year sales growth rate (such discounts are doing good because they are resulting in contribution margin growth rate which is in excess of sales growth rate.)
2) Design of the new parameter ‘PPGF’ (Proportionate Profitability Growth Factor):
The idea was to find whether contribution margin (CM) growth was commensurate with sales growth for any customer. So, initially it was thought that simple ratio of: CM growth percentage divided by sales growth percentage would provide the desired information. If this was higher than 1, it meant CM growth rate surpasses the sales growth rate which is good. But if sales shrank by say 10% and if CM shrank by 20% still the ratio = - 20% / -10% = +2 which is >1. And as anyone can see this of course is not good. So this ratio didn’t provide a single value against which we could compare and determine for sure that the value is good. I modified the ratio by including absolute (+ve) value function in different combinations for numerator and denominator, still with no luck with respect to a single guiding number. Then I devised a formula explained below that served our purpose superbly:
PPGF: Stands for Proportionate Profitability Growth Factor. Tells how large is the excess profitability as compared to the absolute (i.e. +ve) value of %change in Net Sales.
So first we define excess profitability as follows:
Excess profitability = (%Change in CM - %Change in Net Sales)
For e.g. Say Customer1 has % change in CM of 30% and % change in Net Sales of 10%. Then
Excess Profitability is simply 30% - 10% = 20 percent points.
Tells the extent by which CM growth rate exceeds Net Sales growth rate.
Thus,
PPGF = Excess Profitability / abs(%Change in Net Sales)
Basically following holds about its value:
| Condition |
Interpretation
|
| PPGF = 0 |
Neutral: CM grows/shrinks at same rate NS (CM follows NS exactly) |
| PPGF >0 |
Good: CM grows faster / shrinks slower than NS |
| PPGF <0 |
Bad: CM grows slower / shrinks faster than NS (values highlighted in light red) |
For eg. Say Customer1 has % change in CM of -30% and % change in Net Sales of -10%.
Then Excess Profitability is simply: (-30%) – (-10%) = -20 percent points.
(telling CM growth lags Net Sales growth by 20 percent points)
Then PPGF is: -20% /abs(-10%) = -20% /10% = -2 , telling CM growth rate
lag is 2 times compared to Net Sales growth rate.
Its
value is highlighted if it’s less than 0, telling CM growth is unfavourable.
The Learning:
Profitability Analysis is an
important exercise for any for-profit organization. Profitability of a firm can be analyzed along
many dimensions like contribution, its percentage to sales, discount percentages, contribution growth relative to sales growth, etc. It helps you
answer some important questions like: Are high-sales customers highly profitable too? Are discounts and such incentives having their desired impact? Are discounts in a reasonable range or are we doling out unnecessarily high discounts? What is the product mix of highly profitable customers? How much of the total are top
x customers contributing?
Now depending on the answers that you are seeking; you can
take actions that correct unfavourable observations (like margin growing at slower rate despite high sales growth rate) or that replicate some good phenomenon (like high proportion of customers repeating in top 50). Since analysis spans 7 divisions, there can be
comparative learning. Good things in one division should be attempted in other divisions and bad observations in a division should immediately put other divisions on an alert.
Thus to summarize, the analysis helps organization understand its current profitability state, find reasons behind some occurrences-of-interest and take adequate steps to reach desired strategic goal.
There are several other takeaways from this project work. I understood that proper
integration of information systems of acquired company with the acquiring company is extremely important and difficult also. This is because, before acquisition two operated as altogether different individuals; with different business processes (invoicing systems, pricing mechanisms, etc). So to achieve uniformity some business processes might need to be re-engineered, conceptual similarities need to be identified, data need to be mapped. Uniformity is necessary to analyze both on similar terms and to formulate a common strategy. Many problems may creep into system due to geographically diverse operations. For example orders from different plants of same customer company may be invoiced with different names by salespeople in different regions. But during the analysis all such names must be considered under a single head because they all belong to same customer. If this is not done, this customer even though he may be a topmost customer may be missed out because profits from him will be fragmented among tens of different names at different places. So such
customer grouping was a critical and manual task that I performed during data preparation.
Now some learnings regarding managerial aspects. I found that data,
knowledge and skills are
distributed in an organization. Typically individuals handle specific things and hence develop specialized knowledge and skills in their area of work. Because some decisions are inter-linked, managers require resources from different departments. So, it’s important to be aware of people possessing requisite resources and to be able to connect with them. This was exemplified when my mentor directed me to appropriate personnel to resolve my specific problems such as understanding discount calculation procedure, explore the possibility of having functions within an excel pivot table, etc. Finally these words by my mentor shall remain in my memory for quite some time. He said that qualifications can be acquired and it’s not difficult to find people with required qualification. It might be important to get in. But in the long run; it’s the fundamental qualities like hard-work, sincerity, interpersonal skills, adaptability, respect for individuals, awareness of struggle period and such that distinguish a person and truly successful. Even many guests working at top levels of reputed organizations who come to our college often stress the importance of ‘organizational behaviour’ knowledge at upper echelons of organizations.
The Credits:
Finally, every significant work is a result of many people contributing to it directly or indirectly. Just as ‘
Rome was not built in a day’; ‘
Rome was also not built without its citizens’ toil and support’. This sojourn was made possible, pleasant and worthwhile by many personnel from Merck sometimes even from different departments. I was fortunate to be given ample exposure including invaluable interactions with the CFO that set a proper direction to my voyage. My mentor was very supportive and went out of his way to ensure an extremely pleasant and enriching experience at Merck. I thank Merck Limited for giving me this wonderful opportunity to work in a professional environment, understand practical aspects of financial analysis and for the truly memorable experience.
!! Thank You, Adieu and All the Best !!