Excel-Based Problems 8-13. The fund-basis financial statements of Jefferson County have been completed for the year 2017 and appear in the first tab of the Excel spreadsheet provided with this exercise. The following information is also available: a. Capital Assets Capital assets purchased in previous years through governmental-type funds totaled $7,890,000 (net of accumulated depreciation) as of January 1. Depreciation on capital assets used in governmental-type activities amounted to $316,000. No capital assets were sold or disposed of, and all purchases are properly reflected in the fund-basis statements as capital expenditures. b. Long-Term Debt There was no outstanding long-term debt associated with governmental-type funds as of January 1. April 1, 6 percent bonds with a face value of $700,000 were issued at a premium. Bond payments are made on October 1 and April 1 of each year. Interest is based on an annual rate of 6 percent, and principal payments are $22,000 each. The first payment interest and principal) was made on October 1. Amortization of the bond premium for the current year is $1,800. C. Deferred Inflows of Resources Deferred inflows (comprised solely of property taxes) are expected to be collected more than 60 days after year-end. The balance of deferred taxes at the end of 2016 was $19,500. d. Transfers page 257 Transfers were between governmental-type funds. e. Internal Service Fund The (motor pool) internal service fund's revenue is predominantly derived from departments classified as governmental-type activities. There were no amounts due to the internal service fund from the General Fund. The outstanding balance of Due to Other Funds was with the enterprise fund and is not capital related. The enterprise fund provided a long-term advance to the internal service fund (not capital related). 181 Required: Use the Excel template provided to complete the following requirements; a separate tab is provided in Excel for each of these steps. 1. Prepare the journal entries necessary to convert the governmental fund financial statements to the accrual basis of accounting. 2. Post the journal entries to the conversion worksheet provided. 3. Prepare a government-wide Statement of Activities and Statement of Net Position for the year 2017. All of the governmental fund revenues are "general revenues." This is an involved problem, requiring many steps. Here are some hints. a. Tab 1 is information to be used in the problem. You do not enter anything here. b. After you make the journal entries (Tab 2), post these to the worksheet to convert to the accrual basis. This worksheet is set up so that you enter debits as positive numbers and credits as negative. After you post your entries, look at the numbers below the total credit column to see that debits equal credits. If not, you probably entered a credit as a positive number. c. Make sure that total debits equal total credits in the last column (balances for government-wide statements). d. When calculating Restricted Net Position, recall that permanent fund principal is added to restricted fund balances. A B Jefferson County Governmental Funds Balance Sheet as of 12-31-2017 Capital General Projects Debt Service Permanent Fund Fund Fund Fund Total Governmental Funds $ 128,148 S 181,000 $ 22,800 $ $ 8,400 502,400 51,325 Assets -Cash - Investments - Taxes Reveivable (net) - Due from other governments Accrued interest receivable Supplies Inventory Total Assets 340,348 502,400 51,325 280,000 1,700 280,000 1,700 350 350 179,823 $ $461,000 $ 22,800 $ 512,500 $ 1,176,123 4,100 6,100 2,000 850 850 2 Liabilities, Deferred Inflows and Fund Balance 3 Liabilities 4 Accounts Payable 5 Wages Payable Contracts payable 7 Total Liabilities 3 Deferred Inflows 2 Deferred Inflows - Property Taxes 25,000 25,000 25,000 31,950 2,850 4,100 22,600 22,600 350 350 500,000 1 Fund Balances 2 Nonspendable 3 Inventories 4 Permanent fund principal 5 Restricted 5 Assigned 7 Unassigned Total Fund Equity 436,000 22,800 8,400 800 153,223 154,373 500,000 436,000 32,000 153,223 1,121,573 436,000 22,800 508,400 Total Liabilities, Deferred Inflows and Fund Balanc $ 179,823 $461,000 $ 22,800 $ 512,500 $ 1,176,123 for the year Total Governmental Funds Jefferson County Statement of Revenues Expenditures and Changes in Fund Balance ended 12-31-2017 Capital General Projects Debt Service Permanent Fund Fund Fund Fund 4 Revenues 5 Property Taxes $ 262,075 $ 6 Intergovernmental revenues 280,000 7 Investment income: 21,100 Total Revenues 262,075 280,000 21,100 262,075 280,000 21,100 563,175 181,560 12,700 -0 Expenditures -1 Current: -2 General Government Operations -3 Library 4 Capital Outlay -5 Debt Service -6 Principal -7 Interest Total Expenditures 181,560 12,700 651,900 39,900 612,000 22,000 21,000 43,000 22,000 21,000 889,160 -8 221,460 612,000 12,700 50 Excess (Deficiency) of Revenues Over Expenditure 40,615 (332,000) (43,000) 8,400 (325,985) 700,000 22,800 2 Other financing sources (uses) 3 Proceeds of bonds 54 Premum on bonds 5 Transfers in 6 Transfers out 7 total other financing sources (uses) 65,800 700,000 22,800 65,800 (65,800) 722,800 (43,000) (43,000) (22,800) 700,000 65,800 22,800 9 Net Change in Fund Balance 50 Fund Balance, January 1 1 Fund Balance, December 31 (2,385) 156,758 154,373 368,000 68,000 $ 436,000 8,400 500,000 508,400 396,815 724,758 1,121,573 $ $ 22,800 $ $ Jefferson County 5 Statement of Revenues, Expenses, and Changes in fund Net Position For the year ended 12-31-2017 Jefferson County Statement of Net Position As of 12-31-2017 Enterprise Funds Internal Service Fund Enterprise Funds Internal Service Fund 8 Revenues Charges for Services $ 805,000 $ 96,250 $ 22,000 $ 1 22,850 15,750 3,500 42,100 Total Revenues 805,000 96,250 Assets Cash Due from other funds Supplies Inventory total current assets Advance to Internal Service fund Capital Assets (net) Total Assets 18,000 3 Operating Expenses 4 Cost of services 5 Depreciation 40,000 17,000 255,000 312,000 $ 703,000 60,000 83,690 6,900 21,600 63,700 S 7 Total Operaing Expenses 763,000 90,590 16,750 850 9 Operating Income (loss) 42,000 5,660 Liabilities Accounts payable Wages payable Interest payable Due to Other funds total current liabilities 4,350 490 600 1 Nonoperating income (expenses) 2 Interest Expense 3 Total nonoperating revenue 15,750 33,350 (600) 5,440 (5,000) (5,000) (600) 200,000 5 Change in Net Position 6 Net Position- Beginning of Year 7 Net Position - End of Year 37,000 41,650 78,650 5,060 26,200 31,260 10,000 17,000 32,440 $ Bond payable Bank note payable Advance from Enterprise fund Total Liabilities Net position Net investment in capital assets Unrestricted $ 233,350 00 NO 55,000 23,650 78,650 4,600 26,660 31,260 Net Position S $ Gov'tal Fund Balances Balances for Govt-wide Stmts Adjustments & Eliminations Debits Credits Internal Service Funds Debits Credits Worksheet Journal Entries to Convert to the Accrual basis of Accounting debits $ credits $ DEBITS: Casla Investment in corporate bonds Taxes Receivable Due from other governments Acchied interest receivable Supplies Inventory Due from Other Funds Capital Assets - net of accumulated desreciation two rows 340.348 502,400 51,325 280.000 1,700 340,348 502.400 51.325 280,000 1.700 1 | Expenditures (expenses) Current General Govt. Library 181,560 12.700 181,560 12.700 Other Expenditures (expenses) - Debt Service Principal - Interest expenselexpenditure 1 - Capital Ouday 1 - Depreciation Other Fin. Uses - Transfers Out 22,000 21.000 651.900 22.000 21.000 651.900 65,800 65,800 2.131.083 2.131,083 Total Debits CREDITS: Accounts Payable Wages Pavable Deferred Inflows - Property taxes | Contracts Payable 6.100 6.100 250 22,600 25,000 22,600 25.000 Accrued Interest Payable I Bank Note Payable Bonds Payable (two rows) Premium on Bonds Advance from Water Utility Fund 262.075 Revenues Property Taxes | Intergovernmental Revenues Investment income 280,000 21.100 280,000 21,100 | Other Financing Sources Proceeds of Bonds Proceeds of Bond premium Trensfers In Net Position at beginning of year two rows) Total Credits 700.000 22.800 65.800 700,000 2.800 724,758 2131,083 724 75B 2131,083 totals Journal entries Jefferson County Statement of Activities for the year ended 12-31-2017 Program Revenues Net (Expense) Revenue and Change in Net Position Operational | Capital Charges for Grants and Grants and Governmental Business-Type Services Contributions Contributions || | Activities Activities Expenses Total Functions/Programs Governmental Activities: General Government Library Depreciation Interest Total Governmental Activities Business Type Activities Water and Sewer Total Government IS - $ Ils General Revenues Taxes: Property Taxes Intergovernmental revenues Investment Income Total General Revenues Change in Net Position Net Position, Beginning Net Position, Ending ca Jefferson County Statement of Net Position as of 12-31-2017 Business- Type Activities Total Governmental Activities Assets 3 Cash 4 Investments 5 Taxes Receivable (Net) 6 Interest Receivable 7 Internal Balances Current B Due from Other Governments 9 Inventories Internal Balances Long-Term 1 Capital Assets, Net of Accumulated Depreciation Total Assets UN Liabilities 5 Accounts Payable 6 Wages Payable 7 Accrued Interest Payable B Contract Payable 9 Bank Note Payable Bonds Payable 1 Premium on Bonds Sold Total Liabilities UN 4 Net Position 5 Net investment in capital assets 5 Restricted 7 Unrestricted Total Net Position 3