Working Capital Training Courses

Net Working Capital (NWC) is the difference between a company's current assets (net of cash) and current liabilities (net of debt) on its balance sheet. It is a measure of a company’s liquidity and its ability to meet short-term obligations as well as fund operations of the business. The ideal position is to. Course covers. This 3 day training course introduces participants to the key fundamentals of working capital and cash forecasting and how to integrate cash flow forecasting in order to improve the organization’s balance sheet and asset allocation. To get access to this and 977 other courses with unlimited CPE $199 This course offers an introduction to the management of working capital, beginning with collections, trade credit, and accounts receivable.

Free Training Courses
Venture Capital Training Courses
Working Capital Training Courses

Training Courses Here at Capital Hair & Beauty, we understand the importance of high quality training. Whether you’re a beginner looking to start a career in the ever growing hair and beauty industry, or you have an established business and want to increase your offering to clients, our courses are designed to suit every need. Guide to Working Capital formula, its uses along with practical examples. Here you also find Working Capital Calculator with downloadable excel template. Learn Investment Banking: Financial Modeling Training Courses Online.

This course offers an introduction to the management of working capital, beginning with collections, trade credit, and accounts receivable. Financial managers in companies of all sizes need effective strategies for these areas because a breakdown in any one of them can have harsh effects on the overall organization and the company’s liquidity position. Net Working Capital (NWC) is the difference between a company's current assets (net of cash) and current liabilities (net of debt) on its balance sheet. It is a measure of a company’s liquidity and its ability to meet short-term obligations as well as fund operations of the business.

Never has it been clearer than in the current global economy that cash is king. This article considers the practical issues surrounding modelling working capital adjustments. By Liam Bastick, director with SumProduct Pty Ltd.

Query

I have forecast income and expenditure items for the company’s Income Statement. What tips can you provide for deriving cash flow figures from these projections?

Advice

Let’s start at the beginning: please feel free to use the attached Excel file to help clarify the ideas discussed below.

Imagine a company just starts off in business (i.e. has no amounts due) and generates sales of $1,000 in the period. At the end of the period, assuming no bad debts, $753 has been paid, leaving a closing debtor balance of $247. This difference is what I refer to as the working capital adjustment.

If we had modelled the sales of $1,000 in the period, how might we generate the cash receipts forecast such that is assumptions changed, the receipts would calculate appropriately?

Clearly, if we are given the closing debtor balances, the problem becomes trivial, so I will assume that this is not so. Therefore, I am going to consider an alternative approach and some of the associated underlying issues that need to be considered when modelling.

At the risk of teaching the accountants in the audience to suck eggs, let me first derive an alternative method.
Let’s assume that the sales accrue evenly over the period of time and for the sake of this example, that period is one year (365 days). Presuming (i) all sales are made on credit terms, (ii) all customers pay their invoices on the day the amounts fall due and (iii) no bad debts are incurred, this can be reflected graphically as follows:

Clearly, the credit period is the “gap” at the beginning of the time period, i.e. 247/1000 x 365 days = 90 days. This can be represented formulaically as:

Days Receivable = (Closing Debtors x Days in Period) / Sales in Period

Closing Debtors = (Sales in Period x Days Receivable) / Days in Period,

Therefore, in modelling, we often set the number of days receivable (and days payable) as key assumptions for cash flow forecasting.

However, it’s not always as simple as that. Let me explain. Consider we are planning to build a monthly model (assuming 30 days in a month) and sales for the month are again $1,000. Debtor days remain at 90 days.

Based on these calculations, we would generate the following control account:

Free Training Courses

Erm, that’s right: make sales of $1,000 and have $3,000 (= 90/30 x 365) owing to you by the end of the month. Also, the company pays $2,000 to customers a reclaimable $2 for each $1 spent. That’s nonsense – and yet, as an experienced model auditor I have seen this erroneous calculation crop up on a regular basis…

The problem is, in this current economic climate most businesses want to prepare monthly – sometimes weekly and even daily – cash flow projections. Clearly, if the days receivable or days payable assumption exceeds the number of days in each forecast period this approach is inappropriate and will lead to calculation errors.

For example, in a monthly model, if payments are made exactly one month or two months or three months later (and so on), the resolution is simple: the receipts can be calculated using a simple OFFSET (displacement) formula.

Therefore, let’s complicate the scenario slightly. Imagine we are building a monthly forecast model, but that the days receivable are 75. For the purposes of keeping this article reasonably brief, I will simplify the problem by assuming an average number of days in a month (say, 30). Using this simplifying assumption, this will mean that payments are made on average 2.5 (2.5 = 75 / 30) months after the sale has been made.

That 2.5 months figure is important. The integer part (2) denotes how many complete months (including the current month) have sales payments outstanding. The residual (0.5) shows the proportion of the month preceding these complete months that is also outstanding. With this borne in mind, the OFFSET function can now come to the rescue, viz.

In this illustration (above), cells J18 and K18 break the number of days receivable (cell G18) into the number of whole months and residual proportion respectively, assuming that each month has 30 days (cell H13).

The key formula here is the calculation for Closing Debtors (Cash Receipts is simply the balancing figure). For example, the formula in cell J28 (above) is:

=IF($J$18,SUM(OFFSET(J26,1,-MIN($J$18,J$23))),)
+IF(J$23-$J$18<=0,OFFSET(J26,-$J$18)*$K$18)

Venture Capital Training Courses

It may seem a little complex upon first inspection, but it’s not as bad as it seems. Essentially, there are two parts to this formula identified by the two added IF statements:

1. IF($J$18,SUM(OFFSET(J26,1,-MIN($J$18,J$23))),) considers the completed number of months where sales remain outstanding and adds up the sales for these periods. This approach has been discussed previously in detail in the Simple Depreciation Example detailed in my OFFSET article.

In essence, this part of the formula checks that the number of completed months is not zero (in this case the amount is just zero), and assuming this is not the case, it sums the sales for the relevant number of completed months (i.e. starts with the current month and then considers the sales in previous months, working from right to left in the spreadsheet). The MIN formula is required to ensure that the model does not try to include periods prior to the beginning of the forecast period).

2. IF(J$23-$J$18<=0,OFFSET(J26,-$J$18)*$K$18) considers the residual (remaining) amount for the month before the earliest completed month. For example, if the credit period is 2.5 months and the current month is April, then March and April will be “whole months” where no payment has been received, with half of February’s monies still outstanding too.

The reason for the IF statement here is to prevent calculations considering periods before the beginning of the forecast period.

To clarify, consider the Closing Debtor figure of $1,050 in Period 5 (above, cell N28 in the illustration). This is calculated as the sales for Periods 4 and 5 (400 + 500 respectively), plus half of the Period 3 sales (300 x 0.5 = 150), i.e. 400 + 500 + 150 = 1,050.

It only gets more complicated. What if payments are not made evenly? Or that some sales are written off as payments are never made (i.e. bad debts)? Actually, we have covered this scenario in a previous article.

Think about it: depreciation arises when cash is laid out in one period, but the costs are allocated over multiple periods. Debtors arise when sales are made in one period, but the cash receipts are allocated over multiple periods. It’s the same logic, just different labelling!

The calculations can be reviewed in depth in the attached Excel file, but what can be clearly seen is the similarity to a depreciation calculation. In this illustration, the Cash Receipt Profile percentages do not add up to 100%. This is deliberate – the missing 5% is the assumed bad debt here.

Word to the Wise