[SOLVED] CS Excel finance Netscape: Sample Final Exam

Netscape: Sample Final Exam
Financial Statement Analysis ACC 411
Professor Charles E. Wasley Simon School
University of Rochester
Professor Charles E. Wasley 1

Instructions (Read Carefully)
1) This exam is closed book and closed notes.
You may use one SINGLE-sided 812 x 11 cheat-sheet, but your
cheat-sheet must be handwritten.
Nothing can be taped, printed or pasted onto it like output from Excel or Word.
You may NOT use your original copy of the Netscape case.
A clean and unmarked copy of the case is provided with the exam.
2) Answer all questions in your white, blue or yellow book, clearly label your response to each question, use a pencil and you may use a calculator.
You have 3 hours and 15 minutes to complete the exam.
Professor Charles E. Wasley 2

Instructions (Read Carefully)
3) Answer the questions using the information provided in the exam.
Always be sure to clearly state any assumptions and show all of your work to receive potential partial credit.
4) The exam is worth a total of 150 points and point allocations for each question are shown on the exam.
5) All 10 questions are required.
Professor Charles E. Wasley 3

Instructions (Read Carefully)
6) You may answer the questions in any order you wish, just be sure to clearly label each answer in your white, blue or yellow book.
7) Check right now to ensure you have all 31 pages of the exam.
I recommend you disassemble (i.e., tear apart) the exam. When you are finished, all you need to hand in is your blue book and your CHEAT-SHEET.
Your exam will not be graded unless your CHEAT-SHEET is handed in.
Professor Charles E. Wasley 4

Instructions (Read Carefully)
8) Rather than providing suggested time allocations per question, I offer the following (obvious) advice.
Questions worth more points require more time.
Be careful not to get bogged down on any one question and try to recognize when you have hit diminishing returns to your (time) on a given question.
Professor Charles E. Wasley 5

Instructions (Read Carefully)
9) ON ALL QUESTIONS, THINK MORE AND WRITE LESS.
Use the first 15 minutes of the exam time to review the questions and to decide the order you want to answer them in. This will help you organize your time.
Develop a strategy before you start answering questions!!
Professor Charles E. Wasley 6

Question 1
1) Based on a proposed valuation of over $1,000,000,000 ($28 IPO price x 37,764,000 shares outstanding post-IPO), Netscape appears to be quite valuable in the eyes of investors.
Assume you have been asked to answer the following question in one sentence, and your response will be used in your investment firms initial equity analyst report on Netscape.
At the time of the case, why do you think investors are so keen on Netscape, a firm with less than two years of an operating history. (5 points)
Professor Charles E. Wasley 7

Question 2
2) Your firms investment report on Netscape needs to succinctly describe the firms strategy?
How would you concisely describe it in one or two sentences? (5 points)
Professor Charles E. Wasley 8

Question 3
3) From an operating perspective, what are the TWO most important financial statement consequences of the firms strategy?
This will also go into your firms report on Netscape. (8 points)
Professor Charles E. Wasley 9

Question 4
4) The report also needs to succinctly describe the risks Netscape faces?
How would you describe the TWO primary risks, (again in one or two sentences)? (8 points)
Professor Charles E. Wasley 10

Question 5
5) Its clear from the case Netscape needs to raise capital.
To do so Netscape could conceivably rely on: 1) Angel Investors like Jim Clark, 2) Venture Capital Funds like Kleiner, Perkins, Caufield & Byers, 3) Bank Loans, 4) Long-Term Debt, 5) Strategic Alliances or 6) Equity (i.e., the IPO).
Briefly, why do you think Netscape ruled out the other options and decided on the IPO? (7 points)
Professor Charles E. Wasley 11

Questions 6, 7, 8 and 9 (The Setting)
As noted earlier, the answers to prior questions are going into your firms first equity analyst report on Netscape.
The report also needs to contain some valuation analysis related to the proposed $28 IPO price.
Professor Charles E. Wasley 12

Questions 6, 7, 8 and 9 (The Setting)
Before being terminated for not knowing enough about economics, finance and accounting, a Northwestern MBA graduate (who thought beta was a fraternity) had begun the firms valuation analysis of Netscapes IPO.
Upon his termination, the individual took with him all the valuation analysis he performed, except for a couple of pages of Excel output and some notes.
Professor Charles E. Wasley 13

Questions 6, 7, 8 and 9 (The Setting)
The materials left behind include
An incomplete DCF valuation with some numbers missing (e.g., the estimated stock price) and without the underlying assumptions fully articulated.
An incomplete residual income valuation of Netscape, also with some numbers missing.
It seems the two valuations are based on entirely different assumptions, thus its not obvious the resulting valuations will be the same.
An incomplete Abnormal ROE valuation of Netscape, also with some numbers missing.
Professor Charles E. Wasley 14

Questions 6, 7, 8 and 9 (The Setting)
The materials left behind include
A note related to his residual income valuation.
The note expressed concern about the GAAP requirement that Netscapes R&D be expensed immediately.
The note went on to read he was concerned the required expensing of R&D was understating Netscapes book value of equity (and overstating future net income was well) because Netscapes R&D would benefit the future and should have been capitalized.
Professor Charles E. Wasley 15

Questions 6, 7, 8 and 9 (The Setting)
The managing partner of your investment firm is more than a little concerned the firms valuation of Netscape is behind schedule, but hes thrilled to find out the firm has hired you.
Since you are a Simon School graduate he knows you are likely to have taken Professor Wasleys FSA course (and a number of fine Finance courses from other Simon School faculty).
As a result, hes certain your training will enable you to unravel the valuation the departing Northwestern MBA left in total disarray.
The specific issues he wants you to address are
enumerated in questions 6, 7, 8 and 9.
Professor Charles E. Wasley 16

Question 6
6) The slide on the following page contains the (partial) DCF valuation left behind by the departing analyst. The analyst used a 10 year forecasting horizon. (40 points)
Your task is to:
a) Fill in the missing amounts (i.e., fill in the blanks).
b) Back out the underlying assumptions the valuation is based on.
c) Evaluate the overall sensibility of the valuation (2006 is the terminal year).
Professor Charles E. Wasley 17

DCF VALAUTION OF NETSCAPE ACTUAL 1995
FORECASTS=====================================================================>
REVENUES $16,625 COST OF GOODS $1,736 GROSS PROFIT $14,889 R&D $6,115 OPERATINGEXPENSES $13,449 OPERATINGINCOME -$4,675 TAXES $0
1996 $25,785
1997 $40,001
1998 $62,053
1999 $96,263 $10,011 $86,252 $35,425 $39,372 $11,455
2000 $149,333
2001 $231,661
2002 $359,375
2003 $557,499
2004 $864,848
2005 $1,341,639
NET INCOME
CUMULATIVE TAX LOSS CARRYFORWARD
-$4,675 -$4,675 $7,618
$0 -$3,240
$1,179 $0 $1,179
$0 $11,455
$75,109 $114,641 $38,978 $75,663
$60,466 $117,376
$93,801 $182,085
CAPITAL EXPENDITURES
DEPRECIATION $918
$10,005 $1,418
$12,720 $2,200
$15,389 $3,413
$17,135 $5,294
$16,128 $8,213
$25,019
$38,813
$60,210
$93,404
$144,897 $73,790
FREECASHFLOW
IGNORE
IGNORE
IGNORE
IGNORE
IGNORE
IGNORE
$12,741 IGNORE
$19,766 IGNORE
$30,662 IGNORE
$47,567 IGNORE
?
ASSUMPTIONS: RISKLESS RATE: DISCOUNT RATE GROWTHRATE TERMINAL GROWTHRATE DEBT RETI RED OR I SSUED
6.71% 12.00%
PRESENT VALUE OF FREE CASH FLOWS 1996-2005 PRESENT VALUE OF TERMINAL VALUE
TOTAL PRESENT VALUE
$180,088 ?
CURRENT SHARES OUTSTANDING NEWSHARES
TOTAL SHARES VALUEPERSHARE
32,764 5,000 37,764 ?
Professor Charles E. Wasley
18
? 4.00%
?
$0. 00
$2,682 $23,104 $9,489 $18,282 -$4,667 $0 -$4,667
$4,160 $35,841 $14,720 $24,361 -$3,240
$6,454 $55,600 $22,836 $31,585
$15,531 $133,803 $54,955 $46,144 $32,704 $5 $32,699
$24,093 $207,568 $85,251 $48,417 $73,900 $25,126 $48,774
$37,375 $322,000 $132,250
$57,980 $499,519 $205,160 $116,517 $177,842
$89,944 $774,904 $318,264 $180,753 $275,887
$139,530 $1,202,108 $493,723 $280,402 $427,983 $145,514 $282,469
-$9,347
-$12,594
-$11,426
$0
$0
$0
$0
$0
$0
$0

Question 7
7) The slides on the following 3 pages contain the (partial) Residual Income valuation left behind by the departing analyst. The analyst used a 20 year forecasting horizon and assumed a 4% terminal growth rate in residual income.
Your task is to fill in the missing amounts. (22 points)
Professor Charles E. Wasley 19

Earnings-BasedValuation
($000)
CompanyName
Most Recent Fiscal Year End Dateof Valuation
Cost of Common Equity
Fiscal Year of Forecast ValuationtoCommonEquity
Net Income
Common Equity at Beginning of Year Residual Income
Present Valueof Residual Income
NETSCAPE
Present ValueBeyond20Years Present Valueof First 20Years
? 788,624,632
ForecastedValueasof ValuationDate
?
37,764,000
CommonSharesOutstandingat BSDate Forecast Price/Share
?
6/30/1995 6/30/1995 12.00%
6/30/1996 6/30/1997 6/30/1998 6/30/1999
6/30/2000
6/30/2001
6/30/2002
(5,051,675) (3,760,311) 114,930 8,794,133 16,473,620 12,892,414 17,996,521 24,811,204 (7,028,510) (5,307,400) (2,044,653) 5,816,788 (6,275,455) (4,231,027) (1,455,343) 3,696,674
30,817,048 33,671,905 26,776,420 15,193,660
47,172,764 44,764,393 41,801,037 21,177,706
73,700,740 57,857,978 66,757,783 30,197,831
Professor Charles E. Wasley
20

Earnings-BasedValuation
CompanyName MostRecentFiscalYearEnd DateofValuation CostofCommonEquity FiscalYearofForecast ValuationtoCommonEquity NetIncome CommonEquityatBeginningofYear ResidualIncome PresentValueofResidualIncome
6/30/2003 6/30/2004 6/30/2005 6/30/2006 6/30/2007 6/30/2008 6/30/2009
115,140,849 179,088,702 277,766,577 288,076,364 299,599,419 311,583,396 324,046,732 71,790,179 83,509,925 129,523,894 200,891,560 276,367,966 287,422,685 298,919,592 106,526,027 169,067,511 262,223,710 263,969,377 266,435,263 277,092,674 288,176,381
43,024,076 60,967,440 84,429,017 75,884,888 68,387,296 63,502,489 58,966,597
Professor Charles E. Wasley 21

Earnings-BasedValuation
CompanyName
Most Recent Fi scal Year End DateofValuation CostofCommonEquity
Fiscal Year of Forecast ValuationtoCommonEquity NetIncome CommonEquityatBeginningofYear Residual Income PresentValueofResidualIncome
TERMINAL YEAR
6/30/2010
6/30/2011
6/30/2012
6/30/2013
6/30/2014
6/30/2015
6/30/2016
6/30/2017
337,008,601 350,488,945 364,508,503 379,088,843 394,252,397 410,022,492 426,423,392 443,480,328 310,876,376 323,311,431 336,243,888 349,693,644 363,681,389 378,228,645 393,357,791 409,092,102 299,703,436 311,691,573 324,159,236 337,125,606 350,610,630 364,635,055 379,220,457 394,389,275
54,754,697 50,843,647 47,211,958 43,839,676 40,708,270 37,800,537 35,100,498 32,593,320
Professor Charles E. Wasley 22

Question 8
8) The slides on the following 3 pages contain the (partial) Abnormal ROE valuation left behind by the departing analyst. The analyst used a 20 year forecasting horizon and assumed a 4% terminal growth rate in residual income.
Your task is to fill in the missing amounts (i.e., fill in the blanks), comment on the underlying long-run performance implied the valuation, and evaluate the
overall sensibility of the valuation. (28 points)
Professor Charles E. Wasley 23

ABNORMALROEVALUATION
($000)
CompanyName
Most Recent Fiscal Year End
Date of Valuation
Cost of Common Equity
Fiscal Year of Forecast
Net Income
BookValueof StockholdersEquity, BOY Returnonbeginequity=ROE=Net Income/ BVof SE, BOY Cost of equity= RE
NETSCAPE
Abnormal ROE = ROE RE
Cumgrowth in BV of SE CumgrowthinBVof SExAbnormal ROE
0.1200 -0.1136
Discount rate=1/(1+RE)t DiscountedcumgrowthinBVof SExAbnormal ROE Cumulative PV of ROE
0.711780 -0.088344 -0.685305
IndicatedPrice-to-BookRatio BookValueof Equityat TimeZero IndicatedMarket Value
Number of Shares Outstanding Indicated Market Price Per Share
? ? ?
?
6/30/1995 6/30/1995 12.00%
6/30/1996 (5,051,675)
6/30/1997 (3,760,311)
6/30/1998 114,930 17,996,521
6/30/2002 8,794,133 30,817,048 47,172,764 73,700,740
16,473,620 -0.3067 0.1200 -0.4267 1.0000 -0.4267 0.892857 -0.380940 -0.340125
12,892,414 -0.2917 0.1200 -0.4117 0.782610 -0.3222 0.797194 -0.256837 -0.596961
0.0064
24,811,204 0.3544 0.1200 0.2344 1.506117 0.3531 0.635518 0.224400 -0.460905
33,671,905 0.9152 0.1200 0.7952 2.043989 1.6254 0.567427 0.922302 0.461397
44,764,393 1.0538 0.1200 0.9338 2.717338 2.5375 0.506631 1.285553 1.746950
57,857,978 1.2738 0.1200 1.1538 3.512159 4.0524 0.452349 1.833102 3.580052
37,764,000
Professor Charles E. Wasley
24
1.092445 -0.1241
6/30/1999
6/30/2000
6/30/2001

ABNORMALROEVALUATION
CompanyName
MostRecentFiscalYearEnd
DateofValuation
CostofCommonEquity
FiscalYearofForecast
NetIncome
BookValueofStockholdersEquity,BOY Returnonbeginequity=ROE=NetIncome/BVofSE,BOY 1.6039 Costofequity=RE 0.1200
AbnormalROE=ROE-RE 1.4839
2.1445 0.1200 2.0245
2.1445 0.1200 2.0245
1.4340 0.1200 1.3140
1.0841 0.1200 0.9641
1.0841 0.1200 0.9641
1.0841 0.1200 0.9641
1.0841 0.1200 0.9641
CumgrowthinBVofSE
Cumgrowth in BV of SEx Abnormal ROE
4.357887 6.4665
5.069312 10.2629
7.862503 15.9178
12.194743 16.0238
16.776396 16.1734
17.447451 16.8204
18.145349 17.4932
18.871163 18.1929
Discountrate=1/(1+RE)t DiscountedcumgrowthinBVofSExAbnormalROE CumulativePVofROE
0.403883
0.360610
0.321973
0.287476
0.256675
0.229174
0.204620
0.182696
6/30/2003 6/30/2004 6/30/2005 6/30/2006 6/30/2007 6/30/2008 6/30/2009 6/30/2010 115,140,849 179,088,702 277,766,577 288,076,364 299,599,419 311,583,396 324,046,732 337,008,601 71,790,179 83,509,925 129,523,894 200,891,560 276,367,966 287,422,685 298,919,592 310,876,376
2.611695
3.700913
5.125104 15.017765
4.606449 19.624213
4.151322 23.775535
3.854799 27.630334
3.579456 31.209790
3.323781
6.191747
9.892661
34.533570
Professor Charles E. Wasley
25

ABNORMALROEVALUATION
CompanyName
MostRecentFiscalYearEnd
DateofValuation
CostofCommonEquity
FiscalYearofForecast
NetIncome
BookValueofStockholdersEquity,BOY Returnonbeginequity=ROE=NetIncome/BVofSE,BOY 1.0841 Costofequity=RE 0.1200
TERMINAL YEAR
AbnormalROE=ROE-RE 0.9641
1.0841 0.1200 0.9641
1.0841 0.1200 0.9641
1.0841 0.1200 0.9641
1.0841 0.1200 0.9641
1.0841 0.1200 0.9641
1.0841 0.1200 0.9641
1.0841 0.1200 0.9641
CumgrowthinBVofSE
Cumgrowth in BV of SEx Abnormal ROE
19.626010 18.9206
20.411050 19.6775
21.227492 20.4646
22.076592 21.2832
22.959656 22.1345
23.878042 23.0199
24.833164 23.9407
25.826490 24.8983
Discountrate=1/(1+RE)t DiscountedcumgrowthinBVofSExAbnormalROE CumulativePVofROE
0.163122
0.145644
0.130040
0.116107
0.103667
0.092560
0.082643
0.073788
6/30/2011 6/30/2012 6/30/2013 6/30/2014 6/30/2015 6/30/2016 6/30/2017 6/30/2018 350,488,945 364,508,503 379,088,843 394,252,397 410,022,492 426,423,392 443,480,328 461,219,541 323,311,431 336,243,888 349,693,644 363,681,389 378,228,645 393,357,791 409,092,102 425,455,786
3.086368 37.619938
2.865913 40.485851
2.661205
2.471119 45.618174
2.294610 47.912784
2.130709 50.043494
1.978516 52.022010
1.837193
Professor Charles E. Wasley
26
43.147055
53.859203

Question 9
9) As noted above, the departing analyst left a note expressing some concern about the GAAP requirement that Netscapes R&D be expensed.
As stated earlier, the note expressed concern the required expensing of R&D was understating Netscapes book value of equity (and overstating future net income was well because Netscapes R&D would benefit future operations). According to the note, he felt the R&D should have been capitalized for analysis purposes. More importantly, he was sure there was something wrong with the residual income model because the R&D was expensed instead of capitalized.
The (partial) contents of the note appear on the next two slides.
Professor Charles E. Wasley 27

Question 9
The note contained the standard expression for the Residual Income Model:
P0=BV0 +[(NI1 rBV0)/(1+r)]+[(NI2 rBV1)/ (1+r)2]+[(NI3 rBV2)/(1+r)3]++[(NIT rBVT-1) / (1 + r)T]
The note then stated the analyst thought $C of R&D should have been capitalized into BV0, and just to keep the analysis simple, the same $C of R&D
would all be written in year 1.
Professor Charles E. Wasley 28

Question 9
As a result, the analyst had rewritten the Residual Income Model as:
P0=(BV0 +C)+[(NI1 Cr(BV0+C) /(1+r)] +[(NI2 rBV1)/(1+r)2]+[(NI3 rBV2)/(1+ r)3]++[(NIT rBVT-1)/(1+r)T]
The addition of $C in the revised equation reflected the analysts capitalization of the R&D into BV at time 0 (rather than expensing it then) and then writing it all off in period 1.
Professor Charles E. Wasley 29

Question 9
Having seen the two different expressions for the residual income model, the firms managing partner is concerned and wondering whether GAAP accounting for things like R&D messes up residual income valuations.
He wants you to prove why the prior analyst is right and why there is something wrong with the residual income model, or prove why the prior analyst is wrong and why the residual income model is still a valid tool
for valuation. (20 points)
Professor Charles E. Wasley 30

Question 10
10) As you are finishing your analysis of questions 6-9, one of your firms old-timers steps into your office.
Seeing your DCF valuation he makes the following statement,
You cant really be relying on that DCF valuation too heavily can you? There is just too much uncertainty about Netscapes future to base a valuation on DCF.
In a sentence or two, how would you respond. Would you agree or disagree, and precisely why? (7 points)
Professor Charles E. Wasley 31