|
COMOC
Ordering Spreadsheets
Tools
to simplify the ordering process.
[Download
this document as a printable pdf file.]
If you have used a spreadsheet before, then using either of COMOC's Ordering Spreadsheets should be fairly
straightforward. If you are new to using a spreadsheet, we have designed them for ease of use and have built
in formulas so all you have to do is enter your product info and your members' orders. Exactly what should
be entered where and the function of each column is explained below. Questions or problems? Contact
COMOC's data nerd at tom@snakeroot.net .
There are two Spreadsheets, the “Buying Clubs Formulas Spreadsheet” and the
“COMOC Order Spreadsheet”. The “Buying Club Formulas Spreadsheet” has no items listed, which allows
you to fill your own product info into the first five columns. It has numerous formulas entered to allow immediate reporting of unit pricing,
member payments due, order totaling and more. There is also a “COMOC Order” spreadsheet which is simply the entire
COMOC Availability List pre-entered into the first five columns. You can use it to paste the items you
want into the Buying Club Formulas spreadsheet, or into any spreadsheet of your liking. Both are downloadable
in either MicroSoft Excel or OpenOffice Calc format. Both have explanatory “cell notes” attached to cells in the
first row, which display when you hover over the cell.
Buying Clubs Formulas Spreadsheet • COMOC
Order Spreadsheet • How to use
The COMOC Order Spreadsheet is designed to be used with the Buying Clubs Formulas
Spreadsheet or any spreadsheet of your choice. It consists of COMOC's entire Item Availability List with
each item and each case size on a separate line. The information on Product Code, Product Name, Unit
Type, Units Per Case, and Price Per Case is in the first five columns. This includes all case
sizes for all items, comprising more than 300 rows. We recognize that no buying club is going to want to
offer every item in every size, but it is far easier to delete or hide the rows that you don't want to use
than it is to enter all of the product info for the items that you want to order. We won't be
offended.
The COMOC Order Spreadsheet can be emailed to registered buying clubs upon
request.
NOTES:
- In the Units column: "1#" means one pound packages and
"#" means loose by the pound.
- In the Products column, OG means certified organically grown.
- Some meat and cheese weights are approximate; your invoice will reflect actual weights.
- The Product Code, Product and Unit Type columns are text formated, and no calculations are performed with them.
- The Units Per Case and Case Price columns are number formatted, as they are used in calculations.
- See how these columns are used in the Buying Clubs Formulas Spreadsheet description of these
columns.
The Buying Clubs Formulas Spreadsheet
There are three parts to this spreadsheet: the Product Info (first five columns), the Calculations (second five columns) and the Member Orders
(twenty-five pairs of columns). Next to each member order column is a column that calculates what the member owes for that order. At the top, in the
second row, is a totals row that reports how much each member owes and how much the buying club's whole order will cost.
Here are the descriptions and requirements for each of the columns:
The Product Info Columns are ready for you to type or paste in the Product Code, Product
Name, Unit Type, Units Per Case, and Price Per Case in the
first five columns. The formulas on the spreadsheet are good for up to the 100th row of items entered, but if you add
items past the 100th row, be sure to "Fill Down" all the formula and member order columns as far down as you have items
entered.
The Product Info Columns:
Product Code: Anything or nothing can go in this column. No calculations are done with entries in
this column, but sometimes it is useful when placing an order to have the product code
handy.
Product Name: Anything you want to offer members a chance to order can be entered in this column, and various
lines can be left blank to separate categories.
Unit Type: This can be lb., #, 8 oz jar, 2lb bag, or any unit a member can purchase. No calculations are
done with this column. It is handy notation to have "1#" mean one pound packages, while "#" mean loose by the pound.
Units per Case: Calculations are done using entries in this column, so all entries in this column
need to be be a pure number, like 25 or 50 or 12, but never “25#” or “12 jars”. This
is because all entries in this column are used in formulas to calculate the cost per unit for that
product.
Price Per Case: Calculations are done using entries in this column, so all entries in this column
must be a pure number, like 32.50 or 62. The column is already set up to assume entries are currency so your
entries will appear as $32.50 or $62.00.
The Calculations columns are the Price per Unit, Cost of Order, Cases Taken, Surplus
Units Ordered, and the Units Taken columns. Do not write anything in the these columns!
They are not really blank, but contain formulas required for various calculations. Wherever you see a
“--” that means a formula is in that column waiting for data to be entered somewhere else and
it shouldn't be written into. If you do happen to overwrite a formula in a cell, you can regenerate it by copying any
cell in the same column (as long as it's below row 2) and pasting into the cell. The formulas will recalculate
for that cell's position.
The Calculations Columns:
Price per Unit is a calculated column using the entry in the Case Price column divided by the entry in the Units per Case
column.
Cost of Order is calculated for each product and is the units the buying club is ordering
(Taken plus Surplus) multiplied by the Cost Per Unit.
Cases Taken is the amount for each item that is being ordered (the Taken column) divided by the Units per Case. This lets the
Masterlist Maker know when the amount ordered makes up full cases. Each row in the Taken column is filled with a “--” until an order
is entered in a Member column.
Surplus Units Ordered is the difference between what has been ordered so far and what it will take to
order a whole number of full cases. This is useful for buying clubs that routinely order surplus to
distribute among members or to cover any spillage, spoilage or shrinkage. Each row of the Surplus
column is filled with a “--” until an amount is entered into one of the Member
columns.
Units Taken is all members' orders for each item added together. When this number equals the Units per Case, then one full case has been
ordered.
The Order Entry Columns consist of the Member and Member$ in
twenty-five repeating column pairs, allowing for the recording of twenty five members' orders. The
spreadsheet is supplied with twenty-five Member, Member$ column pairs, but they don't all have to be
used. And if you need more, just copy an unused pair and paste it after the last pair.
The columns are labeled in the top row as MEM1, MEM1$, MEM2, MEM2$, etc. but you can replace the labels in
the MEM columns with your members' names or initials. For example, change MEM1 to TOM, and then MEM1$ will
automatically change to TOM$. Below each Member$ label is a total owed for that member for the items
ordered in the Member column, done by adding the first 100 rows in the column, which gives you space
for listing around 90 items. That ought to be enough, but if not, you can highlight cells F100 to your last
column and and then "Fill Down" as far as you need to.
The Order Entry Columns:
The Member column is where each member enters their order for
the number of units for each item they are ordering. Once the product info columns are filled in (the first five columns), the Member column is
the only column where any numbers are entered. If a member wants to order one whole case, they must write in the number appearing in the Units Per
Case column, and not just a "1", otherwise a single unit will be ordered (Obvious exception: when a case has only one unit in it!)
- The Member$ column automatically calculates the cost of any order placed in the Member column. Each row in the Member$ column
is filled with a “--” until an order is entered into the Member column, at which time the Member$ column will immediately
calculate the amount owed for that order.
How to use the spreadsheets. Once you have the spreadsheet, what do you do with it?
Option 1: Use on your home computer. To do this you will need a spreadsheet program on your home
computer that is capable of opening an MicroSoft Excel .xls format or an OpenOffice Calc .ods format file.
Download the version of the spreadsheet that your spreadsheet program will open. Load the spreadsheet into
your spreadsheet program, customize the member names, and enter the list of items you will be ordering.
If you want to use the spreadsheet to order from COMOC, you may use the COMOC Order
spreadsheet. Load that into your spreadsheet program, and delete the rows (items) you don't want to
order. Then copy all five columns of the remaining items, and paste it into your Buying Clubs Formulas
spreadsheet. Alternatively, you can copy the items you want row-by-row and paste them into your Buying Clubs
Formulas Spreadsheet. You will notice that the Unit Cost column now has calculated the values based
on the entries in the Case Price column.
You are now ready to enter the member orders.
If you want to use this set of instructions in printed format to help along the way, you can download this document as a printable .pdf file.
|
