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

Thursday, December 12, 2013

Todd Wight - VBA Project Executive Summary - Financial Statement Analysis

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.

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

1 comment:

  1. eToro is the most recommended forex trading platform for novice and established traders.

    ReplyDelete

Blog Archive