Excel-Based Problems 8-13. The fund-basis financial statements of Jefferson County have been com- pleted 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 57,890,000 (net of accumulated depreciation) as of January 1. Depreciation on capital assets used in governmental-type activities amounted to 5316,000 No capital assets were sold or disposed of, and all purchases are prop- erly reflected in the fund basis statements as capital expenditures. b. Long-Term Debt There was no outstanding long-term debe 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 cach year. Interest is based on an annual rate of 6 percent, and principal pay ments 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 Governmen wide Sonments. Capitol Assets. cong Tem Debr 257 d. Transfers 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) Required: Use the Excel template provided to complete the following requirements: a separate tabs 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 "peneral 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 any- thing here. b. After you make the journal entries (Tab 2), post these to the work- sheet 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 col- umn 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 (bal- ances for government-wide statements). d. When calculating Restricted Net Position, recall that permanent fund principal is added to restricted fund balances, Fund Basis Statements Journal Entries Financial Statements ca home w ME De we Cane th The Fund Basis Statements Journal Entries Worksheet Journal Entries to Convert to the Accrual basis of Accounting debits credits $ Journal Entries Financial Statements ambers. The worksheet is formatted to add dd credits to liabilities, equities and Govtat Fund Balances Balances for Govt-wide Stmts Adjustments & Eliminations Debits Credits Internal Service Funds Debits Credits DEBITS: Cash Investment in corporate bonds Taxes Receivable Due from other governments Accrued interest receivable Supplies Inventory Due from Other Funds Capital Assets-net of accumulated depreciation 340.348 502.400 51.325 280.000 1.700 350 340.348 502.400 51.325 280.000 1.700 350 Expenditures (expenses) Current General Govt Llorary 181.560 12.700 181.560 12.700 Other Expenditures (expenses) -Debt Service Principal - Interest expense expenditure - Capital Outlay Depreciation Other Fin. Uses - Transfers Out 22.000 21.000 651.900 220001 21.000 651.900 65.800 65.800 0 2.131.083 2.131.083 6.100 Total Debits CREDITS: Accounts Payable Wages Payable Deferred Infows - Property taxes Contracts Payable 6.100 850 22.600 25,000 22.600 25.000 O Accrued Interest Payable Bank Note Payable O 0 Bonds Payable (two rows] Premium on Bonds Advance from Water Ualty Fund O 0 Revenues Property Taxes Intergovernmental Revenues Investment income 262.075 280,000 21.100 262.075 280.000 21.100 0 Other Financing Sources Proceeds of Bonde Proceeds of Bond premium Transfers in Net Position at beginning of your w rows Total Credits 700.000 22.600 65.500 700.000 22 800 65 8001 724.756 2.131.083 724.75 2.131.083 totals Journal entries O Excel-Based Problems 8-13. The fund-basis financial statements of Jefferson County have been com- pleted 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 57,890,000 (net of accumulated depreciation) as of January 1. Depreciation on capital assets used in governmental-type activities amounted to 5316,000 No capital assets were sold or disposed of, and all purchases are prop- erly reflected in the fund basis statements as capital expenditures. b. Long-Term Debt There was no outstanding long-term debe 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 cach year. Interest is based on an annual rate of 6 percent, and principal pay ments 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 Governmen wide Sonments. Capitol Assets. cong Tem Debr 257 d. Transfers 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) Required: Use the Excel template provided to complete the following requirements: a separate tabs 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 "peneral 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 any- thing here. b. After you make the journal entries (Tab 2), post these to the work- sheet 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 col- umn 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 (bal- ances for government-wide statements). d. When calculating Restricted Net Position, recall that permanent fund principal is added to restricted fund balances, Fund Basis Statements Journal Entries Financial Statements ca home w ME De we Cane th The Fund Basis Statements Journal Entries Worksheet Journal Entries to Convert to the Accrual basis of Accounting debits credits $ Journal Entries Financial Statements ambers. The worksheet is formatted to add dd credits to liabilities, equities and Govtat Fund Balances Balances for Govt-wide Stmts Adjustments & Eliminations Debits Credits Internal Service Funds Debits Credits DEBITS: Cash Investment in corporate bonds Taxes Receivable Due from other governments Accrued interest receivable Supplies Inventory Due from Other Funds Capital Assets-net of accumulated depreciation 340.348 502.400 51.325 280.000 1.700 350 340.348 502.400 51.325 280.000 1.700 350 Expenditures (expenses) Current General Govt Llorary 181.560 12.700 181.560 12.700 Other Expenditures (expenses) -Debt Service Principal - Interest expense expenditure - Capital Outlay Depreciation Other Fin. Uses - Transfers Out 22.000 21.000 651.900 220001 21.000 651.900 65.800 65.800 0 2.131.083 2.131.083 6.100 Total Debits CREDITS: Accounts Payable Wages Payable Deferred Infows - Property taxes Contracts Payable 6.100 850 22.600 25,000 22.600 25.000 O Accrued Interest Payable Bank Note Payable O 0 Bonds Payable (two rows] Premium on Bonds Advance from Water Ualty Fund O 0 Revenues Property Taxes Intergovernmental Revenues Investment income 262.075 280,000 21.100 262.075 280.000 21.100 0 Other Financing Sources Proceeds of Bonde Proceeds of Bond premium Transfers in Net Position at beginning of your w rows Total Credits 700.000 22.600 65.500 700.000 22 800 65 8001 724.756 2.131.083 724.75 2.131.083 totals Journal entries O