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

Monday, April 14, 2014

SML Reservation Generator

For my final project, I created a reservation creation system. I work for a small business (Stewart Mountain Lodging) that does nightly property rentals in Sundance, UT. As part of the booking process, a reservation is created and sent to prospective guests. Because we are a small business, these reservations are made manually (rather than on an expensive hospitality software system). The reservations list the cabin, the nights of the rental, the rate per night, taxes, balance due, and our booking policies. In the past there have regularly been calculation and syntax errors on these reservations.
 For a previous excel class, I created a reservation form that used formulas to calculate the totals, pulled the nightly rate for a property with a vLookup formula, and allowed for selection of a cabin with a drop-down menu. Only one employee at the company has taken the time to learn how to use the file, and all reservations must now go through her. However, she often rushes through the process, resulting in syntax and calculation errors (such as applying discounts, making the reservation document look professional if there are alterations, etc.). Further, there is a separate template for each rental scenario (regular, Christmas, pets, multiple cabins, etc.), and the appropriate template is not always used (there are over 15).

In order to mitigate the problems with the current reservation system, I used VBA to create sequential user forms through which the creator of a reservation can input specific information, and the system (the VBA code) behind the user forms populates the reservation form as appropriate. I also programmed the system to save each new reservation under a different file name. The final product of the system is a PDF containing the finished reservation. As another aspect of this project, I created a “Quote Builder”. This quote builder can be used to quickly provide a potential guest with the cost of renting a single property without going through the entire reservation creation process. This should be a very useful tool for mathematically-challenged individuals trying to make a sale and provide a potential guest with a total cost for renting a property while the customer is on the phone. Finally, I added buttons to the Excel Ribbon to increase the overall professionalism and functionality. In the following section I will describe the specific components of the system, and how they help to fulfill these objectives.

Upload URLs:

No comments:

Post a Comment

Blog Archive