Advertising Review Center (ARC)

• Problem:
  • 12 departments in 20 divisions needed to update their weekly sales ad comments in 20 spreadsheets by noon every Thursday.
  • Only 1 person could be in the main spreadsheet at a time.
  • A department would create a copy of the file to enter their data.
  • A set range of rows in the spreadsheet were assigned to each department.
  • Each department would then email their copy back to advertising.
  • Advertising would open/copy/paste all the data from each department into the main spreadsheet.
  • Advertising would review the comments and return any that were incorrect or unclear back to the department.
• Solution:
  • An SQL database was created to store the ad comments.
  • A VB.Net application was created to allow the department to enter their ad comments without having to wait for a file to be unlocked or create a copy of the file.
  • As soon as the department finished their ad comments, Advertising would receive a notification.
  • Advertisng could accept or recject the comment.
  • If the comment was rejected, the department would receive a notication.
  • If the comment was accepted, the ad comment was locked and ready to be sent to the printing company.
• Estimated Savings:
  • An average of 500 people worked on weekly sales ad comments every week.
  • With the old Excelprocess, each person spent about 60 minutes every week working on ad comments.
  • With ARC, each person spent about 10 minutes every week working on ad comments.
  • 500 x 50 minutes = 25,000 minutes or about 416.67 hours.
  • The average wage was $25 per person.
  • Estimated savings in labor per week (416.67 * 25): $10,416.