How to create a cash flow statement in Sage 50

Sage 50 cash flow

What does a cash flow projection consists of.  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 50.

What is a Sage 50 cash flow projection?

A cash flow projection consists of three main parts:

• The beginning cash balances
• Plus, anticipated cash receipts
• Less Expected cash disbursements

Sage 50 Cash Screen Shots

In Sage 50 there is a built-in Sage 50 cash flow statement.

It is found under analysis – under the main task bar and then by selecting cash flow.

You have the ability to select the cash accounts you want to include in the cash flow projection. Once you select them there will be a visible check mark in the include box to the left of the cash account ID.

Displayed will be the account number, description and the cash balance as of today if you are running in real time or have smart posting activated. As you can see, we have four bank accounts with a total available balance of $57,238.60 but will only be working with the regular checking account with a balance of $16,116.52 as of today.

The next step is to specify the time frame to be included in the projection found in the upper left corner of the window.

We will now work with the file tab expected incoming cash. Most businesses will use the current outstanding customer invoices as the source of the expected cash receipts section. This information is from the Sage 50 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.

By default, most of these invoices will be select to be included in the projected cash balance. However, as you can see there are a few invoices with a date displayed in red. This indicates they are past the due date of expected payment. To include them as an exception, edit the date field and modify it to have an expected date after today’s date. And then use the include column to add it to the calculation.

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 and perhaps customer deposits.

To add a what if for other types of expected incoming cash you will use the adjustment in the type field,

Enter a value in the code number, id and name, date and amount fields and be sure to use the include column to add it to the calculation. In this example we have added an expected deposit on a project from a client of $15,000.

Next, we will work with the file tab expected outgoing cash. Expected cash disbursements for most businesses come from two sources:

1. The outstanding vendor invoices
2. Payroll and related expenses such as taxes and benefits

This information is pulled from the Sage 50 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.

If you need to add a what if for other types of outgoing cash you can also add it to this file tab as an adjustment in the type field, Enter a value in the code number, id and name, date and amount fields and be sure to use the include column to add it to the calculation.

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.

Under the bar – change cash flow settings

You have the option for open transactions or historical transactions and in the lower portion of the window you can specify the types of transactions you want to use for incoming and outgoing cash transactions.

For Sage 50 Cash Flow – modify the time frame you wish to work with

You can calculate this based on the average days to pay or transaction due date. Once you are finished with your decisions use the update cash flow bar to refresh your balances.

Exporting Sage 50 cash flow to Excel

– use the excel icon on the upper task bar. Once this is started You will now see options for your export.

You can create a new excel sheet or choose to add to an existing sheet.

You can choose to use freeze panes and define how the header will be displayed.

Once this is complete an excel worksheet will automatically be created with the following tabs.

You can view, save and print these. Let’s look at the starting cash file tab which lists each account along with the beginning cash balance for each account.

Under the file tab Expected Incoming Cash is a list of balances by customer by invoice of 87,197.51

Under the file tab Expected outgoing cash is a list of balances by vendor by invoice of 28,374.68

To review the summary file tab

The starting cash balance is $16,116.52 as of today.

There is a total of +87,197.51 expected incoming cash included

With a total of –28,374.68 expected outgoing cash included

With a projected cash balance of =74,939.35

This format of this report can be easily modified using your excel skills and then shared with others.

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.

If this is a format you wish to return to you can save your selections and the what if scenarios that have been identified for later use.

Thank you for joining us to learn more about creating a cash flow statement in Sage 50. Accounting Business Solutions by JCS provides sales, installation, integration and training services for Sage 50 Accounting Software as well as technical support and custom reports. 800-475-1047. If there is anything we can do to help give us a call.