The Pricing Sheet is a two part sheet inside of an Excel file. The file itself was created in Excel 2007 and saved in the compatible 97-2003 file format. We suggest that you UPGRADE to 2007 so that you are not left behind and so that future files can operate at 100% their running capacity without losing data in the conversion to older versions. Office 2010 will be released later this year, which means that those of you without Office 2007 will become even more out of date with the latest in Office technology.New Office software is released every 3-4 years, and at about $399.00 for the Standard version (Excel, Powerpoint, Word, and Outlook), you are looking at about a $100.00 investment per year, or about $2.00 per week. Considering some of the programs that we run can run close to $1000.00 or more, $399.00 for 4 years is not that bad of a purchase. Please seriously consider upgrading to Office 2007.
The pricing sheet for download is an Excel Spreadsheet using Data Validation and Formulas in order to create point and click drop down menus in order to quickly view pricing for heads, shafts, grips, club builds, and any other data that you wish to key in. The template above already has a head, shaft, grip, and build setup keyed in so all you have to do is replace the template data on Sheet 2 with your inventory and pricing for the inventory.
Example of Price Sheet
n the photo above, there are 3 main columns that are of interest. Column A is the column that will mark which row is used for which product/service. Row 1 is your Head Selection. Row 2 is your Shaft Selection. Row 3 is your Grip Selection. Row 4 is your Build Selection.Column B is where you will input Data Validation lists, which we will get to in a bit. In this template, the Validation has already been done for you.
Column C is your pricing column. This is where the prices will show when you choose your product/service. The formula required for this has already been placed in the template so there is no need to worry about the formula for now unless you plan on changing the sheet completely.
Row 7 is where your Total from Rows 1-4 will be placed. Once again, the formula to calculate has already been put into place.
Different Sheets in Spread Sheet
The Sheet that your data is on can be found on the bottom of the Excel Spreadsheet. On the lower left hand side of the screen, you will see 3 tabs that say Sheet 1, Sheet 2, and Sheet 3. On Sheet 2, you will see the above as being filler for you to key in your information into this Spreadsheet and save it on your computer under a name that you will remember. (ex. “My Shop Pricing Sheet”)On Sheet 2, you will see Sample Head 1, Sample Head 2, etc. You will put your information into these cells. So in Row 1, if you want to put an Alpha RX-3 head, you will place it in the cell at Row 1, Column A.
It is important to note that Column A is named “heads”, Column C is named “shafts”, Column E is named “grips”, and Column G is named “build”. This is so that the VLOOKUP formula on Sheet 1 will function properly as well as the Data Validation on Sheet 1.
Replacing Sample Template Data with Your Data
As you key in your products, you will need to sort them so that they can work the right way on Sheet 1. You will undoubtedly have numerous heads, shafts, and grips to all of this will need to be in alphabetical order so that Excel’s formulas can do what they are asked to do.
Once you have keyed in all of your heads, whether it is 5 or 500, you will need to go to the DATA tab in Excel to sort the data alphabetically. You will want to click on the “A” for Column A to highlight the entire column. Once you have done this, the picture shows you where to click so that the information can be sorted. Once this is done, all of your info will be in ABC order. It is necessary for the data to be in ABC order and there should be NO spaces between products or the formula will not work.
You will repeat this process with the Shafts column, the Grips column, and the Build column.
Creating Data Validation Cells For Drop Down Menus
n order to create a drop down menu, or a list, that you can choose your head, shaft, grip, or build from, you will need to do what is called Data Validation. This is the reason that we name the columns that house the names of our heads, shafts, grips, and builds. This way, when we key in the Data Validation, we will have the name of a column to point to.The only reason that you will want to mess with the Data Validation in the template is if you are wanting to add more to your pricing sheet. This would mean that if you want to separate driver heads, iron heads, wedges, hybrids, fairways, etc., or have different tables for steel shafts or graphite, iron shafts, or driver shafts, so on and so forth. If you are wanting to add more information to the Pricing Sheet than what is on the template, then you will need to go through the same steps to create a column of alphabetized data, name that column, plug in all the information, and the have a price column beside it to coincide with.In order to create a drop down list, you will need to click on the DATA tab in the menu bar and then look for the pane called Data Validation, which can be seen in the photo above. This pane, or button, will drop down a sub menu where you will once again look for the Data Validation option to click on.
The above instructions in the picture tell you what you must do in order to create a drop down menu list for your pricing table. Click on the cell you wish the menu to be in and then follow the directions in the photo. This way, whenever you create your table, you will be able to point to a head for a club, (ex: Alpha RX-3), and with the VLOOKUP formula that will follow in the next column, you will be able to see price.
The VLOOKUP Formula
Once you have set up your Data Validation list, you will want to point that list to a price column and set the spreadsheet up so that when you choose an item from that menu, the proper price for that item will automatically show.In Row 1 on the first sheet of the spreadsheet, we have set up in this order: Column A is “Head Selection”. Column B is the place for our drop down list. Column C will be where our price will show up. When you have designated what row you are working on which cell you wish for the price to show up in, then you click on that cell. (In the example above, we have chosen cell “C1” for our price for Head Selection.)
Once you have chosen the cell, then go up to the bar above where you can type in your formula. It is marked in the photo above as the VLOOKUP Formula. You will place the following formula in that bar: =VLOOKUP(B1,Sheet2!A:B,2) This is your VLOOKUP Formula that you will use for your prices. Each time you need this formula, you will have to change certain aspects of it.
Let’s break down the formula so that you can understand better how it works:
=VLOOKUP tells the spreadsheet what formula it needs to perform. VLOOKUP is just another way of saying “vertical look up of another column”.
(B1 dictates in the formula that the VLOOKUP will be working beside the Data Validation drop down menu that is found in cell B1 on the first page.
,Sheet2! tells the VLOOKUP that it will pull data from a different Sheet on the entire spreadsheet, in this case, the data will be found on Sheet 2. You can also pull data from the same sheet you are working on, but for this template, we are pulling data from another sheet.
A:B,2) The first letter in the formula, “A” represents the column of information to pull from that will hold your heads, shafts, grips, builds, or other information. By telling the formula “:B” it will then match the information in Column A on Sheet 2, with the information in Column B on Sheet 2, if you have dictated to pull the information from Sheet 2.
So for example, on Sheet 2, in Column A, you have Iron Heads. You have many different heads in this column, from Alpha all the way to Wishon. Beside this column, in Column B, you have put the respective prices for each head. You have set up the VLOOKUP on Sheet 1 to coincide with the Data Validation drop down list that you have created in cell B1 on Sheet 1. This VLOOKUP will pop up the price from Sheet 2 once you choose your head.
If we choose an Alpha RX-3 Iron Head, and the price we have designated for this head is $99.99, then on Sheet 1 where we have the drop down list and the VLOOKUP, $99.99 will pop in the price box that we have set up.
See below for how the sheet will operate:
Choose from the list of Iron Heads. Alpha RX-3 is the iron head that we are looking for.
With the proper formula written out [=VLOOKUP(B1,Sheet2!A:B,2)], once Alpha RX-3 is chosen, the price will then become $99.99. This is why it is important to alphabetize your head, shaft, or grip list FIRST before keying in your prices. Once your list is in alphabetical order, you can go back to your price column and key in the price of that particular head. (Wishon Head A – $69.99, or Alpha Head A – $59.99, etc.)With your Shaft Selection row, you VLOOKUP would look like this: =VLOOKUP(B2,Sheet2!C:D,2)
Grip Selection row, your VLOOKUP would look like this: =VLOOKUP(B3,Sheet2!E:F,2)
Build Method row, VLOOKUP would look like this: =VLOOKUP(B3,Sheet2!G:H,2)
Below, you will see the formula to come up with the TOTAL for all components added together. With cost of head, grip, shaft, and build all added together to create a sum, you can give the customer or yourself an idea as to what a club will cost.
When you have designated the cell you want for your TOTAL, you will want to type in a SUM formula so that all the cells will be added together. In this example, we will use the following formula:=C1+C2+C3+C4
This will take the value in each cell and add them together to come up with one total. In the template you can download, this formula has already been done for you. So when you have a head that costs $89.00, a shaft that costs $149.00, a grip that runs at $9.00, and a build setup that will cost $65.00, the total that you will receive in your TOTAL cell is $312.00 for that one club.
If you keep the template AS IS, then all you will essentially have to do is key in your head names, shaft names, grip names, build method, and the prices to coincide with each. The template already has the formulas keyed in for you. The purpose of the above tutorial was to teach you how this worked and how you could do it on your own provided you want to create separate tables for Driver heads, Fairways, Hybrids, Irons, Wedges, etc. The example below is a brief portion of what you could do with this type of set up.
If you have any questions whatsoever about this template or how to adjust it, or if you are having problems viewing the template or replacing the Sample Products with your own products and prices, please e-mail me at mick@agcpgolf.com with a complete and detailed description of the issue that you are having. Please remember that I am not at your desk, nor do I have access to your computer. Therefore, if you send me an e-mail stating “It won’t work,” without telling me what you are attempting to do, I can’t help you.Some things that I will need to know when/if you e-mail me:
What version of Microsoft Excel are you using? Excel 97, Excel 2000 (version 9.0), Excel 2002 (version 10) , Excel 2003 (version 11), or Excel 2007 (version 12)?
Are you having problems opening the Document?
Are you having problems with the drop down menu?
Are you having problems with the pricing columns?
Are your products/services in alphabetical order on your information sheet?
Have you made sure not to leave empty cells between products/service?
These are the main things that I will need to know when/if you e-mail or call me. As always, if you have any questions or comments, e-mail me directly at roy@agcpgolf.com.
You must be logged in to post a comment.