.png)
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.
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
0.11(0.08%) 9:44AM EDT - Nasdaq Real Time Price
|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
------------------------------------------------------------------------------------------------------------------------------------------------------------
Are you SEARCHING for SOLUTION(S) of this assignment or similar to this?
Our professional writers are available 24/7 we offer:
+ Lowest price then other online writing services.
+ Zero% plagiarism at all.
+ Free Harvard Style Referencing.
+ Free amendments in your work for unlimited number of times.
+ Pay only after your order is accepted.
+ Secured payment methods (Skrill, Bank Transfer, Western Union).
+ Zero% plagiarism at all.
+ Free Harvard Style Referencing.
+ Free amendments in your work for unlimited number of times.
+ Pay only after your order is accepted.
+ Secured payment methods (Skrill, Bank Transfer, Western Union).
----------------------------------------------------------------------------------------------------------------------------------------------------------