Excel Sheet

MB0411
6Projecttemplate.xlsx
Home>Business & Finance homework help>Management homework help>Excel Sheet

Data

Financial Statement Analysis Package (FSAP): Version 10.0
Financial Reporting, Financial Statement Analysis, and Valuation: A Strategic Perspective, 10th Edition FSAP User Guides:
By Jim Wahlen, Steve Baginski and Mark Bradshaw
The FSAP user should only enter data in the blue-font cells shaded light green.
The FSAP User Guides appear in column I to the right. 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 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)).
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.
Analyst Name: Group 6 Insert your name in column B.
Company Name: Microsoft 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.) 2018 2019 2020 2021 2022 2023 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).
BALANCE SHEET DATA 2018 2019 2020 2021 2022 2023 Enter Balance Sheet Data:
Assets:
Cash and cash equivalents 11,946 11,356 13,576 14,224 13,931 34,704
Short-term investments 121,822 122,463 122,951 116,110 90,826 76,558
Accounts and notes receivable - net 26,481 29,524 32,011 38,043 44,261 48,688
Inventories 2,662 2,063 1,895 2,636 3,742 2,500
Prepaid expenses and other current assets
Other current assets (1) 6,751 10,146 11,482 13,393 16,924 21,807 Other current assets (1), (2) and (3) can be renamed and used for different types of current assets for different firms.
Other current assets (2)
Other current assets (3)
Current Assets 169,662 175,552 181,915 169,684 184,257 FSAP automatically computes the amount of total current assets.
Long-term investments
Equity and cost investments 1,862.00 2,649.00 2,965.00 5,984.00 6,891.00 9,879.00
Property, plant, and equipment - at cost (net accu) 29,460 36,477 44,151 59,715 74,398 95,641
<Accumulated depreciation> 51,351 59,660 68,251
Operating lease right-of-use assets 6,686 7,379 8,753 11,088 13,148 14,346
Other noncurrent asset (1) (Goodwill) 35,683 42,026 43,351 49,711 67,524 67,886 Other noncurrent assets (1), (2) and (3) can be renamed and used for different types of noncurrent assets for different firms.
Other noncurrent asset (2) (intangible Assets, net) 8,053 7,750 7,038 7,800 9,366 11,298
Other noncurrent asset (3) 7,442 14,723 13,138 15,075 21,897 30,601
Total Assets 258,848 286,556 301,311 333,779 422,568 482,159 FSAP automatically computes the amount of total assets.
Liabilities and Equities:
Accounts payable 8,617 9,382 12,530 15,163 19,000 18,095
Accrued liabilities 6,103 6,830 7,874 10,057 10,661 11,009
Notes payable and short-term debt
Current maturities of long-term debt 3,998 5,516 3,749 8,072 2,870 2,912
Current operating lease liabilities
Income taxes payable 2,121 5,665 2,130 2,174 26,069 25,560
Other current liabilities (1) 8,744 9,351 10,027 11,666 13,067 14,745 Other current liabilities (1) and (2) can be renamed and used for different types of current liabilities for different firms.
Other current liabilities (2) (short-term unearned rev) 28,905 32,676 36,000 41,525 45,538 50,901
Current Liabilities 58,488 69,420 72,310 88,657 117,205 123,222 FSAP automatically computes the amount of total current liabilities.
Long-term debt 72,242 66,662 59,578 50,074 47,032 41,990
Long-term operating lease liabilities 5,568 6,188 7,671 9,629 11,489 12,728
Deferred tax liabilities 541 223 204 198 230 433
Other noncurrent liabilities (1) 5,211 7,581 10,632 13,427 15,526 17,981 Other noncurrent liabilities (1) and (2) can be renamed and used for different types of non-current liabilities for different firms.
Other noncurrent liabilities (2) (long-term income tax) 30,265 29,612 29,432 27,190 26,069 25,560
Total Liabilities 172,315 179,686 107,517 100,518 217,551 221,914 FSAP automatically computes the amount of total liabilitries.
Preferred stock
Common stock + Additional paid in capital 71,223 78,520 80,552 83,111 86,939 93,718
Retained earnings <deficit> 13,682 24,150 34,566 57,055 84,281 118,848
Accum. other comprehensive income <loss> -2,187 -340 3,186 1,822 -4,678 -6,343
<Treasury stock> and other equity adjustments Include on this line any treasury stock accounts and 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.
Total Common Shareholders' Equity 82,718 102,330 118,304 141,988 166,542 206,223 FSAP automatically computes the amount of total shareholders' equity.
Noncontrolling interests
Total Equity 82,718 102,330 118,304 141,988 166,542 206,223
Total Liabilities and Equities 255,033 282,016 225,821 242,506 384,093 428,137 FSAP automatically computes the amount of total liabilities plus shareholders' equity.
INCOME STATEMENT DATA 2018 2019 2020 2021 2022 2023 Enter Income Statement Data:
Revenues 110,360 125,843 143,015 168,088 198,270 211,915 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.
<Cost of goods sold> -38,353 -42,910 46,078 52,232 62,650 65,863
Gross Profit 72,007 82,933 189,093 220,320 260,920 277,778 FSAP automatically computes the amount of gross profit.
<Selling, general, and administrative expenses> -4,754 -4,885 5,111 5,107 5,900 7,575
<Advertising expenses> -17,469 -18,213 19,598 20,117 21,825 22,759
<Research and development expenses> -14,726 -16,876 19,269 20,117 24,512 27,195
Add back: Imputed interest expense on operating lease obligations 15,242 17,340 Compute and add back the imputed interest expense on operating lease obligations (if any), as shown in Chapter 10.
<Other operating expenses (1)> Other operating expenses (1), (2), and (3) can be renamed and used for different types of recurring operating expenses for different firms.
<Other operating expenses (2)>
<Other operating expenses (3)>
<Non-recurring operating expenses> Non-recurring operating expenses can be renamed and used for different types of non-recurring expenses for different firms.
Income <Loss> from equity affiliates
Non-recurring operating gains <losses> 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.
Operating Income 35,058 42,959 233,071 265,661 328,399 352,647 FSAP automatically computes the amount of operating profit.
<Interest expense> -2,346 -2,094 -2,994
Subtract: Imputed interest expense on operating lease obligations Compute and subtract the imputed interest expense on operating lease obligations (if any), as shown in Chapter 10.
Other income <expense> 1,416 729 77 1,186 333 788
Income <Loss> from equity affiliates Enter any amounts of income (or <loss>) from equity or noncontrolled affiliates.
Other income or gains <Other expenses or losses> -59 -57 Enter income or gain amounts (or expense or loss amounts as negative numbers) that are unusual and non-recurring and outside of normal business operations.
Income before Tax 36,415 43,631 233,148 264,501 326,638 350,441 FSAP automatically computes the amount of income before tax.
<Income tax expense> -19,903 -4,448 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.
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.
Income <Loss> from changes in accounting principles 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 16,512 39,183 233,148 264,501 326,638 350,441 FSAP automatically computes the amount of net income using the above data for revenues, expenses, gains and losses.
Net income attributable to noncontrolling interests Enter the amount of net income attributable to noncontrolling interests. Enter amount as a negative number. If the firm reports a net loss attributable to noncontrolling interests, enter the amount as a positive number.
Net Income attributable to common shareholders 16,512 39,183 233,148 264,501 326,638 350,441
Net Income (enter reported amount as a check) 16,571 39,240 44,281 61,271 72,738 72,361 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 -2,856 1,914 This amount usually appears in the Statement of Comprehensive Income. Enter as a positive or negative number as appropriate.
Comprehensive Income 13,656 41,097 233,148 264,501 326,638 350,441 FSAP automatically computes the amount of comprehensive income.
STATEMENT OF CASH FLOWS DATA 2018 2019 2020 2021 2022 2023 Enter Statement of Cash Flows Data:
Net Income 16,512 39,183 233,148 264,501 326,638 350,441 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.
Add back depreciation and amortization expenses 10,261 11,682 12,796 11,686 14,460 13,861 FSAP automatically enters the Net Income amount computed above.
Add back stock-based compensation expense 3,940 4,652 5,289 6,118 7,502 9,611
Deferred income taxes -5,143 -6,463 11 150 5,702 6,059
<Income from equity affiliates, net of dividends> -2,212 -792
<Increase> Decrease in accounts receivable 3,862 2,812 2,577 6,481 6,834 4,087
<Increase> Decrease in inventories 465 -597 168 737 1,123 1,242
<Increase> Decrease in prepaid expenses
<Increase> Decrease in other current assets 952 1,718 2,330 932 709 1,991
<Increase> Decrease in other noncurrent assets 285 1,834 1,037
Increase <Decrease> in accounts payable 1,148 232 3,018 2,798 2,943 2,721
Increase <Decrease> in income taxes payable 18,183 2,929 3,631 2,309 696 358
Increase <Decrease> in other current liabilities 798 1,419 1,346 4,149 2,344 2,272
Operating lease right-of-use assets and liabilities, net
Other addbacks to <subtractions from> net income -5,922 -4,462 2,212
Other operating cash flows -20 591 1,348
Net CF from Operating Activities 43,109 54,738 268,911 35,360 368,951 392,643 FSAP automatically computes the amount of cash flow from operations.
Proceeds from sales of property, plant, and equipment
<Property, plant, and equipment acquired> -11,632 -13,925 15,441 20,622 23,886 28,107
<Increase> Decrease in marketable securities 26,360 20,043 66,449 62,924 26,456 37,651
Investments sold 117,577 38,194 17,721 14,008 28,443 14,354
<Investments acquired> -137,380 -57,697 77,190 62,924 26,456 37,651
Payments for acquisitions of intangible assets -888 -2,388 2,521 8,909 22,038 1,670
Other investing transactions -98 1,241 922 2,825 3,116
Net CF from Investing Activities -6,061 -15,773 180,563 170,309 130,104 122,549 FSAP automatically computes the amount of cash flow from investing activities.
Increase in short-term borrowing
<Decrease in short-term borrowing> -7,324
Increase in long-term borrowing 7,183
<Decrease in long-term borrowing> -10,060 -4,000
Issue of capital stock 1,002 1,142 1,343 1,693 1,841 1,866
Proceeds from stock option exercises
<Share repurchases> -10,721 -19,543 22,968 -27,385 -32,696 -22,245
<Dividend payments> -12,699 -13,811 15,137 -16,521 -18,135 -19,800
Other financing transactions (1) -971 -675 224
Other financing transactions (2)
Net CF from Financing Activities -33,590 -36,887 39,672 -42,213 -48,990 -40,179 FSAP automatically computes the amount of cash flow from financing activities.
Effects of exchange rate changes on cash 50 -115 201 29 141 194
Changes in restricted cash 2,220 648 293 20,773
Net Change in Cash 3,508 1,963 491,567 164,133 450,499 495,980 FSAP automatically computes the net change in cash.
Cash and cash equivalents, beginning of year 7,663 11,946 11,356 13,576 14,224 13,931 Enter the beginning balance in cash and cash equivalents for the first year of data.
Cash and cash equivalents, end of year 11,171 13,909 502,923 177,709 464,723 509,911
SUPPLEMENTAL DATA 2018 2019 2020 2021 2022 2023 Enter Supplemental Data:
Statutory tax rate 28.10% 21.00% 21.00% 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 54.70% 10.20% 0.00% 0.00% 0.00% 0.00% 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 -42 -45 0 0 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.
Depreciation expense 29,223 35,330 9,300 12,600 11,000 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) Enter the total amount of preferred stock dividends paid, if any.
Common shares outstanding 7,677 7,643 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) 2.15 5.11 5.82 8.12 9.7 9.72 Enter the amount that appears on the firm's income statement.
Common dividends per share 1.65 1.81 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! 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.
Share price at fiscal year end 99.61 133.96 203.51 270.9 256.83 340.54 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 3,815 4,540 75,490 91,273 38,475 54,022 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) -59 -57 188,867 203,230 253,900 278,080 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 2,553 489,347 163,485 450,792 475,207 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 10.0
Financial Reporting, Financial Statement Analysis, and Valuation: A Strategic Perspective, 10th 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: Group 6
Company Name: Microsoft
DATA CHECKS
Assets - Liabilities - Equities 3815 4540 75490 91273 38475 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) -59 -57 188867 203230 253900 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 2553 489347 163485 450792 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: 2019 2020 2021 2022 2023 Profitability Factors:
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 31.10% 163.00% 158.80% 165.80% 166.80% 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 0.5 0.5 0.5 0.5 0.5 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.
ERROR:#NAME? 14.4% 79.30% 84.00% 86.90% 78.10% 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 31.20% 163.00% 158.80% 165.80% 166.80% These computations of ROA exclude the after-tax effects of nonrecurring items in income to measure the firm's persistent ROA.
x Asset Turnover 0.5 0.5 0.5 0.5 0.5
ERROR:#NAME? 14.4% 79.30% 84.00% 86.90% 78.10%
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 31.10% 163.00% 157.40% 164.70% 165.40% 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 0.5 0.5 0.5 0.5 0.5 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 2.9 2.7 2.4 2.5 2.4 Capital structure leverage measures the average amount invested in assets divided by the average amount financed by common equity shareholders.
ERROR:#NAME? 42.3% 211.30% 203.20% 211.70% 188.00% 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 31.20% 163.00% 157.40% 164.70% 165.40% These computations of ROCE exclude the after-tax effects of nonrecurring items in income to measure the firm's persistent ROCE.
x Asset Turnover 0.5 0.5 0.5 0.5 0.5
x Capital Structure Leverage 2.9 2.7 2.4 2.5 2.4
ERROR:#NAME? 42.4% 211.30% 203.20% 211.70% 188.00%
OPERATING PERFORMANCE:
Gross Profit / Revenues 65.90% 132.20% 131.10% 131.60% 131.10% Gross profit margin as a percent of revenues.
Operating Profit / Revenues 34.10% 163.00% 158.00% 165.60% 166.40% Operating income as a percent of revenues.
Net Income / Revenues 31.10% 163.00% 157.40% 164.70% 165.40% Net income as a percent of revenues.
Comprehensive Income / Revenues 32.70% 163.00% 157.40% 164.70% 165.40% Comprehensive income as a percent of revenues.
PERSISTENT OPERATING PERFORMANCE (excluding the effects of nonrecurring items):
Persistent Operating Profit / Revenues 34.10% 163.00% 158.00% 165.60% 166.40% 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 31.20% 163.00% 157.40% 164.70% 165.40% Net income as a percent of revenues, after excluding the effects of non-recurring items in income.
GROWTH RATES:
Revenue Growth 14.00% 13.60% 17.50% 18.00% 6.90% Year-on-year growth rate in revenues.
Net Income Growth 137.30% 495.00% 13.40% 23.50% 7.30% Year-on-year growth rate in net income.
Persistent Net Income Growth 137.00% 494.30% 13.40% 23.50% 7.30% Year-on-year growth rate in net income, after excluding the effects of non-recurring items in income.
OPERATING CONTROL:
Gross Profit Control Index 101.00% 200.60% 99.10% 100.40% 99.60% The rate of change in gross profit relative to the rate of change in revenues.
Operating Profit Contol Index 107.50% 477.40% 97.00% 104.80% 100.50% The rate of change in operating income relative to the rate of change in revenues.
Net Profit Contol Index 208.10% 523.60% 96.50% 104.70% 100.40% The rate of change in net income relative to the rate of change in revenues.
Profit Margin Decomposition:
Gross Profit Margin 65.90% 132.20% 131.10% 131.60% 131.10% Gross profit margin as a percent of revenues.
Operating Profit Index 51.80% 123.30% 120.60% 125.90% 127.00% 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 101.60% 100.00% 99.60% 99.50% 99.40% 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 89.80% 100.00% 100.00% 100.00% 100.00% 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 31.10% 163.00% 157.40% 164.70% 165.40% 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 104.90% 100.00% 100.00% 100.00% 100.00% Comprehensive income as a percent of net income.
Comprehensive Income Margin 32.70% 163.00% 157.40% 164.70% 165.40% Comprehensive income as a percent of revenues.
RISK FACTORS: 2019 2020 2021 2022 2023 Risk Factors:
LIQUIDITY:
Current Ratio 2.53 2.52 0 1.45 1.5 Current assets divided by current liabilities.
Quick Ratio 2.35 2.33 1.9 1.27 1.3 More liquid current assets (cash and cash equivalents, marketable securities, accounts receivable) divided by current liabilities.
Operating Cash Flow to Current Liabilities 85.60% 379.50% 43.90% 358.40% 326.60% Operating cash flows divided by the average amount of current liabilities.
ASSET TURNOVER:
Cash Turnover 10.8 11.5 12.1 14.1 8.7 Revenues divided by the average cash balance.
Days Sales Held in Cash 33.8 31.8 30.2 25.9 41.9 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.
Accounts Receivable Turnover 4.5 4.6 4.8 4.8 4.6 Total revenues divided by the average balance in accounts receivable.
Days Receivables Held 81 79 76 76 80 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 18.2 -23.3 -23.1 -19.6 -21.1 Cost of goods sold divided by the average amaount of inventory.
Days Inventory Held 20 -16 -16 -19 -17 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 4.7 -4.2 -3.7 -3.6 -3.6 Inventory purchases (computed as cost of goods sold plus the change in inventory) divided by the average amount in accounts payable.
Days Payables Held 78 -86 -98 -101 -101 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 24 149 158 158 164 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.8 3.5 2.2 1.6 1.4 Total revenues divided by the average balance in net property, plant, and equipment. This measures efficiency is using fixed assets to generate revenues.
CAPEX Index 0.4 ERROR:#DIV/0! -2.2 -1.9 -2.6 Capital expenditures to acquire PPE divided by depreciation expense.
Expected Useful Life of PPE for Depreciation 0.9 ERROR:#DIV/0! 5.6 5.3 7.7 Net PPE (gross PPE minus accumulated depreciation) by depreciation expense. Average expected remaining useful life (in years) of PPE, assuming straight-line depreciation.
Expected Remaining Useful Life of PPE 1 ERROR:#DIV/0! 11.9 10.6 14.9 Average gross PPE (at cost) divided by depreciation expense. Average expected useful life (in years) of PPE, assuming straight-line depreciation.
SOLVENCY:
Total Liabilities / Total Assets 62.70% 35.70% 30.10% 51.50% 46.00% This ratio measures the percentage of total ssets financed by total liabilities.
Total Liabilities / Total Equity 175.60% 90.90% 70.80% 130.60% 107.60% This debt/equity ratio measures total liabiliteis as a percent of common shareholders' equity.
LT Debt / LT Capital 39.40% 33.50% 26.10% 22.00% 16.90% This ratio measures the percent of debt financing relative to total long term capital (long term debt plus commmon shareholders' equity).
LT Debt / Total Equity 65.10% 50.40% 35.30% 28.20% 20.40% This ratio measures the percent of long term debt financing relative to commmon shareholders' equity.
Operating Cash Flow to Total Liabilities 31.10% 187.30% 34.00% 232.00% 178.70% Operating cash flows divided by the average amount of total liabilities.
Interest Coverage Ratio (reported amounts) 113.7 157 118 Net income before interest expense, income taxes, and minority interest in income, divided by interest expense.
Interest Coverage ratio (recurring amounts) 113.7 157 118 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 4.91 3.62 3.03 3.45 3.33 The Altman Z-score is a multivariate predictor of bankruptcy.
Bankruptcy Probability 0.00% 0.43% 2.13% 0.72% 0.99% The probability of bankruptcy over the next two years as indicated by the Altman Z-score.
Earnings Manipulation Predictors:
Beneish Earnings Manipulation Score -2.53 -2.97 1.1 -2.69 -2.78 The Beneish Earnings Manipulation Score is a multivariate indicator of the likelihood reported earnings numbers have been fraudulently manipulated.
Earnings Manipulation Probability 0.56% 0.15% 86.41% 0.36% 0.27% The probability of earnings manipulation given the Beneish Earnings Manipulation Score.
DIVIDEND and STOCK MARKET-BASED RATIOS:
Stock Returns 36.30% Stock returns measure fiscal year-end share price plus dividends divided by beginning of year share price.
Price-Earnings Ratio (reported amounts) 26.2 35 33.4 26.5 35 Fiscal year-end share price divided by earnings per share.
Price-Earnings Ratio (recurring amounts) 26.2 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 10 0 0 0 0 Market value of common equity divided by book value of common equity.
Common Dividends per Share $1.81 #DIV/0! #DIV/0! #DIV/0! #DIV/0!
Common Dividend Payout (% of Net Income) 35.20% -6.50% 6.20% 5.60% 5.70%
Common Dividend Yield (% of Share Price) 1.30% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
INCOME STATEMENT ITEMS AS A PERCENT OF REVENUES: Common-Sized Income Statements:
2019 2020 2021 2022 2023
Revenues 100.00% 100.00% 100.00% 100.00% 100.00% All of the common-size income statement ratios measure a particular income amount as a percent of total revenues.
<Cost of goods sold> -34.10% 32.20% 31.10% 31.60% 31.10%
Gross Profit 65.90% 132.20% 131.10% 131.60% 131.10%
<Selling, general, and administrative expenses> -3.90% 3.60% 3.00% 3.00% 3.60%
<Advertising expenses> -14.50% 13.70% 12.00% 11.00% 10.70%
<Research and development expenses> -13.40% 13.50% 12.00% 12.40% 12.80%
<Other operating expenses (1)>
<Other operating expenses (2)>
<Other operating expenses (3)>
<Non-recurring operating expenses>
Income <Loss> from equity affiliates
Non-recurring operating gains <losses>
Operating Income 34.10% 163.00% 158.00% 165.60% 166.40%
<Interest expense> -1.40% -1.10% -1.40%
Other income <expense> 0.60% 0.10% 0.70% 0.20% 0.40%
Income <Loss> from equity affiliates
Other income or gains <Other expenses or losses> 0.00%
Income before Tax 34.70% 163.00% 157.40% 164.70% 165.40%
<Income tax expense> -3.50%
Income <Loss> from discontinued operations
Extraordinary gains <losses>
Income <Loss> from changes in accounting principles
Net Income 31.10% 163.00% 157.40% 164.70% 165.40%
Net income attributable to noncontrolling interests
Net Income attributable to common shareholders 31.10% 163.00% 157.40% 164.70% 165.40%
Other comprehensive income items 1.50%
Comprehensive Income 32.70% 163.00% 157.40% 164.70% 165.40%
INCOME STATEMENT ITEMS: GROWTH RATES Income Statement Growth Rates:
2019 2020 2021 2022 2023
YEAR TO YEAR GROWTH RATES: CAGR:
Revenues 14.00% 13.60% 17.50% 18.00% 6.90% 13.90% The year-on-year growth rates indicate the annual rate of growth in a particular income item.
<Cost of goods sold> 11.90% -207.40% 13.40% 19.90% 5.10% -211.40% The CAGR (compound annual growth rates) indicate the average compounded rate of growth in a particular income statement 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 15.20% 128.00% 16.50% 18.40% 6.50% 31.00%
<Selling, general, and administrative expenses> 2.80% -204.60% -0.10% 15.50% 28.40% -209.80%
<Advertising expenses> 4.30% -207.60% 2.60% 8.50% 4.30% -205.40%
<Research and development expenses> 14.60% -214.20% 4.40% 21.80% 10.90% -213.10%
<Other operating expenses (1)>
<Other operating expenses (2)>
<Other operating expenses (3)>
<Non-recurring operating expenses>
Income <Loss> from equity affiliates
Non-recurring operating gains <losses>
Operating Income 22.50% 442.50% 14.00% 23.60% 7.40% 58.70%
<Interest expense> -10.70% 43.00%
Other income <expense> -48.50% -89.40% 1440.30% -71.90% 136.60% -11.10%
Income <Loss> from equity affiliates
Other income or gains <Other expenses or losses> -3.40% -100.00% -100.00%
Income before Tax 19.80% 434.40% 13.40% 23.50% 7.30% 57.30%
<Income tax expense> -77.70% -100.00% -100.00%
Income <Loss> from discontinued operations
Extraordinary gains <losses>
Income <Loss> from changes in accounting principles
Net Income 137.30% 495.00% 13.40% 23.50% 7.30% 84.20%
Net income attributable to noncontrolling interests
Net Income attributable to common shareholders 137.30% 495.00% 13.40% 23.50% 7.30% 84.20%
Other comprehensive income items -167.00% -100.00% -100.00%
Comprehensive Income 200.90% 467.30% 13.40% 23.50% 7.30% 91.40%
COMMON SIZE BALANCE SHEET - AS A PERCENT OF TOTAL ASSETS Common-Sized Balance Sheets:
2019 2020 2021 2022 2023
Assets:
Cash and cash equivalents 4.00% 4.50% 4.30% 3.30% 7.20% All of the common-size balance sheet ratios measure a particular balance sheet amount as a percent of total assets.
Short-term investments 42.70% 40.80% 34.80% 21.50% 15.90%
Accounts and notes receivable - net 10.30% 10.60% 11.40% 10.50% 10.10%
Inventories 0.70% 0.60% 0.80% 0.90% 0.50%
Prepaid expenses and other current assets
Other current assets (1) 3.50% 3.80% 4.00% 4.00% 4.50%
Other current assets (2)
Other current assets (3)
Current Assets 61.30% 60.40% 40.20% 38.20%
Long-term investments
Equity and cost investments 0.90% 1.00% 1.80% 1.60% 2.00%
Property, plant, and equipment - at cost (net accu) 12.70% 14.70% 17.90% 17.60% 19.80%
<Accumulated depreciation> 15.40% 14.10% 14.20%
Operating lease right-of-use assets 2.60% 2.90% 3.30% 3.10% 3.00%
Other noncurrent asset (1) (Goodwill) 14.70% 14.40% 14.90% 16.00% 14.10%
Other noncurrent asset (2) (intangible Assets, net) 2.70% 2.30% 2.30% 2.20% 2.30%
Other noncurrent asset (3) 5.10% 4.40% 4.50% 5.20% 6.30%
Total Assets 100.00% 100.00% 100.00% 100.00% 100.00%
Liabilities and Equities:
Accounts payable 3.30% 4.20% 4.50% 4.50% 3.80%
Accrued liabilities 2.40% 2.60% 3.00% 2.50% 2.30%
Notes payable and short-term debt
Current maturities of long-term debt 1.90% 1.20% 2.40% 0.70% 0.60%
Current operating lease liabilities
Income taxes payable 2.00% 0.70% 0.70% 6.20% 5.30%
Other current liabilities (1) 3.30% 3.30% 3.50% 3.10% 3.10%
Other current liabilities (2) (short-term unearned rev) 11.40% 11.90% 12.40% 10.80% 10.60%
Current Liabilities 24.20% 24.00% 26.60% 27.70% 25.60%
Long-term debt 23.30% 19.80% 15.00% 11.10% 8.70%
Long-term operating lease liabilities 2.20% 2.50% 2.90% 2.70% 2.60%
Deferred tax liabilities 0.10% 0.10% 0.10% 0.10% 0.10%
Other noncurrent liabilities (1) 2.60% 3.50% 4.00% 3.70% 3.70%
Other noncurrent liabilities (2) (long-term income tax) 10.30% 9.80% 8.10% 6.20% 5.30%
Total Liabilities 62.70% 35.70% 30.10% 51.50% 46.00%
Preferred stock
Common stock + Additional paid in capital 27.40% 26.70% 24.90% 20.60% 19.40%
Retained earnings <deficit> 8.40% 11.50% 17.10% 19.90% 24.60%
Accum. other comprehensive income <loss> -0.10% 1.10% 0.50% -1.10% -1.30%
<Treasury stock> and other equity adjustments
Total Common Shareholders' Equity 35.70% 39.30% 42.50% 39.40% 42.80%
Noncontrolling interests
Total Equity 35.70% 39.30% 42.50% 39.40% 42.80%
Total Liabilities and Equities 98.40% 74.90% 72.70% 90.90% 88.80%
BALANCE SHEET ITEMS: GROWTH RATES Balance Sheet Growth Rates:
2019 2020 2021 2022 2023
Assets: YEAR TO YEAR GROWTH RATES: CAGR: The year-on-year growth rates indicate the annual rate of growth in a particular balance sheet item.
Cash and cash equivalents -4.90% 19.50% 4.80% -2.10% 149.10% 23.80% The CAGR (compound annual 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.
Short-term investments 0.50% 0.40% -5.60% -21.80% -15.70% -8.90%
Accounts and notes receivable - net 11.50% 8.40% 18.80% 16.30% 10.00% 13.00%
Inventories -22.50% -8.10% 39.10% 42.00% -33.20% -1.20%
Prepaid expenses and other current assets
Other current assets (1) 50.30% 13.20% 16.60% 26.40% 28.90% 26.40%
Other current assets (2)
Other current assets (3)
Current Assets 3.50% 3.60% -100.00% 8.60% 1.70%
Long-term investments
Equity and cost investments 42.30% 11.90% 101.80% 15.20% 43.40% -19.60%
Property, plant, and equipment - at cost (net accu) 23.80% 21.00% 35.30% 24.60% 28.60% ERROR:#DIV/0!
<Accumulated depreciation> 16.20% 14.40%
Operating lease right-of-use assets 10.40% 18.60% 26.70% 18.60% 9.10% -16.70%
Other noncurrent asset (1) (Goodwill) 17.80% 3.20% 14.70% 35.80% 0.50% 53.20%
Other noncurrent asset (2) (intangible Assets, net) -3.80% -9.20% 10.80% 20.10% 20.60% 8.70%
Other noncurrent asset (3) 97.80% -10.80% 14.70% 45.30% 39.70% -34.80%
Total Assets 10.70% 5.10% 10.80% 26.60% 14.10% 13.20%
Liabilities and Equities:
Accounts payable 8.90% 33.60% 21.00% 25.30% -4.80% 16.00%
Accrued liabilities 11.90% 15.30% 27.70% 6.00% 3.30% 12.50%
Notes payable and short-term debt
Current maturities of long-term debt 38.00% -32.00% 115.30% -64.40% 1.50% -6.10%
Current operating lease liabilities
Income taxes payable 167.10% -62.40% 2.10% 1099.10% -2.00% 64.50%
Other current liabilities (1) 6.90% 7.20% 16.30% 12.00% 12.80% 11.00%
Other current liabilities (2) (short-term unearned rev) 13.00% 10.20% 15.30% 9.70% 11.80% 12.00%
Current Liabilities 18.70% 4.20% 22.60% 32.20% 5.10% 16.10%
Long-term debt -7.70% -10.60% -16.00% -6.10% -10.70% -10.30%
Long-term operating lease liabilities 11.10% 24.00% 25.50% 19.30% 10.80% 18.00%
Deferred tax liabilities -58.80% -8.50% -2.90% 16.20% 88.30% -4.40%
Other noncurrent liabilities (1) 45.50% 40.20% 26.30% 15.60% 15.80% 28.10%
Other noncurrent liabilities (2) (long-term income tax) -2.20% -0.60% -7.60% -4.10% -2.00% -3.30%
Total Liabilities 4.30% -40.20% -6.50% 116.40% 2.00% 5.20%
Preferred stock
Common stock + Additional paid in capital 10.20% 2.60% 3.20% 4.60% 7.80% 5.60%
Retained earnings <deficit> 76.50% 43.10% 65.10% 47.70% 41.00% 54.10%
Accum. other comprehensive income <loss> -84.50% -1037.10% -42.80% -356.80% 35.60% 23.70%
<Treasury stock> and other equity adjustments
Total Common Shareholders' Equity 23.70% 15.60% 20.00% 17.30% 23.80% 20.00%
Noncontrolling interests
Total Equity 23.70% 15.60% 20.00% 17.30% 23.80% 20.00%
Total Liabilities and Equities 10.60% -19.90% 7.40% 58.40% 11.50% 10.90%
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
2021 2022 2023
84.00% 86.90% 78.10%
Level 2 PROFIT MARGIN FOR ROA ASSET TURNOVER
2021 2022 2023 2021 2022 2023
158.80% 165.80% 166.80% 0.5 0.5 0.5
Level 3 2021 2022 2023 2021 2022 2023 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.00% 100.00% 100.00% 4.8 4.8 4.6 Receivables
<Cost of goods sold> 31.10% 31.60% 31.10% -23.1 -19.6 -21.1 Inventory
Gross Profit 131.10% 131.60% 131.10% 2.2 1.6 1.4 Fixed Assets
<Selling, general, and administrative expenses> 3.00% 3.00% 3.60%
Operating Income 158.00% 165.60% 166.40%
Income before Tax 157.40% 164.70% 165.40%
<Income tax expense>
Profit Margin for ROA* 158.80% 165.80% 166.80%
*Amounts do not sum.
RETURN ON COMMON SHAREHOLDERS' EQUITY ANALYSIS (excluding the effects of non-recurring items)
RETURN ON COMMON SHAREHOLDERS' EQUITY
2021 2022 2023
203.20% 211.70% 188.00%
2021 2022 2023
PROFIT MARGIN FOR ROCE 157.40% 164.70% 165.40%
ASSET TURNOVER 0.5 0.5 0.5
CAPITAL STRUCTURE LEVERAGE 2.4 2.5 2.4
RETURN ON COMMON SHAREHOLDERS' EQUITY ANALYSIS: Alternative Approach to Disaggregation
RETURN ON COMMON SHAREHOLDERS' EQUITY
2021 2022 2023
ROCE 203.20% 211.70% 188.00%
INPUT VARIABLES
Total Revenues 168,088 198,270 211,915
Net Operating Profit After Tax (NOPAT) 266,847 328,732 353,435
Net Financing Expense After Tax 2,346 2,094 2,994
Average Net Operating Assets 193,781 228,835 280,033
Average Financing Obligations 60,737 54,024 47,402
Average Common Equity 130,146 154,265 186,383
Profit margin for operating ROA 1.588 1.658 1.668
Net operating asset turnover 0.867 0.866 0.757
Operating ROA (NOPAT/Average NOA) 1.377 1.437 1.262
Net Borrowing Rate 0.039 0.039 0.063
Spread 1.338 1.398 1.199
Leverage 0.467 0.35 0.254
Leverage*Spread 0.625 0.49 0.305
ROCE = Operating ROA+Leverage*Spread 2.002 1.926 1.567
STATEMENT OF CASH FLOWS: SUMMARY Summary Statement of Cash Flows:
Year 2019 2020 2021 2022 2023 The Summary Statement of Cash Flows provides an aggregated summation of the major sources of cash inflows and outlfows.
Operating Activities: 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.
Net Income 39,183 233,148 264,501 326,638 350,441
Add back depreciation and amortization expenses 11,682 12,796 11,686 14,460 13,861
Net cash flows for working capital 10,347 14,107 17,406 14,649 12,671
Other net addbacks/subtractions -6,474 8,860 6,268 13,204 15,670
Net CF from Operating Activities 54,738 268,911 299,861 368,951 392,643
Investing Activities:
Capital expenditures (net) -13,925 15,441 20,622 23,886 28,107
Investments 540 161,360 139,856 81,355 89,656
Other investing transactions -2,388 3,762 9,831 24,863 4,786
Net CF from Investing Activities -15,773 180,563 170,309 130,104 122,549
Financing Activities:
Net proceeds from short-term borrowing 0 0 0 0 0
Net proceeds from long-term borrowing -4,000 0 0 0 0
Net proceeds from share issues and repurchases -18,401 24,311 -25,692 -30,855 -20,379
Dividends -13,811 15,137 -16,521 -18,135 -19,800
Other financing transactions -675 224 0 0 0
Net CF from Financing Activities -36,887 39,672 -42,213 -48,990 -40,179
Effects of exchange rate changes on cash -115 201 29 141 194
Net Change in Cash 1,963 489,347 427,986 450,206 475,207

Forecast Development

Financial Statement Analysis Package (FSAP): Version 10.0
Financial Reporting, Financial Statement Analysis, and Valuation: A Strategic Perspective, 10th Edition FSAP User Guides:
By James Wahlen, Steve Baginski and Mark Bradshaw
The FSAP User Guides appear in column K to the right. The FSAP user should only enter data in the blue-font cells shaded light green.
Analyst Name: Group 6 Forecast Development:
Company Name: Microsoft This Forecast Development spreadsheet provides work space in which the analyst can:
- build detailed revenue forecasts
Sales Forecast Development - build forecasts of capital expenditures, property, plant and equipment, depreciation expense, and accumulated depreciation.
- build detailed forecasts of other financial statement amounts.
Historical Figures: Sales Forecasts: It is not necessary to use this spreadsheet to build financial statement forecasts in the FSAP Forecasts
Year 2021 2022 2023 Year +1 Year +2 Year +3 Year +4 Year +5 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.
Net Sales (in millions): $168,088.00 $198,270.00 $211,915.00 $222,500.00 $233,620.00 $245,300.00 $257,560.00 $270,430.00
rates of change 18.00% 6.90% 5.00% 5.00% 5.00% 5.00% 5.00%
Implied revenue growth rates.
Sales Forecasts by Segments (from projections below):
Productivity and Business Processes $53,915.00 $63,364.00 $69,274.00 $72,737.70 $76,374.60 $80,193.30 $84,203.00 $88,413.10
Intelligent Cloud 60,080.00 75,251.00 87,907.00 92,302.40 96,917.50 101,763.30 106,851.50 112,194.10
More Personal Computing 54,093.00 59,655.00 54,734.00 57,470.70 60,344.20 63,361.40 66,529.50 69,856.00
Segment 4 0 0 0 - - - - -
Total Net Sales $168,088.00 $198,270.00 $211,915.00 $222,510.80 $233,636.30 $245,318.10 $257,584.00 $270,463.20 FSAP users who wish to develop detailed revenue forecasts to incorporate a firm's various sources of revenue may do so here.
rates of change 18.00% 6.90% 5.00% 5.00% 5.00% 5.00% 5.00% For an example of a detailed revenue forecast, see the FSAP Clorox model and the description in Chaper 10.
Implied revenue growth rates.
Sales by Segment: Sales Forecasts by Segment:
2021 2022 2023 Year +1 Year +2 Year +3 Year +4 Year +5
Productivity and Business Processes $53,915.00 $63,364.00 $69,274.00 $72,737.70 $76,374.60 $80,193.30 $84,203.00 $88,413.10
Sales growth rates: 17.50% 9.30% 5.00% 5.00% 5.00% 5.00% 5.00%
Volume growth: 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00%
Price changes: 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00%
Five-year growth rate history: 2016 2017 2018 2019 2020 CAGR
Sales growth rates: 2.00% 6.00% 11.00% 7.20% 3.50% 5.90%
Volume growth: 4.00% 9.00% 9.00% 7.80% 9.50% 7.80%
Price changes: 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Intelligent Cloud $60,080.00 $75,251.00 $87,907.00 $92,302.40 $96,917.50 $101,763.30 $106,851.50 $112,194.10
Sales growth rates: 25.30% 16.80% 5.00% 5.00% 5.00% 5.00% 5.00%
Volume growth: 5.00% 5.00% 5.00% 5.00% 5.00% 5.00% 5.00%
Price changes: 10.00% 10.00% 5.00% 5.00% 5.00% 5.00% 5.00%
Five-year growth rate history: 2016 2017 2018 2019 2020 CAGR
Sales growth rates: 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Volume growth: 4.00% 8.00% 16.00% 15.00% 6.00% 9.70%
Price changes: 2.00% 2.00% 2.00% 7.00% 18.00% 6.00%
More Personal Computing $54,093.00 $59,655.00 $54,734.00 $57,470.70 $60,344.20 $63,361.40 $66,529.50 $69,856.00
Sales growth rates: 10.30% -8.20% 5.00% 5.00% 5.00% 5.00% 5.00%
Volume growth: 0.00% 0.00% 5.00% 5.00% 5.00% 5.00% 5.00%
Price changes: 0.00% 0.00% 5.00% 5.00% 5.00% 5.00% 5.00%
Five-year growth rate history: 2016 2017 2018 2019 2020 CAGR
Sales growth rates: -6.00% 3.00% 0.00% 6.00% 4.00% 1.30%
Volume growth: 0.00% 1.00% 5.00% 2.00% 2.00% 2.00%
Price changes: 0.00% 0.00% 3.00% 0.00% 0.00% 0.60%
Segment 4 $- $- $- $- $- $- $- $-
Sales growth rates: ERROR:#DIV/0! ERROR:#DIV/0! 0.00% 0.00% 0.00% 0.00% 0.00%
Volume growth: 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Price changes and foreign currency exchange rates: 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Five-year growth rate history: 2016 2017 2018 2019 2020 CAGR
Sales growth rates: 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Volume growth: 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Price changes and foreign currency exchange rates: 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Total Sales $168,088.00 $198,270.00 $211,915.00 $222,510.80 $233,636.30 $245,318.10 $257,584.00 $270,463.20
18.00% 6.90% 5.00% 5.00% 5.00% 5.00% 5.00%
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.
2021 2022 2023 Year +1 Year +2 Year +3 Year +4 Year +5
CAPEX: $20,622.00 $23,886.00 $28,107.00 $31,760.00 $35,888.00 $40,554.00 $45,826.00 $51,783.00
PP&E Acquired $20,622.00 $23,886.00 $28,107.00 $31,760.00 $35,888.00 $40,554.00 $45,826.00 $51,783.00
PP&E Sold 0 0 0 0 0 0 0 0
Net CAPEX $20,622.00 $23,886.00 $28,107.00 $29,512.40 $30,989.50 $32,541.20 $34,170.80 $35,882.70
Net CAPEX as a percent of:
Gross PP&E 46.70% 40.00% 37.80% 30.90% 24.80% 20.80% 18.10% 16.10%
Revenues 12.30% 12.00% 13.30% 13.30% 13.30% 13.30% 13.30% 13.30%
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: 2021 2022 2023 Year +1 Year +2 Year +3 Year +4 Year +5
Beg. balance at cost: 44,151 59,715 74,398 $95,641.00 $125,153.40 $156,142.80 $188,684.00 $222,854.80
Add: CAPEX forecasts from above: 20,622 23,886 28,107 29,512.40 30,989.50 32,541.20 34,170.80 35,882.70
End balance at cost: $59,715.00 $74,398.00 $95,641.00 $125,153.40 $156,142.80 $188,684.00 $222,854.80 $258,737.50
Accumulated Depreciation:
Beg. Balance: 43,197 51,351 59,660 $68,251.00 $61,298.00 $52,623.40 $42,141.00 $29,760.20
Subtract: Depreciation expense forecasts from below: -9,300 -12,500 -11,000 -6,953.00 -8,674.60 -10,482.40 -12,380.80 -14,374.30
End Balance: $51,351.00 $59,660.00 $68,251.00 $61,298.00 $52,623.40 $42,141.00 $29,760.20 $15,385.90
PP&E - net $111,066.00 $134,058.00 $163,892.00 $186,451.40 $208,766.20 $230,825.00 $252,615.00 $274,123.30
Depreciation Expense Forecast Development: Depreciation expense forecast on existing PP&E:
Existing PP&E at cost, less any non-depreciable PP&E (i.e., land): $95,641.00 $5,313.40 $5,313.40 $5,313.40 $5,313.40 $5,313.40 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. If the firm has non-depreciable PP&E, such as land, you shoudl subtract that amount here. .
Remaining balance to be depreciated. $163,892.00 $158,578.60 $153,265.20 $147,951.80 $142,638.40 $137,325.10 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.
New PP&E to be Depreciated: Depreciation expense forecasts on new PP&E:
Capex Year +1 $29,512.40 $1,639.60 $1,639.60 $1,639.60 $1,639.60 $1,639.60 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 $30,989.50 $1,721.60 $1,721.60 $1,721.60 $1,721.60
Capex Year +3 $32,541.20 $1,807.80 $1,807.80 $1,807.80
Capex Year +4 $34,170.80 $1,898.40 $1,898.40
Capex Year +5 $35,882.70 $1,993.50
Total Depreciation Expense $6,953.00 $8,674.60 $10,482.40 $12,380.80 $14,374.30
Depreciation methods: 2021 2022 2023
PP&E at Cost $59,715.00 $74,398.00 $95,641.00 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.
Subtract any PP&E that is not Depreciable -3660 -4734 -5683 If the firm has PPE that is not depreciable (e.g., land and construction in progress), subtract it here.
Depreciable PP&E $56,055.00 $69,664.00 $89,958.00
Avg Depreciable PP&E $62,859.50 $79,811.00
Depreciation Expense $9,300.00 $12,600.00 $11,000.00
Implied Average Useful Life in Years 5 7.3
Useful Life Forecast Assumption: (in years) 18

Valuation

Financial Statement Analysis Package (FSAP): Version 10.0
Financial Reporting, Financial Statement Analysis, and Valuation: A Strategic Perspective, 10th Edition FSAP User Guides:
By James Wahlen, Steve Baginski and Mark Bradshaw
The FSAP user should only enter data in the blue-font cells shaded light green.
The FSAP User Guides appear in column J 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 #DIV/0! FSAP automatically references the estimated value per share.
Free Cash Flow Valuation #DIV/0! FSAP automatically references the estimated value per share.
Residual Income Valuation #DIV/0! FSAP automatically references the estimated value per share.
Residual Income Market-to-Book Valuation #DIV/0! FSAP automatically references the estimated value per share.
Free Cash Flow for All Debt and Equity Valuation #DIV/0! FSAP automatically references the estimated value per share.
Check: All Estimated Value per Share amounts should be the same, with the possible exception of the share value from the
Free Cash Flow for All Debt and Equity model. See additional comments in cell J266.
FSAP OUTPUT: VALUATION MODELS
Analyst Name: Group 6
Company Name: Microsoft
VALUATION PARAMETER ASSUMPTIONS
Market Value Parameters:
Current share price $340.54 FSAP uses 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 0 FSAP uses 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 $- FSAP computes market value of equity using market price per share times number of shares outstanding.
Long-run growth assumption used in forecasts 3.00% Long Run Growth Parameters:
Long-run growth assumption used in valuation. 3.00% 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 Enter the market beta.
Risk free rate 0.00% Enter a risk-free rate of return, such as the yield on long-term U.S. Treasury bonds.
Market risk premium 0.00% 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. Reasonable estimates commonly range from 3% to 9%.
Required rate of return on common equity: 0.00% Using the above parameters, FSAP computes 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 $- FSAP uses 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.00% FSAP uses 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.00% FSAP uses 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. Enter zero if short-term and long-term interest-bearing debt are zero.
COST OF LEASE OBLIGATIONS Cost of Lease Obligations Parameters:
Capitalized lease obligations $12,728 FSAP uses the total amount of short term and long term obligations for operating and finance (capital) leases from the most recent balance sheet data in the Forecasts worksheet. The analyst can override this default by entering the fair value of lease obligations, if known.
Implied interest rate on leases, before tax 0.00% FSAP uses the implied interest rate assumption on lease obligations entered in the Forecasts spreadsheet. The analyst can override this default by entering here the expected interest rate on lease obligations to be used in computing the weighted average cost of capital. The interest rate on lease obligations used here in valuation should be consistent with the interest rate assumed in the forecasts of future imputed interest expense on lease obligations in the Forecasts Spreadsheet.
Effective tax rate 0.00% FSAP uses 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 lease obligations.
After-tax cost of debt capital 0.00% FSAP uses the above parameters to compute the after-tax cost of capital on lease obligations. Enter zero if short-term and long-term lease obligations are zero.
COST OF PREFERRED STOCK Cost of Preferred Stock Parameters:
Preferred stock capital $- FSAP uses the amount of preferred stock entered in the most recent balance sheet data in the Forecasts worksheet. The analyst can override this default by entering the market value of preferred stock, if known.
Preferred dividends $- FSAP uses the preferred stock dividend entered in the Forecasts spreadsheet. The analyst can override this default by entering the required rate of return on preferred stock, if known.
Implied yield 0.00% FSAP uses the above parameters to compute the cost of preferred stock capital. Enter zero if preferred stock is zero.
COST OF NONCONTROLLING INTERESTS' CAPITAL Cost of Noncontrolling Interests Parameters:
Noncontrolling interests capital $- FSAP uses the amount of equity capital attributable to noncontrolling interests entered in the most recent balance sheet data in the Forecasts worksheet. The analyst can override this default by entering the market value of noncontrolling interests, if known.
Earnings attributable to noncontrolling interests $- FSAP uses the earnings attributable to noncontrolling interests entered in the Forecasts spreadsheet. The analyst can override this default by entering the required rate of return on noncontrolling interests, if known.
Implied yield 0.00% FSAP uses the above parameters to compute the cost of capital atttributable to noncontrolling interests. Enter zero if noncontrolling intersts are zero.
WEIGHTED AVERAGE COST OF CAPITAL Weighted Average Cost of Capital:
Weight of equity in capital structure 0 FSAP 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, preferred stock, and noncontrolling interests).
Weight of debt in capital structure 0 FSAP 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, preferred stock, and noncontrolling interests).
Weight of lease obligations in capital structure 1
Weight of preferred stock in capital structure 0 FSAP 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, preferred stock, and noncontrolling interests).
Weight of noncontrolling interests in capital structure 0 FSAP computes the weight of noncontrolling interests in the capital structure by dividing the book value of noncontrolling interests by the market value of total capital (common equity, debt, preferred stock, and noncontrolling interests).
Weighted average cost of capital 0.00% FSAP uses the above weights and costs of capital to compute a weighted average cost of capital.
FSAP OUTPUT: VALUATION MODELS
Analyst Name: Group 6
Company Name: Microsoft
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 0 0 0 0 0 FSAP uses implied dividends for common shareholders from the Forecasts worksheet.
Less: Common Stock Issues 0 0 0 0 0 FSAP uses the change in common stock plus paid in capital from the Forecasts worksheet. Stock issues are treated as negative dividends.
Plus: Common Stock Repurchases 145,929.40 211,321.40 223,259.30 235,794.00 248,954.80 FSAP uses the change in treasury stock from the Forecasts worksheet. Purchases of treasury stock are treated as dividends.
Dividends to Common Equity 145,929.40 211,321.40 223,259.30 235,794.00 248,954.80 267,462.80 These figures represent the forecasted total dividends to common equity shareholders.
Present Value Factors 1 1 1 1 1 These present value factors are based on the equity cost of capital, computed above.
Present Value Net Dividends 145,929.40 211,321.40 223,259.30 235,794.00 248,954.80
Sum of Present Value Net Dividends 1,065,259.00 The sum of the present value of net dividends through Year +5.
Present Value of Continuing Value -8,915,427.10 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 -7,850,168.10
Adjust to midyear discounting 1 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 Present Value Dividends -7,850,168.10
Shares Outstanding 0
Estimated Value per Share #DIV/0! The estimated value per share.
Current share price $340.54
Percent difference ERROR:#DIV/0! (Value/price)-1: positive number indicates underpricing.
FSAP OUTPUT: VALUATION MODELS
Analyst Name: Group 6
Company Name: Microsoft
Continuing
1 2 3 4 5 Value Free Cash Flows for Common Equity 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 229,463.70 242,310.90 255,800.50 269,964.80 284,837.50 281,508.40 FSAP uses net cash flows from operations from the Forecasts worksheet.
Decrease (Increase) in Cash Required for Operations -54,022.00 0 0 0 0 -1,695.30 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 -29,512.40 -30,989.50 -32,541.20 -34,170.80 -35,882.70 -14,079.20 FSAP uses net cash flows from investing from the Forecasts worksheet.
Net CFs from Debt Financing 0 0 0 0 0 1,728.90 FSAP uses net cash flows from debt financing from the Forecasts worksheet.
Net CFs into Financial Assets 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 Noncontrolling Interests 0 0 0 0 0 0 FSAP uses net cash flows from preferred stock and minority interests from the Forecasts worksheet.
Free Cash Flow for Common Equity 145,929.40 211,321.40 223,259.30 235,794.00 248,954.80 267,462.80 These figures represent the forecasted total free cash flows to common equity shareholders.
Present Value Factors 1 1 1 1 1 These present value factors are based on the equity cost of capital, computed above.
Present Value Free Cash Flows 145,929.40 211,321.40 223,259.30 235,794.00 248,954.80
Sum of Present Value Free Cash Flows 1,065,259.00 The sum of the present value of free cash flows for common equity shareholders through Year +5.
Present Value of Continuing Value -8,915,427.10 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 -7,850,168.10
Adjust to midyear discounting 1 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 Present Value Free Cash Flows to Equity -7,850,168.10
Shares Outstanding 0
Estimated Value per Share #DIV/0! The estimated value per share.
Current share price $340.54
Percent difference ERROR:#DIV/0! (Value/price)-1: positive number indicates underpricing.
FSAP OUTPUT: VALUATION MODELS
Analyst Name: Group 6
Company Name: Microsoft
Dividends Valuation Sensitivity Analysis:
Vary Long-Run Growth Assumptions: Sensitivity Analyses:
ERROR:#DIV/0! 0.00% 1.00% 2.00% 2.50% 3.00% 3.50% 4.00% 5.00% 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.
Vary 5.25% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Discount 5.50% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Rates: 5.75% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
6.00% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
6.25% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
6.50% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
6.75% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
7.00% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
7.25% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
7.50% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
7.75% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
8.00% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
8.50% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
9.00% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
FSAP OUTPUT: VALUATION MODELS
Analyst Name: Group 6
Company Name: Microsoft
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 222,510.80 233,636.30 245,318.10 257,584.00 270,463.20 278,577.10 FSAP uses comprehensive income from the Forecasts worksheet, less any expected dividends to preferred stockholders.
Lagged Book Value of Common
Shareholders' Equity (at t-1) 206,223.00 282,804.40 305,119.20 327,178.00 348,968.00 370,476.30 FSAP uses beginning of year (lagged) book value of common shareholders' equity from the Forecasts worsksheet.
Required Earnings 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 222,510.80 233,636.30 245,318.10 257,584.00 270,463.20 278,577.10 Residual income is the difference between projected comprehensive income available to common and required earnings.
Present Value Factors 1 1 1 1 1 These present value factors are based on the equity cost of capital, computed above.
Present Value Residual Income 222,510.80 233,636.30 245,318.10 257,584.00 270,463.20
Sum of Present Value Residual Income 1,229,512.40 The sum of the present value of residual income through Year +5.
Present Value of Continuing Value -9,285,903.50 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 -8,056,391.10
Add: Beginning Book Value of Equity 206,223.00
Present Value of Equity -7,850,168.10
Adjust to midyear discounting 1 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 Present Value of Equity -7,850,168.10
Shares Outstanding 0
Estimated Value per Share #DIV/0! The estimated value per share.
Current share price $340.54
Percent difference ERROR:#DIV/0! (Value/price)-1: positive number indicates underpricing.
FSAP OUTPUT: VALUATION MODELS
Analyst Name: Group 6
Company Name: Microsoft
Residual Income Valuation Sensitivity Analysis:
Long-Run Growth Assumptions Sensitivity Analyses:
ERROR:#DIV/0! 0.00% 1.00% 2.00% 2.50% 3.00% 3.50% 4.00% 5.00% 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.
5.00% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Discount 5.50% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Rates: 5.75% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
6.00% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
6.25% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
6.50% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
6.75% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
7.00% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
7.25% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
7.50% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
8.00% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
8.50% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
9.00% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
10.00% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
FSAP OUTPUT: VALUATION MODELS
Analyst Name: Group 6
Company Name: Microsoft
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 222,510.80 233,636.30 245,318.10 257,584.00 270,463.20 278,577.10 FSAP uses comprehensive income from the Forecasts worksheet, less any expected dividends to preferred stockholders.
Book Value of Common
Shareholders' Equity (at t-1) 206,223.00 282,804.40 305,119.20 327,178.00 348,968.00 370,476.30 FSAP uses beginning of year (lagged) book value of common shareholders' equity from the Forecasts worksheet.
Implied ROCE 107.90% 82.61% 80.40% 78.73% 77.50% 75.19% FSAP computes the implied ROCE, dividing comprehensive income by beginning of year book value of common equity.
Residual ROCE 107.90% 82.61% 80.40% 78.73% 77.50% 75.19% 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.00% 137.14% 147.96% 158.65% 169.22% 179.65% 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 107.90% 113.29% 118.96% 124.91% 131.15% 135.09% The product of residual ROCE and the cumulative growth factor in common equity.
Present Value Factors 1 1 1 1 1 These present value factors are based on the equity cost of capital, computed above.
Present Value Residual ROCE times growth 1.079 1.133 1.19 1.249 1.312
Sum of Present Value Residual ROCE times growth 5.96 The sum of the present value of residual ROCE times cumulative growth through Year +5.
Present Value of Continuing Value -45.03 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 Present Value Residual ROCE -39.07
Add one for book value of equity at t-1 1
Sum -38.07
Adjust to mid-year discounting 1 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 -38.066 The implied market-to-book value ratio.
Times Beginning Book Value of Equity 206,223.00
Total Present Value of Equity -7,850,168.10
Shares Outstanding 0
Estimated Value per Share #DIV/0! The estimated value per share.
Current share price $340.54
Percent difference ERROR:#DIV/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: Group 6
Company Name: Microsoft
Continuing
1 2 3 4 5 Value Free Cash Flows for All Debt and Equity 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 229,463.70 242,310.90 255,800.50 269,964.80 284,837.50 281,508.40 FSAP uses net cash flows from operations from the Forecasts worksheet.
Add back: Interest Expense after tax 0 0 0 0 0 0 FSAP uses interest expense from the Forecasts worksheet, and adds back the after-tax amount of interest expense.
Subtract: Interest Income after tax 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 -54,022.00 0 0 0 0 -1,695.30 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 175,441.70 242,310.90 255,800.50 269,964.80 284,837.50 279,813.10
Net Cash Flow from Investing -29,512.40 -30,989.50 -32,541.20 -34,170.80 -35,882.70 -14,079.20 FSAP uses net cash flows from investing from the Forecasts worksheet.
Add back: Net CFs into Financial Assets 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 145,929.40 211,321.40 223,259.30 235,794.00 248,954.80 265,733.90 These figures represent the forecasted total free cash flows to all debt and equity stakeholders.
Present Value Factors 1 1 1 1 1 These present value factors are based on the weighted average cost of capital, computed above.
Present Value Free Cash Flows 145,929.40 211,321.40 223,259.30 235,794.00 248,954.80
Sum of Present Value Free Cash Flows 1,065,259.00 The sum of the present value of free cash flows for all debt and equity stakeholders through Year +5.
Present Value of Continuing Value -8,857,797.10 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 Present Value Free Cash Flows to Equity and Debt -7,792,538.10 Total present value of all equity and debt.
Less: Value of Outstanding Debt 0 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 Lease Obligations -12,728.00 Subtract the value of outstanding lease obligations. Value should be market value, if known, or fair value if disclosed. If not, use book value.
Less: Value of Preferred Stock 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 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.
Present Value of Equity -7,805,266.10
Adjust to midyear discounting 1 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 Present Value of Equity -7,805,266.10
Shares Outstanding 0
Estimated Value per Share #DIV/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 $340.54
Percent difference ERROR:#DIV/0! (Value/price)-1: positive number indicates underpricing.