Change In Working Capital (How To Interpret And Calculate) In Excel With Marketxls
Meet The Ultimate Excel Solution for Investors
- Live Streaming Prices Prices in your Excel
- All historical (intraday) data in your Excel
- Real time option greeks and analytics in your Excel
- Leading data in Excel service for Investment Managers, RIAs, Asset Managers, Financial Analysts, and Individual Investors.
- Easy to use with formulas and pre-made sheets
What is working Capital and how to calculate it in Excel with MarketXLS
Analysts and investors will frequently refer to something called the net working capital. Or a lot of times they will just call it working capital for short. So working capital is very simple it’s like the current assets minus the current liabilities of a firm. When valuing the companies the “change in working capital” is more useful.
Working capital is not mandatory to be put inside the financial statements. It is the measure of the liquidity of the firm and it gives us ideas about how well a company can meet its current obligations.
Let’s take a real life example of a company and see how we would calculate working capital with the inbuilt functions provided by MarketXLS. MarketXLS has functions available for both current assets and current liabilities. So all that you will need to do is use the current assets and current liabilities functions and subtract them like shown below.
The current liabilities are some kind of gauge that we have to understand the firms near future obligations and if with the current assets the company will be able to meet those obligations. Essentially, working capital gives us an indication of how liquid is a firm.
Current assets are elements like the cash, accounts receivables, inventory. The current liabilities are the element like accounts payable, accrued income taxes etc. Now with market XLS because you already have the current assets and the current liabilities functions, you do not need to worry about the individual components of those elements.
Let’s use MarketXLS to calculate working capital of Apple Inc. from 2011 to 2016. And to do that we will have that year number in one column and we will simply refer to that column when calculating the current assets and the current liabilities.
Instead of using the current assets and the current liabilities functions you can also use the working capital function instead.
With working capital functions of MarketXLS you can quickly calculate working capital of multiple companies in an easy way. So, you can get to the analysis and interpretation of the numbers. Below, I have calculated the working capital of various companies using this formula.
Now that we have the numbers let’s try and see what do they mean.
How to interpret working CapitalIf the working capital is increasing we can conclude that the company is getting more and more liquid. Which could mean a lot of things, mainly, it could mean that the company is able to utilize its existing resources in a better way to generate more revenue and accumulate more cash flow from operations.
You might be wondering why some companies have negative working capital and some positive. A simple explanation for this is that the companies who buy inventory before they sell products have negative working capital. So, Walmart, needs to buy inventory before they sell the products in stores. What it means is that Walmart would have more current liabilities and less current assets because they would pay their vendors much earlier than when they can charge their customers.
So, in general, you would see that software companies would have positive working capital because they don’t have to buy the inventory before they could sell the product to the end customer. Which means that they immediately get revenue without accumulating current liabilities.
So what does it mean when a company has negative working capital? And how do you calculate the change in working capitalIf a company have the consistent negative working capital numbers it may mean that this company may need external funding to grow. Their working capital is not contributing to the immediate cash flow so if they were to invest in growth they would need some money from outside the company to invest.
We should also look at what percent of revenue is the working capital. If working capital is a very small proportion of revenue, then our conclusion above may not hold.
In the case of Walmart, working capital is less than 1% of their revenue. So our interpretation above may not hold true for Walmart.
Almost all the time working capital is an indication of its daily operations. But sometimes it also suggests financial problems especially when working capital is a large proportion of the revenue.Change in working capitalJust looking at working capital numbers does not give us a complete picture of the operational health of a company. To get a real understanding of the company’s operational efficiency we need to look at “change in working capital”.
Changing working capital does mean actual change in value year over year. But it means the change current assets minus the change current liabilities. The change in working capital value gives a real indication on why the working capital has increased or decreased. If current assets have remained same but the current liabilities have increased it means a negative change in working capital.
When valuing companies the change in working capital value is a more telling figure than the working capital itself. The change in the working capital will have a direct impact on the cash flow from operations. And the cash-flow is the main factor we consider when valuing a company.
Any increase in revenue will also generally show an increase in working capital. This is specifically true for the companies where the working capital is a big proportion of the revenue, unlike Walmart.
With MarketXLS you can do all these calculations and ratios very easily with the historical fundamental functions as described. If you have done any analysis on working capital and would like to share your knowledge please contact us.
Sources :WikipediaInvestopedia
I invite you to book a demo with me or my team to save time, enhance your investment research, and streamline your workflows.