Spreadsheet: jeff_dasovich__14051__Amerhome dylan+jeff
Sheet: Amerhome

 | EXHIBIT 1 | Selected Financial Data for American Home Products
 |  | Corporation 1972-1981
 |  |  |  |  |  |  | More -->
 |  |  | 1972 | 1973 | 1974 | 1975 | 1976 | 1977 | 1978 | 1979 | 1980 | 1981
 | Sales ($ mill) |  | 1587.1 | 1784.4 | 2048.7 | 2258.6 | 2471.7 | 2685.1 | 3062.6 | 3406.3 | 3798.5 | 4131.2
 | Cash |  | -- | -- | -- | -- | 358.8 | 322.9 | 436.6 | 493.8 | 593.3 | 729.1
 | Total Debt |  | -- | -- | -- | -- | 7.8 | 10.3 | 13.7 | 10.3 | 13.9 | 16.6
 | Net Worth |  | -- | -- | -- | -- | 991.5 | 1035.3 | 1178 | 1322 | 1472.8 | 1654.5
 | Total Assets ($ mill) |  | 1042 | 1126 | 1241.6 | 1390.7 | 1510.9 | 1611.3 | 1862.2 | 2090.7 | 2370.3 | 2588.5
 | Net income ($ mill) |  | 172.7 | 199.2 | 225.6 | 250.7 | 277.9 | 306.2 | 348.4 | 396 | 445.9 | 497.3
 | Earnings per share |  | 1.08 | 1.25 | 1.42 | 1.58 | 1.75 | 1.94 | 2.21 | 2.51 | 2.84 | 3.18
 | Dividends per share |  | 0.59 | 0.625 | 0.777 | 0.9 | 1 | 1.15 | 1.325 | 1.5 | 1.7 | 1.9
 | Annual Growth in Sales |  | -- | 0.124 | 0.148 | 0.102 | 0.094 | 0.086 | 0.141 | 0.111 | 0.117 | 0.088
 | Annual Growth in EPS |  | -- | 0.157 | 0.136 | 0.113 | 0.108 | 0.109 | 0.139 | 0.136 | 0.131 | 0.12
 | Dividend [NAME] |  | 0.546 | 0.5 | 0.547 | 0.57 | 0.571 | 0.593 | 0.6 | 0.598 | 0.6 | 0.597
 | After Tax Profit Margin |  | 0.109 | 0.112 | 0.11 | 0.111 | 0.112 | 0.114 | 0.114 | 0.116 | 0.117 | 0.12
 | Return on Equity |  | 0.259 | 0.282 | 0.282 | 0.279 | 0.28 | 0.295 | 0.296 | 0.3 | 0.303 | 0.301
 |  |  |  |  | SCROLL DOWN FOR EXHIBIT 2
 | EXHIBIT 2 | Comparison Data for American Home Products and
 |  | Warner-Lambert, 1980 ($ million except for per share
 |  | and ratio data)
 |  |  |  |  | American
 |  |  |  |  | Home | Warner-
 |  |  |  |  | Products | [NAME]
 |  |  |  |  | Corp. | Company
 | Sales |  |  |  | 3798.5 | 3479.2
 | 5-yr compound annual growth rate |  |  |  | 0.11 | 0.099
 | Profit after tax |  |  |  | 445.9 | 192.7
 | 5-yr compound annual growth rate |  |  |  | 0.122 | 0.033
 |  |  |  |  |  |  |  | Turnover Ratios
 | Cash and Equivalents |  |  |  | 593.3 | 360.3 |  | AHP | Warner
 | Accounts Receivable |  |  |  | 517.3 | 541.5
 | Inventory |  |  |  | 557.3 | 645.8
 | Net Propert, plant and equipment |  |  |  | 450.5 | 827.1
 | Other |  |  |  | 251.9 | 582.5
 | Total Assets |  |  |  | 2370.3 | 2957.2
 | Total Debt |  |  |  | 13.9 | 710.1
 | Net Worth |  |  |  | 1472.8 | 1482.7
 | Earnings per share |  |  |  | 2.84 | 2.41
 | 5-yr compound annual growth rate |  |  |  | 0.124 | 0.03
 | Dividends per share |  |  |  | 1.7 | 1.32
 | 5-yr compound annual growth rate |  |  |  | 0.136 | 0.08
 | Stock Price (end of 1980) |  |  |  | 30 | 20 |  |  | AHP's stock price using Warner's PE ratio
 | Price/Earnings Ratio |  |  |  | 10.6 | 8.3
 | Profit Margin (profit after tax/sales) |  |  |  | 0.117 | 0.055
 | Return on Equity |  |  |  | 0.303 | 0.13
 | Interest Coverage (times) |  |  |  | 436.6 | 5
 | Ratio of Total Debt to Total Capital |  |  |  | 0.009 | 0.324
 | Bond Rating (a) |  |  |  | AAA | AAA/AA
 | (a) Warner-Lambert's debt was rated triple A but analysts felt
 | the firm was close to being downgraded to double A.
 |  |  |  | SCROLL DOWN FOR EXHIBIT 3...
 | EXHIBIT 3 | Pro Forma 1981 Results for Alternative Capital
 |  | Structure ($ millions except per share data)
 |  |  |  | --------Pro Forma 1981 for------
 |  |  | Actual | 30% Debt | 50% Debt | 70% Debt
 |  |  | 1981 | --------- to Total Capital -------
 | Sales |  | 4131.2 | 4131.2 | 4131.2 | 4131.2
 | EBIT  (a) |  | 954.8 | 922.2 | 922.2 | 922.2
 | Interest |  | 2.3 | 52.7 | 87.8 | 122.9
 | PROFIT BEFORE TAXES |  | 952.5 | 869.5 | 834.4 | 799.3
 | Taxes |  | 455.2 | 417.4 | 400.5 | 383.7
 | PROFIT AFTER TAXES |  | 497.3 | 452.1 | 433.9 | 415.6
 | Dividends on Pfd. Stock |  | 0.4 | 0.4 | 0.4 | 0.4
 | Earnings Available to |  |  |  |  |  |  |  | Dividend/Net Worth
 | Common Shareholders |  | 496.9 | 451.7 | 433.5 | 415.2
 | Dividnds on Common Stock |  | 295.3 | 271 | 260.1 | 249.1
 | Average Common Shares
 | Outstanding (millions) |  | 155.5 | 135.7 | 127.3 | 118.9
 | Earnings per Share |  | 3.18 | 3.33 | 3.41 | 3.49
 | Dividends per Share |  | 1.9 | 2 | 2.04 | 2.1 |  |  |  | Time Warner
 | Spread
 | ROA
 | [NAME]
 | ROE
 | Debt/Equity
 | Interest Coverage
 | Debt/Total Capital
 | Stock Price*
 | Delta
 | 2nd order change
 | * Assumes 10.6x P/E from 1980
 | BEGINNING OF YEAR: |  |  | After recapitalization
 | Cash and Equivalents |  | 593.3 | 360.3 | 360.3 | 360.3
 | Total Debt |  | 8 | 376.1 | 626.8 | 877.6
 | Net Worth |  | 1472.8 | 877.6 | 626.9 | 376.1
 | Common Stock Price |  | 30 | -- | -- | --
 | Aggregate Market Value
 | of Common Stock |  | 4665 | -- | -- | --
 | Total Assets |  | 2370.3 |  | 2137.3 | 2137.3
 | (a) EBIT is reduced in pro forma results due to the loss of
 | interest income from the $233 million in excess cash used to
 | repurchase stock.
 |  |  |  | SCROLL DOWN FOR INSTRUCTIONS...
 | AMERICAN HOME PRODUCTS CORP.
 | Harvard Business School
 | Case Software 2-292-701
 | Copyright (c) 1989 by the President and Fellows of Harvard College.
 | Developed in conjunction with MicroMentor, Inc., Cambridge, MA.
 | This case was prepared as a basis for class discussion rather than
 | to illustrate either effective or ineffective handling of an
 | administrative situation.
 |  |  |  | SCROLL DOWN FOR LIST OF EXHIBITS...
 | WORKSHEET NAVIGATION
 | This worksheet contains the data from the following exhibits:
 | Exhibit 1: | Selected Financial Data for American Home Products
 |  | Corporation, 1972-1981
 | Exhibit 2: | Comparison Data for American Home Products and
 |  | Warner-Lambert, 1980
 | Exhibit 3: | Pro Forma Results for Alternative Capital Structures
 | The first exhibit is located in the [NAME] position.
 | To view each succeeding exhibit, use the SCROLL Bar to view the next exhibit
 | OPTIONAL NAVIGATIONAL TECHNIQUE
 | Each exhibit in this worksheet has been named using the <Formula>
 | <Define> <Name> commands in Excel.  This allows you to easily move
 | from one section of the worksheet to another.    Either of the following
 | techniques will allow you to move from one exhibit to another.
 | Click on <[NAME] in the menu bar, then select <Go to...> from pulldown menu
 | A dialog box will appear listing the exhibts
 | Click on the exhibit you wish to view, then click on <OK>.
 |  | OR
 | You may use the scroll bar or arrow keys to page down through the list
 | of exhibits if the one you wish to view is not immediately apparent.
 | SCROLL TO A1 TO GO TO FIRST EXHIBIT...
 | REVISION HISTORY
 | Date | Name |  | Explanation of Change
 | 01/23/87 | [NAME] |  | Data Resources, Inc., Lexington, MA
 |  |  |  | convert to 2.01
 | 01/18/88 | [NAME] |  | Converted for use with ELAI
 |  |  |  | Changed all formulas to constants
 | 01/15/90 | [NAME] |  | MicroMentor, Inc., Cambridge, MA.
 |  |  |  | Converted to MicroMentor format
 | 1991-06-26 00:00:00 | [NAME] |  | Harvard Business School, Boston, MA
 |  |  |  | Converted to Excel
 | 1991-11-28 00:00:00 | [NAME] |  | Harvard Business School, Boston, MA
 |  |  |  | Converted to Excel/HBSUTIL
 | 1992-06-03 00:00:00 | [NAME] |  | Stripped HBSUTIL