Peachtree Price Changer: How It Works

A Brief Tutorial

Before using the Peachtree Price Changer on your own data, you should play around with it using the sample Bellwether Garden Supply data that is normally included when you install Peachtree. If you don't have this data on your system, you will need to reinstall Peachtree.

Back up the Bellwether Garden Supply data before starting this brief tutorial so that you can restore the data to its original state later.

When you start the application, this screen appears, looking something like this:

If the company shown is not Bellwether Garden Supply, click the Select Company button to get the Select Company form:

The default directory for Peachtree will be shown when this form starts. Normally Bellwether Garden Supply will show in the Company Name panel on the left. If for some reason it is located in another directory, use the Drives and Directories lists to select its location. Click on the company name so it is highlighted as shown above, then click the OK button.

The default number of factors is 5. You can change the number of factors by changing the value in the Factors box. For now, leave the value at 5. You'll understand what changing this value means later.

Click the Select Worksheet button to choose the workbook you want to work in:

The price changer includes a small default spreadsheet named PriceChangeMethod1.xls that is located in the directory where the application was installed. For this exercise choose this file by clicking the Open button.

The default filter includes all items that are active. We'll change the filter to select only the records that have an ItemID beginning with "BOOK". Click the Modify Filter button:

In the box labeled "Item ID Contains" enter BOOK* to select only items that start with BOOK and end in anything. Notice that you can also set the filter to select only Inactive items or all items and can define a filter mask for the Item Type and the Location. Click OK to set the filter.

Now we are ready to work with the data in Excel. Click the View/Edit Prices button. This will start the specified Excel workbook, delete the spreadsheet Peachtree Prices, if it exists, create a new spreadsheet with the same name, and load it with the latest data from Peachtree. (If Update Spreadsheet is unchecked, the spreadsheet will not be deleted nor will it be updated with the latest data from Peachtree. This feature is provided to allow you to pull up the last spreadsheet values.)

The spreadsheet columns are divided into four groups. The first group contains the Item ID and Item Description; the second group contains the current cost (as calculated by Peachtree using the costing method specified for the item) and the current item price levels:

The third group contains your price factors:

Notice that since you specified 5 factors the columns for factors 6 through 20 (U-AI) are hidden.

The fourth group contains the new price factors:

Click on cell AK4. It should contain the formula "=E4*P4", which evaluates to the current Price 1. Examine the contents of some of the other new price cells and you should see similar formulas.

Now lets change the way the prices are computed. First we'll rename the factor column titles. Change the name "Factor 1" in column P3 to "Base", the name "Factor 2" in column Q3 to "Markup %", and the name "Factor 3" in column R3 to "Overhead". As long as we update prices later, the new names for the factor labels will be preserved so the next time we use the price changer our new labels will appear.

Next change the Base in cell P4 to be "=D4". That is, it will be the Peachtree computed cost for the item. Copy this formula down to the rest of the cells in column P.

Set the markup % for all items to be 50 and the overhead for all items to be 10.

We want to compute a new Price 1 that is the cost (1+Markup%/100) + Overhead, and the calculated amount should round up to the next cent if there is a fraction of a cent. Enter the formula "=Ceiling(P4*(1+Q4/100)+R4,0.01)" in cell AK4 and copy this formula down to the other cells in column AK.

Now we want to compute Price 2 through Price 5 to be 5% less than the previous new price rounded up to the nearest cent. Enter the formula "Ceiling(AK4*0.95,0.01)" in cell AL4 and copy the formula right to cells AM4 through AO4. Then copy the formulas in columns AM though AO down to all other items.

Finally we will write the new prices back to Peachtree. Notice that there is a small always-on-top box in the lower right corner of the screen labeled "Price Changer". Click the Update button and the data will be written to Peachtree and the Excel sheet closed.

Exit from the price changer and start it again. Notice that the company, number of factors, workbook name, and filter values have all been preserved from your previous session. Click the View Edit/Prices button to view the spreadsheet again. Notice that the current prices are now the new ones you set from the last session and that the factor labels and the formulas for the factors and new prices have all been retained from your previous session.

This is a good time to experiment on your own. Try entering labels and formulas that are appropriate for your business. When you feel comfortable with the price changer, you are ready to try it on your own company's data.

    or call free (800) 256-1786.      

______________________________________________________________________________________________