Page 14 - Farmers

Basic HTML Version

14
Sample Spreadsheet Using Microsoft Excel
A
B
C
D
E
F
G
H
I
J
Date:
Item
# Taken to
F. Mkt.
# Bad
# Returned
to Farm
# Sold
At Mkt.
B-(C+D)
Wholesale
Price/Unit
(Unit =flat, bag,
master, box)
C.O.G.S.
Wholesale
Value
E x F
Retail $
per Unit
(flat, box,
etc)
Expected
Gross
Sales
E x H
Gross
Profit
I - G
Strawberries
(6 qts/flat.)
24
0.5
3.5
20
$9.00
$180.00
$18.00
$360.00 $180.00
Sweet corn
(5 doz./bag)
15
0.5
1
13.5
$10.00
$135.00
$20.00
$270.00 $135.00
Pickling Cucs
(6, 3 litres/master)
6
0.25
0.5
5.25
$8.00
$42.00
$15.00
$78.75 $36.75
Beans-Green
(6, 3 litre/master)
10
0.75
0
9.25
$7.00
$64.75
$15.00
$138.75 $74.00
Cauliflower
(12 head/box)
5
0.25
0.5
4.25
$6.00
$25.50
$12.00
$51.00 $25.50
Totals
60
2.25
5.5
52.25
$447.25
$898.50 $451.25
Expected Gross
Sales (I)
$898.50
C.O.G.S. (G)
$447.25
Gross Profit (J)
$451.25
Gross Margin
(Gross Profit / Gross
Sales)
50.2%
This spreadsheet was designed by Bob Allison, an Ontario farmers’ market vendor.
The sample spreadsheet was designed using Microsoft Excel. If you don’t have access to a computer, you can fill this template out by hand. Regardless, a
copy of the spreadsheet should be completed for each truckload that goes to market.
The cells appearing in green in this template contain formulas and calculate automatically. In the sample spreadsheet, Excel was set up to do the calculations
for columns G, I and J.
The total for column I is $898.50,
indicating how much money came
home from the farmers’ market,
including the float. Assuming you took
$50 in change, your farmers’ market
sales should total $948.50 ($898.50 -
$50.00). If it isn’t that total, you need
to ask a few questions. Did:
• staff lower the price on any items to
boost sales?
• someone miscount the inventory
loaded onto the truck?
• staff replace a product purchased
the previous week at no cost
following a customer complaint?
• some products get stolen?
• a staff member pocket some
money?
If you can’t establish a realistic
wholesale price/unit, you won’t be
able to calculate your gross profit
(column J) or gross margin. If you fill
in the inventory columns (columns B,
C, D) and column H, retail price per
unit, you will still be able to calculate
what your expected gross sales
should be and compare the total
expected gross sales at the bottom of
column I to the total cash that came
back from the market. Using this
process will identify a theft problem.
The bottom line is that if you don’t
take an inventory of what went to the
farmers’ market and what is returned
after the market, you won’t know if
you have a theft problem.
Helpful Websites
and Books
The information contained in the
following section is intended to start
your search on these topic areas and
is not all-inclusive.
Books and Magazines
The New Farmers’ Market: Farm
Fresh Ideas for Producers, Managers
& Communities. By Corum, V.,
Gibson, E. & Rosenzweig, M. 2001.
Publisher ― New World Publishing.
Sell What You Sow! The Grower’s
Guide to Successful Produce
Marketing. By Gibson, Eric, 1994.
Publisher ― New World Publishing.