Calculate the standard deviation using excel formula

Assignment Help Marketing Management
Reference no: EM131394976

STEP #1: Template for Income Statement, Balance Sheet & Ratios.


The first significant milestone for this project is to build an excel worksheet EXACTLY like this. Step guidance as follows:

1. Choose your S$P 500 company.

2. Then select 2 key competitors of that company.

3. My recommendation is to use Yahoo Finance. Has tons of information on each company. Just click on COMPETITORS link to find out key competitors.

4. Download Income Statement Data and Balance Sheet Data into a new Excel w/s.

5. Start building the framework.

6. Complete Income Statement section first.

7. Complete Balance Sheet section.

8. Use Quad Graphics Data FIRST. Replicate my EXACT w/s.

9. Once you have replicated all my equations, now drop in your company data.

10. Once you have completed one company, just copy w/s into 2 new tabs.

11. Lather, rinse, repeat :-)

12. Excluding your IS and BS data input, your worksheet should be 100% equations. I will be checking for that with your final submission.

STEP #2: Complete Company Analysis (Part 2). [PDF DOCS ONLY].

Step Guidance as follows:

1. First, review the sections that are required for Part 2.

2. There are 4 sections: Stock Analysis, WACC Analysis, Beta Analysis, and Cash Flow Analysis.

3. A GOOD RULE OF THUMB is to FIRST build your financial equations by replicating my data in the worksheet.

4. KEEP IN MIND: You will need to complete this worksheet for all 2 companies. Data from this worksheet will need to be transferred to Balance Scorecard.

Stock Analysis

1. Retrieve monthly share prices by going to Yahoo HISTORICAL PRICES.

2. Calculate the Standard Deviation using excel formula.

3. Remember, the bigger the standard deviation, the riskier the stock (asset).

WACC Analysis

1. Go to your Income Statement and Balance Sheet for the data you need in this section.

2. If your company does NOT have interest expense (or long term debt) data, calculate the arithmetic average of your 2 competitor companies, and use that.

3. You may have to do some work to calculate the DIV GROWTH rate. If you cannot find this stat through research, like stock analyst opinions and recommendations, you will have to calculate it using historical dividend data. Do the following: Go to Historical Prices on YF banner. Change the setting to MONTHLY prices. Make sure you back at least 4 years. In addition to share prices, YF provides the dividend paid. Group the last 4 dividends to make your current year. Do that again for the next 4 dividends. Then do it one more time. Now calculate the dividend growth rate for each year. Use these rates for your dividend growth rate assumption.

4. PLEASE PROVIDE YOUR DIV DATA in the right section of the worksheet with AVG calculations for me to review.

BETA Analysis

1. Go to YF Historical Prices section.


3. Type in your first company. Copy and paste share prices for 12 months into your excel worksheet.

4. Do this again for your other 2 companies.

5. Now use the same screen to retrieve S&P historical prices. The S&P 500 is like a security. It is an index. Just type in S&P 500 and you will get the index prices.

6. Now replicate the monthly % change logic that I have in my worksheet.

7. The BETA calculation is the % change of the stock (num) divided by the % change of the S&P 500 (denom).

8. The final step is to calculate the 12 month arithmetic average.

9. Transfer that data point to your Balance Scorecard.

CASH FLOW Analysis

1. This data will come from the Cash Flow Statement from YF. Last report in the banner.

2. Keep numbers in the THOUSANDS.

3. Replicate my financial logic that I have in my worksheet.

4. Make sure your totals (per section) agree to the totals on YF. You do this by plugging the OTHER line item (in each section).

5. You will need to include SHARES OUTSTANDING for each year.

6. If you cannot easily find the # of shares outstanding, do a little reverse engineering. Since EPS = REV / Shares Outstanding, then just take REV / EPS to back into the # of shares outstanding for each year.

7. Remember, the WTD average #s that you transfer to the Balance Scorecard should be weighted by RECENCY. ((cur.yr x 3)+(mid.yr x2)+(base.yr x

STEP 3: Transfer Your Metrics to the Balanced Scorecard.

Attached Files:

Step Guidance as follows:

1. Download the Balanced Scorecard template.

2. Manually transfer your metrics from the Project Templates to the BS scorecard.

3. Follow all directions on the template.

4. After your metrics have been transferred, now RATE each scorecard COMPONENT based on its value to the business.

5. When you have completed, theoretically, the company with the HIGHEST score is financially the strongest.

6. You will use this BS during your presentation in Week 6.

7. Your presentation should be focused on WHY a particular company scored the way it did.

8. You must be prepared to field questions from the teacher and class.

STEP #4: Equity Research Report
Report requirements follow:

1. GOAL. The final step of the class project is to produce an EQUITY RESEARCH REPORT / COMPARISON. Equity reports are generally produced for individual companies. However, you will be preparing a COMPARISON of your two companies. Pretend you are a Financial Advisor. Your client informed you that he wants to invest $1MM of his retirement in ONE of the two companies you are researching. Your goal is to prepare a robust, well researched report that concludes with a recommendation of one company.

2. FORMAT. APA format. 5 pages of content (minimum) 10 pages maximum. Page 6 and 7 are the cover page and sources page. Abstract page is not required.

3. GRAMMAR. Grammar, spelling, punctuation, highlights, and business clarity/brevity all count.

4. SECTIONS OF PAPER. You should have the following sections in your paper: Basic Information, Analyst Opinion and Summary, Key Highlights of Both Companies, Snapshot of the Industry, Financial Ratio Analysis, Valuation Analysis, and Investment Risk Factors.

5. HOW TO REFERENCE. Do not cut and paste your Benchmark, Page 2 or your Balanced Scorecard into this report. You can reference and include "key data".

6. INCLUDE PDF DOCS. Please include PDF documents of your Benchmarks, Page 2 and Balanced Scorecard at the end of the report so that I can easily reference. Each PDF document should be ONE page and readable. That means you will have to get good at managing the size of columns and rows for each page. When in doubt, just go to my PDF examples. When you print each report, it prints nicely on ONE page. While it is small, it is still professional and readable. Remember, the inclusion of these PDF documents do not count towards your report page count.

7. REFERENCES. Please use the website references below to learn how to write great equity research reports.

8. YOUR CLASS PRESENTATION. After you have done ALL this work, you should be able to stand up in front of the class and explain your Investment Recommendation. You are expected to give a 3 minute (timed) presentation. You can have notes in front of you -- but no projector slides. My goal with this requirement is to teach you to credibly discuss and present key financial aspects of companies. And be prepared for questions from the class and myself.



Reference no: EM131394976

Are brand extensions good or bad

Are Brand Extensions Good or Bad? Some critics vigorously denounce the practice of brand extensions, because they feel that too often companies lose focus and consumers beco

Discuss the factors influencing employee issues

Discuss the factors influencing employee issues including the right to work, employment at will, due process and employee participation, health and safety standards, family re

How a new company define its business mission statement

How can a new company best define its business mission statement? Can you find examples of good and bad mission statements on the Internet? How might you improve the bad one

How you will manage each stage of the product life cycle

How you will manage each stage of the PRODUCT LIFE CYCLE for the fire slide that is built with the house or apartment and include tactical plans for the Four Ps at each s

Equity along the four dimensions of brand personality

Describe how Zappos is building brand equity along the four dimensions of brand personality. What steps might Zappos take to build its private brands without endangering its r

Critical analysis of article- basis of market segmentation

you have to prepare a 2,000 critical analysis of a given article from a peer-reviewed journal. You need to critically evaluate the article from a theoretical perspective and

What role do you believe it plays in today culture

The term, experience economy was coined in 1998 by Pine and Gilmore and is discussed in the text. How has the experience economy developed since then and what role do you be

Determine the price point the company

Using the target price of $5.99, determine the price point the company should use for the wholesaler.  Does this price point allow the company to maintain its desired 45 per


Write a Review

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd