The ledger of AISExperts Inc. showed the following balances after adjustment, but before closing, on December 31, 2015, the end of the current year:
Accounts payable 78000
Accounts receivable 102000
Accumulated depreciation - equipment 27000
Depreciation expense 1200
Interest revenue 1025
Common stock (10,000 shares outstanding) 100000
Cost of merchandise sold 634550
Dividend declared 19100
General expenses 114250
Dividend revenue 5000
Interest expense 5600
Merchandise inventory 154250
Supplies expense 1300
Prepaid insurance 11225
Retained earnings ?
Salaries Payable 14550
Selling expenses 140700
Please help me develop a flexible financial statement package using Excel that includes:
- a data entry sheet (should include the information above)
- a trial balance (link to the data entry sheet),
- a single-step income statement,
- a multi-step income statement,
- a statement of retained earnings,
- a classified balance sheet, and
- a post-close trial balance (a trial balance with only accounts not "closed")
Each statement must be on one sheet in the file and this file should allow the financial statements to be prepared quickly by entering account balances in the appropriate cells on the first sheet of the file (book) - data entry sheet.
Include percentages in the Income Statement (vertical analysis) i.e., gross profit percentage, % of selling expenses to sales, etc. (common sizing and vertical analysis are basically the same analysis techniques - every number in the multi-step income statement is divided by sales).© BrainMass Inc. brainmass.com October 25, 2018, 10:03 am ad1c9bdddf
This comprehensive solution illustrates how to organize accounts and amounts into a pre-closing trial balance, how to use that trial balance to create a single-step common-size income statement, a multi-step common-size income statement, a statement of retained earnings, a balance sheet, and a post-closing trial balance.
Comprehensive Accounting Problem
Miller Design Studio's July 31, 2011 trial balance is presented here:
Miller Design Studio
Post-Closing Trial Balance
July 31, 2011
Accounts Receivable 5,000
Office Supplies 3,660
Prepaid Rent 1,600
Office Equipment 16,320
Accumulated Depreciation?Office Equipment $ 300
Accounts Payable 6,280
Unearned Design Revenue 600
Wages Payable 720
J. Miller, Capital 41,160
During August, the studio engaged in these transactions:
1 Received an additional investment of cash from J. Miller, $20,000.
2 Purchased additional office equipment with cash, $4,700.
7 Purchased additional office supplies for cash, $540.
8 Completed the series of designs that began on July 31 and billed for the total design services performed, including the accrued revenues of $800 that had been recognized in an adjusting entry in July, $1,400.
12 Paid the amount due for the office equipment purchased last month, $3,000.
13 Accepted an advance in cash for design work to be done, $2,400.
15 Performed design services and received a cash fee, $2,900.
16 Received payment on account for design services performed last month, $2,800.
19 Made a partial payment on the utilities bill that was received and recorded at the end of July, $140.
20 Performed design services for Rave Department Stores and agreed to accept payment next month, $3,200.
21 Performed design services for cash, $1,160.
22 Received and paid the utilities bill for August, $900.
23 Paid the assistant for four weeks' wages, $4,800.
26 Paid the rent for September in advance, $1,600.
30 Paid cash to J. Miller as a withdrawal for personal expenses, $2,800.
1. Record entries in journal form and post to the ledger accounts the optional reversing entries on August 1 for Wages Payable and Accounts Receivable (see adjustment for unrecorded wages on page 116 and adjustment for design revenue on page 119). (Begin the general journal on page 5.)
2. Record the transactions for August in journal form.
3. Post the August transactions to the ledger accounts.
4. Prepare the Trial Balance columns of a work sheet.
5. Prepare adjusting entries and complete the work sheet using the information below.
a. One month's prepaid rent has expired, $1,600.
b. An inventory of supplies reveals $2,020 still on hand on August 31.
c. Depreciation on equipment for August is calculated to be $300.
d. Services performed for which payment had been received in advance totaled $1,300.
e. Services performed that will not be billed until September totaled $580.
£ Wages accrued by the end of August, $720.
6. From the work sheet, prepare an income statement, a statement of owner's equity, and a balance sheet for August 31, 2011.
7. Record the adjusting entries on August 31, 2011, in journal form, and post them to the ledger accounts.
8. Record the closing entries on August 31, 2011, in journal form, and post them to the ledger accounts.
9. Prepare a post-closing trial balance at August 31, 2011.