I have this spreadsheet and project information. I need the very last tab completed. Can you help with that? Can you also confirm that my blanks are correct and if not fix my mistakes? If not please let me know as soon as possible. The only tabs that are to be completed and reviewed are: "GF WS", "GF OS", "GF BS", "GF BtoA", "AP SRF", "AP OS", "AP BS", "ED SRF", "ED OS", "ED BS", and "Std WS".

Balance Checkers 100,000 Account Title 1 Cash City of Harvey City General Fund FY 20X4 Transactions 25,400 - - Balances Beginning of Year Dr. Cr. 1,000,000 # 4a 6 17 20 22 30 1 Investments 480,000 24c 800 3 1,580,000 1,300,000 280,000 4b 30,000 280,000 21a 1 Vouchers Payable 1 Accrued Salaries Payable 1 Deferred Revenues 1 Due to Internal Service Fund 1 Fund Balance Estimated Revenues Appropriations 1 Budgetary Fund Balance Encumbrances 1,015,845 - Balance Sheet Dr. 1 Cr. 177,975 - - 280,800 - - - - - - - 3 - - - - - - 216,000 23,000 12,000 20 21a 23 189,000 - - - 189,000 - 23,000 30,000 4b - 27,000 - - - 27,000 19 35,000 27,000 4,800 800 20 21b 23 56,900 - - - 56,900 - 21b 4,800 5,600 19 - 18,300 - - - 18,300 2,000 24b 2,700 2,000 6 2,700 - - - 2,700 - 53,000 5b 25b 19,000 168,200 190,700 26 49,500 - - - 49,500 - 112,000 31 500,000 19,000 264,200 52,700 168,200 5b 11b 14 25b - 116,100 - - - 116,100 50,000 7 50,000 13,050 18 - 13,050 - - - 13,050 78,400 24a - 178,400 - - - 178,400 20,000 54,500 1 Allowance for Uncollectible Interest and Penalties 1 Inventory of Materials and Supplies - Operating Statement Dr. 2 Cr. - - - 160,000 17,500 100,000 6 Cr. - - 1 Allowance for Uncollectible Taxes -- Delinquent 1 Accrued Interest Receivable 7 13 15 16 27 29 31 Dr. 177,975 - 280,800 Allowance for Uncollectible Taxes -- Current 1 Interest and Penalties Receivable # 30,000 4b 1,015,845 Balances End of Year 200,000 Taxes Receivable -- Current 1 Taxes Receivable -- Delinquent Transactions Dr. Cr. 1,300,000 1,355,000 759,140 61,000 100,000 500,000 243,000 1,059,965 72,000 10,000 250,000 60,200 500,000 - 4a 4b 8,000 13 61,000 57,070 12 - 4,070 - - - 4,070 1,442,000 32 49,500 69,500 1 - 1,462,000 - - - 1,462,000 1 2,032,500 2,032,500 33 - - - - - - 33 1,963,000 1,963,000 1 - - - - - - 33 69,500 19,000 2 50,500 - - - 50,500 - 2 8 10 19,000 195,000 279,200 229,000 - - - - - 264,200 11 19,000 279,200 195,000 2 10 9 - 229,000 - - - - 1,550,000 3 - 1,485,100 - 1,485,100 - - 48,480 6 - 48,480 - 48,480 - - 2 Revenues -- Integovernmental 122,460 6 - 122,460 - 122,460 - - 2 Revenues -- Licenses and Permits 346,200 6 - 346,200 - 346,200 - - 40,000 2,700 800 6 24b 24c - 43,500 - 43,500 - - Outstanding Encumbrances 11 2 Revenues -- Property Taxes 264,200 24a 64,900 2 Revenues -- Fines and Forfeitures 2 Revenues -- Interest 2 Expenditures -- General Government 7 9 12 14 18 26 180,000 900 18,000 11,420 1,800 11,000 223,120 - 223,120 - - - 2 Expenditures -- Public Safety 7 9 12 14 18 26 29 580,000 13,500 15,000 41,280 5,800 35,000 60,200 750,780 - 750,780 - - - 2 Expenditures -- Highways and Streets 7 9 12 18 26 175,000 3,300 3,500 1,750 39,400 222,950 - 222,950 - - - 2 Expenditures -- Health & Sanitation 7 9 12 18 26 150,000 2,900 6,630 1,500 75,300 236,330 - 236,330 - - - 2 Expenditures -- Parks and Recreation 7 9 220,000 1,900 268,040 - 268,040 - - - 12 18 26 1 Due to Water and Sewer Fund 13,940 2,200 30,000 100,000 2 Expenditures -- Capital Outlay 78,400 24a - 178,400 - - - 178,400 354,200 - 354,200 - - - 250,000 - - - 250,000 - 11b 27 264,200 90,000 1 Due from Addiction Prevention SRF 15 500,000 2 Transfer to Addiction Prevention SRF 16 60,000 60,000 - 60,000 - - - 2 Transfer to Parks and Recreation CPF 16 200,000 200,000 - 200,000 - - - 2 Transfer to Bridge CPF 16 70,000 70,000 - 70,000 - - - 2 Transfer to Refunding DSF 16 729,965 729,965 - 729,965 - - - 2 Transfer from Water and Sewer EF 250,000 30 100,000 13,500 2 OFS -- Proceeds from Sale of Equipment 1 Tax Liens Receivable 23 - 100,000 - - 29,400 19 1,125,345 - 1,125,345 - - - 22 - 72,000 - 72,000 - - 12,800 - - - 12,800 - - 80,000 - 80,000 - - - - - - - 5,539,905 4,524,060 4,240,730 4,240,730 2,297,740 1,942,990 4,240,730 1,070,175 1,942,990 3,013,165 1,997,320 1,997,320 12,800 2 OFS -- Capital Lease 80,000 1,749,500 Change in Fund Balance 100,000 - 24a - 72,000 2 Revenues -- Interest and Penalties 17 1,849,500 14,057,125 14,082,525 27 CITY OF HARVEY CITY GENERAL FUND Statement of Revenues, Expenditures, and Changes in Fund Balance For the Year Ended December 31, 20X4 Revenues: Taxes Interest and penalties on taxes Licenses and permits Fines and forfeitures Intergovernmental Investment income Total Revenues Expenditures: Operating General government Public safety Highways and streets Health and sanitation Parks and recreation Capital Outlay Total Expenditures Excess of Revenues over Expenditures Other Financing Sources and (Uses): Transfer from Enterprise Fund Proceeds from sale of general capital assets Capital leases Transfer to Special Revenue Fund Transfers to Capital Projects Funds Transfer to Debt Service Fund Total Other Financing Sources and (Uses) $ 1,485,100 346,200 48,480 122,460 43,500 15,900 $ 223,120 750,780 222,950 236,330 268,040 354,200 2,055,420 (9,680) 100,000 72,000 80,000 (60,000) (270,000) (729,965) (807,965) Net Change in Fund Balance Fund Balance, January 1, 20X4 Fund Balance, December 31, 20X4 2,045,740 (817,645) $ 1,442,000 624,355 CITY OF HARVEY CITY GENERAL FUND Balance Sheet December 31, 20X4 Assets Cash Investments Accrued interest receivable Taxes receivable--delinquent Less: Allowance for uncollectible delinquent taxes Tax liens receivable Interest and penalties receivable on taxes Less: Allowance for uncollectible interest and penalties Inventory of materials and supplies Due from Special Revenue Fund Total Assets $ $ 189,000 (27,000) 177,975 280,800 2,700 162,000 12,800 56,900 (18,300) $ Liabilities, Deferred Inflows, and Fund Balance Liabilities: Vouchers payable $ 116,100 Accrued salaries payable 13,050 Due to Internal Service Fund 4,070 Due to Enterprise Fund 178,400 38,600 49,500 250,000 974,375 $ 311,620 Deferred Inflows -- Deferred revenues 178,400 Fund Balance 49,500 264,200 170,655 Total Liabilities and Fund Balance $ 484,355 974,375 CITY OF HARVEY CITY GENERAL FUND Statement of Revenues, Expenditures, and Changes in Fund Balance Budget vs. Actual (GAAP Basis) For the Year Ended December 31, 20X4 Original Budget Final Budget Revenues: Taxes Interest and penalties on taxes Licenses and permits Fines and forfeitures Intergovernmental Investment income Total Revenues 1,500,000 15,500 300,000 50,000 122,000 45,000 2,032,500 1,500,000 15,500 300,000 50,000 122,000 45,000 2,032,500 Expenditures: Operating: General government Public safety Highways and streets Health and sanitation Parks and recreation Capital Outlay Total Expenditures Excess of Revenues over Expenditures 260,000 868,000 290,000 215,000 330,000 1,963,000 69,500 Actual 1,485,100 346,200 48,480 122,460 43,500 2,045,740 (14,900) (15,500) 46,200 (1,520) 460 (1,500) 13,240 260,000 868,000 290,000 215,000 330,000 80,000 2,043,000 (10,500) 223,120 750,780 222,950 236,330 268,040 354,200 2,055,420 (9,680) 36,880 117,220 67,050 (21,330) 61,960 (274,200) (12,420) 820 - 100,000 80,000 (60,000) (270,000) (729,965) (879,965) 100,000 72,000 80,000 (60,000) (270,000) (729,965) (807,965) 72,000 72,000 Net Change in Fund Balance 69,500 (890,465) (817,645) 72,820 Fund Balance, January 1 Fund Balance, December 31 1,442,000 1,511,500 1,442,000 551,535 1,442,000 624,355 72,820 Other Financing Sources and (Uses): Transfer from Enterprise Fund Proceeds from sale of general capital assets Capital leases Transfer to Special Revenue Fund Transfers to Capital Projects Funds Transfer to Debt Service Fund Total Other Financing Sources and (Uses) $ $ $ Variance-Favorable (Unfavorable) $ $ Balance Checks - Account Title City of Harvey City Addiction Prevention Special Revenue Fund FY 20X4 Transactions - - Balances Beginning of Year Dr. Cr. # 4 6 9 1 Due from Federal Government 7b 1 Vouchers Payable Cr. 430,000 250,000 # 5 8 50,000 5 1 Due to GF Dr. 500,000 450,000 60,000 430,000 - Balances End of Year Transactions 1 Cash - Dr. 330,000 Cr. - - - Operating Statement Dr. 2 Cr. - - - - Balance Sheet Dr. 1 Cr. 330,000 - 50,000 450,000 75,000 - - - 50,000 - 3 - 95,000 - - - 95,000 7a 8 500,000 4 - 250,000 - - - 250,000 10 5,000 5,000 1 - - - - - - 1 530,000 530,000 10 - - - - - - 10 525,000 525,000 1 - - - - - - - - - - - - - 500,000 - 500,000 - - 525,000 - 525,000 - - - - 60,000 - 60,000 - - - Fund Balance 250,000 - - - - - 905,000 905,000 525,000 35,000 560,000 560,000 380,000 380,000 345,000 35,000 380,000 Estimated Revenues Appropriations Budgetary Fund Balance 2 Revenue -- Operating Grants 450,000 50,000 2 Expenditures -- Public Safety 3 7a 2 Transfer from GF 450,000 75,000 - Change in Fund Balance - 6 7b 3,325,000 60,000 3,325,000 9 560,000 CITY OF HARVEY CITY ADDICTION PREVENTION SPECIAL REVENUE FUND Statement of Revenues, Expenditures, and Changes in Fund Balance For the Year Ended December 31, 20X4 Revenues: Operating Grants $ 500,000 Expenditures: Public Safety 525,000 Excess of Revenues over Expenditures (25,000) Other Financing Sources (Uses): Transfer from General Fund 60,000 Net Change in Fund Balance 35,000 Fund Balance, January 1, 20X4 Fund Balance, December 31, 20X4 $ 35,000 CITY OF HARVEY CITY ADDICTION PREVENTION SPECIAL REVENUE FUND Balance Sheet December 31, 20X4 Assets Cash Due from Federal Government Total Assets Liabilities and Fund Balance Liabilities: Vouchers payable $ Due to General Fund Fund Balance: Total Liabilities and Fund Balance $ $ 95,000 250,000 $ $ 330,000 50,000 380,000 345,000 35,000 380,000 Balance Checks - City of Harvey City Economic Development Special Revenue Fund FY 20X4 Transactions 573,250 - 1 Cash Balances Beginning of Year Dr. Cr. 342,230 1 Investments 125,000 Account Title 573,250 Balances End of Year Transactions # - 573,250 - Balance Sheet Dr. 1 Cr. 537,230 - 3 1,300,000 1,700 800,000 7 626,700 - - - 626,700 - 82,000 8 800,000 840,000 5 - 122,000 - - - 122,000 450 6 450 200 10 - 200 - - - 200 384,780 12 25,000 25,000 1 - 384,780 - - - 384,780 1 970,000 970,000 12 - - - - - - 12 945,000 945,000 1 - - - - - - - - - - - - 9a 1 Vouchers Payable 1 Accrued Salaries Payable 1 Fund Balance Estimated Revenues Appropriations Budgetary Fund Balance 2 Revenues -- Intergovernmental Cr. Operating Statement Dr. 2 Cr. - - Cr. 1,300,000 75,000 800,000 3 6 8 Dr. 537,230 - Dr. 1,500,000 55,000 815,000 2 4 7 # - - 914,750 11 - 914,750 - 914,750 - - 55,000 15,000 13,700 4 7 9 - 83,700 - 83,700 - - 914,750 - 914,750 - - - Interest Receivable - - - - - - Unearned Revenue - - - - - - - - - - - - 2,078,680 1,505,430 914,750 83,700 998,450 998,450 998,450 1,163,930 1,163,930 506,980 83,700 590,680 2 Revenues -- Interest 2 Expenditures -- Operating 5 6 10 467,230 Change in Fund Balance 467,230 840,000 74,550 200 7,326,900 6,753,650 CITY OF HARVEY CITY ECONOMIC DEVELOPMENT SPECIAL REVENUE FUND Statement of Revenues, Expenditures, and Changes in Fund Balance For the Year Ended December 31, 20X4 Revenues: Operating grants Investment Income $ 914,750 83,700 $ 998,450 Expenditures: Economic Development 914,750 Excess of Revenues over Expenditures 83,700 Fund Balance, January 1, 20X4 Fund Balance, December 31, 20X4 $ 384,780 468,480 CITY OF HARVEY CITY ECONOMIC DEVELOPMENT SPECIAL REVENUE FUND Balance Sheet December 31, 20X4 Assets Cash Investments Accrued interest receivable Total Assets $ 537,230 626,700 $ 1,163,930 Liabilities and Fund Balance Liabilities: Vouchers payable $ 122,000 Accrued salaries payable 200 Unearned Revenues - $ Fund Balance: Total Liabilities and Fund Balance $ 122,200 468,480 590,680 Balance Checks - Account Title City of Harvey City Parks and Recreation Capital Projects Fund FY 20X4 Transactions - - Balances Beginning of Year Dr. Cr. Dr. - Balances End of Year Transactions # - Cr. # Dr. Cr. Operating Statement Dr. 2 Cr. - - Balance Sheet Dr. 1 Cr. - - Cash - - Estimated Revenues and OFSs - - - - - - Appropriations - - - - - - Revenues -- Intergovernmental - - - - - - Transfer from GF - - - - - - OFS -- Bond Principal - - - - - - OFS -- Bond Premium - - - - - - Expenditures -- Debt Service -- Bond Issue Costs - - - - - - Encumbrances - - - - - - Encumbrances Outstanding - - - - - - Expenditures -- Capital Outlay - - - - - - Contracts Payable -- Retained Percentage - - - - - - - - - - - - Change in Fund Balance - - - - - - CITY OF HARVEY CITY PARKS AND RECREATION CAPITAL PROJECTS FUND Statement of Revenues, Expenditures, and Changes in Fund Balance For the Year Ended December 31, 20X4 Revenues: Intergovernmental Expenditures: Capital outlay: Debt Service -- Bond Issue Costs Total Expenditures $ $ - Excess (Deficiency) of Revenues Over (Under) Expenditures Other Financing Sources and (Uses): Transfer from General Fund Bond Principal Bond Premium Total Other Financing Sources and (Uses) - - Net Change in Fund Balance - Fund Balance, January 1, 20X4 Fund Balance, December 31, 20X4 - $ CITY OF HARVEY CITY PARKS AND RECREATION CAPITAL PROJECTS FUND Balance Sheet December 31, 20X4 Assets Cash Liabilities and Fund Balance Liabilities: Contracts Payable -- Retained Percentage Fund Balance $ (200,000) 200,000 Total Liabilities and Fund Balance $ - $ - $ - All resources of this fund except those transferred from the General Fund were restricted for the project. Restricted resources were assumed to be expended first when both restricted and unrestricted resources were available. In all probability, the council took action to commit the resources transferred to the Capital Projects Fund--particularly since the county is financing part of the cost. If so, Committed Fund Balance is $200,000; if not, Assigned Fund Balance is $200,000. The $200,000 can not be reported as Unassigned Fund Balance. Balance Checks - Account Title Cash Investments City of Harvey City Bridge Capital Projects Fund FY 20X4 Transactions - - Balances Beginning of Year Dr. Cr. 155,000 Dr. - Balances End of Year Transactions # - Cr. # 1,650,000 Dr. 155,000 Cr. - Operating Statement Dr. 2 Cr. - - - Balance Sheet Dr. 1 Cr. - - - 1,650,000 - - - - - 85,000 - 85,000 - - - - 1,720,000 - 1,720,000 - - - - Estimated Revenues and OFSs - - - - - - Appropriations - - - - - - Budgetary Fund Balance - - - - - - Encumbrances - - - - - - Encumbrances Outstanding - - - - - - Expenditures -- Capital Outlay - - - - - - Contracts Payable - - - - - - Revenues -- Interest - - - - - - Transfer from the GF - - - - - - - - - - - - 1,805,000 1,805,000 - - - - Contracts Payable -- Retained Percentage Fund Balance 1,805,000 Change in Fund Balance 1,805,000 - - CITY OF HARVEY CITY BRIDGE CAPITAL PROJECTS FUND Statement of Revenues, Expenditures, and Changes in Fund Balance For the Year Ended December 31, 20X4 Revenues: Investment Income $ - Expenditures: Capital Outlay - Excess of Revenues over Expenditures - Other Financing Sources--Transfer from General Fund - Net Change in Fund Balance - Fund Balance, January 1, 20X4 Fund Balance, December 31, 20X4 1,720,000 $1,720,000 Balance Checks - Account Title City of Harvey City City Hall Bonds Debt Service Fund FY 20X4 Transactions - - Balances Beginning of Year Dr. Cr. Balances End of Year Transactions # Dr. - Cr. # Dr. Cr. Operating Statement Dr. 2 Cr. - - Balance Sheet Dr. 1 Cr. - - Cash - - OFS -- Refunding Bond Principal - - - - - - Transfer from GF - - - - - - Expenditures -- Debt Service -- Interest - - - - - - OFU -- Payment to RBEA - - - - - - Expenditures -- Debt Service -- Payment to RBEA - - - - - - - - - - - - - - - - - - Change in Fund Balance - - - - - - CITY OF HARVEY CITY CITY HALL BONDS DEBT SERVICE FUND Statement of Revenues, Expenditures, and Changes in Fund Balance For the Year Ended December 31, 20X4 Revenues Expenditures Debt Service Interest Payment to Refunding Bond Escrow Agent Total Expenditures $ $ - Excess (Deficiency) of Revenues Over (Under) Expenditures Other Financing Sources and (Uses): Transfer from General Fund Refunding bonds Payment to Refunded Bond Escrow Agent Total Other Financing Sources and (Uses) - - Net Change in Fund Balance - Fund Balance, January 1, 20X4 Fund Balance, December 31, 20X4 - $ Balance Checks - Account Title Cash Investments City of Harvey City General Debt Service Fund FY 20X4 Transactions - - Balances Beginning of Year Dr. Cr. 171,350 Dr. - Balances End of Year Transactions # - Cr. # Dr. 171,350 Cr. - Operating Statement Dr. 2 Cr. - - - Balance Sheet Dr. 1 Cr. - - - 1,237,000 1,237,000 - - - - - 52,000 52,000 - - - - - - 5,800 - - - - 10,900 - - - - - 3,500 - 3,500 - - - - 50,000 - 50,000 - - - - 1,411,950 - 1,411,950 - - - - Taxes Receivable -- Current - - - - - - Allowance for Uncollectible Taxes -- Current - - - - - - Revenues -- Property Taxes - - - - - - Revenues -- Interest and Penalties - - - - - - Revenues -- Interest - - - - - - Expenditures -- Debt Service -- Principal - - - - - - Expenditures -- Debt Service -- Interest - - - - - - - - - - - - 1,471,250 1,471,250 - - - - Taxes Receivable -- Delinquent Allowance for Uncollectible Taxes -- Delinquent Interest and Penalties Receivable 5,800 10,900 Allowance for Uncollectible Interest and Penalties Deferred Revenues Fund Balance 1,471,250 Change in Fund Balance 1,471,250 - - CITY OF HARVEY CITY GENERAL DEBT SERVICE FUND Statement of Revenues, Expenditures, and Changes in Fund Balance For the Year Ended December 31, 20X4 Revenues: Taxes Interest and penalties on taxes Investment income Total Revenues Expenditures: Debt service: Bond Principal Interest Total Expenditures Excess (Deficiency) of Revenues over Expenditures Fund Balance, January 1, 20X4 Fund Balance, December 31, 20X4 $ $ - 1,411,950 $ 1,411,950 CITY OF HARVEY CITY GENERAL DEBT SERVICE FUND Balance Sheet December 31, 20X4 Assets Cash Investments Taxes receivable--delinquent Less: Allowance for uncollectible delinquent taxes Interest and penalties receivable on taxes Less: Allowance for uncollectible interest and penalties Total Assets $ $ - - $ - $ - Liabilities, Deferred Inflows, and Fund Balance Liabilities: Deferred Inflows: Deferred revenues - Fund Balance: Total Liabilities, Deferred Inflows, and Fund Balance 1,411,950 $ 1,411,950 City of Harvey City General Capital Assets and General Long-Term Liabilities Accounts FY 20X4 Transactions - Balance Checks Account Title 1 Land 1 Buildings Balances Beginning of Year Dr. Cr. 800,000 Dr. - Balances End of Year Transactions # - Cr. # 5,300,000 Dr. 800,000 Cr. - - - General Capital Assets General Long-Term Liabilities Dr. 1 Cr. 800,000 - 5,300,000 - 5,300,000 - - 2,200,000 - 2,200,000 1,750,000 - 1,750,000 - - 550,000 - 550,000 13,000,000 - 13,000,000 - - 6,000,000 - 6,000,000 1,700,000 - 1,700,000 - 9,000,000 - 9,000,000 - 9,000,000 1 Premium on Bonds Payable 180,000 - 180,000 - 180,000 1 Long-Term Claims and Judgments Payable 700,000 - 700,000 - 700,000 1 Long-Term Compensated Absences Payable 220,000 - 220,000 - 220,000 3,700,000 - 3,700,000 - 3,700,000 1 Capital Lease Payable - - - - 1 Refunding Bonds Payable - - - - 1 Leased Equipment - - - - 22,550,000 22,550,000 22,550,000 22,550,000 22,550,000 22,550,000 1 Accumulated Depreciation -- Buildings 1 Machinery & Equipment 2,200,000 1,750,000 1 Accumulated Depreciation -- Machinery & Equipment 1 Infrastructure 550,000 13,000,000 1 Accumulated Depreciation -- Infrastructure 1 Construction in Progress 6,000,000 1,700,000 1 Bonds Payable 1 Net Position 22,550,000 Change in Net Position 22,550,000 - - City of Harvey City General Capital Assets Note Disclosure Worksheet FY 20X4 Transactions Beginning Balance Nondepreciable Assets Land Ref # Increases Ref # Decreases Ending Balance 800,000 800,000 1,700,000 1,700,000 2,500,000 2,500,000 Depreciable Assets Buildings 5,300,000 5,300,000 Machinery & Equipment 1,750,000 1,750,000 Construction in Progress Total Nondepreciable Assets #REF! Leased Equipment Infrastructure Total Depreciable Assets Accumulated Depreciation Buildings Machinery & Equipment Leased Equipment Infrastructure Total Accumulated Depreciation Total Depreciable Assets, Net Governmental Activities Capital Assets, Net - - 13,000,000 13,000,000 #REF! 20,050,000 (2,200,000) (2,200,000) (550,000) #REF! (550,000) - - (6,000,000) (6,000,000) #REF! #REF! #REF! - - (8,750,000) 11,300,000 13,800,000 City of Harvey City General Capital Assets Note Disclosure For the Year Ended December 31, 20X4 Beginning Balance Nondepreciable Assets Land Construction in Progress Total Nondepreciable Assets $ Increases 800,000 1,700,000 2,500,000 $ Ending Balance Decreases - $ - $ 800,000 1,700,000 2,500,000 Depreciable Assets Buildings Machinery & Equipment Leased Equipment Infrastructure Total Depreciable Assets 5,300,000 #REF! 13,000,000 #REF! - - 5,300,000 #REF! 13,000,000 #REF! Accumulated Depreciation Buildings Machinery & Equipment Leased Equipment Infrastructure Total Accumulated Depreciation Total Depreciable Assets, Net Governmental Activities Capital Assets, Net (2,200,000) #REF! (6,000,000) #REF! #REF! #REF! - - (2,200,000) #REF! (6,000,000) #REF! #REF! #REF! Depreciation Expense was charged to governmental functions as follows: General Government Public Safety Highways and Streets Health and Sanitation Parks and Recreation Economic Development Total Depreciation Expense $ - $ $ City of Harvey City General Long-Term Liabilities Note Worksheet FY 20X4 Transactions Beginning Balance Bonds and Notes Payable Bonds Payable Ref # Increases Ref # Decreases Amounts Due in One Year Ending Balance 9,000,000 9,000,000 180,000 180,000 Capital Lease Payable - - Refunding Bonds Payable - - 9,180,000 9,180,000 Other Long-Term Liabilities Claims and Judgments Payable 700,000 700,000 Compensated Absences Payable 220,000 220,000 Other Long-Term Liabilities 920,000 - - 920,000 - 10,100,000 - - 10,100,000 - Premium on Bonds Payable Total Bonds and Notes Payable Governmental Activities Long-Term Liabilities - City of Harvey City General Long-Term Liabilities Note For the Year Ended December 31, 20X4 Beginning Balance Bonds and Notes Payable Bonds Payable Premium on Bonds Payable Capital Lease Payable Refunding Bonds Payable Total Bonds and Notes Payable Other Long-Term Liabilities Claims and Judgments Payable Compensated Absences Payable Other Long-Term Liabilities Governmental Activities Long-Term Liabilities $ 9,000,000 180,000 9,180,000 Increases $ 700,000 220,000 920,000 $ 10,100,000 Decreases - $ $ - - $ 9,000,000 180,000 9,180,000 $ Amounts Due in One Year Ending Balance 700,000 220,000 920,000 - $ 10,100,000 $ - $ - Balance Checkers - Account Title Name of Government Name of Fund FYXX Transactions - - Balances Beginning of Year Dr. Cr. Balances End of Year Transactions Dr. - Cr. Dr. 1 Cash Cr. Operating Statement Dr. 2 Cr. - - - Dr. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Change in Fund Balance - - - - - - - - - - - - - Balance Sheet 1 Cr. - - - - - - - - - - - - - - Activity ACCT 4600/01 Government and Not-For-Profit Accounting NO LATE WORK. Assignment: This is an individual assignment. You should not discuss it with any other person except Dr. Quosigk. Complete attached worksheet for Harvey City Comprehensive Case for Chapter 4 below. - 6 as per the description Do not alter the layout of the worksheets. You only need to insert the transactions Enter a code in column "A" of each worksheet to tell the worksheet where to place the account balance. A code of "I" causes the balance to be placed on the Balance Sheet while a code of "2" will place the balances on the Operating Statement. Budgetary accounts (Estimated Revenues, Appropriations, Budgetary Fund Balance, Encumbrances, and Encumbrances Outstanding) should not be assigned a code. Enter references (e.g. 6b, 1 la, or 3) in the "a" column. A row called "Balance Checks" has been added near the top of each fund worksheet. In an amount appears on this row, it indicates that set of debit/credit columns is out of balance. If the amount appears over the debit column, it means that column's total is less than the credit column's total. Conversely, if the amount appears over the credit column, it means that column's total is less than the debit column's total. It does not indicate the source of the error. You should save your file as Activity 1 .xlsx (do not add any other verbiage such as your name, course name or number, etc.). You should submit your PC Excel file by attaching the file through the Dropbox in D21. Students are responsible for ensuring that they have attached the appropriate file and submitted the assignment. If you have any questions about the assignment, please contact Dr. Quosigk. Unreadable or corrupt files will be treated as a nonsubmission. Failure to follow the instructions will result in a loss of points. Only those assignments submitted through D2L will be graded. No late assignments will be accepted nor graded. WORKSHEET SUMMARY AND EXPLANATIONS Tab Name Description Chapter GF WS General Fund Worksheet GF Os General Fund Operating Statement General Fund Balance Sheet 6 General Fund Budget to Actual Statement 6 AP SRF AP OS Addiction Prevention Special Revenue Fund Worksheet S Addiction Prevention Special Revenue Fund Operating Statement 5 AP BS Addiction Prevention Special Revenue Fund Balance Sheet 5 ED SRF Economic Development Special Revenue Fund Worksheet 6 ED OS Economic Development Special Revenue Fund Operating Statement 6 ED BS Economic Development Special Revenue Fund Balance Sheet Std WS Standard Worksheet (for use with fund requirement) GF BS GF BtoA 4-6 6 6 All The Harvey City Comprehensive Case consists of the last problem in each chapter from Chapters 4 through 15. Completing this case essentially requires that you account for all the transactions of a moderately complex city for a year (dealing with summary transactions) and that you prepare the basic financial statements and a significant portion of the financial section of the comprehensive annual financial report (CA FR) for that city. If you complete all of the requirements for the Comprehensive Case, you will: Prepare journal entries and financial statements for one or more funds of each type for Harvey City except Permanent Funds. Like most local governments, Harvey City does , not have a Permanent Fund. (chapters 3-8 and 10-12) Account for Harvey City s general capital assets and general long-term liabilities. ' (Chapter 9) Account for and report the vast majority of transactions that are either unique to governments or are handled in a unique manner by governments. (Chapters 3-12) Determine which of Harvey City's funds are major funds. (Chapter 13) Prepare a complete set of fund financial statements for Harvey City, including governmental fund financial statements, proprietary fund financial statements, and fiduciary fund financial statements. (Chapter 13) Prepare worksheets to derive Harvey city's government-wide financial statement data from its fund financial statement data. (Chapter 14) Prepare a complete set of government-wide financial statements for Harvey city, including both a government-wide statement of net assets and a government-wide statement of activities. (Chapter 14) Prepare reconciliations of Harvey city's fund financial statements and its governmentwide financial statements. (Chapter 14) Prepare combining financial statements that articulate with Harvey city's presentations for nonmajor funds in its fund financial statements. (Chapter 15) Except for the General Fund, the transactions and events affecting each individual fund of Harvey City are dealt with in the chapter on that fund type. Most General Fund transactions are presented in this chapter, but more advanced transactions of the General Fund are presented in Chapters 5 and 6 after the related topics have been covered. Various approaches may be used to complete the Harvey City Comprehensive Case. We believe that the most efficient approach is to use electronic spreadsheets similar to the worksheet shown in the appendix to Chapter 3 in Illustration 3-11 on pages 102-103. The one difference we would suggest is that you use detailed revenue and expenditures accounts in the worksheet instead of using the control accountsubsidiary ledger approach demonstrated in this chapter. The use of detailed general ledger accounts is illustrated in Appendix 3-3 and is used in several later chapters. You may want to see Illustration 7-3 for an example of a simple detailed general ledger worksheet. Using a worksheet approach to solve the problem will be particularly helpful when you begin to prepare fund financial statements in Chapter 13 and a worksheet to derive government-wide financial statement data in chapter 14. (Worksheet templates are available for the case.) Again, other use of a general ledger and detailed subsidiary ledgers, for instance -are acceptable, if your professor prefers. a. Enter the beginning (January 1, 20X4) trial balance of the General Fund of Harvey City in a General Fund worksheet. (A different solution approach may be used if desired by your professor.) The worksheet (which is similar to Illustration 3-11) may be set up as follows 1. The first column should be used for account titles. 2. Columns 2 and 3 should be the debit and credit columns, respectively, for the beginning trial balance of the General Fund. 3. Column 4 of the worksheet should be a reference column to tie the journal entry debits to the transaction number in this problem. 4. Columns 5 and 6 are the debit and credit columns in which the 20X4 transactions and events of the Harvey City General Fund are to be recorded. Add rows as necessary to provide sufficient room for all transactions. (The solution template should have the proper number of rows.) 5. Column 7 of the worksheet should be a reference column to tie the journal entry credits to the transaction number in this problem. 6. Columns 8 and 9 are for the preclosing trial balance of the Harvey City General Fund. They will be used in Chapter 6. 7. Columns 10 and 11 are to be used to close the nonbudgetary accounts of Harvey City. These columns also will contain all balances that are to be reported in the General Fund statement of revenues, expenditures, and changes in fund balances. 8. Columns 12 and 13 are to be used for the postclosing trial balance (balance sheet data). All asset, deferred outflow, liability, deferred inflow, and fund balance amounts should be entered here from the trial balance columns. 9. The differences between the closing entry columns and the balance sheet columns should be equal to each other and to the net change in fund balances of the General Fund. This amount should be entered in the smaller of the closing entry columns and the smaller of the balance sheet columns. (If the closing entry debit column is smaller, the balance sheet credit column should be smallerand vice versa.) b. Enter the effects of the following transactions and events in the appropriate columns of the worksheet. (Again, a different solution approach may be used if desired by your professor.) The trial balance of the General Fund of Harvey City at January 1, 20X4, was: Harvey City General Fund Trial Balance January x4 Debit Cash........................................................................................................ $1,000,000 Investments...........................................................................................480,000 Taxes Receivable Delinquent .......................................................... 160,000 Allowance for Uncollectibic Delinquent Taxes Interest and Penalties Receivable ...................................................... 54,500 Allowance for Tjncollectiblc Interest and Penalties Accrued Interest Receivable...............................................................2,000 Inventory of Materials and Supplies ..................................................53,001) Accrued Salaries Payable .................................................................... VouchersPayable ................................................................................. Deferred Revenues .............................................................................. Due to Internal Service Fund.............................................................. Fund Totals.................................................................................................. $1,749,500 Balance ........................................................................................ Credit $ 20,000 17,500 50,000 112,000 100,000 8,000 11442,000 $1,749,500 6 The General Fund budget for 20X4 was adopted by the city council. The city budgets on the modified accrual basis. Transfers are not budgeted. The adopted budget is presented here: Estimated Revenues Taxes..................................................... .. .................................... $1,500,000 Interest and penalties .................................. .............................. 15,500 Licensesand permits................................................................. 122,000 Fines and forfeitures .................................................................50,000 Intergovernmental grants.........................................................300,000 Investment income ....................................................................45,000 Total estimated revenues ........................................................ $2032,500 Appropriations General government................................................................. $ 260,000 Publicsafety ..................... ............... ........................................... 868,000 Highways and streets.................................................................290,000 Health and sanitation................................................................215,000 Parks and recreation .................................................................330,000 Total appropriations................................................................ Budgeted excess of revenues over appropriations 1,963,000 $ 69500 7 1. Record the budget. (You may use a single Estimated Revenues account and a single Appropriations account) 2. Reestablish the encumbrances of $19,000 that were closed at the end of last year. 3. The city levied its general property taxes for the year of $1,580,000. The city estimates that $30,000 of the taxes will prove uncollectible. Record the taxes assuming the city will collect the remaining balance later during the fiscal year. 4. The city collected $1,300,000 of property taxes before the duo date for taxes. The remainder of the taxes receivable became delinquent. 5. The city received and vouchered the materials and supplies that were on order from the previous year. The actual cost equaled the estimated cost of these materials and supplies, $191000. The city records expenditures for materials and supplies when they are consumed. A perpetual inventory system is used. 6. The city collected $200,000 when investments matured and also collected the following General Fund revenues during the year: Fines and forfeitures ............................. .... Unrestricted grants from the state Licenses and permits Interest revenue from investments (including $2,000 accrued at the end of 20X3) ......................... Total ............................................ ... . . $ 483 480 122,460 346,200 42,000 $ 559,140 7. The city incurred and paid salary expenditures as follows: Accrued salaries payable, January 1.................... $ 5000 180,000 General government ................................. 580,000 Public safety ........................................ Highways and streets ..................................175,000 150,000 . Health and sanitation ......................... . ...... 220,000 Parks and recreation..... ................. ........... Total ..............................................$1,355,000 8 8. The city ordered General Fund materials and supplies as follows: General government................. Public safety ........................ Highways and streets Health and sanitation Parks and recreation Total ............................ 1000 40,000 751 000 40,000 301 000 $195,000 $ Billings were received from the Water and Sewer Enterprise Fund as follows: General government Public Highways and streets Health and sanitation Parks and recreation Total ............................ safety. A $ ....................... 900 131500 31300 2,900 1,900 22.500 Equipment was ordered for the following functions: General government. ................ Public safety ...................... .. Highways and streets Health and sanitation Parks and recreation Total ............................ 351000 150000 $ 25,300 8,900 609 000 $279,200 ii. The equipment ordered was received (and vouchers approved) as follows: General government................. Public safety ........................ Highways and streets Health and sanitation Parks and recreation Totals ........................... Estimated Actual Cost Cost $ 35,000 $ 35,000 135,000 134,000 25,300 25,31)0 81 900 8,900 60,000 601 000 $263200 $264200 12. Billings were received from the Central Communications Network Internal Service Fund for communications services used by general government agencies and departments as follows: General government .... ............. Public safety ........................ Highways and streets Health and sanitation Parks and recreation Total ............................ $ 18,000 15,000 3,500 6,630 13,940 $ 57,070 13. $61,000 was paid on the amoi.uits owed to the Central Communications Network Internal Service Fund. 14. Other unencumbered expenditures incurred during the year were vouchcred as follows: General government................. Public safety ........................ Total ............................ $ 11,420 41,280 $ 525 700 10 15. $500000 was loaned from the General Fund to the Addiction Prevention Special Revenue Fund to provide working capital for that fund. The loan is to be repaid within a year. 16. The General Fund transferred resources to other funds as follows: Addiction Prevention Special Revenue Fund Parks and Recreation Capital Projects Fund Bridge Capital Projects Fund . ..................... Refunding Debt Service Fund...................... Total .......................................... . 60,000 200,000 70,000 729,965 $i,059965 $ 17. The General Fund received a transfer of $100,000 from the Water and Sewer Enterprise Fund. 18. Accrued salaries and wages for General Fund employees at December 31 were as follows: General government. . . ........................... Public safety ..................................... Highways and streets ............................. Health and sanitation ............................. Parks and recreation .............................. Total .......................................... $ $ 100 5,800 1,750 1,500 2,200 131050 11 Using the worksheet you began in Chapter 4, enter the effects of the following additional 20X4 transactions and events of the Harvey City General Fund in the transactions columns of the worksheet. (A different solution approach may be used if desired by your professor.) 19. The city levied interest and penalties of $35,000 on the overdue taxes receivable. Interest and penalties of $5,600 are expected to prove uncollectibic. 20. The city collected $216,000 of delinquent taxes receivable and $27,000 of interest and penalties receivable. 21. The city wrote off uncollectible taxes receivable of $23,000 and related interest and penaltics of $4800. 22. General government equipment with an original cost of $300,000 and accumulated depreciation of $187,000 was sold for $72,000, which was deposited in the General Fund. 23. The city formalized tax liens against properties that had claims against them for delinquent taxes of $12,000 and interest and penalties of $800. The estimated salable value of the properties was $14,000. 24. The following revenue-related information was available at year end: a. $144,000 of the December 31, 20X4, balance of delinquent taxes receivable and $34,400 of the December 31, 20X4, balance of interest and penalties receivable are not expected to be collected within the first 60 days of 20X5. (The January 1, 20X4, delinquent taxes receivable balance included $79,100 of taxes that were collected after the first 60 days of 20X4, and the January 1, 20X4, interest and penalties receivable balance included $20,900 of interest and penalties an taxes that were collected after the first 60 days of 20X4.) (Hint: Deferred Revenues must be adjusted.) b. Accrued interest receivable on investments at December 31, 20X4, totals $2,700. c. The fair value. of General Fund investments at December 31, 20X4, is $800 more than their book value. The Addiction Prevention Special Revenue Fund was established in 20X4 to account for federal grants intended to help communities prevent and battle drug and alcohol addictions. Because this is the first year for this fund, there is no beginning trial balance for the Addiction Prevention Special Revenue Fund. a. b. c. Prepare a worksheet for the Addiction Prevention Special Revenue Fund similar to the General Fund worksheet you created in Chapter 4. Enter the effects of the following transactions and events in the appropriate columns of the worksheet. (A different solution approach may be used if desired by your professor.) Enter the preclosing trial balance in the appropriate worksheet columns. Enter the preclosing trial balance amounts in the closing entry and postelosing trial balance (balance sheet data) columns, as appropriate. 12 d. e. Prepare the 20X4 Statement of Revenues, Expenditures, and Changes in Fund Balance for the Addiction Prevention Special Revenue Fund. Prepare the 20X4 balance sheet for the Addiction Prevention Special Revenue Fund. (Assume. that all but $60,000 of the resources of the fund at December 31, 20X4 are restricted.) Transactions and Events-20M 1. The city council adopted the budget for the Addiction Prevention Special Revenue Fund. The budget for the fund included estimated grant revenues of $530,000 and appropriations for public safety of $525,000. 2. The city was awarded a federal grant for drug addiction and enforcement programs. The grant requires the city to incur qualifying expenditures, then apply for reimbursement. The total amount of the grant award, which can be used anytime during the 3 years, is $1,200,000none of which is received at this time. 3. The city contracted for and received services costing $450,000 that qualify for reimbursement under the grant agreement. The expenditures, for the Public Safety function, were vouchered. 4. The Addiction Prevention Special Revenue Fund borrowed $500,000 from the General Fund on a short-term basis. 5. The paid $430,000 of the vouchers payable. 6. The city applied for and received reimbursement of $450,000 from the federal grantor agency under the provisions of the grant agreement. 7. The city incurred and vouchered $75,000 of Public Safety expenditures for the Addiction Prevention program: $50,000 of the costs is reimbursable under the grant agreement. The city applied for reimbursement, which is expected early next year. 8. $250,000 of the loan from. the General Fund was repaid. 9. A transfer of $60.000 was received from the General Fund. 10. The budgetary accounts were closed at year end. (Close the budgetary accounts in the transactions columns.) next city 13 Additional 20X4 transactions and events for the Harvey City General Fund are presented next, and conclude the General Fund case from chapters 4 and 5. a. Using the worksheet you began in Chapter 4, enter the effects of the following additional 20X4 transactions and events of the Harvey City General Fund in the transactions column of the worksheet. (A different solution approach may be used if desired by your professor) b. Prepare the 20X4 statement of revenues, expenditures, and changes in fund balance for the. Harvey city General Fund. c. Prepare the 20X4 statement of revenues, expenditures, and changes in fund balancebudget and actual for the Harvey City General Fund. d. Prepare the. 20X4 balance sheet for the Harvey City General Fund. Assume that $100,000 of fund balance was assigned by the city manager, under authority delegated to her by city council, to be used to establish new park and recreation programs in the next year. Also, assume that the city has budgeted a $75,000 decrease of otherwise unassigned fund balance to provide for a budgeted excess of General Fund expenditures over revenues for 20X5. 25. Materials and supplies ordered were received as follows: Estimated Cost General government ............................ $ 10,000 Public safety ................................... 32000 Highways and streets ................. ........... 60,000 Health and sanitation............................ 36,000 Parks and recreation............................. 30,000 Totals .......................................$168,000 Actual Cost $ 10,000 32,500 59,700 36,000 3000 $168200 14 26. Materials and supplies were used by General Fund departments during the year as follows: General government Public safety Flighways and streets ............................ Health and sanitation ....................... ..... Parks and recreation .......... ................... Total .....- ...... . ............................ - $ 11,000 35,000 39,400 .75,300 30,000 $19%700 27.. The city entered into a capital lease for parks and recreation equipment on December 31. The capitalizable cost of the equipment was $90,000, including a downpayinent of $10,000. 28. A lawsuit has been filed against the city related to an accident that occurred during the fiscal year. A city employee is at fault. The city expects to settle the lawsuit by sometime late in the next fiscal year and considers it probable that the city will lose $62,000. 29. The city contributed $60,200 to the city Police and Fire Pension Trust Fund. 30. The General Fund received $250,000 from the Addiction Prevention Special Revenue Fund in partial repayment of the interfund loan. 31. The city paid vouchers payable of $500,000. 32. The budgetary accounts, including encumbrances, were closed at year end. (Close the budgetary accounts in the transactions coluinn&) The beginning trial balance of the Har vey City Economic Developmnnt, Graiits Special Revenue Fund and its 20X4 budget and transactions are presented next. a. Prepare a worksheet for the Economic Development Special Revenue Fund similar to the General Fund worksheet you created in chapter 4. Enter the effects of the following transactions and events in the appropriate columns of the worksheet. (A different solution approach may be used if desired by your professor.) b. Prepare the preclosing trial balance in the appropriate. worksheet columns. c. Enter the preclosing trial balance amounts in the closing entry or postelasiag trial balance (balance sheet data) columns, as appropriate. d. Prepare the 20X4 statement of revenues, expenditures, and changes in fund balance for the Economic Development Special Revenue Fund. e.. Prepare the 20X4 balance sheet for the Economic Development Special Revenue Fund. 15 The trial balance of the Economic Development Special Revenue Fund of Harvey City at January 1, 20X4, is presented here. Harvey City Economic Development Special Revenue Fund Trial Balance January 1, 20X4 Credit Debit Cash ................................................ Investments .......................................... Accrued Salaries Payable .............................. Vouchers Payable ..................................... Fund Balance ........................................ Totals ............................................. $342,230 125,000 $ 450 82,000 3841780 $467;230 $467,230 The Economic Development Special Revenue Fund budget was adopted by the city council. The adopted budget included estimated operating grant revenues of $930,000 and estimated investment income of $40,000. Appropriations of $945,000 were adopted for economic development programs. 1. Record the budget. 2. The city received a $1500,000 economic development grant from the state's Department of Economic Development. The grant cash was received and is to be used in certain specified efforts to attract new businesses to the city and surrounding area. 3. The city purchased investments of $1,300,000. The grant requires that all investment income from the investment of the grant proceeds must be used for economic development. 4. The city received interest on its investments, $55,000. 5. The city incurred and vouchered $840,000 of economic development expenditures that qualify under the state grant program. 6. The city paid salaries of $75,000 to economic development personnel. The payment included $450 of accrued salaries payable from the prior year. The salaries expenditures qualify under the grant program. 7. The city sold investments costing $800,000 for $815,000. 8. The city paid $800,000 on vouchers payable for economic development. 9. The fair value of the investments of the Economic Development Special Revenue Fund at year end increased by $1,700. In addition, accrued interest on the investments at year end was $12,000. 10. Accrued salaries payable at year end totaled $200. 11. Grant revenues for the year were recorded (if not recorded earlier). 12. The budgetary accounts were closed at year end. (Close the budgetary accounts in the transactions columns.)