Discussion ; One page

profileWitts
FSAPspreadsheet.xls
Home>Business & Finance homework help>Discussion ; One page

Data

Financial Statement Analysis Package (FSAP): Version 7.0
Financial Reporting, Financial Statement Analysis, and Valuation: A Strategic Perspective, 7th Edition FSAP User Guides:
By James Wahlen, Steve Baginski and Mark Bradshaw
The Data spreadsheet is designed for up to six years of financial statement data. The user must input the most recent year of financial statement data in column G, regardless of the number of years of data inputted.
The FSAP User Guides appear in column J to the right. The user must conform financial statement data to the FSAP template because the spreadsheets within FSAP use the Data spreadsheet as their base. The user can, however, rename account titles as necessary to match the account titles of the particular firm. FSAP contains a number of general purpose accounts that can be renamed to fit the accounts of the particular firm (for example, Other Current Assets (1) and (2)).
Enter data in the blue-font cells shaded light green. FSAP automatically computes the amounts of various sub-totals and totals within the Data spreadsheet. These items are shaded in gray and serve in checking the mathematical accuracy of inputted amounts. FSAP checks to ensure that total assets equal total liabilities and shareholders’ equity, that total revenues and gains minus total expenses and losses equal reported net income, and that cash flows from operating, investing, and financing activities equal the change in cash on the balance sheet. These financial data checks appear at the bottom of the Data spreadsheet. Any material non-zero amounts (that are not due to rounding) on these rows require the user to re-check amounts inputted to identify and correct the error.
Throughout FSAP, enter amounts for account titles listed in brackets <> as negative numbers. Except for per share amounts, be consistent with the units of the amounts entered (for example, thosands or millions).
Analyst Name: Tareq Hamdan Insert your name in column B.
Company Name: Proctor & Gamble Enter the name of the company in Column B. This name will appear on the output of all spreadsheets within FSAP.
Year (Most recent in far right column.) 2011 2012 2013 2014 2015 2016
BALANCE SHEET DATA Enter Balance Sheet Data:
Assets:
Cash and Cash Equivalents 201 145 174 313 281 270
Marketable Securities 149 508 133 141 157 53
Accounts Receivable - Net 114 140 191 224 288 330
Inventories 343 423 546 636 692 693
Prepaid Expenses and Other Current Assets 55 71 94 127 149 169
Deferred Tax Assets - Current 61 64 71 89 129 234
Other Current Assets (1) Other Current Assets (1) and (2) can be renamed and used for different types of current assets for different firms.
Other Current Assets (2)
Current Assets 924 1,351 1,209 1,530 1,696 1,748 FSAP automatically computes the amount of total current assets.
Long Term Investments 136 135 60 6 21 71
Property, Plant & Equipment - at cost 2,435 2,878 3,468 4,258 5,307 5,717
<Accumulated Depreciation> -1,050 -1,326 -1,626 -1,970 -2,416 -2,761
Amortizable Intangible Assets (net) 25 27 35 38 42 67
Goodwill and Nonamortizable Intangibles 63 69 92 161 216 267
Deferred Tax Assets - Noncurrent
Equity and Other Investments 144 168 201 219 259 303 Other Non-Current Assets (1) and (2) can be renamed and used for different types of long term assets for different firms.
Other Non-Current Assets (2) 52 86 73 187 219 261
Total Assets 2,730 3,387 3,514 4,429 5,344 5,673 FSAP automatically computes the amount of total assets.
Liabilities and Equities:
Accounts Payable - Trade 169 199 221 341 391 325
Current Accrued Liabilities 366 425 553 662 757 783
Notes Payable and Short Term Debt 0 0 277 700 710 713
Current Maturities of Long Term Debt 1 1 1 1 1 1
Deferred Tax Liabilities - Current
Income Taxes Payable
Deferred Revenue 73 121 175 232 297 368 Other Current Liabilities (1) and (2) can be renamed and used for different types of current liabilities for different firms.
Other Current Liabilities (2)
Current Liabilities 609 746 1,227 1,936 2,156 2,190 FSAP automatically computes the amount of total current liabilities.
Long Term Debt 4 4 3 2 550 550
Long Term Accrued Liabilities 1 145 194 263 354 442
Deferred Tax Liabilities - Noncurrent 33 22 0 0 0 0
Other Non-Current Liabilities (1) Other Non-Current Liabilities (1) and (2) can be renamed and used for different types of non-current liabilities for different firms.
Other Non-Current Liabilities (2)
Total Liabilities 648 917 1,423 2,201 3,060 3,182 FSAP automatically computes the amount of total liabilitries.
Minority Interest
Preferred Stock
Common Stock + Paid in Capital 998 996 130 40 40 40
Retained Earnings <Deficit> 1,070 1,445 1,939 2,151 2,189 2,402
Accum. Other Comprehensive Income <Loss> 14 29 21 37 55 48 The amount for this item appears either on the Balance Sheet or the Statement of Shareholders' Equity. Be sure to enter as a positive or negative amount as appropriate.
Other Equity Adjustments Include on this line any items that do not fall within some other shareholders' equity line. Such items seldom appear in balance sheets of U.S. firms. Amounts that increase (decrease) total shareholders' equity should be entered as positive (negative) amounts.
<Treasury Stock>
Common Shareholders' Equity 2,082 2,470 2,091 2,229 2,284 2,491 FSAP automatically computes the amount of total shareholders' equity.
Total Liabilities and Equities 2,730 3,387 3,514 4,429 5,344 5,673 FSAP automatically computes the amount of total liabilities plus shareholders' equity.
INCOME STATEMENT DATA 2011 2012 2013 2014 2015 2016 Enter Income Statement Data:
When entering income statement data, enter amounts that increase income (revenues, gains, income) as positive amounts, and enter amounts that decrease income (expenses, losses) as negative amounts.
Revenues 4,076 5,294 6,369 7,787 9,411 10,383
<Cost of Sales and Occupancy> -1,681 -2,191 -2,605 -3,179 -3,999 -4,645
Gross Profit 2,394 3,103 3,764 4,608 5,412 5,738 FSAP automatically computes the amount of gross profit.
<Store Operating Expenses> -1,380 -1,790 -2,166 -2,688 -3,216 -3,745
<Other Operating Expenses> -141 -172 -193 -254 -294 -330
<Depreciation and Amortization> -245 -289 -340 -387 -467 -549
<General and Administrative Expenses> -245 -304 -362 -479 -489 -456 Other Operating Expenses (1), (2), and (3) can be renamed and used for different types of recurring operating expenses for different firms.
<Restructuring Charges> -267
Income from equity investees 38 59 77 94 108 114 Other Operating Income (1) and (2) can be renamed used for different sources of recurring operating income for different firms.
Other Operating Income (2) 38% 41% 45% 45% 40% - average returns on equity investments
Non-Recurring Operating Gains The FSAP user must decide whether particular operating gains or losses are non-recurring - infrequent and unusual given the firm's business and operating environment. If so, enter the amounts on the appropriate rows. Enter expense and loss amounts as negative numbers.
<Non-Recurring Operating Losses>
Operating Profit 422 607 780 894 1,054 504 FSAP automatically computes the amount of operating profit.
Interest Income 12 15 17 21 41 9
<Interest Expense> -0 -0 -1 -8 -38 -53
Income <Loss> from Equity Affiliates Enter any amounts of income (or <loss>) from equity or noncontrolled affiliates.
Other Income or Gains Enter income or gain amounts that are unusual and non-recurring and outside of normal business operations.
<Other Expenses or Losses> Enter expense or loss amounts that are unusual and non-recurring and outside of normal buisness operations. Enter these amounts as negative numbers.
Income before Tax 434 621 796 906 1,056 460 FSAP automatically computes the amount of income before tax.
<Income Tax Expense> -168 -232 -302 -325 -384 -144 Enter the amount of income tax expense that appears on the income statement in the section for income from continuing operations. If income tax expense reduces income, enter the amount as a negative number.
<Minority Interest in Earnings> Enter the amount of minority interest in income. Enter the amount as a negative number.
Income <Loss> from Discontinued Operations Enter any amount reported in the separate section of the income statement labeled Discontinued Operations. The amount is reported net of tax effects. Enter as a positive or negative number as appropriate.
Extraordinary Gains <Losses> Enter any amount reported in the separate section of the income statement labeled Extraordinary Items. The amount is reported net of taxes. Enter as a positive or negative number as appropriate.
Changes in Acctg. Principles -17 Enter any amount reported in the separate section of the income statement labeled Changes in Accounting Principles. The amount is reported net of income taxes. Enter as a positive or negative number as appropriate.
Net Income (computed) 266 388 494 564 673 316 FSAP automatically computes the amount of net income using the above data for revenues, expenses, gains and losses.
Net Income (enter reported amount as a check) 266 388 494 564 673 316 Enter the amount of reported net income on this line. It will be used by FSAP to provide a mathematical check on the amounts of all revenues and expenses on preceding lines.
Other Comprehensive Income Items 23 15 -8 16 17 -6 This amount usually appears in the Statement of Shareholders' Equity. Enter as a positive or negative number as appropriate.
Comprehensive Income 288 403 486 581 690 309 FSAP automatically computes the amount of comprehensive income.
STATEMENT OF CASH FLOWS DATA 2011 2012 2013 2014 2015 2016 Enter Statement of Cash Flows Data:
In the Statement of Cash Flows Data, enter amounts reported on the firm's statement of cash flows. Enter amounts that increase (decrease) cash as positive (negative) numbers. The row headings help indicate whether amounts should be positive or negative.
Net Income 266 388 494 564 673 316 FSAP automatically enters the Net Income amount computed above.
Add back Depreciation and Amortization Expenses 259 305 367 413 491 605
Add back Stock-Based Compensation Expense 106 104 75
Deferred Income Taxes -6 -3 -31 -84 -37 -117
<Income from Equity Affiliates, net of Dividends> -23 -33 -19 -11 0 -9
<Increase> Decrease in Accounts Receivable -49
<Increase> Decrease in Inventories -65 -78 -122 -86 -49 -1
<Increase> Decrease in Prepaid Expenses -13 -17
<Increase> Decrease in Tax Benefit from Exercise of Stock Options 37 63 110 -116 -85 -11
Net Amortization of Trading Securities 6 12 10 2 1 -
Increase <Decrease> in Accounts Payable 25 28 10 105 36 -64
Increase <Decrease> in Deferred Revenue 31 48 53 57 63 72
Increase <Decrease> in Accruals 46 80 23 187 125 7
Provision for impairment and asset disposal 8 14 20 20 26 325
Increase <Decrease> in Other Non-Current Liabilities (2)
Other Addbacks to Net Income 17
<Other Subtractions from Net Income>
Other Operating Cash Flows -7 -16 57 -41 -16 60
Net CF from Operations 563.752 790.238 923.452 1,132 1,331 1,259 FSAP automatically computes the amount of cash flow from operations.
Proceeds from Sales of Property, Plant, and Equipment
<Property, Plant, and Equipment Acquired> -357 -386 -644 -771 -1,080 -985 Net Amount.
<Increase> Decrease in Marketable Securities -54 -212 452 61 -12 24 Includes Maturity of AFS Securities
Investments Sold
<Investments Acquired> -117 -72 -29 -131 -110 -126 Net Amount.
Distributions from equity investees 29 38
Other Investment Transactions (2)
Net CF from Investing Activities -499 -632 -221 -841 -1,202 -1,087 FSAP automatically computes the amount of cash flow from investing activities.
Increase in Short-Term Borrowing 277 423 18,081 66,299
<Decrease in Short-Term Borrowing> -18,071 -66,297
Increase in Long-Term Borrowing 549
<Decrease in Long-Term Borrowing> -1 -1 -1 -1 -1 -1
Issue of Capital Stock 107 138 164 159 177 112
Proceeds from Stock Option Exercises 117 93 15
<Share Repurchases - Treasury Stock> -76 -203 -1,114 -854 -997 -311
<Dividend Payments>
Other Financing Transactions (1)
Other Financing Transactions (2) -4 -2
Net CF from Financing Activities 31 -67 -674 -155 -172 -185 FSAP automatically computes the amount of cash flow from financing activities.
Effects of exchange rate changes on cash 3 3 0 4 11 1
Net Change in Cash 99 -56 29 139 -31 -12 FSAP automatically computes the net change in cash.
Cash and Cash Equivalents, Beginning of Year 201 145 174 313 281
Cash and Cash Equivalents, End of Year 145 174 313 281 270
SUPPLEMENTAL DATA 2011 2012 2013 2014 2015 2016 Enter Supplemental Data:
Statutory Tax Rate 35.0% 35.0% 35.0% 35.0% 35.0% 35.0% Enter the statutory income tax rate applicable to ordinary income and deductions (such as the deduction for interest expense). The Federal corporate income tax rate is currently 35 percent in the United States. Alternatively, one can enter a statutory tax rate that captures the combined effects of Federal, state, and foreign income taxes. These rates are commonly disclosed in the tax note.
Average Tax Rate Implied from Income Statement Data 38.7% 37.5% 37.9% 35.8% 36.3% 31.3% This rate is computed by FSAP as the ratio of the income tax expense to income before tax.
After-tax Effects of Nonrecurring and Unusual Items on Net Income 0 0 0 -17 0 0 This row automatically sums the pre-tax amounts of unusual and nonrecurring items and the after-tax amounts of discontinued operatons, extraordinary items and changes in accounting principles from the income statement above. The analyst must then adjust the items that are stated in pre-tax amounts to an after-tax basis either by adjusting for the specific amounts of applicable tax (or tax savings) as disclosed by the firm, or if not disclosed, by adjusting these items for the statutory tax rate.
Total Deferred Tax Assets (from above) 61 64 71 89 129 234 This row automatically sums the total deferred tax assets (current plus non-current) amounts from the asset section of teh balance sheet above.
Deferred Tax Asset Valuation Allowance -14 -8 -8 -9 -14 -20 Enter the deferred tax asset valuation allowance amounts. These amounts (if any) are usually disclosed in the tax note.
Allowance for Uncollectible Accounts Receivable 5 2 3 4 3 5 Enter the allowances for uncollectible accounts receivable. These amounts (if any) are usually disclosed either in the receivables note or in a supplemental inforrmation note.
Depreciation Expense 245 289 340 387 467 549 Enter the amount of depreciation expense on property, plant and equipment. These amounts (if any) are usually disclosed either in the property, plant and equipment note or in a supplemental inforrmation note. If depreciation expense is not disclosed separately from amortization expense, enter depreciation plus amortization expense.
Preferred Stock Dividends (total, if any) 0 0 0 0 0 0 Enter the total amount of preferred stock dividends paid, if any.
Common Shares Outstanding 787 795 767 757 738 736 Enter the number of common shares outstanding at the end of each year. Be sure to reduce the number of shares issued by the number of any shares held as treasury stock to arrive at the number of common shares outstanding. The number of common shares outstanding should be expressed in the same numerical units (for example, thousands or millions) as the financial statement amounts entered in the preceding cells.
Earnings per Share (basic) 0.35 0.50 0.63 0.74 0.90 0.43 Enter the amount that appears on the firm's income statement.
Common Dividends per Share 0.00 0.00 0.00 0.00 0.00 0.00 This cell computes common dividends per share by dividing the dividend payments from the cash flow statement by the number of outstanding shares. This assumes the firm pays immaterial preferred dividends. If that assumption does not hold, enter the amount of common dividends per share directly.
Market Price per Share at Fiscal Year End 14.62 23.52 25.20 33.62 26.32 14.17 This should be the closing market price per share on the last day of the firm's accounting period (usually December 31 of each year). If stock markets are closed on the last day of the accounting period, use the closing price on the most recent trading day following the end of the period.
FINANCIAL DATA CHECKS
Assets - Liabilities - Equities -0 -0 0 -0 -0 0 FSAP checks for an equality between total assets and total liabilities plus shareholders' equity. A non-zero amount in this row indicates a likely data input error in one or more balance sheet accounts.
Net Income (computed) - Net Income (reported) -0 0 -0 0 -0 0 FSAP checks that the inputted amounts of revenues and expenses equal the reported amount of net income. A non-zero amount on this row likely indicates an input error in one or more income statement accounts.
Cash Changes 0 -0 0 -0 -0 FSAP checks that the change in cash on the statement of cash flows equals the change in cash on the balance sheet. A non-zero amount indicates either a data input error on one or more rows of the cash flow statement or the use of a different definition of cash on the two financial statements. The user should identify the reason for and correct any non-zero amount.

Analysis

Financial Statement Analysis Package (FSAP): Version 7.0
Financial Reporting, Financial Statement Analysis, and Valuation: A Strategic Perspective, 7th Edition FSAP User Guides:
By James Wahlen, Steve Baginski and Mark Bradshaw
The FSAP User Guides appear in column J to the right. The Analysis worksheet in FSAP automatically computes a wide array of financial statement analysis ratios using the amounts entered on the Data worksheet.
The FSAP User Guides next to each row provide brief descriptions of ratio computations. See the text for more in-depth discussion of how to compute and interpret each ratio.
Analyst Name: Tareq Hamdan
Company Name: Proctor & Gamble
DATA CHECKS
Assets - Liabilities - Equities -0.479 -0.199 0.225 -0.148 -0.019 FSAP checks the Data worksheet for an equality between total assets and total liabilities plus shareholders' equity. A non-zero amount in this row indicates a likely data input error in one or more balance sheet accounts.
Net Income (computed) - Net Income (reported) -0.349 0.165 -0.156 0.045 -0.085 FSAP checks whether the net income amounts determined by revenue and expense amounts entered in the Data worksheet equal the reported amount of net income. A non-zero amount on this row likely indicates an input error in one or more income statement accounts.
Cash Changes 0 -0.156 0.045 -0.085 FSAP checks that the change in cash on the statement of cash flows equals the change in cash on the balance sheet in the Data worksheet. A non-zero amount indicates either a data input error on one or more rows of the cash flow statement or the use of a different definition of cash on the two financial statements. The user should identify the reason for and correct any non-zero amount.
In the computations below, a #DIV/0! message indicates that a ratio denominator is zero.
PROFITABILITY FACTORS: Profitability Factors:
Year 2012 2013 2014 2015 2016
RETURN ON ASSETS (based on reported amounts): Return on assets measures the rate of return the firm earns per average dollar invested in assets.
Profit Margin for ROA 7.3% 7.8% 7.3% 7.4% Note: Profit margin for ROA measures how much profitability the firm derives from its revenues. For this ratio, profitability is measured before the effects of financing costs (after tax) and minority interest in earnings.
x Asset Turnover 1.7 1.8 2.0 1.9 2008 Ratios must be calculated. Asset turnover measures how efficiently the firm uses its assets to generate sales. It measures the number of sales dollars generated per average dollar invested in assets.
= Return on Assets 12.7% 14.4% 14.3% 14.3% Rate of return on assets is the product of the firm's profitability and its efficiency.
RETURN ON ASSETS (excluding the effects of nonrecurring items): See the preceding FSAP User Guides on ROA.
Profit Margin for ROA 7.3% 7.8% 7.5% 7.4% These computations of ROA exclude the after-tax effects of nonrecurring items in income to measure the firm's persistent ROA.
x Asset Turnover 1.7 1.8 2.0 1.9
= Return on Assets 12.7% 14.4% 14.8% 14.3%
RETURN ON COMMON EQUITY (based on reported amounts): Return on common equity measures the rate of return the firm earns per average dollar in common shareholders' equity.
Profit Margin for ROCE 7.3% 7.8% 7.2% 7.1% Profit margin for ROCE measures the net profit margin per dollar of sales. Profit margin for ROCE is measured after deducting any preferred dividends from net income, in order to compute the amount of net income available to common equity shareholders.
x Asset Turnover 1.7 1.8 2.0 1.9 Asset turnover measures how efficiently the firm uses its assets to generate sales. It measures the number of sales dollars generated per average dollar invested in assets.
x Capital Structure Leverage 1.3 1.5 1.8 2.2 Capital structure leverage measures the average amount invested in assets divided by the average amount financed by common equity shareholders.
= Return on Common Equity 17.1% 21.7% 26.1% 29.8% Rate of return on common equity is the product of the firm's profitability, efficiency, and leverage.
RETURN ON COMMON EQUITY (excluding the effects of nonrecurring items): See the preceding FSAP User Guides on ROCE.
Profit Margin for ROCE 7.3% 7.8% 7.5% 7.1% These computations of ROCE exclude the after-tax effects of nonrecurring items in income to measure the firm's persistent ROCE.
x Asset Turnover 1.7 1.8 2.0 1.9
x Capital Structure Leverage 1.3 1.5 1.8 2.2
= Return on Common Equity 17.1% 21.7% 26.9% 29.8%
OPERATING PERFORMANCE:
Gross Profit / Revenues 58.6% 59.1% 59.2% 57.5% Gross profit margin as a percent of revenues.
Operating Profit / Revenues 11.5% 12.3% 11.5% 11.2% Operating income as a percent of revenues.
Net Income / Revenues 7.3% 7.8% 7.2% 7.1% Net income as a percent of revenues.
Comprehensive Income / Revenues 7.6% 7.6% 7.5% 7.3% Comprehensive income as a percent of revenues.
PERSISTENT OPERATING PERFORMANCE (excluding the effects of nonrecurring items):
Persistent Operating Profit / Revenues 11.5% 12.3% 11.5% 11.2% Operating income as a percent of revenues after excluding the effects of non-recurring operating income items (such as non-recurring operating expenses and losses).
Persistent Net Income / Revenues 7.3% 7.8% 7.5% 7.1% Net income as a percent of revenues, after excluding the effects of non-recurring items in income.
GROWTH:
Revenue Growth 29.9% 20.3% 22.3% 20.9% Year-on-year growth rate in revenues.
Net Income Growth 46.1% 27.3% 14.2% 19.2% Year-on-year growth rate in net income.
Persistent Net Income Growth 46.1% 27.3% 17.6% 15.7% Year-on-year growth rate in net income, after excluding the effects of non-recurring items in income.
OPERATING CONTROL:
Gross Profit Control Index 99.8% 100.8% 100.1% 97.2% The rate of change in gross profit relative to the rate of change in revenues.
Operating Profit Contol Index 110.6% 107.0% 93.7% 97.5% The rate of change in operating income relative to the rate of change in revenues.
Profit Margin Decomposition:
Gross Profit Margin 58.6% 59.1% 59.2% 57.5% Gross profit margin as a percent of revenues.
Operating Profit Index 19.5% 20.7% 19.4% 19.5% Operating profit as a percent of gross profit. The complement of this percentage is the percent of gross profit absorbed by overhead and operating expenses.
Leverage Index 102.3% 102.0% 101.4% 100.2% Income before tax as a percent of operating profit. The complement of this percentage is the percent of operating profit absorbed by (net) financing costs. If this index is great than 100%, it implies financing income (interest income, income from equity affiliates) exceeds financing costs (interest expense).
Tax Index 62.5% 62.1% 62.3% 63.7% Net income as a percent of income before tax. The complement of this percentage is the average effective tax rate. This index is also affected by items such as extraordinary gains and losses, discontinued operations, and changes in accounting principles.
Net Profit Margin 7.3% 7.8% 7.2% 7.1% Net income as a percent of revenues. The net profit margin will also equal the product of the gross profit margin times the operating profit index, the leverage index and the tax index.
Comprehensive Income Performance:
Comprehensive Income Index 103.9% 98.3% 102.9% 102.6% Comprehensive income as a percent of net income.
Comprehensive Income Margin 7.6% 7.6% 7.5% 7.3% Comprehensive income as a percent of revenues.
RISK FACTORS: Risk Factors:
Year 2012 2013 2014 2015 2016
LIQUIDITY:
Current Ratio 1.81 0.99 0.79 0.79 Current assets divided by current liabilities.
Quick Ratio 1.06 0.41 0.35 0.34 More liquid current assets (cash and cash equivalents, marketable securities, accounts receivable) divided by current liabilities.
Operating Cash Flow to Current Liabilities 116.6% 93.6% 71.6% 65.1% Operating cash flows divided by the average amount of current liabilities.
ASSET TURNOVER:
Accounts Receivable Turnover na 5.9 5.8 5.5 Uses specialty revenues only. Total revenues divided by the average balance in accounts receivable.
Days Receivables Held na 62 63 66 The number of days in receivables is measured as 365 divided by the accounts receivable turnover rate. This measures the average number of days to collect receivables.
Inventory Turnover 5.7 5.4 5.4 6.0 Cost of goods sold divided by the average amaount of inventory.
Days Inventory Held 64 68 68 61 The number of days in inventory is measured as 365 divided by the inventory turnover rate. This measures the average number of days to make and sell inventory.
Accounts Payable Turnover 12.3 13.0 11.6 11.1 Inventory purchases (computed as cost of goods sold plus the change in inventory) divided by the average amount in accounts payable.
Days Payables Held 30 28 31 33 The number of days in payables is measured as 365 divided by the accounts payable turnover rate. This measures the average number of days to pay payables.
Net Working Capital Days na 102 99 94 Net working capital days measures the number of days to make and sell inventory plus the number of days to collect receivables, minus the number of days to pay payables.
Revenues / Average Net Fixed Assets 3.61 3.75 3.77 3.63 Total revenues divided by the average balance in net property, plant, and equipment. This measures efficiency is using fixed assets to generate revenues.
Cash Turnover 30.6 40.0 32.0 31.7 Revenues divided by the average cash balance.
Days Sales Held in Cash 11.9 9.1 11.4 11.5 The number of days sales held in cash is measured as 365 divided by the cash turnover rate. It measures the average number of days of sales held in cash and cash equivalents.
SOLVENCY:
Total Liabilities / Total Assets 27.1% 40.5% 49.7% 57.3% This ratio measures the percentage of total ssets financed by total liabilities.
Total Liabilities / Shareholders' Equity 37.1% 68.1% 98.7% 134.0% This debt/equity ratio measures total liabiliteis as a percent of common shareholders' equity.
LT Debt / LT Capital 0.1% 0.1% 0.1% 19.4% This ratio measures the percent of debt financing relative to total long term capital (long term debt plus commmon shareholders' equity).
LT Debt / Shareholders' Equity 0.1% 0.1% 0.1% 24.1% This ratio measures the percent of long term debt financing relative to commmon shareholders' equity.
Operating Cash Flow to Total Liabilities 101.0% 78.9% 62.5% 50.6% Operating cash flows divided by the average amount of total liabilities.
Interest Coverage Ratio (reported amounts) 1552.6 613.5 106.8 28.7 Net income before interest expense, income taxes, and minority interest in income, divided by interest expense.
Interest Coverage ratio (recurring amounts) 1552.6 613.5 108.9 28.7 Net income before interest expense, income taxes, minority interest in income, and non-recurring items divided by interest expense.
RISK FACTORS:
Bankruptcy Predictors:
Altman Z Score 15.22 11.48 9.95 6.72 4.59 The Altman Z-score is a multivariate predictor of bankruptcy.
Bankruptcy Probability 0.00% 0.00% 0.00% 0.00% 0.02% The probability of bankruptcy over the next two years as indicated by the Altman Z-score.
Earnings Manipulation Predictors:
Beneish Earnings Manipulation Score -2.82 -2.90 -2.89 -2.84 -3.06 The Beneish Earnings Manipulation Score is a multivariate indicator of the likelihood reported earnings numbers have been fraudulently manipulated.
Earnings Manipulation Probability 0.24% 0.19% 0.19% 0.23% 0.11% The probability of earnings manipulation given the Beneish Earnings Manipulation Score.
STOCK MARKET-BASED RATIOS:
Stock Returns 60.9% 7.1% 33.4% -21.7% -46.2% Stock returns measure fiscal year-end share price plus dividends divided by beginning of year share price.
Price-Earnings Ratio (reported amounts) 47.52 40.00 45.43 29.24 32.95 Fiscal year-end share price divided by earnings per share.
Price-Earnings Ratio (recurring amounts) 47.52 40.00 44.08 29.24 32.95 Fiscal year-end share price divided by earnings per share after excluding the per-share effects of non-recurring items in income.
Market Value to Book Value Ratio 7.6 9.3 11.4 8.5 4.2 Market value of common equity divided by book value of common equity.
INCOME STATEMENT ITEMS AS A PERCENT OF REVENUES: Common-Sized Income Statements:
Year 2012 2013 2014 2015 2016
Revenues 100.0% 100.0% 100.0% 100.0% 100.0% All of the common-size income statement ratios measure a particular income amount as a percent of total revenues.
<Cost of Sales and Occupancy> -41.4% -40.9% -40.8% -42.5% -44.7%
Gross Profit 58.6% 59.1% 59.2% 57.5% 55.3%
<Store Operating Expenses> -33.8% -34.0% -34.5% -34.2% -36.1%
<Other Operating Expenses> -3.2% -3.0% -3.3% -3.1% -3.2%
<Depreciation and Amortization> -5.5% -5.3% -5.0% -5.0% -5.3%
<General and Administrative Expenses> -5.7% -5.7% -6.2% -5.2% -4.4%
<Restructuring Charges> 0.0% 0.0% 0.0% 0.0% -2.6%
Income from equity investees 1.1% 1.2% 1.2% 1.1% 1.1%
Other Operating Income (2) 0.0% 0.0% 0.0% 0.0% 0.0%
Non-Recurring Operating Gains 0.0% 0.0% 0.0% 0.0% 0.0%
<Non-Recurring Operating Losses> 0.0% 0.0% 0.0% 0.0% 0.0%
Operating Profit 11.5% 12.3% 11.5% 11.2% 4.9%
Interest Income 0.3% 0.3% 0.3% 0.4% 0.1%
<Interest Expense> -0.0% -0.0% -0.1% -0.4% -0.5%
Income <Loss> from Equity Affiliates 0.0% 0.0% 0.0% 0.0% 0.0%
Other Income or Gains 0.0% 0.0% 0.0% 0.0% 0.0%
<Other Expenses or Losses> 0.0% 0.0% 0.0% 0.0% 0.0%
Income before Tax 11.7% 12.5% 11.6% 11.2% 4.4%
<Income Tax Expense> -4.4% -4.7% -4.2% -4.1% -1.4%
<Minority Interest in Earnings> 0.0% 0.0% 0.0% 0.0% 0.0%
Income <Loss> from Discontinued Operations 0.0% 0.0% 0.0% 0.0% 0.0%
Extraordinary Gains <Losses> 0.0% 0.0% 0.0% 0.0% 0.0%
Changes in Acctg. Principles 0.0% 0.0% -0.2% 0.0% 0.0%
Net Income (computed) 7.3% 7.8% 7.2% 7.1% 3.0%
Other Comprehensive Income Items 0.3% -0.1% 0.2% 0.2% -0.1%
Comprehensive Income 7.6% 7.6% 7.5% 7.3% 3.0%
INCOME STATEMENT ITEMS: GROWTH RATES Income Statement Growth Rates:
Year 2012 2013 2014 2015 2016
COMPOUND
GROWTH
YEAR TO YEAR GROWTH RATES: RATE
Revenues 29.9% 20.3% 22.3% 20.9% 10.3% 20.6% The year-on-year growth rates indicate the annual rate of growth in a particular income item.
<Cost of Sales and Occupancy> 30.3% 18.9% 22.0% 25.8% 16.2% 22.5% The compound growth rates indicate the average compounded rate of growth in a particular income item over the five-year data period (six years of data yield five periods of growth). If fewer than six year of data have been entered into the Data Worksheet, these compounded growth rate computations should be revised to measure compounded growth over the period for which data are available.
Gross Profit 29.6% 21.3% 22.4% 17.5% 6.0% 19.1%
<Store Operating Expenses> 29.8% 21.0% 24.1% 19.6% 16.5% 22.1%
<Other Operating Expenses> 21.4% 12.2% 31.8% 16.0% 12.2% 18.5%
<Depreciation and Amortization> 18.2% 17.6% 13.8% 20.7% 17.6% 17.6%
<General and Administrative Expenses> 24.4% 18.8% 32.6% 2.0% -6.8% 13.3%
<Restructuring Charges> 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Income from equity investees 53.7% 29.8% 22.6% 15.0% 5.2% 24.2%
Other Operating Income (2) 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Non-Recurring Operating Gains 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
<Non-Recurring Operating Losses> 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Operating Profit 43.6% 28.7% 14.5% 17.9% -52.2% 3.6%
Interest Income 24.8% 17.9% 21.1% 96.1% -77.8% -5.0%
<Interest Expense> 33.3% 225.0% 546.2% 354.8% 39.8% 181.9%
Income <Loss> from Equity Affiliates 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Other Income or Gains 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
<Other Expenses or Losses> 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Income before Tax 43.1% 28.3% 13.8% 16.6% -56.5% 1.2%
<Income Tax Expense> 38.4% 29.9% 7.5% 18.2% -62.5% -3.0%
<Minority Interest in Earnings> 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Income <Loss> from Discontinued Operations 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Extraordinary Gains <Losses> 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Changes in Acctg. Principles 0.0% 0.0% 0.0% -100.0% 0.0% 0.0%
Net Income (computed) 46.1% 27.3% 14.2% 19.2% -53.1% 3.5%
Other Comprehensive Income Items -34.2% -155.3% -297.6% 5.5% -135.8% -177.1%
Comprehensive Income 39.8% 20.5% 19.5% 18.8% -55.2% 1.4%
COMMON SIZE BALANCE SHEET - AS A PERCENT OF TOTAL ASSETS Common-Sized Balance Sheets:
Year 2012 2013 2014 2015 2016
Assets:
Cash and Cash Equivalents 4.3% 4.9% 7.1% 5.3% 4.8% All of the common-size balance sheet ratios measure a particular balance sheet amount as a percent of total assets.
Marketable Securities 15.0% 3.8% 3.2% 2.9% 0.9%
Accounts Receivable - Net 4.1% 5.4% 5.1% 5.4% 5.8%
Inventories 12.5% 15.5% 14.4% 12.9% 12.2%
Prepaid Expenses and Other Current Assets 2.1% 2.7% 2.9% 2.8% 3.0%
Deferred Tax Assets - Current 1.9% 2.0% 2.0% 2.4% 4.1%
Other Current Assets (1) 0.0% 0.0% 0.0% 0.0% 0.0%
Other Current Assets (2) 0.0% 0.0% 0.0% 0.0% 0.0%
Current Assets 39.9% 34.4% 34.5% 31.7% 30.8%
Long Term Investments 4.0% 1.7% 0.1% 0.4% 1.3%
Property, Plant & Equipment - at cost 85.0% 98.7% 96.1% 99.3% 100.8%
<Accumulated Depreciation> -39.2% -46.3% -44.5% -45.2% -48.7%
Amortizable Intangible Assets (net) 0.8% 1.0% 0.9% 0.8% 1.2%
Goodwill and Nonamortizable Intangibles 2.0% 2.6% 3.6% 4.0% 4.7%
Deferred Tax Assets - Noncurrent 0.0% 0.0% 0.0% 0.0% 0.0%
Equity and Other Investments 5.0% 5.7% 4.9% 4.8% 5.3%
Other Non-Current Assets (2) 2.5% 2.1% 4.2% 4.1% 4.6%
Total Assets 100.0% 100.0% 100.0% 100.0% 100.0%
Liabilities and Equities:
Accounts Payable - Trade 5.9% 6.3% 7.7% 7.3% 5.7%
Current Accrued Liabilities 12.5% 15.7% 14.9% 14.2% 13.8%
Notes Payable and Short Term Debt 0.0% 7.9% 15.8% 13.3% 12.6%
Current Maturities of Long Term Debt 0.0% 0.0% 0.0% 0.0% 0.0%
Deferred Tax Liabilities - Current 0.0% 0.0% 0.0% 0.0% 0.0%
Income Taxes Payable 0.0% 0.0% 0.0% 0.0% 0.0%
Deferred Revenue 3.6% 5.0% 5.2% 5.6% 6.5%
Other Current Liabilities (2) 0.0% 0.0% 0.0% 0.0% 0.0%
Current Liabilities 22.0% 34.9% 43.7% 40.3% 38.6%
Long Term Debt 0.1% 0.1% 0.0% 10.3% 9.7%
Long Term Accrued Liabilities 4.3% 5.5% 5.9% 6.6% 7.8%
Deferred Tax Liabilities - Noncurrent 0.6% 0.0% 0.0% 0.0% 0.0%
Other Non-Current Liabilities (1) 0.0% 0.0% 0.0% 0.0% 0.0%
Other Non-Current Liabilities (2) 0.0% 0.0% 0.0% 0.0% 0.0%
Total Liabilities 27.1% 40.5% 49.7% 57.3% 56.1%
Minority Interest 0.0% 0.0% 0.0% 0.0% 0.0%
Preferred Stock 0.0% 0.0% 0.0% 0.0% 0.0%
Common Stock + Paid in Capital 29.4% 3.7% 0.9% 0.8% 0.7%
Retained Earnings <Deficit> 42.7% 55.2% 48.6% 41.0% 42.4%
Accum. Other Comprehensive Income <Loss> 0.9% 0.6% 0.8% 1.0% 0.9%
Other Equity Adjustments 0.0% 0.0% 0.0% 0.0% 0.0%
<Treasury Stock> 0.0% 0.0% 0.0% 0.0% 0.0%
Common Shareholders' Equity 72.9% 59.5% 50.3% 42.7% 43.9%
Total Liabilities and Equities 100.0% 100.0% 100.0% 100.0% 100.0%
BALANCE SHEET ITEMS: GROWTH RATES Balance Sheet Growth Rates:
Year 2012 2013 2014 2015 2016
COMPOUND
GROWTH
Assets: YEAR TO YEAR GROWTH RATES: RATE
Cash and Cash Equivalents -27.8% 19.8% 79.9% -10.0% -4.1% 6.1% The year-on-year growth rates indicate the annual rate of growth in a particular balance sheet item.
Marketable Securities 240.7% -73.8% 5.9% 11.6% -66.7% -18.8% The compound growth rates indicate the average compounded rate of growth in a particular balance sheet item over the five-year data period (six years of data yield five periods of growth). If fewer than six year of data have been entered into the Data Worksheet, these compounded growth rate computations should be revised to measure compounded growth over the period for which data are available.
Accounts Receivable - Net 22.5% 36.0% 17.6% 28.4% 14.4% 23.6%
Inventories 23.2% 29.3% 16.5% 8.7% 0.2% 15.1%
Prepaid Expenses and Other Current Assets 29.3% 32.4% 34.4% 17.2% 13.7% 25.1%
Deferred Tax Assets - Current 3.6% 11.2% 25.4% 45.8% 80.9% 30.7%
Other Current Assets (1) 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Other Current Assets (2) 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Current Assets 46.2% -10.5% 26.5% 10.9% 3.0% 13.6%
Long Term Investments -0.7% -55.3% -90.4% 261.8% 239.6% -12.1%
Property, Plant & Equipment - at cost 18.2% 20.5% 22.8% 24.6% 7.7% 18.6%
<Accumulated Depreciation> 26.3% 22.6% 21.2% 22.7% 14.3% 21.3%
Amortizable Intangible Assets (net) 7.4% 32.1% 7.2% 10.8% 58.4% 21.7%
Goodwill and Nonamortizable Intangibles 8.9% 34.1% 74.6% 33.5% 23.6% 33.3%
Deferred Tax Assets - Noncurrent 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Equity and Other Investments 16.3% 20.1% 8.8% 18.1% 16.9% 16.0%
Other Non-Current Assets (2) 64.2% -14.8% 156.4% 17.4% 19.0% 38.0%
Total Assets 24.1% 3.8% 26.0% 20.7% 6.2% 15.8%
Liabilities and Equities:
Accounts Payable - Trade 18.0% 10.8% 54.3% 14.6% -16.9% 14.0%
Current Accrued Liabilities 16.1% 30.1% 19.7% 14.3% 3.4% 16.4%
Notes Payable and Short Term Debt 0.0% 0.0% 152.7% 1.5% 0.4% 0.0%
Current Maturities of Long Term Debt 1.8% 1.8% 1.9% 1.7% -9.7% -0.6%
Deferred Tax Liabilities - Current 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Income Taxes Payable 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Deferred Revenue 65.2% 44.2% 32.5% 28.0% 24.1% 38.0%
Other Current Liabilities (2) 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Current Liabilities 22.5% 64.3% 57.8% 11.4% 1.6% 29.2%
Long Term Debt -16.9% -20.7% -31.8% 27996.1% -0.1% 163.2%
Long Term Accrued Liabilities 13745.3% 33.8% 35.9% 34.6% 24.9% 235.2%
Deferred Tax Liabilities - Noncurrent -34.5% -100.0% 0.0% 0.0% 0.0% -100.0%
Other Non-Current Liabilities (1) 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Other Non-Current Liabilities (2) 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Total Liabilities 41.5% 55.3% 54.6% 39.0% 4.0% 37.5%
Minority Interest 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Preferred Stock 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Common Stock + Paid in Capital -0.2% -86.9% -69.2% -0.0% -0.1% -47.4%
Retained Earnings <Deficit> 35.1% 34.2% 10.9% 1.8% 9.7% 17.6%
Accum. Other Comprehensive Income <Loss> 105.2% -28.5% 78.2% 46.5% -11.4% 27.7%
Other Equity Adjustments 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
<Treasury Stock> 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Common Shareholders' Equity 18.6% -15.4% 6.6% 2.5% 9.1% 3.6%
Total Liabilities and Equities 24.0% 3.8% 26.0% 20.7% 6.2% 15.7%
RETURN ON ASSETS ANALYSIS (excluding the effects of non-recurring items) Decomposition of ROA and ROCE:
This schematic provides a decomposition of ROA and ROCE into component ratios that determine ROA and ROCE.
Level 1 RETURN ON ASSETS
2014 2015 2016
14.8% 14.3% 0.0%
Level 2 PROFIT MARGIN FOR ROA ASSET TURNOVER
2014 2015 2016 2014 2015 2016
7.5% 7.4% 0.0% 2.0 1.9 0.0
Level 3 2014 2015 2016 2014 2015 2016 Turnovers: Level 3 component ratios provide more detail about components of income that affect the profit margin for ROA as well as turnover ratios for specific assets.
Revenues 100.0% 100.0% 100.0% 5.8 5.5 0.0 Receivables
<Cost of Sales and Occupancy> -40.8% -42.5% -44.7% 5.4 6.0 0.0 Inventory
Gross Profit 59.2% 57.5% 55.3% 3.8 3.6 0.0 Fixed Assets
<Store Operating Expenses> -34.5% -34.2% -36.1%
Operating Profit 11.5% 11.2% 4.9%
Income before Tax 11.6% 11.2% 4.4%
<Income Tax Expense> -4.2% -4.1% -1.4%
Profit Margin for ROA* 7.5% 7.4% 0.0%
*Amounts do not sum.
RETURN ON COMMON SHAREHOLDERS' EQUITY ANALYSIS (excluding the effects of non-recurring items)
RETURN ON COMMON SHAREHOLDERS' EQUITY
2014 2015 2016
26.9% 29.8% 0.0%
2014 2015 2016
PROFIT MARGIN FOR ROCE 7.5% 7.1% 0.0%
ASSET TURNOVER 2.0 1.9 - 0
CAPITAL STRUCTURE LEVERAGE 1.8 2.2 0.0
STATEMENT OF CASH FLOWS: SUMMARY Summary Statement of Cash Flows:
Year 2012 2013 2014 2015 2016 The Summary Statement of Cash Flows provides an aggregated summation of the major sources of cash inflows and outlfows.
While the Statement of Cash Flows provides useful detail on specific cash inflows and outflows, this aggegation provide a high-level summary of major categories of cash being generated and used. This aggregation reveals quickly how cash is being generated and how cash is being used.
Operating Activities:
Net Income 388 494 564 673 316
Add back Depreciation and Amortization Expenses 305 367 413 491 605
Net Cash Flows for Working Capital 150 55 169 117 329
Other Net Addbacks/Subtractions -53 7 -14 50 9
Net CF from Operations 790 923 1,132 1,331 1,259
Investing Activities:
Capital Expenditures (net) -386 -644 -771 -1,080 -985
Investments -284 423 -70 -122 -102
Other Investing Transactions 38 0 0 0 0
Net CF from Investing Activities -632 -221 -841 -1,202 -1,087
Financing Activities:
Net Proceeds from Short-Term Borrowing 0 277 423 10 2
Net Proceeds from Long-Term Borrowing -1 -1 -1 548 -1
Net Proceeds from Share Issues and Repurchases -66 -950 -577 -727 -184
Dividends 0 0 0 0 0
Other Financing Transactions 0 0 0 -4 -2
Net CF from Financing Activities -67 -674 -155 -172 -185
Effects of exchange rate changes on cash 3 0 4 11 1
Net Change in Cash 95 29 139 -31 -12

Forecasts

Financial Statement Analysis Package (FSAP): Version 7.0
Financial Reporting, Financial Statement Analysis, and Valuation: A Strategic Perspective, 7th Edition FSAP User Guides:
By James Wahlen, Steve Baginski, and Mark Bradshaw
The FSAP User Guides appear in column M to the right. A Comment on Entering Forecast Assumptions:
This worksheet allows the FSAP user to build forecasts of future income statements, balance sheets, and statements of cash flows. FSAP automatically enters data from the DATA spreadsheet for the most recent three years in columns B, C, and D. This worksheet allows the user flexibility to compute forecast amounts for each income statement and balance sheet account. For each account, the user should enter the forecast computations in the first row (highlighted in green with bold blue font). In the second row for each account, the user can enter forecast assumption parameters (such as growth rates or percentages) to be used in the forecast computations. In the third row for each account, the user can enter a brief explanatory note to explain the forecast assumptions. The FSAP user can also develop more detailed computations of forecast amounts in the Forecast Development worksheet, and incorporate those forecast amounts by referencing them here in the Forecasts worksheet.
FSAP OUTPUT: FINANCIAL STATEMENT FORECASTS
Analyst Name: Tareq Hamdan
Company Name: Proctor & Gamble
Row Format: Row Format:
Actual Amounts Forecast Amounts Year +6 and beyond: A Comment on Forecasts for Year +6 and Beyond:
Common Size Percentage Forecast assumption Long-Run Growth Rate: 3.0% For long-run forecast amounts, the FSAP user needs to enter a long-run growth rate assumption. FSAP will automatically use that growth rate to compute forecast amounts for all of the accounts for Year +6 and beyond. The FSAP user should not alter the specific forecast computations for any of the accounts for Year +6 and beyond. The FSAP user must be sure to enter the same long run growth rate assumption as a valuation parameter in the Valuation spreadsheet.
Rate of Change Percentage Forecast assumption explanation Long-Run Growth Factor: 103.0%
Actuals Forecasts A Comment on Entering Forecast Assumptions:
Year 2014 2015 2016 Year +1 Year +2 Year +3 Year +4 Year +5 Year +6 This worksheet allows the FSAP user to build forecasts of future income statements, balance sheets, and statements of cash flows. FSAP automatically enters data from the DATA spreadsheet for the most recent three years in columns B, C, and D. This worksheet allows the user flexibility to compute forecast amounts for each income statement and balance sheet account. For each account, the user should enter the forecast computations in the first row (highlighted in green with bold blue font). In the second row for each account, the user can enter forecast assumption parameters (such as growth rates or percentages) to be used in the forecast computations. In the third row for each account, the user can enter a brief explanatory note to explain the forecast assumptions. The FSAP user can also develop more detailed computations of forecast amounts in the Forecast Development worksheet, and incorporate those forecast amounts by referencing them here in the Forecasts worksheet.
INCOME STATEMENT
Revenues 7,787 9,411 10,383 0 0 0 0 0 0 Income Statement Default Assumptions:
common size 100.0% 100.0% 100.0% 0.0% 0.0% 0.0% 0.0% 0.0% The FSAP Forecasts worksheet defaults to assume that each income statement line item will remain the same percent of total revenues as in the most current year. The FSAP user must either accept this default as a reasonable expectation or override it and enter more reasonable forecast assumptions.
rate of change 20.9% 10.3% See Forecast Development worksheet for details.
<Cost of Sales and Occupancy> -3,179 -3,999 -4,645 0 0 0 0 0 0
common size -40.8% -42.5% -44.7% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 25.8% 16.2% Explain assumptions.
Gross Profit 4,608 5,412 5,738 0 0 0 0 0 0
common size 59.2% 57.5% 55.3% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 17.5% 6.0% -100.0% 0.0% 0.0% 0.0% 0.0% 0.0%
<Store Operating Expenses> -2,688 -3,216 -3,745 0 0 0 0 0 0
common size -34.5% -34.2% -36.1% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 19.6% 16.5% Explain assumptions.
<Other Operating Expenses> -254 -294 -330 0 0 0 0 0 0
common size -3.3% -3.1% -3.2% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 16.0% 12.2% Explain assumptions.
<Depreciation and Amortization> -387 -467 -549 0 0 0 0 0 0
common size -5.0% -5.0% -5.3%
rate of change 20.7% 17.6% See depreciation schedule in Forecast Development.
<General and Administrative Expenses> -479 -489 -456 0 0 0 0 0 0
common size -6.2% -5.2% -4.4% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 2.0% -6.8% Explain assumptions.
<Restructuring Charges> 0 0 -267 0 0 0 0 0 0
common size 0.0% 0.0% -2.6% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 0.0% 0.0% Explain assumptions.
Income from equity investees 94 108 114 0 0 0 0 0 0
common size 1.2% 1.1% 1.1% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 15.0% 5.2% Explain assumptions.
Other Operating Income (2) 0 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 0.0% 0.0% Explain assumptions.
Non-Recurring Operating Gains 0 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 0.0% 0.0% Explain assumptions
<Non-Recurring Operating Losses> 0 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 0.0% 0.0% Explain assumptions
Operating Profit 894 1,054 504 0 0 0 0 0 0
common size 11.5% 11.2% 4.9% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 17.9% -52.2% -100.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Interest Income 21 41 9 0 0 0 0 0 0
common size 0.3% 0.4% 0.1% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 96.1% -77.8% Interest rate earned on average balance in cash, ST and LT securities.
<Interest Expense> -8 -38 -53 0 0 0 0 0 0
common size -0.1% -0.4% -0.5% 0.00% 0.00% 0.00% 0.00% 0.00%
rate of change 354.8% 39.8% Interest rate on average balance in long-term debt.
Income <Loss> from Equity Affiliates 0 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 0.0% 0.0% Explain assumptions.
Other Income or Gains 0 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 0.0% 0.0% Explain assumptions
<Other Expenses or Losses> 0 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 0.0% 0.0% Explain assumptions
Income before Tax 906 1,056 460 0 0 0 0 0 0
common size 11.6% 11.2% 4.4% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 16.6% -56.5% -100.0% 0.0% 0.0% 0.0% 0.0% 0.0%
<Income Tax Expense> -325 -384 -144 0 0 0 0 0 0
common size -4.2% -4.1% -1.4% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 18.2% -62.5% Effective income tax rate assumptions.
<Minority Interest in Earnings> 0 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 0.0% 0.0% Explain assumptions
Income <Loss> from Discontinued Operations 0 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 0.0% 0.0% Explain assumptions
Extraordinary Gains <Losses> 0 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 0.0% 0.0% Explain assumptions
Changes in Acctg. Principles -17 0 0 0 0 0 0 0 0
common size -0.2% 0.0% 0.0% 0.0 0.0 0.0 0.0 0.0
rate of change -100.0% 0.0% Explain assumptions
Net Income (computed) 564 673 316 0 0 0 0 0 0
common size 7.2% 7.1% 3.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 19.2% -53.1% -100.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Other Comprehensive Income Items 16 17 -6 0 0 0 0 0 0
0.2% 0.2% -0.1% 0.0 0.0 0.0 0.0 0.0
5.5% -135.8% Explain assumptions
Comprehensive Income 581 690 309 0 0 0 0 0 0
common size 7.5% 7.3% 3.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 18.8% -55.2% -100.0% 0.0% 0.0% 0.0% 0.0% 0.0%
FSAP OUTPUT: FINANCIAL STATEMENT FORECASTS
Analyst Name: Tareq Hamdan
Company Name: Proctor & Gamble
Row Format: Row Format:
Actual Amounts Forecast Amounts Year +6 and beyond:
Common Size Percent Forecast assumption Long-Run Growth Rate: 3.0%
Rate of Change Percent Forecast assumption explanation Long-Run Growth Factor: 103.0%
Actuals Forecasts A Comment on Entering Forecast Assumptions:
2014 2015 2016 Year +1 Year +2 Year +3 Year +4 Year +5 Year +6 This worksheet allows the FSAP user to build forecasts of future income statements, balance sheets, and statements of cash flows. FSAP automatically enters data from the DATA spreadsheet for the most recent three years in columns B, C, and D. This worksheet allows the user flexibility to compute forecast amounts for each income statement and balance sheet account. For each account, the user should enter the forecast computations in the first row (highlighted in green with bold blue font). In the second row for each account, the user can enter forecast assumption parameters (such as growth rates or percentages) to be used in the forecast computations. In the third row for each account, the user can enter a brief explanatory note to explain the forecast assumptions. The FSAP user can also develop more detailed computations of forecast amounts in the Forecast Development worksheet, and incorporate those forecast amounts by referencing them here in the Forecasts worksheet.
BALANCE SHEET
ASSETS:
Cash and Cash Equivalents 313 281 270 0 0 0 0 0 0 Balance Sheet Default Assumptions:
common size 7.1% 5.3% 4.8% 0.0 0.0 0.0 0.0 0.0 The FSAP Forecasts worksheet defaults to assume that each balance sheet item (except retained earnngs) will remain the same percent of total assets as in the most current year. The FSAP user must either accept this default as a reasonable expectation or override it and enter more reasonable forecast assumptions.
rate of change -10.0% -4.1% Explain assumptions
Marketable Securities 141 157 53 0 0 0 0 0 0
common size 3.2% 2.9% 0.9% 0.0 0.0 0.0 0.0 0.0
rate of change 11.6% -66.7% Explain assumptions
Accounts Receivable - Net 224 288 330 0 0 0 0 0 0
common size 5.1% 5.4% 5.8% 0.0 0.0 0.0 0.0 0.0
rate of change 28.4% 14.4% Explain assumptions
Inventories 636 692 693 0 0 0 0 0 0
common size 14.4% 12.9% 12.2% 0.0 0.0 0.0 0.0 0.0
rate of change 8.7% 0.2% Explain assumptions
Prepaid Expenses and Other Current Assets 127 149 169 0 0 0 0 0 0
common size 2.9% 2.8% 3.0% 0.0 0.0 0.0 0.0 0.0
rate of change 17.2% 13.7% Explain assumptions
Deferred Tax Assets - Current 89 129 234 0 0 0 0 0 0
common size 2.0% 2.4% 4.1% 0.0 0.0 0.0 0.0 0.0
rate of change 45.8% 80.9% Explain assumptions
Other Current Assets (1) 0 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 0.0% 0.0% Explain assumptions
Other Current Assets (2) 0 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 0.0% 0.0% Explain assumptions
Current Assets 1,530 1,696 1,748 0 0 0 0 0 0
common size 34.5% 31.7% 30.8% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 10.9% 3.0% -100.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Long Term Investments 6 21 71 0 0 0 0 0 0
common size 0.1% 0.4% 1.3% 0.0 0.0 0.0 0.0 0.0
rate of change 261.8% 239.6% Explain assumptions
Property, Plant & Equipment - at cost 4,258 5,307 5,717 0 0 0 0 0 0
common size 96.1% 99.3% 100.8%
rate of change 24.6% 7.7% PP&E assumptions - see schedule in forecast development
<Accumulated Depreciation> -1,970 -2,416 -2,761 0 0 0 0 0 0
common size -44.5% -45.2% -48.7%
rate of change 22.7% 14.3% See depreciation schedule in forecast development worksheet.
Amortizable Intangible Assets (net) 38 42 67 0 0 0 0 0 0
common size 0.9% 0.8% 1.2% 0.0 0.0 0.0 0.0 0.0
rate of change 10.8% 58.4% Explain assumptions
Goodwill and Nonamortizable Intangibles 161 216 267 0 0 0 0 0 0
common size 3.6% 4.0% 4.7% 0.0 0.0 0.0 0.0 0.0
rate of change 33.5% 23.6% Explain assumptions
Deferred Tax Assets - Noncurrent 0 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 0.0% 0.0% Explain assumptions
Equity and Other Investments 219 259 303 0 0 0 0 0 0
common size 4.9% 4.8% 5.3% 0.0 0.0 0.0 0.0 0.0
rate of change 18.1% 16.9% Explain assumptions
Other Non-Current Assets (2) 187 219 261 0 0 0 0 0 0
common size 4.2% 4.1% 4.6% 0.0 0.0 0.0 0.0 0.0
rate of change 17.4% 19.0% Explain assumptions
Total Assets 4,429 5,344 5,673 0 0 0 0 0 0
common size 100.0% 100.0% 100.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 20.7% 6.2% -100.0% 0.0% 0.0% 0.0% 0.0% 0.0%
LIABILITIES:
Accounts Payable - Trade 341 391 325 0 0 0 0 0 0
common size 7.7% 7.3% 5.7% 0.0 0.0 0.0 0.0 0.0
rate of change 14.6% -16.9% Explain assumptions
Current Accrued Liabilities 662 757 783 0 0 0 0 0 0
common size 14.9% 14.2% 13.8% 0.0 0.0 0.0 0.0 0.0
rate of change 14.3% 3.4% Explain assumptions
Notes Payable and Short Term Debt 700 710 713 0 0 0 0 0 0
common size 15.8% 13.3% 12.6% 0.0 0.0 0.0 0.0 0.0
rate of change 1.5% 0.4% Explain assumptions
Current Maturities of Long Term Debt 1 1 1 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 1.7% -9.7% Explain assumptions
Deferred Tax Liabilities - Current 0 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 0.0% 0.0% Explain assumptions
Income Taxes Payable 0 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 0.0% 0.0% Explain assumptions.
Deferred Revenue 232 297 368 0 0 0 0 0 0
common size 5.2% 5.6% 6.5% 0.0 0.0 0.0 0.0 0.0
rate of change 28.0% 24.1% Explain assumptions
Other Current Liabilities (2) 0 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 0.0% 0.0% Explain assumptions.
Current Liabilities 1,936 2,156 2,190 0 0 0 0 0 0
common size 43.7% 40.3% 38.6% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 11.4% 1.6% -100.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Long Term Debt 2 550 550 0 0 0 0 0 0
common size 0.0% 10.3% 9.7% 0.0 0.0 0.0 0.0 0.0
rate of change 27996.1% -0.1% Explain assumptions
Long Term Accrued Liabilities 263 354 442 0 0 0 0 0 0
common size 5.9% 6.6% 7.8% 0.0 0.0 0.0 0.0 0.0
rate of change 34.6% 24.9% Explain assumptions
Deferred Tax Liabilities - Noncurrent 0 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 0.0% 0.0% Explain assumptions
Other Non-Current Liabilities (1) 0 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 0.0% 0.0% Explain assumptions
Other Non-Current Liabilities (2) 0 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 0.0% 0.0% Explain assumptions
Total Liabilities 2,201 3,060 3,182 0 0 0 0 0 0
common size 49.7% 57.3% 56.1% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 39.0% 4.0% -100.0% 0.0% 0.0% 0.0% 0.0% 0.0%
SHAREHOLDERS' EQUITY
Minority Interest 0 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 0.0% 0.0% Minority interest assumptions
Preferred Stock 0 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 0.0% 0.0% Preferred stock assumptions
Common Stock + Paid in Capital 40 40 40 0 0 0 0 0 0
common size 0.9% 0.8% 0.7% 0.0 0.0 0.0 0.0 0.0
rate of change -0.0% -0.1% Explain assumptions
Retained Earnings <Deficit> 2,151 2,189 2,402 0 0 0 0 0 0
common size 48.6% 41.0% 42.4% 0.0 0.0 0.0 0.0 0.0
rate of change 1.8% 9.7% Explain assumptions
Accum. Other Comprehensive Income <Loss> 37 55 48 0 0 0 0 0 0
common size 0.8% 1.0% 0.9% 0.0 0.0 0.0 0.0 0.0
rate of change 46.5% -11.4% Add accumulated other comprehensive income items from income statement
Other Equity Adjustments 0 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 0.0% 0.0% Explain assumptions
<Treasury Stock> 0 0 0 0 0 0 0 0 0
common size 0.0% 0.0% 0.0% 0 0 0 0 0
rate of change 0.0% 0.0% Treasury stock repurchases, net of treasury stock reissues.
Common Shareholders' Equity 2,229 2,284 2,491 0 0 0 0 0 0
common size 50.3% 42.7% 43.9% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 2.5% 9.1% -100.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Total Liabilities and Equities 4,429 5,344 5,673 0 0 0 0 0 0
common size 100.0% 100.0% 100.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
rate of change 20.7% 6.2% -100.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Check figures: Balance Sheet A=L+OE? -0 -0 0 0 0 0 0 0 0 These check figures should be zero, indicating the total assets on the balance sheet balances with the total liabilities and shareholders' equity. If a check figure is not zero, it indicates one or more errors either on the balance sheet or in the plug figure used to balance the balance sheet. The user must find and correct any errors.
Initial adjustment needed to balance the balance sheet:
-2,402 0 0 0 0 0 A Comment on Balancing the Balance Sheet:
The Forecasts spreadsheet is programmed to balance the balance sheet by adjusting dividends (see dividends forecast box below). The user can alter this assumption by reprogamming the computations to adjust an alternate flexible financial account.
Account adjusted: Dividends The initial adjustment to balance the balance sheet is computed as total assets minus total liabilities and shareholders equity before any adjustments. The initial adjustment amount is then used to adjust dividends so that total assets equal total liabilities and equities.
Dividends forecasts:
Common Dividends: 0 0 0 0 0 0
0.0% 0.0% 0.0% 0.0% 0.0%
Assume zero dividend payout.
Preferred Dividends: 0 0 0 0 0 0
0.0 0.0 0.0 0.0 0.0
Enter preferred stock dividend payments, if any.
Implied Dividends: 2,402 0 0 0 0 0
Implied dividend amount to balance the balance sheet.
Total Dividends: 2,402 0 0 0 0 0
Total dividend forecast amounts.
FSAP OUTPUT: FINANCIAL STATEMENT FORECASTS
Analyst Name: Tareq Hamdan
Company Name: Proctor & Gamble
Actuals Forecasts Implied Statements of Cash Flows:
IMPLIED STATEMENT OF CASH FLOWS 2015 2016 Year +1 Year +2 Year +3 Year +4 Year +5 Year +6 The following implied statements of cash flows are derived from the above income statements and balance sheets. They are not the reported statements of cash flows.
Net Income 673 316 0 0 0 0 0 0 (1)
Add back Depreciation Expense (net) 446 345 -2,761 0 0 0 0 0 (2) This row approximates depreciation expense using the change in accumulated depreciation.
<Increase> Decrease in Receivables - Net -64 -42 330 0 0 0 0 0 (3)
<Increase> Decrease in Inventories -55 -1 693 0 0 0 0 0 (4)
<Increase> Decrease in Prepaid Expenses -22 -20 169 0 0 0 0 0 (5)
<Increase> Decrease in Other Curr. Assets (1) 0 0 0 0 0 0 0 0 (6)
<Increase> Decrease in Other Curr. Assets (2) 0 0 0 0 0 0 0 0 (7)
Increase <Decrease> in Accounts Payable - Trade 50 -66 -325 0 0 0 0 0 (8)
Increase <Decrease> in Current Accrued Liabilities 95 26 -783 0 0 0 0 0 (9)
Increase <Decrease> in Income Taxes Payable 0 0 0 0 0 0 0 0 (10)
Increase <Decrease> in Other Current Liabilities (1) 65 72 -368 0 0 0 0 0 (11)
Increase <Decrease> in Other Current Liabilities (2) 0 0 0 0 0 0 0 0 (12)
Net Change in Deferred Tax Assets and Liabilities -41 -105 234 0 0 0 0 0 (13)
Increase <Decrease> in Long-Term Accrued Liabilities 91 88 -442 0 0 0 0 0 (14)
Increase <Decrease> in Other Non-Current Liabilities (1) 0 0 0 0 0 0 0 0 (15)
Increase <Decrease> in Other Non-Current Liabilities (2) 0 0 0 0 0 0 0 0 (16)
Net Cash Flows from Operations 1,238 612 -3,254 0 0 0 0 0 (17)
<Increase> Decrease in Prop., Plant, & Equip. at cost -1,049 -411 5,717 0 0 0 0 0 (18)
<Increase> Decrease in Marketable Securities -16 105 53 0 0 0 0 0 (19)
<Increase> Decrease in Investment Securities -15 -50 71 0 0 0 0 0 (20)
<Increase> Decrease in Amortizable Intangible Assets (net) -4 -25 67 0 0 0 0 0 (21)
<Increase> Decrease in Goodwill and Nonamort. Intang. -54 -51 267 0 0 0 0 0 (22)
<Increase> Decrease in Equity Method Affiliates -40 -44 303 0 0 0 0 0 (23)
<Increase> Decrease in Other Non-Current Assets (2) -33 -42 261 0 0 0 0 0 (24)
Net Cash Flows from Investing -1,211 -517 6,738 0 0 0 0 0 (25)
Increase <Decrease> in Short-Term Debt 10 3 -714 0 0 0 0 0 (26)
Increase <Decrease> in Long-Term Debt 548 -1 -550 0 0 0 0 0 (27)
Increase <Decrease> in Minority Interest and Preferred Stock 0 0 0 0 0 0 0 0 (28)
Increase <Decrease> in Common Stock + Paid in Capital -0 -0 -40 0 0 0 0 0 (29)
Increase <Decrease> in Accum. OCI and Other Equity Adjs. 17 -6 -48 0 0 0 0 0 (30)
Increase <Decrease> in Treasury Stock 0 0 0 0 0 0 0 0 (31)
Dividends -634 -102 -2,402 0 0 0 0 0 (32)
Net Cash Flows from Financing -59 -107 -3,754 0 0 0 0 0 (33)
Net Change in Cash -31 -11 -270 0 0 0 0 0 (34)
Check Figure:
Net change in cash - Change in cash balance -0 -0 0 0 0 0 0 0 These check figures should be zero, indicating the net change in cash on the statement of cash flows agrees with the change in cash on the balance sheet. If a check figure is not zero, it indicates one or more errors either in the balance sheet or the statement of cash flows. The user must find and correct any errors.
FSAP OUTPUT: FINANCIAL STATEMENT FORECASTS
Analyst Name: Tareq Hamdan
Company Name: Proctor & Gamble
Actuals Forecasts
2014 2015 2016 Year +1 Year +2 Year +3 Year +4 Year +5 Year +6
FORECAST VALIDITY CHECK DATA: Forecast Validity Checks:
GROWTH FSAP computes these ratios using the forecast amounts above. The FSAP user can evaluate these ratios to assess whether forecast assumptions are reasonable or not.
Revenue Growth Rates: 22.3% 20.9% 10.3% -100.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Net Income Growth Rates: 14.2% 19.2% -53.1% -100.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Total Asset Growth Rates 26.0% 20.7% 6.2% -100.0% 0.0% 0.0% 0.0% 0.0% 0.0%
RETURN ON ASSETS (based on reported amounts):
Profit Margin for ROA 7.3% 7.4% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
x Asset Turnover 2.0 1.9 0.0 0.0 0.0 0.0 0.0 0.0 0.0
= Return on Assets 14.3% 14.3% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
RETURN ON ASSETS (excluding the effects of nonrecurring items):
Profit Margin for ROA 7.5% 7.4% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
x Asset Turnover 2.0 1.9 0.0 0.0 0.0 0.0 0.0 0.0 0.0
= Return on Assets 14.8% 14.3% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
RETURN ON COMMON EQUITY (based on reported amounts):
Profit Margin for ROCE 7.2% 7.1% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
x Asset Turnover 2.0 1.9 0.0 0.0 0.0 0.0 0.0 0.0 0.0
x Capital Structure Leverage 1.8 2.2 0.0 2.3 0.0 0.0 0.0 0.0 0.0
= Return on Common Equity 26.1% 29.8% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
RETURN ON COMMON EQUITY (excluding the effects of nonrecurring items):
Profit Margin for ROCE 7.5% 7.1% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
x Asset Turnover 2.0 1.9 0.0 0.0 0.0 0.0 0.0 0.0 0.0
x Capital Structure Leverage 1.8 2.2 0.0 2.3 0.0 0.0 0.0 0.0 0.0
= Return on Common Equity 26.9% 29.8% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
OPERATING PERFORMANCE:
Gross Profit / Revenues 59.2% 57.5% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Operating Profit Before Taxes / Revenues 11.5% 11.2% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
ASSET TURNOVER:
Revenues / Avg. Accounts Receivable 5.8 5.5 0.0 0.0 0.0 0.0 0.0 0.0 0.0
COGS / Average Inventory 5.4 6.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Revenues / Average Fixed Assets 3.8 3.6 0.0 0.0 0.0 0.0 0.0 0.0 0.0
LIQUIDITY:
Current Ratio 0.8 0.8 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Quick Ratio 0.4 0.3 0.0 0.0 0.0 0.0 0.0 0.0 0.0
SOLVENCY:
Total Liabilities / Total Assets 49.7% 57.3% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Total Liabilities / Total Equity 98.7% 134.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
Interest Coverage Ratio 106.8 28.7 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Accounts Receivable Turnover
Inventory Turnover
Fixed Asset Turnover

Forecast Development

Financial Statement Analysis Package (FSAP): Version 7.0
Financial Reporting, Financial Statement Analysis, and Valuation: A Strategic Perspective, 7th Edition FSAP User Guides:
By James Wahlen, Steve Baginski and Mark Bradshaw
The FSAP User Guides appear in column M to the right. Forecast Development:
This Forecast Development spreadsheet provides work space in which the analyst can:
- build detailed sales revenue forecasts
- build forecasts of capital expenditures, property, plant and equipment, depreciation expense, and accumulated depreciation.
- build detailed forecasts of other financial statement amounts.
Analyst Name: Tareq Hamdan
Company Name: Proctor & Gamble
It is not necessary to use this spreadsheet to build financial statement forecasts in the FSAP Forecasts
spreadsheet. If you use this spreadsheet to build more detailed forecasts, the you will need to link these
forecast amounts to the appropriate cells in the financial statements in the FSAP Forecasts spreadsheet.
Sales Revenue Forecast Development
Actuals Forecasts
Year 2005 2014 2015 2016 Year +1 Year +2 Year +3 Year +4 Year +5
This sales revenue forecast development schedule was developed specifically for PepsiCo. FSAP users who wish to develop detailed sales forecasts of other firms should adapt this schedule as necessary to incorporate the other firms' various soruces of revenue.
(input your forecast assumptions in the blue font cells)
Sales (in millions):
Retail 5,391.9 6,583.1 7,998.3 8,771.9 9,122.2 9,122.2 9,122.2 9,122.2 9,122.2
Specialty 977.4 1,203.8 1,413.2 1,611.1 1,662.8 1,662.8 1,662.8 1,662.8 1,662.8
Net Revenues 6,369.3 7,786.9 9,411.5 10,383.0 10,785.0 10,785.0 10,785.0 10,785.0 10,785.0 Note: Sales figures here simply assume no growth in
Implied sales growth forecasts: stores or same store sales. User must enter forecasts for
Sales growth rates: 20.3% 22.3% 20.9% 10.3% 3.9% 0.0% 0.0% 0.0% 0.0% these amounts in schedules below.
Sales by Segment and Type: Starbucks: Sales Forecasts by Segment and Type
2005 2006 2007 2008 Year +1 Year +2 Year +3 Year +4 Year +5
Retail
U.S. 4,539.5 5,495.2 6,560.9 6,997.7 7,219.6 7,219.6 7,219.6 7,219.6 7,219.6
International 852.5 1,087.9 1,437.4 1,774.2 1,902.5 1,902.5 1,902.5 1,902.5 1,902.5
Specialty
U.S. Licensed 278.0 369.2 439.2 504.2 531.1 531.1 531.1 531.1 531.1
Int'l. Licensed 145.7 186.1 220.8 274.8 299.6 299.6 299.6 299.6 299.6
Foodservice & CPG 553.6 648.6 753.2 832.1 832.1 832.1 832.1 832.1 832.1
Net Revenues 6,369.3 7,786.9 9,411.5 10,383.0 10,785.0 10,785.0 10,785.0 10,785.0 10,785.0
Sales per Average Store-Year by Segment and Type: Expectations for comparable store sales growth rates by type:
2005 2006 2007 2008 Year +1 Year +2 Year +3 Year +4 Year +5
Retail
U.S. 0.981 1.032 1.048 0.997 0.997 0.997 0.997 0.997 0.997
growth 5.2% 1.5% -4.8% 0% 0% 0% 0% 0%
0.6%
International 0.782 0.825 0.914 0.961 0.961 0.961 0.961 0.961 0.961
growth 5.5% 10.7% 5.2% 0% 0% 0% 0% 0%
7.1%
Specialty
U.S. Licensed - 0 0.132 0.124 0.123 0.123 0.123 0.123 0.123 0.123
growth -5.6% -1.4% 0% 0% 0% 0% 0%
Int'l. Licensed - 0 0.098 0.093 0.096 0.096 0.096 0.096 0.096 0.096
growth -4.6% 2.3% 0% 0% 0% 0% 0%
Foodservice & CPG 553.6 648.6 753.2 832.1 832.1 832.1 832.1 832.1 832.1
growth 17.2% 16.1% 10.5% 0% 0% 0% 0% 0%
14.5%
Starbucks: Store Operating Data: Forecasts: Numbers of Stores
2005 2006 2007 2008 Year +1 Year +2 Year +3 Year +4 Year +5
Percentage change in comparable store sales:
U.S. 9% 7% 4% -5%
International 6% 8% 7% 2%
Consolidated 8% 7% 5% -3%
New stores opened during the year: Expectations for net new stores to be opened each year:
openings: 0 0 0 0 0
U.S. (closings): 0 0 0 0 0
Company-operated: 580 810 1,065 445 0 0 0 0 0
Licensed: 596 733 723 438 0 0 0 0 0
International
openings: 0 0 0 0 0
(closings): 0 0 0 0 0
Company-operated: 166 233 277 236 0 0 0 0 0
Licensed: 330 423 506 550 0 0 0 0 0
Totals
Company-operated: 746 1,043 1,342 681 - - - - -
Licensed: 926 1,156 1,229 988 - - - - -
Total stores opened 1,672 2,199 2,571 1,669 - - - - -
Stores open at year end: Implied Expectations for Total Stores to be Open:
U.S.
Company-operated: 4,918 5,728 6,793 7,238 7,238 7,238 7,238 7,238 7,238
Licensed: 2,435 3,168 3,891 4,329 4,329 4,329 4,329 4,329 4,329
International
Company-operated: 1,202 1,435 1,712 1,979 1,979 1,979 1,979 1,979 1,979
Licensed: 1,686 2,109 2,615 3,134 3,134 3,134 3,134 3,134 3,134
Totals
Company-operated: 6,120 7,163 8,505 9,217 9,217 9,217 9,217 9,217 9,217
Licensed: 4,121 5,277 6,506 7,463 7,463 7,463 7,463 7,463 7,463
Total stores open: 10,241 12,440 15,011 16,680 16,680 16,680 16,680 16,680 16,680
Implied store growth rate forecasts:
Store growth rates: 19.5% 21.5% 20.7% 11.1% 0.0% 0.0% 0.0% 0.0% 0.0%
Starbucks: Operating Expense Data: Forecasts: Operating Expenses
2005 2006 2007 2008 Year +1 Year +2 Year +3 Year +4 Year +5
Store Operating Expenses -2,166 -2,688 -3,216 -3,745 0 0 0 0 0
As a percent of retail revenues -40.2% -40.8% -40.2% -42.7% 0.0% 0.0% 0.0% 0.0% 0.0%
Other Operating Expenses -193 -254 -294 -330 0 0 0 0 0
As a percent of specialty revenues -19.7% -21.1% -20.8% -20.5% 0.0% 0.0% 0.0% 0.0% 0.0%
Forecast Development: Capital Expenditures, Property, Plant and Equipment, and Depreciation
Capital Expenditures: CAPEX Forecasts: The Capital Expenditures schedule permits the FSAP user to build detailed forecasts of future capital expenditures as a percent of future revenues, gross PP&E or any other reasonable basis for these forecast assumptions.
2005 2014 2015 2016 Year +1 Year +2 Year +3 Year +4 Year +5
CAPEX:
PP&E Acquired (net) 644 771 1,080 985
PP&E Sold 0 0 0 0
Net CAPEX 644 771 1,080 985 0 0 0 0 0
Number of new stores opened 746 1,043 1,342 681 - - - - -
Average CAPEX per new store $ 0.863 $ 0.739 $ 0.805 $ 1.446
Four-year average CAPEX per new store $ 0.963
Expected CAPEX per new store $ 0.992 $ 1.022 $ 1.053 $ 1.084 $ 1.117
CAPEX Inflation Forecast: 3% 3% 3% 3% 3%
Total CAPEX for New Stores $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
Additional CAPEX for refurbishments $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
Total CAPEX $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
Property, Plant and Equipment and Depreciation Property, Plant and Equipment and Depreciation Forecasts: The Property, Plant & Equipment and Depreciation schedule automatically computes for the FSAP detailed forecasts of future PP&E based on exisitng PP&E plus projected future capital expenditures. The Depreciation expense schedule automatically computes future depreciation expense based on exisitng depreciable PP&E future capital expenditures. The expected useful life for depreciation purposes is computed below.
FSAP automatically links the projected amounts for gross PP&E, accumulated depreciation, depreciation expense, and capital expenditures into the financial statements in the Forecasts worksheet.
PP&E at cost: 2005 2014 2015 2016 Year +1 Year +2 Year +3 Year +4 Year +5
Beg. balance at cost: 5,717 5,717 5,717 5,717 5,717
Add: CAPEX forecasts from above: 0 0 0 0 0
End balance at cost: 3,468 4,258 5,307 5,717 5,717 5,717 5,717 5,717 5,717
Accumulated Depreciation:
Beg. Balance: -2,761 -3,331 -3,900 -4,470 -5,040
Subtract: Depreciation expense forecasts from below: -570 -570 -570 -570 -570
End Balance: -1,626 -1,970 -2,416 -2,761 -3,331 -3,900 -4,470 -5,040 -5,610
PP&E - net 1,842 2,288 2,890 2,956 2,387 1,817 1,247 677 108
Depreciation Expense Forecast Development: Depreciation expense forecast on existing PP&E:
Existing PP&E at cost: 5,717 570 570 570 570 570 This computation shows depreciation expense based on the exisiting depreciable PP&E at the start of the forecast period. The computation assumes straight line depreciation methods, zero salvage value, and the estimated useful life computed below. .
Remaining balance to be depreciated. 2,956 2,387 1,817 1,247 677 108 This computation shows the amount of gross PP&E still to be depreciated. Once this amount falls to zero, depreciation is complete.The FSAP user should be sure that these amounts are not negative.
PP&E Purchases: Depreciation expense forecasts on new PP&E:
Capex Year +1 0 0 0 0 0 0 FSAP automatically computes a new depreciation schedule for each year's capital expenditures, which are included in PP&E. These computations assume straight line depreciation methods and zero salvage value. The computations use the extimated useful life as computed below.
Capex Year +2 0 0 0 0 0
Capex Year +3 0 0 0 0
Capex Year +4 0 0 0
Capex Year +5 0 0
Total Depreciation Expense 570 570 570 570 570
Depreciation methods: 2005 2014 2015 2016
PPE at Cost 3,468 4,258 5,307 5,717 FSAP automatically estimates the estimated useful life for depreciation purposes by dividing the average amount of gross depreciable PP&E by depreciation expense. This estimate assume straight line depreciation and zero salvage value.
Avg Depreciable PPE 3,863 4,782 5,512
Depreciation Expense 340 387 467 549
Implied Avg. Useful Life in Years 10.0 10.2 10.0
Useful Life Forecast Assumption: 10.0
(in years)

Valuation

Financial Statement Analysis Package (FSAP): Version 7.0
Financial Reporting, Financial Statement Analysis, and Valuation: A Strategic Perspective, 7th Edition FSAP User Guides:
By James Wahlen, Steve Baginski and Mark Bradshaw
The FSAP User Guides appear in column M to the right. A Comment on Entering Valuation Parameter Assumptions:
The FSAP user must enter valuation parameter assumptions in the green-shaded, blue font boxes below. These valuation parameters involve the costs of common equity capital, debt capital, and preferred stock capital (if any), as well as the long run growth rate assumption. FSAP references data in the Data spreadsheet and the Forecasts spreadsheet when available. FSAP uses these parameters to compute costs of capital to use as discount rates in the valuation models.
DATA CHECKS - Estimated Value per Share
Dividend Based Valuation $ 3.32 FSAP automatically references the estimated value per share.
Free Cash Flow Valuation $ 3.39 FSAP automatically references the estimated value per share.
Residual Income Valuation $ 3.39 FSAP automatically references the estimated value per share.
Residual Income Market-to-Book Valuation $ - 0 FSAP automatically references the estimated value per share.
Free Cash Flow for All Debt and Equity Valuation $ - 0 FSAP automatically references the estimated value per share.
Check: All Estimated Value per Share amounts should be the same, possibly with an exception to Free Cash Flow for All Debt and Equity. See additional comments in cell M266.
FSAP OUTPUT: VALUATION MODELS
Analyst Name: Tareq Hamdan
Company Name: Proctor & Gamble
VALUATION PARAMETER ASSUMPTIONS
Market Value Parameters:
Current share price $ 14.17 FSAP automatically references the most recent share price entered in the Data spreadsheet. The FSAP user can override this and enter the most recent share price directly in this cell.
Number of shares outstanding 735.5 FSAP automatically references the most recent number of shares outstanding entered in the Data spreadsheet. The FSAP user can override this and enter the most recent number of shares outstanding directly in this cell.
Current market value $ 10,422 FSAP automatically computes market value of equity using market price per share times number of shares outstanding.
Long-run growth assumption used in forecasts 3.0% Long Run Growth Parameters:
Long-run growth assumption used in valuation. 3.0% Enter the long run growth rate assumption for use in the valuation models. This growth rate must agree with the long run growth rate used to forecast Year +6 and Beyond in the Forecasts spreadsheet.
(Both long-run growth assumptions should be the same.)
COST OF EQUITY CAPITAL: Cost of Equity Capital Parameters:
Equity risk factor (market beta) 0.00 Enter market beta.
Risk free rate 0.0% Enter a risk-free rate of return, such as the yield on 3 to 5 year U.S. Treasury bonds.
Market risk premium 0.0% Enter the expected market risk premium. This is the amount by which the average expected rate of return on a diversified portfolio of stocks is expected to exceed the expected rate of return on a portfolio of risk free securities.
Required rate of return on common equity: 0.00% Using the above parameters, FSAP will compute the expected rate of return on equity using the market model version of the CAPM.
COST OF DEBT CAPITAL Cost of Debt Capital Parameters:
Debt capital $ 1,263 FSAP will automatically reference the total amount of short term debt and long term debt from the most recent balance sheet data in the Forecasts worksheet. The analyst can override this default by entering the market value of debt capital, if known.
Cost of debt capital, before tax 0.0% FSAP automatically references the interest rate assumption entered in the Forecasts spreadsheet. The analyst can override this default by entering here the expected interest rate on debt capital to be used in computing the weighted average cost of capital. The interest rate on debt capital used here in valuation should be consistent with the interest rate assumed in the forecasts of future interest expense in the Forecasts Spreadsheet.
Effective tax rate 0.0% FSAP automatically references the tax rate assumption entered in the Forecasts spreadsheet. The analyst can override this default by entering here the effective tax rate for use in computing the effective after-tax cost of debt capital.
After-tax cost of debt capital 0.00% FSAP uses the above parameters to compute the after-tax cost of debt capital.
COST OF PREFERRED STOCK Cost of Preferred Stock Parameters:
Preferred stock capital $ - 0 Enter the market value of preferred stock, if any. FSAP automatically references the amount of preferred stock entered in the most recent balance sheet data in the Forecasts worksheet.
Preferred dividends $ - 0 Enter the amount of preferred dividends, if any. FSAP does not automatically reference the amount of preferred stock in the most recent balance sheet data because relatively few firms have preferred stock.
Implied yield 0.00% FSAP assumes most firms do not have preferred stock and sets this yield to zero. If the firm does have preferred stock, compute and enter the implied yield on preferred stock.
WEIGHTED AVERAGE COST OF CAPITAL Weighted Average Cost of Capital:
Weight of equity in capital structure 0.89 FSAP automatically computes the weight of equity in the capital structure by dividing the market value of equity by the market value of total capital (common equity, debt, and preferred stock).
Weight of debt in capital structure 0.11 FSAP automatically computes the weight of debt in the capital structure by dividing the value of debt by the market value of total capital (common equity, debt, and preferred stock).
Weight of preferred in capital structure 0.00 FSAP automatically computes the weight of preferred stock in the capital structure by dividing the market value of preferred by the market value of total capital (common equity, debt, and preferred stock).
Weighted average cost of capital 0.00% FSAP will use the valuation parameter data to compute a weighted average cost of capital.
FSAP OUTPUT: VALUATION MODELS
Analyst Name: Tareq Hamdan
Company Name: Proctor & Gamble
Continuing
1 2 3 4 5 Value Dividends-Based Valuation:
Dividends-Based Valuation Year +1 Year +2 Year +3 Year +4 Year +5 Year +6 Chapter 11 describes the dividends-based valuation approach.
Dividends Paid to Common Shareholders 2,402.4 0.0 0.0 0.0 0.0 FSAP references implied dividends for common shareholders from the Forecasts worksheet.
Less: Common Stock Issues 40.1 0.0 0.0 0.0 0.0 FSAP references the change in common stock plus paid in capital from the Forecasts worksheet. Stock issues are treated as negative dividends.
Plus: Common Stock Repurchases 0.0 0.0 0.0 0.0 0.0 FSAP reference the change in treasury stock from the Forecasts worksheet. Purchases of treasury stock are treated as dividends.
Dividends to Common Equity 2,442.5 0.0 0.0 0.0 0.0 0.0 Total dividends to common equity shareholders.
Present Value Factors 1.000 1.000 1.000 1.000 1.000 Present value factors based on the equity cost of capital computed above.
PV Net Dividends 2,442.5 0.0 0.0 0.0 0.0
Sum of PV Net Dividends 2,442.5 The sum of the present value of net dividends through Year +5.
PV of Continuing Value 0.0 The present value of continuing value dividends in Year +6 and beyond. Year +6 dividends are treated as a perpetuity with growth using the long-run growth rate assumption, discounted to present value at the equity cost of capital.
Total 2,442.5
Adjust to midyear discounting 1.0000 This adjustment corrects for over-discounting. The present value factors discount from the end of each year to the present, whereas dividends, cash flows, and earnings are generated throughout the year. This adjustment computes the present value so that dividends, cash flows, and earnings are discounted from the mid-point of each year.
Total PV Dividends 2,442.5
Shares Outstanding 735.5
Estimated Value per Share $ 3.32 The estimated value per share.
Current share price $ 14.17
Percent difference -77% (Value/price)-1: positive number indicates underpricing.
FSAP OUTPUT: VALUATION MODELS
Analyst Name: Tareq Hamdan
Company Name: Proctor & Gamble
Continuing
1 2 3 4 5 Value Free Cash Flows-Based Valuation:
Free Cash Flows for Common Equity Year +1 Year +2 Year +3 Year +4 Year +5 Year +6 Chapter 12 describes the free cash flows-based valuation approaches.
Net Cash Flow from Operations -3,253.6 0.0 0.0 0.0 0.0 0.0 FSAP references net cash flows from operations from the Forecasts worksheet.
Decrease (Increase) in Cash Required for Operations 269.8 0.0 0.0 0.0 0.0 0.0 The analyst should adjust free cash flows for changes in cash required for operations. As firms grow, they typically require larger cash balances for liquidity in operating activities. FSAP is programmed to automatically adjust free cash flows for the change in the cash balance, which is assumed to be required for operations.
Net Cash Flow from Investing 6,738.0 0.0 0.0 0.0 0.0 0.0 FSAP references net cash flows from investing from the Forecasts worksheet.
Net CFs from Debt Financing -1,263.3 0.0 0.0 0.0 0.0 0.0 FSAP references net cash flows from debt financing from the Forecasts worksheet.
Net CFs into Financial Assets 0.0 0.0 0.0 0.0 0.0 0.0 This row enables the analyst to adjust for any investing cash flows that should be classified as financing cash flows. For example, if the investing cash flows include cash outflows to acquire investment securities that will be used to retire debt, then these cash outflows should be added back in computing free cash flows to debt and equity.
Net CFs - Pref. Stock and Minority Int. 0.0 0.0 0.0 0.0 0.0 0.0 FSAP references net cash flows from preferred stock and minority interests from the Forecasts worksheet.
Free Cash Flow for Common Equity 2,490.9 0.0 0.0 0.0 0.0 0.0
Present Value Factors 1.000 1.000 1.000 1.000 1.000 Present value factors based on the equity cost of capital computed above.
PV Free Cash Flows 2,490.9 0.0 0.0 0.0 0.0
Sum of PV Free Cash Flows 2,490.9 The sum of the present value of free cash flows for common equity shareholders through Year +5.
PV of Continuing Value 0.0 The present value of continuing free cash flows in Year +6 and beyond. Year +6 free cash flows are treated as a perpetuity with growth using the long-run growth rate assumption, discounted to present value at the equity cost of capital.
Total 2,490.9
Adjust to midyear discounting 1.0000 This adjustment corrects for over-discounting. The present value factors discount from the end of each year to the present, whereas dividends, cash flows, and earnings are generated throughout the year. This adjustment computes the present value so that dividends, cash flows, and earnings are discounted from the mid-point of each year.
Total PV Free Cash Flows to Equity 2,490.9
Shares Outstanding 735.5
Estimated Value per Share $ 3.39 The estimated value per share.
Current share price $ 14.17
Percent difference -76% (Value/price)-1: positive number indicates underpricing.
FSAP OUTPUT: VALUATION MODELS
Analyst Name: Tareq Hamdan
Company Name: Proctor & Gamble
Free Cash Flow Valuation Sensitivity Analysis:
Long-Run Growth Assumptions Sensitivity Analyses:
3.39 0% 2% 3% 4% 5% 6% 8% 10% The FSAP user can enter the relevant range of discount rates in the left-most column and the relevant range of long run growth rates in the top row. Enter the discount rates and growth rates as percentages.
Discount 5% 3.31 3.31 3.31 3.31 0.00 3.31 3.31 3.31
Rates: 6% 3.29 3.29 3.29 3.29 3.29 0.00 3.29 3.29
6.48% 3.28 3.28 3.28 3.28 3.28 3.28 3.28 3.28
7.48% 3.27 3.27 3.27 3.27 3.27 3.27 3.27 3.27
8.48% 3.25 3.25 3.25 3.25 3.25 3.25 3.25 3.25
10% 3.23 3.23 3.23 3.23 3.23 3.23 3.23 0.00
11% 3.22 3.22 3.22 3.22 3.22 3.22 3.22 3.22
12% 3.21 3.21 3.21 3.21 3.21 3.21 3.21 3.21
13% 3.19 3.19 3.19 3.19 3.19 3.19 3.19 3.19
14% 3.18 3.18 3.18 3.18 3.18 3.18 3.18 3.18
15% 3.17 3.17 3.17 3.17 3.17 3.17 3.17 3.17
16% 3.15 3.15 3.15 3.15 3.15 3.15 3.15 3.15
18% 3.13 3.13 3.13 3.13 3.13 3.13 3.13 3.13
20% 3.10 3.10 3.10 3.10 3.10 3.10 3.10 3.10
FSAP OUTPUT: VALUATION MODELS
Analyst Name: Tareq Hamdan
Company Name: Proctor & Gamble
Continuing
1 2 3 4 5 Value Residual Income Valuation:
RESIDUAL INCOME VALUATION Year +1 Year +2 Year +3 Year +4 Year +5 Year +6 Chapter 13 describes the residual income valuation approach.
Comprehensive Income Available
for Common Shareholders 0.0 0.0 0.0 0.0 0.0 0.0 FSAP references comprehensive income from the Forecasts worksheet, less any expected dividends to preferred stockholders.
Lagged Book Value of Common
Shareholders' Equity (at t-1) 2,490.9 0.0 0.0 0.0 0.0 0.0 FSAP references beginning of year (lagged) book value of common shareholders' equity from the Forecasts worsksheet.
Required Earnings 0.0 0.0 0.0 0.0 0.0 0.0 FSAP computes required earnings as the equity cost of capital times the beginning of year book value of common shareholders' equity.
Residual Income 0.0 0.0 0.0 0.0 0.0 0.0 Residual income is the difference between projected comprehensive income available to common and required earnings.
Present Value Factors 1.000 1.000 1.000 1.000 1.000 Present value factors based on the equity cost of capital computed above.
PV Residual Income 0.0 0.0 0.0 0.0 0.0
Sum of PV Residual Income 0.0 The sum of the present value of residual income through Year +5.
PV of Continuing Value 0.0 The present value of continuing residual income in Year +6 and beyond. Year +6 residual income is treated as a perpetuity with growth using the long-run growth rate assumption, discounted to present value at the equity cost of capital.
Total 0.0
Add: Beginning Book Value of Equity 2,490.9
PV of Equity 2,490.9
Adjust to midyear discounting 1.000 This adjustment corrects for over-discounting. The present value factors discount from the end of each year to the present, whereas dividends, cash flows, and earnings are generated throughout the year. This adjustment computes the present value so that dividends, cash flows, and earnings are discounted from the mid-point of each year.
Total PV of Equity 2,490.9
Shares Outstanding 735.5
Estimated Value per Share $ 3.39 The estimated value per share.
Current share price $ 14.17
Percent difference -76% (Value/price)-1: positive number indicates underpricing.
FSAP OUTPUT: VALUATION MODELS
Analyst Name: Tareq Hamdan
Company Name: Proctor & Gamble
RESIDUAL INCOME VALUATION SENSITIVITY ANALYSIS:
Long-Run Growth Assumptions Sensitivity Analyses:
3.39 0% 2% 3% 4% 5% 6% 8% 10% The FSAP user can enter the relevant range of discount rates in the left-most column and the relevant range of long run growth rates in the top row. Enter the discount rates and growth rates as percentages.
Discount 5% 3.31 3.31 3.31 3.31 0.00 3.31 3.31 3.31
Rates: 6% 3.29 3.29 3.29 3.29 3.29 0.00 3.29 3.29
7% 3.28 3.28 3.28 3.28 3.28 3.28 3.28 3.28
7.48% 3.27 3.27 3.27 3.27 3.27 3.27 3.27 3.27
9% 3.25 3.25 3.25 3.25 3.25 3.25 3.25 3.25
10% 3.23 3.23 3.23 3.23 3.23 3.23 3.23 0.00
11% 3.22 3.22 3.22 3.22 3.22 3.22 3.22 3.22
12% 3.21 3.21 3.21 3.21 3.21 3.21 3.21 3.21
13% 3.19 3.19 3.19 3.19 3.19 3.19 3.19 3.19
14% 3.18 3.18 3.18 3.18 3.18 3.18 3.18 3.18
15% 3.17 3.17 3.17 3.17 3.17 3.17 3.17 3.17
16% 3.15 3.15 3.15 3.15 3.15 3.15 3.15 3.15
18% 3.13 3.13 3.13 3.13 3.13 3.13 3.13 3.13
20% 3.10 3.10 3.10 3.10 3.10 3.10 3.10 3.10
FSAP OUTPUT: VALUATION MODELS
Analyst Name: Tareq Hamdan
Company Name: Proctor & Gamble
Continuing
RESIDUAL INCOME VALUATION 1 2 3 4 5 Value Market-to-Book Valuation:
Market-to-Book Approach Year +1 Year +2 Year +3 Year +4 Year +5 Year +6 Chapter 14 describes the market-to-book valuation approach.
Comprehensive Income Available
for Common Shareholders 0.0 0.0 0.0 0.0 0.0 0.0 FSAP references comprehensive income from the Forecasts worksheet, less any expected dividends to preferred stockholders.
Book Value of Common
Shareholders' Equity (at t-1) 2,490.9 0.0 0.0 0.0 0.0 0.0 FSAP references beginning of year (lagged) book value of common shareholders' equity from the Forecasts worksheet.
Implied ROCE 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% FSAP computes the implied ROCE, dividing comprehensive income by beginning of year book value of common equity.
Residual ROCE 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% FSAP computes the residual ROCE as implied ROCE minus the equity cost of capital computed above.
Cumulative growth factor in common equity as of t-1 100.0% 0.0% 0.0% 0.0% 0.0% 0.0% FSAP computes the cumulative growth factor in common equity as beginning of year book value of common equity divided by book value of common equity on the firm's current balance sheet.
Residual ROCE times cumulative growth 0.0% 0.0% 0.0% 0.0% 0.0% 0.0% The product of residual ROCE and the cumulative growth factor in common equity.
Present Value Factors 1.000 1.000 1.000 1.000 1.000 Present value factors based on the equity cost of capital computed above.
PV Residual ROCE times growth 0.000 0.000 0.000 0.000 0.000
Sum of PV Residual ROCE times growth 0.00 The sum of the present value of residual ROCE times cumulative growth through Year +5.
PV of Continuing Value 0.00 The present value of residual ROCE times cumulative growth in Year +6 and beyond. Year +6 residual ROCE is treated as a perpetuity with growth using the long-run growth rate assumption, discounted to present value at the equity cost of capital.
Total PV Residual ROCE 0.00
Add one for book value of equity at t-1 1.0
Sum 0.00
Adjust to mid-year discounting 1.000 This adjustment corrects for over-discounting. The present value factors discount from the end of each year to the present, whereas dividends, cash flows, and earnings are generated throughout the year. This adjustment computes the present value so that dividends, cash flows, and earnings are discounted from the mid-point of each year.
Implied Market-to-Book Ratio 0.000 The implied market-to-book value ratio.
Times Beginning Book Value of Equity 2,490.9
Total PV of Equity 0.0
Shares Outstanding 735.5
Estimated Value per Share $ - 0 The estimated value per share.
Current share price $ 14.17
Percent difference 0% (Value/price)-1: positive number indicates underpricing.
Sensitivity analysis for the market-to-book approach should be identical to that of the residual income approach.
FSAP OUTPUT: VALUATION MODELS
Analyst Name: Tareq Hamdan
Company Name: Proctor & Gamble
Continuing
1 2 3 4 5 Value Free Cash Flows Valuation:
Free Cash Flows for All Debt and Equity Year +1 Year +2 Year +3 Year +4 Year +5 Year +6 Chapter 12 describes the free cash flows-based valuation approaches.
Net Cash Flow from Operations -3,253.6 0.0 0.0 0.0 0.0 0.0 FSAP references net cash flows from operations from the Forecast worksheet.
Add back: Interest Expense after tax 0.0 0.0 0.0 0.0 0.0 0.0 FSAP automatically references interest expense from the Forecast worksheet, and adds back the after-tax amount of interest expense.
Subtract: Interest Income after tax 0.0 0.0 0.0 0.0 0.0 0.0 The analyst should program FSAP to subtract interest income after tax if the analyst determines that the firm's financial assets are part of the financial capital structure (such as investment securities intended to retire debt).
Decrease (Increase) in Cash Required for Operations 269.8 0.0 0.0 0.0 0.0 0.0 The analyst should adjust free cash flows for changes in cash required for operations. As firms grow, they typically require larger cash balances for liquidity in operating activities. FSAP is programmed to automatically adjust free cash flows for the changes in cash balances, which are assumed to be required for operations.
Free Cash Flow from Operations 0.0 0.0 0.0 0.0 0.0 0.0
Net Cash Flow from Investing 6,738.0 0.0 0.0 0.0 0.0 0.0 FSAP references net cash flows from investing from the Forecast worksheet.
Add back: Net CFs into Financial Assets 0.0 0.0 0.0 0.0 0.0 0.0 This row enables the analyst to adjust for any investing cash flows that should be classified as financing cash flows. For example, if the investing cash flows include cash outflows to acquire investment securities that will be used to retire debt, then these cash outflows should be added back in computing free cash flows to debt and equity.
Free Cash Flows - All Debt and Equity 0.0 0.0 0.0 0.0 0.0 0.0
Present Value Factors 1.000 1.000 1.000 1.000 1.000 Present value factors based on the weighted average cost of capital computed above.
PV Free Cash Flows 0.0 0.0 0.0 0.0 0.0
Sum of PV Free Cash Flows 0.0 The sum of the present value of free cash flows for all debt and equity stakeholders through Year +5.
PV of Continuing Value 0.0 The present value of continuing free cash flows in Year +6 and beyond. Year +6 free cash flows are treated as a perpetuity with growth using the long-run growth rate assumption, discounted to present value at the weighted average cost of capital.
Total PV Free Cash Flows to Equity and Debt 0.0 Total present value of all equity and debt.
Less: Value of Outstanding Debt -1,263.3 Subtract the value of outstanding debt. Value should be market value, if known, or fair value if disclosed. If not, use book value.
Less: Value of Preferred Stock 0.0 Subtract the value of outstanding preferred stock. Value should be market value, if known, or fair value if disclosed. If not, use book value.
Plus: Value of Financial Assets 0.0 Add the value of financial assets to be used to retire debt or pay dividends. Value should be market value, if known, or fair value if disclosed. If not, use book value.
PV of Equity 0.0
Adjust to midyear discounting 1.0000 This adjustment corrects for over-discounting. The present value factors discount from the end of each year to the present, whereas dividends, cash flows, and earnings are generated throughout the year. This adjustment computes the present value so that dividends, cash flows, and earnings are discounted from the mid-point of each year.
Total PV of Equity 0.0
Shares Outstanding 735.5
Estimated Value per Share $ - 0 The estimated value per share. The first-iteration estimate of share value using this approach frequently differs slightly from the other share value estimates. Several iterations can be required to adjust the weights of debt and equity used to compute WACC to agree with the value of common equity implied by this valuation model.
Current share price $ 14.17
Percent difference 0% (Value/price)-1: positive number indicates underpricing.