Apple Inc

EXERCISE 10

Updated 5 Nov 2014

To START: Open sample10.xls. Save it to a local file as “exer10.xls”. Note that there are multiple sheet tabs at the bottom of the worksheet. Clicking on these tabs takes you to subsidiary worksheets where you will do most of your work. The output cells in the subsidiary worksheets are automatically read onto the MAIN sheet. Do not modify the MAIN sheet in any way other than pasting a chart (as in step 4 below).

1) Ratio Analysis: Open http://finance.yahoo.com. Find most recent financials (income statement and balance sheet dated 27 Sept 2014), and current market data (7 Nov 2014 or later) for Apple, Inc. (AAPL). Save copies of those web pages as local files (your files).

In your file "exer10.xls", click on the "RATIO" tab at the bottom of the sheet. Fill in cells B3 thru B10 with data from Yahoo. Create formulas in cells B13 thru B18 to yield the appropriate output. Use space at right of inputs and outputs to do any intermediate steps. Outputs that you calculate in the RATIO sheet will automatically be read into the MAIN sheet. Hint: Refer to exer1.doc and sample1.xls for review of ratio concepts.

2) Capital Budgeting: Given a cash flow and a discount rate, calculate NPV and IRR*. Use the "CAP BUD" worksheet to do the calculations for NPV and IRR*. Use the same discount rate for all years. That is, DO NOT increase the discount rate as with the “ascending discount rate” model. Use cells B4 and B5 (in CAP BUD tab) for your answers. And similar to step 1, cells in the MAIN tab have already been set to equal CAP BUD!B4 and B5.

3) Portfolio Management: Tab PORT includes some price per share data. For this exercise we will only use a few days worth of data. Calculate the market values for the three firms (pps X no of shares) to fill in tier 2. Calculate the market value of the portfolio by adding the market values of the three firms. Calculate the daily returns of the portfolio, the three firms, and the market (^GSPC) using the logic (day2-day1)/day1. Calculate the standard deviation (=STDEVP) of the daily returns for the portfolio, for the firms and for the market. Calculate the TOTAL return (from 30Jun06 to 10Jul06) for the portfolio, the firms, and the market.

4) Hyperbola: Continue working in the PORT tab to create a hyperbola using Clorox (CLX) and Nestlés (NSRGY.PK) stock. Their data can be found in columns M through V and rows 3 through 762. The data is laid out in the three tiers, similar to the previous problem 3 (above).

Note: Tiers 2 & 3 were pasted “values only”. You’ll need to replace the values with formulas in order to see updated values in the “portfolio” column.

Also note: The stock prices in col.O and col.P (for CLX and NSRGY.PK) were copied to Col.R and Col.S, and again to Col.U and Col.V, thus making three different portfolios. While all three portfolios have only two firms, the dollar weights of the firms within the portfolios will be different.

First, calculate the "normalized no of shares" (NNOS) for CLX and NSRGY.PK that would yield $10,000 worth of stock for each firm (=10000/Avg pps). There is a scratch box (G thru L in PORT tab) for you to work in. Then replace O3 & P3 with NNOS for CLX and ZERO shares for NSRGY.

In I6 & I7 write the formula to calculate the “STDEVP” of the daily returns (tier3) and “total K” of tier2 [(last day-first day) divided by first day] for this first portfolio.

Next, go to cell R3 & S3 and change the no of shares of CLX to one half of its NNOS and change the no of shares of NSRGY to one half of its NNOS. Put the “std devp” and “total K” for this second portfolio in J6 & J7.

Last, go to U3 and zero out the number of shares of CLX and put the NNOS for NSRGY.PK in V3. Put the resulting stddevp and total K in K6 &K7

“Insert" a "scatter" plot (X-axis =std dev, Y-axis =total K, with smooth lines) representing the hyperbola plotted from a set of portfolios going from 100% CLX to 100% NSRGY.PK (with a 50/50 mix about half way between.) Save the chart somewhere in tab PORT. Also, try to copy the chart into the MAIN tab in the upper right corner within rows 1-12 and columns G through N. Do not change the formatting of the MAIN sheet in exer10.xls – that will mess up my viewing of your other data.

5) Betas: Continue working in PORT tab, but return to the left side to calculate the betas for the portfolio, the three firms (YHOO, GE, MMM) and the market. A space for the answers is on the lower left and is linked to the MAIN tab. There is no chart required in this step.

6) Valuations: Open the VALUATION tab and make the calculations for 1) expected stock price; 2) Bond valuation; 3) Continuous Compounding; 4) Payments on a loan; and 5) the first few periods of a loan amortization schedule (do only three periods for this exam). Use the "inputs" as the "givens" for all your calculations.

7) Black-Scholes Option Pricing Model: Open the BSOPM tab. Use the inputs as shown. Create (or copy from previous exercises) the formulas to yield the intermediate variables and the final price of the call option. Your answers from tab BSOPM should copy into the MAIN tab automatically.

8) International Currency: 1) Open the tab CURRENCY to find the beginning of a cross rate table based on once-current data. Complete the table. Hint: The cells in the first row will be the reciprocals of the cells in the first column. 2) Calculate the forward premiums (or discounts) related to the UK pound on this out-of-date data.

Apple Inc. (AAPL)

-NasdaqGS

Watchlist

126.55

0.25(0.20%) 9:39AM EDT - Nasdaq Real Time Price

Add to Portfolio

Balance Sheet

Get Balance Sheet for:

27-Sep-1428-Sep-1329-Sep-12

Cash And Cash

Equivalents

13,844,000  

14,259,000 

 

10,746,000  

Short Term

Investments

11,233,000  

26,287,000 

 

18,383,000  

Net Receivables31,537,000  

24,094,000 

 

21,275,000  

Inventory2,111,000  1,764,000  791,000  

Other Current

Assets

9,806,000  6,882,000  6,458,000  

68,531,000  

73,286,000 

 

57,653,000  

130,162,000 

 

106,215,000

  

92,122,000  

20,624,000  

16,597,000 

 

15,452,000  

4,616,000  1,577,000  1,135,000  

4,142,000  4,179,000  4,224,000  

-  -  -  

3,764,000  5,146,000  5,478,000  

-  -  -  

231,839,000 

 

207,000,000

  

176,064,000 

 

Accounts Payable48,649,000  

36,223,000 

 

32,589,000  

Short/Current Long

Term Debt

6,308,000  -  -  

Other Current

Liabilities

8,491,000  7,435,000  5,953,000  

63,448,000  

43,658,000 

 

38,542,000  

28,987,000  

16,960,000 

 

-  

24,826,000  

20,208,000 

 

16,664,000  

3,031,000  2,625,000  2,648,000  

-  -  -  

-  -  -  

120,292,000 

 

83,451,000 

 

57,854,000  

-  -  -  

-  -  -  

-  -  -  

23,313,000  

19,764,000 

 

16,422,000  

87,152,000  

104,256,000

  

101,289,000 

 

-  -  -  

-  -  -  

1,082,000  -471,000499,000  

111,547,000 

 

123,549,000

  

118,210,000 

 

102,789,000 

 

117,793,000

  

112,851,000 

 

Capital Surplus

Other Stockholder Equity

Total Stockholder Equity

Net Tangible Assets

Misc Stocks Options Warrants

Redeemable Preferred Stock

Preferred Stock

Common Stock

Retained Earnings

Treasury Stock

Minority Interest

Negative Goodwill

Total Liabilities

Stockholders' Equity

Current Liabilities

Total Current Liabilities

Long Term Debt

Other Liabilities

Deferred Long Term Liability Charges

Other Assets

Deferred Long Term Asset Charges

Total Assets

Liabilities

Total Current Assets

Long Term Investments

Property Plant and Equipment

Goodwill

Intangible Assets

Accumulated Amortization

View: Annual Data | Quarterly Data

Period Ending

Assets

Current Assets

Current Data April 15, 2015

Apple Inc. (AAPL) –NasdaqGS DATE: 4/15/2015

Watchlist

126.20 Down 0.10(0.08%) 9:51AM EDT - Nasdaq Real Time Price

Add to Portfolio

Prev Close: 126.30

Open: 126.55

Bid: 126.30 x 200

Ask: 126.32 x 200

1y Target Est: 139.85

Beta: 0.93

Next Earnings Date: 27-Apr-15AAPL Earnings announcement

Day's Range: 126.11 - 126.82

52wk Range: 73.45 - 133.60

Volume: 3,190,257

Avg Vol (3m): 55,650,600

Market Cap: 735.08B

P/E (ttm): 17.10

EPS (ttm): 7.39

Div& Yield: 1.88 (1.50%)

People viewing AAPL also viewed:

Income Statement

Watchlist

126.19 0.11(0.08%) 9:44AM EDT - Nasdaq Real Time Price

Add to Portfolio

Income Statement

Get Income Statement for:

View: Annual Data | Quarterly Data

All numbers in thousands

Period Ending Sep 27, 2014 Sep 28, 2013 Sep 29, 2012 Total Revenue 182,795,000   170,910,000   156,508,000   Cost of Revenue 112,258,000   106,606,000   87,846,000   Gross Profit 70,537,000   64,304,000   68,662,000   Operating Expenses Research Development 6,041,000   4,475,000   3,381,000   Selling General and Administrative 11,993,000   10,830,000   10,040,000   Non Recurring -   -   -   Others -   -   -   Total Operating Expenses -   -   -   Operating Income or Loss 52,503,000   48,999,000   55,241,000   Income from Continuing Operations Total Other Income/Expenses Net 980,000   1,156,000   522,000   Earnings Before Interest And Taxes 53,483,000   50,155,000   55,763,000   Interest Expense -   -   -   Income Before Tax 53,483,000   50,155,000   55,763,000   Income Tax Expense 13,973,000   13,118,000   14,030,000   Minority Interest -   -   -   Net Income From Continuing Ops 39,510,000   37,037,000   41,733,000   Non-recurring Events Discontinued Operations -   -   -   Extraordinary Items -   -   -   Effect Of Accounting Changes -   -   -   Other Items -   -   -   Net Income 39,510,000   37,037,000   41,733,000   Preferred Stock And Other Adjustments -   -   -   Net Income Applicable To Common Shares 39,510,000   37,037,000   41,733,000