This blog will introduce you to how Brand Managers model basic FMCG KPIs in Excel.
There are a lot of articles that touch upon the life of a Brand Manager and the various responsibilities they shoulder. Here we will put a microscope on just one of the numerous calculations that Brand Managers undertake, and learn how they find business improvement areas through data analysis.
If you are a Brand Manager, we recommend you skip to the end of this blog to ‘Basic FMCG Modeling Made Easy’ or read ‘Complementing Excel – How Brand Managers can Simplify Data Exploration and Analysis’.
Let us understand how to obtain Gross Margin, Net Margin, and Operational Profit. Arriving at these numbers helps Brand Managers analyze where they are losing their margin – is it at the production level, is it the cost of sales and marketing, or is it the head office costs? Brand Managers thus have a sense of direction to initiate further data exploration and make optimal, data-driven decisions.
Let’s begin:
Part 1 – Obtaining Net Margin
- Unit Gross Margin
Unit Gross Margin Depends on two things –
- The average price we are getting from the middlemen, or if we are directly selling to the customers, from them
- Subtracting the unit production cost from this average price
So Unit Gross Margin = Avg product price (say Rs. 70) minus its production cost (say Rs. 40) = Rs. 30
Note: The unit production cost is again dependent on two things –
a. The total fixed cost divided by the total quantity produced, plus
b. The unit variable cost
There are further sub-calculations in each component. For example, Total Fixed Cost (FC) includes salaries to be paid, which is typically generated as: taking the number of full-time employees or full-time equivalents (FTE), setting an average salary per FTE, and assuming some social securities as a percentage of the salary. The salary excludes the bonus earned by the employee.
- Gross Margin
Once we have the unit gross margin and the total number of products sold, we get the Gross Margin easily enough.
Gross Margin = Unit Gross Margin x Total Products Sold
The Gross Margin will be calculated for various channels we are selling through, and a year-on-year, or month-on-month record will be maintained too.
As you can see, such calculations require Brand Managers to be detail-oriented, organized, knowledgeable and possess a deft hand at Excel.
- Sales and Marketing Costs
Obtaining the Gross Margin has covered the Production Cost. We have yet to factor in the sales and marketing costs, so let’s do that. Sales and marketing costs depend on the size of a brand’s market share. A bigger market share means we are selling more, which means that the costs attached to sales and marketing per unit is lesser.
Marketing elements would include –
- Social Media
- TV ads (computed as the number of campaigns multiplied by the cost of 1 campaign)
- Outdoor campaigns
- Loyalty programs
- Market research
- Mailing
Components of cost of sales would be –
- Salaries
- External services (cars, phones, fuel, etc)
- Materials & Energy
- Other related services
These would be calculated for both retail chains where we supply directly as well as for the traditional stores that we reach via wholesalers.
- Net Margin
Part 2 – Obtaining Operational Profit
Deducting Head Office costs from the Net Margin gives us the Operational Profit. Head Office costs include –
- Salaries
- Material and Utilities
- Maintenance
- Rent (for offices and warehouses)
- Depreciation and amortization of assets
Part 3 – Zooming Out
Converting all numbers into percentages for easier visual view, the final output would be like this:
Basic FMCG Modeling Made Easy
The above KPI modeling and profit calculation require a Brand Manager to continuously switch between multiple tabs and insert various formulae to get the figures. The same process can be augmented through Explorazor, our data exploration tool.
Explorazor combines and hosts all datasets, for example, market research, internal sales, Nielsen data, etc. in an integrated manner. Brand Managers thus obtain a single view of the entire dataset. From there, they can extract data cuts instantly through a simple search function of using column names as keywords.
Explorazor also allows
- Visualizing pivots as charts
- Pinning the charts to a pinboard, and
- Downloading them as CSV files
Moreover, all data resides on servers and is accessible via a browser. Laptops are thus relieved from the burden of processing huge datasets. Brand Managers are further liberated when their reliance on BI teams is reduced. The acceleration of ad-hoc exploration is experienced immediately with Explorazor.
Explorazor is built for large enterprises, with single sign-on, row and column level security, data encryption, and on-cloud and on-premise availability.
Do you want to see other features added to Explorazor? Write to us at sales@vphrase.com. If you want to see the product in action, take an interactive Product Tour.