How to Calculate the Stock to Sales Ratio Using a Formula in Excel – 4 Steps

This is an overview:

Overview Image of how to calculate Stock to Sales Ratio using Formula in Excel]


The Stock to Sales Ratio

The stock to sales ratio 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.

Stock to Sales Ratio Formula:

The formula is:

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


Step 1 – Create a Stock to Sales Ratio Table

  • Create a table in Excel and enter: Average Stock, Gross Sales, and Net Sales.

Table creation to calculate Stock to Sales Ratio]


Step 2 – Calculate the Average Stock Value

  • Use the formula in C13 and press Enter.

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

Calculation of Average Stock value]


Step 3 – Determine the Net Sales

  • To get the gross sales, enter this formula in C14.

=((C7-C8)*C10)

Calculation of Gross sales to get stock to sales ratio]

  • Copy this formula to C15 to see the Net Sales.

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

Calculation of Net Sales to get Stock to Sales Ratio]


Step 4 – Compute the Stock to Sales Ratio

  • Use the formula in C16:

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

Final output of Stock to Sales Ratio]

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

=C13/C15

Calculating Stock to Sales Ratio Percentage]


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

  •  It indicates business efficiency: A higher Inventory Turn ratio indicates that the company is selling its products quickly, generating revenue, and minimizing the cost of carrying excess inventory.
  • It helps to optimize inventory management: 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.
  • Improves cash flow: Businesses can identify slow-moving products and make necessary adjustments to improve sales.
  • Helps forecasting: By analyzing trends in the Inventory turnover ratio over time, businesses can develop more accurate sales forecasts.

What Is a Good Inventory to Sales Ratio?

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. A ratio of around 1 is considered a good benchmark.


Frequently Asked Questions

  • How do you calculate the stock turnover ratio in Excel?

Use the following formula:

Stock Turnover Ratio = Cost of Goods Sold / Average Stock

  • What is the formula of the Purchase to Sales Ratio?

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

PSR = Cost of goods sold / Net sales

A high PSR indicates the company is carrying too much inventory.


Download Practice Workbook

Download the following Excel workbook.


Related Articles


<< Go Back to Inventory Management in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mahfuza Anika Era
Mahfuza Anika Era

Mahfuza Anika Era graduated from the Bangladesh University of Engineering and Technology in Civil Engineering. She has been with ExcelDemy for almost a year, where he has written nearly 30 articles and reviewed many. She has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, she is working as a team leader for ExcelDemy. Her role is to guide his team to write reader-friendly content. Her interests are Advanced Excel, Data Analysis, Charts & Dashboards,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo