Accounting

Posted: December 10th, 2013

CC 325 – 1st Spreadsheet assignment
Given are the following beginning balances of Meredith Bookstore for December 2011:

Cash 45000 Common Stock 45000

a. Make journal entries in Excel for the following events that occurred during December 2011:

1. Meredith purchases equipment for $15,000 cash on Dec. 1st. It has no salvage value and has a useful life of five years. Also compute and make the entry for depreciation for the month of December on the equipment (only one month).

2. The company buys inventory on account for 12,000.

3. The company sells books for 21,000 on account. The books cost 10,000. Make the entries for the sale on account and the charge to cost of goods sold.

4. The company pays rent in cash for 2,800.

5. The company pays salaries in cash for 5,500.

b. Prepare (by hand) the (adjusted) Trial Balance, Income Statement, Statement of Retained Earnings, and Balance Sheet for December 31, 2011. Beginning balance of Retained Earnings is 0. No dividends are paid. The income tax rate is 30%.

c. Then transfer this information into an Excel Financial Worksheet. Put the title of your worksheet and your name in the first row. Then skip a line or two and in the first column, type “Accounts”. Below that, type the names of the accounts that are used in the problem. You should type them beginning with Assets (Cash, Accounts receivable, etc.), then liabilities and equity accounts, followed by Revenue, Cost of Goods Sold, and Expense accounts.

d. The headings across the page, level with “Accounts”, will be as follows, and will have subheadings underneath with a column for debits (Dr.) and credits (Cr.):

Trial Balance Adjustments Adjusted Trial Balance Income Statement Balance Sheet
Dr. Cr. Dr. Cr. Dr. Cr. Dr. Cr. Dr. Cr.

e. Fill in the numbers from the problem. The numbers in the later columns should not be typed, but should be automatically picked up from the “Trial Balance” and “Adjustments” column.
1. For the “Adjusted Trial Balance” column, your formulas will depend on whether the number is an asset, contra-asset, or liability. For example – if Cash is in cell C6, and Adjustments are in column E (debits) and F (credits), then to get the Cash account balance to the Adjusted Trial Balance cell G6, in cell G6 you would type “=C6 E6-F6” – without the quotes. If the number in the Accounts column is a liability, then it will be entered in column H. So a balance in cell H11 would have the formula: “=D11-E11 F11” – without the quotes.
2. For the Income Statement and Balance sheet columns, (click on the cell where you want the number to be, then type an “=” sign – without the quotes – followed by the current cell location of the number). Add formatting with commas and proper underscores, and use the Excel “Sum” function to compute the totals.

e. Prepare a properly formatted Income Statement and Balance Sheet. (Note: there
is a way to automatically pick up the numbers for the income statement and balance sheet from the worksheet – we will discuss in class).

f. Print the spreadsheet(s), or attach the spreadsheet to an email to turn in.

g. Below is an example of how the layout should look. Remember, all of your numbers in the Adjusted Trial Balance, Income Statement and Balance Sheet columns must be computed from formulas:

Accounts Trial Balance Adjustments Adjusted Trial Balance Income Statement Balance Sheet
Dr. Cr. Dr. Cr. Dr. Cr. Dr. Cr. Dr. Cr.
Cash
Accounts Receivable
Inventory
Equipment
Accumulated Depreciation ( )
Accounts Payable
Taxes Payable
Common Stock
Retained Earnings
Revenue
Cost of Goods Sold
Rent Expense
Depreciation Expense
Salary Expense
Tax Expense
Totals
Net income xxx
59,450 59,450