Download the 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.

This tutorial explains the funding and modelling of working capital and linking capital calculations to the financial statements.

KEY LEARNINGS

  • Funding of working capital
  • Modelling working capital in a model
  • Common mistakes in modelling trade debtors and creditors
  • Variation in modelling working capital

Download instructions

Shortly after you complete the form and click the “send me the workbook” button, you will receive an email containing the link to download the Excel workbook.

Download Excel Workbook

  • Mazars needs the contact information you provide to us to contact you about our products and services. You may unsubscribe from these communications at any time. For information on how to unsubscribe, as well as our privacy practices and commitment to protecting your privacy, please review our Privacy Policy.

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.

How to model the working capital

Screenshot 1: Modelling working capital – calculated per period working capital adjustments

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.

Screenshot 2

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 Academy: Corality Financial Modelling Campus

There are numerous other tutorials and free resources related to financial modelling in the Corality Financial Modelling Campus.

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