Allow hubs to charge fees to producers

Software improvement (Archived)
1

What is the need / problem

Our business model includes a fee charged to our producers on every product sold - a percentage to the price, per item. This is identical to a flat % per item sales fee available as an Enterprise Fee now - except charged to the producer, not the customer.

Who does it impact

Hubs who share operating expenses between the customers shopping at them, and the producers who supply the hub. The fee is collected to cover handling, distribution, marketing, and costs related to getting products from the producer to the customer in an OC.

What is the current impact of this problem

CLFC is unable to make a transition to the OFN platform without the ability to charge fees back to the producers.

What is the benefit in focusing on this

This would remove a huge roadblock in CLFC transitioning its business to the OFN platform, bringing over 1700 registered members, including around 120 producers, in the process.

Potential solutions that will solve this problem

An additional field in Enterprise Fees - “Paid by” - would be the simplest solution I can think of, at a high level. IE: Paid by customer. Paid by producer. Paid by hub. Not sure why a hub would charge itself a fee … but you never know!

The code behind it would be much more complicated than “just add a field,” obviously. Depending on the database structure, maybe that wouldn’t be an option. But if I had my hands in the code, (sorry, didn’t learn enough Ruby to be useful here) that’s where I would start.

2

So to understand this - this is really more about producer invoicing - right? You want to be able to calculate a fee, and pass it on to the producer. So won’t you have that capacity with the new fees report? Each producer will have a fee that is passed onto the consumer through the shop. Then in the backend, the new report will let you isolate and total the fees charged per producer. Then, you can use that total to calculate the fee the producer pays (ie: is it 100% of the consumer paid fee, or 50%…) This amount is then deducted from the hub’s payment to the producer. So - that is now in the works. This new issue, I’m assuming, gives you some added possibility - but I don’t see what it is?

3

No, the new fees report still won’t allow us to charge the producer a fee. All the fees now get passed on to the customers.

Let’s say a head of lettuce is listed by a farmer for $1.00. Right now, the producer is paid $0.95 on the sale, and the customer pays $1.10. CLFC receives $0.15 on the sale in total. The way OFN works now, the customer would pay $1.15, and the producer would get $1.00. We need that producer fee to be removed from the price of the item as set by the producer.

So what we need is the ability for the fees paid by the producers to NOT factor into the final sale price to the customer, only the amount they are owed when they are paid for their orders.

4

If we make every fee separate we can do this. We know the ‘naked price’ from the producer (.95), and then you just add in the other fees. For ex - add on a transport fee that the consumer pays. Then add on an admin fee (or whatever) for the hub. So the producer (or the hub on their behalf) uses the nake price - .95 to create invoices to the hub for the product. So once we have the ability to isolate all these fees, you would just subtract the producer’s transport fee payment from the .95 cents (or really you would do it on totals on the invoice.). The producer transport fee won’t appear in the OFN backend per se (at least not any time soon). But the consumer transport fee will appear once we have the new report. So you would just take this total for any given producer and use it to calculate the producer transport fee. (ie if its 50%, or 100% or 150% of what the consumer pays…) So once we have the fee report downloadable, this can be done off-line, and it could even link dynamically to a file that does producer invoices I believe (although this is why I need an excel power user).

5

I’ve read that 4 times now, and I’m still not sure I follow your train of thought. If you add the consumer fees to the ‘naked’ price that is paid to the producer, the 10% consumer fee would get calculated on 95 cents instead of a dollar and throw everything out. The consumer fee has to be paid on the full price, not the price minus the 5% producer fee. That’s where it gets complicated. The fees have to be calculated separately based on the full list price, not the price payable to the producer. Unless I’m completely misunderstanding your logic there, which is always possible.

6

OK - now I see the problem. You want to be able to calculate a fee ontop of a fee. (A consumer transport fee, calculated on top of the admin/mark-up fee the hub takes.) So you are correct - this will not work in OFN at present. Maybe the short term workaround is to calculate the consumer transport fee on the naked product price (the producer’s product price)? Same as the hub’s admin fee? Or to add the hub’s admin fee before the products are entered maybe (but there are implications of doing that.) Why don’t you and Jen and I have a call to discuss the CLFC fees and business model and see how we can make this work. I’m just worried that you could be the only user right now who needs this change, and so the modification isn’t likely to happen soon. I think this is a really specific use case and not likley that useful to have this discussion here.

7

It’s entirely possible this is unique to us on the OFN platform right now, but the addition of an option to choose who pays the fee (consumer vs. producer) opens up a lot more possibilities for distribution hubs. But it is central to our business model right now, as it is the fee that the producers pay to cover CLFC’s costs related to marketing for our producers, and staffing the hubs. That’s the producer’s share of the costs, so it’s not all thrust on our customers.

8

I haven’t read all the above in detail so apologies if I am misunderstanding, but it seems like this is mostly a perception issue. Do the Producers currently receive an invoice from the Hub and pay in a separate transaction? So they recognise it as a separate payment?

But if it is the same amount that is reduced from the amount that they are paid, and the Customer pays the same amount as before and the Hub gets the same amount as before it is ultimately the same thing? But this is maybe what you’re saying - that it’s not currently possible to get all the amounts right under existing structure

9

I’ll try to break this down so it’s a little more clear. (Hopefully!) Currently, our system works like this:

  • Producer creates their product and sets the price.
  • A 10% fee is added to that price, payable by our customers, which becomes the final sale price. This is the price the customers see when they are shopping.
  • A 5% fee is charged to the producers by the hub, which comes out of the sale price they set. This fee is invisible to customers - only producers see it on their invoices.
  • At the end of an OC, customer invoices are generated, with the final sale prices, which include the 10% fee. Now, with OFN, this would show as a separate line item. That’s no problem.
  • At the end of an OC, PRODUCER invoices are generated as well. These use the prices the producer set (which do not include the 10% customer markup), and deducts a 5% fee. This total is what we use to pay the producers each OC.

The way fees work in OFN, they ALL get passed along to the customer, and the producers don’t pay anything. This would have to be calculated manually every OC, which would add a lot of admin time compared to the automated solution we have now.

10

I am resisting the compulsion to sit with a spreadsheet because I really need to be looking at other things right now, but I obviously need to because I don’t understand why the Producers reducing their sale prices by 5% wouldn’t have the same effect. I guess it would be much harder to ‘police’ and transparency might be lost. I’ll come back to it later - sorry I can’t wrap my head around this right now!

11

Reducing their prices by 5 percent would throw off the 10 percent fee charged. Using the $1.00 example, 10% on $1.00 is $0.10. 10% on $0.95 is only $0.095 … which rounds downward as the sale price rises. So it would cause a net loss of revenue.

Even adding 5% to the reduced price would turn 5 cents into 4.75 cents on every dollar, so 10% on top of $0.9975 still works out to a net loss over the course of thousands of dollars in sales.

I know, it’s a little confusing, but somehow it actually works. lol

12

So why couldn’t we…

  1. producer sets sale price (lets call it the naked price for clarity)
  2. hub marks up 10% - and that is then the fee visible in the shop that consumers pay.
  3. After the OC closes, then our only task is to prepare invoices for the producers and levy their 5% fee (calculated on the marked up sales price (or get them the data to prepare their own invoices).

To do the invoicing we need 2 numbers: the total ‘naked’ prices for each producer (that exists in one of the reports now), and the total sales for each producer (naked price + 10% markup) - that also exists in a report now (forget which one). So - these two reports can be downloaded, and a macro (maybe that’s not the right term) designed which automatically manipulates them by taking the total sales for each producer *5% (the producer ‘fee’) and adds it to their total ‘naked’ prices for goods supplied.

??? isn’t that it?

13

I see where you’re going with that, except the producer fee would only be calculated on the ‘naked’ price, not the total sales. And if you run the “order cycle distributor totals by supplier” report individually, for each OC supplier, save a csv for each supplier, pull each csv into excel and calculate the fee, technically we could extrapolate the data … but it’s a logistical, record-keeping, and accounting nightmare.

It would make a ton of extra work on delivery day, which is usually already a 10 hour day for our hub co-ordinator. And that’s with our current platform taking care of all the heavy lifting, calculations, and invoicing for us.

14

The more I’ve been looking at the existing reports, and comparing them to what seem to be fairly specific CLFC problems, the more I’m thinking I might try to develop a solution for them on my own … using the csv data from the existing reports, pulling it into a database, and extrapolating the data to suit our own needs.

Ultimately, if I can create a couple of functions that will get us the data provided from OFN reports into the format we want for our own purposes in a couple of really simple steps, I’d call it a win. The only thing I worry a little about is if we calculate the producer fees outside of OFN, there won’t be a proper financial trail for auditor purposes. BUT, if my thinking is right at least, there might be a way to make that work that won’t be a ton of extra work … or at least nothing that has to happen until after delivery day is over.

I have some experimenting to do, and some code to write.

15

I’m not sure why you think we don’t have the number now in reports.

Isn’t this how you want to calculate the producer fee:

Take 5% of the shopfront sale price (so the sale price is naked price + 10%) - so we have the sale totals for each producer - we just download as csv and take 5% of it. This is our producer fee - no?

then take this number and subtract it from the producer/supplier totals (naked price) - and we have this in a report now.

???

16

We have reports that the numbers can be generated from, yes. But there is no report where it has already generated the final values - the 5% producer fee, and the net amount they get paid for the OC.

So what I’ve done is created a script that will upload the csv from the Order Cycle Supplier Totals report - which gives us the ‘naked’ totals - and import that data into a mySQL database that I’ve formatted to match the columns of the csv. Then I created another script here - https://31eagledrive.com/ofn/producer_invoices.php - that will grab the OC dates from the data alread entered (which has to be added manually via a date picker drop-down when uploading the csv) and give a list of available OCs to generate our producer invoices from. These will list their total sales for each product, total them all up, subtract the 5%, and show the net amount they will be paid by CLFC. Then I added a ‘cheque list’ checklist at the end, which will allow our hub co-ordinator to see who is owed how much at a glance, when they’re writing cheques each week.

+++++++++++++++++++++++
Update: I’ve combined the two scripts into one, and set it up so that as soon as the file is uploaded, the new producer invoices are automatically generated and displayed. There is also a link at the top on first load to view a list of past imports to re-generate those invoices. Link to combined script here.
+++++++++++++++++++++++

At the end of it all (and this is still a rough first draft document), they will end up with printouts like this PDF. CLFC Producer Invoices-2018-07-03.pdf (159.4 KB)

I’ve run it by our co-ordinator, who is also our bookkeeper, and she’s confident that this would work just fine for her purposes.

So yes, the numbers needed to generate the report we need are there. But to remove margins of error, we want a solution that is either fully automatic out of the OFN reporting backend - OR as automated as possible, to avoid errors in repetitive manual calculations every week. The more places we can remove potential errors, the more smoothly everything operates. So now I have this down to a few clicks … download csv, import csv, click the OC date (a step I intend to eliminate and have it auto-generate on import), and print. Save as PDF is an option from the print dialogue in Chrome, and what we have been using for a few years now to generate PDFs of our invoices.

Download, import, print is a relatively quick and painless process to get around what could otherwise be a fairly involved manual process every week.

17

fantastic - so glad this is worked out - a good short to medium term fix.

18

magnificent work @CLFC - I love that you’ve rigged together something that does the job. Perfect concept testing and shaping so that if/when we build something in we’ll know exactly what it needs to do. Thanks!

19

Thanks @Kirsten. I’m going to try to do something similar with the packing reports for labels. Although that hinges slightly on having shops able to see customer names from hub sales. (In this thread.) I think something more automated there would also be a great benefit to users. :slight_smile:

20

@Kirsten can you please formally review this icebox item - does it have everything it needs to be moved to approved and ready to be voted on?

Tip for first time reviewers: there are some “ready” criteria on the process overview.

And here is the outline of the process of reviewing:

54%20pm

Voting and prioritisation is happening over the coming weeks so the sooner this can be ready to be prioritised the better.