Need help with this accounting 201 project please. My company is Apple. Annual R
ID: 2523115 • Letter: N
Question
Need help with this accounting 201 project please. My company is Apple.
Annual Report Project
General
The objective of these assignments is to relate the material being studied in ACG 201 lectures to actual financial statements. You will gain experience obtaining a company’s annual report and using the information found in that report. After your instructor had divided you into teams, you will work on one of the following companies:
Apple Microsoft
Alphabet (Google) Facebook
You can find a copy of the Annual Report from https://www.sec.gov/edgar.shtml (Form 10-K). In addition, you should consult other financial and business sources (LexisNexis, The Wall Street Journal, etc.). The assignment includes using Excel to analyze the financial data, creating pie charts in Excel (see page 2), and (see page 3), and answering questions based on your analysis (see page 4 and 5) (ratios, charts, questions worth 50 points) and a group presentation on WebEx (50 points). You will need a computer with a webcam to make the presentation. If you do not have a webcam, you can reserve a computer in the library.
Excel Assignment
Locate your company’s 2016 10-K (Go to https://www.sec.gov/edgar.shtml, do not GOOGLE or go to company’s website) You can ignore any amendments to the 10K. There will be a 10K for each year, make sure you select the one that corresponds to 2016, even if it was released in 2017. (You can make sure by opening the 10K and making sure that it applies to the fiscal year ended some time in 2016.)
Find the Balance Sheet in the 10-k and in the “Balance Sheet” tab of your company’s excel document, enter all of the amounts for 2016.
Follow the same process in step 2 for the “Income Statement” tab.
Determine values for all ratios listed on the Ratio sheet of the Excel workbook (see below).
Create charts (see below)
Questions Assignment
Please answer all of the questions starting on the page 6 of this document. Use Word and create a new document to answer the questions. Use complete sentences and correct grammar. DO NOT PASTE FROM a website. If you wish to OCCASIONALLY use a direct quote from a website, you may do so as long as you correctly cite the information. There are four sections of questions: Company Section, Financial Background, Company Leverage/Solvency, Accounting Information. Create a new WORD document for the questions and upload to BB (one per group)
Instructions for Ratio Analysis Assignment (Excel)
Click on the sheet named “Ratios.” (your company workbook downloaded from BB) Use the cell referencing commands to carry the amounts over to the sheet named “Ratios”. Analysis of some ratios can be compared more easily across its own industry. Each ratio should be calculated using the proper format and style. They should be computed using formulas in Excel, not a calculator.
Liquidity Ratios
Current Ratio Current Assets
Current Liabilities
Companies want their current ratio to be higher than 1. It measures the ability to pay current liabilities with current assets.
Working Capital Current Assets - Current Liabilities
Companies want a positive number. Measures the ability of a company to pay its debts if needed.
Leverage and Solvency
Debt to Total Assets Total Liabilities
Total Assets
Measures the percentage of assets that are financed by creditors. Less than 60% is good. The greater the amount of debt, the riskier the company.
Equity to Total Assets Total Shareholders’ Equity
Total Assets
Measures the percentage of assets financed by stockholders. Between 40% and 60% is good.
Profitability
Gross Profit Percentage Gross Profit
Net Sales
Measures the percentage of profit that is derived from the sale of goods.
Profit Margin Net Income
Net Sales
Measures the amount of profit from each dollar of sales after all operating expenses.
Management of Working Capital
Receivable Turnover Sales
Average Accounts Receivables
Measures the amount of days it takes for a company to turn over its accounts receivable. Average = 12.0.
Inventory Turnover* Cost of Sales
Average Inventory
Measures the amount in days that it takes for a company to turn over its inventory. Average = 5.0.
*Facebook does not have inventory so do not compute.
Return of Equity Net Income
Average Shareholder’s Equity
Measures the income earned on each dollar of investment by stockholders.
Earnings per Share * Net Income
Common Shares outstanding
Measures the Net Income earned on each share of Common Stock.
*For Alphabet: Only compute for Class A stock
Price-Earnings Ratio* Stock Price per Share **
Earnings per Share
Measures the investors’ assessment of a company’s future earnings.
*For Alphabet, only use CLASS A stock
**Go to NASDAQ.com to get the year end stock price at the close of the day.
Instructions for Chart Assignments (Excel)
You will be making three graphs. The first two graphs will be a composition of the current assets in an exploded pie chart. You will need to use absolute cell referencing to make a common size balance sheet, which is assets as a percentage of total Current Assets. Place the Current Assets as a percentage in the column next to the numbers in the Balance Sheet. Remember to save often in order not to lose your work!
1) Create an Exploded Pie Chart with 3-D effects for both charts.
a. The title of the chart should be “Composition of Current Assets.” You should also include the year within the title.
b. The legend should be placed at the right side of the chart. Place the chart in a new sheet that you renamed “Charts.”
c. Do not use any data labels for these two charts.
2) Complete a similar chart for the prior year.
3) The third chart you will create will be a breakdown of expenses in comparison from the two most current years. Create a Column Chart and place it in the “Charts” sheet you created at the beginning of the assignment. Show the legend and place it at the right of the chart. Rename the legend for the year the expenses were incurred.
Title the chart “Expense Breakdown.”
The chart should be placed in the new sheet that you renamed “Charts” that was created in the beginning.
Add data labels and percentages
A list of the expenses that you will use for your company is listed next:
Facebook uses cost of revenue, research & development, marketing & sales, and general & administrative expenses.
Microsoft uses cost of revenue, research & development, marketing & sales, general & administrative, goodwill impairment, integration & restructuring, and provision for income taxes.
Alphabet uses costs of revenues, general and administrative, sales and marketing, and research and development
Apple uses cost of sales, selling, general and administrative, research and development, and provision for income taxes
Explanation / Answer
Balance Sheet
Apple Inc. CONSOLIDATED BALANCE SHEETS (In millions, except number of shares which are reflected in thousands and par value) September 24, September 26, particulars 2016 2015 ASSETS: Current assets: Cash and cash equivalents 20,484 20,289.00 Short-term marketable securities 46,671 53,892.00 Accounts receivable, less allowances of $58 and $53, respectively 15,754 17,874.00 Inventories 2,132 4,855.00 Vendor non-trade receivables 13,545 17,799.00 Other current assets 8,283 13,936.00 Total current assets 106,869 128,645 Long-term marketable securities 170,430 194,714.00 Property, plant and equipment, net 27,010 33,783.00 Goodwill 5,414 5,717.00 Acquired intangible assets, net 3,206 2,298.00 Other non-current assets 8,757 10,162.00 Total assets 321,686 375,319.00 LIABILITIES AND SHAREHOLDERS’ EQUITY: Current liabilities: Accounts payable 37,294 49,049.00 Accrued expenses 22,027 25,744.00 Deferred revenue 8,080 7,548.00 Commercial paper 8,105 11,977.00 Current portion of long-term debt 3,500 6,496.00 Total current liabilities 79,006 100,814.00 Deferred revenue, non-current 2,930 2,836.00 Long-term debt 75,427 97,207.00 Other non-current liabilities 36,074 40,415.00 Total liabilities 193,437 241,272.00 Commitments and contingencies - Shareholders’ equity: - Common stock and additional paid-in capital, $0.00001 par value: 12,600,000 shares authorized; 5,126,201 and 5,336,166 shares issued and outstanding, respectively 31,251 35,867.00 Retained earnings 96,364 98,330.00 Accumulated other comprehensive income/(loss) 634 - Total shareholders’ equity 128,249 134,047.00 Total liabilities and shareholders’ equity 321686 375,319.00