DuPont Analysis, Forecasting with the Parsimonious Method and Estimating
Share Value of Cisco Systems Inc Using the DCF ModelAll directions, format, Parsimonious Method for Forecasting NOPAT and NOA i linked in 4 files below, please review it carefully before you start. Answer 10 questions in the file “DuPont Analysis and Equity Valuation of Cisco Systems Inc” by word and fill the Valuation Sheet by excel.Notes: Your report must be submitted in Word in the following format,1. Cover page (project title, your name, email, and submission date).2. In the main text, first list the required question in bold, and then type in your answers, including models, tables, detailed explanations and discussions.3. All text in the report should be in 12-point Times Roman font and 1.5 spaced. Your project paper will be graded based on both the correctness of your answers and the writing of the report. For example, are you providing detailed explanations of the concept and model? Are you giving detailed discussions of the results?Project Report RubricsCriterionStrongAverageWeakIdentification of Main Issues/ProblemsIdentifies and demonstrates a sophisticated understanding of the main issues/problems in the case study.Identifies and demonstrates an accomplished understanding of most of the issues/problems.Identifies and demonstrates acceptable understanding of some of the issues/problems in the case study.Analysis and Evaluation of Issues/ProblemsPresents an insightful and thorough analysis of all identified issues/problems; includes all necessary calculations.Presents a thorough analysis of most of the issues identified; missing some necessary calculations.Presents a superficial or incompleteanalysis of some of the identifiedissues; omits necessary calculationsRecommendationson Effective Solutions/StrategiesSupports diagnosis and opinions with strong arguments and well documented evidence; presents a balanced and critical view; interpretation is both reasonable and objective.Supports diagnosis and opinions with limited reasoning and evidence; presents a somewhat one-sided argument; demonstrates little engagement with ideas presented.Little or no action suggested and/or inappropriate solutions proposed to the issues in the case study.Writing Mechanics andFormatting GuidelinesDemonstrates clarity, conciseness and correctness; formatting is appropriate and writing is free of grammar and spelling errors.Occasional grammar or spelling errors, but still a clear presentation of ideas; lacks organization.Writing is unfocused, rambling, or contains serious errors; poorly organized and does not follow specified guidelines.
appendix_12b_parsimonious_method.pdf
dupont_analysis_and_equity_valuation_of_cisco_systems_inc.pdf
project_report_format_and_grading_rubrics.pdf
valuation_sheet__1_.xlsx
Unformatted Attachment Preview
Appendix 12B: Parsimonious Method for Forecasting NOPAT and NOA
This appendix explains a parsimonious method to obtain forecast for net operating profit after tax
(NOPAT) and for net operating assets (NOA). This method requires three crucial inputs:
1. Sales growth.
2. Net operating profit margin (NOPM); Defined in Module 4 as NOPAT divide by sales.
3. Net operating asset turnover (NOAT); defined in Module 4 as sales divide by average
NOA. (For forecasting purposes, we define NOAT as sales divide by year-end NOA
instead of average NOA because we want to forecast year-end values.)
Multiyear Forecasting with Parsimonious Method
We use Procter & Gamble’s 2016 income statement from Exhibit 12.2, and its 2016 balance sheet
form Exhibit 12.3, to determine the following measures. We assume that P&G’s statutory tax rate
is 37% on nonoperating revenues and expenses.
$ millions
2016
Sales
$65,299
Net operating profit after tax ($13,441-[$3342+ ($579-$182-$325) ×
$10,072
37%])
NOA ($127,136-$7,102-$6,246-$7,185-$9,325-$7,449-$9,113$70,391
$10,325)*
NOPM ($10,072/$65,299)
15.4%
NOAT ($65,299/$70,391)
0.93
*we use ending balance sheet amounts, rather than average amounts, because we forecast ending
balance ending balance sheet amounts.
Each year’s forecasted sales is the prior year sales multiplied successively by (1+ growth rate) and
then rounded to whole digits. Consistent with our prior revenue growth rate assumption for P&G,
we define “1+ growth rate” as 1.01 for 2017 and 1.02 for 2018 onward. NOPAT is computed using
forecasted ( and rounded) sales each year times the 2016 NOPM of 15.4%; and NOA is computed
using forecasted (and rounded) sales divided by the 2016 NOAT of 0.93. Forecasted numbers for
2017 through 2020 are in Exhibit 12B.1; supporting computations are in parentheses.
This forecasting process can be continued for any desired forecast horizon. Also, the forecast
assumption such as sales growth, NOPM, and NOAT can be varied by year, if desired. This
parsimonious method is simpler than the method illustrated in this method. However, its simplicity
foregoes information than can improve forecast accuracy.
Exhibit 12B.1 P&G Parsimonious Method Forecasts of Sales, NOPAT and NOA
Reported
$ millions
2016
Net sales growth
Net sales (unrounded)
$65,299
Forecast
2017 Est.
2018 Est.
2019 Est.
2020 Est.
1.0%
2.0%
2.0%
2.0%
$65,951.99
$67,271.03
$68,616.45
$69,988.78
($65,299 × 1.01)
($65,951.99 × 1.02)
($67,271.03× 1.02)
($68,616.45 × 1.002)
Net sales (rounded)
$65,299
$65,952
$67,271
$68,616
$69,989
NOPAT¹
$10,072
$10,157
$10,360
$10,567
$10,778
($65,952 × 0.154)
($67,271 × 0.154)
($68,616 × 0.154)
($69,989 × 0.154)
$70,916
$72,334
$73,781
$75,257
($65,952/0.93)
($67,271/0.93)
($68,616/0.93)
($69,989/0.93)
NOA²
$70,391
¹ Forecasted NOPAT = Forecasted net sales (rounded) × 2016 NOPM
² Forecasted NOA = Forecasted net sales (rounded) ÷ 2016 NOAT
DuPont Analysis, Forecasting with the Parsimonious Method and Estimating
Share Value of Cisco Systems Inc Using the DCF Model
Cisco Systems, Inc. is a multinational information technology company headquartered in San
Jose, California, that produces and sells networking hardware, telecommunications equipment and
other high-technology services. Cisco Systems was founded in December 1984 by Leonard Bosack
and Sandy Lerner, two Stanford University computer scientists, who pioneered the concept of a
local area network (LAN). In 1990, Cisco Systems went public with a market capitalization of
$224 million. By 2000, Cisco had become the most valuable company in the world with a more
than $500 billion market capitalization. The stock was initially listed on the NASDAQ in 1990,
and then was added to the Dow Jones Industrial Average on June 8, 2009. Cisco is currently
included in the S&P 500 Index, the Russell 1000 Index, NASDAQ-100 Index and the Russell 1000
Growth Stock Index. 1
As Figure 1 shows, the stock price of Cisco Systems peaked in 1999-2000 before the burst of
internet bubble. However, its stock price has been fluctuating in the range of $10-$30 since 2001.
Figure 1. Stock Price of Cisco Systems Inc.
90
80
70
60
50
40
30
20
10
1
See https://en.wikipedia.org/wiki/Cisco_Systems
1
1/1/2015
1/1/2014
1/1/2013
1/1/2012
1/1/2011
1/1/2010
1/1/2009
1/1/2008
1/1/2007
1/1/2006
1/1/2005
1/1/2004
1/1/2003
1/1/2002
1/1/2001
1/1/2000
1/1/1999
1/1/1998
1/1/1997
1/1/1996
1/1/1995
1/1/1994
1/1/1993
1/1/1992
1/1/1991
0
Following are the income statement and balance sheet for Cisco Systems for the year ended
July 30, 2016.
Cisco Systems Inc.
Consolidated Statements of Income
Years Ended December ($ millions)
July 30, 2016
Revenue
Product
$37,254
Service
11,993
Total revenue
49,247
$37,750
11,411
49,161
Cost of sales
Product
Service
Total cost of sales
Gross margin
14,161
4,126
18,287
30,960
15,377
4,103
19,480
29,681
Operating expenses
Research and development
Sales and marketing
General and administrative
Amortization of purchased intangible assets
Restructuring and other charges
Total operating expenses
Operating income
6,296
9,619
1,814
303
268
18,300
12,660
6,207
9,821
2,040
359
484
18,911
10,770
Interest income
Interest expense
Other income (loss), net
Interest and other income (loss), net
1,005
(676)
(69)
260
769
(566)
228
431
Income before provision for income taxes
Provision for income taxes
12,920
2,181
11,201
2,220
Net income
$10,739
$8,981
2
July 25, 2015
Cisco Systems Inc.
Consolidated Balance Sheets
In millions, except par value
July 30, 2016
July 25, 2015
$7,631
58,125
$6,877
53,539
Assets
Current assets
Cash and cash equivalents
Investments
Accounts receivable, net of allowance for doubtful accounts of $249
at July 30, 2016 and $302 at July 25, 2015
Inventories
Financing receivables, net
Other current assets
Total current assets
5,847
5,344
1,217
4,272
1,627
78,719
1,627
4,491
1,490
73,368
Property and equipment, net
Financing receivables, net
Goodwill
Purchased intangible assets, net
Deferred tax assets
Other assets
Total assets
3,506
4,158
26,625
2,501
4,299
1,844
$121,652
3,332
3,858
24,469
2,376
4,454
1,516
$113,373
$4,160
1,056
517
2,951
10,155
6,072
$3,897
1,104
62
3,049
9,824
5,476
Total current liabilities
24,911
23,412
Long-term debt
Income taxes payable
Deferred revenue
Other long-term liabilities
Total liabilities
24,483
925
6,317
1,431
58,067
21,457
1,876
5,359
1,562
53,666
Liabilities
Current liabilities
Short-term debt
Accounts payable
Income taxes payable
Accrued compensation
Deferred revenue
Other current liabilities
3
Cisco Systems Inc.
Consolidated Balance Sheets
In millions, except par value
July 30, 2016
July 25, 2015
—
—
Cisco shareholders’ equity
Preferred stock, no par value: 5 shaes authorized; none issued and
outstanding
Common stock and additional paid-in capital, $0.001 par value:
20,000 shares authorized; 5,029 and 5,085 shares issued and
outstanding at July 30, 2016 and July 25, 2015, respectively
Retained earnings
Accumulated other comprehensive income (loss)
Total Cisco shareholders’ equity
Noncontrolling interests
44,516
43,592
19,396
(326)
63,586
(1)
16,045
61
59,698
9
Total equity
63,585
59,707
Total liabilities and equity
$121,652
$113,373
Required
(a) compute the return on assets (ROA) and return on equity (ROE) for 2016
(b) Complete the DuPont disaggregation of return on equity (ROE) for 2016. Analyze the
DuPont financial ratios and discuss how Cisco Systems Inc. can achieve a high ROE.
(c) Compute net operating assets (NOA) for 2016.
(d) Compute net operating profit after tax (NOPAT) for 2016, assuming a federal and state
statutory tax rate of 37%. (Round your answer to the nearest whole number.)
(e) Forecast Cisco’s sales, NOPAT, and NOA for years 2017 through 2020 and the terminal
period using the following assumptions:
Sales growth 2017
1%
Sales growth 2018-2020
2%
Terminal growth
1%
Net operating profit margin
2016 rate rounded to three decimal places
Net operating asset turnover
2016 rate rounded to three decimal places
4
Assume a discount rate (WACC) of 10%, common shares outstanding of 5,029 million, and net
nonoperating obligations (NNO) of $(37,113) million (NNO is negative which means that Cisco
has net nonoperating investments).
(f) Estimate the value of a share of Cisco common stock as of July 30, 2016 using the discounted
cash flow (DCF) model and sales, NOPAT and NOA forecast in (e);
(g) If Cisco’s top management were optimistic about CISCO’s market growth opportunities and
revised their sales growth rates up by 2%, please forecast Cisco’s sales, NOPAT, and NOA for
years 2017 through 2020 and the terminal period using the following assumptions:
Sales growth 2017
3%
Sales growth 2018-2020
4%
Terminal growth
1%
Net operating profit margin
2016 rate rounded to three decimal places
Net operating asset turnover
2016 rate rounded to three decimal places
(h) Estimate the value of a share of Cisco common stock as of July 30, 2016 using the
discounted cash flow (DCF) model and the sales forecast in (g); Note, we still assume a discount
rate (WACC) of 10%, common shares outstanding of 5,029 million, and net nonoperating
obligations (NNO) of $(37,113) million.
(i) Cisco stock closed at $31.47 on September 8, 2016, the date the Form 10-K was filed with the
SEC. How does your DCF valuation estimates compare with this closing price? What do you
believe are some reasons for the difference? What investment decision is suggested from your
results?
(j) Are there other equity valuation models? Please discuss the advantages and disadvantages of
different equity valuation models.
5
Project Report Format
Your report must be submitted in Word. It should have the following information:
1. Cover page (project title, your name, email, and submission date)
2. In the report, first list the required question in bold, and then type in your answers,
including models, tables, detailed explanations and discussions.
3. All text in the report should be in 12-point Times Roman font and 1.5 spaced.
I will grade your project report not only based on whether your solutions are correct, but also
based on the writing of the report. For example, are you providing detailed explanations of the
concept and model? Are you giving detailed discussions of the results?
A clean, well-written and organized paper is necessary to earn a high grade (See the report
grading rubrics on next page).
1
Accounting Project Report Rubrics
Criterion
Strong
Average
Weak
Identification of Main
Issues/Problems
Identifies and demonstrates a
sophisticated understanding of
the main issues/problems in the
case study.
Identifies and demonstrates an
accomplished understanding of
most of the issues/problems.
Identifies and demonstrates
acceptable understanding of
some of the issues/problems in
the case study.
Analysis and Evaluation
of Issues/Problems
Presents an insightful and
thorough analysis of all
identified issues/problems;
includes all necessary
calculations.
Presents a thorough analysis of
most of the issues identified;
missing some necessary
calculations.
Presents a superficial or
incomplete
analysis of some of the identified
issues; omits necessary
calculations
Recommendations
on Effective
Solutions/Strategies
Supports diagnosis and opinions
with strong arguments and well
documented evidence; presents a
balanced and critical view;
interpretation is both reasonable
and objective.
Supports diagnosis and opinions
with limited reasoning and
evidence; presents a somewhat
one-sided argument;
demonstrates little engagement
with ideas presented.
Little or no action suggested
and/or inappropriate solutions
proposed to the issues in the case
study.
Writing Mechanics and
Formatting Guidelines
Demonstrates clarity,
conciseness and correctness;
formatting is appropriate and
writing is free of grammar and
spelling errors.
Occasional grammar or spelling
errors, but still a clear
presentation of ideas; lacks
organization.
Writing is unfocused, rambling,
or contains serious errors; poorly
organized and does not follow
specified guidelines.
2
CSCO
($millions)
Reported
2016
Forcast Horizon
2017
Sales growth
Sales, unrounded
Sales, rounded
NOPAT1
NOA2
1 2016 NOPM is rounded to 3 decimals.
2 2016 NOAT is rounded to 3 decimals.
2018
2019
2020
Terminal
Period
CSCO
($millions)
DCF Model
Increase in NOA
FCFF (NOPAT – Increase in NOA)
Discount factor [1 / (1 + rw)t ]
Present value of horizon FCFF
Cum PV of horizon FCFF
Present value of terminal FCFF
Total firm value
Less (plus) NNO
Less (plus) NCI
Firm equity value
Shares outstanding (millions)
Stock price per share
Reported
2016
2017
Forecast Horizon
2018
2019
Horizon
2020
Terminal
Period
…
Purchase answer to see full
attachment
