Is there a way to report all transactions in a specific period on a cash basis (not entry date).
I can’t see any option under the General Ledger Transactions report. Is there another way to achieve this?
The General Ledger Transactions report includes all transactions regardless of your accounting approach. Otherwise, it would not balance debits and credits. Cash basis accounting is really a reporting option, not different accounting. The differences between accrual and cash basis accounting show up on balance sheets and profit and loss statements. But both approaches depend on the same entries.
The general ledger came to be because of accrual accounting.
1 Like
Hi Tut, all,
I’m chasing this thread as it’s similar enough to my own and I’ve spend enough time digging it’s time to call for help.
I need to get an export of all transactions, grouped by accounts, relevant to a cash-basis accounting year. The General Transactions Ledger is oh-so-close, but appear to filter (and only expose) the ledger date, and not the cash transaction date. As a result it not only includes transactions that would be relevant to an accrued accounting year (and not necessarily a cash-basis year) but there is no other exposed data (like cash transaction date) to manipulate this in the way I need in Excel.
Taking a different approach, there doesn’t seem to be a good way to filter transactions from another report such that you can get the account and transaction date as relevant fields.
I need this because:
- I am reporting on a cash basis for a financial year (both P/L and B/S)
- I am translating each transaction to a new foreign currency on the date it hit the books as a cash transaction (in Excel)
- I need to be able to summarise these transactions based on their relevant account
I just can’t seem to get all three of these criteria to be met in the same report, and doing this report for each account is not my idea of a good time…
Any chance someone can make a helpful suggestion?
Cheers!
Why not just choose the cash basis option when defining these reports?
Why not let Manager do this?
That is what the P&L or BS will do.
You seem to making a great deal of unnecessary work for yourself.
Hi Tut, (all)
I appreciate the prompt response. It’s clear you are a frequent and passionate contributor.
I do use this for the overall summary. However, because of the condition:
…a summary is insufficient. The summary eliminates my ability to deal with this at transaction level.
Perhaps this is what I’m looking for, and I simply can’t figure out how to do it. I need transaction level translation of currency at this stage. I enter my data in currency X. I need to report the sum of the transactions in currency Y, evaluated at the transaction level. My books need to remain in X as my functional currency and my company’s native tax home and I need to continue to report in currency X.
If Manager can do this, can you (or others) provide guidance? I’ve bashed my head bloody against this trying to find a way. Manager doesn’t seem to expose the cash-basis-equivalent date from a General Ledger Transaction report… and I have yet to find a way to generate the equivalent.
True, if I didn’t have to concern myself with the other simultaneous conditions. Maybe I’m missing something that would get me over the line? I could slog through each account, exporting each individually to Excel, but it seems like something that should be easy to expose at a General Ledger level.
If we determine what I need isn’t necessary I’m very happy to forgo it. However, my advice to-date suggests that this is not unnecessary and so I think it best to focus on the capabilities of Manager than the difficulties of my reporting situation. I’m certainly not here seeking international tax advice. 
If I must I’ll slog through the transactions. That said, being able to filter transactions from the general ledger based on a cost-basis effective date (and then expose that date) doesn’t seem like a bizarre capability to expect, so I thought I’d come to the forum for advice.
That is because—as I wrote in a previous post in this thread—cash basis accounting is a matter of reporting, not different accounting. It simply excludes transactions. To get the equivalent of the General Ledger Transactins report corresponding only to those transactions used for cash basis would require a custom report with exclusion filters for all the accounts not involved.
As for the rest of what you are trying to do, this does not amount to just handling foreign customers, employers, or bank accounts. You seem to want your entire books kept in two currencies. Manager will not do that. I cannot imagine why you would want to do this, but the easiest way would be to set up a completely parallel business with a different base currency. Then, convert every single transaction to the different base currency before entering it. You would have two completely separate sets of accounting records for the business. The possibilities for error seem enormous, but you could do it.
I did read your previous post. As to cash-basis accounting being a matter of reporting, this is true. But the output of accounting is reports, so I disagree with the implication that this somehow means it is beyond the scope of Manager, or accounting writ-large, to address.
The fact that Manager can generate a cash-basis summary means that at some level it is pairing cash transactions to expense/income transactions (via A/P or A/R), and using the date of the cash transaction as the effective date. This is what I need to tap into if I’m going to deal with this efficiently.
I’ve tried to do this. However the report fields for the general ledger tab (in custom reports) doesn’t expose any linking information between a cash transaction and the A/P or A/R entry it satisfies, which would allowing me to attribute the transaction to a specific expense or income account. I tried to use the other tabs (like Receipts and Payments) as a potential means approaching this but they do not include values of transactions for some reason (maybe a bug?). In fact most of the other tabs don’t include amount fields, making me think Custom Reports are mostly a work in progress at this stage.
In any case, the tool-set doesn’t seem to be there which is why I’ve come to the forum - to see if it is just me unable to find it, or if it is a fundamental limitation of Manager reporting at this stage. Given your input on this I believe it is the latter.
Although this would satisfy my underlying requirements perfectly, I am aware of this limitation.
Yes, this is an ugly option, and one I’ve seriously considered since I don’t want to give up on Manager but still need to satisfy my reporting requirements.
I’ve been able to do most of what I need by exporting the general ledger transactions to Excel and modifying the values with the appropriate exchange rates there by doing a lookup against a ForEx table vs. transaction date… Unfortunately it falls short because the the A/P records do not have (exposed) unique identifiers that allow me to tie an expense from account “A” to A/P to the the payment from account “C” to A/P. I have to manually pick through the transactions to find and apply the cash-basis equivalent date to those transactions from “A” to A/P. Oh-so-close, but not quite there. Better than keeping an entirely separate set of books though.
I did not say it was. I was pointing out that the transaction base is the same. There are not separate cash basis dates as you seem to think.
There is only one date for each cash transaction, and it has nothing to do with Accounts payable or Accounts receivable. Transactions are not magically “paired.” Receipts and payments are posted to accounts you designate, when they are entered. The differences between accrual and cash basis accounting have to do with recognition of income and expenses entered as invoices, as well as billable time. The reason I told you exclusion filters would be needed is to eliminate transactions posted to Accounts payable, Accounts receivable, and Billable time. You will be able to produce the report you describe if you follow the advice I’ve now given twice. But nothing in Manager is going to help with dual-currency requirement you’ve set for yourself.
In spite of the advice you’ve given (twice) I’ve found no way to create a report with line items that simultaneously:
A. Indicate the date that a cash transaction occurred
B. Indicate the income or expense account that this cash transaction would be accounted against.
Perhaps there is something I’m doing wrong so I’ll provide a simple example and maybe we can identify where our two pictures are failing to converge.
-
On date 1 Jan 2019 I create a purchase invoice for supplier “Joe’s Accounting.” I indicate this is an expense in “Accounting Expenses.” A counter-entry is created automatically by Manager in Accounts Payable, associated with this invoice.
-
On date 1 Jan 2020 (one year later) I create a payment from “My Bank” to Joe’s Accounting and select the invoice from (1) from Manager’s drop down list. A counter entry is created in Accounts Payable. There is clearly a link here via the invoice (see 4).
-
If I prepare a cash-based P/L for 1 July 2019 - Nada. No transactions of relevance, as expected.
-
If I prepare a cash-based P/L for 1 July 2020 - Manager reports an expense in Accounting Expense, as expected. But how can this be if there was no data stored in transaction (2) that referred to Accounting Expense? Manager must be using the invoice to ‘know’ that the cash transaction applies to the Accounting Expense account, and uses that link to combine the date from transaction (2) with the account of relevance from transaction (1) and post the expense appropriately. I do not suspect magic is involved, but I hope through your assistance I can tap this dark force and bend it to my will.
-
Following your advice, both times, I go to “filter” a custom report of General Ledger Transactions. I select General Transaction Ledger as the ‘tab’ to source the data from. I can choose date, I can choose description, and I can choose amount. I can choose everything except a field that would allow me to uniquely connect the two transactions, allowing me to map the date of the cash transaction to the account that it was posted against via the invoice (as Manager clearly can and does in the background). I don’t exclude any accounts because… well… what would I exclude to do what needs to be done?
So with the available custom reporting tool, how exactly can one connect the cash transaction (B) to the relevant account in (A)? I am earnestly trying to follow your advice but I am failing to see how the exclusion of certain accounts from a G/L transaction report would provide this…
I suspect that you may not have appreciated that I am using the supplier/purchase-invoice and customer/sales-invoice functions of manager. I can see that if you did not do this than you would bypass A/P and A/R accounts with cash transactions and have to attribute them directly to the relevant I/E accounts. In such a case I can at least understand why you so emphatically stated that the A/P and A/R accounts are and need not be involved. However, in my case they clearly are involved and it is masking the relationship between the cash transaction and the account of relevance.
You are not correct. Manager uses only the receipt itself to determine the date. It does not rely on the invoice, even though the receipt is posted against it. Each transaction contains all information necessary for the debit and credit involved. This is what I meant when I told you there is no “pairing.”
No, it does not. See above explanation.
The accounts not involved in cash basis accounting presentation—the ones I already listed.
I fully understood.
No, it is not, because there is no connection to mask. As I’ve tried to explain many times, all transactions stand on their own. It is only a matter of designing a custom report to present the data you want and exclud the data you don’t.
Thanks AJD,
Great to get some other input on this. I appreciate the concrete example which was very similar to what I’d already tried.
Unfortunately, due to my use of the supplier/customer based invoicing feature (which works through the A/P and A/R accounts as an intermediary account), the cash transactions are grouped against A/P and A/R accounts and not the expense and income accounts that the invoices refer to.
As an example, I’ve created a test company with the only transactions being that of my previous example. One invoice generated on 01/01/2019, and one payment against that invoice on 01/01/2020.
Here is a custom report with the fields as noted, grouped by account:
You can see how no single line contains both the cash transaction date (01/01/2020), and the appropriate account (Accounting Fees).
Further, I have not identified any report column I can include in the report that would enable them to be tied together.
[Aside: I am confused as to why Manager double-transacted on 01/01/2020 though… there is only one receipt in the entire file.]
That is because each transaction involves two separate entries in the general ledger—the debit and the corresponding credit. That is why it is called double-entry accounting.
There isn’t one, because the debit and credit postings are separate.
Because that’s what happens in double-entry accounting. You seem to be unfamiliar with basic concepts of double-entry accounting. Until you learn these, you will struggle with Manager or any other double-entry accounting system. The carpenter cannot use the tool until its purpose is understood.
1 Like
Using the above report, construct a cash-basis P/L without making assumptions about what transaction in one group belongs to one transaction in the another. It can’t be done.
I fully understand double-entry accounting. Please refrain from the abusive stance.