Saturday, February 16, 2013

Excel Model: Automatic WACC

How much time do you spend trying to calculate the WACC of a company? Well here is a simple method, and in less than 15 seconds. I used the excel add-in of the posting before (here is the link), and the formula of the hurdle rate to calculate automatically this ratio in excel. There some assumptions behind the calculations. Enjoy!


Download the Excel File







Wednesday, February 13, 2013

Excel Add-in: EXCEL Stock Market Functions

What happens when you can combine the information of yahoo finance and the power of excel? Well,  Randy Harmelink did it. He created an excel add-in that allows us to extract directly from yahoo finance and many others key financial information directly into our excel. I am currently working in a company valuation and getting the data took me a lot of time, now with this tool and its frameworks it is only a matter of seconds.  Enjoy!

Link to the add-in

Tuesday, February 12, 2013

Excel Add-in: XY Chart Labeler


It is one of the most useful add-ins in excel; it helps you to add labels to XY chart data points (scatter plot). Without this add-in we have to do it manually, which sometimes it is impossible. In the future, Excel should include this functionality but for the moment a good alternative option is to use this add-in. Find the link of the add-in below and an example.

If you found this helpful, please share.

Excel Example Scatter Plot
XY Chart Labeler

Sunday, February 10, 2013

Excel VBA: United States Business Cycle


One of the most popular techniques to identify the cycle of an economic time series is the filter of Baxter & King. This filter transforms the economic variable using a Fourier series methods. I created an Excel VBA program to replicate the results of this filter. With this macro we are going to be able to get the evolution of the cycle directly in excel without the necessity to use Rats or Eviews. The program is very similar to the method used in Rats. As an example, I used the US GDP to get what is the position in the cycle in 2015-Q5; it seems that until that date the GDP was still in a contractionary phase.



Excel Economic Database: Getting the Recession Indicators

Do you know that there are four indicators that help the NBER Business Cycle Dating Committee to determine if the US economy is in recession? Yes, these variables are Industrial Production, Real Income, Employment and Real Retail Sales. Each one helps to monitor how the US economy is doing as a whole.

I remember to get these variables some years ago you needed an analyst to keep the database updated or buy an expensive specialize services, but now the Federal Reserve Bank of Saint Louis allows you to track these figures easily; they created an excel add-in where you can extract time series, manipulate data and build graphs. Now if you want to know how is behaving an economic indicator you can use this very useful add-in.


If you found this helpful, please share.


Saturday, February 9, 2013

TreeMap: World Economy 2012

The excel add-in is a powerful tool that helps us to create more complex analysis in excel. There are many of them that we can get free in internet. One of the best that I found is Sparklines (link below). As an Example, I created a treemap using this add-in which shows how the participation in the world economy is changing between 2012 and 2000. China's and India's contributions are significantly better while the developed countries are falling behind. 

If you found this helpful, please share.

Download links:
Sparklines Excel
How to install an add-in
Download Excel file here

Thursday, February 7, 2013

Excel Maps: Unemployment Rate 2012 by States

Excel allows you to include and edit maps easily. Here is a map of United States by states including unemployment rate in 2012. You can download the excel file and edit this map using conditional formatting (thank you Wayne Winston for the classes). I did not know that Indiana has an unemployment rate higher than 8%.



Wednesday, February 6, 2013

Excel Maps: World Economic Indicators

Excel is like our brain, we have only exploit less than 10% of its capabilities. Using an small VBA program and shape maps we can generate beautiful maps and at our convenience change their colors using the power of conditional formatting. In this example I mapped the 2012 Public Debt in GDP percentage. For example, the red color indicates the countries that are highly indebted. In the link below you can find the excel file that help me create this map. Enjoy!    


 https://www.dropbox.com/s/lwlkqcngh61lill/Indicators%20World%20Map.xlsm

If you found this helpful, please share.

Monday, February 4, 2013

Surfing Formulas in Excel

Tracking formulas is one of the most time consuming aspects of analyzing excel files, especially when you have to review these files created from other analysts. I developed a VBA program that helped me to reduce a lot of time understanding formulas and proactively detect potential mistakes. Enjoy!




Link to download the excel file

How do I create a Personal.XLS