This project will take you through a complete accounting cycle, starting with journalizing transactions and ending with a post-closing trial balance. We will talk more about accounting cycle and worksheet in Chapter 2, however all theoretical knowledge that you may need to post transactions, and to prepare financial statements, you have achieved in Accounting 284 and Accounting 301. If you feel stuck on a given entry, it would be wise to review an appropriate topic in your Accounting 284 textbook or our current textbook (See ‘Hints’ file for references). A successful completion of this project will be a strong indicator of your readiness for Accounting 386/387 material.
Due dates:
Part 1 – Wednesday, September 9, 4:00 p.m. (Parts 1 – 9, see. pp. 4–5) – 25% of the grade
Final Submission – Wednesday, October 7th, 4:00 p.m. (Full project, Parts 1–15) – 75% of the grade
***Read through entire project at least twice before you begin. This will help you familiarize yourself with all of the important parts***
***Use Hints file on Canvas. Print it out and have it available for you as your work on the project. This file has developed over many years to address questions that your predecessors kept asking***
The Excel template for the project is available on Canvas under “Project”. You must use the Excel file on Canvas. Do not create new Excel Spreadsheet file and do not use Google Docs. You must finish the following tabs to complete the project:
Cost of Goods Sold and Depreciation Schedule
General Journal (GJ)
General Ledger (GL)
Worksheet (use Appendix 2A for reference)
Income Statement (ignore EPS)
Statement of Retained Earnings
Balance Sheet
Cash Flow Statement
Post-Closing Trial Balance
Any changes made in the journal entry must be carried forward though the entire workbook.
You manage a downtown store, which sells chocolate candy. The candies are sold in boxes. Each box can contain different varieties of chocolates, such as milk chocolate, dark chocolate, truffles, pralines, assorted, etc., but each box has the same price. You purchase candy boxes from a third party. For financial reporting purposes you track inventory in boxes (box is a “unit”) without regards to what mix of chocolate candies is inside. The name of the store is “X Y Chocolate Delights, Inc.”, where X and Y are your first and last names respectively. Transactions for 2017 are representative of this company’s business activities and are listed below (record all transactions alphabetically, a, b, c, and so on, not by date)
a. The company began 2017 with 3,075 boxes on hand which had a cost of $5.00 each. Employees physically counted 2,393 boxes in the warehouse at the end of 2017. The company uses a perpetual FIFO inventory system to cost the inventory. The following purchases (all on account) were made during 2017
| Date | Units | Cost per unit |
| 15-Jan | 5,331 | $ 5.00 |
| 17-Mar | 6,899 | $ 4.95 |
| 10-May | 8,002 | $ 5.15 |
| 7-Oct | 5,078 | $ 5.25 |
b. Information about sales is as follows:
| Date | Units | Price | Type |
| 30-Jan | 5,050 | 10.00 | cash |
| 11-Apr | 2,005 | 10.00 | credit |
| 4-Jun | 8,155 | 10.00 | credit |
| 19-Aug | 2,458 | 10.50 | cash |
| 1-Nov | 2,825 | 10.50 | credit |
| 15-Dec | 5,499 | 10.50 | cash |
c. Cash collections from customers (on credit sales) amounted to $115,000 during 2017.
d. During 2017, the company made cash payments to chocolates’ suppliers totaling $105,000.
e. On March 31, new store fixtures (PPE, ID# 1984) were purchased for $6,450 and paid for in cash.
f. The company made cash payment for insurance policy premium of $5,400 on April XXXXXXXXXXThe insurance policy is for store building and covers one year staring on April XXXXXXXXXXPrepaid Insurance balance on the 2016 Balance Sheet represents insurance premium payment covering January 2017 – March 2017).
g. On April 15, 2017, the company paid 2016 income taxes. Income tax payable accrual adjusting entry was properly recorded on 12/31/2016.
h. On October 1, 2017, the company borrowed $18,000 on a new 5-year note payable. The note carries a 5.0% annual interest rate, and requires semi-annual interest payments.
i. On November 1, 2017, $230 of receivables were deemed uncollectible and were written off.
j. The Note Payable outstanding at the beginning of the year had 6% annual interest rate, had been issued on December 1, 2014, matures in 5 years from the date of issue, and pays interest annually on November 30.
k. On December 1, the company paid off note payable referenced in transaction (j).
l. On December 31, 2017, old color laser printer (ID # 1982) was sold for $396. It was acquired on January 1, 2014 at a cost of $1,200, its estimated salvage value was $50 and estimated useful life was 5 years. The company depreciated the printer on a straight line basis. (The year an asset is sold, partial depreciation expense for that asset is recorded to the nearest full month, and dated with the day of sale, not as an adjusting entry).
m. During 2017, company paid $85,550 in cash for wages. This amount includes both wages earned during 2017 and wages owed at the beginning of 2017. An adjusting entry to accrue wages expense was properly recorded on 12/31/16.
n. 500 shares of common stock ($1 par) were issued on December 31, 2017 for $6,500.
o. Other expenses (paid in cash during 2017) totaled $6,060.
p. Dividends of $7,325 were paid on December 31, 2017.
q. Salaries and wages expense accrued, but unpaid on December 31, 2017 amounted to $3,700.
Additional Information for Adjusting Entries (reference adjusting entries as a1, a2, a3, etc.)
1) The company follows Balance Sheet (Allowance) approach to estimate uncollectible receivables, and expects 3.5% of ending gross accounts receivable to be uncollectible.
2) The company uses straight line depreciation for all store fixtures and office equipment (ID#1981 and ID#1984). The company depreciates equipment with ID #1983, which is a truck using activity method. The truck was driven a total of 24,500 miles since the day it was purchased (12,000 miles in 2016 and 12,500 miles in XXXXXXXXXXTotal estimated mileage for the truck is 100,000 miles. Depreciation expense is typically recorded once at the end of the year. Partial depreciation is calculated to the nearest full month.
Below is a schedule of the store fixtures and equipment at the end of 2017. (You need to calculate depreciation expense for each asset).
| ID# | Cost | Useful Life | Salvage Value | Date Acquired |
| 1981 | $14,000 | 10 years | $1,000 | 1/1/2011 |
| 1983 | $36,000 | 5 years | $10,000 | 1/1/2016 |
| 1984 | $6,450 | 15 years | $450 | 3/31/2017 |
3) Review the trial balance and identify any additional adjusting entries that may be needed, such as interest expense or rent expense.
4) Income tax rate for 2017 is 25%.
Requirements and instructions:
Submission: Submit on Canvas on each due date: Excel File
1. Fill out the cover page: enter your name(s) and date of submission.
2. Enter your company’s name at the top of each worksheet, general journal, general ledger, and financial statements.
3. Format all numeric cells to have two numbers after the decimal.
4. Prepare COGS and depreciation schedules. You pick which table format to choose. This worksheet is supplementary for the reader, thus, it should clearly and in detail show how you calculate COGS, depreciation expense for 2017 and accumulated depreciation at the end of 2017. You should use Excel formulas to obtain numbers beyond the information given in this assignment. Do not calculate COGS and depreciation by hand and enter into schedules manually. Reference any related journal entries to this worksheet.
5. Set up 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 the account. In the explanation column enter “Beg. Balance”. Enter the December 31, 2016 account balances in the “Balance” debit or credit columns by referencing the appropriate cell in the XXXXXXXXXXBalances 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 accounts.
6. Journalize Transactions. Go to the General Journal worksheet. Journalize all transactions for 2017. Input the letter of transaction (e.g. a to q) under column ‘Expl.’ for each journal entry. Under each debit and credit entry use a line to provide explanation of the transaction, in italics, ex. “to record cash sales”. In the “Ref’ column in the GJ enter the account number the debit or credit was posted to. (Under the ‘Date’ column, report year only if month/day is not available for the transaction. List the journal entries in the alphabetical order.)
7. 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 “Explanation” column in the GL enter the transaction numbers a through p.
- Prepare a trial balance: use a worksheet to prepare a trial balance. Go to the “Worksheet” tab and enter the December 31, 2017 account balances by referencing the appropriate cells in the GL worksheet. DO NOT ENTER THE AMOUNTS MANUALLY. Be sure the trial balance balances before continuing with the next step.
- Prepare and post adjusting entries: journalize the adjusting entries in the GJ with a Dec. 31, 2017 date. Post the adjusting entries to the GL worksheet by using cell references and update account balances. Reference adjusting entries in general ledger using a1, a2, a3, etc. notation.
- Prepare an adjusted trial balance: on the worksheet, enter the adjusted trial balance using formulas to combine the trial balance and adjusting entry amounts.
- Finish the worksheet: extend the account balances from the adjusted trial balance columns to the income statement or balance sheet columns using cell references.
12. Prepare a detailed multiple step income statement for the year ended Dec. 31, 2017 in good form on the income statement worksheet. DO NOT ENTER DOLLAR AMOUNTS MANUALLY, reference the “Worksheet”.
13. Prepare the statement of retained earnings for 2017 in good form on the Retained Earning worksheet. DO NOT ENTER DOLLAR AMOUNTS MANUALLY. Reference cells in the Worksheet.
14. Prepare the December 31, 2017 balance sheet in good form on the Balance Sheet worksheet. DO NOT ENTER DOLLAR AMOUNTS MANUALLY. Reference cells in the worksheet.
15. Prepare the Cash Flow Statement for 2017 in good form on the CFS worksheet. To prepare a cash flow statement use the indirect method for Cash Flows from Operations section. For cash flows from financing and investing activities, carefully review all transactions a) - p) and note any cash inflows or outflows relating to financing or investing activities (those involving non-current assets and non-current liabilities). Make sure to reference differences in current assets and current liabilities, do not enter amounts manually. Reference investing and financing activities’ amounts from the general journal or “Cash” general ledger.
16. 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. Reference closing entries in General Ledger as c1, c2, etc.
17. Post closing trial balance: prepare a post-closing trial balance on the last worksheet by referencing ending account balances in the GL worksheet. Do not leave accounts with zero balances in the post-closing trial balance.