Sage 100 Cash Flow
How to create a cash flow forecast using Sage 100
Sage 100 Cash Flow – This report may be required if you are looking at additional financing for your business or need to report to a board of directors or potential buyer. It is also useful for anticipating future cash shortfalls. After reviewing what makes up a cash flow projection, we will go through an example using data from Sage 100.
A Sage 100 cash flow projection consists of three main parts:
In Sage 100 we have developed a crystal report that allows the selection of a single, range or all cash accounts.
We then define the end date of the first period as well as, the number of days included in each period.
We can then specify summary, detail or none for both vendors and customer balances to be displayed and included. In addition, there is also the choice of using the invoice date or the due date for the period the balances are categorized in.
For purposes of this demonstration we will be using the ABC demo data in Sage 100.
The beginning cash balances can either be found in the general ledger or, if you are using the bank reconciliation module, from the bank code table.
The first piece of data we need is the current cash balances. For this we will go to Bank Code Maintenance in the Bank Reconciliation module. As you can see, we have four bank accounts with a total balance of $5,722.57 as of today.
Sage 100 Cash Flow
Most businesses will use the current outstanding customer invoices as the source of the expected cash receipts section. If the business has another source of cash that is not showing up in the current receivables this should also be included. Examples of these could be rents, royalties, or any kind of investment income.
This information is from the Sage 100 customer open invoice file. For this example we will assume that we receive the cash on the due date for the full amount of the invoice. The amounts are spread out into seven weekly periods with the last column for any amounts due past the seventh period. In this example we have chosen to display summary customer balances from Sage 100.
Expected cash disbursements for most businesses come from two sources:
A) The outstanding vendor invoices
B) Payroll and related expenses such as taxes and benefits
As we mentioned with cash receipts, if there are any additional cash requirements not listed above, they should also be included. Examples of these may be loan or line of credit payments, recurring and automatic payments from your bank accounts.
This information is pulled from the Sage 100 vendor open invoice file. Like the customer invoices these are assumed to be paid on the due date in the full amount and are spread out over the same periods.
In this example we have chosen to display summary vendor balances from Sage 100.
As you can see from the summary at the bottom, each period shows the cash balance at the start of the period, then adds the cash receipts and subtracts the cash payments to arrive at the cash balance at the end of the period which is then carried over to the beginning cash balance for the next period.
Discounts are one additional consideration which may affect the amount of the receipt or disbursement to be expected. The invoice balances already recorded would generally be used for planning but if you offer early payment discounts to your customers or if your vendors offer them to you, these should be considered if they are normally taken or given.
Lastly the timing of the receipt or disbursement need to be addressed. Every invoice should have a due date associated with it. However, as mentioned previously, early payment discounts may provide enough incentive for someone to pay earlier than that date. In addition, although an invoice may have a net 30 days terms associated with it, it is not uncommon for the average time an invoice is paid to stretch a little. Most systems will keep track of the average days to pay by customer and vendor. This number, added to the invoice date, can provide a more realistic model if utilized.
As we mentioned this is a simple scenario but can be modified to use the average days to pay rather than the due date or consider early payment discounts by showing payments due by the discount date.
