I have multiple companies using Xero. When the Advanced Management Report loads the P&L data, the values in columns K and M for “Org Value” and “Group Value” will show accounts with an Expense type as a negative in one company and a positive in another company. I assume it is some setting but I cannot seem to find it. Any clues?
This occurs when you have, for example, money received in an Interest expense account. The money received will be showing under expenses as it is being debited to the expense account but then being of a negative figure similar to the below screenshots.
Hope this helps. Please let me know should you require anything further.
Thanks David, however I don’t think this answered the issue. In your screens the value that appears in the column titled “Org Value” are negative for credit values and positive for debit values That is consistent with all but one of my companies. In that company the opposite is true and I need to total the values in columns K or M to agree the net profit. In the other companies column N shows the values that allow the net profit to be totaled.
The way column N works is that it looks at column D and changes the sign ( positive or negative) if the value is not “Revenue”. So what column N is doing is changing the Value of M if the value in column D is not “Revenue”. If the value in column D is Revenue then it will give the same value of Column M.
So to solve your issue I believe all you need to do is change the account type of the accounts “Perpetual support & Maintenance” from type “Revenue” to any other type in column D. This has to be done from Xero and not from the template.
Alternatively you can change the formula in Column N to identify the account “Perpetual support & Maintenance” and change the value in column N to negative.
Hope this helps,
I also encountered this issue with only one of three companies in the same report. Even though accounts are Type “Expense”, the report extract is extracting the data incorrectly as credit amounts even though they are debits in Xero, but again only for one company.
For example, in my case, Direct Purchases is set up as Type “Direct Costs” in Xero in both Company 1 and 2, and Bank Fees are set up in the Xero as Type “Expense” in Xero in both Company 1 and 2.
When the data extracts, it shows Direct Purchases correctly as a net debit in each of the companies, but it shows Bank Fees as a net debit in one company and as a net credit in the other company, even though in Xero, they are both Net debits. This causes the NP not to balance, and because the extract “rule” is not consistent across all companies, it is not possible to change the formula for Modified Group Value. By changing this formula to correct the amounts showing incorrectly as credits and change them to debits, will in turn cause the same account in another company to show as credits.
as mentioned above this should be solvable by adding another column at the end with a formula to ignore the signs.
If you please send me the clearer picture of the excel above I can send you the formula you need to fix this.
Hi David, thanks for getting back to me.
I will have to do this in a few comments since I can only load one image on the trial.
Herewith the first and my notes:
Data in Company 1 – debits extracting in P&L report as Net debits for Type “DIRECTCOSTS” but as net credits for Type “Expense”
However, Data extract in Smart Profit and Loss report showing Bank fees (category Expense) from Company 1 as a net credit even though it shows Bank fees (also category Expense), correctly as a debit (last two lines in this screen shot).
Any formula in the modified Org value does not seem to work, and changing the category manually, does not correctly pull through to the report.
Thanks for the help, I would really like to use this report for our next EOM, it is great!
Apologies for the late reply. I am not sure I understood your explanation above. The amounts in Red should be shown as positive figures?
Yes they should be, they are debit amounts on the P&L, and so they should be showing the same as other debit balances like Direct Purchases as positive amounts. They show correctly as debit (positive) amounts in other companies, but it is not consistent,
Is there any update on this? I was hoping to use these for our March EOM reports.