Indzara

How to calculate Profitability by Product in Excel?

This post from our “Data to Decisions” series is dedicated to calculating the profitability by product when you have a list of transactions (or orders) of the goods sold and the cost of each of these goods.

Consider a sample transaction list of products sold along with their quantity and sales price. Also, a product-level list of costs to produce the corresponding product.

calculate Profitability by Product sample data

For this sample, we’ll look at how to create a report that shows the profitability of each product. This report can be used to make key decisions like:

  1. Which products are more profitable?
  2. Identify those that are less profitable and take corrective decisions.

Before we begin, let us first convert our sample data into Tables. This is a step, we highly recommend to increase formula readability and scalability when data expands.

For this, select all the data, click CTRL+T, and assign a name to each table.

calculate Profitability by Product select data

For the transaction details table, we’ve named it ORDERDETAILS and the product costs table as COST.

Let us get into a detailed step-by-step approach to generate the profitability by product report.

Step 1:

First, let us extract the distinct list of products from our ORDERDETAILS table by using the UNIQUE function. The formula in cell K7 will be:

The UNIQUE function takes as input an array or range of data and returns only the unique list of values. Please read our blog on extracting unique values to know more.

calculate Profitability by Product extract unique values

Step 2:

Now, we will get the quantity sold for each product from the ORDERDETAILS table by using the SUMIF function, its syntax is as follows

calculate Profitability by Product SUMIF function

In our case, the formula in cell L7 will be:

Note: Click and Drag the formula from cell L7 till wherever is needed to apply the same formula.

calculate Profitability by Product apply SUMIF function

To remove the zeroes, we’ll enclose the above formula within an IF function, to display QTY only when there is a corresponding product. The modified formula will be,

The cleaned up, QTY details will look like this:

calculate Profitability by Product remove zeros

Step 3:

Let us now calculate the SALES amount. We’ll use the FILTER function over the ORDERDETAILS table to multiply the quantity and corresponding sales price but filter it based on the product in cell K7.

The syntax for FILER is, 

calculate Profitability by Product FILTER function

Now our formula in cell M7 will be,

This will return the sum of all product A sales based on the individual orders (orders 1,4 and 7). This looks like the below:

calculate Profitability by Product sum of all product

But, we need the sum of sales value for each of these products, so we’ll wrap the above formula with a SUM function. Similar to the previous step, we’ll also include an IF condition to return sales only when a corresponding product exists.

Our formula now will be:

After Step 3, the report will look like this

calculate Profitability by Product sum of sales

Step 4:

Here, we’ll calculate the Cost of Goods by using the XLOOKUP function. We’ll look up product A (in cell K7) from the COST table, and return the COST tables’ values.

In cell N7 our formula will be,  

The syntax for XLOOKUP is:

calculate Profitability by Product XLOOKUP

The report will have the cost of product A in cell N7:

calculate Profitability by Product cost of product

Since we need the total sales, we’ll multiply with the QTY of product A from our report (in cell L7). Similar to previous steps, we’ll wrap the whole function with an IF condition, our final formula will be:

The report will be,

calculate Profitability by Product report

Step 5:

We’ll use the SUM function to calculate the total Quantity (in cell L5) as shown:

Click and drag, horizontally to get the total Quantity, Sales, and Cost of Goods:

calculate Profitability by Product get total quantity

Step 6:

We’ll get into profit calculation now: this is straightforward, the sales value (in cell M7) minus the cost of goods (in cell N7) gives us the profit by product.

Here too, we’ll wrap this around an IF condition, our formula in cell O7 will be:

Once this is calculated, our final report will have all the necessary details to make meaningful analyses:

Profitability by Product Report

We have a detailed, step-by-step explainer video on calculating profitability by product, do check it out:

Leave a Reply

Your email address will not be published. Required fields are marked *