Staples POS Report Instructions – ADV II

Staples POS Report

Username: msandoval@advantus.com

Password: Willie803!

Link: Staples Exchange

S:\ISD\Staples\Staples POS

1. Sign in
2. Select Advantus Corp. 138501
3. Click Reports in the top left corner.
4. Your screen should look like the below.
5. Under Select a Company, choose ADVANTUS CORPORATION (FL) – 138501.
6. Click View Details under Sales & Inventory.
7. Your screen should look like the below.

1. Click Select Week: & chose the most recent week. The report pulls every Saturday.
2. Click Export Type: & chose Summary.
3. The Excel report will automatically pull & pop up in the bottom left hand corner
4. Once your report is open, you will need to sort the data. Click Data tab in Excel. Click Sort & then Sort by Business Unit.
5. You will see that it has sorted between Retail Sales & Contract Sales (NAD).
6. Insert a row between RET & NAD (Contract) to keep yourself & data organized.
7. Open the official Staples POS Report in the following folder:
   1. S:\ISD\Staples\Staples POS
8. In the Staples POS report and add in 4 new columns on your spreadsheet 140001 RET 138501 NAD
   1. (I keep the White and Blue color codes – to help keep them straight)
   2. 138501 RET
   3. 140001 RET
   4. 138501 NAD (Contract)
   5. 140001 NAD (Contract)
   6. (I keep the White and Blue color codes – to help keep them straight)

1. Complete the following v-lookups from the Portal Spreadsheet Data to the POS Report.:
   1. 138501 – Retail
   2. 140001 – Retail
   3. 140001 – NAD
   4. 138501 – NAD
2. When completed copy and paste Values for each column.

1. Click on a box where it states #N/A, copy that and press CTRL and F at the same time; a box like below will come up.

1. Select Replace All. This will leave you with just numbers on your Staples POS Spreadsheet.
2. Add together the two Retail columns and the two NAD columns.
   1. RET + RET
   2. NAD + NAD
3. Click where the first sku number and Retail YTD meet and insert a (+) sign in the total number and click on the new retail weeks sales number to add this information to the complete total. Once you click on the last retail number information, press Enter.
4. Repeat for the top cells in the NADcolumn.
5. Next, select the top 2 cells and drag that equation to the bottom of the spreadsheet. (ignore the gray Web YTD Total Column)
6. Save and send out to the following:
   1. Dave Schenker
   2. Chris Pitzo
   3. Kristen Bibby
   4. Thomas Schwister
   5. Tiffany Kindschy
   6. Maureen Sandoval
   7. Jenny Bowen
   8. Holly Bohn
   9. Jamie Thompson
   10. Natalie Ganas

Subject of Email: Staples POS Report

Text in Email:

Hello,

Please see attached for Staples POS report weekending ___ (put in date of the report being pulled last.)

V Look Up Instructions:

1. Click the first blank box of the column you wish to pull data for
2. Click fx symbol in the top left corner of excel
3. Click VLOOKUP
4. You will get a pop up that looks like the following:

1. Insert the Lookup_value. In the case of the Staples POS report, you will click the first number listed under column SKU Number

1. Next add the Table_Array. In the case of the Staples POS report, you will select the data in the report you pulled from the Staples POS Report. Note: you will be select the data that corresponds with the column you are completing the VLOOKUP for. If you’re completing a VLOOKUP for Retail – 13, you’ll only select the data for Retail.

1. Add the Col_Index_Number. This will be the number 4 in the case of the Staples POS Report. This is because we need to know the Total Sales from the report we pulled and insert that into our POS Report. Total Sales is the 4^th column on the report we pulled.

1. Add zero for the Range_Lookup.
2. Your VLOOKUP should look like the below. Then click OK.

1. You VLOOKUP will appear in the orignianl first blank box of the column you clicked to pull data for.
2. Double click the box the formula drags down for all the rows in that column.
3. Keep the column selected and press CTRL + C
4. Then press CTRL + ALT + V
5. The following will appear

1. Click Values. Then Click OK
2. Keep the column selected and press CTRL + F
3. Click Replace
4. Type in #N/A in the Find What
5. Type in zero in the Replace With
6. Click Replace All
7. You will repeat steps 1 – 20 until you complete VLOOKUPS for all four columns on the Staples POS Report.