Accounting Finance:Rebates-Preparing Month End Rebate Reports

Run A Query

• Click System Management→Query manager→Run a query request
• CLASS010B (class 010 inventory) (run before noon on the first day of each month)
• Double click CLASS010B
• Note: this report must be run IMMEDIATELY before the buyers start making any changes to CURE & VERE items.
• Click enter

UWD Staging

• Finished query reports are located (within hours of processing) on the superserver in the network drive Staging on Bart
• Locate mapped directory < staging (\\superserver)>
• Enter user name (if required)
• Enter password
• Click ok

• CLASS010B (saved to staging on superserver in your directory)
• Class010B (YYMMDD – xxxxxx month end.xlsx)

1. Open previous month worksheet and rename to current month directory (2017 Fiscal (current)/All Sales/YYMMXXX class010 month end)
2. Copy class010 data to class010 sheet and change header
3. Insert Cost of VEBA (f3*h3) and Diff – Cost & Cost of VEBA (g3-i3)
4. Update pivot tables (make sure the totals are reconciled to class010 sheet totals)

• Under option - enter 2
• Click enter
• Under option - enter 1
• Click enter
• Under option - enter 1
• Click enter
• Under option - enter 1 (CCP -- Pricing)
• Click enter
• Under option - enter 31 (Miscellaneous Billing Menu)
• Click enter
• Under option - enter 1 (Vendor and Customer Rebates)
• Click enter

1. Enter Selling S
2. Enter from date
3. Enter to date
4. Enter price Type CURE
5. Click enter

Click on each file and save with new file name (Excel format) to monthly sales on superserver/2015 Fiscal (current)/current month 1. CUREYYYYMMDDtoYYYYMMDDS.CSV

• Save to current month directory (2017 Fiscal (current)/All Sales/current month/ as Excel file (xlsx)

2. CUREYYYYMMDDtoYYYYMMDDD.CSV

• Save to current month directory (2017 Fiscal (current)/All Sales/current month as Excel file (xlsx)

3. CUREYYYYMMDDtoYYYYMMDDI.CSV

• Save to current month directory (2017 Fiscal (current)/All Sales/current month as Excel file (xlsx)

4. Create YYMMXXXCUREdetails.xlsx

• Open previous month worksheet and rename to current month directory (2017 Fiscal (current)/All Sales/YYMMXXX)
• Copy CUREYYYYMMDDtoYYYYMMDDD.xls data to CURE-D sheet

5. Create YYMMXXXCUREmps.xlsx

• Open previous month worksheet and rename to current month directory (2017 Fiscal (current)/All Sales/YYMMXXX)
• Copy CUREYYYYMMDDtoYYYYMMDDS.xls data to CURE-S sheet
• Create sorted sheet and MPS (create -10% MPS, adjusted MPS and zero out NC and AM. Only C shareholders are eligible for MPS)

Customer Sales

• Go to https://infonet.unipharm.com/

1. Enter User Name
2. Enter Password
3. Click Login

• Under uniVIEW, click Sales Statistics
• Under Sales Statistics uniVIEW, click Customer Sales

1. Enter from date (two digit month not fiscal period)
2. Enter to date
3. Click Search

• If the results are what you want, click Export to Excel

1. Click open
2. Insert totals at the bottom
3. Save file to (2017 Fiscal (current)/Sales Analysis/current month/YYMMXXX-Customer Sales-uniVIEW.XLS.

• Click Home
• Click Analyzer
• Click Sales from Transactions

Customer Sales vs Last Year

1. Enter current from date
2. Enter current to date
3. Enter previous from date
4. Enter previous to date
5. Click summarize drop-down and select customer
6. Click Search
7. Click Export to Excel
8. Click open
9. Delete C-F columns
10. Insert totals at the bottom and check to see that total sales is the same as YYMMXXX-Customer Sales.xls
11. Save file to (2017 Fiscal (current)/Sales Analysis/current month/YYMMXXX-Customer Sales vs Last Year-uniVIEW.XLS.

Main Supplier Sales vs Last Year

1. Click summarize drop-down and select supplier
2. Click Search
3. Click Export to Excel
4. Click open
5. Delete C-F columns
6. Insert totals at the bottom and check to see that total sales s the same as YYMMXXX-Customer Sales .xls
7. Save file to (2017 Fiscal (current)/Sales Analysis/current month/YYMMXXX-Main Supplier Sales vs Last Year-uniVIEW.XLS.

Brands (Special) Sales vs Last Year

1. Click summarized drop-down and select customer
2. Enter Item 02468817 (Harvoni 90MG/400MG appears)
3. Click 02468817 Harvoni
4. Click Search
5. Click Export to Excel
6. Click open
7. Save file to (2017 Fiscal (current)/Sales Analysis/current month/YYMMXXX-Harvoni-uniVIEW.XLS.

Brands (Special) Sales vs Last Year

1. Click summarized drop-down and select customer
2. Enter Item 02448371 (Sovaldi 400MG Tabs appears)
3. Click 02448371 Sovaldi
4. Click Search
5. Click Export to Excel
6. Click open
7. Save file to (2017 Fiscal (current)/Sales Analysis/current month/YYMMXXX-Sovaldi-uniVIEW.XLS.

Lucentis Sales vs Last Year

1. Click summarized drop-down and select customer
2. Enter Item Lucentis (note item number for Lucentis will appear)
3. Click 02132785 Lucentis 10MG
4. Click Search
5. Click Export to Excel
6. Click open
7. Save file to (2017 Fiscal (current)/Sales Analysis/current month/YYMMXXX-Lucentis-uniVIEW.XLS.

Eylea Sales vs Last Year

1. Enter Item Eylea (note item number for Eylea will appear)
2. Click 02444784 Eylea
3. Click Search
4. Click Export to Excel
5. Click open
6. Save file to (2017 Fiscal (current)/Sales Analysis/current month/YYMMXXX-Eylea-uniVIEW.XLS.

Avastin Sales vs Last Year

1. Enter Item Holkira
2. Click 02473684 Holkira
3. Click Search
4. Click Export to Excel
5. Click open
6. Save file to (2017 Fiscal (current)/Sales Analysis/current month/YYMMXXX-Holkira-uniVIEW.XLS.

Incivek Sales vs Last Year

1. Enter Item Epclusa
2. Click 0253942 Epclusa
3. Click Search
4. Click Export to Excel
5. Click open
6. Save file to (2017 Fiscal (current)/Sales Analysis/current month/YYMMXXX-Epclusa-uniVIEW.XLS.

Create Rebate Report

• Open previous month worksheet and rename to current month directory (2017 Fiscal (current)/All Sales/YYMMXXXRebateReport.xls)

1. Copy InfoNet sales to Infonet sheet
2. Copy Class 010 data from CUREmps worksheet
3. Copy Harvoni sales
4. Copy Sovaldi sales
5. Copy Lucentis sales
6. Copy Eylea sales
7. Copy Holkira sales
8. Copy Epclusa sales
9. Copy YYMMXXXRebateReport to superserver accounting\common\AR\2016-2017\Rebates & sales\imported data\FFMM – XXX 2017