Need help with these journal entries for my governmental and non profit accounting class. Only need help with the entries that are on the excel file!

ACC 322 Final Project One Case Study Use of the Workbook For Final Project One, you will use the Final Project One Student Workbook Excel spreadsheet provided for you. If you would like a refresher course on using various features of Excel, sign in to Atomic Learning and type \"Excel\" in the search box. This will provide you with options to select the specific level of training that you need (i.e., intro, intermediate, or advanced) and the specific version of Excel that you have (i.e., 2011, 2013, etc.). The trainings are broken down into small, meaningful chunks. Therefore, you should be able to find specific topics at each level that will meet your needs. IMPORTANT: For the transaction worksheets for each fund, in column A, the part shaded in yellow, code the account 1 for balance sheet accounts and 2 for operating statement accounts. For example, on sheet GF WS, cash is a balance sheet account so in cell A1 you will put 1. These codes will allow the worksheet to automatically populate the operating statement and balance sheet columns on the worksheets. These will automatically populate the corresponding financial statements. For some funds, you will be required to prepare entries for transactions. Statements will automatically be created (if you properly code column A). You can check your work by ensuring debits equal credits and that the corresponding statements balance. For other funds, you will be given the completed worksheet for the funds' transactions and you will be required to prepare the appropriate statements (statements will not automatically be populated from the completed worksheet). Sheets you are required to complete will have white tabs. Sheets that automatically populate or that have given information will have green tabs. SAVE YOUR WORK OFTEN! General Fund As one of the accountants for the City of Springfield, you will record the following transactions into the accompanying workbooksheet GF WS. Note that some transactions have already been recorded in the workbook. The city has other accountants who also record transactions. 1. 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. 2. The city collected $1,300,000 of property taxes before the due date for taxes. The remainder of the taxes receivable became delinquent. 3. 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, $19,000. The city records expenditures for materials and supplies when they are consumed. A perpetual inventory system is used. 4. The city incurred and paid salary expenditures as follows: Accrued salaries payable, January 1. . . . . . . . . . . . . . . . . . . . General government. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Public safety. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Highways and streets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Health and sanitation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Parks and recreation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Total . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $ 50,000 180,000 580,000 175,000 150,000 220,000 $1,355,000 5. 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 6. $61,000 was paid on the amounts owed to the Central Communications Network Internal Service Fund. 7. Other unencumbered expenditures incurred during the year were vouchered as follows: General government. . . . . . . . . . . . . . . . . Public safety. . . . . . . . . . . . . . . . . . . . . . . . $ 11,420 41,280 Total . . . . . . . . . . . . . . . . . . . . . . . . . . . . $ 52,700 8. $500,000 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. 9. The General Fund transferred resources to other funds as follows: Addiction Prevention Special Revenue Fund . . . . . . . . . Parks and Recreation Capital Projects Fund. . . . . . . . . . Total . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $ 60,000 200,000 $260,000 10. The General Fund received a transfer of $100,000 from the Water and Sewer Enterprise Fund. 11. The city levied interest and penalties of $35,000 on the overdue taxes receivable. Interest and penalties of $5,600 are expected to prove uncollectible. 12. The city wrote off uncollectible taxes receivable of $23,000. 13. 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. 14. Materials and supplies were used by General Fund departments during the year as follows: General government . . . . . . . . . . . . . . . . . . . . . . . . . . . . Public safety . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Highways and streets . . . . . . . . . . . . . . . . . . . . . . . . . . . . Health and sanitation . . . . . . . . . . . . . . . . . . . . . . . . . . . . Parks and recreation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Total . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $ 11,000 35,000 75,300 39,400 30,000 $190,700 15. 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 down payment of $10,000. 16. The city contributed $60,200 to the city Police and Fire Pension Trust Fund. 17. The General Fund received $250,000 from the Addiction Prevention Special Revenue Fund in partial repayment of the interfund loan. 18. The city paid vouchers payable of $235,800. Special Revenue Fund 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. You are given the completed transaction worksheet (tab APSFR). From the worksheet: A. Prepare the 20X4 Statement of Revenues, Expenditures, and Changes in Fund Balance for the Addiction Prevention Special Revenue Fund (tab AP OP). B. Prepare the 20X4 balance sheet for the Addiction Prevention Special Revenue Fund (tab AP BS). Parks and Recreation Capital Projects Fund Record the following transactions to tab P&R CPF 1. The city approved a major capital improvement project to construct a recreational facility. The project will be financed by a bond issue of $1,500,000, transfers from the General Fund of $500,000, and a contribution from the county of $300,000. Record the budget assuming these amounts, along with an equal appropriation for the project, were adopted for 20X4. 2. The city received the county's contribution of $300,000. These resources are required to be used for the construction project. The grant is expenditure-driven. The city's policies indicate that resources restricted for a given purpose are considered expended prior to any unrestricted resources available for that purpose. 3. The city transferred $200,000 from the General Fund to the Parks and Recreation Capital Projects Fund. 4. The city issued bonds with a face (par) value of $1,500,000 at a premium of $50,000 on January 1. Bond issue costs of $15,000 were incurred. Interest of 8% per year and $100,000 of principal are due each December 31. 5. The city signed a $2,190,000 contract for construction of the new recreational facility. The process to establish the contract qualifies as a commitment under the city's commitments policy. 6. The city purchased land as the site for the facility at a cost of $110,000. Payment was made for the land. 7. The contractor billed the city $1,200,000. The city paid all but a 5% retainage. 8. The outstanding encumbrances were closed (use the transactions columns for this entry). 9. The budgetary accounts were closed at year end. Appropriations do not lapse at year end. Close the budgetary accounts in the transactions columns. General Debt Service Fund You are given the completed transaction worksheet (tab GEN DSF). From the worksheet, A. Prepare the 20X4 Statement of Revenues, Expenditures, and Changes in Fund Balance for the General Debt Service Fund (tab GEN DSF OS). B. Prepare the 20X4 balance sheet for the General Debt Service Fund (tab GEN DSF BS). General Capital Assets You are given the completed transaction worksheet CGA WS; prepare the General Capital Asset Note Disclosure (tab CGA Note). General Long-Term Liabilities You are given the completed transaction worksheet GLTL; prepare the General Longterm Liabilities Note Disclosure (tab GLTL Note). Water and Sewer Enterprise Fund You are given the completed transaction worksheet (tab W&S EF). From the worksheet, A. Prepare the 20X4 Statement of Revenues, Expenditures, and Changes in Net Position (tab W&S OS). B. Prepare the 20X4 Statement of Net Position (tab W&S SNP). Central Communications Network Internal Service Fund Record the following transactions to tab CCN ISF: 1. Billings to departments for communications network services during 20X4 were as follows: General Fund departments . . . . . . . . . . . . . . . . . . . . . . Water and Sewer Department . . . . . . . . . . . . . . . . . . . . Total . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $57,070 33,000 $90,070 2. The department collected $61,000 from the General Fund and $30,000 from the Enterprise Fund. 3. Payroll of $60,000 was paid, and $3,000 was contributed to the statewide retirement system. 4. The department purchased materials and supplies costing $14,000 and a voucher was approved. 5. Materials and supplies costing $17,700 were used by the Central Communications Network during 20X4. 6. Vouchers payable of $15,000 were paid. 7. Depreciation for the year was $8,370. 8. Salaries and wages of $1,000 were accrued at the end of the year. Police and Fire Pension Trust Fund You are given the completed transaction worksheet (tab PTF WS). From the worksheet, A. Prepare the 20X4 Statement of Changes in Net Position (tab PTF OS). B. Prepare the 20X4 Statement of Net Position (tab PTF SNP). Fund and Government-Wide Financial Statements The tabs colored purple are the fund and government-wide statements. If all of your previous work is correct, your statements will be complete and will balance. Review the purple tabs to ensure all of your statements are complete and correct. Once they are correct, you are done with the assignment and may submit the entire workbook to your instructor. ACC 322 - Governmental and Not for Profit Accounting Project 1 Supplemental Guidance Although the student workbook looks daunting, the color of the tabs will tell you what input is needed from you: White tabs - these are the sheets that require input from you. Green tabs - These sheets will automatically populate or they contain given information Purple tabs - These sheets will automatically populate but you will need to be assured that they balance. Even though a worksheet may populate itself, it is important that you verify that it is in balance because that will let you know that your input was done correctly. In addition, each tab has a code that tells you what it is first by fund and then by type of work paper. The abbreviations used are as follows GF AP P&R GenDSF GCA GLTL W&S CCN PTF General Fund Addiction Prevention Parks & Recreation General Debt Service General Capital Assets General Long Term Liabilities Water and Sewer Central Communications Network Police & Fire Pension Trust Fund WS OS BS BtoA SRF CPF EF SNP SCF ISF Note Worksheet Operating Stmt Balance Sheet Budget to Actual Special Revenue Fund Capital Project Fund Enterprise Fund Statement of Net Position Statement of Cash Flows Internal Service Fund Note Disclosure There are also four types of tasks that you need to accomplish: Generate journal entries and post into the ledger sheets: General Fund Parks and Recreation Capital Projects Fund Central Communications Network Internal Service Fund The input for these is located on the document referred to as: ACC 322 Final Project One Case Study Please see below for specific help with this part of the project.* Prepare the Operating Statements and Balance Sheets for: Addiction Prevention Special Revenue Fund General Debt Service Fund Police and Fire Pension Trust Fund Prepare the Note Disclosures: General Capital Assets General Long-term Liabilities Verify that the Fund and Government-Wide Financial Statements are in balance. *Because there is nowhere in the project workbooks with direction related to the journal entries, many students will just try to post the entries directly into the ledger sheets directly from the transactions described in the Case Study packet. This is always a mistake - and in fact, this would never be done in an Accounting Office. So I have created the journal entry formats for you to guide you through those entries as described. They are in the three tabs that follow. Once you have written these transactions out, post them into the proper Ledger Sheet in the Student Workbook. Be sure to post them in the proper column as either a debit or a credit. Do not use a minus sign or parentheses for the credits. The worksheets are set up to know the difference between them. Also, as the case study tells you on Page 1: For the transaction worksheets for each fund, in column A, the part shaded in yellow needs to code the account as either: 1 for balance sheet accounts or 2 for operating statement accounts. For example, on sheet GF WS, cash is a balance sheet account. So in cell A1, you will put 1. These codes will allow the worksheet to populate the operating statement and balance sheet columns on the worksheets automatically. In turn they will automatically populate the corresponding financial statements. SAVE YOUR WORK OFTEN! DO NOT DO THE 5-7 PAGE WRITE UP THAT THE CASE STUDY DOCUMENT REQUESTS. If you have any questions, please ask them in the Ask the Instructor area of the discussion board. qattachments_875c983a57f786262251a47a3e5560395f4c0e30.xlsx City of Springfield General Fund FY 20X4 Transactions Transactions Account Title Ref # 1 1 1 To record the levy of general property taxes for the year with the estimate of $30k in uncollectibles. 2 2 2 2 2 2 To record the collection of $1.3m in property taxes before the due date with the remainder reclassified to delinquent status. 3 3 3 3 To record vouchers for materials and supplies on order from previous year and the expenditure in the current year as consumed. 4 4 4 4 4 4 4 To record the salary expenditures incurred and paid in the current year. 5 5 5 Dr. Cr. ### qattachments_875c983a57f786262251a47a3e5560395f4c0e30.xlsx 5 5 5 To record the billings received from the Central Communications Network Internal Service Fund for services used by general government agencies and departments. 6 6 To record amounts owed to the Central Communications Network Internal Service Fund. 7 7 7 To record vouchers for other expenditures incurred during the current year. 8 8 To record loan from the General Fund to Addiciton Prevention Special Revenue Fund to provide working capital. (Loan to be repaid within one year.) Cash To record transfer of resources to other funds. 9 9 9 10 10 To record receipt of transfer from the Water and Sewer Enterprise Fund. 11 11 11 To record interest and penalties levied on overdue taxes receivable as well as an allowance for those levies considered to be uncollectible. qattachments_875c983a57f786262251a47a3e5560395f4c0e30.xlsx 12 12 To record the write off of uncollectible taxes receivable. 13 13 To record the sale of general government equipment. 14 14 14 14 14 14 To record the use of materials and supplies by General Fund departments during the year. 15 15 15 To record the capital leae for parks and recreation equipment on December 31 with a total capitalizable cost of $90K which includes a cash deposit of $10k. 16 16 To record contribution to the Police and Fire Pension Trust Fund. 17 17 To record receipt from Addicition Prevention Special Revenue Fund in partial repayment of the interfund loan. 18 18 ### ### To record payment of vouchers. Hash totals 0 0 qattachments_875c983a57f786262251a47a3e5560395f4c0e30.xlsx City of Springfield Parks and Recreation Capital Projects Fund FY 20X4 Transactions Ref # 1 1 To record estimated revenues and other funding sources as appropriated. 2 2 To record the receipt of the county's contribution. 3 3 To record a transfer from the General Fund. 4 4 4 4 To record the issuance of bonds and the cost of the bond issuance. 5 5 To record the issuance of a contract for construction of the new recreational facility. 6 6 To record the purchase of the land as the site for the facility. 7 7 7 7 7 DR qattachments_875c983a57f786262251a47a3e5560395f4c0e30.xlsx To record the payment to the contractor with a 5% retainage. 8 8 To close the outstanding encumbrances. 9 9 To close the budgetary accounts at year end. Hash total 0 0 qattachments_875c983a57f786262251a47a3e5560395f4c0e30.xlsx City of Springfield Central Communications Network Internal Service Fund FY 20X4 Transactions Account Title Ref # 1 1 1 1 To record billings to departments for communications network services during 20X4. 2 2 2 To record collections from the General Fund and the Enterprise Fund. 3 3 3 To record expenses paid for the period. 4 4 To record the purchase of materials and supplies. 5 5 To record the use of materials and supplies during the period. 6 6 To record the payment of vouchers payable. 7 7 To record the accrual of depreciation on machinery and equipment for the period. 8 8 Dr. Cr. qattachments_875c983a57f786262251a47a3e5560395f4c0e30.xlsx To accrue salaries at the end of the period. Hash totals 0 0