Spreadsheet Assignment Use of ExcelAdams Company started operations on January 1, 2011. Si...

Spreadsheet Assignment Use of Excel

Adams Company started operations on January 1, 2011. Six months later on June 30J 2011, the company decided to prepare financial statements. The company’s accountant decided to problem solve for the adjusting journal entries and the final adjusted account balances by using an electronic spreadsheet. Once the spreadsheet is complete, she will record the adjusting entries in the general journal and post to the ledger. The accountant has started the following spreadsheet but wants you to finish it for her.

Required

a. On a blank spreadsheet, enter the following trial balance in Columns A through C. Also enter the headings for Columns E through I.

b. Each of the following events requires an adjusting journal entry. Instead of recording entries in general journal format, record the adjusting entries in the Debit and Credit columns under the heading Adjusting Journal Entries. Entry (1) has already been recorded as an example. Be sure to number your adjusting entries on the spreadsheet. It will be necessary to insert new accounts for the adjustments. Recall that the accounting period is for six months.

(1) Received a $5,000 cash advance on April 1 for a contract to provide five months of service.

(2) Had accrued salaries on June 30 amounting to 51,500.

(3) On January 1 invested in a one-year, $10,000 certificate of deposit that had a 5 percent interest rate.

(4) On January 1 paid $12,000 in advance for a one-year lease on office space.

(5) Received in the mail a utility bill dated June 30 for $150.

(6) Purchased $1,500 of supplies on January 1. As of June 30, $700 of supplies remained on hand.

(7) Paid $9,000 for office equipment on January 1. The equipment was expected to have a four-year useful life and a $1,000 salvage value. Depreciation is computed on a straight- line basis.

c. Develop formulas to sum both the Debit and Credit columns under the Adjusting Journal Entries heading.

d. Develop formulas to derive the adjusted balances for the adjusted trial balance. For example, the formula for the ending balance of Unearned Revenue is =C10-E10+G10. In other words, a credit balance minus debit entries plus credit entries equals the ending balance. Once an ending balance is formulated for one credit account, that formula can be copied to all other credit accounts; the same is true for debit accounts. Once an ending balance is formulated for a debit account, that formula can be copied to all other debit accounts.

e. Develop formulas to sum both the Debit and Credit columns under the Adjusted Trial Balance heading.

Spreadsheet Tops

1. Rows and columns can be inserted by positioning the mouse on the immediate row or column after the desired position. Click on the right mouse button. With the left mouse button, choose Insert and then either Entire Column or Entire Row. Use the same method to delete columns or rows.

2. Enter the sequential numbering of the adjusting entries as labels rather than values by positioning an apostrophe in front of each entry. The first adjusting entry should be labeled (1).