Analytics Calculations: Inventory Profit Potential

josephmckeownjosephmckeown Moderator, Lightspeed Staff Posts: 81 moderator
edited October 2019 in Reporting & Analytics

Sometimes we may be interested in reading the profit or margins of our unsold inventory.

There are limitation in how much we can infer from Inventory margins because there is an assumption that we will sell all our inventory without discount. However, it can be a good indicator of whether our pricing is on track to meet our profit goals, and if there are areas that we should consider.

So, we could use calculations to define, based on our current inventory costs and retail prices, what our potential for profit is, and what our potential profit margins are.

Step One: Prepare an Inventory Report

Let's begin by opening up a Dusty Inventory report, as its core is the Inventory report.

and let's open up the filters. There are two filters that we need to remove to turn a "Dusty" report into an "All Inventory" report:

  • Is Dusty is Yes
  • Quantity on Hand is greater than 0

So let's remove both of these filters...

Then run the report a second time

And we'll get a true Inventory report.

Step Two: Add the measures that we want

Profit is a function of Total minus Cost. We already have the "Total Cost" per product on the report. What we're missing is the "Total"

What "Total" do we want though? Total Sales? Nope! That will show us what has already (if ever) sold. We want to forecast a potential total, and this number can be found in the Item Metrics, On Hand> Total Retail Value

Let's add it to the report and run again.

Now, "Dusty" also includes some measures that we don't need. Just to make our report more efficient, I'm going to remove the "Days Since Sold"

and let's do the same for "Days Since Received"

So now we have all the raw data that we want for our report

Step Three: Profit Potential

Profit is Total minus Cost, so we could use a Table Calculation to provide this number.

Let's go to our Custom Fields, and add a new Table Calculation:

We'll start looking for "Total Retail Value"...

There it is!

Then we'll subtract...

...and look for "Cost"...

...selecting "Total Cost"

Let's also make sure that our number Type is U.S. Dollars, and let's give the calculation a more meaningful title...

So when we save...

We start getting some meaningful results!

So, if we were to sell everything currently in our inventory with no discount, this would be our line profit.

Step Four: Margin Potential

Now that we have "Profit Potential", we could create a second calculation to plot this as a potential profit margin.

Profit margins are the profit of a sale compared to the full retail price of the sale.

So let's go back to our Table Calculations, and duplicate our code for Profit Potential...

...and let's edit our copy...

First, we need to put brackets around our profit code, so that the order of operations is maintained...

Once there is an opening bracket at the beginning, and a closing bracket at the end, we'll include a division (using the forward slash)

And we'll divide by the Total Retail Value again

Finally, we'll switch the number type to Percent, and give the calculation a more meaningful title

So when we save our calculation...

There it is!

Spoiler Alert:

Profit Potential is


Margin Potential is



Calculations Home

Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Sign In or Register to comment.