How to Calculate Stock to Sales Ratio Using Formula in Excel

In this article, I am going to explain how to calculate the Stock to sales ratio step-by-step in Excel using a formula. If you are new to Inventory Management and want to know how to calculate Stock to Sales Ratio then you have come to the right place. Stock to sales ratio is a very useful KPI in the supply chain. It is broadly known as Inventory Turnover. Actually, Stock to Sales Ratio is known by the following names:

Inventory Turnover
Stock Turn
Inventory Turn
Days in Inventory
Inventory Coverage
Days on Hand

Now, let’s get started.

What Is Stock to Sales Ratio?

The stock to sales ratio is a financial ratio that measures the amount of inventory a business has on hand relative to the amount of sales it generates over a particular period of time. It is calculated by dividing the value of the average inventory held during a given period by the value of sales generated during the same period.

The ratio is often used by businesses to evaluate their inventory management efficiency and to identify potential inventory excesses or shortages. A high stock to sales ratio indicates that a business is holding too much inventory relative to its sales, which can lead to higher storage costs and decreased cash flow. On the other hand, a low stock-to-sales ratio may suggest that a business is not holding enough inventory to meet demand, which can result in lost sales and missed opportunities.

Stock to Sales Ratio Formula:

The formula for calculating the stock-to-sales ratio is:

<span style="font-size: 14pt;"><strong>Stock-to-Sales Ratio = Average Inventory or stock / Net Sales</strong></span>

Calculate Stock to Sales Ratio Using Formula in Excel: Step-by-Step

To Calculate Stock to Sales Ratio, you will need the Average Stock Value and Net Sales. By using Excel Formulas you can calculate this data very easily.

Suppose, there is a company that sells Blazer. I want to Calculate Stock to Sales Ratio of this particular product. Follow the steps below to compute the ratio.

Step 1: Create a Stock to Sales Ratio Table

Firstly, you need to create a table in Excel. Here, in the first table, all the necessary information about the product is given. Based on this information, I will calculate Stock to Sales Ratio.

I need Average Stock, Gross Sales, and Net Sales to calculate Stock to Sales Ratio and Percentage. So I have created a table with this data.

Step 2: Calculate Average Stock Value

Secondly, write this formula to the C13 cell to calculate the Average Stock and press Enter.

=(C7*C9+C8*C9)/2

Step 3: Determine Net Sales

To calculate Gross Sales, write this formula to cell C14.

=((C7-C8)*C10)

Next, copy this formula to cell C15 and you will get Net Sales.

=((C7-C8)*C10-C11*C10)

Step 4: Compute Stock to Sales Ratio

Now I have all the information to calculate Stock to Sales Ratio.

Write the formula to cell C16 and you will get Stock to Sales Ratio.

=ROUND(C13/C15,2)&":"&1

To calculate Stock to Sales percentage, copy this formula to cell C17.

=C13/C15

Why Is the Inventory Turn or Stock to Sales Ratio KPI So important?

The Inventory Turn or Stock to Sales Ratio is an essential Key Performance Indicator (KPI) for businesses that rely on inventory to generate revenue, such as retail stores, wholesalers, and manufacturers. The Inventory turnover ratio measures the number of times a company sells and replaces its inventory during a specific period, usually a year.

There are several reasons why the Inventory Turn or Stock-to-Sales Ratio is crucial for businesses:

Indicates Business Efficiency: The Inventory turnover ratio reflects how efficiently a company manages its inventory. A higher Inventory Turn ratio indicates that the company is selling its products quickly, generating revenue, and minimizing the cost of carrying excess inventory.
Helps to Optimize Inventory Management: By tracking the Inventory Turn ratio, businesses can determine how quickly they sell their products and how often they need to restock. This information can help them make more informed decisions about inventory management, such as when to reorder and how much to order.
Improves cash flow: By monitoring the Inventory turnover ratio, businesses can identify slow-moving products and make necessary adjustments to improve sales. This can help free up cash tied up in slow-moving inventory and improve overall cash flow.
Helps with forecasting: By analyzing trends in the Inventory turnover ratio over time, businesses can develop more accurate sales forecasts and make better-informed decisions about future inventory needs.

Overall, the Inventory Turn or Stock-to-Sales Ratio KPI is essential because it provides insight into a company’s inventory management, efficiency, cash flow, and forecasting. By tracking this metric, businesses can make informed decisions that can help them increase profitability and achieve long-term success.

What Is a Good Inventory to Sales Ratio?

The inventory-to-sales ratio is a financial metric that measures how efficiently a company manages its inventory. A good inventory-to-sales ratio varies by industry, but a lower ratio is generally preferable.

A lower ratio indicates that a company is selling its inventory quickly and efficiently, which reduces the risk of excess inventory or stockouts. Generally, a ratio of around 1 is considered a good benchmark, but again, this varies by industry.

For example, a company in the retail industry may have a higher inventory-to-sales ratio due to the nature of its business, where inventory turnover tends to be slower. In contrast, a company in the food industry may have a lower inventory-to-sales ratio since their products have a shorter shelf life.

Ultimately, a “good” inventory-to-sales ratio is one that aligns with a company’s industry norms and goals and allows for effective inventory management and profitability.

Frequently Asked Questions

What is a good stock to sales ratio?

As a rule of thumb, a stock to sales ratio of 1 is good for most industries. However, some industries such as retail, where inventory turnover is faster, may have a ratio below 1. On the other hand, some industries such as manufacturing, where inventory turnover is slower, may have a higher ratio.

What does a 1.0 stock to sales ratio mean?

In retail and manufacturing, a stock to sales ratio of around 1:1 is often a healthy thing. This means that the company is selling all its inventory within a year. However, some industries like technology may have higher ratios, as they tend to hold onto their inventory for longer periods. It’s important to compare a company’s stock to sales ratio with those of its competitors and industry averages to determine what’s considered good.

How do you calculate stock turnover ratio in Excel?

Stock to Sales Ratio and Stock Turnover Ratio is basically the same thing.

To calculate the stock turnover ratio in Excel, you can use the following formula:

Stock Turnover Ratio = Cost of Goods Sold / Average Stock

What is the formula of Purchase to Sales Ratio?

The purchase-to-sales ratio (PSR) is a parameter used to evaluate a company’s ability to manage inventory. The formula for calculating the PSR is:

PSR = Cost of goods sold / Net sales

Where the cost of goods sold represents the direct costs associated with producing or purchasing the goods that a company sells, and net sales represent the total revenue generated from the sale of those goods after any returns or discounts are taken into account.

A high PSR indicates that a company is carrying too much inventory relative to its sales, which can result in higher carrying costs and reduced profitability. A low PSR, on the other hand, suggests that a company is effectively managing its inventory and is able to generate higher profits.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.

Calculating Stock to Sales Ratio.xlsx

Conclusion

Stock to Sales Ratio also known as Inventory Turnover is a very important parameter in Supply Chain. In this article, I have explained 4 easy steps for calculating Stock to Sales Ratio with a formula in Excel. Follow the steps carefully and make necessary changes according to your dataset. I hope this article will help you to calculate Stock to Sales ratio by just entering some simple formulas. If you face any problems regarding this article, please comment so that we can help.