Report that isolates various enterprise fees

Tags: #<Tag:0x00007f79ff89fbe0>

What is the need / problem

There is no report which itemizes Enterprise Fees. Today if an enterprise has a single ‘enterprise fee’applied in the OC and a ‘shipping fee’, the Order Cycle Customer Totals report could be used to extract the total fees paid, split out as Enterprise Fees and Shipping Fees. However, if there are multiple enterprise fees applied, this report will only give a single total for all the enterprise fees combined, and the hub manager doesn’t know how to split them up.

Who does it impact

Any enterprise that needs/wants to make use of the rich enterprise fee structure available in the OFN platform.

What is the current impact of this problem

Hubs that want to make use of diverse enterprise fees are not able to do so. So currently hubs are just using ‘mark-up’ kind of fees. There are hubs that want to use fees to: do fundraising, calculate food transport costs, levy extra charges on distant products, etc. OFN allows for these things, but without the ability to total different fees in a report, it is too time consuming to calculate these totals ‘by hand’ using customer orders.

What is the benefit in focusing on this

Why have all these possible fees, if we can’t isolate and work with them? By providing a report that isolates and totals these different fees, enterprises will be able to make maximum use of the fees that are possible. Without this, there is little reason to be using the multiple fees that are now available

Plus - its an ‘easy win’.

Potential solutions that will solve this problem

A report that itemizes each different enterprise fee paid in a given OC. It could be modeled like the other reports – with searchable date fields perhaps - or even if it was only available by a particular OC that would work.
Ideally it breaks down by customer (customer by each fee), with totals for each customer and for each different fee.

Value x ease analysis and selection of our feature candidate

We only identified one feature candidate?

T-shirt size of the selected feature candidate

S ? M ?

Metrics to measure if need is well satisfied after feature has been implemented

  • Mary/Shannon can access fee breakdown (O/N) [would need to be better specified]

Epic and/or project board where you can follow implementation


Connected wishlist and discovery discussions:

No linked discussions identified

1 Like

great @tschumilas! could you do an example csv / xls with demo data, to think through how columns / rows etc should ideally work?

you are the database queen @Kirsten. I seriously need help on that front. So please - yes - do an example. I know for certain that we need to be able to get a total of each different fee used in an order cycle. So - I guess if we did that by order, then we also have the possibility of displaying different fees paid by each customer in an oc. That might also be useful - for ex: if a hub is doing a fundraising campaign, the customer could be told how much they contributed to the campaign. Ideally, it woudl also be possible to have fees x supplier for a given OC. I can imagine one use case thats come up here - certified organic producers here often collect a voluntary fee of 1% to contribute to their trade association. If they did that through an OC, then they’d need to isolate the fees the hub collected on their particular orders — so that would be a fees x supplier report.

@CLFC - this is the feature we discussed the other day (report that isolates enterprise fees). Note it is in ‘icebox’ - this is the category where we place features to be prioritized after they have had some general ‘wishlist’ discussion. Icebox items are priorized quarterly. This particular feature is being devleoped now.

Excellent! I agree that there is an excellent system in place for adding various fees, but I’m finding it very difficult to isolate them - and can see it being problematic for our bookkeeper and auditors when we have to provide a paper trail of all of the fees collected, what they were for, and where they ended up in our revenue stream. Thanks for tagging me in @tschumilas.

ok, I’ve had a crack at this. A little bit difficult without clarity around required use cases etc. Could you have a look at this and see if you think it would cover it @tschumilas @CLFC. If not please try and explain exactly what case and/or info you need covered?

I have no idea how difficult it would be to do this - reports can be fiddly but hopefully this would be straightforward . .

I think that would give us a good starting point. One thing we’re accustomed to (using OFS) is a very extensive reporting system. It will take us right down to line items for every piece of a transaction … it’s almost TOO extensive. We’ve struggled at times in the past teaching our co-ordinators (and bookkeepers) everything they can access. But I don’t think there’s ever been a line item they couldn’t find.

I like what you have in the “By Customer” sheet. Although instead of by customer, maybe sorted on the fee type and/or fee owner columns. That would make it easier to pull out all of the admin fees, or transportation fees, etc if they’re all grouped together. Then if we had a fee for a fundraiser, it would be really simple to see all the fees collected specifically for that event, and separate them on the bookkeeping end.

I’m assuming people would export as a csv and sort / filter / pivot it to whatever they want. But I think you mean more useful to default to sort by Fee Type and then Fee Owner . . poss. also with some sub-totals already calculated?

One of the things I’m finding (this is generally speaking not this case per se) is that users look for all the reports they need right in the OFN platform, and they don’t realize that downling to CSV gives them more power than default ‘pre-set’ reports. Thats not the case with fees (yet) because until the fees are separated out even the CSV doesn’t help you. BUT - I agree that once exported to CSV - what you have proposed @Kirsten will allow for multiple use cases as far as I can tell. And I agree with @CLFC that the default report without downloading data is likely best as sort by fee type, then fee owner (presumably once a particular OC has been selected). This would be amazing.

@Kirsten - we have many producers who are anything but tech savvy … and wouldn’t know a csv from a potato. While I wouldn’t have an issue using a csv file, @tschumilas is spot on. Users look for the reports right in the browser, without downloading anything or using a spreadsheet program that they may not know how to use either. It’s great to have both options - csv for advanced users, and a straight forward in-browser report available to users who aren’t tech-savvy.

Okey dokey, I’ve made a couple of changes to the spreadsheet . .

  • Sorted by Fee Type then Fee Owner (Enterprise)
  • Reordered the Columns so more intuitive with that default sort
  • Added columns for Name and Tax Category (seems useful :wink:
  • I haven’t added columns for Calculator or Calculator Values (the only other two fee fields - https://openfoodnetwork.org.au/admin/enterprise_fees) as you don’t seem to need them

Are the other existing fields necessary? the ‘Fee Placement’ and ‘Fee Calc on Transfer Through’? I suppose it doesn’t hurt to have them and then it’s more info accessible if people actually need it?

I propose that we leave out any auto-sub-totals etc at this point, because there are so many ways to cut them, they complicate the report creation process and actually make things more annoying if you’re re-ordering data and have all these extra lines. What do you think?

Yeah, the sub-totals would get weird quickly if you’re re-ordering data anyway. There are all sorts of ways to complicate reports after they’re working anyway!

Tax category isn’t useful for us, because we don’t deal with taxes. But others likely do use it, so I’d certainly include the column for those sites. :slight_smile:

Yes we need tax category - hubs/farms sell many taxable products - flowers, non-food items like soaps, candles, sustainable food wrapping…

ok people so is this good to go? any other changes - think carefully because it gets more expensive to change once someone starts working on it!! @tschumilas @CLFC @MyriamBoure @lin_d_hop @CynthiaReynolds @sstead . .

As I commented on Slack the need is not the report. Let’s ask 5 times the question “why” to clearly understand why hub managers need fees aggregated data by fee type and by fee? Why do they need those data precisely, what do they want to do with them? My guess is that it is for analytic accounting, they need to know in their revenue stream what is for which project / cost category, etc. If this is the need, is the best way to answer it to have a separate report for fees? Or to integrate those columns in the sales report / account reports packages… because maybe you want fees per distribution point as well, so isolating that report from the order report might not meet the need? Like maybe some distribution sites will not collect enough logistics fees compared the the volume of orders. Maybe you want to compare those kind of things. Maybe you want some data like the % of the global orders each type of fee represent, like logistics cost, what do they represent in % of global orders? And follow those indicators to monitor and improve your business. Again, the more we understand the need, the more likely we will build an efficient solution at a minimum cost. So I cant’ tell about which columns we need on the report personaly if I don’t understand that need clearly… does it make sense @tschumilas @Kirsten @CLFC ? I would love some support from @danielle about how to clearly understand and document the need on this specific case :slight_smile: We started writting the need reporting process with a first version of a template on how to investigate a need but we surely need to iterate on that.

At present there are 3 needs that exist:: @CLFC needs to be able to isolate the total fees paid in an OC associated with individual producers products. This is because in their model, the producer and the consumer co-pay the transport costs. So we are talking about different ways to SET these transport fees (perhaps depending on distance the product travels) - but regardless of HOW we set the fee in the end, we need to isolate each fee in a report so this can be assigned to the correct producer after each OC. Second, I’m working with a hub in Waterloo (Bailey’s) that is launching a series of satelittes (distribution points). Each of these points will have slightly different models and some of them are partnerships with food security projects and they want to charge a fee and then re-distribute it to selected consumers. So - the hub (Bailey’s) needs to be able to isolate the fundraising fee for that distribution point, so they can forward them those funds after the OC. The third case we have is a kind of levy that organic producers use. Many certified organic producers here are part of a program that adds 1% to their sales for an organic promotion fund. It is an optional program, so not all producers do it, but some of the producers supplying the Bailey’s hub have asked if OFN can support this. Bailey’s adds a 1% ‘organic promotion’ fee to participating producers products. The hub then needs to isolate the totals for each participating producer, and forward it to OCO (Organic Council of Ontario) who administers the funds.

In addition, I think it is a likely scenerio that a hub will want to compare the operations of its satellites/pick-up spots/distributors to ensure they are cost efficient. This is not a current use case however - but I think its just around the corner. So - do we anticipate it and build it in now?

1 Like

I think @tschumilas summed things up very well there.

Right now, the only place to really see the fees broken out is to go into each individual orders - then we can see each fee on each product, line by line. When we hit our annual audits, and the auditors want to see every line of income, we would have to go through every individual order for every order cycle for every week of the year to get them - as opposed to a fee report that could list them all in one place, saving literally thousands of page loads and I don’t even know how many hours it would take otherwise. That data is already in the system … since it appears on the order review screens … this would just make it far, FAR easier to access when needed.

Ok, I get it, it becomes clearer. And I guess the idea is that we will filter by OC and date and only see aggregated data about fees. Got it. @Kirsten do you think we should use that report to start writting report in another way, to improve reports performance? Instead of still developing it in that way that we know is making reports break… if a hub want those data on a year it’s very likely they bug the server…

not sure @MyriamBoure - i think it’s again the tricky balance between having picked out a ‘quick win’ to deal with a quite well-known and common problem (@tschumilas has been begging for this to be dealt with for a long time) and the whole ‘restumping the house’ issue. My tendancy would be to give the dev that picks this up some range to do in a better way if they can see what that is, but to be well aware that ‘redo the way we do reports’ is not what has been put into the pipe. Tricky balance for sure.

1 Like

@MyriamBoure @danielle just want to confirm my next step here in the evolving process . . I think that:

  • we have prior agreement in product curation to do this
  • we have now scoped acceptable report to achieve the needs
  • I should create an issue or epic in github with all info for a dev to pick it up?
    • I can put a note in that issue about openness to doing report in a different way?

NB. Xero is doing some interesting things with its reports - making them heaps better. When I use them I see the old reports with a button at top right telling me that a new one is available if i want to try. I click that button and use the new one, and it then becomes my default.

If @Rachel has mock-up of possible new report filtering etc we could include that and just build this from scratch with new ux as i don’t really think there is one to work from that would make things any quicker?