Acct 386 Spring 2024 Accounting Cycle Project (100 points) Due Dates: March 21. Hand in the hardcopy AND submit your final Excel file on Canvas. This project is designed to give you practice completing the accounting cycle for a business. Suppose you own a hat retail store "X Y Hat Corp", where X and Y are your first and last name. It is a merchandise company. Transactions for 2023 are representative of such a company's business activities. (HINT: Read through the entire assignment at least twice before beginning to do any work. This will help you familiarize yourself with all of the important facts.) The Excel template is available in Canvas under "Project", "Individual Project" folder. You need to finish the following worksheets to complete the project. Beginning balance is available under worksheet "12-31-23 Balance Sheet". Any change made in the journal entry amounts should be carried forward through the entire spreadsheet. Chart of Account General Journal (GJ) General Ledger (GL) Worksheet Income Statement Statement of Retained Earnings Balance Sheet Post-Closing Trial Balance Short Questions The followings are the store's transactions for the year of 2023. a. The following purchases (all on account) were made during 2023: i. January 15th - 850 hats @ $25.00 each ii. March 23rd - 600 hats @ 28.00 each iii. July 2nd 350 hats @ $29.00 each iv. October 31st - 200 hats @ 26.00 each b. 1,850 Hats are sold at $45 each during 2023. All sales were made on account. In order to induce prompt payment, sales discount is offered with the term 2/10, E.O.M. 60 percent of sales are collected within 10 days. The company use the gross amount method to account for discount. 25 percent of sales are collected after 10 days. The rest remains uncollected. c. The Sales returns are 100% refunded, and amounts are: If your last name starts with A-M, 80 pieces; If your last name starts with N-Z, 40 pieces; d. During the year, the company made cash payments to hat suppliers totaling $42,000. e. It borrowed $9,000 on a new note payable and required semiannual interest payments. The term of the note is as follows: - 1. If your student ID is an even number, the new note is borrowed on February If your student ID is an odd number, the new note is borrowed on March 1. If you are a female, the new note carries a 10% annual interest. If you are a male, the new note carries a 12% annual interest. 1 f. It has a 30% income tax rate. In March of 2023, the company paid 2020 income taxes. g. The company made two rent payments of $1,500 each during 2023 (on April 1 and October 1). The payments were for rent on the store building and were prepaid for six months each. The balance in the prepaid account at the end of 2022 represents the rent for January through March of 2023. h. On August 20, $420 of receivables are uncollectible and wrote off. i. On October 1, new store fixtures (ID#5422) were purchased for $7,200 in cash. j. On October 20, administrative expenses (paid in cash) totaled $3,800. k. Dividends of $3,000 were paid on November 1. I. New common stock was issued for $35,000 in December 1. m. Cash paid out in December 31 for wages totaled $15,000 (including the wages owed from last year). The followings are information for adjusting entries at the end of year 2023. 1) The company began 2023 with 150 hats which had a cost of $23 each. The company uses a periodic LIFO inventory system to cost their inventory. Employees physically counted number of hats remaining in the warehouse at the end of 2023 indicated there were: 380 pieces, if your last name starts with A-M; 340 pieces, if your last name starts with N-Z; 2) The company follows a percentage-of-receivables approach to estimate their accounts receivable that will become uncollectible. As of the fiscal year end, the company estimates that 5% of receivables will be uncollectible. 3) Records indicate that salaries for the last week of December amounted to $250 and would be paid at the end of the first week in January (a two-week pay period) 4) The company uses straight-line depreciation for all of its store fixtures and office equipment. Below is a schedule of the store fixtures and office equipment at the end of 2023. It determines that the useful life of office equipment (ID#4299) is extended to 7 years with a salvage value of $600. FIXTURES AND EQUIPMENT (as of December 31, 2023) ID # Historical Cost Estimated Useful Life Estimated Salvage Value Date acquired 1256 $15,000 10 years $1,000 Jan. 1, 2017 1876 $1,500 10 years $200 Jan. 1, 2018 4299 $20,000 5 years $0 Jan. 1, 2020 5422 $8,300 5 years $500 Oct. 1, 2023 REQUIREMENT: 1. Correct the chart of accounts: The Chart of Accounts worksheet lists the accounts needed for the company, but in the wrong order. Put the accounts in the proper order so that they will appear in the correct order on the trial balance and the financial statements. Use an account coding system with four-digit account numbers where all asset accounts begin with "1", liability accounts begin with "2", etc. 2. Set up the General Ledger: Go to the GL worksheet. Create a general ledger account for each account in the Chart of Accounts by entering the account number and name at the top of 2 the account. In the explanation column enter "Beg. Balance". Enter the prior-year account balances in the "Balance" debit or credit columns by referencing the appropriate cell in the prior year's Balances Sheet. DO NOT ENTER THE AMOUNTS MANUALLY. If it is a negative number on the Balance Sheet, multiply it with -1 to obtain a positive number. You may create additional ledger accounts by copying and pasting the format given for the first two accounts. 3. Journalize transactions: go to the GJ worksheet. Journalize all transactions for the year of 2023. Input the letter of transaction (e.g. a or c) under column 'Explanation' for each journal entry. (Under the 'Date' column, report year only if month is not available for the transaction. List the journal entries in the order of letters that transactions are listed above.) 4. Post to the General Ledger: post your journal entries to the GL worksheet by referencing the appropriate cell in the GJ worksheet. DO NOT ENTER THE AMOUNTS MANUALLY. Update the account balance after each transaction. In the "Ref column in the GJ enter the account number the debit or credit was posted to; in the "Ref" column in the GL enter GJ102 for all entries. 5. Prepare a trial balance: use a worksheet to prepare a trial balance. Go to the "Worksheet" tab and enter the December 31, 2023 account balances by referencing the appropriate cells in the GL worksheet. (See Appendix 24 after Chapter 2 for a sample worksheet) DO NOT ENTER THE AMOUNTS MANUALLY. Be sure the trial balance balances before continuing with the next step. 6. Prepare and post adjusting entries: journalize the adjusting entries in the GJ with a balance sheet date. Post the adjusting entries to the GL worksheet by using cell references and update account balances. 7. Prepare an adjusted trial balance: on the worksheet, enter the adjusted trial balance using formulas to combine the trial balance and adjusting entry amounts. 8. Finish the worksheet: extend the account balances from the adjusted trial balance columns to the income statement or balance sheet columns using cell references. 9. Prepare a detailed multiple step Income Statement in good form on the income statement worksheet. DO NOT ENTER DOLLAR AMOUNTS MANUALLY. Remember to calculate and show earnings per share after net income. (Assume the average common stock shares outstanding is 25,000 for the current year) 10. Prepare the Statement of Retained Earnings in good form on the Retained Earning worksheet. DO NOT ENTER DOLLAR AMOUNTS MANUALLY. Reference cells in the Worksheet. 11. Prepare Balance Sheet in good form on the Balance Sheet worksheet. DO NOT ENTER DOLLAR AMOUNTS MANUALLY. Reference cells in the worksheet. 12. Prepare and post closing entries: journalize the closing entries in the GJ worksheet and post them to the GL worksheet. Update account balances in the GL. 3 13. Post closing trial balance: prepare a post-closing trial balance on the last worksheet by referencing ending account balances in the GL worksheet. 14. Answer short questions: a) Suppose you estimate that 12% of the receivables are uncollectible. What would be the effect on the net income (show both the dollar amount and percentage change)? b) What is earnings management? c) Give two examples on how to manage earnings up within GAAP. 15. FASB codification exercise: Answer the following questions. Indicate the specific codification citation (XXX-XX-XX-X) in the Accounting Standard Codification and show the relevant statement. a) If you need to prepare a cash flow statement, which section should interest payments be included in the cash flow statement? b) If the company spent $12,000 to develop new design software for its own use, the software is not fully functional at the end of the year, should the cost of software be expensed or capitalized (listed as an intangible asset)? FASB Accounting Standard Codification free access through https://asc.fasb.org/Login 16. Review the final project for spelling, grammatical, formatting, and technical errors. Print final copies. (Color copies are not required). By 5 pm on March 21, hand in all the worksheets, staple them together and submit your final Excel file on Canvas. Your final project score will include points for the accuracy of the project and the overall appearance of the final package you submit. You may need to change the page setups in Excel for your printer. Your project is not complete until you have handed in the above items and your final Excel file has been submitted. Late projects will NOT be accepted. 4