Excel Template:

Excel-Based Problems 12-9. As of January 1, 2017, the trial balance for Haven Hospital was as follows (these have been entered into the Excel template provided): Debits Credits $830,000 3,250,000 Cash Patient Accounts Receivable Allowance for Uncollectible Patient Receivables $650,000 2,480,000 Contributions Receivable Allowance for Uncollectible Contributions Receivable 343,000 130,000 Supplies Assets Limited as to Use Investments 1,700,000 10,100,000 Investments Undesignated Property, Plant and Equipment Accumulated Depreciation - Property, Plant & Equipment Accounts Payable Long-term Debt Long-term Debt - Noncurrent Net Assets without Donor Restrictions 7,500,000 4,600,000 600,000 Current Installment 200,000 3,100,000 1,700,000 1,644,000 Board Designated Net Assets without Donor Restrictions - Undesignated Program Restrictions Net Assets with Donor Restrictions 13.143.000 Totals $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. 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. UnrestrietedWithout donor restriction gifts and bequests were received in cash in the amount of $317,000. Unrestrieted-Without donor restriction income on investments of endowment funds amounted to $400,000. (It is the hospital's practice to treat unrestrieted without donor restriction 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 net assets with donor restrictions, for time) and were unrestrieted-without donor restriction as to purpose. In addition, pledges for endowment purposes were collected in the amount of $450,000 9. $1,550,000 in restricted net assets was expended, as the donors stipulated, for cancer research. Debit Operating Expense Salaries and Benefits, $1,400,000; and Operating Expense Supplies, $150,000. (Assume the supplies were purchased with cash and used in the same year.) 10. $1,970,000 in donor restricted net assets was expended for equipment, 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 program restricted purposes. It was decided 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 donor restricted (purpose). 17. Cash in the amount of $6,800,000 was invested. Of that amount, $95,000 was from Cash- Assets Limited as to Use and is designated by the board for renewals and replacements (see entry 5 18. A reading of the financial press indicated investments increased in market value by $800,000. Of that amount, $250,000 was in investments designated 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 O Search Formulas Review View Help Share Comments File Home Insert Page Layout Data A A TTY Wrap Text Times New Romar 8 General Sort & Find & Filter Select Conditional Format as Cell Ideas Paste Insert Delete Format E Merge & Center IU . A. E % B Formatting Table Styles Clipboard Number Styles Editing Font Alignment ells Ideas fi Z32 C debes HI D E G L M N O P O T U W X Y Journal Entner refat UNCOLLECTABLE ACCOUNTS 650000 s ACCOLNTS RECEIVABLE 325000 CONTRIBUIIONS BECEIVABLE SUPPLS 30000 130 00) 330000 3.290000 65000 130000 480000 PROPERTY PLAN INVESTMENTS ASSETS STO USE ALLOWANCE FOR CASH-ASSETS NCOLLECTABLE CONT LMTED AS TO 1SE NVE OTHER ENT hit J0100.000 18. 353.000 00000 7,500,000 19 ACCUMULATED ACCOUNTS PAYABU CURRENT PORTION NONCURRENT 21 DEPRECIATION LONG-TERMDRT ACCRUED EXPENSES LONG-TERMDERT 3100000 b 20,0 4,00,000b 4,0000 2010 NET ASSE cE RESTRICTIONS DONCE RESTRICTIONS BOARD DESIONATED RESTRICTIONS UNDESIGNATED L703.000 bb 13 143 000 to LG44,000 a L700.00 13143.000 L644,000 gasIRCIED UNRESTRICTED CPERATING REVENUES NONOPERATING REVENUES CONTRACTUA ADJUSTAENTS O Search File Formulas Review Help Share Comments Home Insert Page Layout Data View A A aWrap Text General Tirmes New Romar8 Parte LB Conditional Fomat as Cell Sort & Find& iter-Select Ideas Insert Delete Forma E A Merge & Center B IU Formatting Table Styles Number Editing Clipboard Font Alignment Styles ells Ideas Z32 H I E F G J K L M N C P Q TU V W X Y 32 OPERATING EEVENUES NONOPERATINGREVENUES CONTEACTLAL ADUSTMENTE 24 34 27 38 DO0OE PESTRICTIONS OPERATING EXPENSES 41 14 45 RECLASSIFICATION EROM 52 RECLASSIFICATION TO ET ASSETS WITH RESTRICTIONS NET ASSETS WITHOUTRESTRICTIONSS 54 -ImeRestnenons Proren Rextrictions Flant Acquston 7 Search File Formulas Review Help Share Comments Home Insert Data View Page Layout X A A Tirmes New Romar aWrap Text General Parte LB Conditional Format as Cell Sort & Find& citer-Select Ideas Insert Delete Forma E E E Merge & Center B IU A % Formatting Table Styles Number Editing clipboard Alignment Styles ells Ideas Font Z32 L M R F G HI J O T U W XY $4 REVENUES WITH DONOR 10 ESIRCIIONS OPERATING EXPENSES 42 RECLASSIFICATION FROM ET ASSETS wITH RESTRICTIONS ET 48SETS WITHOUT RESTRICTIONS Saisfactien ef -Iestchoms- -Plant Acquitun 23.932.029 beganag balances nding bolance 25 0 000 25900 000 2E8288846rEKO56648t Excel-Based Problems 12-9. As of January 1, 2017, the trial balance for Haven Hospital was as follows (these have been entered into the Excel template provided): Debits Credits $830,000 3,250,000 Cash Patient Accounts Receivable Allowance for Uncollectible Patient Receivables $650,000 2,480,000 Contributions Receivable Allowance for Uncollectible Contributions Receivable 343,000 130,000 Supplies Assets Limited as to Use Investments 1,700,000 10,100,000 Investments Undesignated Property, Plant and Equipment Accumulated Depreciation - Property, Plant & Equipment Accounts Payable Long-term Debt Long-term Debt - Noncurrent Net Assets without Donor Restrictions 7,500,000 4,600,000 600,000 Current Installment 200,000 3,100,000 1,700,000 1,644,000 Board Designated Net Assets without Donor Restrictions - Undesignated Program Restrictions Net Assets with Donor Restrictions 13.143.000 Totals $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. 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. UnrestrietedWithout donor restriction gifts and bequests were received in cash in the amount of $317,000. Unrestrieted-Without donor restriction income on investments of endowment funds amounted to $400,000. (It is the hospital's practice to treat unrestrieted without donor restriction 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 net assets with donor restrictions, for time) and were unrestrieted-without donor restriction as to purpose. In addition, pledges for endowment purposes were collected in the amount of $450,000 9. $1,550,000 in restricted net assets was expended, as the donors stipulated, for cancer research. Debit Operating Expense Salaries and Benefits, $1,400,000; and Operating Expense Supplies, $150,000. (Assume the supplies were purchased with cash and used in the same year.) 10. $1,970,000 in donor restricted net assets was expended for equipment, 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 program restricted purposes. It was decided 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 donor restricted (purpose). 17. Cash in the amount of $6,800,000 was invested. Of that amount, $95,000 was from Cash- Assets Limited as to Use and is designated by the board for renewals and replacements (see entry 5 18. A reading of the financial press indicated investments increased in market value by $800,000. Of that amount, $250,000 was in investments designated 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 O Search Formulas Review View Help Share Comments File Home Insert Page Layout Data A A TTY Wrap Text Times New Romar 8 General Sort & Find & Filter Select Conditional Format as Cell Ideas Paste Insert Delete Format E Merge & Center IU . A. E % B Formatting Table Styles Clipboard Number Styles Editing Font Alignment ells Ideas fi Z32 C debes HI D E G L M N O P O T U W X Y Journal Entner refat UNCOLLECTABLE ACCOUNTS 650000 s ACCOLNTS RECEIVABLE 325000 CONTRIBUIIONS BECEIVABLE SUPPLS 30000 130 00) 330000 3.290000 65000 130000 480000 PROPERTY PLAN INVESTMENTS ASSETS STO USE ALLOWANCE FOR CASH-ASSETS NCOLLECTABLE CONT LMTED AS TO 1SE NVE OTHER ENT hit J0100.000 18. 353.000 00000 7,500,000 19 ACCUMULATED ACCOUNTS PAYABU CURRENT PORTION NONCURRENT 21 DEPRECIATION LONG-TERMDRT ACCRUED EXPENSES LONG-TERMDERT 3100000 b 20,0 4,00,000b 4,0000 2010 NET ASSE cE RESTRICTIONS DONCE RESTRICTIONS BOARD DESIONATED RESTRICTIONS UNDESIGNATED L703.000 bb 13 143 000 to LG44,000 a L700.00 13143.000 L644,000 gasIRCIED UNRESTRICTED CPERATING REVENUES NONOPERATING REVENUES CONTRACTUA ADJUSTAENTS O Search File Formulas Review Help Share Comments Home Insert Page Layout Data View A A aWrap Text General Tirmes New Romar8 Parte LB Conditional Fomat as Cell Sort & Find& iter-Select Ideas Insert Delete Forma E A Merge & Center B IU Formatting Table Styles Number Editing Clipboard Font Alignment Styles ells Ideas Z32 H I E F G J K L M N C P Q TU V W X Y 32 OPERATING EEVENUES NONOPERATINGREVENUES CONTEACTLAL ADUSTMENTE 24 34 27 38 DO0OE PESTRICTIONS OPERATING EXPENSES 41 14 45 RECLASSIFICATION EROM 52 RECLASSIFICATION TO ET ASSETS WITH RESTRICTIONS NET ASSETS WITHOUTRESTRICTIONSS 54 -ImeRestnenons Proren Rextrictions Flant Acquston 7 Search File Formulas Review Help Share Comments Home Insert Data View Page Layout X A A Tirmes New Romar aWrap Text General Parte LB Conditional Format as Cell Sort & Find& citer-Select Ideas Insert Delete Forma E E E Merge & Center B IU A % Formatting Table Styles Number Editing clipboard Alignment Styles ells Ideas Font Z32 L M R F G HI J O T U W XY $4 REVENUES WITH DONOR 10 ESIRCIIONS OPERATING EXPENSES 42 RECLASSIFICATION FROM ET ASSETS wITH RESTRICTIONS ET 48SETS WITHOUT RESTRICTIONS Saisfactien ef -Iestchoms- -Plant Acquitun 23.932.029 beganag balances nding bolance 25 0 000 25900 000 2E8288846rEKO56648t