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.
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
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.
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!
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
So why couldn’t we…
- producer sets sale price (lets call it the naked price for clarity)
- hub marks up 10% - and that is then the fee visible in the shop that consumers pay.
- 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?
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.
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.
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.
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.
fantastic - so glad this is worked out - a good short to medium term fix.
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!
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.
@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:
Voting and prioritisation is happening over the coming weeks so the sooner this can be ready to be prioritised the better.
It is my understanding that this icebox item has been resolved. It related to a specific hub need and that hub has worked out another way to do it - would you agree @tschumilas @CLFC? If so, we can close it.
OR do you think there is something here that you would still want done / integrated into OFN code . . and if so could you describe what that it? I suspect it would be going into a new wishlist item . .
Yes it is resolved outside OFN. I see the following process:
- If the need is clearly defined here, move it to Icebox->Approved. If it’s a rare enough case it will never get enough votes in product curation and will just stay there.
- If the need has to be refined move it to Icebox->Draft
- If we evaluate that no one ever will have this need inside OFN, close the topic. Maybe have another category “Archive” or something in Icebox. It’s nice to see what needs we rejected in the past.
- No need to move back to Wishlist
Yes, while it would make things much better for our accounting if this could all be calculated and tracked within OFN, I believe the solution I have built will satisfy our specific needs. On a global scale, it will simply not get voted up, since we seem to be the only co-op out there using this model.
I like @sigmundpetersen 's idea of an ‘archive’ category. In the event that someone else somewhere should have a similar request, they would at least be able to see what was discussed and done previously, and if a solution was found outside OFN, they would have the option to contact the person involved to ask questions on how they can resolve it for their instance as well.
Great, thanks all
I’m going to close and archive this icebox item and it can be found and revisited if needed later on.