Read PDF and do it on excel file
ACC 5823 1 ACC 5823 Project 2—Instructions 55 points Due: Wednesday, April 14 at 12:00 P.M. (noon) CDST This is an individual assignment. You should not discuss it with any other person except Dr. Pitman. Students are expected to follow the UTSA Honor Pledge: “As a UTSA Roadrunner I live with honor and integrity.” The purpose of this project is to provide you additional work in the area of a single spreadsheet approach for a GASB 34 conversion (something that is done in the “real world”). You should review the related information in the appropriate appendix in your textbook. The estimated time to complete this assignment correctly is 3 to 5 hours (some may complete it in less than 3 hours and some may take longer than 5 hours), so plan accordingly. You are a new staff accountant for the City of Namtip. The city has only four (4) governmental funds (General, Special Revenue, Capital Projects, and Debt Service) and no Permanent, Proprietary, or Trust/Agency Funds. Cecile Hockafinger III, Chief Accounting Officer for the City of Namtip, prepared the attached PC Excel® file (Namtip 2020.xls) to assist in the preparation of the governmental activities of the government-wide financial statements as of and for the year ending September 30, 2020 (FY20). So far, Cecile has entered the preclosing balances of the accounts at September 30, 2020 of each of the governmental funds on the worksheet. Additionally, he has entered the beginning account balances (October 1, 2019) for the General Capital Assets and General Long-term Liability (GCA/GLTL) nonfund accounts. He has also setup the general format for completing the worksheet. Unfortunately, before he could make the appropriate adjusting, eliminating, and reclassifying entries for the GASB 34 conversion and complete the worksheet, Cecile’s Reserve unit was called up for an oversea deployment and he will not be able to return to work for at least six months. Since you are the only other accounting employee to ever have taken a governmental accounting course, the city manager, R.U. Gullible, has asked you to complete the task Cecile started. You will first need to calculate the individual account balances for the Total Combined Funds and GCA/GLTL Accounts. Then you are to prepare and enter all of the adjusting, elimination, and reclassification entries on the worksheet that are necessary to compute the balances for the governmental activities for the preparation of the government-wide financial statements. Round all calculations to the nearest whole dollar. All entries should be in whole dollars (i.e., no decimals) without dollar signs. You should use compound entries whenever it is logical to do. Additional information is provided to assist you in this assignment on page 3. To leave a trail of your entries, you should number your entries using Arabic numbers (1, 2, 3, etc.). Be sure to put the journal entry number in Dr Ref column left of the Dr Column of the Adjustments and Eliminations as well as the journal entry number in Cr Ref column right of the Cr Column of the Adjustments and Eliminations. In the case of any compound journal entries, you should indicate journal entry number for each debit or credit amount that you enter. For example, if journal entry 25 consists of debits of $45,000 and $5,000 and credits of $35,000 and $15,000 you would enter the following on the appropriate lines of the accounts involved: 2 Adjustments and Eliminations Dr Ref Dr Cr Cr Ref 25 45,000 25 5,000 35,000 25 15,000 25 Do not put more than one number or amount in the reference columns or the amount columns (in other words, show each entry in separate cells). If an account is not listed, you may use one of the blank rows (72 to 87) available with an appropriate account title. You should use the capabilities of Excel® wherever possible. Do not make any additional adjustments/eliminations simply to reclassify expenditures to expenses. (You should make sure that each entry you make that the debits and credits equal by looking at the total Debits and Credits columns of the Adjustments and Eliminations columns after each entry.) After you have entered all of your all of the adjusting, elimination, and reclassification entries, you should calculate the government-wide preclosing trial balance and, then spread the accounts to the appropriate columns of the Statement of Activities and Statement of Net Position. Do not add any additional worksheets. Do not prepare any of the formal government-wide financial statements. Do not enter anything below Row 90 nor to the right of Column W. Do not add any sheets to the file. You should save your PC Excel® file as Project 2 (do not add any other verbiage such as your name, course name or number, etc.) with the appropriate extension. Prior to submitting your project, you must unfreeze and unhide any columns and/or rows. Your project file should be submitted by attaching it through the projects/assignment page of Blackboard Learn. (For help in submitting your assignment see: https://help.blackboard.com/Learn/Student/Assignments/Submit_Assignments). Students are responsible for ensuring that they have attached the appropriate file and submitted the project/assignment. If you have any questions about the assignment, please contact Dr. Pitman. 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 Blackboard Learn will be graded. No late assignments will be accepted nor graded. https://help.blackboard.com/Learn/Student/Assignments/Submit_Assignments 3 Additional Information In addition to the normal adjusting/elimination entries that will be made, the following information is provided to assist you. The city uses following average useful lives to depreciate its general capital assets using the straight- line method with no residual values: Buildings 40 years Improvements Other than Buildings 25 years Autos, Trucks, and Heavy Equipment 10 years Furniture and Office Equipment 10 years Infrastructure 50 years No depreciation is recorded in the year of acquisition, regardless when acquired; however, a full year’s depreciation is recorded in the year of disposal, regardless when disposed. Depreciation on the Infrastructure Assets is allocated solely to Public Works. All other depreciation is allocated to the following functions: General Government 55%; Public Safety 15%; Public Works 5%; Health and Welfare 10%; and Parks and Recreation 15%. An analysis of the Expenditures-Capital Outlay accounts reveals the following: furniture and office equipment was acquired at a cost of $450,000; various autos, trucks and heavy equipment were acquired at a total cost of $3,000,000; and the remaining balance represents the costs incurred during the year for the construction of a new city hall/administration building that should be completed in FY21. The Other Financing Sources-Sale of Autos, Trucks, and Heavy Equipment represents the proceeds of the several sales of various autos, trucks, and heavy equipment during FY20 that had a total cost of $280,000 and been depreciated through FY19 for a total 6 years. On April 30, 2020 the city of Namtip issued some 30-year, 3% term bonds with a face amount of $60,000,000 at 100½. Interest is paid annually on April 30th. The city has a policy of amortizing of any premium or discount at the end of the fiscal year using the straight-line method over the term of the bond as part of the GASB 34 conversion process. Prior to FY20, the City of Namtip issued some 4% serial bonds at par. The indenture requires $10,000,000 of the bonds to be paid off each year on September 30th; interest is paid annually also on September 30th. The Deferred Property Taxes represents amounts that were levied for FY20, but are not expected to be collected before January 2021. The balance of Deferred Property Taxes at October 1, 2019 was $1,900,000. In late September 2020, the city attorney advised the city council that a lawsuit involving Parks and Recreation that occurred in May 2020 had been recently filed. The attorney believes it is probable that the city will have to pay the claimant approximately $125,000, but it will not be settled for at least 18 months. The city has a policy that all employees must use their accumulated sick leave and vacation leave by the end of each fiscal year (commonly referred to as “use it or lose it”). Accordingly, there is no compensated absences short- or long-term balances at October 1, 2019 nor September 30, 2020.