Financial Analysis with Microsoft Excel 7th Edition

Chapter15 Internet Problem 1

1. Go to http://www.microsoft.com/Investor/EarningsandFinancials/TrendedHistory/AnnualStatements.aspx and download Microsoft's annual income statements from 1994 to the most recent year available. There is a direct link to the Excel file on that page.
2. Reformat the worksheet so that it is suitable for a pivot table. This will require changing the year headings to actual dates, deleting any blank rows or columns, and then transposing the data.
3. Create a pivot table that shows the net profit margin for each year and format it appropriately. This will require you to create a calculated field.
4. Add a pivot chart that displays the results from part b over time.
5. Filter the dates so that only the last five years area displayed in the pivot table and pivot chart. This will require a custom date filter using this "is after or equal to" option.
6. How does Microsoft's net profit margin compare to the average of the company's in the S&P 500 Index? ( Hint: Use the date file from problem 5 to calculate this answer using the S&P 500 company date.xlsx workbook from the official website.)

Please use a compatible excel workbook and include and linked formulas for each cell.