Let’s admit it, some people just have BIG Item lists. What is worse, they may have 3 or 4 different ‘price levels’ that apply to that list. Even before there were the “Price Rules” for QuickBooks Enterprise, an add-on subscription, it was still possible to have some pretty sophisticated price level tables if you selected the option that permits a unique ‘price’ for each item within each price level.
For example, I have a client who has around 3500 items in their price list, and within those items they have ‘brand name’ products, ‘generic products’ and ‘specialized products’. Now they don’t use the same ‘mark-up levels’ across the board, they have higher mark-up levels for brand-name than generic and still higher mark-up levels for their specialized products. But they also offer a variety of ‘discount levels’ and that is where price levels come in.
You say, that sounds simple enough, once the mark-up is established and the selling-price over cost is established, the price level just discounts from the selling-price. True enough, with one problem; the discount for each price level also changes for each of the three product categories as well. Case-in-point, let assume there are three ‘price levels’; ‘Dealers’, ‘Majors’ and ‘Wholesalers’, in the price level for Dealers the discount might only be 5% for ‘brand-name’ products, but 15% for ‘specialized products’. Wholesalers might receive a 12.5% discount on brand-name products, but a 20% discount on ‘specialized products’.
By now you are thinking, this must be a case for Price Rules, not Price Levels…………..NOT ! This is the same kind of sophisticated pricing that ProAdvisors have had to be dealing with for years, long before there was such a thing as Price Rules….it simply means that you have to build ‘complete’ price tables using the custom price for each Price Level option. It really isn’t that complicated, the problem comes when it is time to ‘update’ all of those prices, you could easily have 10- or 12- thousand prices to ‘rekey’.
While the Price Level tables will let you apply global changes to each table already built, and while you can make those changes with a % applied, you would still have to select all the appropriate items for each of the product categories in this example, and the price table wouldn’t even display those categories, making it a royal pain in the “&^$”.
Well in my case I rely on Excel and Transaction Pro Importer when it comes time to do this job. It is simple enough to Export the existing Item list, including the various price levels and ‘price category’ (custom field) to Excel. Then make the changes using formula computations for each item and price level based upon the price categories once the list is sorted by those categories. When the adjustments are made simply link and update between Excel and QuickBooks using Transaction Pro Importer.
Transaction Pro Importer, from Baystate Consulting, is an excellent tool for importing data from Excel Spreadsheets to QuickBooks; all kinds of data, including Price Level tables. I am not going to go into all the possible uses of TPI, but I do want to spend a minute or so going over some specifics of using TPI for Price Level imports.
Price Levels in QuickBooks are one of two types, either ‘fixed percentage’, or ‘per item’. The fixed percentage price table increases or decreases all items in your Price Level table by, as the name applies, a ‘fixed percentage’. This fixed percentage is applied to one of three prices for the price level, the item’s cost, standard price (listed under Price in the item list), or the current custom price (already displayed in the Price Level).
The alternative type of Price Level is the ‘per item’ table which allows the price level to be set for each and every item uniquely. Transaction Pro Importer works with either of these two Price Level types.
In this edition of ‘What If’ we are looking at the Per Item Price Level table format and importing data into it. Figure 2 shows how the data is ‘mapped’ in TPI; it is important to insure all the fields are mapped appropriately.
The Name in the mapping table corresponds to the name of your QuickBooks Price Level table. If you are importing only one Price Level table at a time, which is what I recommend, then you can enter the name of the table as a ‘static value’ in the mapping window. If you have smaller tables, and want to import them all at the same time, then your Excel spreadsheet would need a column corresponds to each Price Level table. The Is Active field should be a Y (for yes).
The Type in the mapping table corresponds to one of those two price level types, either Fixed Percentage or Per Item. It is extremely important that you enter these the way QuickBooks reads them, so Fixed Percentage must be entered as FixedPercentage (no space, and case sensitive just as shown here).
In the case of our example, we are using the Per Item table type, so we had to enter PerItem (again with no space and case sensitive).
The Item field is the QuickBooks Name/Number for each of your QuickBooks item. The formatting in your Excel file must correspond to the formatting in QuickBooks (for example sub-items separated by a color character).
The Custom Price field is the Excel field containing your new price to be updated to the QuickBooks Price List.
The Adjust Relative To field is where you designate any price change relative to an existing value. For example the Cost (Cost), Standard Price (StandardPrice) or Current Custom Price (CurrentCustomPrice). The exact values that must be used for these options are shown in parentheses, note that there is ‘no space’ a case sensitivity must be maintained exactly as shown.
If you follow these steps you can change literally 10’s-of-thousands of custom prices in a jiffy within multiple QuickBooks Price Lists that can not be updated using the global price feature and giving you price complexity without the additional cost of the optional Price Rules you must purchase in QuickBooks Enterprise Advanced Pricing.