Todd Wight
VBA Final Project
MBA 614
Fall 2013
Executive Summary
Company overview
A. Schulman is a global plastics company with over $4B in
revenue. In the Winter 2013 semester, my Field Studies team consulted with them
on a field Studies project. Part of this consultation required an in-depth analysis
of their business units in the Americas. North and Latin America each contain
four business units; however, Distribution Services was shut down in Latin
America, for the time period we analyzed, so although it is included in the
financial statements, it has no data in my computations.
Objective
Take the non-standard presentation of financial statements
and compute ratios, charts and graphs. This should eliminate the need to number
crunch manually or to continually double- and triple-check the results.
Background
The hard part of this work last year was that the financial
statements all had different lines on them, depending on the business unit
(BU), which made it very time consuming. (Ex.: SG&A would be on line 100
for one BU, but on line 112 for another BU.) It was also difficult because the
years started with the most recent first, but that meant I had to create a table
with the order reversed in order to create the graphs. I really wished I could
have created a macro to do it all automatically and save myself boat loads of
time and stress.
Note: I originally intended to include another small project
for this assignment, but I did not end up doing it. I cleared this decision
with Nathan.
Benefits of this
project
·
The automation removes the need to double check
all of my computations. The complicated part of the project was that the Excel
data was given in reverse order (2012, 2011, 2010) of the order we needed it to
be in to make a chronological analysis (2010, 2011, 2012). This was a lot of
manual work on my part but with this automation, it happens automatically.
· The automation made it easy to standardize the formatting on all of the charts. They requested data that took about 40 charts and at the time, it was a huge pain to go back and change all of the formatting. With this code, I could have simply edited a couple properties and re-run all of the code, saving hours of work.
· Each table needed three charts to make the appropriate comparisons.
· The automation made it easy to standardize the formatting on all of the charts. They requested data that took about 40 charts and at the time, it was a huge pain to go back and change all of the formatting. With this code, I could have simply edited a couple properties and re-run all of the code, saving hours of work.
· Each table needed three charts to make the appropriate comparisons.
Implementation
documentation
·
All of the outputs are placed a tab called
“Computations” which is created by the VBA code. The code is smart enough to
delete any existing sheets with the same name before running the code, and
suppresses any possible alerts in the process. This is particularly important
since Excel does not allow copying data over tables, so the sub will fail if it
does not start on an empty sheet.
·
The subroutine loops through all of the
workbooks and worksheets in each workbook and gathers all of the necessary data
into an array. The source data is contained in a folder data “data” that
resides in the same directory as the workbook running the code. At this stage,
no computations are yet made.
·
The data is then copied into the worksheets in
Excel tables with specific formatting and headers (bold, color and titles). The
cells themselves are formatted as currency.
·
The requested computations are now performed
using the tables. The computations utilize matrix computations so that they can
be done in a single line of code, avoiding unnecessary double for-loops. The
resulting data are also formatted as tables, and since these data are all
percentages, they are formatted accordingly.
·
For each table, three line charts are created:
One comparing the data for North and Latin America, and one each comparing the
business units for North and Latin America. The axes label units reflect the
charts themselves, and decimals are removed for simplicity.
o
This portion of the code is hard-coded much more
than I would like, but it was a little more complicated since it required
specific rows from the tables.
·
Provide a concise, well-organized documentation
of what you actually did for your solution. You may want to use tables or
bulleted lists to describe the components of your solution and their role in
the overall task. In any case, you should provide a textual description of the
elements so it is clear what you have done, why it was included, how it is
intended to be used in the task. Screen captures may be helpful in illustrating
what you have done.
Assistance
·
This was an individual project and I was not
part of any team.
·
I received no assistance from other individuals
on this project. This means I did not make any posts to online forums
requesting assistance.
·
On many occasions I did consult the internet to
learn how to perform various tasks, but I did not copy any extensive code.
There were a couple small, modular chunks that I did copy, but in each case I
had to modify the code to suit my needs.
Challenges and
learning points
·
It was difficult for me to outline the entire project
without making a lot of progress on it. This made it a little more difficult to
know how and where to modularize my code, and discouraged me from starting with
a UserForm that would allow for the dynamic direction of which data to gather
and analyze. Towards the end of the project, this became more clear, but
modifications of the code at this point were slow.
·
Chart objects can be finicky. I initially used a
macro to learn how to use charts in VBA; however, the recorded macro omitted
the use of a ChartObject object. Without a ChartObject object, formatting
becomes much more complicated, and I eventually realized I would have to
re-write this functionality to make use of a ChartObject object.
·
One of the challenges I had during the original
project was that they wanted to see a lot of charts and it took a long time. I
spent a lot of time trying to figure out how to copy the charts to a PowerPoint
presentation. I each chart to occupy a single slide. In the end I was
unsuccessful.
·
Going into this project I was not very familiar
with the functionality of tables. There are many things that I still do not
understand, but they are very useful, especially for aesthetic reasons.
·
From the time I began using tables for the data,
I suspected that there would be an easy way to make a new table that was the
result of a computation involving two other tables. I did not find a way to do
so using table functionality, but I did find a matrix computation that can do
it in a single line, avoiding double for-loops. It requires a little bit of
manipulation but once that is done, it is a pretty slick bit of code. Here is
an example; the matrix computation is the second line of the With block.
·
I realized that there are many ways to work with
Ranges and I became much more familiar with them. There is also a lot for me to
continue to learn!
What I would have
done next
·
I made an attempt to copy each chart to a
PowerPoint slide but I was unable to get it to work. The syntax of interfacing
with another program was confusing. It would have been fun to do, but given my
time restraints and the fact that I didn’t include it in the scope of the
project to begin with, I left it out.
·
I would have built user form that would allow
the user to select with data and ratios to compute and graph. Right now there
is a fair amount of hard-coded work. This works for this project since the
scope was well-defined to begin with, but it would be nice to accommodate
requests for more analysis in the future. I actually did quite a bit of
modularization of my code in the hopes that I would get far enough along to do
this part, but I didn’t get there.
·
I would have moved the button to a ribbon.
·
In hindsight, I probably should have moved all
of the original data into a single workbook to make things a bit cleaner, but
it works as is.
To run the program:
·
Data files will need to be in a folder named
“data” that resides in the same folder as this file.
·
Click the button “Analyze Financial Statements”
which is on the “Home Base” tab
No comments:
Post a Comment