Setting Up Product Import File

This document shows you how to set up a suppliers Products breakdown catalogue/ Product List for fast importing into AwardPro.

One thing to note well is that you have the option of adding a retail price to the import file or leaving it blank.

· If you leave the price column blank the system will use the the product type mark up to calculate the sell price.

· If you add the retail Price (includes Tax) to the import file the system will use that price instead.

These notes provide step by step instructions on how to import trophy products. See Importing Products – Non Trophies for other products.

· Ensure you have a copy (if available) of the product catalogue you are setting up for importing in front of you.

· Ensure that you have an understanding of the way in which the supplier has handled hardware in their costing. See Hardware, below. This will be important when you get to step 14 in the importing process.

· Ensure that the engraving plate codes and resist codes for your supplier are in AwardPro. See Engraving Plate and Resist, below.

3. Immediately go to “File” ” Save As” to save the copy of the file as an Excel 97-2003 workbook. If your Office version is prior to 2007, this automatically saves as 97-2003(Fig1). If you have Office 2007 or later, you will need to select the Excel 97-2003 from the Save as Type List – see Fig 2

4. Open and export a list of Product Types from AwardPro. You will need this so that you can copy and paste the product type into the Product Type Column. This must match exactly what has been saved in AwardPro. To do this:

c. Click “Export” and follow the wizard. This will create an Excel version of the list.

1. Open the suppliers Product List you wish to use and save a copy of it on your computer. This way you retain the original in your company files and you use the copy to work with. Leave this file open.

2. Keep your saved suppliers products spreadsheet open as well as your saved template

For Excel 97-2003 Users Click “Window” at the top of the screen. Then click “Arrange” & then “Vertical”. This will then arrange side by side both sheets making it a quicker process to copy and paste data as it’s all on the one page.

For Excel 2007 + Users Click “View” and then “Arrange All” And then click “Vertical”. This will then arrange side by side both sheets making it a quicker process to copy and paste data as it’s all on the one page.

3. Your page should then look something like this with the spreadsheets arranged side by side:

5. Click and drag the Component codes to the component codes column next to the product code. If there is more than one component that makes up the product, don’t be concerned that the product code cell will be blank for the second and subsequent components. This will be dealt with later.

7. Click and drag the Recommended Retail Price to the RRP column on the first line for the product.

8. Add the Product Type to the Product Type column, according to the list you exported at step 4 when creating the template.

11. To ensure you have the correct pricing information for your product, you may need to add an engraving plate or resist to a trophy or a glass trophy. It is at this point you will need to add a separate component for engraving plate or resist, if appropriate. Refer to notes on Hardware and Engraving Plates and Resist later in this document. You will need to consider:

· Yes - add the appropriate supplier engraving plate code to the components in your import list. Then go to step 12.

· Yes - leave a blank line on the import list and go to step 12. (You will add your gold plate at step 15.)

14. Products may have more than one component. In the importing process you will have the first line with the product code and the other components related to the product in subsequent lines with no product code. So that Award Pro knows which component relates to which product, three hash marks are put into the blank code column. The most efficient way to do this is to:

a. Click the number 1 at the top column of column 1, this will highlight the entire column

f. Double check to ensure that there has not been any extra lines added. If there has, will need to highlight the lines, Right click & delete them.

15. Now you will need to add the gold engraving plates in column 2. (At step 11 you have inserted a blank line ready for this). The most efficient way to do this is to:

c. Leaving the “Find What” line blank, type the appropriate gold plate code in the “Replace With” line.

16. Once you have finished your product import, you will need to make sure that there are no borders around your text.

For Excel 2007 users Go to the Home Tab & The Font toolbar, You will find the borders there, Click no border

When you are preparing your product list for import, There is an option of importing different quantity break pricing.

· Each product type has a certain amount of price levels set. For example, If the product type is Glass Trophy & there is only a 5 different quantity breaks within the product type, the system will only import the first 5 prices.

1. The product price level will have discounts applied so the retail price being imported will adjust the discount.

2. If there are 6 qty levels and the import file only have 5. It should enter in anyway and the last qty break just picks up the 5^th price.

3. If the import file has 7 price levels but the product type only has 6 then the program should chuck a wobbly and give an error massage.

Of course you can just ignore prices like it allows know and the program will work with the system mark up.

Each supplier treats hardware differently. Hardware is things like screws etc that you need to construct a trophy. You will need to know the details of what how your supplier has treated hardware so that when building the product you will know what to do to ensure your costing is calculated correctly.

The hardware may or may not be included as a separate item in the supplier’s component list. It may or may not include separate costing. It may or may not include engraving plate or resist. See notes below on Engraving Plates and Resist

· Evaton has a code named Hardware - this includes engraving plate and all hardware required to build trophies. Hardware has been entered as a component in AwardPro for Evaton.

· Styles Factory has different codes for each trophy with correct pricing. This includes all hardware required to build the trophy except for engraving plates. Styles has separate components of their own for hardware and this would have been imported when you imported there component list.

· PDU/ATW has a hardware code called H/Ware. This includes all hardware required to build the trophy except for plates. The code H/Ware had been entered as a component in AwardPro for PDU and ATW.

· TCD do not have a hardware code with separate pricing. However, they list the components with individual pricing and then a line that has Cost +Hardware. This is the addition of the individual components as listed plus any hardware. If you wanted to include hardware as a separate component for them you would have to calculate the hardware cost. Only perpetuals include engraving plates.

· Interleisure has Sundry as their hardware code. This code includes all hardware required to build the trophy, excluding engraving plates. Please note on resin trophies the sundry price includes engraving plate. Sundry has been entered as a component in AwardPro for Interleisure.

AwardPro has preinstalled the 5 mains suppliers’ current catalogues and component price lists so this is already set up for you.

Important note: For all suppliers except Styles Factory, Their hardware component is at a value of 0.01. To ensure the correct pricing you will need to make the QTY in the import the total amount of hardware costing. For example, hardware $0.47 is a qty of 47

As discussed under Hardware (above) most suppliers do not include engraving plates and resist in their component listing for products. You will need to check your supplier to see whether or not this is the case. Also check to see if there are exceptions.

If they have not included the plate or resist as a separate component, you will need to add it so that your costing is done correctly.

Each supplier’s Product import must contain components relevant to that supplier only. Engraving plates and resist need to be added within each supplier. AwardPro has the following codes already loaded:

For awards that require sandblasting, there are for basic sizes for the resist, see list below.

Then for each supplier, codes need to be set up. For the 5 main suppliers AwardPro has the following codes preloaded.

	RZ-PW	RZ-GTS	RZ-GTM	RZ-GTL
Evaton	RZ-PW-EV	RZ-GTS-EV	RZ-GTM-EV	RZ-GTL-EV
Interleisure	RZ-PW-IL	RZ-GTS-IL	RZ-GTM-IL	RZ-GTL-IL
Styles	RZ-PW-SF	RZ-GTS-SF	RZ-GTM-SF	RZ-GTL-SF
TCD	RZ-PW-TCD	RZ-GTS-TCD	RZ-GTM-TCD	RZ-GTL-TCD
PDU/ATW	RZ-PW-PDU	RZ-GTS-PDU	RZ-GTM-PDU	RZ-GTL-PDU

For glass only suppliers the importing process is a little different to general trophy suppliers.

1. At point 5 of the import process you will open and save a copy of the supplier’s component import list. This is because these suppliers do not have a product list.

2. At step 7 you copy and paste the Code Line from your Components Import Spread Sheet. Click onto 1 at the top of column 1 and then click Control +C

3. Click over to your Product Import Sheet, Click on the first available line in column 1 and Click CTRL+V

4. Then click CTRL+V into the first available line in Column 2 under “Component Code”

6. You will then need to change the name of some components. For example, Glass Trophies will need to be changed to Glass Trophy and Crystal Trophies to Crystal Trophy. A Quick way of doing this is to click CTRL+ F and go to the replace tab. Type Crystal Trophies and replace with Crystal Trophy

a. To do this, Click the Product Type Column. Click CTRL+F & click the replace tab

b. Type in the relevant replace, For Example, As above Glass Trophies replace to Glass Trophy. Click “Replace All”

c. Repeat the same step for any relevant component types that are different to the product type.

7. Copy and paste the Description over using the same technique as discussed in step 3.

8. For some suppliers, the size is in the description. You will need to move columns, so you will need to cut and paste this over by clicking into the column, highlighting the part you need to move & holding down CTRL+X

9. You will need to add resist component to each product. To do this, follow the following steps.

b. Hold down the CTRL key and click on each row before which you want to add rows.

10. Look at the size of the product to determine the resist size. Add the appropriate resist code to the blank component line for the trophy

12. If you come to a product that does not require resist, delete the blank line.

13. Then once you are finished, click the Product code column (via the grey bar at the top) and click CTRL+F replace. To do this:

c. Leaving the Find what column blank, Type in Replace with ###. The ### is required for the importing process, Letting the system know that the component code belongs to the one product code.

Their breakdown comes in a spreadsheet where the code and description of the product &/or the Component are in the one column.

You will need to click into the column & cut and paste the code out into the correct column

Styles have formulas in their product breakdowns to calculate their pricing. So for Stles you need to handle step 5 in the Importing procedure a little differently. Instead of just saving their list on your computer, do the following steps so that the correct pricing can be copied and pasted into your import list:

· Click on the arrow under Paste Icon at the top left of the screen and select Paste Special