These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Wednesday, April 13, 2011

Dealership Customer Management

In February I bought a new car. My wife and I like to think of this car as our graduation present to ourselves (which is contingent upon passing this project, so life just comes full circle again). When they noticed that I was an Information Systems major, they showed me the current tool that they are using for tracking customers as they come and leave the dealership. To put things briefly, they are a mess.

When a customer comes into the dealership, the secretary notes some information about the customer, as well as the salesperson that helped that customer. After the visit, the salesperson is expected to report the results of the visit to the secretary.

The problem comes in where everything is done by hand, in Microsoft Excel. The data is put in by hand, so there is no consistency to the data as it is put in the table. If they want to do a report for the last week, they have to create a new worksheet, copy the data, and produce a report based on that data. In this process, cells are overwritten and formulas are lost. The dealership manager told me that he had to input the formulas again on a daily basis because they were being overwritten.

The objective in this new Excel spreadsheet was to make the data harder to overwrite. While we were at it, we made the data more consistent, and we made things easier for the secretaries. The reporting is now better than before because it produces better numbers with less work.

Files:
http://files.gove.net/shares/files/11w/tjones6/TylerJones-Opportunities.xlsm
http://files.gove.net/shares/files/11w/tjones6/TylerJones-FinalWriteup.pdf


No comments:

Post a Comment

Blog Archive