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 8, 2011

VBA Final Project – Vending Machine Tracking

Executive Summary

The business problem I addressed with this project was how to track and analyze sales at a vending machine that my Dad and I operate. We own a snack and drink vending machine, and I stock it, set prices, collect money, and choose which products will be sold. So far, I have just written down in a notebook the items that are sold each collection period, and so I had no effective way of keeping track of what I have sold in the past, for how much, how much profit I have made, what is currently in the machine, and when I need to restock. This project provided an opportunity to organize that information into a spreadsheet and automate the spreadsheet to a level where I can get all the information I am looking for. As a note, I used fictional numbers for the spreadsheet I uploaded. They are sufficient to demonstrate the functionality, and are indicative of the type of numbers I see in tracking the machine.

The system I built grew beyond the original scope I had set in my proposal. This became necessary to get all the information I needed and wanted. With the system I am able to enter products through forms into the spreadsheet. I am also able to enter sales for each sales period, and the spreadsheet calculates and tracks units sold, revenue, cost of goods sold, and profit. It also has an inventory system which is automatically updated when items are purchased, sold, or written off, and you can get a current status of all inventory with the click of a button. Since cash collection does not usually line up with the revenue, and to account for sales tax when I purchase items, it became necessary to create a separate cash tracking sheet, with forms to enter that data. There are also some forms and functions used for convenience, such as the ability to change product names and see those changes reflected in relevant sheets. One of the nicer features is the ability to get a summary of units sold and profit made for any specific item, or for all items, over the user selected time period. These results can also be used to automatically generate charts for that information.

Project Write UP

Vending Machine Tracking Excel File


  1. If a customer is a man, the machine is more likely to suggest a coffee, based on past group and pattern research. Men usually take coffee.

    Inventory forms

  2. Hi
    Good Information. vending machines available from different manufacturers and suppliers. Some machines need electricity to vend the products and some are automatic.
    drinks vending machine

  3. This information is really very impressive.Vending machine provide every thing according to your choice.But first it offer his own choice.Thanks for this nice information.

    Cost of a Vending Machine

  4. Hello, I have many days looking for some system to control the only machine that I have, you might let me use your system?
    I can explain how your I delete mine data to enter data?


  5. How do we get a copy of the macro that runs to add a new product?


Blog Archive