Cash Forecast Reporting with ICRW

• July 11, 2023
• Kathy Jastrzebski

Predicting cash flow requires a combination of information: prior year trends, accounts payable bills due in the future, due dates for accounts receivable invoices, and general ledger cash account balances.

The Interactive Custom Report Writer (ICRW) allows you to combine reporting areas that might otherwise require running multiple reports and exporting data to Excel or some other data analytics tool. In this blog we’ll walk through a few sample ICRW reports based on combined reporting areas to give you a better picture of cash projections.

Starting with what goes into cash projections?

Prior year trends

• Analyzing prior year revenue is a good starting point to predict months of high sales. For example, implementations tend to trend upwards at the end of each year with clients often preferring a 1/1 go-live.
• Prior year expenses can also predict this year’s expenses. For example, software renewals which are often monthly or annual expenses, conferences that always occur at the same time each year, utilities that increase in summer due to the heat, etc.

Accounts payable bills

• Reporting on AP Bill due dates allows you to better predict what cash will be expended in the future and when.

Accounts receivable invoices

• Being able to see when invoices are due allows you to forecast when cash should be incoming from customers.

General ledger cash account balances

• The General Ledger would include not only current cash balances but would also capture changes to cash that might occur outside of AP or AR such as payroll.

Gathering this information would require pooling three areas of Intacct: General Ledger activity, AP Bills, and AR Invoices both by their due dates.

How can ICRW help?

Using SQL in ICRW, the below report combines multiple reporting areas to view cash activity to date for the year as well as cash forecast for future months based on AR and AP activity.

What information can be gleaned from this report?

• Year Filter – At the top left, the report shows us it is filtered for 2023, but we could easily toggle to show 2022 or any prior year and analyze historical cash trends.
• GL Activity column shows us all posted cash receipts, AP payments, and even JE’s that have been posted to the cash account.
• Payroll column shows us monthly payroll trends.
• AR invoices column we see all open AR invoices sorted by their due date. We have a large AR balance that will become due in December. There are a few that remain open with May and June due dates. These are likely past due and we should make sure they are being collected on.
• AP Bills Due column shows any posted AP Bill sorted by when it is due. The balance in October is higher than most other months, perhaps an annual software subscription renewal will be due.

What else can ICRW do?

Combining multiple reporting areas is just one of the unique features of ICRW. You could add on to this report to show a corresponding data table with the AP Bills that make up a certain month’s amount or AR Invoice amount, respectively. In the below screenshot, clicking into December 2023 in the left-hand table, shows us that only a single invoice makes up the $78,250 due and the $15,000 AP Bill is to Intacct.

The above report also contains formatting that is customized (CLA brand colors!), but of course you could use your own company’s theme colors.

What’s next?

In our webinar on June 27th (recording here) we get you started with navigating ICRW and show you how to build your own AP Bills Analysis report hands on.

Already have the ICRW subscription, but haven’t delved into combining reporting areas, a great starting point is this SQL example provided in Intacct’s Help Center. Interested in learning more about what is possible with ICRW? Or have a specific report you’ve always dreamt of having in Sage Intacct? Reach out to your CLA Sage Intacct team!

 

Tags: ICRW, Interactive Custom Report Writer, Reporting | 1 Comment »