[UI Data] Tax Reporting

[lin_d_hop]

This work forms the first part of the Improved Reporting task on the Product Curation priorities.

After months of conversations and iterations in the reports channel and on various spreadsheets this work is now ready for approval from the community before being moving into Github Epics.

What is the Need/Problem

As an enterprise I need to be able to understand:

• How much tax I am taking across my orders
• How much tax of each tax rate I am taking
• How much tax I am taking on different kinds of fees
• How much tax I need to pay on to my (tax registered) producers
• How much I need to pay directly to government

Currently OFN reports do not offer this data. Tax is aggregated in order totals and it difficult to disaggregate through queries directly.

Who does it impact

All tax registered enterprises.

What is the scale of the impact

For tax registered enterprises this creates huge problems in accounting, compliance, viability and operational confidence.

Reputationally this inhibits the growth of OFN if we cannot provide a functional solution for tax registered enterprises.

---

Proposed Solutions

The proposal is to create 4 new reports to cover these cases.

Enterprise Fee Reports

a) By Producer
b) By Order

These reports will show totals for each Enterprise Fee. The report will include tax information for each enterprise fee and will display the fees relevant to each producer or order. The total for the line item totals with tax, again by producer or order.

The report will include a toggle to include total rows or not, which will apply both in the UI view and CSV download.

Find mocks of these reports here.

Enterprise Fee by Producer

Enterprise Fee by Order

Tax Totals Reports

a) By Producer
b) By Order

These reports will show totals for each Tax Rate. When viewed by Producer this report will not include any fee information, it will simply be the producers cost price. When viewed by Order all fees on the order will be included. The Enterprise Fee report will enable to user to see a breakdown of the tax types per fee.

Note that when the Inherit Tax Type is used on the Enterprise Fee this the full tax rate breakdown of this fee will not be visible to the user.

The report will include a toggle to include total rows or not, which will apply both in the UI view and CSV download.

Find mocks of these reports here.

Tax Totals By Producer

Tax Totals By Order

---

Community Sign Off

Note I’m cheekily testing out a BETA process here… In order to progress from inception to delivery we require Core instance sign off with optional (and desired) sign off from Affiliated instances. This is a live discussion that has been happening here.

Core Instances

[YES ] Australia
[ ] Canada
[YES ] France
[YES ] UK
[ ] US

Affiliated Instances

[ ] Katuma
[ ] Belgium
[ ] Deutschland
[ ] South Africa
[ ] New Zealand
[ ] Brazil
[ ] Costa Rica
[ ] India
[ ] Italy
[ ] Turkey
[ ] Russia
[ ] Ireland
[ ] Jordan

[Rachel]

@lin_d_hop thank you so much for this clear documentation and all the work that got us this far It fixes all scenarios.

A quick question on Fee Tax Type: am I correct that all fees related to payment method will fall down under the empty tax type? Can we give it a name so we can filter on it when using as a spreadsheet?
But this makes me think also that we need to set up the UI filters we want to have on these reports.

Re total lines, would it be possible to repeat the Enterprise and producer on order ID info on the total lines? Maybe you already answered me this on Wednesday, sorry if it is the case…

While playing with real data I came to realize that for VAT declaration purpose only I will end up picking things in all 4 reports, in order to build reports for single producers or multi-producers hub. I’m a bit afraid we are introducing something more difficult to handle for them.
So I wonder if there is room to build these 4 reports that are very detailed and have everything + add a “summary report” for users with less complex model (which could actually replace the two current tax reports). Kind of a “cascading complexity”.

While the idea is interesting, maybe the simple case scenario does not require the same data across instances
In FR the data that would be needed would concern only a “by order” format:

• merging the two current sales reports (FR doesn’t have a user that only uses one of them, everyone is downloading both and copy-pasting them on the same file, is that the case everywhere?)
• adding Fees on payment method (those are the nasty one that are missing in the current reports and make you go “the tax report is wrong” => when you add the amount excl. tax and the amount of tax, you don’t find the total amount including tax)
• not rounding the results, to leave the option to the user to recalculate stuff like amount of a particular tax before the tax is applied to it

So users would have 2 options: either merging 4 reports, or downloading only one but doing additional customization to it.

Does any other instance has this need too? Happy to brainstorm potential other solutions.

[tschumilas]

I have run through the kinds of reports that our 3 more complicated hubs (in terms of fees and taxes) need and all the data they need is here. But, like @Rachel notes, despite all our work on this, those users are still going t need to run 2 reports : tax totals by producer and fee totals by producer and I think add up a payout report for each producer ‘manually’. This is because for a producer payout, a hub must find for each producer: total product sold + sales tax on product sold + fees owned by that producer + taxes on fees owned by that producer. Am I correct? The hub would have to do this calcuation for each producer - or is that number somewhere and I’m missing it (apologies if so). I understand if we proceed regardless - it just means OFN Support will likely be more hands on helping hubs that need this.

[tschumilas]

And I wonder if this is an opportunity to clarify some terms. Right now in OFN configuration we use tax zones, tax rates, and tax categories. Tax category is the only field the user enters. Zone is taken from the buyer’s address, and rate is taken by the zone. So - in these tables we have used ‘tax type’ - and it feels to me like we are introducing that term as some kind of hybrid between ‘tax rate’ and ‘tax category’. For example - ‘inherit’ is a tax category, but 20% is a tax rate. The tax category will be the same for a given product until the user changes it. But the tax rate will change if the buyer’s location (tax zone) changes. – and maybe this is an @Matt-Yorkley task … just trying to race ahead again! Its just we have such inconsistency in report column names already - we likely don’t want to introduce more.

[tschumilas]

One last thing - just checking out – on the Enterprise Fee by Orders table - the examples all have the Fee Owner as the hub. But would the fees that are owned by the Producer also display there? For the flower hub I tested for example, a given order often has 10 or more fees - each supplier has a transport fee (that the supplier owns and gets paid out to them), plus there is a fee (a markup) the hub owns. Just so I’m understanding correctly - if all of these list - this is basically a summary of all the fees on the orders management page.

[lin_d_hop]

Thanks @tschumilas and @Rachel

I understand that these reports don’t meet your specific instance needs in a single report. The conscious design decision here was to create general reports that would be accessible and understandable by all instances to use to support their users.

I specifically wanted to avoid creating a bespoke FR report and a bespoke CA report and in turn create both the precedent and demand for bespoke reports for every instance going forward.

In line with the goals of the Reports Project the idea is that we can create different ways for users to access the data. The UI should solve the majority of cases, though often not perfectly. The API can be used to better solve and metabase for creating bespoke reports.

If you are both saying that these reports won’t be useful then perhaps we need to stop this work here.

Some options:

• Instead focus on building a reports tool that gives users much more flexibility. We can expect this to be a multiyear project.
• Update the Sales reports to better include tax information (note we have to choose between line items, tax and fees - this is a three dimensional matrix and we are mapping to 1 dimension)
• Build bespoke in-app reports for each instance that needs new reports
• Think about how to use the API to better meet specific instance needs with integrations. This could mean continuing down this path. It might mean better enabling bespoke reports to be accessed via the UI.
• Someone else has a go at bringing these requirements together to see if they find a better way to cut this up.
• We wait for there to be space in the design pipe and get a real designer on this work.

[lin_d_hop]

@tschumilas

‘inherit’ is a tax category, but 20% is a tax rate.

When the category is inherit then there is not tax rate applicable on a field that totals. We can include two columns - tax category and tax rate. This will resolve your confusion here. When tax category is inherit then the tax rate will be empty in the case you describe.

@Rachel

not rounding the results,

Perhaps we instead consider rounding to 4 decimal places. I would say we need to find a point of consistency on rounding so that we don’t try to handle recurring decimals. There must be a standard and a point in which you don’t need to round anymore to maintain accuracy at the second decimal place. I would guess this is 4-5 decimal places.

@tschumilas

the examples all have the Fee Owner as the hub.

I have added the WillyWonka fee to the EnterpriseFee by orders. The by Order report should give all detail of fees that extend from cost of product through to order total.

[tschumilas]

@lin_d_hop

When the category is inherit then there is not tax rate applicable on a field that totals. We can include two columns - tax category and tax rate. This will resolve your confusion here. When tax category is inherit then the tax rate will be empty in the case you describe.

I understand that when inherit applies, there is not a single tax rate applicable/documented on a field that totals. I was just asking that where possible we use the same language in reports (re: tax stuff) that we use for configuring an instance to avoid confusion. This is important for any instance that has to configure complex tax categories, tax zones, and tax rates. So - thanks for this.

[tschumilas]

I’m feeling exasperated by this - perhaps like others are. Lets breath and put our heads together. I think we all want the same thing - to be able to as easily as possible meet users needs for tax and fee related reporting. One of the reasons I had hoped that the reports would do this, is that OFN-CAN (and also OFN-US because I’m trying to represent them here too) have limited ability to do API generated reports. Further, we are not set up to do metabase generated reports (and can’t be until our server is upgraded as I understand it) so I can’t get ‘hands on’ with that to figure out whats possible.

It would help me to understand - is it just France, OFN-CAN and OFN-US where users will need to download and work across multiple reports to get what they need? I gather the UK users are OK with the above. What about AUS ? Can your users get what they need from downloading one of the above reports? (I think we need a clear assessment of where we are at)

[lin_d_hop]

In the UK if a user finds using multiple reports difficult we build a tool in Google Sheets to help. Then the problem becomes:

• Download the reports
• Import to GoogleSheets
• Press a button
• All your data in one place

Most of the people who are big enough to have complex data needs and need to download multiple reports to access the data they need for tax/paying suppliers/VAT - these people use accounting packages for the hefty work. We just help them get the data in there.

[Rachel]

@lin_d_hop

I understand that these reports don’t meet your specific instance needs in a single report.

@lin_d_hop just to clarify: this is not what I wanted to express. Those report do meet the needs of complex hubs that are ticking all cases. So we definitely need to do them.

I was wondering if we could on top of that add a report more easy to grab. Something that does not tick all the cases. A smaller perimeter for simple enterprises.

FR has around 200 active shops and around 70 enterprises registration per month. For now we do manage to help the bigger ones, but a lot of the small ones just don’t contact support. I’m guessing other instances have similar situation?

One thing they are the most struggling with is to open a .csv and when they do they don’t understand that in order to do formulas they need first to replace all dots by commas. The Stripe csv report has not that issue. I wonder how they do it Anyway, I’m looking forward to the clean up of existing reports! But that’s another topic

If an additional report that tick only part of the cases is not something doable across instances, then I think we need to keep the current Sales report.

Maybe this is also very linked to our target customer. If simple cases are not targeted then my comment is useless

[chez]

Our customers will also need to pull data from multiple reports to get everything they need. While we also use google sheets or Airtable/Zap work arounds for larger customers with Kirstens help, there are lots of customers who express frustration with it not being all in one place.

[konrad]

Hey,
just some quick feedback from our instance (Germany).
We believe that we can work with the proposed tax reports as a first step. Thank you for the great opening post, Lynne.
We are aware that the general reporting features will be improved, too. This is very much appreciated and I would like to underline that from our point of view this is a very high priority to fulfill the user’s needs.

[lin_d_hop]

Hi folks,

Following on with the previous work done in this task here and here I am preparing this work for the delivery pipe.

The final step before I do that is just to gain sign off on the UI and search aspects of this work.

Note: In this work we are not making significant changes to the UX or design of the reports pages, we are simply replicating the existing layout. More will come on this soon.

1. Admin/Reports

• The two new reports will be displayed under these existing sections.
• The existing reports will not yet be removed. I would suggest this becomes part of the bigger reports redesign work.
• Displaying the reports by Order or Producer and with or without titles will be options available on the reports pages themselves.

2. Search/Filter Fields

I have created two proposals for search and filter fields to be included on the reports pages.

Search Input Fields for the Tax Totals Reports

Please go here and make suggestions in the Google Sheet if you feel things are missing or are unsure why things are included.

Search Input fields for the Enterprise Fees Reports

Please go here and make suggestions in the Google Sheet if you feel things are missing or are unsure why things are included.

• This is an initial proposal, so if anyone has strong opinions please do express them
• I’ll reach out to people who have been involved for sign off
• Please comment and suggest in the Google Sheets rather than in this thread please please.

Ping @Kirsten @Rachel @tschumilas @konrad @chez

[konrad]

Hey @lin_d_hop,

thank your for preparing. We are currently looking into this.

Until when do you wish to have our feedback? It needs some time to think it through.

Thanks!

[lin_d_hop]

Hi Konrad, By Weds Feb 3rd please. If possible this round of feedback is simply for the Search Input Fields for these reports. The reports themselves have been approved by the community.

[Rachel]

Thank @lin_d_hop I’ve added my comments

[Kirsten]

Looking good @lin_d_hop - thank you so much for wrestling this and coming out alive. Have added one serious suggestion and one ambit that likely makes no sense and can be ignored

[Rachel]

@lin_d_hop @Matt-Yorkley a user asked us if these new reports could give tax info / numbers (anything that we compute) with more than 2 decimals.
They are talking with other software providers who are handling rounding problems with keeping always 6 digits after the comma. So they would love something similar for any tax info within OFN reports.

I remember we talked about rounding problems but I can’t find the conclusion anymore. Does any of you remember? Thanks

[lin_d_hop]

Please now refer to the Github Issues for all current information on this work. This work is in delivery. Inception phases are finished. All questions and explorations to happen on Github now

Tax Totals Report Epic

Enterprise Fees with Tax Epic