Create a Free, Powerful System for Floral Pricing and Wholesale Purchases
One of the most useful systems I've set up in the past several years for my business is a price tracking database or a dynamic floral pricing worksheet. While it can seem somewhat tedious to enter the prices of every wholesale fresh flowers or hard goods purchase you make, having the information at your fingertips makes it worthwhile!
In this article, I’ll show you step by step how to create your own floral pricing database that will help solve some common, everyday pricing woes. But first, let’s talk about why you would want to do this.
Design: Philosophy Flowers
When you track your floral business purchases via a floral pricing worksheet or database, you can:
Check what you paid for something at a holiday vs. every day (so you know how much to raise your prices during a holiday).
See how much a specific flower was last year at a particular time of year (making wedding quotes much easier).
Quickly access pricing, rather than waiting for your sales rep to be available. (This is something that’s very helpful when you're trying to provide a quote quickly or if you're working late at night.)
Tracking your prices can be set up in several ways. A simple spreadsheet will work, but it's somewhat challenging to mine the data. On the other hand, a database works great but can be difficult to set up and/or learn.
Enter Airtable! Airtable is a software I started using a couple of years ago, and it makes creating databases super easy. You can find Airtable here.
Airtable also has a very robust free option. The database we're going to set up today will work on the free option.
Step 1: Set Up Your Price History Table for Your Floral Pricing Database
This “price history table” is where you will record information from your invoices.
As you're setting this up, you'll want to copy my Column Names included below so that the formulas below work correctly.
Once you’ve created your Airtable account (if you haven’t already), start by creating a new database and a new table within that database. Next, we'll set up the columns. Your new table will have a few automatically generated columns—just delete those.
Design: Philosophy Flowers
Column 1:
Column Name: ID
Column Type: Autonumber field
This field is just a unique placeholder. We won't use it for anything else.
Column 2:
Column Name: Item Name
Column Type: Single Select field
By setting this up as a select field, you make sure that you enter items the same each time (i.e., rose instead of roses).
Column 3:
Column Name: Pic
Column Type: attachment field
This field isn't necessary, but if you're really visual, including a picture can be helpful.
Column 4:
Column Name: Variety
Column Type: single-line text field
You'll want to keep the variety and item name separate for sorting and grouping later on.
Column 5:
Column Name: #PerBunch
Column Type: Number field
Column 6:
Column Name: Type
Column Type: Single select field
This can be as simple as FRESH / HARD or as detailed as you like. I personally like several categories (FOLIAGE / FILLER / HARD / CONTAINER / PLANT, etc.).
Column 7:
Column Name: PricePaid/bunch
Column Type: Currency field
This information will come from your invoice.
Column 8:
Column Name: # of bunches purchased
Column Type: Number field
This information will also come from your invoice.
Column 9:
Column Name: PurchasedFor
Column Type: Single Select field
This is another field that can be as detailed as you like.
Options may include: EVERYDAY, HOLIDAY, WEDDING, OTHER EVENT, etc.
You can use this for sorting and grouping later and for knowing what areas you purchased for.
Column 10:
Column Name: Supplier
Column Type: single select field
You can add suppliers now or as you go.
Column 11:
Column Name: DatePurchased
Column Type: date field
Enter your invoice date here. You can copy and paste for all the items on an invoice.
So now that you’ve created and labeled all of your columns, let’s take a look at what this should look like.
Your database should now look like this:
And once you add a few items in, it will look like this:
Step 2: The Formulas for Your Floral Pricing Worksheet
Okay, so this is the part of building your database that's either going to make you run for the tequila or get giddy with anticipation. (Sorry about that—although I do have a great margarita recipe if you need it!)
To make it easier, I'm including lots of pictures and copy/paste instructions.
And if you're sitting on the edge of your seat waiting for all the gooey formula goodness, let's meet—you're my kind of people.
Below, you'll find a “copy/paste” section for each column. Copy the information and paste it in your database. If you adjusted the name of any columns used in the formulas, you'll need to change the copied info to match.
Column 12:
Column Name: TotalPurchaseCost
Copy/paste: {# of bunches purchased}*{PricePaid/bunch}
Column 13:
Column Name: $/pc
Copy/paste: {PricePaid/bunch} / {#PerBunch}
Column 14:
Column Name: Month Purchased
Copy/paste: DATETIME_FORMAT({DatePurchased}, “MM”)
Column 15:
Column Name: Year Purchased
Copy/paste: DATETIME_FORMAT({DatePurchased}, “YYYY”)
Column 16:
Column Name: Notes
Column Type: Long text field
This is just a place you can put extra information
Step 3: Start Recording Your Wholesale Fresh Flowers and Hard Goods Pricing Information
Now that you have your floral pricing worksheet all set up, using the Prices Table, enter information from your invoices.
In the Item Name field, start typing the name of the item. The list of items you've entered before will appear. Select the correct item. You can add new items just by typing.
Upload a photo if you want. (Depending on how visual you are.)
Enter variety if needed. This column isn't required, but it can be helpful to know the difference between red roses and other colors of roses. Feel free to delete this column if you don't care about that level of detail.
Enter #PerBunch. Enter information the way it shows on your invoice. For example, most roses are sold in packs of 25 but invoiced by stem. You'll enter 1 in this field for roses.
Pick the Type. This may be simple FRESH MATERIAL or HARD GOODS, or it may be more detailed—it's up to you.
Enter PricePaid/bunch from your invoice. Make sure to match to the #perbunch. (So if your roses are invoiced by the stem, you'll enter the stem price here.)
Enter # of bunches purchased. Again, make sure to match #PerBunch (for example, if you buy one bundle of roses, but the invoice lists it as 25, enter 25 here).
Pick what the item was purchased for. You can change the list as needed. (Example: You may only need holiday/wedding/shop, or you may want Valentine's Day/Mother's Day/Christmas/wedding/retail). If you don't want to track this, delete the column.
Pick the name of the supplier. Add new ones as you go.
Enter the date purchased.
Step 4: Pulling the Information Together in your Floral Pricing Database
Just a little bit more. I promise.
The section of this article shows you some ways to view the information that might be helpful. This is where you’ll get practical with all the information and all the formulas you worked hard to input—and this is where you’ll see trends and catch important information that will help you price out your products and services in the future.
We'll start by creating a new view. Click on the arrow to the left of MAIN VIEW. Under the “Add a view” section, pick GRID.
Double click on the new name and rename. (I've used Avg Price here).
Using the Hide Fields dropdown, hide everything but the Item Name, Date Purchased, and $/pc columns.
Then using the Group button, group by Item Name.
At the top of the $/pc column, use the dropdown to get the average.
And now you can see the average price you paid for each item!
Use the Filter and Grouping sections to see the information in various ways. You can create new views or just use the main view.
Some Database Viewing options include:
Grouped by Name & Year
This is good for seeing how much wholesale floral prices have increased. Plus, you can use this to estimate future price increase when you're working on wedding quotes.
Grouped by PurchasedFor & Year
This is one of my favorite views because I can compare what I bought for Mother's Day for the last two years. I can see if my costs have gone up or not. I can quickly order the same things or make adjustments based on sales predictions.
Grouped by Supplier
This one is good for comparing prices between suppliers for things like weddings and events. You can quickly see who has the better price and if their prices are consistent.
WRAPPING IT UP
Airtable is a pretty powerful tool for manipulating data. There's quite a bit more you can do with a database like we've created today. However, it involves multiple tables and lots of formulas—not everyone's cup of tea, I know! If you're interested in more, contact me on the Team Flower Community. In the meantime, have fun playing with the numbers!
