Prepare a vertical and horizontal analysis based on the Statement of Net Assets and the Statement of Revenue, Expenditures and Changes in Fund Balances for the City of Dixon for the year ended 30 April 2010 (see Excel documents) and discuss any red flags, if any, identified by your analysis.
*can right-click to open the image in new tab to zoom in the picture
CITY OF DIXON, ILLINOIS STATEMENT OF NET ASSETS April 30, 2009 Primary Government Governmental Activities Business-type Activities Total $ 2,134,862 $ 5,174,742 1,368,890 $ 201,339 209,721 227,670 ASSETS Cash and cash equivalents Investments Restricted cash Restricted investments General property taxes receivable Due from State of Illinois Motor fuel tax receivable Accounts receivable Other receivable Notes receivable Due from (to) external parties Inventory Prepaid insurance Land and improvements Construction in progress Other capital assets, net of depreciation 3,503,752 5,376,081 209,721 227,670 3,694,618 967,106 31,833 882,711 191,876 33,202 707,239 720 3,694,618 967,106 31,833 175,472 191,156 33,202 485,000 20,316 166,472 2,530,861 2.930,278 38,387,731 (485,000) 120,209 55,854 149,073 5,724,982 29,322,419 140,525 222,326 2,679,934 8,655,260 67,710,150 56,923,649 37,603,116 94,526,765 TOTAL ASSETS 899,478 404,528 186,099 37,896 3,694,618 433,113 35,560 124,299 LIABILITIES Voucher payable Accrued payroll and taxes Accrued interest Deferred license income Deferred property tax revenue Consumer deposits Long-term liabilities: Due within one year Due in more than one year Total liabilities 1,332,591 440,088 310,398 37,896 3,694,618 376,799 376,799 1,072.911 11,873,764 18,169,294 1,277,126 20,165,694 22,412,591 2,350,037 32,039,458 40,581,885 30,902,195 13,753,654 44,655,849 NET ASSETS (DEFICITS) Invested in capital assets, net of related debt Restricted for Special revenue Debt service Capital projects Enterprise funds Unrestricted 9,085,323 18 1,675,847 9,085,323 18 1,675,847 437,391 (1,909,548) 437,391 999,480 (2,909,028) $ 38,754,355 $ 15,190,525 $ 53,944,880 TOTAL NET ASSETS (DEFICITS) CITY OF DIXON, ILLINOIS STATEMENT OF REVENUES, EXPENDITURES AND CHANGES IN FUND BALANCES GOVERNMENTAL FUNDS Year Ended April 30, 2009 Illinois Municipal Retirement Municipal Sales Tax Non-Major Total Capital Governmental Governmental Development Funds Funds General $ 1,880,891 $ 420,963 $ $ 1,205,560 5 16,808 3,455,201 2,432,166 510,279 REVENUES General property taxes Foreign fire insurance tax Intergovernmental revenues Licenses, permits, and other fees Fines and penalties Charges for services Revenue from use of money and property Other Total revenues 3,042,388 266,924 148,360 3,507,414 16,808 9,440,034 266,924 148,360 446,319 44,574 4,516 342,953 8,544 247.305 5,594,412 56,073 60,000 2,548,239 446,319 229,246 17,384 2,425,596 324,689 14,493,501 465,537 3,459,717 2,040,823 47,252 47,521 2,135,596 2,572,899 2,572,899 453,938 1,269,928 145,320 867,021 102,061 94,542 EXPENDITURES General government Public safety Police protection and community relations Fire fighting and prevention and emergency vehicle Public works Highways and streets Traffic development Welfare-Civil Defense Culture and recreation Airport operations Cemetery operations Capital outlay Debt Service Principal Interest Payments to pensions Payments to Social Security System Total expenditures 7,274 462,440 113,477 401,470 79,388 1.723,866 145,320 961,563 102,061 7,274 462,440 113,477 401.470 8,645,654 8,566,266 691,000 523,556 687,745 165,557 691,000 523,556 853,302 189,009 19,528,487 189,009 1,849,059 7,685.797 165,557 47,252 9,780,822 Excess (deficiency) of revenues over expenditures (2,091,385) 299,980 3,412,465 (7,232,583) ) 576,537 (5,034,986) 5,075,000 2,066,000 (1,050,000) 200.000 (646,000) OTHER FINANCING SOURCES (USES) Operating transfers in Operating transfers out Total other financing sources (uses) 7,341,000 (5,406,000) (3,710,000 1,016,000 (3.710,000) 5,075,000 1,935,000 (446,000) Excess (deficiency) of revenues and other financing sources over expenditures and other financing uses (3,099,986) (1,075,385) 299,980 130,537 (2,157,583) (297,535) 2,901,381 3,833,430 914,016 5,150,354 FUND (DEFICIT) BALANCE, BEGINNING OF YEAR (1,660,936) 11,138,245 FUND (DEFICIT) BALANCE, END OF YEAR 616,481 $ 8,038,259 $ (2.736,321 $ 3,201,361 $ 1,675,847 $ 5,280,891 $ CITY OF DIXON, ILLINOIS STATEMENT OF NET ASSETS April 30, 2010 Primary Government Governmental Activities Business-type Activities Total $ 816,508 $ 532,041 465,799 $ 270,656 287,989 163,779 ASSETS Cash and cash equivalents Investments Restricted cash Restricted investments General property taxes receivable Due from State of Illinois Motor fuel tax receivable Accounts receivable Other receivables Notes receivable Internal balances Inventory Prepaid insurance Land and improvements Construction in progress Other capital assets, net of depreciation 1,282,307 802,697 287,989 163,779 3,740,517 1,077,851 34,578 1,025,149 290,738 10,368 795,055 720 3,740,517 1,077,851 34,578 230,094 290.018 10,368 305,498 48,458 48,230 2,530,861 208,420 43,453,601 (305,498) 100,025 55,721 149,073 1,651,056 37,840,184 148,483 103,951 2,679,934 1,859,476 81,293,785 TOTAL ASSETS 53,327,043 41,474,559 94,801,602 192,209 39,219 126,818 LIABILITIES Vouchers payable Accrued payroll and taxes Accrued interest Accrued liabilities Deferred license income Deferred property tax revenue Consumer deposits Long-term liabilities: Due within one year Due in more than one year Total liabilities 450,921 435,779 176,021 80,960 37,276 3,740,517 643,130 474,998 302,839 80,960 37,276 3,740,517 391,242 391,242 1,262,064 11,236,965 17,420,503 1,318,028 19,706,188 21,773,704 2,580,092 30,943,153 39,194,207 33,693,853 18,616,097 52,309,950 NET ASSETS (DEFICITS) Invested in capital assets, net of related debt Restricted for: Special revenue Debt service Enterprise funds Unrestricted 8,410,197 18 8,410,197 18 451,768 (5,564,538) 451,768 632,990 (6, 197,528) TOTAL NET ASSETS (DEFICITS) $ 35,906,540 $ 19,700,855 $ 55,607,395 CITY OF DIXON, ILLINOIS STATEMENT OF REVENUES, EXPENDITURES AND CHANGES IN FUND BALANCES GOVERNMENTAL FUNDS Year Ended April 30, 2010 Illinols Municipal Retirement Municipal Sales Tax Non-Major Total Governmental Governmental Funds Funds Capital Development General $ 1,972,544 $ 443,396 $ $ $ 1,276,023 $ 24,226 576,267 3,392,775 REVENUES General property taxes Foreign fire insurance tax Intergovernmental revenues Licenses, permits, and other fees Fines and penalties Charges for services Revenue from use of money and property Other Total revenues 2,538,168 270,910 159,894 3,691,963 24,226 6,507,210 270,910 159,894 453,633 139,579 1,166,469 12,413,884 16,833 189 335 489,586 5,431,437 1,227 650,000 651,227 453,633 120,995 26,883 2,478,027 460,229 3,392,964 2,229,580 52,188 52,314 2,334,082 2,635,980 2,635,980 331,992 1,620,119 EXPENDITURES General government Public safety Police protection and community relations Fire fighting and prevention and emergency vehicle Public Works Highways and streets Traffic development Welfare-Civil Defense Culture and recreation Airport operations Cemetery operations Capital outlay 139,118 1,288,127 139,118 735,156 77,690 1,347,113 25,016 459,315 117,177 248,014 100,902 2,082,269 77,690 25,016 459,315 117,177 248,014 7,634,585 7,533,663 Debt Service Principal 647,000 558,295 Interest Payments to pensions Payments to Social Security System Total expenditures 647,000 558,295 926,630 747,479 179, 151 183,165 183,185 7,853,130 179, 151 52,188 8,738,958 2,865,008 19,688,435 Excess (deficiency) of revenues over expenditures (2,421,693) 281,078 3,340,776 (8,087,731) (386,981) (7.274,551) 4,925,000 OTHER FINANCING SOURCES (USES) Operating transfers in Operating transfers out Total other financing sources (uses) 1,010,000 (400,000) (3,460,000 (450,000 5,935,000 (4,310,000 610,000 (3.460,000 4,925,000 (450,000 1,625,000 Excess (deficiency) of revenues and other financing sources over expenditures and other financing uses (1,811,693) 281,078 (119,224) (3,162,731) (836,981) (5,649,551) FUND (DEFICIT) BALANCE, BEGINNING OF YEAR (2.736,321) 3,201,361 616,481 1,675,847 5,280,891 8,038 259 FUND (DEFICIT) BALANCE, END OF YEAR $ (4,548,014) $ 3,482,439 $ 497,257 $ (1,486,884) $4,443,910 $ 2,388, 708 CITY OF DIXON, ILLINOIS STATEMENT OF NET ASSETS April 30, 2009 Primary Government Governmental Activities Business-type Activities Total $ 2,134,862 $ 5,174,742 1,368,890 $ 201,339 209,721 227,670 ASSETS Cash and cash equivalents Investments Restricted cash Restricted investments General property taxes receivable Due from State of Illinois Motor fuel tax receivable Accounts receivable Other receivable Notes receivable Due from (to) external parties Inventory Prepaid insurance Land and improvements Construction in progress Other capital assets, net of depreciation 3,503,752 5,376,081 209,721 227,670 3,694,618 967,106 31,833 882,711 191,876 33,202 707,239 720 3,694,618 967,106 31,833 175,472 191,156 33,202 485,000 20,316 166,472 2,530,861 2.930,278 38,387,731 (485,000) 120,209 55,854 149,073 5,724,982 29,322,419 140,525 222,326 2,679,934 8,655,260 67,710,150 56,923,649 37,603,116 94,526,765 TOTAL ASSETS 899,478 404,528 186,099 37,896 3,694,618 433,113 35,560 124,299 LIABILITIES Voucher payable Accrued payroll and taxes Accrued interest Deferred license income Deferred property tax revenue Consumer deposits Long-term liabilities: Due within one year Due in more than one year Total liabilities 1,332,591 440,088 310,398 37,896 3,694,618 376,799 376,799 1,072.911 11,873,764 18,169,294 1,277,126 20,165,694 22,412,591 2,350,037 32,039,458 40,581,885 30,902,195 13,753,654 44,655,849 NET ASSETS (DEFICITS) Invested in capital assets, net of related debt Restricted for Special revenue Debt service Capital projects Enterprise funds Unrestricted 9,085,323 18 1,675,847 9,085,323 18 1,675,847 437,391 (1,909,548) 437,391 999,480 (2,909,028) $ 38,754,355 $ 15,190,525 $ 53,944,880 TOTAL NET ASSETS (DEFICITS) CITY OF DIXON, ILLINOIS STATEMENT OF REVENUES, EXPENDITURES AND CHANGES IN FUND BALANCES GOVERNMENTAL FUNDS Year Ended April 30, 2009 Illinois Municipal Retirement Municipal Sales Tax Non-Major Total Capital Governmental Governmental Development Funds Funds General $ 1,880,891 $ 420,963 $ $ 1,205,560 5 16,808 3,455,201 2,432,166 510,279 REVENUES General property taxes Foreign fire insurance tax Intergovernmental revenues Licenses, permits, and other fees Fines and penalties Charges for services Revenue from use of money and property Other Total revenues 3,042,388 266,924 148,360 3,507,414 16,808 9,440,034 266,924 148,360 446,319 44,574 4,516 342,953 8,544 247.305 5,594,412 56,073 60,000 2,548,239 446,319 229,246 17,384 2,425,596 324,689 14,493,501 465,537 3,459,717 2,040,823 47,252 47,521 2,135,596 2,572,899 2,572,899 453,938 1,269,928 145,320 867,021 102,061 94,542 EXPENDITURES General government Public safety Police protection and community relations Fire fighting and prevention and emergency vehicle Public works Highways and streets Traffic development Welfare-Civil Defense Culture and recreation Airport operations Cemetery operations Capital outlay Debt Service Principal Interest Payments to pensions Payments to Social Security System Total expenditures 7,274 462,440 113,477 401,470 79,388 1.723,866 145,320 961,563 102,061 7,274 462,440 113,477 401.470 8,645,654 8,566,266 691,000 523,556 687,745 165,557 691,000 523,556 853,302 189,009 19,528,487 189,009 1,849,059 7,685.797 165,557 47,252 9,780,822 Excess (deficiency) of revenues over expenditures (2,091,385) 299,980 3,412,465 (7,232,583) ) 576,537 (5,034,986) 5,075,000 2,066,000 (1,050,000) 200.000 (646,000) OTHER FINANCING SOURCES (USES) Operating transfers in Operating transfers out Total other financing sources (uses) 7,341,000 (5,406,000) (3,710,000 1,016,000 (3.710,000) 5,075,000 1,935,000 (446,000) Excess (deficiency) of revenues and other financing sources over expenditures and other financing uses (3,099,986) (1,075,385) 299,980 130,537 (2,157,583) (297,535) 2,901,381 3,833,430 914,016 5,150,354 FUND (DEFICIT) BALANCE, BEGINNING OF YEAR (1,660,936) 11,138,245 FUND (DEFICIT) BALANCE, END OF YEAR 616,481 $ 8,038,259 $ (2.736,321 $ 3,201,361 $ 1,675,847 $ 5,280,891 $ CITY OF DIXON, ILLINOIS STATEMENT OF NET ASSETS April 30, 2010 Primary Government Governmental Activities Business-type Activities Total $ 816,508 $ 532,041 465,799 $ 270,656 287,989 163,779 ASSETS Cash and cash equivalents Investments Restricted cash Restricted investments General property taxes receivable Due from State of Illinois Motor fuel tax receivable Accounts receivable Other receivables Notes receivable Internal balances Inventory Prepaid insurance Land and improvements Construction in progress Other capital assets, net of depreciation 1,282,307 802,697 287,989 163,779 3,740,517 1,077,851 34,578 1,025,149 290,738 10,368 795,055 720 3,740,517 1,077,851 34,578 230,094 290.018 10,368 305,498 48,458 48,230 2,530,861 208,420 43,453,601 (305,498) 100,025 55,721 149,073 1,651,056 37,840,184 148,483 103,951 2,679,934 1,859,476 81,293,785 TOTAL ASSETS 53,327,043 41,474,559 94,801,602 192,209 39,219 126,818 LIABILITIES Vouchers payable Accrued payroll and taxes Accrued interest Accrued liabilities Deferred license income Deferred property tax revenue Consumer deposits Long-term liabilities: Due within one year Due in more than one year Total liabilities 450,921 435,779 176,021 80,960 37,276 3,740,517 643,130 474,998 302,839 80,960 37,276 3,740,517 391,242 391,242 1,262,064 11,236,965 17,420,503 1,318,028 19,706,188 21,773,704 2,580,092 30,943,153 39,194,207 33,693,853 18,616,097 52,309,950 NET ASSETS (DEFICITS) Invested in capital assets, net of related debt Restricted for: Special revenue Debt service Enterprise funds Unrestricted 8,410,197 18 8,410,197 18 451,768 (5,564,538) 451,768 632,990 (6, 197,528) TOTAL NET ASSETS (DEFICITS) $ 35,906,540 $ 19,700,855 $ 55,607,395 CITY OF DIXON, ILLINOIS STATEMENT OF REVENUES, EXPENDITURES AND CHANGES IN FUND BALANCES GOVERNMENTAL FUNDS Year Ended April 30, 2010 Illinols Municipal Retirement Municipal Sales Tax Non-Major Total Governmental Governmental Funds Funds Capital Development General $ 1,972,544 $ 443,396 $ $ $ 1,276,023 $ 24,226 576,267 3,392,775 REVENUES General property taxes Foreign fire insurance tax Intergovernmental revenues Licenses, permits, and other fees Fines and penalties Charges for services Revenue from use of money and property Other Total revenues 2,538,168 270,910 159,894 3,691,963 24,226 6,507,210 270,910 159,894 453,633 139,579 1,166,469 12,413,884 16,833 189 335 489,586 5,431,437 1,227 650,000 651,227 453,633 120,995 26,883 2,478,027 460,229 3,392,964 2,229,580 52,188 52,314 2,334,082 2,635,980 2,635,980 331,992 1,620,119 EXPENDITURES General government Public safety Police protection and community relations Fire fighting and prevention and emergency vehicle Public Works Highways and streets Traffic development Welfare-Civil Defense Culture and recreation Airport operations Cemetery operations Capital outlay 139,118 1,288,127 139,118 735,156 77,690 1,347,113 25,016 459,315 117,177 248,014 100,902 2,082,269 77,690 25,016 459,315 117,177 248,014 7,634,585 7,533,663 Debt Service Principal 647,000 558,295 Interest Payments to pensions Payments to Social Security System Total expenditures 647,000 558,295 926,630 747,479 179, 151 183,165 183,185 7,853,130 179, 151 52,188 8,738,958 2,865,008 19,688,435 Excess (deficiency) of revenues over expenditures (2,421,693) 281,078 3,340,776 (8,087,731) (386,981) (7.274,551) 4,925,000 OTHER FINANCING SOURCES (USES) Operating transfers in Operating transfers out Total other financing sources (uses) 1,010,000 (400,000) (3,460,000 (450,000 5,935,000 (4,310,000 610,000 (3.460,000 4,925,000 (450,000 1,625,000 Excess (deficiency) of revenues and other financing sources over expenditures and other financing uses (1,811,693) 281,078 (119,224) (3,162,731) (836,981) (5,649,551) FUND (DEFICIT) BALANCE, BEGINNING OF YEAR (2.736,321) 3,201,361 616,481 1,675,847 5,280,891 8,038 259 FUND (DEFICIT) BALANCE, END OF YEAR $ (4,548,014) $ 3,482,439 $ 497,257 $ (1,486,884) $4,443,910 $ 2,388, 708
