Basic HTML Version
13
Tracking Cash Sales
The following spreadsheet was
designed by Bob Allison, a farmers’
market vendor from Ontario, using a
Microsoft Excel spreadsheet. 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.
In the example below, Excel was set
up to do the calculations for Columns
G, I and J.
Explanation of the Spreadsheet
• Note that in column E, third row
down, there is B-(C+D). This is
to show you how column E was
calculated. Take the total number
of flats of strawberries (24) in
column B and subtract the sum
of the number of bad (column C)
and returned to the farm unsold
(column D) flats. Once you know
how many units were bad or
returned unsold, you can assume
that the rest were sold. Column E
contains formulas and is calculated
automatically.
• In column F, the wholesale price/
unit for strawberries that week was
$9 per flat. The wholesale price/
unit is what you would expect to
get for the strawberries if you had
sold them to the local grocery store
rather than going to the farmers’
market.
• The cost of goods sold (C.O.G.S.)
in column G is simply the total
wholesale value of the 20 cases
of strawberries. To make that
calculation, multiply column E by
column F.
• In column H, record the retail $
(price)/unit for strawberries that
week at the market. Since all the
calculations have to be in the same
unit (i.e. flats), take the retail price
for a quart of strawberries and
multiply it by 6, because there are
six quarts in a flat. If strawberries
sell for $3 a quart, the flat would be
worth $18 ($3 x 6 quarts).
• Column I contains formulas and is
calculated automatically. If doing
the calculations manually, multiply
the number of strawberry flats
sold, (column E shows 20 flats
were sold) x the Retail $/Unit,
(column H), which totals $360.00.
Each column is identified with a
letter A through J
A - A listing of the items taken to
market, ie, first item is flats of
strawberries
B - You took 24 flats of
strawberries, (6 quarts
per flat).
C - By the end of market day, you
threw away three quarts of
strawberries, which equals
half of a flat
D - When you unloaded the truck
back at the farm, there were
three and a half flats left
unsold
E - This means you must have
sold 20 flats of strawberries
F - The wholesale price for a flat
of strawberries is $9
G - Total wholesale value (cost
of goods sold) of the 20 flats
of strawberries that were
sold is $180 (column E x
column F)
H - The retail value of each flat is
$18 ($3/quart x 6 quarts/flat)
I - Expected gross sales of the
20 flats sold is $360
(20 x $18)
J - Your gross profit on the 20
flats you sold is $180
($360 - $180)
• Column J contains formulas and
is calculated automatically. If
calculating manually, subtract the
C.O.G.S. (total wholesale value) in
column G from the expected gross
sales in strawberries, (column I) to
give you your gross profit of $180
($360.00 - $180.00)
How the Spreadsheet
Works: