Next to Staples, PepsiCo is our most active repeat client
We have had the opportunity and pleasure to have worked/work with PepsiCo on multiple simultaneously projects with several divisions (R&D, Beverages), only having more work with Staples. For Staples we have completed over 400 projects and counting. Sure, many of those are smaller projects, but still, that is a whole lot of experience with just this one client.
PepsiCo is another client that we’ve completed fully automated and integrated Microsoft Excel with Microsoft SQL Server solutions. The trend away from Access and to SQL Server as a back-end to an Excel front-end is still on the rise, and Azure is only fueling the increase in SQL Server Solutions.
PepsiCo recently asked us if we can help them with another Microsoft Excel dashboard reporting need. They receive a file each week with thousands of records representing their employees. They need to compare week to week changes. Manually comparing the files with VLOOKUPs is time consuming and error prone. Being employee data, they didn’t want it in a database on a server, but in an Excel file that they can control.
Receiving an ever growing percentage of repeat business, year-after-year, from our thousands of international clients indicates that you have found a Quality Microsoft Solutions Provider. Follow the smart-money.
We modified their Excel workbook. It consists of a sheet with the raw data, a sheet for reporting, and a hidden sheet to set parameters. We also created a custom ribbon that reacts to which class of user opened the file. The ‘Admin’ users are identified on the parameters sheet. These users see extra options on the ribbon that allows them to import the data. The process will delete any data with the specified date before loading new data in case you need to replace data. Any other users, identified by their network login, only can see the reports. The Admins can also edit lists that map different levels of managers to top level groups.
We then wrote VBA code to import the new information. We wrote formulas to add in the mapping information.
On the report sheet, we added pivot tables to group the data as the client wants to see it. The pivot tables include a Drilldown feature to be able to see what employees make up a particular number.
The report can be filtered to compare any two weeks in the database. By default it’s the two newest weeks, but the current week can be compared to last month, quarter or year.
Read Our PepsiCo Testimonial:
Worksheet highlights include:
- The parameters sheet allows the user to add additional administrators, modify the mapping of the input files (if the headings change), and alter the reports columns provided in the detailed reports (when you double-click on a number in the reports).
- In the report tables, double-clicking on any number will show the details that make up that number. The report specification is that listed on the Config sheet.
- So we took a process that took 3 hours each week and reduced it to 3 minutes. Start to finish coding took three calendar weeks.
- Let us help you with these reports that take too much of your time.
Let us help you with these reports that take too much of your time.