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

Friday, December 12, 2014

Caleb Flint - Forecasting Automation

I. Executive Summary
Description of Business. Zarbee’s, a natural pharmaceutical company founded in Draper, Utah, is experiencing significant growth, largely due to the number of new products the company is launching in a number of major retailers nationwide. The forecasting department of the company previously utilized a separate excel file for each of the SKUs it attempted to forecast, with each file requiring a number of manual tasks that had to be completed each week and each month before the forecasting analysis was performed. As the number of SKUs proliferated, the amount of time dedicated to simply setting the files up each week significantly increased and overwhelmed those in the forecasting department.
Overview of System Built. My final project addressed these issue by automating many of these setup tasks through two simple buttons on the ribbon of the main forecasting file. The first button, to be used weekly, is designed to automatically open 100+ files and make formatting and average formula changes. This macro was built to be robust enough to account for foreseeable formatting differences in the different files, such as differences in the number of columns and placement of the reference cell. The second button, to be used monthly, adjusts the summary formulas of hundreds of cells to reflect the addition of the previous month’s actual sales data. The value of the solution lies in the combination of its simplicity for the end user and its robust code. The user of the code estimated that the weekly button will save him 2 hours per week and the monthly button about half a day per month. 

No comments:

Post a Comment

Blog Archive