5Assignment: Strategic Financial Analysis

MrsLovingLife2016
cf_week_5_assignment_spreadsheet1.xlsx
Home>Human Resource Management homework help>5Assignment: Strategic Financial Analysis

Balance Sheets

Stanford Health Care Consolidated Statements of Operations and Changes in Net Assets Years Ending August 31, 2015, 2016, 2017, and 2018
Common Size Balance Sheets Years Ending August 31, 2015, 2016, 2017, and 2018
Assets % of Change between 2018 & 2017 2018 ($) % of Change between 2017 & 2016 2017 ($) % of Change between 2016 & 2015 2016 ($) 2015 ($) 2018 (%) 2017 (%) 2016 (%) 2015 (%)
Current assets:
Cash and cash equivalents -8% 652,256 3% 710,109 45% 690,460 475,677 9.0% 11.4% 12.0% 8.6%
Short term investments 68% 391,314 125% 233,533 2% 103,627 101,677 5.4% 3.7% 1.8% 1.8%
Patient accounts receivable, net of allowance for doubtful accounts 2% 623,077 9% 610,734 2% 559,933 550,721 8.6% 9.8% 9.7% 10.0%
Other receivables 11% 79,036 -24% 71,112 23% 92,961 75,427 1.1% 1.1% 1.6% 1.4%
Inventories 4% 58,884 13% 56,559 16% 50,016 42,935 0.8% 0.9% 0.9% 0.8%
Prepaid expenses and other 24% 52,886 17% 42,528 2% 36,273 35,486 0.7% 0.7% 0.6% 0.6%
Total current assets 8% 1,857,453 12% 1,724,575 20% 1,533,270 1,281,923 25.7% 27.7% 26.6% 23.2%
Investments 357% 509,781 -16% 111,664 3% 132,273 127,860 7.1% 1.8% 2.3% 2.3%
Investments at equity 22% 80,989 66,255 1.1% 1.1% 0.0% 0.0%
Investments in company managed pools 9% 1,400,839 -2% 1,287,193 -9% 1,316,489 1,440,352 19.4% 20.7% 22.9% 26.1%
Assets limited as to use, held by trustee -100% -75% 58,134 -59% 235,788 580,701 0.0% 0.9% 4.1% 10.5%
Property and equipment, net 14% 3,279,048 19% 2,869,346 25% 2,401,880 1,923,465 45.4% 46.1% 41.7% 34.9%
Other assets -23% 86,739 -18% 112,445 -16% 137,637 163,578 1.2% 1.8% 2.4% 3.0%
Total assets 16% 7,214,849 8% 6,229,612 4% 5,757,337 5,517,879 100.0% 100.0% 100.0% 100.0%
Liabalities and net assets % of Change between 2018 & 2017 2018 % of Change between 2018 & 2017 2017 % of Change between 2018 & 2017 2016 2015 2018 2017 2016 2015
Current liabilities:
Accounts payable and accrued liabilities 46% 449,192 -8% 307,899 19.1% 335,995 282,134 6.2% 4.9% 5.8% 5.1%
Accrued salaries and related benefits -18% 209,490 8% 255,759 16.7% 236,819 202,859 2.9% 4.1% 4.1% 3.7%
Due to related parties 39% 98,942 17% 71,429 41.5% 61,308 43,324 1.4% 1.1% 1.1% 0.8%
Third-party payor settlements 90% 34,474 -21% 18,149 154.5% 22,948 9,018 0.5% 0.3% 0.4% 0.2%
Current portion of long-term debt 9% 14,505 -3% 13,335 -1.3% 13,756 13,932 0.2% 0.2% 0.2% 0.3%
Revolving line of credit -100% ERROR:#DIV/0! 135,000 ERROR:#DIV/0! 0.0% 2.2% 0.0% 0.0%
Debt subject to short-term remarketing arrangements 0% 228,200 0% 228,200 0.0% 228,200 228,200 3.2% 3.7% 4.0% 4.1%
Self-insurance reserves and other 20% 54,933 6% 45,854 23.8% 43,232 34,918 0.8% 0.7% 0.8% 0.6%
Total current liabilities 1% 1,089,736 14% 1,075,625 15.7% 942,258 814,385 15.1% 17.3% 16.4% 14.8%
Self-insurance reserves and others, net of current portion 7% 139,841 10% 130,816 -1.1% 118,994 120,364 1.9% 2.1% 2.1% 2.2%
Swap liability -26% 182,527 ERROR:#DIV/0! 245,966 ERROR:#DIV/0! 2.5% 3.9% 0.0% 0.0%
Other long-term liabilities 292% 122,944 -91% 31,363 51.4% 355,683 234,855 1.7% 0.5% 6.2% 4.3%
Pension liability -87% 6,650 -21% 51,745 27.8% 65,463 51,220 0.1% 0.8% 1.1% 0.9%
Long-term debt, net of current portion 44% 1,711,967 -3% 1,189,529 -1.3% 1,220,789 1,237,347 23.7% 19.1% 21.2% 22.4%
Total liabilities 19% 3,253,665 1% 2,725,044 10.0% 2,703,187 2,458,171 45.1% 43.7% 47.0% 44.5%
Net assets:
Unrestricted:
Stanford Health Care 14% 3,285,398 17% 2,871,113 -0.7% 2,449,037 2,467,393 45.5% 46.1% 42.5% 44.7%
Nonconrolling interests -15% 18,727 10% 22,060 -12.4% 20,133 22,979 0.3% 0.4% 0.3% 0.4%
Total unrestricted 14% 3,304,125 17% 2,893,173 -0.9% 2,469,170 2,490,372 45.8% 46.4% 42.9% 45.1%
Temporarily restricted 8% 648,826 5% 603,251 2.7% 577,086 561,642 9.0% 9.7% 10.0% 10.2%
Permanently restricted 1% 8,233 3% 8,144 2.6% 7,894 7,694 0.1% 0.1% 0.1% 0.1%
Total net assets 13% 3,961,184 15% 3,504,568 -0.2% 3,054,150 3,059,708 54.9% 56.3% 53.0% 55.5%
Total liabilities and net assets 16% 7,214,849 8% 6,229,612 4.3% 5,757,337 5,517,879 100.0% 100.0% 100.0% 100.0%

Income Statements

Stanford Health Care Consolidated Statements of Operations and Changes in Net Assets Years Ending August 31, 2015, 2016, 2017, and 2018
Common Size Income Statements Years Ending August 31, 2015, 2016, 2017, and 2018
% of Change between 2018 & 2017 2018 ($) % of Change between 2017 & 2016 2017 ($) % of Change between 2016 & 2015 2016 ($) 2015 ($) 2018 (%) 2017 (%) 2016 (%) 2015 (%)
Operating revenues:
Net patient service revenue 10% 4,735,366.00 7% 4,311,530.00 14% 4,019,285.00 3,525,014.00 96.4% 97% 98% 99%
Provision for doubtful accounts -25% (57,437.00) -39% (77,004.00) -4% (126,280.00) (131,601.00) -1.2% -2% -3% -4%
Net patient service revenue less provision for doubtful accounts 10% 4,677,929.00 9% 4,234,526.00 15% 3,893,005.00 3,393,413.00 95.3% 95% 95% 95%
Premium revenue 15% 92,654.00 12% 80,647.00 15% 72,292.00 62,893.00 1.9% 2% 2% 2%
Other revenue 5% 135,597.00 5% 129,324.00 25% 122,996.00 98,718.00 2.8% 3% 3% 3%
Net assets released from restrictions used for operations -56% 4,366.00 6% 9,904.00 -40% 9,372.00 15,663.00 0.1% 0% 0% 0%
Total operating revenues 10% 4,910,546.00 9% 4,454,401.00 15% 4,097,665.00 3,570,687.00 100.0% 100% 100% 100%
ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Operating expenses: ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Salaries and benefits 5.3% 2,091,260.00 7.4% 1,986,360.00 29.6% 1,850,124.00 1,428,100.00 42.6% 45% 45% 40%
Professioanl services 7.7% 46,146.00 -14.0% 42,851.00 4.3% 49,846.00 47,801.00 0.9% 1% 1% 1%
Supplies 13.9% 667,379.00 10.3% 586,056.00 9.7% 531,130.00 484,036.00 13.6% 13% 13% 14%
Purchased services 7.1% 1,216,992.00 7.4% 1,136,020.00 15.9% 1,058,182.00 912,886.00 24.8% 26% 26% 26%
Depreciation and amortization 14.3% 176,742.00 13.4% 154,686.00 24.3% 136,442.00 109,735.00 3.6% 3% 3% 3%
Interest -18.8% 35,434.00 10.0% 43,643.00 -2.0% 39,661.00 40,485.00 0.7% 1% 1% 1%
Other 24.3% 477,661.00 -1.2% 384,354.00 8.3% 389,199.00 359,368.00 9.7% 9% 9% 10%
Expense recoveries from related parties 7.3% (121,727.00) 8.1% (113,451.00) 12.1% (104,965.00) (93,640.00) -2.5% -3% -3% -3%
Total operating expenses 8.8% 4,589,887.00 6.9% 4,220,519.00 20.1% 3,949,619.00 3,288,771.00 93.5% 95% 96% 92%
Income from operations 37.1% 320,659.00 58.0% 233,882.00 -47.5% 148,046.00 281,916.00 6.5% 5% 4% 8%
Interest and investment income 103.1% 31,122.00 12.4% 15,325.00 -13.0% 13,635.00 15,680.00 0.6% 0% 0% 0%
Earnings on equity method investments 37.8% 7,048.00 ERROR:#DIV/0! 5,114.00 ERROR:#DIV/0! 0.1% 0%
Increase in value of company managed pools -23.4% 110,984.00 498.9% 144,829.00 -55.5% 24,181.00 54,309.00 2.3% 3% 1% 2%
Interest rate swaps mark to market adjustments ERROR:#DIV/0! -100.0% 95.2% (115,958.00) (59,392.00) -3% -2%
Swap interest and change in value of swap agreements -43.7% 48,043.00 ERROR:#DIV/0! 85,368.00 ERROR:#DIV/0! 1.0% 2%
Loss on extinguishment of debt ERROR:#DIV/0! (47,613.00) ERROR:#DIV/0! -100.0% (35.00) -1.0% -0%
Contribution income from Stanford Health Care-ValleyCare affiliation ERROR:#DIV/0! ERROR:#DIV/0! -100.0% 96,758.00 3%
Excess of revenues over expenses -2.9% 470,243.00 593.1% 484,518.00 -82.0% 69,904.00 389,236.00 9.6% 11% 2% 11%
Other change in unrestricted net assets: ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Tranfer to Stanford University, net 41.5% (98,183.00) -22.0% (69,376.00) 33.8% (88,944.00) (66,477.00) -2.0% -2% -2% -2%
Transfer (to) from Lucile Salter Packard Children's Hospital ERROR:#DIV/0! 2,068.00 -100.0% -112.4% (3,300.00) 26,600.00 0.0% -0% 1%
Change in net unrealized gains on investments 792.1% 9,438.00 -15.0% 1,058.00 -150.9% 1,245.00 (2,445.00) 0.2% 0% 0% -0%
Net assets released from restrictions used for: ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Purchase of property and equipment -76.6% 309.00 35.7% 1,320.00 -57.5% 973.00 2,288.00 0.0% 0% 0% 0%
Change in pension and postretirement liability 357.4% 28,277.00 -7827.5% 6,182.00 -99.6% (80.00) (19,461.00) 0.6% 0% -0% -1%
Noncontrolling capital distribution, net -498.7% (1,200.00) -130.1% 301.00 1512.9% (1,000.00) (62.00) -0.0% 0% -0% -0%
(Decrease) increase in unrestricted net assets -3.1% 410,952.00 -2099.8% 424,003.00 -106.4% (21,202.00) 329,679.00 8.4% 10% -1% 9%
Changes in temporary restricted net assets: ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Transfer from Stanford University -20.8% 2,177.00 3.9% 2,748.00 -34.9% 2,645.00 4,062.00 0.0% 0% 0% 0%
Contributions and other 57.3% 44,894.00 37.8% 28,541.00 -60.4% 20,717.00 52,333.00 0.9% 1% 1% 1%
Contribution income from Stanford Health Care-ValleyCare affiliation ERROR:#DIV/0! ERROR:#DIV/0! -100.0% 62.00 0%
Investment income -84.7% 712.00 526.6% 4,662.00 -55.4% 744.00 1,667.00 0.0% 0% 0% 0%
Gains on company managed pools 71.6% 2,467.00 -14.6% 1,438.00 -33.7% 1,683.00 2,537.00 0.1% 0% 0% 0%
Net assets released from restrictions used for: ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Operations -55.9% (4,366.00) 5.7% (9,904.00) -40.2% (9,372.00) (15,663.00) -0.1% -0% -0% -0%
Purchase of property and equipment -76.6% (309.00) 35.7% (1,320.00) -57.5% (973.00) (2,288.00) -0.0% -0% -0% -0%
Increase in temporary restricted net assets 74.2% 45,575.00 69.4% 26,165.00 -63.8% 15,444.00 42,710.00 0.9% 1% 0% 1%
Changes in permanently restricted net assets: ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
Contributions -64.4% 89.00 25.0% 250.00 9900.0% 200.00 2.00 0.0% 0% 0% 0%
Increase in permanently restricted net assets -64.4% 89.00 25.0% 250.00 9900.0% 200.00 2.00 0.0% 0% 0% 0%
(Decrease) increase in net assets 1.4% 456,616.00 -8204.0% 450,418.00 -101.5% (5,558.00) 372,391.00 9.3% 10% -0% 10%
Net assets, beginning of year 14.7% 3,504,568.00 -0.2% 3,054,150.00 13.9% 3,059,708.00 2,687,317.00 71.4% 69% 75% 75%
Net assets, end of year 13.0% 3,961,184.00 14.7% 3,504,568.00 -0.2% 3,054,150.00 3,059,708.00 80.7% 79% 75% 86%