Financial Statement Memo (6 hours due)

profileafdybz
WTRG_Model.xlsm
Home>Business & Finance homework help>Financial Statement Memo (6 hours due)

Dashboard

Premium 0.00% Average change in water consumption Note-
STATE - ARIZONA $ (305,393,284) -20% -10% 0% 10% 20% *effected by per capita income
COUNTY - MARICOPA 3% $ (56,380,298) $ (63,427,836) $ (70,475,373) $ (99,494,591) $ (134,322,185) **effected by inflation
5% $ (93,967,164) $ (105,713,060) $ (117,458,955) $ (165,824,319) $ (223,870,308)
Average change in water consumption 7% $ (131,554,030) $ (147,998,284) $ (164,442,537) $ (232,154,047) $ (313,418,431)
Change in Water Rates 9% $ (169,140,895) $ (190,283,507) $ (211,426,120) $ (298,483,774) $ (402,966,554) Recommendation for Maricopa:
Change in Poverty Rate* 11% $ (206,727,761) $ (232,568,731) $ (258,409,702) $ (364,813,502) $ (492,514,677) Scenarios in orange indicate acceptable premiums/NPV values. These are less than the management suggested premium values. WTRG should proceed with acquisitions based on acceptable range as indicated in orange.
Maintenance costs as % of revenue** 13% $ (244,314,626) $ (274,853,955) $ (305,393,284) $ (431,143,230) $ (582,062,800)
Yearly Inflation rate 15% $ (281,901,492) $ (317,139,179) $ (352,376,866) $ (497,472,957) $ (671,610,924)
Yearly Per capita income change 4.32% 17% $ (319,488,358) $ (359,424,403) $ (399,360,448) $ (563,802,685) $ (761,159,047)
(recommended : 4.32%) 19% $ (357,075,223) $ (401,709,627) $ (446,344,030) $ (630,132,412) $ (850,707,170)
Premium 13% 21% $ (394,662,089) $ (443,994,851) $ (493,327,612) $ (696,462,140) $ (940,255,293)
Acquistion Cost $2,349,179,107 23% $ (432,248,954) $ (486,280,074) $ (540,311,195) $ (762,791,868) $ (1,029,803,416)
NPV ($305,393,284) 25% $ (469,835,820) $ (528,565,298) $ (587,294,777) $ (829,121,595) $ (1,119,351,539)
27% $ (507,422,685) $ (570,850,522) $ (634,278,359) $ (895,451,323) $ (1,208,899,662)
Premium $ (0) Average change in water consumption Recommendation for San Francisco:
STATE - CALIFORNIA $ (341,392,569) -20% -10% 0% 10% 20% Scenarios in pink indicate acceptable premiums/NPV values. These are less than the management suggested premium values. WTRG should proceed with acquisitions based on acceptable range as indicated in pink.
COUNTY - SAN FRANCISCO 3% $ (57,155,061) $ (63,336,434) $ (69,517,808) $ (75,699,181) $ (81,880,555)
5% $ (95,258,434) $ (105,560,724) $ (115,863,013) $ (126,165,302) $ (136,467,591)
Average change in water consumption 7% $ (133,361,808) $ (147,785,013) $ (162,208,218) $ (176,631,423) $ (191,054,628)
Change in Water Rates 9% $ (171,465,182) $ (190,009,303) $ (208,553,423) $ (227,097,544) $ (245,641,664)
Change in Poverty Rate* 11% $ (209,568,556) $ (232,233,592) $ (254,898,628) $ (277,563,664) $ (300,228,701)
Maintenance costs as % of revenue** 13% $ (247,671,930) $ (274,457,881) $ (301,243,833) $ (328,029,785) $ (354,815,737)
Yearly Inflation rate 15% $ (285,775,303) $ (316,682,171) $ (347,589,038) $ (378,495,906) $ (409,402,773)
Yearly Per capita income change 8.47% 17% $ (323,878,677) $ (358,906,460) $ (393,934,244) $ (428,962,027) $ (463,989,810)
(recommended : 8.47%) 19% $ (361,982,051) $ (401,130,750) $ (440,279,449) $ (479,428,148) $ (518,576,846)
Premium 17% 21% $ (400,085,425) $ (443,355,039) $ (486,624,654) $ (529,894,268) $ (573,163,883)
Acquistion Cost $2,008,191,581 23% $ (438,188,798) $ (485,579,329) $ (532,969,859) $ (580,360,389) $ (627,750,919)
NPV ($341,392,569) 25% $ (476,292,172) $ (527,803,618) $ (579,315,064) $ (630,826,510) $ (682,337,956)
27% $ (514,395,546) $ (570,027,908) $ (625,660,269) $ (681,292,631) $ (736,924,992)

Reset San Francisco

Reset Maricopa

Sheet1

Arizona Maricopa California Sanfransisco
Av.consum Maint $ Inflation Poverty rate Water rates Av.consum Maint $ Inflation Poverty rate Water rates
5 1 1 5 3 2 3 1 5 5
1 -20% 10% 0% -20.00% -10.00% 1 -20% 10% 0% -18.31% -10.00%
2 -15% 15% 1% -15.00% -5.00% 2 -15% 15% 1% -13.73% -5.00%
3 -10% 20% 1.50% -10.00% 0.00% 3 -10% 20% 1.50% -9.15% 0.00%
4 -5% 25% 2% -5.00% 5.00% 4 -5% 25% 2% -4.58% 5.00%
5 0% 30% 2.50% 0.00% 10.00% 5 0% 30% 2.50% 0.00% 10.00%
6 5% 35% 3% 5.00% 6 5% 35% 3% 4.58%
7 10% 40% 10.00% 7 10% 40% 9.15%
8 15% 15.00% 8 15% 13.73%
9 20% 20.00% 9 20% 18.31%
0% 10% 0.00% 0.00% 0.00% -15% 20% 0.00% 0.00% 10.00%
FALSE TRUE
13% $ (305,393,284)
17% $ (469,569,315)

Graders Guide

Grader's Guide Location Use in Workbook How it improves efficiency
Lookup On sheet1 Indentifes user's selected variables in dashboard & are used in FCF calculations Helps comboboxes work and change variables for calculation
Checkbox On Dashboard To select yearly per capita income change (separate for each county) Helps include/exclude variable in calculation
Comboboxes On Dashboard To select value drivers on the dashboard Helps select variable ranges
Macros( >5 steps) On Dashboard Reset variables in the dashboard (separate for each county) Helps reset to original/base values
Data Table On Dashboard Sensitivity of NPV to premium and per capita income change Helps identify range of premiums
Conditional Formatting On Dashboard Identifies companies acceptable NPVs based on baseline premium given by board Helps identify acceptable premiums
Data Validation On Dashboard Alerts user to enter a valid premium value in the dashboard Helps set limitation on user inputs
UDF (with loop) In Proformas Identifies signs of cash inflows and outflows appropriately to calculate NPV Helps speed up calculations

ARIZONA

STATE ARIZONA
COUNTY MARICOPA 94.69%
Population (in person)
2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030
Population 3,825,191 3,875,065 3,948,225 4,019,019 4,095,508 4,175,049 4,258,603 4,329,580 4,410,824 4,498,504 4,587,928 4,679,129 4,772,143 4,867,006 4,963,755 5,062,427 5,163,060 5,265,694 5,370,368 5,477,122 5,585,999
households 1,530,076 1,550,026 1,579,290 1,607,608 1,638,203 1,670,020 1,703,441 1,731,832 1,764,330 1,799,402 1,835,171 1,871,652 1,908,857 1,946,802 1,985,502 2,024,971 2,065,224 2,106,277 2,148,147 2,190,849 2,234,400
Population growth 1.30% 1.89% 1.79% 1.90% 1.94% 2.00% 1.67% 1.88%
Above poverty 1,289,854 1,306,672 1,331,341 1,355,213 1,381,005 1,407,827 1,436,001 1,459,934 1,487,330 1,516,896 1,547,049 1,577,802 1,609,167 1,641,154 1,673,778 1,707,050 1,740,984 1,775,592 1,810,888 1,846,886 1,883,599
Below poverty 240,222 243,354 247,949 252,394 257,198 262,193 267,440 271,898 277,000 282,506 288,122 293,849 299,691 305,648 311,724 317,920 324,240 330,686 337,259 343,963 350,801
Total Consumption 106,110,798,340 107,494,303,100 109,523,761,500 111,487,587,060 113,609,391,920 115,815,859,260 118,133,647,220 120,102,549,200 122,356,257,760 124,788,514,466 127,269,120,743 129,799,037,709 132,379,245,584 135,010,744,076 137,694,552,764 140,431,711,495 143,223,280,788 146,070,342,242 148,973,998,956 151,935,375,959 154,955,620,643
Annual consumption per household 69,350 69,350 69,350 69,350 69,350 69,350 69,350 69,350 69,350 69,350 69,350 69,350 69,350 69,350 69,350 69,350 69,350 69,350 69,350 69,350 69,350
Base consumption per household 70,800 70,800 70,800 70,800 70,800 70,800 70,800 70,800 70,800 70,800 70,800 70,800 70,800 70,800 70,800 70,800 70,800 70,800 70,800 70,800 70,800
revenue per HH per year 82 82 82 82 82 82 82 82 82 82 82 82 82 82 82 82 82 82 82 82 82
Annual revenue 115,348,553 116,852,502 119,058,640 121,193,432 123,499,956 125,898,513 128,418,082 130,558,392 133,008,303 135,652,306 138,348,868 141,099,034 143,903,869 146,764,459 149,681,914 152,657,363 155,691,960 158,786,880 161,943,322 165,162,509 168,445,689
Base consumption 108,329,409,120 109,741,840,800 111,813,732,000 113,818,618,080 115,984,786,560 118,237,387,680 120,603,636,960 122,613,705,600 124,914,535,680
Base revenue from above poverty 107,728,640
Base revenue from below poverty 10,031,671
Water used above P/year 14,312,224,480 36,247,079,005 36,931,412,378 37,593,614,357 38,309,086,955 39,053,107,742 39,834,665,843 40,498,579,590 41,258,530,117
Water used below P/year 2,665,503,254 6,750,642,235 6,878,092,222 7,001,420,467 7,134,669,813 7,273,235,962 7,418,793,045 7,542,440,090 7,683,972,987
Sales above poverty (250,365,384) 643,624,474,078 655,775,900,318 667,534,348,503 680,238,701,178 693,449,972,290 707,327,778,032 719,116,621,393 732,610,750,335
Sales below poverty (23,313,977) 59,934,188,867 61,065,727,372 62,160,671,836 63,343,698,746 64,573,929,804 65,866,228,441 66,964,003,297 68,220,574,023
Total Sales (273,679,361) 703,558,662,945 716,841,627,690 729,695,020,339 743,582,399,924 758,023,902,094 773,194,006,473 786,080,624,690 800,831,324,358 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0 - 0
Sales growth -257174.07% 1.89% 1.79% 1.90% 1.94% 2.00% 1.67% 1.88%
Trend 107158.48%
Population growth trend 1.99% 21.69%
Per capita personal income 70800
2016 2017 2018 2019 Discount
$ 43,825 $ 45,667 $ 47,694 50%
rate 4.20% 4.44% 4.67% Persons per household 2.5
Average, trend 4.32%
Water rates per household
Base rate $ 6.96 per month First 5,900 gallons 5900 83.52 70800
Volume rate $ 4.65 per 1,000 gallons Afterwards $ 93 20,000
Note: Total water bill = base rate + volume rate
Others
Poverty Rate 15.70% 0.83%
Average water usage 76 gallons per person per day 30.4 per household per day
Local tax 2.00%
Original Poverty rate 15.70%

Pro Forma_Arizona

Value Drivers WTRG WACC calculation
Premium 13.00%
Acquisition cost $ 2,349,179,107 Debt Average value $2,161,519,200
Directly related to sales Cost of debt 4.66%
Operating Expenses (excluding depreciation) 43.57%
Operating Current Assets 16.97% Equity # Shares outstanding 220,758,719
Operating Current Liabilities 21.14% Current price per share $38.12
Capital Expenditures 0.00% <-- too high? Lower to 50% of past Market value $8,415,322,368
Maintenance Costs 10% Cost of equity 4.95%
Not directly related to sales Beta 0.54
Depreciation Rate 2.01% Rf 0.20%
Interest Rate on Debt 4.66% Rm 9.00%
Interest Rate on ST Investments 0.20% <-- 1 Yr treasry bill rate (as of 3/20/2020)
Dividend payout 88.35% https://www.treasury.gov/resource-center/data-chart-center/interest-rates/pages/textview.aspx?data=yield WACC 4.80%
Tax Rate 9.24%
Long-term growth rate 2%
k 4.80%
WTRG Income statements
PERIOD ENDING 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030
Sales $ 141,099,033.93 $ 143,903,868.58 $ 146,764,459.09 $ 149,681,913.79 $ 152,657,363.07 $ 155,691,959.76 $ 158,786,879.63 $ 161,943,321.81 $ 165,162,509.27 $ 168,445,689.28
Operating Expenses (excluding depreciation) $ 61,478,179.73 $ 62,700,273.64 $ 63,946,659.90 $ 65,217,822.43 $ 66,514,253.73 $ 67,836,456.13 $ 69,184,941.90 $ 70,560,233.53 $ 71,962,863.86 $ 73,393,376.36
Maintenance costs $ 14,109,903.39 $ 14,390,386.86 $ 14,676,445.91 $ 14,968,191.38 $ 15,265,736.31 $ 15,569,195.98 $ 15,878,687.96 $ 16,194,332.18 $ 16,516,250.93 $ 16,844,568.93
Depreciation $ 78,305,970.23 $ 78,305,970.23 $ 78,305,970.23 $ 78,305,970.23 $ 78,305,970.23 $ 78,305,970.23 $ 78,305,970.23 $ 78,305,970.23 $ 78,305,970.23 $ 78,305,970.23
Earnings Before Interest And Taxes $ (12,795,019.42) $ (11,492,762.14) $ (10,164,616.95) $ (8,810,070.24) $ (7,428,597.20) $ (6,019,662.58) $ (4,582,720.47) $ (3,117,214.13) $ (1,622,575.76) $ (98,226.23)
Income Tax Expense $ (1,182,028.40) $ (1,061,723.38) $ (939,026.78) $ (813,891.16) $ (686,268.04) $ (556,107.96) $ (423,360.49) $ (287,974.21) $ (149,896.66) $ (9,074.33)
Net Income $ (11,612,991.02) $ (10,431,038.76) $ (9,225,590.17) $ (7,996,179.08) $ (6,742,329.17) $ (5,463,554.62) $ (4,159,359.97) $ (2,829,239.92) $ (1,472,679.10) $ (89,151.91)
Depreciation $ 78,305,970.23 $ 78,305,970.23 $ 78,305,970.23 $ 78,305,970.23 $ 78,305,970.23 $ 78,305,970.23 $ 78,305,970.23 $ 78,305,970.23 $ 78,305,970.23 $ 78,305,970.23
ONWC $ (5,887,810.62) $ (6,004,851.36) $ (6,124,218.69) $ (6,245,958.86) $ (6,370,119.04) $ (6,496,747.34) $ (6,625,892.82) $ (6,757,605.51) $ (6,891,936.45) $ (7,028,937.69)
Change in ONWC $ (5,887,810.62) $ (117,040.74) $ (119,367.33) $ (121,740.17) $ (124,160.18) $ (126,628.30) $ (129,145.48) $ (131,712.69) $ (134,330.94) $ (137,001.24)
CapEx $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
FCF $ 2,349,179,106.94 $ 72,580,789.83 $ 67,991,972.21 $ 69,199,747.40 $ 70,431,531.33 $ 71,687,801.25 $ 72,969,043.91 $ 74,275,755.73 $ 75,608,443.00 $ 76,967,622.07 $ 78,353,819.56
Terminal value $ 2,850,336,260.73
Recommended acquisition cost $2,349,179,106.94
Premium $305,393,283.90
NPV ($305,393,283.90)
Equity value ERROR:#REF!
FPPS ERROR:#REF!
https://www.treasury.gov/resource-center/data-chart-center/interest-rates/pages/textview.aspx?data=yield

CALIFORNIA

STATE CALIFORNIA 2.5
COUNTY SAN FRANCISCO
Population (in person)
2010 2011 2012 2013 2014 2015 2016 2017 2018
805,550 815,677 829,027 839,841 851,116 863,836 872,795 879,166 883,305
Per capita personal income
2016 2017 2018
$ 114,697 $ 121,778 $ 130,696
Growth 6.17% 7.32%
Average/Trend 6.75% 8.47%
Water rates per household USD/gallon Gallons
Base rate $ 15.40 per month 168
Volume rate $ 7.81 per ccf First 4 ccf $ 0.0104 35,904 374.88
$ 10.01 per ccf Afterwards $ 0.0134
Note: Total water bill = base rate plus volume rate
1 ccf = 748 gallons
Others
Poverty Rate 10.10%
Average water usage 41 gallons per person per day
Local tax 8.50%
Population (in person)
Year 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030
Population 805,550 815,677 829,027 839,841 851,116 863,836 872,795 879,166 883,305 888,486 893,698 898,940 904,213 909,516 914,851 920,218 925,615 931,045 936,506 941,999 947,524
Population growth 1.26% 1.64% 1.30% 1.34% 1.49% 1.04% 0.73% 0.47%
Trend 0.59%
rsquare 63.09%
Households 322,220 326,271 331,611 335,936 340,446 345,534 349,118 351,666 353,322 355,394 357,479 359,576 361,685 363,807 365,941 368,087 370,246 372,418 374,602 376,800 379,010
Annual total usage 10,246,797,388 10,375,615,359 10,545,430,697 10,682,987,480 10,826,408,299 10,988,209,879 11,102,170,599 11,183,211,312 11,235,860,426 11,301,766,074 11,368,058,301 11,434,739,375 11,501,811,577 11,569,277,201 11,637,138,555 11,705,397,961 11,774,057,752 11,843,120,277 11,912,587,900 11,982,462,995 12,052,747,953
Ann. HH usage 31,800.63 31,800.63 31,800.63 31,800.62 31,800.62 31,800.62 31,800.63 31,800.62 31,800.63 31,800.62 31,800.63 31,800.63 31,800.63 31,800.63 31,800.63 31,800.63 31,800.63 31,800.62 31,800.63 31,800.62 31,800.63
Ann. Revenue/HH 487.97 487.97 487.97 487.97 487.97 487.97 487.97 487.97 487.97 487.97 487.97 487.97 487.97 487.97 487.97 487.97 487.97 487.97 487.97 487.97 487.97
Ann. Revenue/HH below PL 243.98 243.98 243.98 243.98 243.98 243.98 243.98 243.98 243.98 243.98 243.98 243.98 243.98 243.98 243.98 243.98 243.98 243.98 243.98 243.98 243.98
Ann. Total above PL 141,352,275.65 143,129,290.73 145,471,855.29 147,369,420.32 149,347,878.40 151,579,894.97 153,151,957.58 154,269,896.07 154,996,178.82 155,905,332.47 156,819,818.89 157,739,669.38 158,664,915.39 159,595,588.57 160,531,720.76 161,473,343.97 162,420,490.42 163,373,192.49 164,331,482.79 165,295,394.08 166,264,959.35
Ann. Total below PL 7,940,255.75 8,040,076.95 8,171,667.07 8,278,259.98 8,389,396.95 8,514,777.19 8,603,085.49 8,665,884.04 8,706,681.90 8,757,752.27 8,809,122.20 8,860,793.44 8,912,767.77 8,965,046.97 9,017,632.81 9,070,527.11 9,123,731.66 9,177,248.30 9,231,078.84 9,285,225.14 9,339,689.04
Ann. Total revenue 149,292,531.40 151,169,367.69 153,643,522.35 155,647,680.30 157,737,275.35 160,094,672.16 161,755,043.07 162,935,780.11 163,702,860.72 164,663,084.73 165,628,941.09 166,600,462.82 167,577,683.16 168,560,635.54 169,549,353.57 170,543,871.08 171,544,222.08 172,550,440.79 173,562,561.63 174,580,619.22 175,604,648.39

Pro Forma_California

Value Drivers WTRG WACC calculation
Acquisition Cost $2,008,191,580.61
Premium 17% Debt Average value $2,161,519,200
Directly related to sales Cost of debt 4.66%
Operating Expenses (excluding depreciation) 43.57%
Operating Current Assets 16.97% Equity # Shares outstanding 220,758,719
Operating Current Liabilities 21.14% Current price per share $38.12
Capital Expenditures 0.00% <-- too high? Lower to 50% of past Market value $8,415,322,368
Maintenance Costs 20% Cost of equity 4.95%
Not directly related to sales Beta 0.54
Depreciation Rate 2.01% Rf 0.20%
Interest Rate on Debt 4.66% Rm 9.00%
Interest Rate on ST Investments 0.20% <-- 1 Yr treasry bill rate (as of 3/20/2020)
Dividend payout 88.35% https://www.treasury.gov/resource-center/data-chart-center/interest-rates/pages/textview.aspx?data=yield WACC 4.74%
Tax Rate 15.74%
Long-term growth rate 2%
k 4.74%
WTRG Income statements
PERIOD ENDING 2020 2021 2022 2023 2024 2025 2026 2027 2028 2029 2030
Sales $ 166,600,462.82 $ 167,577,683.16 $ 168,560,635.54 $ 169,549,353.57 $ 170,543,871.08 $ 171,544,222.08 $ 172,550,440.79 $ 173,562,561.63 $ 174,580,619.22 $ 175,604,648.39
Operating Expenses (excluding depreciation) $ 72,589,392.79 $ 73,015,176.91 $ 73,443,458.53 $ 73,874,252.30 $ 74,307,572.95 $ 74,743,435.32 $ 75,181,854.30 $ 75,622,844.90 $ 76,066,422.19 $ 76,512,601.35
Maintenance costs $ 33,320,092.56 $ 33,515,536.63 $ 33,712,127.11 $ 33,909,870.71 $ 34,108,774.22 $ 34,308,844.42 $ 34,510,088.16 $ 34,712,512.33 $ 34,916,123.84 $ 35,120,929.68
Depreciation $ 66,939,719.35 $ 66,939,719.35 $ 66,939,719.35 $ 66,939,719.35 $ 66,939,719.35 $ 66,939,719.35 $ 66,939,719.35 $ 66,939,719.35 $ 66,939,719.35 $ 66,939,719.35
Earnings Before Interest And Taxes $ (6,248,741.88) $ (5,892,749.73) $ (5,534,669.45) $ (5,174,488.79) $ (4,812,195.44) $ (4,447,777.01) $ (4,081,221.02) $ (3,712,514.94) $ (3,341,646.16) $ (2,968,602.00)
Income Tax Expense $ (983,438.97) $ (927,412.24) $ (871,056.88) $ (814,370.96) $ (757,352.54) $ (699,999.66) $ (642,310.38) $ (584,282.71) $ (525,914.67) $ (467,204.27)
Net Income $ (5,265,302.92) $ (4,965,337.49) $ (4,663,612.57) $ (4,360,117.84) $ (4,054,842.91) $ (3,747,777.34) $ (3,438,910.64) $ (3,128,232.23) $ (2,815,731.49) $ (2,501,397.73)
Depreciation $ 66,939,719.35 $ 66,939,719.35 $ 66,939,719.35 $ 66,939,719.35 $ 66,939,719.35 $ 66,939,719.35 $ 66,939,719.35 $ 66,939,719.35 $ 66,939,719.35 $ 66,939,719.35
ONWC $ (6,951,939.69) $ (6,992,717.35) $ (7,033,734.19) $ (7,074,991.63) $ (7,116,491.07) $ (7,158,233.93) $ (7,200,221.64) $ (7,242,455.63) $ (7,284,937.36) $ (7,327,668.26)
Change in ONWC $ (6,951,939.69) $ (40,777.66) $ (41,016.85) $ (41,257.44) $ (41,499.44) $ (41,742.86) $ (41,987.71) $ (42,233.99) $ (42,481.72) $ (42,730.91)
CapEx $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0 $ - 0
FCF $2,008,191,580.61 $ 68,626,356.12 $ 62,015,159.52 $ 62,317,123.63 $ 62,620,858.95 $ 62,926,375.88 $ 63,233,684.87 $ 63,542,796.42 $ 63,853,721.12 $ 64,166,469.59 $ 64,481,052.53
Terminal value $ 2,398,603,094.95
Recommended acquisition cost $2,008,191,580.61
Premium $ 341,392,568.70
NPV ($341,392,568.70)
https://www.treasury.gov/resource-center/data-chart-center/interest-rates/pages/textview.aspx?data=yield

Balance Sheet

WTRG BALANCE SHEET
As of Dec-19 Dec-18 Dec-17 Dec-16 Dec-15 Dec-20 Dec-50
ASSETS
Current Assets
Cash and Cash Equivalents $ 1,868,922,000 $ 3,627,000 $ 4,204,000 $ 3,763,000 $ 3,229,000
Inventories $ 18,379,000 $ 15,844,000 $ 14,361,000 $ 12,961,000 $ 12,414,000
Trade and Other Receivables, Current $ 107,620,000 $ 101,225,000 $ 98,596,000 $ 97,394,000 $ 99,146,000
Other Current Assets $ 17,817,000 $ 26,476,000 $ 14,085,000 $ 14,532,000 $ 13,581,000
Total Current Assets $ 2,012,738,000 $ 147,172,000 $ 131,246,000 $ 128,650,000 $ 128,370,000
Long-term Investments
Net Property, Plant and Equipment $ 6,345,790,000 $ 5,930,326,000 $ 5,399,860,000 $ 5,001,615,000 $ 4,688,925,000
Other Non-Current Assets $ 1,003,457,000 $ 886,998,000 $ 801,357,000 $ 1,028,726,000 $ 900,578,000
Total Assets $ 9,361,985,000 $ 6,964,496,000 $ 6,332,463,000 $ 6,158,991,000 $ 5,717,873,000
LIABILITIES
Current Liabilities
Payables and Accrued Expenses, Current $ 177,130,000 $ 170,254,000 $ 145,440,000 $ 131,714,000 $ 140,885,000
Current Debt $ 141,719,000 $ 228,723,000 $ 139,048,000 $ 169,822,000 $ 52,314,000
Total Current Liabilities $ 318,849,000 $ 398,977,000 $ 284,488,000 $ 301,536,000 $ 193,199,000
Long Term Debt $ 2,943,327,000 $ 2,398,464,000 $ 2,007,753,000 $ 1,737,605,000 $ 1,720,447,000
Other Long Term Liabilities $ 2,218,949,000 $ 2,157,691,000 $ 2,082,601,000 $ 2,269,782,000 $ 2,078,297,000
Total Liabilities $ 5,481,125,000 $ 4,955,132,000 $ 4,374,842,000 $ 4,308,923,000 $ 3,991,943,000
STOCKHOLDER'S EQUITY
Common Stock $ 34,233,000 $ 14,741,000 $ 17,070,000 $ 19,042,000 $ 21,597,000
Retained Earnings $ 1,210,072,000 $ 1,174,245,000 $ 1,133,416,000 $ 1,033,513,000 $ 930,748,000
Capital Surplus $ 2,636,555,000 $ 820,378,000 $ 807,135,000 $ 797,513,000 $ 773,585,000
Total Stockholder Equity $ 3,880,860,000 $ 2,009,364,000 $ 1,957,621,000 $ 1,850,068,000 $ 1,725,930,000
Total Liabilities & Equities $ 9,361,985,000 $ 6,964,496,000 $ 6,332,463,000 $ 6,158,991,000 $ 5,717,873,000
Operating current assets $ 143,816,000 $ 143,545,000 $ 127,042,000 $ 124,887,000 $ 125,141,000
As a % of sales 16.16% 17.13% 15.69% 15.23% 15.37%
Trend 16.97%
Operating current liabilities $ 177,130,000 $ 170,254,000 $ 145,440,000 $ 131,714,000 $ 140,885,000
As a % of sales 19.91% 20.31% 17.97% 16.07% 17.30%
Trend 21.14%
Gross Property, Plant and Equipment $ 7,648,469,000.00 $ 7,003,993,000.00 $ 6,509,117,000.00 $ 6,088,011,000.00
Depreciation rate 1.92% 1.95% 2.04% 2.11%
Average 2.01%
Trend 1.77%

Income Statement

WTRG INCOME STATEMENTS
PERIOD ENDING Dec-19 Dec-18 Dec-17 Dec-16 Dec-15
Total Revenue $ 889,692,000 $ 838,091,000 $ 809,525,000 $ 819,875,000 $ 814,204,000
Cost of Revenue $ 333,102,000 $ 308,478,000 $ 282,253,000 $ 297,184,000 $ 309,310,000
Gross Profit $ 556,590,000 $ 529,613,000 $ 527,272,000 $ 522,691,000 $ 504,894,000
Selling, General and Administrative Expenses $ 59,955,000 $ 59,762,000 $ 56,628,000 $ 56,385,000 $ 55,057,000
Operating Income (Loss) $ 496,635,000 $ 469,851,000 $ 470,644,000 $ 466,306,000 $ 449,837,000
Other Income/Expense $ (156,476,000) $ (146,673,000) $ (136,724,000) $ (133,008,000) $ (128,737,000)
Earnings Before Interest And Taxes $ 340,159,000 $ 323,178,000 $ 333,920,000 $ 333,298,000 $ 321,100,000
Interest income (expense) $ 128,633,000 $ 144,859,000 $ 77,268,000 $ 78,138,000 $ 104,348,000
Income Before Tax $ 211,526,000 $ 178,319,000 $ 256,652,000 $ 255,160,000 $ 216,752,000
Income Tax Expense $ (13,017,000) $ (13,669,000) $ 16,914,000 $ 20,978,000 $ 14,962,000
Net Income from Continuing Operations $ 224,543,000 $ 191,988,000 $ 239,738,000 $ 234,182,000 $ 201,790,000
Number of common shares outstanding 220,758,719 178,091,621 177,713,943 177,394,376 176,544,091
Annual sales growth 6.16% 3.53% -1.26% 0.70%
Trend 7.55%
Operating expenses (excluding depreciation) $ 393,057,000 $ 368,240,000 $ 338,881,000 $ 353,569,000 $ 364,367,000
Slope 0.55
Intercetp $ (99,367,186.70)
R2 0.83 Too low value to accept Slope/Intercept as valid value drivers
% of revenue 44.18% 43.94% 41.86% 43.12% 44.75%
Average 43.57%
Interest rate on debt 4.50% 6.07% 3.81% 4.25%
Average 4.66%
Tax rate 6.59% 8.22% 6.90%
Average 7.24%

Cash Flow Statement

WTRG STATEMENT OF CASH FLOWS
PERIOD ENDING Dec-19 Dec-18 Dec-17 Dec-16 Dec-15
Net Income from Continuing Operations $ 224,543,000 $ 191,988,000 $ 239,738,000 $ 234,182,000 $ 201,790,000
Operating Activities, Cash Flows Provided By or Used In
Depreciation $ 156,476,000 $ 146,673,000 $ 136,724,000 $ 133,008,000 $ 128,737,000
Stock-Based Compensation, Non-Cash Adjustment $ 7,368,000 $ 7,567,000 $ 6,342,000 $ 5,390,000 $ 5,860,000
Taxes, Non-Cash Adjustment $ (10,436,000) $ (14,950,000) $ 13,780,000 $ 17,250,000 $ 16,506,000
Other Non-Cash Items $ (37,272,000) $ 64,370,000 $ 5,276,000 $ 4,383,000 $ 39,796,000
Change in Trade and Other Receivables $ (4,335,000) $ (18,024,000) $ (6,458,000) $ (3,974,000) $ (6,520,000)
Change in Payables and Accrued Expenses $ 5,108,000 $ 567,000 $ (763,000) $ 4,756,000 $ (3,469,000)
Other Operating Cash Flow $ (2,929,000) $ (9,669,000) $ (13,321,000) $ 1,168,000 $ (11,906,000)
Total Cash Flow From Operating Activities $ 338,523,000 $ 368,522,000 $ 381,318,000 $ 396,163,000 $ 370,794,000
Investing Activities, Cash Flows Provided By or Used In
Purchase of Property, Plant and Equipment $ (550,273,000) $ (495,737,000) $ (478,089,000) $ (382,996,000) $ (364,689,000)
Purchase/Sale of Business, Net $ (56,794,000) $ (144,977,000) $ (4,518,000) $ (1,677,000) $ (28,341,000)
Other Investing Cash Flow $ 2,464,000 $ 899,000 $ 2,223,000 $ 1,464,000 $ (1,032,000)
Total Cash Flows From Investing Activities $ (604,603,000) $ (639,815,000) $ (480,384,000) $ (383,209,000) $ (394,062,000)
Financing Activities, Cash Flows Provided By or Used In
Issuance of/Payments for Common Stock, Net $ 1,943,833,000 $ 2,608,000 $ (714,000) $ (1,640,000) $ (24,570,000)
Issuance of/Repayments for Debt, Net $ 371,073,000 $ 429,542,000 $ 229,071,000 $ 120,313,000 $ 158,460,000
Cash Dividends Paid $ (188,512,000) $ (150,736,000) $ (140,660,000) $ (130,923,000) $ (121,248,000)
Excess Tax Benefit from Share-Based Compensation, Financing Activities $ 3,083,000 $ (12,157,000) $ 8,937,000 $ (5,387,000) $ 335,000
Other Financing Cash Flow $ 1,898,000 $ 1,459,000 $ 2,873,000 $ 5,217,000 $ 9,382,000
Total Cash Flows From Financing Activities $ 2,131,375,000 $ 270,716,000 $ 99,507,000 $ (12,420,000) $ 22,359,000
Cash and Cash Equivalents, End of Period $ 1,868,922,000 $ 3,627,000 $ 4,204,000 $ 3,763,000 $ 3,229,000
Change in Cash $ 1,865,295,000 $ (577,000) $ 441,000 $ 534,000 $ (909,000)
Cash and Cash Equivalents, Beginning of Period $ 3,627,000 $ 4,204,000 $ 3,763,000 $ 3,229,000 $ 4,138,000
Dividend payout 83.95% 78.51% 58.67% 55.91% 60.09%
Trend 88.35%
CapEx $ 550,273,000 $ 495,737,000 $ 478,089,000 $ 382,996,000 $ 364,689,000
As a % of sales 61.85% 59.15% 59.06% 46.71% 44.79%
Trend 68.25%