Dan Nebeker
MBA 614
April 12, 2016
2.1 Executive Summary
The Variance Analysis Automation project that I’ve completed
is one that will be of great value to the company where I am currently headed to
work – Savage Services. Savage Services is an international supply chain
services provider that offers logistics, transportation, loading, unloading and
other supply chain services. I was an intern there last summer and am currently
working part-time.
Savage manages hundreds of operational sites around the US,
Canada and Middle East where they move rail cars, trucks, conveyor belts,
pipelines, ships, barges, vessels, etc. Each operation must be financially monitored
in order to succeed. The company hires business managers to keep a close eye on
the finances on each of these hundreds of operation sites and monitor how
closely they are meeting projections and budgets. Projection is basically a
short-term (monthly) estimate of performance and budget is a reference to
long-term (annual) estimate of performance. The difference between the actual
results estimates are called variances. Savage employs approximately 70
business managers to monitor these variances. The financial data is pulled down
from an internal system and follows the exact same format for every operation.
The data is pulled down and analyzed daily by some managers who high volume
operations and require to know every day if those variances are out of control.
Other operations pull the data 2 or 3 times per week to track performance.
My Variance Analysis Automation tool works with the data
pulled directly from our database for any of the aforementioned operations.
Upon activating the sub procedure the macro will do some simple calculations of
the downloaded data in order to obtain the necessary variances for analysis.
After calculating the variances, a userform will pop up and ask the business
manager for inputs that will define the limit of variances that should be
analyzed. Probably because these limits vary from operation to operation and
from manager to manager there has not been an internal system built already to
handle this variance analysis. After the inputs are received through the
userform, the procedure will identify variances outside of those controls and output
a summary box and graphical illustration of the most consequential variances according
to the userform inputs given. The average time saved each time a business
manager runs the variance analysis is 20 minutes.
I know you could do the math to ballpark how much time and
money this will save the company, but I will do it for you. If the data is
analyzed on average 2.5 times per week by 70 business managers for 350
operations around the world who are on average making $45 per hour, the total
annual dollars saved as result of this project translates to:
Number of Operations for which the macro is used
|
350
|
Average number of times used per week
|
2.5
|
Hours of work saved per occurrence
|
0.3
|
Average hourly rate
|
$40
|
Total Estimated Annual
Company Savings
|
$546,000
|
- http://files.gove.net/shares/files/16w/dfn6/VBA_final_write-up_Dan_Nebeker_MBA_614.pdf
- http://files.gove.net/shares/files/16w/dfn6/DanNebeker_MBA_614_Final_v3.xlsm
No comments:
Post a Comment