Home Page
What We Are Buying Page
Products Page
Schedule Page
Delivery Routes Page
Suppliers Page
About the Clubs
Retailers List
Restaurants
Our Vision
Contact Crown of Maine
Our Delivery Truck.
Map to warehouse.
COMOC blog.
Links.
Farmers Supplies.

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 SpreadsheetCOMOC Order SpreadsheetHow to use



Download the Buying Clubs Formulas Spreadsheet
in Open Office .ods format
.
Download the Buying Clubs Formulas Spreadsheet
in Excel .xls format
.



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:
  1. In the Units column: "1#" means one pound packages and "#" means loose by the pound.
  2. In the Products column, OG means certified organically grown.
  3. Some meat and cheese weights are approximate; your invoice will reflect actual weights.
  4. The Product Code, Product and Unit Type columns are text formated, and no calculations are performed with them.
  5. The Units Per Case and Case Price columns are number formatted, as they are used in calculations.
  6. 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.