If you need to update custom price levels and quantity breaks for lots of items, there's a quick way to do it: export your item info, update it in Excel, then import the info back into AccountEdge.
Don't use Excel?
You can modify the export file in any program that can open text files (like Notepad), but it's easier if you use a spreadsheet program.
Tee Off is a golf store that specialises in golf clubs and accessories. They have a number of different customer levels for their pricing, and the business wants to mark up the selling price of all their items by 5%.
The Item Information window below shows one of their items with the current selling price and one quantity break.
Let's take you through the required tasks.
The first task involves exporting your item price information from AccountEdge.
- In AccountEdge, go to the File menu and choose Export Data then choose Items. The Export File window appears.
- Select the following options:
- Export data as: Tab Delimited
- First Record is: Header Record
In the list of AccountEdge Fields on the right, click to select the fields to be exported.
There are a number of required fields which are denoted with * below which must be exported. The fields selected after that will depend on what you have utilized in your custom price levels and quantity breaks. Tee Off has used Level A to Level D and Quantity Break 1 and Quantity Break 2 for their golf tees. This is all that they need to update.
For our example, we'll click to select the following fields in this order: *Item Number *Selling Price Quantity Break 1 Quantity Break 2 Price Level A, Qty Break 1 Price Level B, Qty Break 1 Price Level C, Qty Break 1 Price Level D, Qty Break 1 Price Level A, Qty Break 2 Price Level B, Qty Break 2 Price Level C, Qty Break 2 Price Level D, Qty Break 2
Specify where you want to save the export file. To make it easy to find, save it to the Desktop.
- Click Save.
- Open Microsoft Excel.
- Go to the File menu and choose Open.
- To ensure the export file is visible when trying to open it, ensure the File Types is set to All Files (*.*).
- Locate the export file and click Open.
- If the Text Import Wizard is displayed, click Finish. The contents of your export file will be displayed. Each column in the spreadsheet represents the fields in your export file, such as Item Number, Selling Price, etc.
- Highlight the entire worksheet by clicking the square between Column A and Row 1.
- Right-click inside any cell on the worksheet and choose Copy.
- Create a new worksheet by going to the Insert menu and choosing Worksheet. In your version of Excel you might be able to click the New Worksheet tab next to the current tab at the bottom of the window. See the Excel help if you're unsure.
- In the newly created worksheet, Right-click the square between Column A and Row 1 and choose Paste. This pastes the cells from the original worksheet into the new worksheet, starting at cell A1. You should now have two identical worksheets.
In the new worksheet, create a formula to update the selling prices. In our example Tee Off are updating all their prices by 5%. You need to create a formula which refers to the original worksheet. In our example the worksheet is called ITEM. The fields that need to be updated are Selling Price, Level A to Level D Qty Break1, and Level A to Level D Qty Break 2. The formula in the example to update the selling price in Cell B2 is =ITEM'!B2+(ITEM!B2*0.05)
This formula has picked up $5 from the worksheet called ITEM and increased the value by 5% as in the window above. The formula then needs to be copied to the appropriate cells to update the prices for the matrix. For more information on how to do this, please refer to Excel's Help menu.
Keep the Excel file as a template
If you'd like to keep a copy of the formulas, save this file in Microsoft Excel format before you save it as a text file.
After you have updated this information, make sure that you are in the updated worksheet.
Save the file in text format (Tab Delimited).
Before you proceed
Create a backup of your company file (File > Back Up). If the import doesn't go to plan, you can always "undo" it by restoring the backup. See the AccountEdge help for instructions (Australia | New Zealand).
- In AccountEdge, go to the File menu and choose Import Data then choose Items.
- In the Import File window, select the following options:
- Import File Format: Tab Delimited
- First Record: Header Record
- Duplicate Records: Update Existing Records
- Click Continue.
- Click to select your modified text file then click Open.
- Click to select the Import Fields on the left (one at a time) and click to match it to the corresponding AccountEdge fields on the right.
- When all Import Fields have been matched, click Import. Your custom price levels and quantity breaks will be updated.
- If any issues were encountered durting the import, check the import log file to find out which files were unsuccessful and why.
In our example, we now see the pricing levels have increased by 5%.