1
SAF RETAIL PRICING WORKSHEET
At a Glance…
In working with the SAF Retail Pricing Worksheet, we hope you will learn what an important part it can play in making your retail floral business more profitable. If you are not that familiar with spreadsheets, we think you will also have a fun and enlightening experience as you discover the many things Excel can do. You can use it for everything from creating a budget to calculating your car payment!
To better understand how the pricing sheet program functions, here’s a brief overview of the pricing worksheet and how it works (references will lead you to more detailed descriptions of each procedure in the attached User Instructions)
I.Entering Your Rate of Markup (User Instructions – Section 1, Pages 1-2)
You need only enter your rate of markup once on the worksheet and Excel will plug it into formulas where necessary. This critical step ensures that the worksheet will set the prices for your floral products accurately.
II.Entering Stems Per Bunch (User Instructions – Section 2, Pages 3-4)
To maximize profitability, your designers need to know your price per stem when creating arrangements. This section sets the stage for accurately determining this. Usually you purchase flowers in bunches, but sometimes flowers are purchased by the individual stem. Either way, this section will take care of you!
III.Entering Your Costs (User Instructions – Section 3, Pages 5-6)
Using the cost information from your invoices, the worksheet calculates the average price you paid for all varieties of flowers purchased, then applies your markup rate to determine the retail price per bunch. Next, it divides the bunch price by the number of stems per bunch to calculate the retail price per stem. This takes the guesswork out of your pricing process and helps you reduce your cost of goods sold (COGS). Welcome to the heart of this amazing little tool!
IV.Set Your Retail Price (User Instructions – Section 4, Page 7)
Using the information the worksheet has given you, now you decide what you want to charge for your flowers, rounding up or down based on your experience. You’ll also be able to determine the retail price per bloom/break for flowers such as mini-carnations or baby’s breath, where that is appropriate.
V.Print Your Price List (User Instructions – Section 5, Page 8)
Print out your price sheet in a clean, concise format so your designers can start using it and you can start saving money!.
VI.Get Ready for Next Week (User Instructions – Section 6, Page 9)
Save portions of this week’s information and delete the rest so that you are all set for next week.
As an added bonus, we’ve created three specialized sheets in the Excel Workbook file you’ve just downloaded. One is for your general cut flowers (General), the second is for cut greens and foliage (Greens), and the third is for your tropical and exotic cut flowers (Tropicals). The separate sheet for tropicals was created to allow for a unique pricing structure. To see the different sheets, just click on the worksheet tabs at the lower left of the screen (see Worksheet Diagram at the end of the instructions for location).
1
SAF RETAIL PRICING WORKSHEET
INSTRUCTIONS
We realize that some users will be well versed in Excel while others will have little or no experience working with spreadsheet software. We have attempted to make these instructions as easy to understand as possible. If you get stuck, we’ll be here to help. And, keep in mind that these instructions apply for all three of the worksheets included in your workbook (General, Greens, and Tropicals).
So, let’s get started!
Here are a few quick definitions that you’ll find helpful:
Cell – Any one of the little boxes you see on the page when you open up your pricing worksheet
Row – A horizontal line of cells. Rows are identified by numbers that appear on the left side of the worksheet
Column – A vertical line of cells. Columns are identified by letters located at the top of the page
Cell pointer – The bold outline of a box that appears around the active cell. You can move this around the spreadsheet’s “grid” of cells by either clicking the mouse pointer on the desired cell or by using the arrow keys
If you have trouble finding these parts of the worksheet or you’re not sure about the meaning of other terms used in these directions, there is a Glossary on page 10 and a labeled Worksheet Diagram on page 11 that will assist you.
SECTION 1 – ENTERING YOUR RATE OF MARKUP
1. Locate cell C3 (Column C, Row 3) and move your cell pointer to that cell.
SAF makes no attempt, implied or specific, to recommend or suggest any markup rate. This is the cell where you will enter your shop’s rate of markup.
2. Enter the rate of mark up that is right for you and your business.
3. Your rate of markup will now drive all the calculations in your worksheet.
Excel will do all the math for you so now you can easily increase or decrease your markup rate by small increments using decimal places. For example, let’s say you want to raise your prices by a certain percentage. Simply calculate that percentage of your current markup, convert it to a decimal (0.15, 0.20, 0.33, etc.) and add it to your current markup. Isn’t that easy?! Keep in mind you must determine and enter your own markup rate.
Excel is able to make all these calculations for you because we have entered mathematical formulas in Columns I, K, M, and P. Altering these formulas may cause your worksheet to provide misleading data.It is very important that you do not type over the contents of the cells in these columns. If you do and you realize it right away (after you’ve pressed “Enter” or clicked on another cell), you may click on the “Edit” menu at the top of the screen and then click “Undo,” which will reverse the last action you did.
If you don’t catch the problem until later, you can copy the formula from another cell in the column and paste it into the cell that got messed up using the following procedure:
Click on any cell containing a correct formula in the column (I, K, M, or P) where the problem occurred (check the Formula Bar to make sure that the cell contains a formula).
Go to the menu bar at the top of the worksheet and click on the word “Edit.” When the drop down menu appears, click on “Copy.” A rolling dashed line will appear around the highlighted cell.
Click on the cell in Column I, K, M, or P where the problem occurred.
Go to the menu bar at the top of the worksheet and click on “Edit” again. When the drop down menu appears, click on “Paste.” Check the Formula Bar to make sure that the desired formula has been entered into the cell. Note that Excel will automatically change the formula to reflect the row where you’ve pasted it…now it should go back to calculating your information correctly!
Press either the “Enter” or “Esc” key to clear the rolling dashed line.
SECTION 2 – ENTERING STEMS PER BUNCH
In order to determine the retail price you want to charge per stem, you must first tell the worksheet how many stems there are in your units of purchase. If you are buying by the bunch, you will enter the number of stems per bunch in column “L” (e.g., 25 for carnations or roses, 10 for tulips, and so on). If you are buying by the stem you will enter 1.
To do this easily, we will first “hide” columns that we don’t need to use right now. Don’t worry…we’ll bring them back!
Move your mouse pointer over the letter “B” at the top of Column B. You will see a white plus sign () appear.
Click and hold your left mouse button down while you drag your mouse pointer over to Column K. This will create a shaded area from Column B to Column K.
Release the left mouse button. The columns B through K will remain shaded.
Go to the menu bar at the top of the worksheet and click on the word “Format.” When the drop down menu appears, click on “Column.” When the Column menu pops out to the side, click on “Hide.”
All the shaded area will now disappear, leaving you with Column A, listing a variety of flowers in alphabetical order, and next to it Column L, headed “Stems per Bunch”.
1. Move your cell pointer to Column L in the row where your first product appears
2. Enter the number of stems found in each bunch, or unit of purchase, for your first product.
3. Move the cell pointer down Column L by either pressing “Enter” or arrowing down and enter the number of stems found in each bunch, or unit of purchase, for all the flowers listed in Column A. Remember, this must be the quantities as you purchase them and if you buy gerbera, hydrangea, or any other flowers by the stem, just enter “1”.
4. When you have finished filling in Column L, click the “Save” icon on your tool bar at the top of the page.
The electricity could go out! Your computer could crash! It is ALWAYS a good idea to save your files frequently so you don’t lose all the work you’ve done.
5. Follow the instructions in the box below to “unhide” your columns. Now you’ll be ready to enter your costs so Excel can determine the average price you paid for each of your flowers over the week.
Time to bring back those hidden cells!
Move your mouse pointer over the letter “A” at the top of Column A. You will see a white plus sign () appear.
Click and hold your left mouse button down while you drag your mouse pointer over to Column L, which is currently right next to Column A. This will shade Columns A and L.
Release the left mouse button. The columns A and L will remain shaded.
Go to the menu bar at the top of the worksheet and click on the word “Format.” When the drop down menu appears, click on “Column.” When the Column menu pops out to the side, click on “Unhide.”
All of your columns should reappear. Just click on any cell to clear the shading.
Pretty cool, huh?!
SECTION 3 – ENTERING YOUR COSTS
You will need to get out your invoices for the last full business week, Monday through Saturday (and Sunday, if applicable).
1. For the sake of convenience, let’s start with Monday. Find the first flower listed on an invoice, then locate the row where that flower appears in Column A.
If you have a flower on your invoice that is not listed on the worksheet, simply move your cell pointer below the last flower name on the list in Column A and type in the name of the new flower. And, don’t forget you’ll need to enter the quantity of stems per unit of purchase (bunch, etc.) in Column L.
Don’t worry that your list is not in alphabetical order…we’ll fix that later
2. Move your cell pointer across the row to the corresponding cell in Column C, the Monday column.
3. Enter the cost as it appears on the invoice and hit Enter or arrow down (Note: If you paid two different prices for the exact same flower on the same day, use the higher price).
Sometimes you will be buying different grades of flowers or flowers with different stem lengths, such as carnations, roses, or alstroemeria, that will always be priced differently due to the quality or grade differences. If that is the case, list each grade of flower separately in Column A so your pricing will distinguish between the two grades (e.g., “Carnations – A grade”, followed by “Carnations – B grade”).
4. Move your cell pointer to the row containing the next flower listed on your Monday invoice and repeat the process until all prices for your Monday invoices have been entered.
5. Click the “Save” button.
6. Repeat the process for Tuesday and each subsequent day of the week until you’ve finished the week. Remember to click on “Save” at the end of each day’s invoice entries.
The prices you pay for some flowers, snapdragons for example, may be different on one day than they are on another. This could make it harder for you to set your prices effectively, but the worksheet takes care of this problem for you. If you go to Column I, you will see that Excel has already computed the average of the different prices you pay as you entered them.
7. After you have filled in all your costs for the week, if you added other flowers at the bottom of Column A, rearrange your list so that the flowers are in alphabetical order using the procedure that follows.
To alphabetize your list after adding new flowers at the bottom:
Move your mouse pointer over the number “6” at the far left of Row 6. You will see a white plus sign () appear.
Click and hold your left mouse button down while you drag your mouse pointer down to the row number corresponding to the last flower in your list.
Release the left mouse button. All the rows you’ve selected will remain shaded.
Go to the Menu Bar at the top of the worksheet and click on the word “Data.” When the drop down menu appears, click on “Sort.”
A dialog box will appear. Confirm that the “Sort by” section at the top reads “Items” with a black dot next to the word “Ascending.”
Click “OK.” Your list of flowers, along with all the information in their corresponding rows, will now appear in alphabetical order. Just click on any cell to remove the shading.
Isn’t this fun?!
Let’s review everything the worksheet has done for us so far:
In Column I (Average Cost), the average price paid for each flower throughout the week has been automatically calculated.
Using the average cost, the worksheet has also applied your rate of markup to determine the retail price per bunch (or other unit of purchase) for each flower you purchased (See Column K, Retail Price per Unit).
Excel then divided the bunch (unit of purchase) price by the number of stems to arrive at a very precise retail price per stem.
Now it’s time to take the information Excel has given you and set your prices for the week!
SECTION 4 – SETTING YOUR RETAIL PRICE
The retail price calculated in Column M is likely to be something like $3.56 or $2.87 or some other number that is impractical for your designers and managers to work with as they are pricing flower arrangements. That is why we have provided Column N. Your next step will be to look at the prices calculated in Column M and, based on that figure, enter the per stem price that you would like your designers to use for each flower.
You may want to follow the previous instructions and “hide” columns B through L (See “Make it Easy on Yourself, Part 1”). If you want to compare your pricing for this week with that of last week, do not hide Column J. Of course, remember to “unhide” them when you’re done (see “Make it Easy on Yourself, Part 2”)!
1. Move your cell pointer to the row in Column N which corresponds to the first flower on your list.
2. Using the price Excel has calculated in Column M as a guideline, enter the price you want your designers/manager to use.
When you set your prices, you will start with the price Excel has calculated and either round up or round down. If you’re still a little uncertain, consider the following scenarios:
Excel has calculated the price for Flower #1 as $3.56, which appears in Column M. If you strictly follow the rules of rounding, you would bump it up to $3.60. However, you may have a reason for wanting your designers to use $3.50 as the price. Either way, you’ll enter the price you’ve chosen in Column N.
Column M is showing $2.87 as the price for Flower #2. You may want to charge $3.00 or you may think $2.75 will be sufficient. Enter your adjusted price in
Column N.
3. Continue down the page until you have filled in adjusted prices for every flower in Column A.
4. Click “Save.”