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.
