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 11, 2014

Revenue Correlation Tool

Executive Summary:
The GFA  equity fund is a undergraduate fund that has over a million dollars in assets. The fund is run by students who analyze hundreds of companies in a semester and present their findings to the class and the professor. Perhaps the hardest part of an equity valuation is forecasting revenues for the company being analyzed. I have created a tool to be used as a sanity check for forecasting revenues. 
To begin using the model you must bring it up on a Bloomberg terminal. You select your company, and then refresh the data which copies it as values into a separate sheet so it can be analyzed 
anywhere. The user can also add economic indicators by filling out a few simple fields in a user form. For example if the user were analyzing Exon Mobil and there were no indicators for oil prices he  or she could look up the ticker for oil prices on Bloomberg and add it into the model.  The update data button will also run all the correlations and trend analyses to be used for forecasting. 

Once the user selects Run Correlator the model will create a new sheet to be used for analysis. The user can filter the selected economic indicators by how highly correlated they are. They can filter based on an absolute number, for example anything with an R2  over .5 or take the top number, such as the top 5.  This summary also forecasts the selected economic indicator by a set amount each quarter for the next 3 years. The default is .25 standard deviations. This summary will also display a graph that shows that past 2 years total revenue and the 3 forecast years. The user can operate the user form to manipulate the data. They can choose different indicators, change the standard deviation in growth of the indicators, choose whether they want an exponential or linear correlation method. Once the user finds a combination they like they can press the add series button, and then select other combinations to compare. The user can add up to 5 series.

This is no perfect solution for forecasting revenue. It is to see if our forecasts our reasonable. For example if we are forecasting the revenues of Johnson and Johnson, and it has .9 R-squared with GDP growth, which is typically 2-3%, and we have forecasted 15% growth, then there is probably something wrong in our forecast. 

Useful Links:

Excel Workbook: 
Full Project Write-up and Instructions: 

1 comment:

  1. eToro is the #1 forex trading platform for beginner and full-time traders.


Blog Archive