Liquid assets are assets that can be easily converted into cash within a short period of time, usually one year or less. Liquid assets are important for businesses and individuals because they provide liquidity, which is the ability to meet short-term financial obligations. Some examples of liquid assets are cash, cash equivalents, marketable securities, accounts receivable, and inventory.
To calculate liquid assets in Excel, we can use the following formula:
Liquid Assets = Current Assets – Inventories – Prepaid Expenses
Current assets are the assets that are expected to be converted into cash within one year or the normal operating cycle of the business, whichever is longer. Current assets include cash, cash equivalents, marketable securities, accounts receivable, inventory, and prepaid expenses.
Inventories are the goods that are held for sale in the ordinary course of business. Inventories are usually less liquid than other current assets because they may not be sold quickly or at their full value.
Prepaid expenses are the payments made in advance for goods or services that will be received in the future. Prepaid expenses are not liquid assets because they cannot be converted into cash until the goods or services are delivered.
To illustrate how to calculate liquid assets in Excel, let us consider the following scenario:
ABC Company has the following current assets as of December 31, 2023:
- Cash: $50,000
- Cash equivalents: $10,000
- Marketable securities: $40,000
- Accounts receivable: $30,000
- Inventory: $20,000
- Prepaid expenses: $5,000
To calculate the liquid assets of ABC Company, we can use the following formula in Excel:
=Liquid Assets = Current Assets – Inventories – Prepaid Expenses
=Liquid Assets = ($50,000 + $10,000 + $40,000 + $30,000) – $20,000 – $5,000
=Liquid Assets = $105,000
The result is shown in the following table:
| Current Assets | Amount |
|---|---|
| Cash | $50,000 |
| Cash equivalents | $10,000 |
| Marketable securities | $40,000 |
| Accounts receivable | $30,000 |
| Inventory | $20,000 |
| Prepaid expenses | $5,000 |
| Total Current Assets | $155,000 |
| Liquid Assets | $105,000 |
The liquid assets of ABC Company are $105,000, which means that the company has enough liquidity to meet its short-term financial obligations.
Another approach to calculate liquid assets in Excel is to use the SUM and SUMIF functions. The SUM function adds up a range of numbers, while the SUMIF function adds up a range of numbers that meet a certain criteria. For example, we can use the following formula to calculate the liquid assets of ABC Company:
=Liquid Assets = SUM(Cash, Cash equivalents, Marketable securities, Accounts receivable) – SUMIF(Current Assets, “Inventory”, Current Assets) – SUMIF(Current Assets, “Prepaid expenses”, Current Assets)
=Liquid Assets = SUM($50,000, $10,000, $40,000, $30,000) – SUMIF($A$2:$A$7, “Inventory”, $B$2:$B$7) – SUMIF($A$2:$A$7, “Prepaid expenses”, $B$2:$B$7)
=Liquid Assets = $130,000 – $20,000 – $5,000
=Liquid Assets = $105,000
The result is the same as the previous formula, but this formula is more flexible because it can automatically adjust to changes in the current assets. For example, if the inventory of ABC Company increases to $25,000, the liquid assets will decrease to $100,000, as shown in the following table:
| Current Assets | Amount |
|---|---|
| Cash | $50,000 |
| Cash equivalents | $10,000 |
| Marketable securities | $40,000 |
| Accounts receivable | $30,000 |
| Inventory | $25,000 |
| Prepaid expenses | $5,000 |
| Total Current Assets | $160,000 |
| Liquid Assets | $100,000 |
This formula is also more convenient because it does not require us to manually subtract the inventories and prepaid expenses from the current assets. We can simply use the SUMIF function to filter out the current assets that are not liquid.
