This help note is not a configuration or setting guide and assumes you have a general understanding of the Calxa cashflow settings and terminology. For assistance with preparing a cashflow forecast you should refer to this step by step guide. If the background calculations do not interest you and you are happy to leave that to Calxa, then this help note is not for you.

Calxa really takes the complexity out of calculating cashflow forecasts and one of the key calculations is the way your debtor income and creditor expenses are calculated and represented both in the cashflow forecast and balance sheet forecast. One of the common questions however is:

How are these values calculated?

You will find this support note useful if you’ve asked the above question and really want to understand the inner workings behind your forecasted debtor receipts and creditor payments.

Each account in Calxa is applied a default cashflow setting. For the most part this is Creditor Days for Expense or Cost of Sales accounts and Debtor Days for Income accounts. With these cashflow settings Calxa uses standard accounting ratios to create payment profiles that are applied to the Creditor Days and Debtor Days accounts.

Creditor Days is calculated as:

[Trade Creditors] / [Creditor Expenses] * 365

Where:

[Trade Creditors] Equals the combined closing balance at the Last Actuals Period for all accounts nominated in the Default Accounts screen as Trade Creditors.

[Creditor Expenses] Includes all accounts where the Cashflow Setting indicates Creditors apply. In general this is expense accounts that have a cashflow type set to Creditor Days or a Profile that is not set to 100% current. This group is then calculated as the sum of all movements in these accounts for the 12 month period ending the Last Actuals Period.

Debtor Days is calculated as:

[Trade Debtors] / [Debtor Income] * 365

Where:

[Trade Debtors] Equals the combined closing balance at the Last Actuals Period for all accounts nominated in the Default Accounts screen as Trade Debtors.

[Debtor Income] Includes all accounts where the Cashflow Setting indicates Debtors apply. In general this is Income accounts that have a cashflow type set to Debtor Days or a Profile that is not set to 100% current. This group is then calculated as the sum of all movements in these accounts for the 12 month period ending the Last Actuals Period.

An important note about the Debtor Days or Creditor Days calculation is that it is heavily dependent on the current outstanding balance of your Debtors or Creditors. If the closing balance each month fluctuates then your calculated days count will also fluctuate. To mitigate this problem you should use the Debtor and Creditor Days overrides in the Default Accounts screen. This way the day count that you choose will be applied to all accounts with the Creditor or Debtor Days cashflow setting.

The end result of either of these formulas is a day’s count which is then used to create a payment profile. The payment profile is then calculated as a normal distribution (or bell-shaped curve) where the resulting days count is the mean (centre of the curve).

Note: For illustrative purposes only and may not be to scale.

The example above shows a payment profile that has been calculated from a day’s count of 50. The following assumptions are applied when calculating the payment profile:

Calculating the area under the curve for each of the monthly segments provides the Payment Profile. So in this example we expect 0.31% of invoices issued to be paid in the same month, 33.04% to be paid in the following month and so on through to the 3rd month.

Note: Premier users may create custom payment profiles on each account. In the rest of this support note we will refer to the payment profile. Whether this profile is manually created or auto calculated as explained above, the resulting profile is all that is applied in the cashflow calculations.

Please download this Excel Workbook titled “Debtor Example”. We will follow a Sales and Debtor example but you should be aware the exact same principles can be applied to Creditors. As we work through the calculations, you should follow along with the workbook's sample data and included formulas.

Note: For simplicity we have excluded GST (Tax) in this example, but in reality GST is also being factored and added to cashflows according to each account's Tax Code setting.

Let’s set the scene. There are two sales accounts; Sales A and Sales B. Each has a separate payment profile noted at the top of the worksheet. Throughout the workbook we will calculate the cashflows associated with a Sales Base for each of these accounts. In periods prior to and including the Last Actuals Period (Feb) the Sales Base is actual sales and in periods after the Last Actuals Period the Sales Base is budgeted sales.

Using the Sales A Base values and the Sales A Payment Profile we can multiply out the cashflows for the current month, 1 month and 2 months.

Let’s break down the March Sales A Raw Cashflow amount of 1550. This is calculated as:

The same concept is applied throughout the rest of the cashflows. Using these cashflows we can also calculate a debtors movement and a therefore a debtors closing balance.

In a perfect world where everybody pays exactly as the payment profiles suggest then; [Debtors Movement] = [Sales A Base] - [Sales A Expected Cashflow]. In reality though it is not a perfect world and the Payment profiles do not match exactly what occurs with your Debtors Movement. So in this example we are showing that; [Actual Debtor Movement] in Feb = 700 (Cell G25 highlighted is red). We expected this to be 600 (Sales of $1,500 less receipts of $900), but for whatever reason it just didn’t happen that way. People did not pay as expected and we now have a 1300 closing balance on debtors as opposed to 1200.

This example shows that without adjusting the cashflows we now effectively forget about this difference of 100 and we will never collect it in a cashflow forecast. If we cease trading and don’t budget sales beyond May we should expect to collect on all outstanding Debtors. This doesn’t occur because we have not adjusted for the variance in Debtors movement in the actuals period (Cell N26 highlighted in red).

This example is setup in exactly the same way with a 100 variance in Debtors for March. While this example only includes 1 account it is easy to see that the 100 variance is attributed to Sales A. In a real world example there are more than likely many sales accounts. Calxa does not store invoice or transaction level detail but rather analyses the monthly movements on accounts to calculate a statistical cashflow forecast. So if we don’t know about specific transactions how do we adjust for the debtor variance and on which accounts? This example will show how we employ an adjustment ratio.

You will note there is a Debtor Adjustment Ratio (Cell G42 highlighted in green) which is calculated as [Debtor Closing Actual] / [Debtor Closing Expected]. This adjustment ratio is then applied to all cashflows that are calculated as a factor of a Sales Base Actuals period (Cells H34, H35, and I35 highlighted in green). It is only applied to these values because the variance is in the actuals balance and we therefore need to adjust in the actuals periods only.

Following the rest of this example you see we have now adjusted for the debtor variance and if we cease trading and don’t budget sales beyond May we now forecast to collect all of our debtors (Cell N38 highlighted in green).

Extending on what we have shown with one sales account we will now look at two sales accounts. Using a similar example, there is a variance between the actual debtor movement and the expected debtor movement (Cell G56 highlighted in red), except this time as discussed earlier we really don’t know which sales account the variance is related to. Just as with the original example if we don’t adjust the cashflows then we will never correct the variance in the actuals debtors balance (Cell N57 highlighted in red).

Whether you have one sales account, two sales accounts or many sales accounts the exact same logic is applied to adjust the raw cashflow amounts.

As you can see the adjusted cashflow figures now wash out the debtor variance and if we stop budgeting beyond May, all debtors are collected.

Under normal circumstances the adjustment ratio will be quite small (close to 1), however if your cashflow settings do not match reality, your cashflow forecast may yield strange looking results, such as large inflows or outflows in the first few months. For example, if all your income accounts have a custom profile of 100% in month 1 or you override the debtor days value in the report criteria to 30 days, then through the cashflow settings you are telling Calxa that all your outstanding debtor income will be collected in the first month. Another way to look at it is that you are telling Calxa your forecasted closing debtors for the current month will be whatever you budget for this month. Imagine your average monthly sales are $50,000 but your outstanding debtors are $100,000.The first month in your cashflow will collect the full $100,000 and you might be asking why your inflows are so high when you only did $50,000 sales last month. The problem here arises because the cashflow settings don’t match reality. It is not realistic to say you collect all your income in the first month if there is $100,000 in outstanding debtors but you only invoiced $50,000 last month. Generally this problem will not occur if you are using the default cashflow settings. Have a look at this FAQ for a guide on resolving this type of problem.

Another thing to understand about the adjustment ratio is that it is accounting for values that sit outside the payment profiles by adjusting all cashflows, not just the account that has the outstanding invoice. The intention of this type of adjustment is to make your overall inflows or outflows as accurate as possible but not to highlight exactly which account has outstanding invoices. This is most noticeable if you have a large transaction and you are expecting to see this large transaction in the cashflow show up on a specific account. Now let’s imagine this large income transaction ($100,000) was invoiced two months ago and we have similar cashflow settings to above, where everything is set to 100% in month 1. The outstanding amount will not be picked up by the raw cashflow values because it is outside the 100% month 1 scope. Instead the adjustment ratio will now pick up the variance in the debtors balance and be applied to all debtor inflows. It is important to understand that the $100,000 is still accounted for, but the inflows are now spread across all your debtor income accounts instead of the one account.