this project should be completed using excel with

Question

This project should be completed using Excel (with formulas and linked data). The parameters of the project are below:

1. Prepare an Income Statement for the year ended 2011. This statement should be flexibly designed (formulas in cells). This should be a multi-step income statement. 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 have been posted to the ledger (many journal entries have been booked to get you started, however none of the entries for 2011 have been posted). You can add a transaction analysis (not required), however you must show actual journal entries that include debits and credits.

3. Prepare a Statement of Retained  Earnings for the year ended 2011.  This statement should be flexibly designed.

4. Prepare a Balance Sheet dated Dec. 31,  2011. Have the Balance Sheets  for 12/31/10 and 12/31/11 on the same Excel sheet labeled Balance Sheets. 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 2011. The Statement of Cash Flows (operating  section) should automatically change when assumptions are changed. The ending cash as shown on the  statement of cash flows will then flow to the Balance Sheet. Cash flow videos are still available in  the classroom for your review and appendix B in your textbook contains  additional information that you might find helpful.

6. Analysis: on a separate sheet titled "Analys is" compute the following and show in a table (show your work below y our table); your table should look similar to that on page 4-21:

1. ROE  for 2011
2. ROA  for 2011
3. RNOA  for 2011
4. Stockholders'  Equity for 2010 and 2011
5. Net  Income for 2011
6. NOPAT  for 2011
7. NOA  for 2010 and 2011
8. NOPM  for 2011
9. NOAT  for 2011
10. NNO  for 2010 and 2011
11. Current  Ratio for 2010 and 2011
12. Quick  Ratio for 2010 and 2011
13. Liabilities-to-Equity  Ratio for 2010 and 2011

Your Name, Inc.

Balance Sheet

12/31/2010

Current Assets

Cash $17,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 $51,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 $266,000

Current Liabilities

Accounts Payable $9,000

Unearned Revenue 2,000

Income Taxes Payable 3,000

 Total Current Liabilities $14,000

Long-term Liabilities

Bonds, 10%, due in 2015 $100,000

Equity

Common Stock $ 50,000

 (100,000 authorized, 50,000 issued)

Additional Pd.-in Capital 80,000

Retained Earnings 22,000

 Total Equity $152,000

Total Liabilities & Equity $266,000

Additional Information (for all entries; please see the posted Excel spreadsheet with a few journal entries already provided):

1. Sales for 2011 are $310,000. All sales are on credit.
2. Gross Margin ratio is 40  percent
3. Accounts Receivable:

i. $190,000 of the accounts receivable is paid by the end of the year (the remaining balance remains on the balance sheet). 

ii. $4,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.

1. Inventory:

i. Inventory purchases is $180,000, all on credit. 

ii. All accounts payable is from inventory purchases; all but $12,000 of inventory purchased is paid by the end of the year.

1. Additional equipment is purchased  on 4/1/11 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.
2. The building depreciates  at $5,000 per year.
3. Half of the marketable  securities were sold for $1,200. The FMV and cost of the other half of the  securities are the same, so no adjustment to FMV is required.
4. Salaries are $2,200 per  month (12 months of salaries expense must be booked). It is expected that one-half month will  be owed on 12/31/11 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).
5. $55,000 in cash is borrowed  on 9/30/11 by issuing a Note Payable. Interest is 8% per year.
6. The bonds were sold at  face value last December and pay interest on Dec. 31, 2011.
7. 10,000 additional shares  of stock were sold for $3 a share.
8. Insurance costing $18,000  was purchased on 6/1/11 (the same time in which the policy purchased in 2010  expired. The new policy was for 12  months).
9. On Dec. 31, 1000 shares of  stock are repurchased from the market at $2.90/share (treasury stock).
10. 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 2010 taxes, however  the 2011 taxes will not be paid until the end of January 2012).
11. Dividends of $3,000 were  paid during 2011.
12. 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 2011):

1. Data Sheet for Additional  Data
2. Entries: Basic and  Adjusting (you do not have to show closing entries, however keep in mind  all temporary accounts are closed to retained earnings)
3. Adjusted Trial Balance for  2011 (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
8. Post-Close Trial Balance  for 2011
9. Analysis

The Post-Close Trial Balance for 2010 is provided below (based on the above balance sheet). This can be used as a starting point or you can use the above Balance Sheet; keep in mind all debits and credits ALWAYS equal AND Assets = Liabilities + Equity:

Your Name, Inc.
Post Close Trial Balance
31-Dec-10
	DEBITS	CREDITS
Cash	17,000
Marketable Securities	2,000
Accounts Rec.	14,000
Allowance for Bad Debt		2,000
Inventory	15,000
Prepaid Insurance	5,000
Land	30,000
Building	150,000
Accumulated Dep. - Building		45,000
Equipment	100,000
Accumulated Dep. - Equipment		20,000
Accounts Payable		9,000
Salaries Payable
Unearned Revenue		2,000
Interest Payable
Income Taxes Payable		3,000
Note Payable
Bonds		100,000
Common Stock		50,000
Additional Pd-in-Capital		80,000
Retained Earnings		22,000
	333,000	333,000