Calibrating the impact of performance indicators

It is often said that between "Ordinary" and "Extraordinary" there is an 'Extra"! However in the pursuit of understanding that quintessential "Extra"; we need to tread a skillful journey that's often confusing!

Similarly, while weaving a strategic plan at the beginning of the financial year, we are often confused in allocating resources to extract that "Extra" juice..... rightly termed as "maximum ROI". Sometimes calculated, often a "Gut Instinct" and many a time through "Mark my word" chivalry; we fix the KPIs, invest resources, invest them as early as possible (before it gets frozen from the top); hoping to showcase much awaited "extraordinary outcomes". But, how to be sure about the budget allocation to the right channel and how much impact can we expect out of it! Yes, historical data plays a major role, but an ever-evolving business environment often becomes the invincible villain, staying a step ahead of us.

We need a solution and need it badly! Let's see, if we can create a model and feed in adequate elasticity to endure variations! I thought of giving it a try...

Imagine a situation, wherein, the funds (investment) need to be allocated to different spend verticals that will help to maximize ROI. Along with that, we will also feed in impact of price rise in the market (considering it is an important factor leading to customer shift to competition).

Let's assume the situation to be linear and depends on "Variables factors" like product price, spend on distribution, discounts offered, advertisements costs (ATL+BTL), awareness event costs , and competition price rise. We will consider a baseline sales i.e Sales volume that continues without any investment (Sales to loyal customers) forming a "Constant" part of the equation.

This can be represented as:

Brand Sales volume (Y)= Base sales +Price increase+Distribution+Discounts+Brand Search Ad spend +TV Ad spend+Awareness drive spend+Competition price increase

The next step is to calculate the impact of each factor which (can also be termed as regression coefficient ; denoted as "m")....This is the "Extra" bit of "Extra-ordinary". Sharper is the "m" better would be the predictability. And "m" needs to be calculated with the help of historical data (removing the outliers) to understand the contribution of each investment bracket/ factor in maximizing the ROI.

The final equation will be represented as:

Sales volume (Y)= Base sales + m1(Price increase) + m2(Distribution spends) + m3(Discounts) + m4(Brand Search Ad spend) +m5(TV Ad spend) +m6(Awareness drive spend) +m7(Competition MRP increase)

To calculate "m", the correlation needs to be seen for each factor, for eg: if the price is increased, is there a decline in sales volume? This can be calculated in excel with the function =CORREL (for one factor) or running a multiple regression model will help to calculate "m1....m6" at one time.

(Multiple videos are available on youtube on steps to calculate multiple regression)

Example:

Then we map the external environment. For that, it is essential to calibrate each factor in the short term. For eg; a Pandemic causes an increase in stocking despite the higher prices, in such a case "m" for discount can be zero for a certain time period. Once we feed in each data, the final sales volume can be derived. Depending on "m1, m2,...", the level of investment can be calculated for each factor to reach a certain sales volume. The equation also helps to understand the impact factor for each variable and which one to be prioritised (can be modelled for output KPI's as well). Besides, it also helps to test any relevant hypothesis in the short term.

The efficiency of the model should be checked by experimenting with historical data to predict current sale volume and compare the percentage of correctness. This will help understand the robustness of the model.

At an overall level, this equation has been kept linear and the correlation depends largely on the quality of data. Hence, larger the data points, better will be the predictability. Also, it is important to remove the outliers!