Sharing my Simple Wire Service Evaluation Spreadsheet

[domineaux]

Here is a very simple spreadsheet for evaluating very quickly the value of WS orders.

You can test the value of your orders for a single or multiple periods as long as you include extended values for each respective period to form a consolidated sheet.

I did not apply credit card processing, because I look at those charges as necessary regardless of order origins.

I used an average cost amount for delivering an order. I realize this can vary broadly in some instances, but for simple evaluation purposes I think an average will suffice.

The profit percentage can be an elusive number in many instances. We have to have some basis for calculating profits to interpret the value of WS orders. In the example sheet I used .50 or 50%, but you can use what you choose.

I did separate Net Profit on Incoming orders from Outgoing orders because there are no delivery charges on Outgoing orders. Delivery costs affect the profits on Incoming orders.

The 2n column is data entry and the 3rd column is calculated fields. This sheet is not protected, therefore you can mess with the sheet at will. This sheet is very simple and you can see all the formulas I used in the calculations. You can play with the sheet and see all you want by just make 2nd column entries.

The bottom two horizontal rows tell the story.

The 2nd from the bottom row gives you a total profit on all orders after WS fees are applied
The bottom row give you an average cost per incoming order

You should be able to evaluate an entire years WS statement quickly, if your statement has annual entry values on a summary sheet.

Nuff said I think, if you have questions or thoughts on it please make a post. I built it in about 30 minutes, my intention was to make it simple and very quick and easy to use anytime.

I changed the outgoing orders to allow for sending florist to charge customer a flat amount sending fee per order
I changed the outgoing orders to allow for sending florist to pay WS a flat relay fee per order, FlowerShopNetwork,etc.

The spreadsheet will probably be changed as posters ask for things - current sheet revised 4:35 AM 01/08/10

 

[BOSS]

Good job.

One thing I would have included would be "Service Charge" on outgoing orders, which could be $10 per or perhaps an additional $50 income on the outgoing side.

 

[domineaux]

I can do that easy enough, but basically I was thinking this would work very well to take the information right off the statements.

The service charge a sending florist might charge the customer would not be reflected on the statement.

If this is important enough to users let me know. I can create a way to do it, without disturbing the basic statement inputs.

I am very interested to hear what others think, and what might be needed.

The calculation of Profit on incoming orders is done by (Net Incoming Orders x the Profit Percentage) less the cost of delivering all the orders.

It could have done it another way, by (Net Incoming Orders less the cost of delivering all the orders) X the Profit Percentage

The only reason I did it the first way is because when the orders come in we are looking at a total order amount. Anyway, if you have ideas on this let me know.

A simple plug in a few numbers when statements arrive will keep us updated pretty quickly on how good were are doing with WS orders.

Also, I'm only familiar with TF so other services may have stranger ways of preparing their statements.

My thinking was to narrow everything down to as simple as possible.

The data in the download is just sample data, it is not intended to make any sense.

 

[TOTO]

Thanks for your efforts!

It's another way for Florists to check their own WS numbers and help them draw their own conclusions.

I was curious as to how you felt after plugging in your own numbers. The example you used show a net profit of $2.41 per incoming order. Since it is only an example, it may not be your own results.

And, as you pointed out, 50% gross profit is a variable projection.

As a general rule, I've been using 30% as a florist's average cost of goods, 25% as a florist's average cost of labor, and 20% as a florist's average cost of delivery for a total cost of 75%.

Obviously, in that scenario, a shop winds up with a net loss of (-5%) per incoming right off the top and when you add another 25% in WS costs, the shop is filling orders at fifty cents on their dollar.

I round off the (-27%) to (-30%) due to the reverse the incoming order transmission charges which averages another (-3%).

The only way the incoming wire service business can be skewed to appear profitable is when the filling florist is willing to offer FREE LABOR for filling each of those orders.

And that's been justified by suggesting that, the design staff was standing around doing nothing anyway, or adding a few orders each day to their workload didn't increase their labor costs.

When the Florist's labor is thrown in as a FREE BEE, the (-25%) gets chopped off and the result is now (-50%) per incoming order.

Using an average incoming order amount of $50.00 Gross, less (-30% COGS and -20% COD), the net is $25.00. If a shop is paying a WS a monthly fee of $250.00, they would have to fill 10 incoming orders at $50.00 each just to offset their monthly base expense.

Once you've reached that plateau base, any additional incoming orders would have a marginal contribution to offsetting a shop's operating expenses after deducting any other ancillary or optional monthly fees from those profits.

On the flip side, you can also use the 20% earned commissions from your outgoing orders and cost shift them over as a credit to the debit side of the WS spreadsheet, but you would then be using those profits to subsidize the loses incurred on the filling side. And the same could be said of any outgoing wire service fees you charge as well.

Getting to the crux of the question on whether or not a Wire Service is PROFITABLE or NOT to your shop, isn't easy. Then again, it was designed by the WS's to be just that way.

All depends upon what your time is worth and if your truck was going by there anyway? However, and in my own umble opinion, if the WS's were forced to skew their numbers the way they expect their florists to, they would no longer be a factor. And then, the BUSINESS would be left to exist between REAL FLORISTS and without having to deal with the HUGE SUCKING SOUND of THE MIDDLEMEN.
lol

 

[domineaux]

Thanks for your efforts!

It's another way for Florists to check their own WS numbers and help them draw their own conclusions.

I was curious as to how you felt after plugging in your own numbers. The example you used show a net profit of $2.41 per incoming order. Since it is only an example, it may not be your own results.

And, as you pointed out, 50% gross profit is a variable projection.

As a general rule, I've been using 30% as a florist's average cost of goods, 25% as a florist's average cost of labor, and 20% as a florist's average cost of delivery for a total cost of 75%.

i could easily accomodate the average cost of delivery like you suggest, however most florists just charge a flat rate for delivery is why I used a flat rate. You can change the delivery cost in the sheet.

Obviously, in that scenario, a shop winds up with a net loss of (-5%) per incoming right off the top and when you add another 25% in WS costs, the shop is filling orders at fifty cents on their dollar.

I didn't break out COG (labor and material), because most shops are probably expecting a certain percentage profit on Incoming orders. The florist needs a least a certain % of profit to accept Incoming WS orders.

I am not even thinking of orders that come in through other sources, just WS orders. The receiving florist is being told the amount of the order, and then the florist has the choice to accept or decline the order only.

I round off the (-27%) to (-30%) due to the reverse the incoming order transmission charges which averages another (-3%).

It really isn't necessary to round the incoming commission because the spreadsheet easily calculates using the actual percentage.

The only way the incoming wire service business can be skewed to appear profitable is when the filling florist is willing to offer FREE LABOR for filling each of those orders.

And that's been justified by suggesting that, the design staff was standing around doing nothing anyway, or adding a few orders each day to their workload didn't increase their labor costs.

When the Florist's labor is thrown in as a FREE BEE, the (-25%) gets chopped off and the result is now (-50%) per incoming order.

Using an average incoming order amount of $50.00 Gross, less (-30% COGS and -20% COD), the net is $25.00. If a shop is paying a WS a monthly fee of $250.00, they would have to fill 10 incoming orders at $50.00 each just to offset their monthly base expense.

Once you've reached that plateau base, any additional incoming orders would have a marginal contribution to offsetting a shop's operating expenses after deducting any other ancillary or optional monthly fees from those profits.

On the flip side, you can also use the 20% earned commissions from your outgoing orders and cost shift them over as a credit to the debit side of the WS spreadsheet, but you would then be using those profits to subsidize the loses incurred on the filling side. And the same could be said of any outgoing wire service fees you charge as well.

Getting to the crux of the question on whether or not a Wire Service is PROFITABLE or NOT to your shop, isn't easy. Then again, it was designed by the WS's to be just that way.

All depends upon what your time is worth and if your truck was going by there anyway? However, and in my own umble opinion, if the WS's were forced to skew their numbers the way they expect their florists to, they would no longer be a factor. And then, the BUSINESS would be left to exist between REAL FLORISTS and without having to deal with the HUGE SUCKING SOUND of THE MIDDLEMEN.
lol

Take some time to apply some of the data from your WS statements to the spreadsheet. The sheet is nothing buy a calculator with some text explanations arranged to make some sense of WS statements.

You can plug in too little % of profit, or too much, too little delivery costs or too much delivery costs. The more sensible your entries the more sensible the results will show out.

You can work with the sheet very simply and you'll have a good feel for profitability. I would suggest you can pug in some unreasonable high profits on incoming orders you'll see how profitability is affected.

It was amusing to me that changing the number of orders, higher or lower, etc. How little effect they often have on the overall profitability of the periods WS orders.

The most important point is.. this is a very simple tool that doesn't take a CPA to understand or work it. Each time you get a statement you can plug in a few numbers and you'll get a feel for your WS profitability.

It is intended to be a good estimate of profitability, nothing more, just enough to keep you informed.

Thanks for your response