This is an overview:
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.
Step 2 – Calculate the Average Stock Value
- Use the formula in C13 and press Enter.
=(C7*C9+C8*C9)/2
Step 3 – Determine the Net Sales
- To get the gross sales, enter this formula in C14.
=((C7-C8)*C10)
- Copy this formula to C15 to see the Net Sales.
=((C7-C8)*C10-C11*C10)
Step 4 – Compute the Stock to Sales Ratio
- Use the formula in C16:
=ROUND(C13/C15,2)&":"&1
- To calculate the Stock to Sales percentage, copy this formula to C17.
=C13/C15
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
- How to Keep Track of Inventory in Excel
- How to Maintain Store Inventory in Excel
- How to Make Inventory Aging Report in Excel
- How to Create Inventory Database in Excel
- Min Max Inventory Calculation in Excel
- How to Calculate Economic Order Quantity in Excel
<< Go Back to Inventory Management in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!