Expense coverage days is a financial ratio that measures how long a company can operate with its current short-term assets without generating additional revenue. It is calculated by dividing the total of the company’s liquid assets such as cash, short-term marketable securities, and accounts receivable by its daily cash expenditures. The formula is:
This ratio indicates the financial health and liquidity of a company. A higher expense coverage days means that the company has more buffer to cover its operating expenses in case of a revenue shortfall or an unexpected expense. A lower expense coverage days means that the company is more dependent on its revenue stream and may face cash flow problems if there is a disruption in its income.
How to calculate expense coverage days in excel
To calculate expense coverage days in excel, we need to have the following data:
- Short-term assets: The sum of the company’s cash, short-term marketable securities, and accounts receivable. This can be entered in a single cell or a range of cells.
- Daily cash expenditures: The average amount of cash that the company spends on its operating activities per day. This can be estimated by dividing the total annual operating expenses by 365 days, or by using a more accurate method based on the company’s historical data. This can be entered in a single cell or a range of cells.
Once we have the data, we can use the following formula in any cell to get the expense coverage days:
=ShortTermAssets/DailyCashExpenditures
Where ShortTermAssets and DailyCashExpenditures are the cell references or ranges that contain the data.
For example, if we have the following data in cells A1 to B3:
| A | B |
|---|---|
| Short-Term Assets | 100,000 |
| Daily Cash Expenditures | 2,000 |
We can enter the formula =B1/B2 in cell B4 to get the expense coverage days, which is 50.
Example
To illustrate how to use the expense coverage days formula in excel, let us consider the following scenario:
ABC Inc. is a company that sells widgets online. It has the following data for the year 2023:
- Cash: $50,000
- Short-Term Marketable Securities: $20,000
- Accounts Receivable: $30,000
- Operating Expenses: $600,000
We want to calculate the expense coverage days for ABC Inc. using excel.
First, we enter the data in cells A1 to B5:
| A | B |
|---|---|
| Cash | 50,000 |
| Short-Term Marketable Securities | 20,000 |
| Accounts Receivable | 30,000 |
| Operating Expenses | 600,000 |
Next, we calculate the short-term assets by adding the values in cells B1 to B3. We enter the formula =SUM(B1:B3) in cell B6 and get the result 100,000.
| A | B |
|---|---|
| Cash | 50,000 |
| Short-Term Marketable Securities | 20,000 |
| Accounts Receivable | 30,000 |
| Operating Expenses | 600,000 |
| Short-Term Assets | 100,000 |
Then, we calculate the daily cash expenditures by dividing the operating expenses by 365 days. We enter the formula =B4/365 in cell B7 and get the result 1,643.84.
| A | B |
|---|---|
| Cash | 50,000 |
| Short-Term Marketable Securities | 20,000 |
| Accounts Receivable | 30,000 |
| Operating Expenses | 600,000 |
| Short-Term Assets | 100,000 |
| Daily Cash Expenditures | 1,643.84 |
Finally, we calculate the expense coverage days by dividing the short-term assets by the daily cash expenditures. We enter the formula =B6/B7 in cell B8 and get the result 60.86.
| A | B |
|---|---|
| Cash | 50,000 |
| Short-Term Marketable Securities | 20,000 |
| Accounts Receivable | 30,000 |
| Operating Expenses | 600,000 |
| Short-Term Assets | 100,000 |
| Daily Cash Expenditures | 1,643.84 |
| Expense Coverage Days | 60.86 |
This means that ABC Inc. can operate for about 61 days with its current short-term assets without generating any revenue.
Other approaches
There are other ways to calculate the expense coverage days in excel, depending on the data available and the level of accuracy required. Some of them are:
- Using the average accounts payable instead of the operating expenses to estimate the daily cash expenditures. This can be done by dividing the total annual accounts payable by 365 days, or by using a more accurate method based on the company’s historical data. This can be more realistic if the company has a long payment cycle or a high credit period from its suppliers.
- Using the net working capital instead of the short-term assets to measure the liquidity of the company. This can be done by subtracting the total current liabilities from the total current assets. This can be more comprehensive if the company has significant short-term debts or other obligations that affect its cash flow.
- Using the monthly or quarterly data instead of the annual data to calculate the expense coverage days. This can be done by dividing the short-term assets by the average monthly or quarterly cash expenditures, or by using a more accurate method based on the company’s historical data. This can be more accurate if the company has seasonal or cyclical variations in its revenue or expenses.
