Excel sheet

Excel sheet

Problem 1: Suppose that at the beginning of December 2008, you purchased shares in Apple, Inc. (Nasdaq: APPL) at the cost of $82.53 per share. It is now exactly five years later and you decide to evaluate your holdings to see if you have done well with this investment. The table below shows the market prices of AAPL. a. Enter the data and format. b. Create a formula to calculate your rate of return for each year. Format the results as percentages with two decimal % Rate of Return = (Current Price – Previous Price) *100 Previous Price Total % Return = 580% increase per 5 years Average % Return = 53% increase per year c. Calculate the total return for the entire holding period. What is the compound average rate of return? Total % Return = (Current Price of Stock- Original Price of Stock) *100 Original Price of Stock Average % Return = (Sum of Return Rates) *100 5 d. Create a Line Chart showing the stock price from May 2008 to May 2013. Title and chart and label the axes. Now, create an XY Scatter Chart of the same data. Which type of chart is more appropriate for the data? $- $100.00 $200.00 $300.00 $400.00 $500.00 $600.00 $700.00 2006 2008 2010 2012 2014 Price Year APPL Stock Prices $- $100.00 $200.00 $300.00 $400.00 $500.00 $600.00 Price Year APPL Stock Prices Too much formatting distracts from the data. e. Experiment with the formatting possibilities. Price $- $200.00 $400.00 $600.00 2008 2009 2010 2011 2012 2013 $82.53 $203.76 $311.89 $391.60 $519.13 $561.02 Price Year APPL Stock Prices $- $100.00 $200.00 $300.00 $400.00 $500.00 $600.00 2008 2009 2010 2011 2012 2013 Price Year APPL Stock Prices Fiscal Year 2013 2012 2011 2010 2009 Total Revenue $ 30,871.00 $ 29,904.00 $ 29,611.00 $ 26,662.00 $ 23,123.00 Net Income $ 4,659.00 $ 4,444.00 $ 4,283.00 $ 4,085.00 $ 3,193.00 Net Profit Margin 15.09% 14.86% 14.46% 15.32% 13.81% Sales Growth 7.49% Net Income Growth 9.91% Change in Sales 103.23% 100.99% 111.06% 115.31% Sales Growth 7.49% Sales Growth 7.65% (arithmetic) Problem 2: In your position as a research assistant to a portfolio manager, you need to analyze the profitability of the companies in the portfolio. Using the data for 3M Company below: a. Calculate the net profit margin for each year. b. Calculate the average annual growth rates for revenue and net income using the GEOMEAN function. Is net income growing more slowly or faster than total revenue? Is this a positive for your investment in the company? c.. Calculate the average annual growth rates for revenue and net income using the GEOMEAN function. Is the result more or less accurate than your result in the previous question? Why? b. Revenues are growing at a 7.49% compound annual rate compared to growth of 9.91% per year for net income. However, both growth rates have slowed considerably over the last two years. c. Using an arithmetic average, revenue growth is 7.65% per year. Whether or not that is more accurate depends on what you are trying to measure. The arithmetic average represents “typical” the growth in revenues in a year, whereas the geometric mean represents the compound average annual return. 0 5,000 10,000 15,000 20,000 25,000 30,000 35,000 2009 2010 2011 2012 2013 Dollars (millions) Year 3M Co. Sales Net Income d. Create a Column chart of total revenue and net income. Be sure to change the chart so that the x -axis labels contain the year numbers, and format the axis so that 2013 is on the far right side of the axis. Fiscal Year 2013 2012 2011 2010 2009 Total Revenue $ 4,680.00 $ 4,987.00 $ 4,984.00 $ 6,325.00 $ 6,460.00 Net Income $ 1,366.00 $ 3,945.00 $ 1,049.00 $ 1,232.00 $ 598.00 Net Profit Margin 29.19% 79.11% 21.05% 19.48% 9.26% Sales Growth -7.74% Net Income Growth 22.94% Change in Sales 93.84% 100.06% 78.80% 97.91% Sales Growth -7.74% Sales Growth -7.35% (arithmetic) Problem 3: In your position as a research assistant to a portfolio manager, you need to analyze the profitability of the companies in the portfolio. Using the data for Yahoo! Inc. below: a. Calculate the net profit margin for each year. b. Calculate the average annual growth rates for revenue and net income using the GEOMEAN function. Is net income growing more slowly or faster than total revenue? Is this a positive for your investment in the company? c.. Calculate the average annual growth rates for revenue and net income using the GEOMEAN function. Is the result more or less accurate than your result in the previous question? Why? a. Yahoo’s sales growth over this period is negative, despite the economic recover and many efforts by the company to turn things around. On the other hand, its net income appears to have grown quite a lot over this period. That may be misleading, though, as it 2009 was likely a low earnings year, and net income has fallen a lot since 2012. It seems likely that Yahoo! can slow the slide in earnings growth, though its best years are probably behind it. b. Based on the net profit margin, Yahoo! was more profitable than 3M. However, it seems that 3M is more likely to produce steady growth and to remain a leader in its industry. 0 5,000 10,000 15,000 20,000 25,000 30,000 35,000 2009 2010 2011 2012 2013 Dollars (millions) Year Yahoo! Inc. Sales Net Income d. Create a Column chart of total revenue and net income. Be sure to change the chart so that the x-axis labels contain the year numbers, and format the axis so that 2013 is on the far right side of the axis.

Is this the question you were looking for? If so, place your order here to get started!