Excel-Based Problems 12-9. As of January 1, 2017, the trial balance for Haven Hospital was as follows: yo Debits Credits $ 830,000 3,250,000 $ 650.000 2,480,000 353,000 Cash Patient Accounts Receivable Allowance for Uncollectible Patient Accounts Receivable Contributions Receivable Allowance for Uncollectible Contributions Receivable Supplies Investments-Board Designated Investments-Other Property, Plant, and Equipment Accumulated Depreciation- Property, Plant, and Equipment Accounts Payable Long-Term Debt-Current Installment Long-Term Debt-Noncurrent Net Assets-Unrestricted-Board Designated Net Assets-Unrestricted-Undesignated Net Assets-Temporarily Restricted Net Assets-Permanently Restricted Totals 130,000 1,700,000 10,100,000 7,500,000 4,600,000 600,000 200,000 3,100,000 1,700,000 1,644,000 6,136,000 7,007,000 $25,990,000 $25,990,000 During the fiscal year ended December 31, 2017, the following transactions occurred: 1. Patient service revenue amounted to $20,990,000, all recorded on account. Contractual adjustments were recorded in the amount of $3,800,000. Uncollectible accounts are estimated to be $620,000. Cash was received on account in the amount of $17,600,000. 366 Chapter 12 2. Other revenue (cafeteria, parking lot, etc.) amounted to $2,580,000, all received in cash. 3. Patient accounts in the amount of $430,000 were written off. 4. Unrestricted gifts and bequests were received in cash in the amount of $317,000. Unrestricted income on investments of endowment funds amounted to $400,000. (It is the hospital's practice to treat unrestricted gifts as nonoperating revenue.) 5. Investment income on board-designated funds, which is limited by board policy to provide renewals and replacements, amounted to $95,000 and was received in cash. Do not increase board-designated net assets at this stage, but close out the revenue account to board-designated net assets in entry 19. 6. Investment income, restricted for donor-specified purposes, was received in cash in the amount of $250,000. Investment income, required by donor agreement to be added to endowment balances, was received in cash in the amount of $100,000. 7. Cash contributions were received in the following amounts: $2,001,000 for current restricted purposes; $2,450,000 for future plant expansion; and $1,050,000 required by the donor to be invested permanently in an endowment. 8. Pledges receivable in the amount of $2,100,000 were received in cash. These pledges were on hand at the beginning of the year (reflected in temporarily restricted net assets, for purposes of time) and were unre- stricted as to purpose. In addition, pledges for endowment purposes were collected in the amount of $450,000. 9. $1,550,000 in temporarily restricted net assets was expended, as the donors stipulated, for cancer research. Debit Operating Expense- Salaries and Benefits, $1,400,000; and Operating ExpenseSupplies, $150,000. (Assume the supplies were purchased with cash and used in the same year.) 10. $1,970,000 in temporarily restricted net assets was expended for equip- ment, as provided for by the donor. The policy of Haven Hospital is to record all property, plant, and equipment as unrestricted. 11. In addition, $600,000 was received in pledges for temporarily restricted purposes. It was decided that the allowance for contributions was sufficient. 12. Supplies were purchased in the amount of $690,000, on account. 13. Operating expenses (in addition to those already recorded in entries 1 and 9) for the year included: depreciation of $600,000; supplies used of $687,000; and salaries and benefits of $20,985,000 (paid in cash). In addition, the following expenses were recorded through Accounts Payable: utilities of $515,000 and insurance of $320,000. 14. Accounts payable were paid in the amount of $1,767,000. 15. Current installments of long-term debt were paid in the amount of $200,000. The portion to be paid next year is $300,000. Interest was paid in the amount of $181,000 and is reported as an operating expense. 16. Investments, carried at a basis of $4,000,000, were sold for $4,050,000. The $50,000 gain is considered to be temporarily restricted. 17. Cash in the amount of $6,800,000 was invested. Of that amount, $95,000 was from CashAssets Whose Use Is Limited and is designated by the board for renewals and replacements (see entry 5). 18. A reading of the financial press indicated that investments increased in mar- ket value by $800,000. Of that amount, $250,000 was in investments desig- nated by the board for renewals and replacements, $350,000 is required by donors to be added to endowment balances, and the remainder is unrestricted. 19. Closing entries were prepared. Required: a. Using the Excel template provided, prepare journal entries for each of the previous transactions. b. Prepare a Statement of Operations for Haven Hospital for the year ended December 31, 2017. c. Prepare a Statement of Changes in Net Assets for Haven Hospital for the year ended December 31, 2017. d. Prepare a Statement of Financial Position for Haven Hospital as of December 31, 2017. e. Prepare a Statement of Cash Flows for Haven Hospital for the year ended December 31, 2017, using the indirect method. D HAVEN HOSPITAL STATEMENT OF OPERATIONS for the year ended June 30 2017 Unrestricted Revenues: Patient Service Revenues -- Net of Contractual Adjustments & Provision for Bad Debts Other Operating Revenues Total Revenues s . 1 2 Net Assets Released From Restrictions: Expiration of Time Restrictions Satisfaction of Program Restrictions 3 Total Net Assets Released From Restrictions Total Operating Revenues Expenses Salaries and Benefits 7 Supplies 3 Utilities Insurance Depreciation Interest 2 Total Operating Expenses 3 4 Operating Loss 1 5 Other Income Unrestricted Gifts and Bequests 3 Income on Endowment Investments Income on Assets Whose Use is Limited Net Realized and Unrealized Gains on Investments - Undesignated Net Realized and Unrealized Gains on Investments - Board 2 Total Other Income 3 Excess of Revenues over Expenses 4 Net Assets Released From Restrictions: 5 Satisfaction of Property Plant and Equipment Restrictions = Increase in Unrestricted Net Assets S 7 HAVEN HOSPITAL STATEMENT OF CHANGES IN NET ASSETS for the year ended June 30 2017 Unrestricted Net Assets: Excess of Revenues over Expenses Net Assets Released From Restrictions: Satisfaction of Property Plant and Equipment Restrictions Increase in Unrestricted Net Assets - Temporarily Restricted Net Assets: Contribution Revenue Net Realized and Unrealized Gains on Investments Other Investment Income Net Assets Released From Restrictions Increase Decrease) in Temporarily Restricted Net Assets Permanently Restricted Net Assets: Contributions Gains on Long-Term Investments Investment Income on Endowment Increase in Permanently Restricted Net Assets - Increase in Net Assets Net Assets, Beginning of year Net Assets, End of year $ - STATEMENT OF FINANCIAL POSITION AS OF JUNE 30, 2017 $ ASSETS CURRENT ASSETS Cash and Cash Equivalents Patient Accounts Receivable (Net of Allowance for Doubtful Contributions Receivable (Net of Allowance for Uncollectible) Supplies Total Current Assets NONCURRENT ASSETS Investments Whose Use is Limited Long-Term Investments Property, Plant, and Equipment Less: Accumulated Depreciation Total Assets S LIABILITIES AND NET ASSETS CURRENT LIABILITIES Accounts Payable Long-Term Debt: Current Installment Total Current Liabilities Long-term Liabilities Total Liabilities Net Assets: Board Designated Other Unrestricted Total Unrestricted Temporarily Restricted Permanently Restricted Total Net Assets Total Liabilities and Net Assets $ STATEMENT OF CASH FLOWS for the year ended June 30 2017 $ Cash Flows From Operating Activities: Change in Net Assets Adjustments to Reconcile Change in Net Assets to Net Cash Provided by Operating Activities: Depreciation Decrease in Patient Receivable (Net of Allowance) Increase in Supplies Decrease in Contributions Receivable (Net of Allowance) Increase in Accounts Payable Gains on Long-Term Investments Contributions Restricted to Investment in Property, Plant and Contributions Restricted to Long-Term Investment in Endowments Investment Income Restricted to Endowment Cash Flows From Operating Activities Cash Flows From Investing Activities: Acquisition of Property, Plant and Equipment Purchase of Investments Sale of Investments Cash Flows From Investing Activities Cash Flows From Financing Activities: Proceeds From Contributions Restricted for. Investment in Property, Plant, and Equipment Investment in Endowments Income Restricted to Endowment Payments on Long-Term Debt Cash Flows From Financing Activities Net Increase (Decrease) in Cash and Cash Equivalents Cash and Cash Equivalents, beginning of year Cash and Cash Equivalents, end of year $ Supplemental Disclosure of Cash Flow Information: - Cash Paid for Interest $ Excel-Based Problems 12-9. As of January 1, 2017, the trial balance for Haven Hospital was as follows: yo Debits Credits $ 830,000 3,250,000 $ 650.000 2,480,000 353,000 Cash Patient Accounts Receivable Allowance for Uncollectible Patient Accounts Receivable Contributions Receivable Allowance for Uncollectible Contributions Receivable Supplies Investments-Board Designated Investments-Other Property, Plant, and Equipment Accumulated Depreciation- Property, Plant, and Equipment Accounts Payable Long-Term Debt-Current Installment Long-Term Debt-Noncurrent Net Assets-Unrestricted-Board Designated Net Assets-Unrestricted-Undesignated Net Assets-Temporarily Restricted Net Assets-Permanently Restricted Totals 130,000 1,700,000 10,100,000 7,500,000 4,600,000 600,000 200,000 3,100,000 1,700,000 1,644,000 6,136,000 7,007,000 $25,990,000 $25,990,000 During the fiscal year ended December 31, 2017, the following transactions occurred: 1. Patient service revenue amounted to $20,990,000, all recorded on account. Contractual adjustments were recorded in the amount of $3,800,000. Uncollectible accounts are estimated to be $620,000. Cash was received on account in the amount of $17,600,000. 366 Chapter 12 2. Other revenue (cafeteria, parking lot, etc.) amounted to $2,580,000, all received in cash. 3. Patient accounts in the amount of $430,000 were written off. 4. Unrestricted gifts and bequests were received in cash in the amount of $317,000. Unrestricted income on investments of endowment funds amounted to $400,000. (It is the hospital's practice to treat unrestricted gifts as nonoperating revenue.) 5. Investment income on board-designated funds, which is limited by board policy to provide renewals and replacements, amounted to $95,000 and was received in cash. Do not increase board-designated net assets at this stage, but close out the revenue account to board-designated net assets in entry 19. 6. Investment income, restricted for donor-specified purposes, was received in cash in the amount of $250,000. Investment income, required by donor agreement to be added to endowment balances, was received in cash in the amount of $100,000. 7. Cash contributions were received in the following amounts: $2,001,000 for current restricted purposes; $2,450,000 for future plant expansion; and $1,050,000 required by the donor to be invested permanently in an endowment. 8. Pledges receivable in the amount of $2,100,000 were received in cash. These pledges were on hand at the beginning of the year (reflected in temporarily restricted net assets, for purposes of time) and were unre- stricted as to purpose. In addition, pledges for endowment purposes were collected in the amount of $450,000. 9. $1,550,000 in temporarily restricted net assets was expended, as the donors stipulated, for cancer research. Debit Operating Expense- Salaries and Benefits, $1,400,000; and Operating ExpenseSupplies, $150,000. (Assume the supplies were purchased with cash and used in the same year.) 10. $1,970,000 in temporarily restricted net assets was expended for equip- ment, as provided for by the donor. The policy of Haven Hospital is to record all property, plant, and equipment as unrestricted. 11. In addition, $600,000 was received in pledges for temporarily restricted purposes. It was decided that the allowance for contributions was sufficient. 12. Supplies were purchased in the amount of $690,000, on account. 13. Operating expenses (in addition to those already recorded in entries 1 and 9) for the year included: depreciation of $600,000; supplies used of $687,000; and salaries and benefits of $20,985,000 (paid in cash). In addition, the following expenses were recorded through Accounts Payable: utilities of $515,000 and insurance of $320,000. 14. Accounts payable were paid in the amount of $1,767,000. 15. Current installments of long-term debt were paid in the amount of $200,000. The portion to be paid next year is $300,000. Interest was paid in the amount of $181,000 and is reported as an operating expense. 16. Investments, carried at a basis of $4,000,000, were sold for $4,050,000. The $50,000 gain is considered to be temporarily restricted. 17. Cash in the amount of $6,800,000 was invested. Of that amount, $95,000 was from CashAssets Whose Use Is Limited and is designated by the board for renewals and replacements (see entry 5). 18. A reading of the financial press indicated that investments increased in mar- ket value by $800,000. Of that amount, $250,000 was in investments desig- nated by the board for renewals and replacements, $350,000 is required by donors to be added to endowment balances, and the remainder is unrestricted. 19. Closing entries were prepared. Required: a. Using the Excel template provided, prepare journal entries for each of the previous transactions. b. Prepare a Statement of Operations for Haven Hospital for the year ended December 31, 2017. c. Prepare a Statement of Changes in Net Assets for Haven Hospital for the year ended December 31, 2017. d. Prepare a Statement of Financial Position for Haven Hospital as of December 31, 2017. e. Prepare a Statement of Cash Flows for Haven Hospital for the year ended December 31, 2017, using the indirect method. D HAVEN HOSPITAL STATEMENT OF OPERATIONS for the year ended June 30 2017 Unrestricted Revenues: Patient Service Revenues -- Net of Contractual Adjustments & Provision for Bad Debts Other Operating Revenues Total Revenues s . 1 2 Net Assets Released From Restrictions: Expiration of Time Restrictions Satisfaction of Program Restrictions 3 Total Net Assets Released From Restrictions Total Operating Revenues Expenses Salaries and Benefits 7 Supplies 3 Utilities Insurance Depreciation Interest 2 Total Operating Expenses 3 4 Operating Loss 1 5 Other Income Unrestricted Gifts and Bequests 3 Income on Endowment Investments Income on Assets Whose Use is Limited Net Realized and Unrealized Gains on Investments - Undesignated Net Realized and Unrealized Gains on Investments - Board 2 Total Other Income 3 Excess of Revenues over Expenses 4 Net Assets Released From Restrictions: 5 Satisfaction of Property Plant and Equipment Restrictions = Increase in Unrestricted Net Assets S 7 HAVEN HOSPITAL STATEMENT OF CHANGES IN NET ASSETS for the year ended June 30 2017 Unrestricted Net Assets: Excess of Revenues over Expenses Net Assets Released From Restrictions: Satisfaction of Property Plant and Equipment Restrictions Increase in Unrestricted Net Assets - Temporarily Restricted Net Assets: Contribution Revenue Net Realized and Unrealized Gains on Investments Other Investment Income Net Assets Released From Restrictions Increase Decrease) in Temporarily Restricted Net Assets Permanently Restricted Net Assets: Contributions Gains on Long-Term Investments Investment Income on Endowment Increase in Permanently Restricted Net Assets - Increase in Net Assets Net Assets, Beginning of year Net Assets, End of year $ - STATEMENT OF FINANCIAL POSITION AS OF JUNE 30, 2017 $ ASSETS CURRENT ASSETS Cash and Cash Equivalents Patient Accounts Receivable (Net of Allowance for Doubtful Contributions Receivable (Net of Allowance for Uncollectible) Supplies Total Current Assets NONCURRENT ASSETS Investments Whose Use is Limited Long-Term Investments Property, Plant, and Equipment Less: Accumulated Depreciation Total Assets S LIABILITIES AND NET ASSETS CURRENT LIABILITIES Accounts Payable Long-Term Debt: Current Installment Total Current Liabilities Long-term Liabilities Total Liabilities Net Assets: Board Designated Other Unrestricted Total Unrestricted Temporarily Restricted Permanently Restricted Total Net Assets Total Liabilities and Net Assets $ STATEMENT OF CASH FLOWS for the year ended June 30 2017 $ Cash Flows From Operating Activities: Change in Net Assets Adjustments to Reconcile Change in Net Assets to Net Cash Provided by Operating Activities: Depreciation Decrease in Patient Receivable (Net of Allowance) Increase in Supplies Decrease in Contributions Receivable (Net of Allowance) Increase in Accounts Payable Gains on Long-Term Investments Contributions Restricted to Investment in Property, Plant and Contributions Restricted to Long-Term Investment in Endowments Investment Income Restricted to Endowment Cash Flows From Operating Activities Cash Flows From Investing Activities: Acquisition of Property, Plant and Equipment Purchase of Investments Sale of Investments Cash Flows From Investing Activities Cash Flows From Financing Activities: Proceeds From Contributions Restricted for. Investment in Property, Plant, and Equipment Investment in Endowments Income Restricted to Endowment Payments on Long-Term Debt Cash Flows From Financing Activities Net Increase (Decrease) in Cash and Cash Equivalents Cash and Cash Equivalents, beginning of year Cash and Cash Equivalents, end of year $ Supplemental Disclosure of Cash Flow Information: - Cash Paid for Interest $