What is Sales Backlog Ratio?
Sales backlog ratio is a measure of how many orders a company has received but not yet fulfilled, compared to its sales volume. It indicates the demand for the company’s products or services, and the potential revenue that can be generated from the backlog. A high sales backlog ratio means that the company has more orders than it can handle, which may be a sign of strong demand, but also a risk of customer dissatisfaction or lost sales. A low sales backlog ratio means that the company has fewer orders than it can handle, which may be a sign of weak demand, but also an opportunity to improve efficiency and customer service.
How to Calculate Sales Backlog Ratio?
There are two ways to calculate sales backlog ratio: by using the total dollar value of the backlog and the sales, or by using the number of days of sales that the backlog represents. The formulas are:
- Sales backlog ratio (dollar value) = Total order backlog / Quarterly sales
- Sales backlog ratio (days of sales) = Total order backlog / (Quarterly sales / 90 days)
The first formula gives the ratio of the backlog to the sales in the same unit (dollars), while the second formula gives the ratio of the backlog to the sales in terms of time (days). Both formulas can be used to compare the sales backlog ratio across different periods or companies, as long as the same unit and time frame are used.
How to Use Excel Formula to Calculate Sales Backlog Ratio?
To use Excel formula to calculate sales backlog ratio, you need to have the data of the total order backlog and the quarterly sales for the period or company you want to analyze. You can enter the data in two columns, such as A and B, and label them as “Backlog” and “Sales”. Then, you can use the following formulas in another column, such as C, and label it as “Sales Backlog Ratio”:
- Sales backlog ratio (dollar value) =
=A2/B2 - Sales backlog ratio (days of sales) =
=A2/(B2/90)
You can copy and paste the formulas to the rest of the rows to get the sales backlog ratio for each period or company. You can also format the cells to show the ratio as a percentage or a decimal number, depending on your preference.
Example of Sales Backlog Ratio Calculation
To illustrate how to calculate sales backlog ratio in Excel formula, let’s use an example of a hypothetical company that sells widgets. The company has the following data of its order backlog and sales for the last four quarters:
| Quarter | Backlog | Sales |
|---|---|---|
| Q1 | $50,000 | $100,000 |
| Q2 | $40,000 | $120,000 |
| Q3 | $60,000 | $80,000 |
| Q4 | $30,000 | $90,000 |
Using the formulas above, we can calculate the sales backlog ratio for each quarter as follows:
| Quarter | Backlog | Sales | Sales Backlog Ratio (dollar value) | Sales Backlog Ratio (days of sales) |
|---|---|---|---|---|
| Q1 | $50,000 | $100,000 | =A2/B2 = 0.5 or 50% |
=A2/(B2/90) = 45 days |
| Q2 | $40,000 | $120,000 | =A3/B3 = 0.33 or 33% |
=A3/(B3/90) = 30 days |
| Q3 | $60,000 | $80,000 | =A4/B4 = 0.75 or 75% |
=A4/(B4/90) = 67.5 days |
| Q4 | $30,000 | $90,000 | =A5/B5 = 0.33 or 33% |
=A5/(B5/90) = 30 days |
The results show that the company has a varying sales backlog ratio over the four quarters, ranging from 0.33 to 0.75 in dollar value, and from 30 to 67.5 days in days of sales. This means that the company has different levels of demand and capacity to fulfill its orders in each quarter. For example, in Q3, the company has a high sales backlog ratio, which means that it has more orders than it can handle, and it may need to increase its production or delivery speed to avoid losing customers or revenue. In Q4, the company has a low sales backlog ratio, which means that it has fewer orders than it can handle, and it may need to boost its marketing or sales efforts to attract more customers or revenue.
