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 12, 2016

Variance Analysis Executive Summary

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
Average number of times used per week
Hours of work saved per occurrence
Average hourly rate
Total Estimated Annual Company Savings

No comments:

Post a Comment

Blog Archive