FINANENG Corporate Finance

Corporate Valuation Project

Drucker Financial Advisors is a full service brokerage company that specializes in providing its clients with reliable financial advisor services based on fundamental values of the investments in question. You have just been hired as a rookie Assistant Financial Analyst by Drucker Financial Advisors and have completed your intensive 6-month training on financial statement analysis and corporate valuation. Your supervisors would like you to get your hands dirty in the field so they have assigned you your first company to be analyzed. You need to provide your clients with a reliable financial analysis report and valuation on the company and industry they are interested in.

Remember your clients may not possess any technical financial information or the skills to complete financial analysis, but they have been investing for a lengthy period of time to judge the quality of financial reporting. Prepare this part of the project so that someone who knows nothing about the firm can get enough information to make a wise decision. However, you also want to show enough numbers and technical information in the writing to back up your story.

Your reputation as an analyst and the reputation of your firm as a financial intermediary all depend on your work. Also remember thatif you recommend a bad investment to a client, they will never come back. Do not be afraid to be truthful about the strengths and weaknesses you observe in the potential investments you analyze.

Prepare an analysis report on the company and the industry. Keep in mind that your clients will perceive your company within the context of its industry. Therefore, industry comparisons, competitor comparisons would be very important. Your report has to include a final price target and a buy/sell/hold recommendation. You will provide a full analysis report on the company as well as a 15 minute presentation in front of clients. Parts of this project will be deliverable throughout the semester to facilitate your progress as we go through the material.

Part 1: Financial Statement Analysis and Forecasting

Choose a publicly traded (on NYSE or NASDAQ for at least 3 years) industrial US firm (Do not attempt financial companies, holding companies or foreign firms since their financial statements may look very different from the standards we discuss in class.) Obtain their annual reports/financial statements for the last 2 years from Bloomberg. (Two years of financial statements will provide you with at least three years of balance sheet and income statement information.)Use the DES screenin Bloomberg for Company Information. Other suggested functions are CF (for company filings), BRC (for research on the company)...etc

Your reports should include but are not limited to the following:

1. An overview of the industry and your company's place in the industry as well as identifying major competitors.Bloomberg BI and COMP Screens provide sector trends and market comparisons.Similarly, the supply chain tool in Bloomberg (SPLC) will help you identify market shares, competition and interdependence pretty well.

2. Ratios (except EVA) on page 102 in your book for your company for the last 3 years as well as any other ratio that may be important or pertinent to your specific company. (You should have at least a total of 66 ratios - 22 per year.) Identify any trends in the ratios, try to pinpoint any strengths or weaknesses for your company.Use excel for calculations. Feel free to use any additional analysis you may need like vertical or horizontal analysis or ratios important to your industry but not covered in this list (e.g R&D/sales or advertising/sales...etc) You can compare your numbers to the ones in Bloomberg for consistency or to identify trends.

3. A comparison to industry averages and the most important competitors for your major ratios ( if necessary, all of them). Identifying the correct competitors is very important (if your company is Coca Cola, finding a competitor is simple: Pepsi, bit if your company is Apple, competition may prove to be more challenging. You can obtain industry averages and competitor information from Bloomberg for the major ratios, but you may have to calculate them yourself for most others.

4. A financial analysis of your company based on your ratio analysis and the comparison to the industry and major competitors. Interpret the ratios you have calculated and form an expert opinion on what the firm should do to increase its value (if anything). (I DO NOT want a few sentences on each ratio that are from the book. I want you to compare all the ratios for 3 years, come up with a story about the financial state of this company and present that.) Remember how we discussed all the relationships between the ratios in class and came up with a general opinion about the firm. Try to identify any strengths or weaknesses about the firm you are analyzing, and then try to explain these. DuPont Analysis may help you start the analysis. Feel free to compare the DuPont of your company with that of its sector. Sector information is available on Bloomberg (RV, FA and/or GF screens).

Part 2: Financial Forecasting and FCF Forecasts

Prepare pro-forma financial statements for your company for the next year and try to forecast FCF for next 5 years.

This section should include but is not limited to the following:

1. Sales forecast. This may be based on managerial input from the discussion part of the annual report, an arithmetic (or geometric) average of historical growth rates or based on a measure of sustainable growth rate. You can use one or multiple growth measures, but you need to justify your use of the growth rate.

2. Pro-forma financial statements for the following fiscal year. (You will need to make a lot of assumptions here, make sure you justify all of them. Your justifications may be more important than the actual number.) Use Excel. You can condense your financial statements if necessary to the major accounts. Please use percentage of sales method as well as more advanced financial forecasting or modeling methods. You will need to justify your method as well as your assumptions.

3. Estimate the company's growth rates for the next few years and a long-term growth rate based on the retention model, or DuPont Ratios or firm information (Use Bloomberg RV, FA or GF screens for information.) This part requires a lot of research, make sure your growth rates make sense.

4. Estimate FCF for next 5 years.

Part 3: Risk and Return Analysis

You will complete a historical risk and return analysis on your stock and create an efficient frontier.

Download the monthly price data from Bloomberg your company, for Newmont Mining Corp (Ticker symbol: NEM) and the S&P 500 Stock Index (Ticker symbol: ^GSPC) between August 2005-August 2015.

1. Calculate the monthlyholding period returns for each in Excel using the split-adjusted closingprices that Bloomberg provides. Beware: returns calculated from beginning to closing prices will not be split adjusted, so make sure you calculate them from closing price of the previous month to the closing price of the current month.

2. Find the average historical return (Tip: use excel's "average" function) and historical standard deviation (Tip: use Excel's "stdev" function) of the returns of all three securities.

3. Create a covariance matrix and a correlation matrix between these three securities. (Tip: Use Analysis Toolpack)

4. Using the average monthly returns and standard deviations calculated above, create portfolios made up of your company and NEM with alternately 0%, 5%, 10%, 15% ....., 95%, 100% weight in your company and the rest in NEM.

a. Calculate the expected returns and standard deviations for the returns of these portfolios between your company and NEM. (Tip: Use the correlation coefficient from #3 between your company and NEM for the standard deviation calculations. Use the formula below for the standard deviation of a two stock portfolio.

b. Graph this portfolio return and standard deviation for all possible portfolios on a graph with return on the vertical axis and standard deviation on the horizontal axis. (hint: Use Scatterplot. Your graph should look like the efficient frontier from portfolio theory lectures.)

c. Find the portfolio with the lowest standard deviation. i.e. calculate the portfolio weight you need to put in your company so you can end up with the minimum standard deviation portfolio (hint: try using solver or manual trial and error); show to several decimal places.

5. Run two regressions to find the betas (β) of your company and of NEM. (Use the SP 500 returns as a proxy for market returns.) We will use these betas in Part 4.

a. Can you have a negative beta? What does a negative beta mean?

b. Compare your betas for your company and NEM from the regressions to the betas reported for these companies in Bloomberg. Are they the same? If not, how can you explain the discrepancy between your numbers and those calculated by Yahoo!? What can be potential reasons for the difference?

6. Using the average historical return on the S&P 500 as the market return (rM) and the 10-yr treasury bond yield (you can find this on Bloomberg GOV functions) as the risk-free rate of return (rRF), calculate the required returns for your company and NEM using the CAPM. (Tip: Do not forget that all your returns here are monthly but the yield on the T-bond will be annual yield. You will need to convert that into a monthly yield in order to apply the formula.)

7. Compare your required returns from #6 to the average historical returns calculated in #2.

a. Is your company over-priced or underpriced? Why? Would you recommend a buy or hold or sell for your company?

b. Is NEM over-priced or under-priced? Why? Would you recommend a buy or hold or sell for NEM?

Part 4: Valuation

Finally combine all pieces of analysis on your company to come up with a valuation model and determine if the company is over-under priced. Come up with a buy/sell recommendation. I will provide a FCF based modeling tool for you. You are welcome to use modeling functions of Bloomberg or other resources.

This section should include at least but is not limited to:

1. Valuation of the company based either on P/E multiples and/or FCF to Equity method.

a. Use the GF function in Bloomberg to compare your company's P/E with the sector and the market. Use your analysis and estimations of growth rates from Part 2 and relative P/E ratios to determine the stock's equilibrium P/E Ratio. Use the forecast EPS from Part 2. Use the multiples approach to determine price.

b. Estimate the firm's WACC. Use the RELS function in Bloomberg for information on bonds/stocks and dividends. Use the betas you calculated in Part 3. Use the FCF estimations and long-term growth rate from Part 2. Use the DCF based corporate valuation model from class to come up with intrinsic value for shares.

c. Using these two estimations, as well as the market based estimation from Part 3, determine if your company is over-under priced?

2. A recommendation to your client on the purchase of the company's shares for long term investment. Are you recommending a "Buy", "Hold" or "Sell" strategy? And why?

3. Compare your valuation against the valuation provided in Bloomberg or by pother analysts. Try to get as close to those numbers as possible, if you cannot, please explain why.

All of the various parts of this project should work as a whole. At the end of the semester you should all be expert in your company and your industry.

Helpful hints:

• Choose a company that you have an interest in or that has a certain stand-out trait which will make it easier to analyze. For instance: Walmart operates with extremely low profit margins and similarly low A/R and inventory turnover days in sales. This efficiency is the main reason behind their stellar financial performance. Dell operates with almost no inventory. Microsoft, as well as many other tech firms,chooses to hold no debt in their capital structure. These stand-out factors may make them easier to analyze since anomalies incertain ratios will jump ateven the non-observant eye. Whereas otherwise very successful and interesting companies like Coca-Cola and Disney may be pretty hard to analyze because no inherent strength or weakness is likely to jump out at first glance. Similarly, try to choose companies where identifying competitors and comparable companies is relatively easy. Even though Amazon is a very interesting company, finding comparable may be very difficult for novice financial analysts like yourselves. This is just a friendly recommendation, you are all free to choose any industrial US firm you are interested in analyzing.

• The firms in the industry and major competitors would be very easy to identify through Bloomberg. Please use Bloomberg industry definitions rather than other financial data sources, since Bloomberg's discretionary system provides better matches - and more information.
• For Part 1, start with the Dupont identity and work from there. The two most important summary measures of performance for a company are ROE and P/E. If your company has increasing/decreasing ROE, find the reason for the change, compare it to the competitor and try to identify the specific reason. If the strength or weakness you identify is acknowledged by financial markets it will lead to higher/lower P/E ratios compared to the industry or major competitors.

• Do not flush your analysis with numbers trying to include every ratio, but provide enough numbers on the key ratios pointing out the strengths and weaknesses you address to make your analysis credible.

• Remember that you are not restricted to the bare minimum requirements provided above. Feel free to include any additional information that you think and investor in your company/industry should know about. (e.g. stock prices, graphs, betas any other risk measure, important developments in the firm or industry like expansions, new technology to squeeze more oil out of old wells, new/old regulation, strategic changes the firm plans to do.

• I would also recommend all of you to be very knowledgeable about your companies. What may seem like trivia to you can be important to a potential investor. Knowing ticker symbols, stock price histories, names of managers and directors, and major philanthropic involvements would impress your investors as well as making your presentations and reports more meaningful.

• Keep in mind that there is no specific form or style I want you to adhere to. However, the information you choose to include as well as the way you utilize that information are very important.

• Remember professionalism and presentation of information is as critical as the information content. Make sure your reports are professional, they are something you would proudly turn into your boss at Chapman Financial Advisors, not something you are doing for just a grade. Always keep in mind that your work represents you and your attitude towards the task.

• Make sure your text is well-written, free of spelling and grammar mistakes and flows logically.

Attachment:- Ratios.xlsx