Automated Year-on-Year Sales Report by Rand Blair

Last summer I interned with a large manufacturing company in the mid-west that manufactures and sells generators to hundreds of customers. While there, I found that the company was not using their sales data as well as they could. Although the data was utilized by finance to track aggregated sales each month, the data was not used by the sales department to effectively analyze sales on a customer and product level. The main reason for this comes from the inaccessibility of the data. The data comes out of the system in a report with four to five thousands rows, each containing a single transaction. Each customer could have anywhere from zero to hundreds of rows of transactions. The inaccessibility of this data makes it difficult for sales employees to pull actionable information for their accounts.

While there I worked to improve this situation by creating a process where the system-generated report could be converted into very dynamic reports that provide summary information by customer. These reports helped greatly, but were difficult to produce, relying on a complex network of vlookups, if-statements, and match excel functions that were beyond the scope of most employees.

For this VBA project I have automated this reporting process so that all the user needs to do now is paste the tab from the system-generated report, specify a few parameters and the report will run itself.

In short, this report runs by taking the thousands of transactions from the system-generated report and loading them into two pivot tables, one for quantity, and another for revenue. The VBA program then populates four dynamic reports by writing a series of vlookups to each report that reference the quantity and revenue pivot tables. The key challenge in this project is making the report dynamic in a sense that it can adjust to a varying number of transactions, columns, customers and products. By utilizing this VBA application, anyone (including those with limited experience in Excel) will be able to update the monthly sales reports in a matter of minutes, rather than the hours it would take an experienced user to update them through the manual process.

Excel File:

ftp://gove.net/RandBlair/Automated_Yearly_Sales_Report.xlsm

PDF Write-up:

ftp://gove.net/RandBlair/Final_Project_Write_Up_-_Rand_Blair.pdf