DISCRETIONARY COST RATIO is a financial metric that helps companies to quantify the unrestricted expenses that can be eliminated in the near term. It is calculated by dividing the discretionary costs by the sales revenues. Discretionary costs include expenses such as routine maintenance, service contracts, employee training, meals, entertainment, travel, as well as participation in efforts such as syndicated research.
Here are the steps to calculate the Discretionary Cost Ratio in Excel:
- Open a new Excel spreadsheet.
- In cell A1, type “Discretionary Costs”.
- In cell B1, type “Sales Revenues”.
- In cell C1, type “Discretionary Cost to Sales Ratio”.
- In cells A2 through A7, enter the discretionary costs for your scenario.
- In cells B2 through B7, enter the sales revenues for your scenario.
- In cell C2, enter the formula
=A2/B2. - Copy the formula in cell C2 to cells C3 through C7.
- Format the cells in column C as a percentage.
For example, let’s say that Company A’s industry was hit hard by what the forecasting team believes will be a near-term reduction in sales due to an economic recession. Company A’s management team asked their business analysts to pull what they’ve concluded are discretionary expenses from last year’s actual results. The table below was provided to Company A’s management team, which concluded they could offset a loss of nearly 4% of sales by eliminating discretionary costs:
| Discretionary Costs | Sales Revenues | Discretionary Cost to Sales Ratio |
|---|---|---|
| $295,218 | $8,200,000 | 3.6% |
| $177,131 | $4,500,000 | 3.9% |
| $161,028 | $5,000,000 | 3.2% |
| $64,411 | $2,000,000 | 3.2% |
| $42,941 | $1,500,000 | 2.9% |
| $32,206 | $1,000,000 | 3.2% |
As you can see, the discretionary cost to sales ratio is calculated by dividing the discretionary costs by the sales revenues. In this example, the discretionary cost to sales ratio ranges from 2.9% to 3.9%. By taking an inventory of what might be seen as discretionary costs and normalizing this value against sales, the company has a better idea of the potential lift in gross profit margin .
There are other approaches to calculate ratios in Excel, such as using the GCD, SUBSTITUTE & TEXT, and the ROUND functions. However, these methods are not applicable to calculate the Discretionary Cost Ratio .
