expense categories reports - totals without HST?

plutek

greetings!

i'm assembling numbers for income tax. the Canadian form for reporting business expenses requires per category totals EXCLUDING HST. i don't see any way to make Wave generate that,

i've set my expense categories to match the CRA categories, so i do get per-category totals, but they include the HST, since expense numbers work that way in Wave. so i have to go through the HST report, line by line, and assemble per-category HST totals which i can then subtract from the category expense totals to get what i need to tax reporting. unfortunately, it's very tedious because i don't see a way to even group the HST numbers by expense category.

any hints about a better way to do this?

thanks much... cheers!
.pltk.

plutek

...i can, of course, manipulate a raw data export.... it shows per-expense HST, so i can pull my full dataset into a spreadsheet, sort the expense entries by category, and then run sums on the HST column for each category group.

is this really the only way?

KatR

I have the same issue with UK VAT. Any ideas gratefully received!

Gabriel_Krozkin

Hi @plutek , @KatR ,

I am happy to let you know that this is possible, but you have to follow these steps, since as you know the standard report from wave is very limited.
However my firm overcome this and you can still obtain the report that you are looking by following these steps:

1).- Go to Settings (bottom right of the screen) --> "Data Export" (bottom option).
2).- It will take a few minutes and you will receive the email with the data export.
3).- From the .zip file that you will receive open the "accounting" file in Excel.
4).- Open the file and "insert a column" (for example between Q and R) and sum the result of both "P" (Amount Before Sales Tax) + "Q" (Sales Tax Amount) in the new column inserted - you can call this column Total including Taxes.
4).- Select the entire Data and utilize "Filter" and filter by: Column "U" (Accounting Group and select Expense.
5).- Now go to column "B" and Filter by Transaction Date (Select the year that you want to display i.e. 2020) This will filter only transactions of 2020.
6).- Voila, the totals in the column inserted are the ones that you are looking for.

BUT, since you want to GROUP the numbers by Category, the following steps still have to be taken:

7).- Select ALL The data filtered (copy).
8).- Open Notepad, (paste) the data, and then click "Edit" --> Select All and then "copy"
9).- Open New Excel spreadsheet and paste the data.
10).- Select All the data in Excel --> Data --> sort by: "Account Name"
11).- Select All the data in Excel --> Data --> "Subtotal", At each change in "Account name" use function Sum, Add subtotal to "Amount Before Sales Tax", "Sales Tax Amount" (and the total column that you inserted "Total including Taxes". Click ok.
12).- Press the number "2" on the top left of the screen, and you will have your report sorted by account, including the column by expense before taxes, Taxes (HST or VAT) and Total.
13).- See sample attached of how will look the report. It is good to keep for audit purposes, for HST and for Income Taxes.

How do I know that this work and is accurate?
Response: The total of the expense column (before taxes) should match exactly the "Profit & Loss" report from Wave for the period.

I hope this helps.

Thanks.

Sincerely,

Gabriel

Experts in Wave.