Forecasting with the Parsimonious Method and Estimating Share Value Using the DCF Model

Following are income statements and balance sheets for Cisco Systems.

Note: Complete the entire question in Excel and format each answer to two decimal places.

Cisco Systems

Consolidated Statements of Income

Years Ended December $($$ millions$)$ July $27,2019$ July $28,2018$

Revenue

Product $$49,146$ $$46,253$

Service $16,25315,902$

Total revenue $65,39962,155$

Cost of sales

Product $18,72718,178$

Service $5,5135,414$

Total cost of sales $24,24023,592$

Gross margin $41,15938,563$

Operating expenses

Research and development $8,2877,978$

Sales and marketing $12,05911,645$

General and administrative $2,3022,701$

Amortization of purchased intangible assets $189278$

Restructuring and other charges $406451$

Total operating expenses $23,24323,053$

Operating income $17,91615,510$

Interest income $1,6481,900$

Interest expense $(1,082)(1,188)$

Other income $($loss$),$ net $(122)208$

Interest and other income $($loss$),$ net $444920$

Income before provision for income taxes $18,36016,430$

Provision for income taxes $3,71716,291$

Net income $$14,643$ $$139$

Cisco Systems Inc.

Consolidated Balance Sheets

In millions, except par value July $27,2019$ July $28,2018$

Assets

Current assets

Cash and cash equivalents $$14,805$ $$11,257$

Investments $27,29547,394$

Accounts receivable, net of allowance for doubtful accounts $6,9196,998$

Inventories $1,7432,326$

Financing receivables, net $6,4206,236$

Other current assets $2,9903,704$

Total current assets $60,17277,915$

Property and equipment, net $3,5143,788$

Financing receivables, net $6,2476,151$

Goodwill $42,24739,950$

Purchased intangible assets, net $2,7733,216$

Deferred tax assets $5,1224,056$

Other assets $3,1451,993$

Total assets $$123,220$ $$137,069$

Liabilities and equity

Current liabilities

Short$-$term debt $$12,841$ $$6,600$

Accounts payable $2,5942,399$

Income taxes payable $1,4481,265$

Accrued compensation $4,0583,762$

Deferred revenue $13,44214,477$

Other current liabilities $5,5745,560$

Total current liabilities $39,95734,063$

Long$-$term debt $18,23925,617$

Income taxes payable $11,24810,817$

Deferred revenue $9,82710,326$

Other long$-$term liabilities $1,6491,807$

Total liabilities $80,92082,630$

Equity:

Cisco shareholders$$ equity

Preferred stock, no par value: $5$ shares authorized; none issued and outstanding $00$

Common stock and additional paid$-$in capital, $$0\mathrm{.}001$ par value: $20,000$ shares authorized;

$4,250$ and $4,614$ shares issued and outstanding at July $27,2019,$ and July $28,2018,$ respectively $50,73653,955$

$($Accumulated deficit$)$ Retained earnings $(7,438)1,554$

Accumulated other comprehensive income $($loss$)(998)(1,070)$

Total Cisco shareholders' equity $42,30054,439$

Total equity $42,30054,439$

Total liabilities and equity $$123,220$ $$137,069$

Feceral and state statutory tax rate $22\%$

$($a$)$ Compute net operating assets $($NOA$)$ for $2019.$

Hint: Treat Financing receivable as operating assets.

NOA

Answer

$($b$)$ Compute net operating profit after tax $($NOPAT$)$ for $2019,$ assuming a federal and state statutory tax rate of $22\%.$ Assume that all items on the $2019$ income statement will persist.

NOPAT

Answer

$($c$)$ Use the parsimonious forecast method, as shown in Analysis Insight box on page $13-4,$ to forecast Cisco$$s sales, NOPAT, and NOA for $2020$ through $2023$ and the terminal period using the following assumptions.

Note: When completing the question in Excel, refer directly to the cells containing calculated assumptions for NOPM and NOAT, i$.$e$.,$ don't type the NOPM number when making a calculation, refer to the cell.

Assumptions

Sales growth $202020235\%$

Terminal growth $1\%$

Net operating profit margin $($NOPM$)2019$ rate

Net operating asset turnover $($NOAT$)2019$ rate

Hint: Use $2019$ NOA, not average NOA, to compute the $2019$ rate for NOAT.

CSCO Reported Forecast Horizon Terminal

$($$ millions$)20192020$ Est. $2021$ Est. $2022$ Est. $2023$ Est. Period

Sales Answer

Answer

$0$

Answer

$0$

Answer

$0$

Answer

$0$

Answer

$0$

NOPAT $=$ Forecasted sales x NOPM assumption Answer

$0$

Answer

$0$

Answer

$0$Answer

$0$

Answer

$0$

Answer

$0$

NOA $=$ Forecasted sales $/$ NOAT assumption Answer

$0$

Answer

$0$

Answer

$0$

Answer

$0$

Answer

$0$

Answer

$0$

$($d$)$ Estimate the value of a share of Cisco common stock using the discounted cash flow $($DCF$)$ model as of July $27,2019$ using the following assumptions

Assumptions

Discount rate $($WACC$)7\mathrm{.}60\%$

Common shares outstanding $5,029\mathrm{.}00$ million

Net nonoperating obligations $($NNO$)$ $$(8,747)$ million

NNO is negative, which means that Cisco has net nonoperating investments

CSCO Reported Forecast Horizon Terminal

$($$ millions$)20192020$ Est. $2021$ Est. $2022$ Est. $2023$ Est. Period

DCF Model

Increase in NOA Answer

$0$

Answer

$0$

Answer

$0$

Answer

$0$

Answer

$0$

FCFF $($NOPAT $-$ Increase in NOA$)$ Answer

$0$

Answer

$0$

Answer

$0$

Answer

$0$

Answer

$0$

Present value of horizon FCFF Answer

$0$

Answer

$0$

Answer

$0$

Answer

$0$

Cum. present value of horizon FCFF

$0$

Present value of terminal FCF

Total firm value

$0$

NNO Answer

$0$

Firm equity value Answer

$0$

Shares outstanding $($millions$)$

$0$

Stock price per share

$0$