This file contains two worksheets. The current worksheet contains the project description. Click the “firmdata” tab below for the second worksheet. The firmdata worksheet contains fundamental information on the 292 largest market cap non-financial service companies in the world. On a relative valuation basis, please identify the most undervalued firm and the most overvalued firm in this sample. In making this analysis:
· You can pick any multiple you want to make this judgment. This multiple can either be an existing multiple (like PE or EV/EBITDA), a modified version of an existing multiple (like PEGY) or a multiple that you invent. (Just make sure that you put it through the consistency test). 2 pts
· You should provide descriptive statistics (mean, minimum, maximum, median, etc) on the multiple you use, on the variables which you think affect the multiple in your write-up. 2 pts
· You have to show a scatter plot of that multiple against the key variable that you believe determine that multiple (For example, if you pick PE ratios, the key variable will be expected growth rates) 3 pts
· You have to control for differences in risk, growth and cash flow potential. A multiple regression is one way to do this but there are other approaches as well. 3 pts.
· You should make a table which shows the multiple linear regression results, and explain your results. 2 pts
· Eventually show the most undervalued and most overvalued firms and explain why. 2 pts
You can use Microsoft Excel for your statistical analysis, and provide clear explanations. For example, you can watch the video on how to use Excel to do the multiple linear regression analysis. In your report you should include the statistical output and explain how you use it. You may rank the firms based on undervaluation or overvaluation. You needn’t list all the firms. All the data you need is included in the firmdata worksheet, don’t search outside for data.