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

Tuesday, April 13, 2010

Daily Sales and Inventory Control for Costco Retailer - Joe Nabrotzky

Excel VBA File: Click Here
Project Report: Click Here (pdf)
Project Report: Click Here (word)

Background

Lilac began as a maternity boutique, located at the base of the Cottonwood Canyons in Salt Lake City, Utah. Its main focus is on designer denim and top maternity brands that are made with the stylish mother in mind. With time, the sisters started their own clothing line which is bringing more opportunities for growth and has gained access on the shelves of stores and boutiques in 18 states, two counties, and 43 locations including Pickles and Ice Cream, Baby Bliss, and Costco. Recently, Lilac landed a deal with Costco that allows them to have product in stores around the nation. This new avenue of business has required Lilac to speedily ramp up and it has been a challenge to grow the company at such an enormously fast rate.

Challenge

Due to Costco’s stringent inventory guidelines, Lilac must have sufficient stock of its products in all the stores around the country. This task requires dynamic inventory control and distribution systems, especially since the number of stores that are carrying Lilac product changes daily. Currently, one of the managers downloads the daily sales from Costco’s website every morning and manually counts both the sales at each location along with the inventory. Such figures are used to determine bonuses of the workers at each location along with establishing the data to conduct reorder point analysis. This process takes up valuable time and has the potential for many mathematical errors.

Solution/Objectives

The goal was to create a system that would satisfy a few distinct objectives.

1. Compute Lilac's total daily sales for all Costco locations

2. Compute the sales from each specific location

3. Keep track of the quantity of items that were sold that day, broken down by which items sold in each location

· The future goal is to match up the daily sales with the current inventory to provide an automatic trigger for the reorder point


No comments:

Post a Comment

Blog Archive