Comprehensive Articulation Project
This project should be completed using Excel (with formulas and linked data). Below are the deliverables:
1. Prepare a Multi-Step Income Statement for the year ended 2017. This statement should be flexibly designed (formulas in cells). To the right of your dollars in this statement, show common-sized percentages based on sales (vertical analysis).
2. Show journal entries, adjusting entries and closing entries for the below additional information...none of the journal entries for 2017 have been posted to the ledger.
3. Prepare a Statement of Retained Earnings for the year ended 2017. This statement should be flexibly designed.
4. Prepare a Classified Balance Sheet dated Dec. 31, 2017. Again, a flexible design is required so any changes will automatically update the balance sheet.
5. Prepare a Statement of Cash Flows using the indirect method for the year ended 2017. The Statement of Cash Flows (operating section) should automatically change when assumptions are changed.
Your Name, Inc. Balance Sheet 12/31/2016
Current Assets
- Cash $18,000
- Marketable Securities (Short-term) 2,000
- Accounts Receivable 14,000
- Allowance for Bad Debt (2,000)
- Inventory 15,000
- Prepaid Insurance 5,000
- Total Current Assets $52,000
Property, Plant, and Equipment
- Land $30,000
- Building 150,000
- Accumulated Dep. - Building (45,000)
- Equipment 100,000
- Accumulated Dep. - Equipment (20,000)
- Total PPE $215,000
- Total Assets $267,000
Current Liabilities
- Accounts Payable $9,000
- Unearned Revenue 3,000
- Income Taxes Payable 3,000
- Total Current Liabilities $15,000
Long-term Liabilities
- Bonds, 10%, due in 2021 $100,000
Equity
- Common Stock (100,000 authorized, 50,000 issued) $ 50,000
- Additional Pd.-in Capital 80,000
- Retained Earnings not given (must be calc.)
- Total Equity $152,000
Total Liabilities & Equity $267,000
Additional Information (for all entries):
1. Sales for 2017 are $250,000. All sales are on credit.
2. Gross Margin/Profit ratio is 40 percent
3. Accounts Receivable:
i. $180,000 of the accounts receivable is paid by the end of the year (the remaining balance remains on the balance sheet).
ii. $3,000 of A/R is written off during the year.
iii. 5% of Accounts Receivable (after write-off and collections) is considered to be uncollectible.
4. Inventory:
i. Inventory purchases is $175,000, all on credit (the periodic method is used).
ii. All accounts payable is from inventory purchases; all but $12,000 of inventory purchased is paid by the end of the year.
5. Additional equipment is purchased on 4/1/17 for $20,000 cash. All equipment when new, including the new purchase, has/had a five year life, no salvage value, and is depreciated using the straight-line method.
6. The building depreciates at $5,000 per year.
7. Half of the marketable securities were sold for $1,300. The FMV of the other half of the securities is also $1,300 and an adjustment to FMV is required.
8. Salaries are $2,100 per month (12 months of salaries expense must be booked). It is expected that one-half month will be owed on 12/31/17 because of when payday falls (therefore, 11.5 months of salaries have been paid and ½ month is still owed to the employees at year end).
9. $60,000 in cash is borrowed on 10/31/17 by issuing a Note Payable. Interest is 8% per year.
10. The bonds were sold at face value last December and pay interest on Dec. 31, 2017.
11. 10,000 additional shares of stock were sold for $4 a share (for EPS purposes, assume these shares were outstanding all year).
12. Insurance costing $20,000 was purchased on 7/1/17 (the same time in which the policy purchased in 2016 expired. The new policy was for 12 months).
13. On Dec. 31, 2017, 1000 shares of stock are repurchased from the market at $2.80/share (treasury stock).
14. The tax rate is 30 percent. Income taxes for the current year are due and therefore paid during the first two months of the next year (you will have complete an entry to pay the 2016 taxes, however the 2017 taxes will not be paid until the end of January 2018).
15. Dividends of $4,000 were paid during 2017.
16. The unearned revenue has been earned during the year (classified as other revenue on the multi-step income stmt.).
Required Labeled Sheets (all statements should be for 2017):
1. Data Sheet (the ending balances from 2016)
2. Entries: Basic and Adjusting (you do not have to separate these entries)
3. Adjusted Trial Balance for 2017 (includes the posted amounts of all entries and adjusting entries)
4. Multi-step Income Statement
5. Retained Earnings Statement
6. Classified Balance Sheet
7. Cash Flow Statement (using the indirect method)
8. Post-Close Trial Balance for 2017 (include closing entries above the post close trial balance)