Corality
View More Content
SHARE

Working capital – financial modelling of trade debtors and creditors

Working capital – financial modelling of trade debtors and creditors
CategoriesTutorials
Download Excel Workbook

It is important to recognise the trade debtors and trade creditors in a cash flow financial model because they capture the cash cycle of a company. This is important since not all revenue earned in a given period is received in the same period, and that not all costs are paid as soon as they are incurred.

Trade debtors represent cash amounts due to be paid by customers who have purchased goods/services from a company. Fewer debtor days means that cash is being received faster from customers.

Trade creditors refer to customers or suppliers to whom cash is owed. More creditor days means that cash remains in the company for longer.

Funding of working capital

Managing the day-to-day operating cash cycle is important for every business, since it ensures a profitable operation. If a business pays its creditors before it receives payment from its debtors, then short term working capital constraints need to be resolved.

Means of funding can include:

  • Cash/saving account
  • Overdraft facility
  • Working capital facility
  • Short revolving credit facility
  • Other types of debt finance


Modelling working capital in a model

Key variables

The key variables in modelling trade debtors and trade creditors are:

  • Trade debtors
    • Variable 1: Revenue
    • Variable 2: Debtor days
  • Trade creditors
    • Variable 1: Costs payable
    • Variable 2: Creditor days


How to model the working capital

The most transparent and efficient way to model working capital in a cash flow model is to calculate per period working capital adjustments. The debtors adjustment is the difference between revenue receivable and revenue received, while the creditors adjustment is the difference between costs payable and costs paid. Screenshot 1 illustrates the calculation.
 

Modelling Working Captial
Screenshot 1: Modelling Working Captial

 


Income statement: The revenue receivable and costs payable should be linked directly to the income statement.

Cash flow waterfall: The revenue receivable and costs payable from the income statement are linked to the cash flow waterfall. Then, the working capital adjustments are added to the line before cash flow available for debt service (CFADS).

Balance sheet: Trade debtors are usually recoverable within one year, while the trade creditors are usually due within one year. Trade debtors will be entered into the current assets, below other asset items which are more liquid (such as cash, debt service reserve account, etc.). Trade creditors will be entered into the current liabilities.
 


placing-working-capital-in-the-balance-sheet
Screenshot 2: Placing working capital in the Balance sheet



Common mistakes in modelling trade debtors and creditors

Complexity: As shown, modelling trade debtors and trade creditors can be done in a transparent and efficient way. Overcomplicating these calculations with a ‘one line approach’ can easily hide any mistakes that are potentially made, and thus damage the integrity of the whole cash flow modelling exercise.

Incorrect linking back to the financial statements: There can be problems with models where the timing resolution changes half way through the model – this can be solved by laying out the number of days for each period (as illustrated in screenshot 1).


Variations in financial modelling of working capital

Next period receipt/payment: In this method, it is assumed that the revenue is received in the next period, and the costs are paid in the next period.

Percentage in Period N, N+1, N+2: This method calculates the revenue received/costs paid as a percentage of revenue receivable/costs payable in Period N, N+1, N+2. 
 

Corality Training Academy - SMART CAMPUS

There are numerous other tutorials and free resources related to financial modelling in Corality's SMART Campus.

Some of the more popular courses that relate to this topic include:

Financial Modelling Techniques for Valuation Analysis

Financial Modelling for Renewable Energy Projects.

Download Excel Workbook

Rickard Wärnelid
by Rickard Wärnelid

Rickard's passion for financial modelling is built on specialist roles in the highly quantitative fields of derivatives and project finance, a career path complemented by an academic grounding in engineering physics. Born in Sweden and with global consulting and leadership experience, Rickard is an internationally recognised authority, speaker and thought-leader on the organisational benefits of best practice financial modelling.

Contact Rickard Wärnelid

view all