SOLVED:Cashflow statement,Trial balance and analysis




MBA520– Comprehensive Financial Accounting Project   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 2015.  This statement should be flexibly designed (formulas in cells).  This should be a multi-step income statement (see video and/or exhibit 4.1 on pg. 4-5).  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 2015 have been posted).  You can add a transaction analysis (not required), however you must complete the entries in the Excel template.

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

Prepare a Balance Sheet dated Dec. 31, 2015.  Have the Balance Sheets for 12/31/14 and 12/31/15 on the same Excel sheet labeled Balance Sheets.  Again, a flexible design is required so any changes will automatically update the balance sheet.

Prepare a Statement of Cash Flows using the indirect method for the year ended 2015.  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.

Analysis:  On a separate sheet titled “Analysis” compute the following and show in a table (show your work below your table); your table should look similar to that on page 4-21:
ROE for 2015
ROA for 2015
RNOA for 2015
Stockholders’ Equity for 2014 and 2015
NOPAT for 2015
NOA for 2014 and 2015
Current Ratio for 2014 and 2015
Quick Ratio for 2014 and 2015
Liabilities-to-Equity Ratio for 2014 and 2015

Your Name, Inc. Balance Sheet 12/31/2014                             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 2018                   $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):

Sales for 2015 are $310,000.  All sales are on credit.
Gross Margin ratio is 40 percent
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.


i.      Inventory purchases are $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.

Additional equipment is purchased on 4/1/15 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.
The building depreciates at $5,000 per year.
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.
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/15 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).
$55,000 in cash is borrowed on 9/30/15 by issuing a Note Payable. Interest is 8% per year.
The bonds were sold at face value last December and pay interest on Dec. 31, 2015.
10,000 additional shares of stock were sold for $3 a share.
Insurance costing $18,000 was purchased on 6/1/15 (the same time in which the policy purchased in 2014 expired.  The new policy was for 12 months).
On Dec. 31, 2015 shares of stock are repurchased from the market at $2.90/share (treasury stock).
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 2014 taxes, however the 2015 taxes will not be paid until the end of January 2016).
Dividends of $3,000 were paid during 2015.
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 2015):

Data Sheet for Additional Data
Entries: Basic and Adjusting (you do not have to show closing entries, however keep in mind all temporary accounts are closed to retained earnings)
Adjusted Trial Balance for 2015 (includes the posted amounts of all entries and adjusting entries)
Multi-step Income Statement
Retained Earnings Statement
Classified Balance Sheet
Cash Flow Statement
Post-Close Trial Balance for 2015

The Post-Close Trial Balance for 2014 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-14
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

100,000 Common Stock
50,000 Additional Pd-in-Capital
80,000 Retained Earnings   22,000
333,000 333,000