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:
No comments:
Post a Comment