1 / 34

National Consolidation System Process

National Consolidation System Process. Weekly Routine. National Consolidation. Wednesday Initial downloading of ECS files from distributors. ECS files can be downloaded via FTP or email address. Validate the downloaded ECS files by checking the date (must be in current week). MARS ECS Files.

akina
Download Presentation

National Consolidation System Process

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. National Consolidation System Process Weekly Routine

  2. National Consolidation • Wednesday • Initial downloading of ECS files from distributors. ECS files can be downloaded via FTP or email address. • Validate the downloaded ECS files by checking the date (must be in current week). MARS ECS Files Solomon ECS Files

  3. National Consolidation • Wednesday (continuation...) 3. Track the ECS data submission of each distributor. This will serve as the distributor ECS file submission scorecard.

  4. National Consolidation Wednesday (continuation…) 4. Follow-up on distributors who have not yet submitted the ECS data. 5. Download the ECS files. 6. Copy the downloaded ECS files to NC server in folder C:\Data\Month 7. Open NC application. User ID is “marsadmin”. Password is “xms288”. Click on Ok button.

  5. National Consolidation • Wednesday (continuation…) 8. Click on “Unzip Files to Load”

  6. National Consolidation • Wednesday (continuation…) 9. Click on “Unzip Files” button. 10. Click on “View Error Log” button to check if there are corrupted zipped files. If there are corrupted zipped files, follow-up on affected distributor to re-generate ECS files. 11. Click on Cancel button if done.

  7. National Consolidation • Wednesday (continuation…) 12. Click on “Loader” button to go to “Files to Load” screen.

  8. National Consolidation • Wednesday (continuation…) 13. Click on “Load Files” button to load the data. NOTE: Uploading could take several hours depending on how many files will be uploaded. 14. Click on “Cancel” button when done. NOTE: Do the Unzip and Load steps for the remaining distributors.

  9. National Consolidation • Thursday 1. Check/Verify the uploading progress of 1st batch of upload. 2. Check FTP/email for new ECS files. 3. Download new ECS files of distributors who failed to send on Wednesday. 4. Follow-up on distributors who have not yet submitted ECS files.

  10. National Consolidation • Thursday (continuation…) 5. Wait until done message appears, click “OK” 6. Click on “Cancel” button to go back to main window.

  11. National Consolidation • Thursday (continuation…) • Make sure that the date picker is in current date/month. 8. Click on “Solomon Data Checker”

  12. National Consolidation • Thursday (continuation…) • Click on “Open” indicating that the data verification for the current month is done. Note: If a previous data verification was done for that particular month and year, a confirmation message to overwrite the previous file created will appear, you will just have to click “Yes” to overwrite

  13. National Consolidation • Thursday (continuation…) • In data verification report, disregard the rows of all inactive distributors, and the column “Location”. • Take note of all fields having a zero (0) value, get the Distributor Code, Column Header, and the Month of Verification Example: (as shown in the figure) MDD – SOSHIPLINE ROX – SOSHIPLINE 5/2010 UPRON – SOSHIPLINE this will serves as reference in searching the items for reuploading

  14. National Consolidation Inactive Distributors found in Data Verification Report: • DC • GMI • MEMPHIS • MKO • MKZ List of Active Distributors

  15. National Consolidation • Thursday (continuation…) • Search the items for reuploading by opening the latest SOLOMON ECS file containing the data having a zero value in the data verification Note: • Use the distributor code and month of verification as reference to know what zip file to open, and the column header for text file to copy • items that will be reuploaded are in .txt format 13. Extract the items for reuploading from the zip archive 14. Copy the txt files in server C:\DATA\month\extracted

  16. National Consolidation • Thursday (continuation…) 15. Click on “Loader” button to go to “Files to Load” screen. 16. In files to load screen, Click on “Load Files” button to load the data. 17. Click on “Cancel” button when done.

  17. National Consolidation • Thursday (continuation…) 18. Generate distributors item per case by clicking on “CSV Gillette” button, a confirmation message will appear when done 19. Open C:\MarsReportSystemExcelReport and copy dist_item_case.txt (for Gillette TIRA conversion)

  18. National Consolidation • Thursday (continuation) 20. Check FTP/email for new ECS files. 21 . Follow-up on distributors who have not yet submitted ECS files 22. Download new ECS files of the remaining distributors who failed to send their ECS files 23. Upload new ECS files from the remaining distributors (see Wednesday #6 - 14)

  19. National Consolidation • Friday 1. Check/Verify the uploading progress of 2nd batch of upload. 2. Do 2nd Solomon Data Verification (see #7 – 17) Note: Make sure that no zero value is present in Data Verification Report for current month and previous months in all active distributors

  20. National Consolidation • Friday (continuation) 3. In NC application, select the month for report generation in date picker 4. Click the option button to choose a report to generate 5. Click “Generate Report” button

  21. National Consolidation • Friday (continuation…) 6. Click on “Open” indicating that the generation of report is done. Note: If a previous data verification was done for that particular month and year, a confirmation message to overwrite the previous file created will appear, you will just have to click “Yes” to overwrite

  22. National Consolidation • Friday (continuation) 7. Select the next report to generate in option, then Click on “Generate Report” button 8. Click on “Open” to save the report when generation is done Note: • do the report generation for the same month until the last report is done • “Inventory Report” is only generated when processing for the current month

  23. National Consolidation • Friday (continuation) 9. Select the previous month now in the date picker 10. Select the first report to generate in the option 11. Click “Generate Report” button 12. Click “Open” indicating that the generation is done and to save the report Note • Do the generation of reports for the previous month until the last report is done

  24. National Consolidation • Friday (continuation) 13. Check if there are missed standard reports for the current month and previous month 14. Click on “Get Maximum Date Loaded” button to get the latest date of distributor’s data loaded in NC • April 2010 • reports • May 2010 • reports

  25. National Consolidation • Friday (continuation) 15. Open a generated report for a particular month Ex. BY_ALL_SOLOMON_ACCOUNTS_TAB(Apr10) 16. Select all data in the current sheet, then Copy.

  26. National Consolidation • Friday (continuation) 17. Open consolidated file for the report type copied 18. Click on the sheet allotted for the particular month Ex. • Copied BY_ALL_SOLOMON_ACCOUNTS_TAB(Apr10) data • Click on April 10 sheet 19. Paste copied data in the current sheet Note: make sure that the sheet is blank before pasting

  27. National Consolidation • Friday (continuation) 20. Pivot the copied data, then put “DIST CODE” field in Rows, and sum of “SU”in Values 21. Convert SU values of distributors to MSU by dividing SU by 1000 Ex: DDI - 121135.8705 /1000 = 121.1359 22. Copy the calculated MSU of all distributors

  28. National Consolidation • Friday (continuation) 23. Click on MSU sheet of the consolidated file 24. Paste the copied MSU values of distributor in the month column

  29. National Consolidation • Friday (continuation) 25. Open the Maximum Date Loaded report 26. Copy the latest date loaded in NC for the month processing

  30. National Consolidation • Friday (continuation) 27. Paste the copied Maximum Date Loaded on the month processing 28. Process the remaining months in the consolidated file

  31. National Consolidation • Friday (continuation) 29. Do the consolidation process for the remaining NC reports Notes: • Only BY_ALL_SOLOMON_ACCOUNTS_TAB & BY_DISTRIBUTOR_BY_SKU_SALES reports requires the calculation of MSU and Maximum Date Loaded • In BY_TOP20MAS_BY_BRAND summary sheet report requires “CHAIN” field instead of “DIST CODE” & the calculations: SU/1000, CS/1000 and ACTUAL/1000

  32. National Consolidation • Friday (continuation) Note: Color coding for columns and sheets of Consolidated Report RED – current month Yellow – previous month Green – Past 2 month and below • Green-colored are considered as closed month • No need to reupload, generate report and consolidate once marked as green

  33. National Consolidation • Friday (continuation) 30. Put each of the consolidated report in a zip archive 31. Put BY_DISTRIBUTOR_BY_SKU_INVENTORY in a zip archive also 32. Send all zip files to FTP, ftp://65.163.13.87/NC Reports

  34. National Consolidation • Friday (continuation) 33. Prepare the NC Data Loading Tracking / Scorecard for the latest date • Get latest date from Maximum Data Loaded Report • Mark dates as Green if it is in the current week and Red if not 34. Send the Data Loading Tracking to Eric Nabos & Distributors

More Related