Analyzing cash flows and payments in Advanced Accounting

 One of the financial statements that has long been available in the Advanced Accounting series including its predecessor versions is "Changes in Financial Position (Cash Flow)" sometimes referred to as "Sources and Uses of Cash" (or sometimes the word "Funds" instead of "Cash"), or simply as a Cash Flow Statement.

TAS-Books* 1987

TAS-Books* 1987 manual - Cash Flow Setup

Settings for the cash flow statement have typically followed the same approach as for the other two financial statements (balance sheet and income statement) but with with inclusion of an income/expense and non-cash expense range along with asset and liability sections.  When setting up a GL chart of account, the program has always had a "Non-Cash" flag, the sole use of which is in connection with the cash flow statement.  And this continues to be the case in all versions.

Books 2.0 and Advanced Accounting 3.0 looked and worked substantially the same as in TAS-Books* version, continuing with Advanced 4.0, 5.0 and 5.1. Graphical printing was added in version 6.0.  In 2010 in the 7i version, the suite of financial statements were "re-made" into a fuller graphical version with some additional new features.  The cash flow statement logic however remained basically the same through version 7i.  Starting with Adv 8 (2019 release) however it has been significantly changed.

Advanced Accounting 3.04 Cash Flow Setup

Advanced Accounting 5.1 Cash Flow Setup

While the Advanced Accounting cash flow statement option historically could be used to help predict cash flows and if run correctly could produce an accurate net cash change, it fell somewhat sort of performing as a true cash flow statement.  Using the so-called indirect method of preparing a cash flow statement is really sort of an upside down balance sheet with a twist:  it involves comparing a set of financial account values to some prior period (usually the immediately prior year, quarter, or month).   This was accomplished in past versions by a from to through month approach, and comparing the difference in the net balances in established ranges from the balances at the end of the from month to the end of the through month provided that the user did not "include beginning balances" but with no "proofing" of the change value computed to the actual increase/decrease in cash.

The idea of a cash flow statement is simply to show the increase or decrease of "cash" accounts (checking accounts, actual cash in a cash drawer or petty cash fund, money market accounts, merchant accounts, deposits in transit not yet delivered to the bank,  certificates of deposit, savings accounts and the like) from one period to the next.

The standard cash flow statement using the indirect method can also be thought of as a sort of a crude accrual to cash conversion starting with net income for a selected prior of time, making adjustments based on the differences in usually a prior balance sheet period, leading ultimately to a computed increase or decrease in cash relating to operating activities.

Cash flows from investing and financing activities are also normally identified in a cash flow statement.   This has not been the case with the Advanced Accounting software cash flow statement option, and we have elected to continue to combine those activities into the operating section for now since these are not normally materially significant activities for most of our users.

Since cash flow statements can be somewhat unintuitive to setup properly and confusing at times to read, we have taken steps to try to simplify the setup and to also make our own changes to the format which we think helps to better focus on understanding the increase/decrease in cash from one period to the next.  Further, now, the user cannot cause erroneous values to be computed if they choose "include beginning balances."  Instead, depending on that selection, the Adv 8 version produces either a "net balance" cash flow analysis as it has in the past (but with a new cash summary at the end with actual beginning and ending balances) or a "full balances" method that compares the difference in balances between two different time periods (the only complication with this approach is that an adjustment for the change in retained/current earnings between the two periods has to be made in order to accurately compute the cash change).

Advanced Accounting 8 Cash Flow Setup

Most users in fact would do well to simply always include in their balance sheet and income statements a "one year past" or similar comparison column with a third column that shows the dollar (and optionally percentage) differences.   Appropriately formatted side-by-side balance/income statements with a third "difference" column that Advanced Accounting has always provided are not only easy to read but include all of the information normally that a financial statement reader might require and if correctly formatted, clearly shows the increase or decrease in cash flows.   To some extent the cash flow statement then becomes largely redundant.

However in order to improve the current option, we have significantly redesigned how it works (but remaining in the same place as it always has been):

• Income/loss is automatically calculated just like it is when printing a balance sheet and so the income/expense range in the setup option has been eliminated - this also solves issues relating to income/expense accounts not all having been establish in a contiguous range
• A new "cash account" range in the setup has been added so that a full range analysis with or without detail can be provided at the end of the statement
• Some additional on-screen help has been provided in the format setup to assist with establishing initial settings
• When generating a cash flow statement, one of two entirely new report layouts are used that utilize different logic.   As in the past, the user can choose the from/through months and whether to include beginning balances (in older versions, that option should not be selected)
• A new option allows the user to include detail or not.  Normally a cash flow statement is summarized and doesn't include the detail, so by default, "include account detail" is not checked but can be chosen for a higher level of analysis.  This is different than how the income/balance sheet options normally work (they always provide the detail).
• Beginning and ending cash and other balances are provided in one of two different formats depending on whether the user is included balance forwards or not.  One of these is in a column presentation similar to how they would be viewed in a side-by-side balance sheet comparison which we believe makes them easier to understand.  The other includes not just beginning and ending balances but also the actual change in each account which can be displayed on a detail basis.
• In the event the cash flow statement is out of balance (i.e. the net actual cash/increase differs from the cash account range) when choosing include beginning balances, the user is notified at the end of the report with some possible causes for the out of balance
• We have intentionally, for now, limited the cash flow report to two years' worth of columns (plus a third different column if "Include Beginning Balance" is checked that is automatically generated without user interaction).  Income statements/balance sheets continue to be able to have any desired combinations of current to six year past amounts in up to four columns and the ability to specify a difference column.  In a future release we may provide an option for a 12 month column view/analysis for each of the three available financial statements.

Adv 8 GL-G Print Financial Statements - Cash Flow options

Adv 8 GL-G - new option for all statement types if  Edit Columns/Headers or add ending notes is checked

Adv 8 Cash Flow Statement report+ when "include beginning balance" is not checked:  net balances method

Adv 8 Cash Flow Statement report+ when "include beginning balance" is checked: new full balance method

Cash flows and payments can of course be analyzed in other ways.   Besides the obvious options in the general ledger detail for cash receipt and cash disbursement types by posting date or general account, customer payments can be analyzed both in the credit analysis report option as well as in the accounts receivable aging "as of date balance/transaction type history" where the output  can be limited to payments (and in the latest version, pay types can be further limited to one of six different types including checks, credit cards, cash, e-transfers, other and refunds).  Point-of-sale (POS) users of course have the additional POS report options that includes on specifically for payments and that can be narrowed down to not just date but also time and payment type.  On the vendor side, in addition to the accounts payable payment history option (which can include, exclude or only include electronic payments), the AP aging "as of date" option can be used to show only type "P" payments to determine the actual amounts paid to all or a given vendor for a desired date range.

*TAS-Books originated in the United States.  It was co-opted for use overseas and evolved into a completely separate product that was never officially licensed by the original publisher (Business Tools, Inc.).  TAS-Books was developed using the TAS-Plus (TAS+ 2.0) programming language also developed and published by Business Tools, Inc.  Later versions based on the TAS Professional 3.0 language were called Books and Advanced Accounting (which first included multi-location inventory tracking, among other things).   Since version 4.0, the accounting software has been referred to solely under the Advanced Accounting name/trademark but long-time users still sometimes refer to it as "Books."   The data files and program names going back to the TAS-Books version were consistently named starting with the letters "BK" (for example BKSOA.RUN for the sales order runtime program, BKARINV.M for the sales order header data file, later BKARINV.B) referencing the product's early beginnings as "Books."


+The data in the report samples is test data and not based on a real, balanced system, and is being shown solely to show what these reports look like with summarized data.  If the detail option was selected, then the individual account detail within each section would be displayed.

Btrieve status code 46

Your users may occasionally experience a Btrieve engine (which here includes the Pervasive and now Actian engines sometimes also referred to as PSQL) status code 46 which indicates that access to a given file ("table") is denied leading to the inability of the software to write to that file.   There are some straightforward reasons for this but also several that are non-intuitive.

A file that is marked as read only whether via user rights or because it was dragged/dropped from media that is read only is the most obvious cause, although usually isn't the cause.  Ensure that the user has both read/write rights (and since in some versions temporary files are created, the user needs delete rights to data folders as well).   Most commonly this happens when a new client PC is added to a network and the user's rights are insufficient.

 

File forced to be read only and the result when that file is attempted to be written to

 
Another issue could be an older formatted Btrieve file.  As users move forward to newer versions of the software and deploy newer Pervasive versions, some of their files may be in an older format that starting with Pervasive 9.x (now considered legacy since the latest version currently is v13 released in 2017), Btrieve 5.x and older files can be read but not written to until they are rebuilt (or "reindexed") into a newer format (6.x or above but 9.5 is the usual standard even with later releases).   A rebuild utility is provided with the Actian/Pervasive engines and is the best way to resolve.  Our reindex program, as originally developed by Business Tools, can globally identify the format level of a user's files in a given company.

Older format causing status code 46 format determined via a BUTIL -STAT

Another possibility is the use of an owner name where an incorrect owner name is passed when the file is open.   While the underlying programming language supports owner names, owner names are normally not used in Advanced Accounting programs, and so this cause can normally be ruled out.

A mixture of Btrieve 6.15 and Pervasive/PSQL engines can lead to a status code 46.   The two engines cannot be used to access the same data files simultaneously.    Different PSQL various normally can be used safely together although it is best that all PC's use the same version.

The most difficult situation involves outside applications or processes that may be scanning,  copying or indexing the data files. We previously made brief mention of status code 46 issues with respect to online or live database backups and these are the the most common cause.

This issue has been known for sometime now but can be very difficult to troubleshoot, and it can lead to significant loss of data should it strike.  Typically we have something like four or five support calls every year where the end user has lost data as a result of a status code 46 issue, whether observed as such or not.  

The peculiarity relates to how the Btrieve API reacts to a file that is being copied or scanned and when it then tries to open that file while no other instances of that file are open by anyone.   If the file that is being copied or scanned was already open by the Btrieve engine on some client/workstation prior to initiation of the copying or scanning, there is no issue.   But if no user has the file open, and the copy or scan begins, and then the file is opened through say the accounting sofware, the Btrieve/Pervasive engine opens the file in a read only mode, and  that is not easily detectable.  Only later when typically a record is attempted to be saved to that file will the 46 result and usually in the middle of a posting process.  The user at that point is able to click on OK and continue.  And continue.  And each time OK is pressed, a record in that file is effectively "lost."  And once this starts to happen, a domino effect occurs to other users who try to access the file and write to it, and the 46 status remains until all instances of the file are closed (even though the copying or scanning process may have long since completed).

It is larger files that take longer to copy or scan that are the most vulnerable, and these often are also some of the more important files.  If all files were all opened all of the time, this kind of status code 46 could never  happen (placing all files in continuous operations mode avoids the problem for backup purposes, but places a heavy overhead on a system).  Keeping all of the files open all of the time however is normally very inefficient and isn't what happens in a typical Advanced Accounting program.  Files instead that are needed in a given program option are instead opened at the beginning of that option and they are automatically closed when that individually selected program option ends (or can be closed earlier programmatically).  This normal internal structure combined wiht the behavior of the Btrieve API can, albeit under somewhat rare circumstances,  lead to situations where no files are opened by anyone and then suddenly an online backup or anti-virus scan of some kind starts just before a user than starts a program than opens that same file, and it if is a file that is later written to or deleted (such as in a posting program), status code 46's can result.

Anti-virus software has been suspected of triggering this issue.  Your data file folders should be excluded from real time anti-virus scanning (data files cannot become infected, and  this is something that is desirable in any event because it will improve performance).  In our experience, the 46's have always been tied to some sort of backup or copy operation that was in progress when a user then unknowingly selected a posting program that opened the same file that was being copied, with no prior instances of the file having been opened by a Btrieve engine.

For one user that was having a problem when copying data files off late in the afternoon to laptops and another that was having nighttime backups taking longer and longer and were still running in the early morning hours (including 5.1 versions in fact), we added some logic when records were being saved to detect this issue.  But that doesn't prevent the problem in the first place and further when it happens, the user can't usually easily resolve the problem on their own.  Therefore we have recently implemented some new logic where we test the most exposed/larger files that are going to be written to in advance to detect if the file is being copied or is being accessed in some manner from outside of the software (and if the copy started before the file was opened by any accounting software user).   We have implemented this newer logic in the SO-G posting program in the latest versions of Advanced Accounting, and will be adding it to others. 

Note that when posting sales orders on the fly, the AR invoice header file (BKARHINV) is already open in Advanced Accounting because it is needed to check for previously assigned invoice numbers.  So when that sales order is then posted right after printing or a print preview, a status code 46 would never result but rather only when posting sales orders in a batch (normally a good thing).  The general ledger transaction file (BKGLTRAN) in either case however would still be vulnerable and that is a file that we also "pre-check."

How can you avoid status code 46's in the situation as last discussed above? 

 (1) Exclude data files from being scanned in real-time.   As noted, it can actually be helpful to have other users utilizing the software keep certain files open during posting processes that can, counterintuitively, help with  this access denied status code (although they can create other problems by locking customer or inventory location records).

(2)  Use of continuous operations mode can also help to avoid the issue.  But most importantly, ensure that scheduled backups are taking place well outside of normal operation times and monitor them to ensure they don't start to creep into the early morning hours.

Note: if you are making a manual backup copy of a file on a system that is live and that is fairly large in size and will take more than a few seconds to copy, it is actually safest to first open that file (either in maintain database or in some program that you know opens it)  prior to making the copy.  Otherwise your file copy request, if the file is large enough, could cause a live user to experience a status code 46.