1000 words finance essay 8 hrs

profileVincent666
AtrionStuff.xlsx
Home>Business & Finance homework help>Economics homework help>1000 words finance essay 8 hrs

Sheet1

HISTORICAL BALANCE SHEET HISTORICAL BALANCE SHEET HISTORICAL BALANCE SHEET HISTORICAL BALANCE SHEET PROFORMA BALANCE SHEET PROFORMA BALANCE SHEET PROFORMA BALANCE SHEET PROFORMA BALANCE SHEET NET WORKING CAPITAL CALCULATIONS
Assets: (In thousands) $59,999 means $59,999,999 Dec 31, 2017 Dec 31,2018 Dec 31, 2019 Dec31, 2020 Dec 31, 2021 Dec 31, 2022 Dec 31, 2023 Dec 31, 2024 Dec 31, 2025 Dec31, 2020 Dec 31, 2021 Dec 31, 2022 Dec 31, 2023 Dec 31, 2024 Dec 31, 2025
Total Current Assets 112,319 138,816 157,855 143,731 158,156 171,121
Current Assets: (Cash) (22,450) (55,166) (87,623) (71,345) (83,533) (94,177)
Cash and cash equivalents $30,136 $58,753 45,048 22,450 to balance 55,166 87,623 71,345 83,533 94,177 (Total Current Liabilities) (13,636) (10,855) (11,141) (11,434) (11,735) (12,043)
Short-term investments 35,468 9,684 23,766 19,258 Professional Judgement 20,799 5,728 6,186 6,681 7,216 Net Working Capital 76,233 72,794 59,091 60,953 62,888 64,901
Accounts receivable, net of allowance for doubtful accounts of $21 and $28 in 2018 and 2017, respectively 17,076 17,014 18,886 16,445 Percent of Revenues 18,354 18,836 19,332 19,840 20,362 Change in NWC (3,439) (13,703) 1,862 1,935 2,013
Inventories 29,354 33,572 42,093 50,298 Percent of Revenues 41,087 42,168 43,277 44,415 45,583
Prepaid expenses and other current assets 3,199 3,242 2,545 3,868 Percent of Revenues 3,410 3,500 3,592 3,686 3,783 FREE CASH FLOW TO FIRM PLANNING PERIOD
Total Current Assets 115,233 122,265 132,338 112,319 calculated 138,816 157,855 143,731 158,156 171,121 Dec 31, 2021 Dec 31, 2022 Dec 31, 2023 Dec 31, 2024 Dec 31, 2025
EBIT 45,543 46,691 47,869 49,077 50,318
Long-term investments 9,136 21,048 31,772 46,207 Professional Judgement = (revenue% - Cash equivalent)/2 38,278 40,574 43,009 45,589 48,324 (TAX) (8,198) (8,404) (8,616) (8,834) (9,057)
NOPAT 37,345 38,287 39,252 40,243 41,261
Property, Plant and Equipment 167,080 181,582 200,990 218,912 mathed out 236,189 253,478 272,143 291,308 310,973 Depreciation 9,788 10,560 11,254 12,018 12,755
Less: accumulated depreciation 100,711 106,689 116,384 123,977 10% of prior net PPE added depreciation 133,765 144,325 155,578 167,596 180,351 (CAPEX) (17,277) (17,289) (18,665) (19,165) (19,665)
Total Equipment 66,369 74,893 84,606 94,935 Calculated 102,424 109,153 116,565 123,712 130,622 (Increase in NWC) 3,439 13,703 (1,862) (1,935) (2,013)
Free Cash Flow to Firm 33,295 45,260 29,979 31,161 32,338
Other Assets and Deferred Charges:
Patents and licenses, net of accumulated amortization of $12,181 and $12,062 in 2018 and 2017, respectively 1,778 1,659 1,539 1,421 reduced by 10-K expense estimates 1,302 1,185 1,072 959 847 WACC 4.64%
Goodwill 9,730 9,730 9,730 9,730 constant 9,730 9,730 9,730 9,730 9,730 Net Present Value 151,087.04 -----------------> $151,087,040
Other 1,534 1,621 2,046 2,278 linear regression model 265.7X + 1205 2,534 2,800 3,065 3,331 3,596
Total Non- Current Assets 13,042 13,010 13,315 13,429 calculated 13,566 13,715 13,867 14,020 14,173
Total Assets $203,780 $231,216 262,031 266,890 TOTAL ASSETS CALCULATED 293,083 321,297 317,171 341,477 364,241
Liabilities and Stockholders’ Equity:
Current Liabilities:
Accounts payable $3,929 $5,082 5,707 6,635 Percent of Revenues use 2017-2019 only 5,143 5,278 5,417 5,559 5,705
Accrued liabilities 4,947 4,519 5,148 6,565 Percent of Revenues use 2017-2019 only 5,124 5,259 5,397 5,539 5,684
Accrued income and other taxes 746 619 419 436 Percent of Revenues 589 605 620 637 654
Total Current Liabilities 9,622 10,220 11,274 13,636 calculated 10,855 11,141 11,434 11,735 12,043
Other Liabilities and Deferred Credits:
Deferred income taxes 7,312 6,687 8,496 10,768 linear regression 1217.7X + 5271.5 11,360 12,577 13,795 15,013 16,230
Other 2,458 3,542 4,391 2,044 linear regression 966.5X + 1530.66667 5,397 6,364 7,330 8,297 9,263
Other Liabilities 9,770 10,229 12,887 12,812 calculated 16,757 18,941 21,125 23,309 25,493
Total Liabilities 19,392 20,449 24,161 26,448 TOTAL LIABILITIES CALCULATED 27,612 30,082 32,559 35,044 37,537
Stockholders’ Equity:
Common stock, par value $.10 per share 342 342 342 342 constant 342 342 342 342 342
Additional paid-in capital 48,730 50,391 52,043 53,527 linear regression 1604.3x+47162 55,184 58,392 59,996 61,601 63,205
Accumulated other comprehensive loss (1,215) 0 0 0 Historically typically zero 0 0 0 0 0
Retained earnings 268,194 291,761 317,745 337,700 consant unless stock buybacks 361,372 384,208 391,002 411,516 430,484
Treasury shares, 1,567 shares in 2018 and 1,568 shares in 2017, at cost (131,663) (131,727) (132,260) (151,127) (151,427) (151,727) (166,727) (167,027) (167,327)
Total Stockholders’ Equity 184,388 210,767 237,870 240,442 TOTAL STOCKHOLDER'S EQUITY CALC'D 265,471 291,215 284,613 306,432 326,704
Total Liabilities and Stockholders’ Equity $203,780 $231,216 262,031 266,890 TOTAL LIABILITIES & SE CALC'D 293,083 321,297 317,172 341,476 364,240
Assets: (In thousands) DONT TOUCH OR EDIT THESE Historical + Forecasted PP&E and CAPEX a. Estimated Firm or Asset Beta Debt/Equity
NET PPE 2017 66,369 Company Name Levered Equity Betas Capitalization Assumed Debt Betas Unlevered Equity Betas
Current Assets: (2018 DEPRECIATION) (5,978) RVP 1.96 10% 0.2 1.8000
Cash and cash equivalents 20.56% 38.54% 29.05% 15.21% PLUS CAPEX 2018 14,502 UTMD 0.26 0.00% 0.2 0.2600
Short-term investments 24.19% 6.35% 15.33% 13.05% NET PPE 2018 74,893 LMAT 1.33 22% 0.2 1.1262
Accounts receivable, net of ADA 11.65% 11.16% 12.18% 11.14% 11.53% 11.53% 11.53% 11.53% 11.53% 11.53% (2019 DEPRECIATION) (9,695) LMNX 0.54 39.00% 0.2 0.4446
Inventories 20.02% 22.02% 27.15% 34.08% 25.82% 25.82% 25.82% 25.82% 25.82% 25.82% PLUS CAPEX 2019 19,408 VAR 1.08 10.00% 0.2 1.0000
Prepaid expenses and other current assets 2.18% 2.13% 1.64% 2.62% 2.14% 2.14% 2.14% 2.14% 2.14% 2.14% NET PPE 2019 84,606
Total Current Assets 78.61% 80.20% 85.34% 76.10% (2020 DEPRECIATION) (7,593) Estimated Asset Beta = 0.9262
PLUS CAPEX 2020 17,922 b. Estimation of the cost of equity
Long-term investments NET PPE 2020 94,935 Analysis based on simple average of unlevered equity betas
(2021 DEPRECIATION) (9,788) beta unlevered D/E beta debt beta levered
Property, Plant and Equipment ignore ignore ignore ignore PLUS CAPEX 2021 17,277 0.9262 0.00% 0.2 0.9261667649
Less: accumulated depreciation ignore ignore ignore ignore NET PPE 2021 102,424
Total Equipment ignore ignore ignore ignore (2022 DEPRECIATION) (10,560) Risk free rate 1.70%
PLUS CAPEX 2022 17,289 Market risk premium 3.17%
Other Assets and Deferred Charges: NET PPE 2022 109,153 Equity beta (levered) 0.9262
Patents and licenses, net of accumulated amortization (2023 DEPRECIATION) (11,254) Estimated cost of equity 4.64%
Goodwill ignore ignore ignore ignore PLUS CAPEX 2023 18,665
Other NET PPE 2023 116,565 c. Estimate of the WACC
Total Non- Current Assets (2024 DEPRECIATION) (12,018) Before tax cost of debt 0.00%
PLUS CAPEX 2024 19,165 Tax rate 18.00%
Total Assets NET PPE 2024 123,712 After-tax
(2025 DEPRECIATION) (12,755) Proportions Cost Product
Liabilities and Stockholders’ Equity: PLUS CAPEX 2025 19,665 Debt 0% 0% 0
NET PPE 2025 130,622 Equity 100.00%
Current Liabilities: WACC
Accounts payable 2.68% 3.33% 3.68% ignore 3.23% 3.23% 3.23% 3.23% 3.23% 3.23%
Accrued liabilities 3.37% 2.96% 3.32% ignore 3.22% 3.22% 3.22% 3.22% 3.22% 3.22% 2017 R/E 268,194 verified
Accrued income and other taxes 0.51% 0.41% 0.27% 0.30% 0.37% 0.37% 0.37% 0.37% 0.37% 0.37% Plus 2018 Net Income 34,255 verified
Total Current Liabilities 6.56% 6.70% 7.27% ignore 6.85% 6.85% 6.85% 6.85% 6.85% 6.85% (2018 Dividends) (9,500) verified
(2018 Stock BuyBacks) 0 verified
Other Liabilities and Deferred Credits: 2018 R/E 291,761 verified
Deferred income taxes Plus 2019 Net Income 36,761 verified
Other (2019 Dividends) (10,800) verified
Other Liabilities (2019 Stock BuyBacks) 0 verified
2019 R/E 317,745 verified
Total Liabilities Plus 2020 Net Income 32,115 verified
(2020 Dividends) (12,100) verified
Stockholders’ Equity: (2020 Stock BuyBacks) (18,800) verified
Common stock, par value $.10 per share 2020 R/E 337,700 verified
Additional paid-in capital Plus 2021 Net Income 37,345 verified
Accumulated other comprehensive loss (2021 Dividends) (13,673) estimated 1.13% avg div increase
Retained earnings (2021 Stock BuyBacks) 0 estimated
Treasury shares 2021 R/E 361,372 estimated
Total Stockholders’ Equity Plus 2022 Net Income 38,287 estimated
(2022 Dividends) (15,450) estimated 1.13% avg div increase
Total Liabilities and Stockholders’ Equity (2022 Stock BuyBacks) 0 estimated
2022 R/E 384,208 estimated
Plus 2023 Net Income 39,252 estimated
Historical I/S for year ending: Dec 31, 2016 Dec 31, 2017 Dec 31,2018 Dec 31, 2019 Dec31, 2020 Proforma I/S Dec 31, 2021 Dec 31, 2022 Dec 31, 2023 Dec 31, 2024 Dec 31, 2025 (2023 Dividends) (17,459) estimated 1.13% avg div increase
Revenues 143,487 146,595 152,448 155,066 147,591 2.63% growth/yr
: 2020 was a blooper, pent up demand will put ATRI back on track, average revenue growth rate 2016-2019 will be used (2.63%), starting off base of 2019 earnings 		159,144 163,330 167,625 172,034 176,558 (2023 Stock BuyBacks) (15,000) estimated
Cost of Goods Sold (75,857) (75,841) (80,670) (84,378) (81,428) percent of revenues -52.98%
: percent of average 2016-2019, 2020 not used because Covid didnt allow for sale of higher margin products 		(84,320) (86,537) (88,813) (91,149) (93,546) 2023 R/E 391,002 estimated
Gross Profit 67,630 70,754 71,778 70,688 66,163 Calculated 74,825 76,793 78,812 80,885 83,012 Plus 2024 Net Income 40,243 estimated
(2024 Dividends) (19,729) estimated 1.13% avg div increase
Operating Expenses: (2024 Stock BuyBacks) 0 estimated
Selling (6,611) (7,251) (8,341) (8,813) (7,520) percent of revenues (same logic as COGS) (8,239) (8,456) (8,678) (8,906) (9,141) 2024 R/E 411,516 estimated
General and administrative (15,319) (16,430) (16,217) (16,308) (17,330) percent of revenues (same logic as COGS) (17,123) (17,574) (18,036) (18,510) (18,997) Plus 2018 Net Income 41,261 estimated
Research and development (6,574) (5,799) (5,513) (5,038) (5,645) percent of revenues
: trending downward historical % of rev, 2016 outlier eliminated, averaged 2017-2020
: 2020 was a blooper, pent up demand will put ATRI back on track, average revenue growth rate 2016-2019 will be used (2.63%), starting off base of 2019 earnings
: percent of average 2016-2019, 2020 not used because Covid didnt allow for sale of higher margin products 		(5,827) (5,980) (6,138) (6,299) (6,465) (2025 Dividends) (22,293) estimated 1.13% avg div increase
Total operating expense (28,504) (29,480) (30,071) (30,159) (30,495) Calculated (31,189) (32,010) (32,852) (33,716) (34,602) (2025 Stock BuyBacks) 0 estimated
2025 R/E 430,484 estimated
Operating Income 39,126 41,274 41,707 40,529 35,668 Calculated 43,635 44,783 45,961 47,169 48,410
Interest and Dividend Income 448 1,061 1,667 2,487 1,444 average of last 3 years 1,866 1,866 1,866 1,866 1,866
Other Investment Income (Loss) (311) 4 (1,380) 152 1,355 average of last 3 years 42 42 42 42 42
Other Income 3 1 42 — — immaterial, not predictable, conservative zeros 0 0 0 0 0
Income before Provision for Income Taxes 39,266 42,340 42,036 43,168 38,467 Calculated 45,543 46,691 47,869 49,077 50,318
Provision for Income Taxes (11,685) (5,747) (7,781) (6,407) (6,352) Quoted expected 18% effective rate in 10-K (8,198) (8,404) (8,616) (8,834) (9,057)
Net Income 27,581 36,593 34,255 36,761 32,115 CALCULATED TOTAL 37,345 38,287 39,252 40,243 41,261
Net Income Per Basic Share $15.12 $19.82 $18.49 $19.82 $17.49
Weighted Average Basic Shares Outstanding 1,824 1,846 1,853.00 1,855.00 1,836.00
Net Income Per Diluted Share $14.85 $19.71 $18.44 $19.73 $17.44
Weighted Average Diluted Shares Outstanding 1,857 1,857 1,858.00 1,863.00 1,841.00
Dividends Per Common Share $3.90 $4.50 $5.10 $5.80 $6.60
Common Size I/S Dec 31, 2016 Dec 31, 2017 Dec 31,2018 Dec 31, 2019 Dec31, 2020 DONT TOUCH OR EDIT THESE
Revenues 100.00% 100.00% 100.00% 100.00% 100.00%
Cost of Goods Sold -52.87% -51.74% -52.92% -54.41% -55.17% -52.98% -52.98% -52.98% -52.98% -52.98% -52.98%
Gross Profit 47.13% 48.26% 47.08% 45.59% 44.83% 47.02% 47.02% 47.02% 47.02% 47.02% 47.02%
0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Operating Expenses: 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Selling -4.61% -4.95% -5.47% -5.68% -5.10% -5.18% -5.18% -5.18% -5.18% -5.18% -5.18%
General and administrative -10.68% -11.21% -10.64% -10.52% -11.74% -10.76% -10.76% -10.76% -10.76% -10.76% -10.76%
Research and development -4.58% -3.96% -3.62% -3.25% -3.82% -3.66% -3.66% -3.66% -3.66% -3.66% -3.66%
Total operating expense -19.87% -20.11% -19.73% -19.45% -20.66% -19.79% -19.79% -19.79% -19.79% -19.79% -19.79%
0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Operating Income 27.27% 28.16% 27.36% 26.14% 24.17% 27.23% 27.23% 27.23% 27.23% 27.23% 27.23%
0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Interest and Dividend Income 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Other Investment Income (Loss) 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Other Income 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Income before Provision for Income Taxes 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Provision for Income Taxes 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Net Income 19.22% 24.96% 22.47% 23.71% 21.76% 22.59% 22.59% 22.59% 22.59% 22.59% 22.59%
0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
Net Income Per Basic Share
Weighted Average Basic Shares Outstanding
Net Income Per Diluted Share
Weighted Average Diluted Shares Outstanding
Dividends Per Common Share