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

Ratio Analysis (Martin D. Garcia)



Ratio Analysis (Martin D. Garcia)

Executive Summary
The purpose of this project is run a comprehensive ratio analysis on a publically traded company.  This analyses is a main requirement of the capstone project for the MBA 693r Corporate Treasury class.  As a cooperate treasurer, it important to constantly review a company’s performance in order to manage its cash needs.  Ratio analysis is an effective method of examining company performance.  The capstone project requires ratio analysis on five years of financial data, the current year and 4 years historical.  Additionally, a comparable analysis against the company’s main competitors is also required.

The steps used to perform ratio analyses:
  1. 1.       Obtain the company’s financial statements filed with the Securities Exchange commission.  Several web sites provide the data and can simply be downloaded.  For this project http://www.marketwatch.com was used. 
  2. 2.       Perform the ratios.  A total of 37 ratios are derived for this project, including the DuPont ratio, to evaluate a company’s level of profitability, asset management, leverage, and liquidity.  The ratios are organized into these four performance categories.
  3. 3.       Perform the comparable analyses.  This requires downloading competitor’s financial data and running exactly the same ratios analysis as on the main company being examined.  For the purpose of this project, comparing only the most current year’s data is necessary.   Although, the project could be modified to run the comparison on historical data if needed.
  4. 4.       Highlight ratios or trends in the analysis that might require further attention.   For DuPont, the option is provided to show whether a certain ratio is increasing or decreasing.   For comparable analyses, the ability to highlight minimum and maximum values for each ratio is provided.  These options give the analyst the ability to visually examine results quickly, and concentrate on the figures that need further review.


Operation of the tool:
To operate the tool, a customized ribbon with five buttons is provided.  The buttons are Import Data, DuPont, Comp Analysis, and two Highlight buttons.   When the import button is press, an input box prompts the user enter the ticker symbol of the company to be evaluated.  The financial statement data of the company is then imported into the spread sheet.  The DuPont and Comp Analysis buttons initiate the analyses.  The Highlight buttons are toggle buttons which apply and remove highlights on the ratio data.


Difficulties and lessons learned:

  •        One of the main difficulties I dealt with was working with values that had numbers mixed with text.   For example, strings such as 20.53T.   Data was received this way from the web query.  I had to use loops to go through each figure and remove the letter characters and then normalize values by converting to billions.  


  •         Each ratios requires a unique formula, which had to be coded in VBA.   I used the FormulaText to construct vba code in the actual worksheets.  Then inserted the statements in my VBA code.
  •     The FORMULATEXT() function came in very handy for doing this. 

    
4
Sheets("Dupont").Range("b4").Formula =   "=(ISData!B59-ISData!B7-ISData!B33)/(BSData!B40-BSData!B67)"
5
Sheets("Dupont").Range("b5").Formula =   "=(ISData!B59-ISData!B7-ISData!B33)/ISData!B3"
6
Sheets("Dupont").Range("b6").Formula =   "=ISData!C4"
7
Sheets("Dupont").Range("b7").Formula =   "=ISData!B59/ISData!B3"
8
Sheets("Dupont").Range("b8").Formula =   "=ISData!B6/ISData!B3"


  •        It was not possible retrieve the company name with the web quarries that were used.  To get around this the agent class module was used.   
  •       The ability to hightlight and un-highlight was needed.  Instead of created two buttons, one for each function, toggle bottons were used.  The toggle button has an extra Boolean perimeter called “pressed”.    The button can be programed to toggle between to options or states. 
  •        Working with ranges. I learned how to dynamically increase the size of a range. 




No comments:

Post a Comment

Blog Archive