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.
Creditor Days and Debtor Days
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
[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
[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).
Fig 1: Example Payment Profile with Creditor or Debtor Days equal to 50
Note: For illustrative purposes only and may not be to scale.
The Payment Profile
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:
Invoices are added at all periods throughout the month with the median being the middle of the month. This is why the graph starts in the middle of the current month
Each month has 30 days. As a result 30, 60 and 90 days is the middle of each month following.
Some invoices are paid early and some are paid late and this results in a normal distribution around the mean or average days count (50 in the case of Fig 1).
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.
Calculating Cashflows with the Payment Profile
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:
40% of 2000 = 800
30% of 1500 = 450
30% of 1000 = 300
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.
Note: In actuals periods the Raw Cashflow values are grey as these are theoretical values only. They do not represent actual cashflows.
Raw Unadjusted Cashflow
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.
The 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).
Two Sales - Raw Unadjusted Cashflow
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).
Two Sales - Adjusted Cashflow
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.
First of all we calculate an adjustment ratio (Cell G78 highlighted in green) which is calculated as [Debtor Closing Actual] / [Debtor Closing Expected].
This adjustment ratio is then applied to all the cashflow amounts that are a factor of Actuals periods. The adjustment ratio is not applied to cashflows calculated as a factor of budgeted periods. In this example there are 6 cells that have the adjustment ratio applied (Cell H66, H67, I67, H70, H71, I71).
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.
Discussion about the Adjustment Ratio
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.