MBA 520 Comprehensive Project.xls



 

 

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.

 

  1. Prepare a Statement of Retained
    Earnings for the year ended 2015

    This statement should be flexibly designed.


 

  1. 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.


 

  1. 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.


 

  1. 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:
    1. ROE
      for 2015
    2. ROA
      for 2015
    3. RNOA
      for 2015
    4. Stockholders’
      Equity for 2014 and 2015
    5. NOPAT
      for 2015
    6. NOA
      for 2014 and 2015
    7. Current
      Ratio for 2014 and 2015
    8. Quick
      Ratio for 2014 and 2015
    9. 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):


  1. Sales for 2015 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 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.

  1. 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.
  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/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).
  5. $55,000 in cash is
    borrowed on 9/30/15 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, 2015.
  7. 10,000 additional shares
    of stock were sold for $3 a share.
  8. 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).
  9. On Dec. 31, 2015 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 2014 taxes, however
    the 2015 taxes will not be paid until the end of January 2016).
  11. Dividends of $3,000 were
    paid during 2015.
  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 2015):


 

  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
    2015 (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 2015
  9. Analysis


 

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






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




















 

 


Powered by