Discussion ; One page

profileWitts
Lecture6practice-PepsiCoForecastSOLUTION.xlsx
Home>Business & Finance homework help>Discussion ; One page

Sheet3

Actuals Forecasts
Year 2006 2007 2008 Year +1 Year +2 Year +3 Year +4 Year +5
INCOME STATEMENT
Revenues 35,137 39,474 43,251 47,191 51,562 57,502 61,820 67,839
common size 100.0% 100.0% 100.0% 9.1% 9.3% 11.5% 7.5% 9.7%
rate of change 12.3% 9.6% See Forecast Development worksheet for details of revenues forecasts.
<Cost of Goods Sold> -15,762 -18,038 -20,351 -22,321 -24,492 -27,429 -29,612 -32,563
common size -44.9% -45.7% -47.1% -47.3% -47.5% -47.7% -47.9% -48.0%
rate of change 14.4% 12.8% Assume slowly increasing cost of goods sold as a percent of sales.
Gross Profit 19,375 21,436 22,900 24,870 27,070 30,074 32,208 35,277
common size 55.1% 54.3% 52.9% 52.7% 52.5% 52.3% 52.1% 52.0%
rate of change 10.6% 6.8% 8.6% 8.8% 11.1% 7.1% 9.5%
<Selling, General and Administrative Expenses> -12,711 -14,208 -15,901 -16,517 -18,047 -20,126 -21,637 -23,744
common size -36.2% -36.0% -36.8% -35.0% -35.0% -35.0% -35.0% -35.0%
rate of change 11.8% 11.9% Assume steady SG&A expense as a percent of sales.
<Research and Development Expenses> 0 0 0 0 0 0 0 0
common size 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change Explain assumptions.
<Amortization of Intangible Assets> -162 -58 -64 -64 -63 -62 -60 -56
common size -0.5% -0.1% -0.1% -64.0 -63.0 -62.0 -60.0 -56.0
rate of change -64.2% 10.3% Amounts based on PepsiCo disclosures in Note 4.
<Other Operating Expenses (1)>
common size
rate of change
<Other Operating Expenses (2)>
common size
rate of change
Other Operating Income (1)
common size
rate of change
Other Operating Income (2)
common size
rate of change
Non-Recurring Operating Gains
common size
rate of change
<Non-Recurring Operating Losses>
common size
rate of change
Operating Profit 6,502 7,170 6,935 8,289 8,960 9,886 10,511 11,477
common size 18.5% 18.2% 16.0% 17.6% 17.4% 17.2% 17.0% 16.9%
rate of change 10.3% -3.3% 19.5% 8.1% 10.3% 6.3% 9.2%
Interest Income 173 125 41 73 81 90 98 107
common size 0.5% 0.3% 0.1% 3.0% 3.0% 3.0% 3.0% 3.0%
rate of change -27.7% -67.2% Interest rate earned on average balance in cash and marketable securities.
<Interest Expense> -239 -224 -329 -493 -531 -579 -626 -677
common size -0.7% -0.6% -0.8% -5.8% -5.8% -5.8% -5.8% -5.8%
rate of change -6.3% 46.9% Interest rate paid on average balance in financial liabilities.
Income <Loss> from Equity Affiliates 553 560 374 480 509 539 572 606
common size 1.6% 1.4% 0.9% 12.0% 12.0% 12.0% 12.0% 12.0%
rate of change 1.3% -33.2% Assume expected return of 12% on investments in noncontrolled affilates.
Other Income or Gains
common size
rate of change
<Other Expenses or Losses>
common size
rate of change
Income before Tax 6,989 7,631 7,021 8,348 9,019 9,935 10,555 11,513
common size 19.9% 19.3% 16.2% 17.7% 17.5% 17.3% 17.1% 17.0%
rate of change 9.2% -8.0% 18.9% 8.0% 10.2% 6.2% 9.1%
<Income Tax Expense> -1,347 -1,973 -1,879 -2,237 -2,417 -2,663 -2,829 -3,085
common size -3.8% -5.0% -4.3% -26.8% -26.8% -26.8% -26.8% -26.8%
rate of change 46.5% -4.8% Effective income tax rate assumptions.
<Minority Interest in Earnings>
common size
rate of change
Income <Loss> from Discontinued Operations
common size
rate of change
Extraordinary Gains <Losses>
common size
rate of change
Changes in Acctg. Principles
common size
rate of change
Net Income (computed) 5,642 5,658 5,142 6,111 6,602 7,273 7,726 8,427
common size 16.1% 14.3% 11.9% 12.9% 12.8% 12.6% 12.5% 12.4%
rate of change 0.3% -9.1% 18.8% 8.0% 10.2% 6.2% 9.1%
Other Comprehensive Income Items 456 1,294 -3,793 0 0 0 0 0
common size 1.3% 3.3% -8.8% 0.0 0.0 0.0 0.0 0.0
rate of change 183.8% -393.1% Assume random walk.
Comprehensive Income 6,098 6,952 1,349 6,111 6,602 7,273 7,726 8,427
common size 17.4% 17.6% 3.1% 12.9% 12.8% 12.6% 12.5% 12.4%
rate of change 14.0% -80.6% 353.0% 8.0% 10.2% 6.2% 9.1%
FSAP OUTPUT: FINANCIAL STATEMENT FORECASTS
Analyst Name: Wahlen, Baginski & Bradshaw
Company Name: PepsiCo
Row Format: Row Format:
Actual Amounts Forecast Amounts
Common Size Percent Forecast assumption Long-Run Growth Rate:
Rate of Change Percent Forecast assumption explanation Long-Run Growth Factor:
Actuals Forecasts
2006 2007 2008 Year +1 Year +2 Year +3 Year +4 Year +5
BALANCE SHEET
ASSETS:
Cash and Cash Equivalents 1,651 910 2,064 1,551 1,695 1,890 2,032 2,230
common size 5.5% 2.6% 5.7% 12.0 12.0 12.0 12.0 12.0
rate of change -44.9% 126.8% Assume ending cash balances equal to 12 days sales.
Marketable Securities 1,171 1,571 213 1,034 1,130 1,260 1,355 1,487
common size 3.9% 4.5% 0.6% 8.0 8.0 8.0 8.0 8.0
rate of change 34.2% -86.4% Assume ending balances equal to 8 days sales.
Accounts Receivable - Net 3,725 4,389 4,683 5,143 5,593 6,380 6,492 7,633
common size 12.4% 12.7% 13.0% 38.0 38.0 38.0 38.0 38.0
rate of change 17.8% 6.7% Assume 38 days to collect sales in accounts receivable.
Inventories 1,926 2,290 2,522 2,730 3,033 3,421 3,546 4,116
common size 6.4% 6.6% 7.0% 8.5 8.5 8.5 8.5 8.5
rate of change 18.9% 10.1% Assume average inventory turnover of roughly 8.5 times per year.
Prepaid Expenses and Other Current Assets 657 991 1,324 1,445 1,578 1,760 1,892 2,077
common size 2.2% 2.9% 3.7% 9.1% 9.3% 11.5% 7.5% 9.7%
rate of change 50.8% 33.6% Assume growth with sales.
Deferred Tax Assets - Current
common size
rate of change
Other Current Assets (1)
common size
rate of change
Other Current Assets (2)
common size
rate of change
Current Assets 9,130 10,151 10,806 11,904 13,029 14,712 15,318 17,543
common size 30.5% 29.3% 30.0% 30.9% 31.3% 32.1% 31.5% 32.8%
rate of change 11.2% 6.5% 10.2% 9.5% 12.9% 4.1% 14.5%
Long Term Investments 3,690 4,354 3,883 4,116 4,363 4,625 4,902 5,196
common size 12.3% 12.6% 10.8% 6% 6% 6% 6% 6%
rate of change 18.0% -10.8% Assume steady growth.
Property, Plant & Equipment - at cost 19,058 21,896 22,552 24,723 27,662 30,939 34,463 38,330
common size 63.7% 63.2% 62.7%
rate of change 14.9% 3.0% PP&E assumptions - see schedule in forecast development
<Accumulated Depreciation> -9,371 -10,668 -10,889 -12,471 -14,241 -16,220 -18,426 -20,878
common size -31.3% -30.8% -30.3%
rate of change 13.8% 2.1% See depreciation schedule in forecast development worksheet.
Amortizable Intangible Assets (net) 637 796 732 668 605 543 483 427
common size 2.1% 2.3% 2.0% -64.0 -63.0 -62.0 -60.0 -56.0
rate of change 25.0% -8.0% Assume amortization per PepsiCo disclosures in Note 4; assume no new investments.
Goodwill and Nonamortizable Intangibles 5,806 6,417 6,252 6,822 7,453 8,312 8,936 9,806
common size 19.4% 18.5% 17.4% 9.1% 9.3% 11.5% 7.5% 9.7%
rate of change 10.5% -2.6% Assume growth with sales.
Deferred Tax Assets - Noncurrent
common size
rate of change
Other Non-Current Assets (1) 980 1,682 2,658 2,738 2,820 2,904 2,992 3,081
common size 3.3% 4.9% 7.4% 3.0% 3.0% 3.0% 3.0% 3.0%
rate of change 71.6% 58.0% Assume steady growth.
Other Non-Current Assets (2)
common size
rate of change
Total Assets 29,930 34,628 35,994 38,499 41,692 45,815 48,669 53,506
common size 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0%
rate of change 15.7% 3.9% 7.0% 8.3% 9.9% 6.2% 9.9%
LIABILITIES:
Accounts Payable - Trade 2,102 2,562 2,846 3,080 3,442 3,875 3,947 4,768
common size 7.0% 7.4% 7.9% 48.0 48.0 48.0 48.0 48.0
rate of change 21.9% 11.1% Assume a steady payment period consistent with recent years.
Current Accrued Liabilities 4,394 5,040 5,427 5,921 6,470 7,215 7,757 8,512
common size 14.7% 14.6% 15.1% 9.1% 9.3% 11.5% 7.5% 9.7%
rate of change 14.7% 7.7% Assume growth with SG&A expenses, which grow with sales.
Notes Payable and Short Term Debt 274 0 369 385 417 458 487 535
common size 0.9% 0.0% 1.0% 1.0% 1.0% 1.0% 1.0% 1.0%
rate of change -100.0% Assume 1.0 percent of total assets.
Current Maturities of Long Term Debt 0 0 0 0 0
common size 0.0 0.0 0.0 0.0 0.0
rate of change Current maturities of long-term debt per long-term debt note.
Deferred Tax Liabilities - Current 0 0 0 0 0
common size 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change Assume steady state growth.
Income Taxes Payable 90 151 145 154 167 183 195 214
common size 0.3% 0.4% 0.4% 0.4% 0.4% 0.4% 0.4% 0.4%
rate of change 67.8% -4.0% Assume a steady percentage of total assets.
Other Current Liabilities (1) 0 0 0 0 0
common size 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change Assume steady state growth.
Other Current Liabilities (2) 0 0 0 0 0
common size 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change Assume steady state growth.
Current Liabilities 6,860 7,753 8,787 9,540 10,495 11,731 12,385 14,029
common size 22.9% 22.4% 24.4% 24.8% 25.2% 25.6% 25.4% 26.2%
rate of change 13.0% 13.3% 8.6% 10.0% 11.8% 5.6% 13.3%
Long Term Debt 2,550 4,203 7,858 8,405 9,102 10,002 10,625 11,681
common size 8.5% 12.1% 21.8% 21.8% 21.8% 21.8% 21.8% 21.8%
rate of change 64.8% 87.0% Assume steady percent of total assets.
Long Term Accrued Liabilities 4,624 4,792 7,017 7,656 8,365 9,329 10,030 11,006
common size 15.4% 13.8% 19.5% 9.1% 9.3% 11.5% 7.5% 9.7%
rate of change 3.6% 46.4% Assume growth with SG&A expenses, which grow with sales.
Deferred Tax Liabilities - Noncurrent 528 646 226 242 262 288 306 336
common size 1.8% 1.9% 0.6% 0.6% 0.6% 0.6% 0.6% 0.6%
rate of change 22.3% -65.0% Assume steady percent of total assets.
Other Non-Current Liabilities (1) 0 0 0 0 0
common size 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change Assume steady state growth.
Other Non-Current Liabilities (2) 0 0 0 0 0
common size 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change Assume steady state growth.
Total Liabilities 14,562 17,394 23,888 25,843 28,224 31,350 33,345 37,052
common size 48.7% 50.2% 66.4% 67.1% 67.7% 68.4% 68.5% 69.2%
rate of change 19.4% 37.3% 8.2% 9.2% 11.1% 6.4% 11.1%
SHAREHOLDERS' EQUITY
Minority Interest 0 0 0 0 0
common size 0.0 0.0 0.0 0.0 0.0
rate of change Minority interest assumptions
Preferred Stock -79 -91 -97 0 0 0 0 0
common size -0.3% -0.3% -0.3% 0.0 0.0 0.0 0.0 0.0
rate of change 15.2% 6.6% Preferred stock assumptions
Common Stock + Paid in Capital 614 480 381.0 407.5 441 485 515 566
common size 2.1% 1.4% 1.1% 1.1% 1.1% 1.1% 1.1% 1.1%
rate of change -21.8% -20.6% Assume steady percent of total assets.
Retained Earnings <Deficit> 24,837 28,184 30,638 33,565 36,842 40,296 43,624 47,203
common size 83.0% 81.4% 85.1%
rate of change 13.5% 8.7% Add net income and subtract dividends; see dividends forecast box below.
Accum. Other Comprehensive Income <Loss> -2,246 -952 -4,694 -4,694 -4,694 -4,694 -4,694 -4,694
common size -7.5% -2.7% -13.0% 0.0 0.0 0.0 0.0 0.0
rate of change -57.6% 393.1% Add accumulated other comprehensive income items from income statement
Other Equity Adjustments 0 0 0 0 0
common size 0.0 0.0 0.0 0.0 0.0
rate of change Other equity adjustments assumptions
<Treasury Stock> -7,758 -10,387 -14,122 -16,622 -19,122 -21,622 -24,122 -26,622
common size -25.9% -30.0% -39.2% -2500 -2500 -2500 -2500 -2500
rate of change 33.9% 36.0% Treasury stock repurchases, net of treasury stock reissues.
Common Shareholders' Equity 15,447 17,325 12,203.0 12,656.1 13,467.4 14,465.3 15,323.5 16,453.6
common size 51.6% 50.0% 33.9% 32.9% 32.3% 31.6% 31.5% 30.8%
rate of change 12.2% -29.6% 3.7% 6.4% 7.4% 5.9% 7.4%
Total Liabilities and Equities 29,930 34,628 35,994 38,499 41,692 45,815 48,669 53,506
common size 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0% 100.0%
rate of change 15.7% 3.9% 7.0% 8.3% 9.9% 6.2% 9.9%
Check figures: Balance Sheet A=L+OE? 0 0 0 0 0 0 0 0
Initial adjustment needed to balance the balance sheet:
-444 -269 -517 -762 -985
Account adjusted: Dividends
Dividends forecasts:
Common Dividends: 2,571 3,055 3,301 3,636 3,863
50.0% 50.0% 50.0% 50.0% 50.0%
Assume dividend payout of lagged net income from continuing operations.
Preferred Dividends: 169 0 0 0 0
169.0 0.0 0.0 0.0 0.0
Enter preferred stock dividend payments, if any.
Implied Dividends: 444 269 517 762 985
Implied dividend amount to balance the balance sheet.
Total Dividends: 3,184 3,325 3,818 4,398 4,848
Total dividend forecast amounts.
shares 1553
1 2 3 4 5 Growth Rate 3%
Year +1 Year +2 Year +3 Year +4 Year +5 0 Beta 0.75
Dividends 5,488.8 5,790.7 6,274.8 6,868.3 7,297.1 8,186.5 Rf 4%
PV of dividends 5,058.8 4,919.0 4,912.6 4,956.0 4,852.9 MRP 6%
Sum of PV of Year +1 to year +5 24,699.3 RE 8.50%
Dt+6 8,186.5
Continuing Div. 148,845.4
PV of Continuing Div 98,988.9
Value of the Firm 123,688.2
Adjust for the over discounting 128,945.0
Share Value 83.03
2008 Year +1 Year +2 Year +3 Year +4 Year +5
BV 12,203.0 12,656.1 13,467.4 14,465.3 15,323.5 16,453.6
NI 5,941.9 6,602.1 7,272.7 7,726.4 8,427.3
Required Income 1,037.3 1,075.8 1,144.7 1,229.5 1,302.5 1,398.6
Residual Income 4,904.6 5,526.3 6,128.0 6,496.9 7,124.8
PV of Res. Income 4,520.4 4,694.4 4,797.6 4,688.0 4,738.3
Sum of Pv of Res. Income 23,438.7
RI t+6 7,281.5
Continuing Value 132,391.7
PV continuing Value 88,046.5
Firm Value before mid year Adjust 123,688.2
Adjusting for mid year 128,945.0
Share Value 83.03

Cash Flows

Actuals Forecasts
IMPLIED STATEMENT OF CASH FLOWS
Net Income 1
Add back Depreciation Expense (net) 2
Add back Amortization Expense (net) 3
<Increase> Decrease in Receivables - Net 4
<Increase> Decrease in Inventories 5
<Increase> Decrease in Prepaid Expenses 6
<Increase> Decrease in Other Curr. Assets (1) 7
<Increase> Decrease in Other Curr. Assets (2) 8
Increase <Decrease> in Accounts Payable - Trade 9
Increase <Decrease> in Current Accrued Liabilities 10
Increase <Decrease> in Income Taxes Payable 11
Increase <Decrease> in Other Current Liabilities (1) 12
Increase <Decrease> in Other Current Liabilities (2) 13
Net Change in Deferred Tax Assets and Liabilities 14
Increase <Decrease> in Long-Term Accrued Liabilities 15
Increase <Decrease> in Other Non-Current Liabilities (1) 16
Increase <Decrease> in Other Non-Current Liabilities (2) 17
Net Cash Flows from Operations 18
<Increase> Decrease in Prop., Plant, & Equip. at cost 19
<Increase> Decrease in Marketable Securities 20
<Increase> Decrease in Investment Securities 21
<Increase> Decrease in Amortizable Intangible Assets (net) 22
<Increase> Decrease in Goodwill and Nonamort. Intang. 23
<Increase> Decrease in Other Non-Current Assets (1) 24
<Increase> Decrease in Other Non-Current Assets (2) 25
Net Cash Flows from Investing Activities 26
Increase <Decrease> in Short-Term Debt 27
Increase <Decrease> in Long-Term Debt 28
Increase <Decrease> in Minority Interest and Preferred Stock 29
Increase <Decrease> in Common Stock + Paid in Capital 30
Increase <Decrease> in Accum. OCI and Other Equity Adjs. 31
Increase <Decrease> in Treasury Stock 32
Dividends 33
Net Cash Flows from Financing Activities 34
Net Change in Cash 35
Check Figure:
Net change in cash - Change in cash balance