Cannot prepare a proper income statement

dave344

I cannot prepare a proper income statement for 2022. By "proper" I mean one that will account for the changes in my beginning and end of year balance sheet accounts.

There were no contributions or distributions of cash or property in 2022 so the net change in net worth for the year should be reflected entirely in the items of income and expense.

I prepare a balance sheet that shows beginning and end of year balances for all accounts. The difference between the beginning and ending net worth (what Quicken calls "Overall Total") equals the net income (also labeled as "Overall Total") of my income statement.

The income statement report is configured to include all transfers and unrealized gains. If there were no contributions or distrubution and everything is properly posted to an account, the net income (the net of the income and expense categories) should equal the change in net worth for the year. It does not.

The problem is that the TO and the FROM transfers (transfers made directly between balance sheet accounts) do not net to zero. So in order to account for the change in my net worth for the year, I need to use my net income plus the excess TO transfer for the insurance account.

But this makes no sense. If I look at the insurance account (a liability account) I see it has a begining balance of 14. There are charges of 22, payments of 36 and an ending balance of 0. All of the transactions are recorded in proper accounts. When I look at the transfers, I see there are cash disbursements of 7 from the insurance account that are properly recorded as the FROM transfer on the income statement. However while there were only payments of 36 from other accounts to the insurance account, the TO transfer is reported as 48; 12 more than can be accounted for. This 12 is the the difference between the beginning and ending net worth that cannot be accounted for by net income.

So my net income is wrong; I have 12 sitting in "TO insurance" that properly belongs to some unknown expense account. The amount clearly does not belong to the insurance account and there appears to be no logic as to why the report writer assigned this amount to "TO Insurance."

How do I troubleshoot this?

Is it a bug? Has anyone else experienced something similar? I have had problems in the past with the report writer, mainly with unrealized gains and losses in investment accounts. But I have not experinced something like this with a basic liability account.

I run the super validation regularly and other than an occassional pricing error, it always comes back clean. I am using Quicken Premier for Windows version R48.15, build 27.1.48.15. The data range is 12/31/19 to 3/24/2023.

Any thoughts would be appreciated.

Jim_Harman

@dave344 I don't have a complete answer for you, but to get the discussion started and avoid confusion, let's be sure we are using Quicken terminology, which is often different from accounting terminology.

In Quicken, "Account" refers to an actual bank, credit card, or brokerage account. Liability accounts are generally loans, and asset accounts are for things you own. Accounts have balances and you can make transfers between them.

"Categories" are for types of income or expenses that you want to track, what an accountant would call "Accounts." Categories have totals over time.

So am I correct that what you call the insurance account would be the Insurance Category in Quicken?

In the Income and Expense by Category report (assuming that is what you are using for your income statement) and Net Worth report there are several settings that would affect whether its Overall Total is the same as the change in your net worth. These settings are accessed by clicking on the gear at the top right of the report.

Income and Expense by Category

• On the Accounts tab, make sure all the accounts where you have recorded income or expenses are included. Only Banking accounts are included by default.
• On the Categories tab, make sure all Categories are selected, including hidden ones. The special Investing categories that begin with _underscores, like _DivInc, are hidden by default.
• On the Advanced tab, you should set Transfers to Exclude internal. This will only include transfers between the included acounts and other accounts

Jim_Harman

Sorry, the formatting of my previous post was messed up somehow.

Also you need to decide whether to include the unrealized gains, if any The setting on the Advanced tab of each report should be set the same.

dave344

Thank you Jim for taking the time to read through a rather complicated post and provide a thoughtful response.

Yes, the "insurance" account is a liability account, not an expense category. It is credited with insurance expense and debited with cash payments.

Both the income statement (created from "Income and Expense by Category" report) and the balance sheet (created from the "Net Worth" report) are configured to include unrealized gains.

The difference between the beginning and ending net worth is 1,000. If I run the income statement to include "All Transfers," the net income is 1,000 which accounts for the change in net worth. However, if I run the income statement to exclude "Internal Transfers," the net income is 1,012.

So without internal transfers, I cannot properly account for the change in my net worth for the year.

There were no external transfers (contributions or distributions) so the entire change should be accounted for by net income. Somehow Quicken is miscalssifying 12 of expense as an internal transfer.

Maybe the more fundamental question to ask is: in what situation would the FROM transfers not equal the TO transfers so that the net internal transfers were NOT zero? Having a net transfer balance of 12 indicates something was misclassified; there is an expense item masquerading as a TO transfer.

So under what situation would it be possible for me to end up with a net internal transfer balance on my income statement? (Keeping in mind there were no external transfers (Bal Fwds) during the year.)

Thanks for any thoughts you may have.

dave344

Jim: I just re-read your response. I double-checked and on both income statement and balance sheet reports all of the accounts, categories, securities, payees, and tags where selected for inclusion.

Jim_Harman

One type of internal transfer to look for would be an opening balance or a balance adustment. These are implemented as a transfer from an account to itself and they have the effect of creating or destroying cash in the account.

Another issue is a transfer that was edited to make it a Deposit or Withdrawal. This can create a broken transfer, where there is a transfer in one account but not the other. I have seen this when an XIn or Xout in an investing account is edited to make it a Deposit or Withdrawal.

To track down the discrepancy, you can adjust the starting and ending dates of the reports and/or the accounts that are included to see when and where the discrepancy first appears. Occasionally you may find a defective transaction, which you can correct by deleting and re-entering the transaction.

Please let us know what you find!

dave344

Excellent suggestion Jim. I prepared income statement and balance sheet reports with an interval of one week. I then looked at the difference between Net Income and the change in retained earnings and it immediately became apparant that the problem was created sometime between December 10 and December 17 2022.

Upon closer examination of the transactions during this period, it became apparant that the problem is the result of a one sided entry.

That may be hard to believe but you can see what I am dealing with quite clearly in the two images I have attached. The first image shows the Amex liability account transactions; this image shows a credit to Amex and a debit to USAA insurance for $1,214.44 on 12/15/22. This is the amount that my internal transfers are off in the income statement.

THe second image shows the USAA Insurance liability account transactions. Notice there is no offsetting $1,214.44 debt transaction against Amex on 12/15/22.

Where is the other side of this entry? When generating the income statement, the report writer plugs the missing USAA insurance debit to internal transfers.

How does this happen? Is the Quicken data file corrupt, and if so, what is the fix in this situation?

I typically run a validation or super validation after backup and no serious errors or problems have been reported. The data file covers only a little more than three years worth of data. Additional information about the data file is shown in the third image

Jim_Harman

Thanks for posting the details. You have what I called a "broken transfer" in my previous post.

I recommend that you back up your data just in case, then delete and re-enter the 12/15/22 transfer in the Amex account.

As I said earlier, you have an unusual account structure. Normally in Quicken, you would not have an Account for each company you do business with; "USAA Insurance" would be a Payee. The information you have put in the Payee column would normally be part of the Memo or Notes.

dave344

Thanks Jim. The account is labled "USAA Insurance" but it's really an expense accrual account. Insurance is billed semiannually and usually paid monthly. So the account is credited when the insurance is billed and debited when payments are made. In this case a payment was made on the Amex card; so the entry should be credit to Amex and debit to accural (USAA Ins). But the debit is missing so the report writer plugs the missing amount to internal transfer.

You're right, I can probably fix the problem by deleting and recreating the transaction. But that gets at the symptom rather than the cause. It took me several hours to find the offending transaction. I can't afford to do that every time my accounts are off. To me it looks like the problem is related to either 1) a bug in the report writer; or 2) a corrupt data file or possibly 3) a flaw in Quicken's data storage architecture. I have opened a ticket with Quicken support and I will leave the file as it is until I have had a chance to discuss it with them.

This is a big deal because right now I have an accounting system that can't be relied upon to provide accurate information. I'm not sure how prevalent this problem might be, but one simple indication that your system has a problem is to run a "Net Worth" report with all accounts, categories, securities, etc. incuded. Also include unrealized gains & losses. Compare the net worth amount in the report with the net worth at the bottom of your "Accounts" column on the left hand side of the Quicken App. If they're not the same, you probably have a problem.

Jim_Harman

You are right that a discrepancy between the Net Worth report and the Accounts Bar is a good indicator of a data problem. There are also situations that will cause a discrepancy between the Account Balances and Net Worth reports.

The original problem you saw, a discrepancy between the change in net worth over time and the Income and Expense by Category report for the same period, would also indicate a problem.

Fortunately these situations are rare.

One would hope that these problems would not occur and that Quicken's Validate and Repair process would detect and resolve these problems or at least point to what needs to be fixed manually, but unfortunately that is not always the case.

The only method I know of to find and resolve these problems is to narrow down the date range and accounts as you did, then delete and re-enter the damaged transactions(s).

Please see, comment, and vote on this Idea post to request improvements in this area.

https://community.quicken.com/discussion/7880500/validation-should-specify-what-it-changed-32-merged-votes

Note that if you just sent your report using Help > Report a Problem, you will not get any response. You must also contact Quicken Support, preferably by phone.

Please let us know how it goes.

Ps56k2

wow … just reading along -