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

Thursday, December 9, 2010

Brian Wade - Final Project - PinsRUS Pricing and Quote Automation

Executive Summary

(names in project changed for privacy)


I happen to know the owner of a growing business called PinsRUs. The business designs and manufactures pins, medallions, and other similar products. The company is headquartered in Utah, but also recently acquired a factory in Kansas. PinsRUs does business all over the country, filling custom orders every day.

When PinsRUs receives a request for a quote (whether over the phone, in person, or through the company website) a company sales associate must perform the necessary calculations to determine what it will cost the company to fill the order, what price the customer should be charged to earn an acceptable profit margin, etc. The pricing model involves several variables, which differ with each custom order. The pricing also depends heavily on the cost of the metal that will be used in manufacturing, which can fluctuate significantly over time. On average, the owner of PinsRUs estimated that his salesmen probably spend 15 to 20 minutes going through all the calculations to provide a single quote to a customer.


To help PinsRUs streamline and speed up the pricing process, I have utilized the knowledge and skills gained from our ISYS 520 class to develop a decision support system with VBA code in Excel. All user interaction with the program is through clean and simple user-forms. The program asks the user to input all necessary details about the order sought by the customer, accesses other information from a master data spreadsheet, and then performs all the necessary calculations almost instantaneously. The program then provides a cost and pricing breakdown with all the necessary information for PinsRUs and the potential customer. The quote breakdown is then saved to a searchable database, and can be accessed at a later time. This program was extremely well received by the owner of PinsRUs, and he estimates that it will (at the very least) cut the time required to prepare a quote by 50%, saving thousands of dollars each year.

Download Project File

View Project Writeup

No comments:

Post a Comment

Blog Archive