Excel Applications for Accounting Principles - Excel

Document Sample
Excel Applications for Accounting Principles - Excel Powered By Docstoc
					CORPORATE TRANSACTIONS                                                      FTRANS

Excel Applications for Accounting Principles: Website Alternative Problem
by Gaylord N. Smith


* Show how financial position is affected by transactions.
* Prepare three basic corporate financial statements.
* Back-solve for cash balances.
* Alter the worksheet to accommodate additional transactions.
* Create a chart showing liability and equity categories.


On October 1 of the current year, Justin Clarkson opened Clarkson Talent Agency, Inc.
The corporation had the following transactions during October.

a. Opened a business checking account and made a deposit, $5,000. In exchange,
Justin was issued 250 shares of $20 par value stock.
b. Paid rent for October for office space and equipment, $1,000.
c. Purchased office supplies (stamps, pens, etc.) on account, $680.
d. Received cash for services rendered, $1,500.
e. Paid creditor for office supplies purchased on account, $500.
f. Purchased office supplies for cash, $190.
g. Billed clients for consultations performed on account, $2,200.
h. Paid utility bill of $180.
i. Paid the secretary's salary of $1,200.
j. Declared and paid dividends of $500.
k. Received cash from clients previously billed, $1,500.
l. Returned $150 of office supplies purchased in transaction f above. Received a full

1) You are to record these transactions using a spreadsheet format. Click the Answer
tab and enter your name in cell C5. Then click the Worksheet tab and enter your name
in cell C1 of the worksheet. There are six formulas needed to complete the worksheet.
Enter the formulas in the appropriate cells. Then enter the increases and decreases
resulting from each transaction on the worksheet. For example, the first transaction
increases the cash account by $5,000 and also increases the common stock account
by $5,000. This transaction has already been recorded on the worksheet. Enter
negative numbers with a minus sign (-). The worksheet will automatically total each
column as values are entered in that column. When you are finished, check line 27 to
make sure that total assets equal total liabilities and equity.

2) It has been determined that the cost of the supplies used during the month was
$300. Record the increase or decrease in the appropriate columns on the worksheet.
This is Transaction m. Save the completed file as FTRANS2. Print the worksheet.
Also print your formulas. Check figure: Ending cash balance (cell B22), $4,580

3) How does Transaction m differ from Transactions a through I? In other words, why
is it treated as a special item?

4) Prepare an income statement, a statement of retained earnings, and a balance
sheet in good form for Clarkson Talent Agency for the month of October.


5) Justin initially invested $5,000 in the business. Could he have invested less? How
little could he have invested initially and never have his cash balance go below zero?
To help answer this question, move to column K and analyze the information provided.
Then enter different amounts in cell B9 to help calculate an answer. When you have
determined an answer, print the worksheet again. Explain how you derived your

6) Reset cell B9 to $5,000. Click the Chart tab. A pie chart appears on the screen
indicating the percentage of each asset in relation to total assets. Justin does not
want his cash balance to exceed 60% of his total assets. How much does he have to
withdraw as dividends to reduce his cash percentage to 60%? To find out, enter
different (negative) values in cell B18 of the worksheet and click the Chart tab after
each entry.

When the assignment is complete, reset cell B18 to -500. Click the Answer tab and
print the Answer sheet. Save the file again as FTRANS2.

TICKLERS (optional)

Worksheet. Two additional transactions occurred in October which need to be
recorded on the worksheet.

n. Billed customers for additional services rendered on account, $1,200.
o. Paid creditor for office supplies purchased on account, $120.

Expand the FTRANS2 worksheet to include these transactions. Do not revise column
K. Use the Print Preview command to make sure that the worksheet will print neatly
on one page, then print the worksheet. Save the revised file as FTRANST.

Chart. Using the FTRANS2 file, create a 3-D pie chart that shows the relative
percentages of the liability and equity accounts. Complete the Chart Tickler Data
Table on the Chart worksheet and use it as a basis for preparing the chart. Put your
name somewhere on the chart. Save the file again as FTRANS2. Select the chart
and then print it out.
ur name

CORPORATE TRANSACTIONS                                          FTRANS
Excel Applications for Accounting Principles: Website Problem
by Gaylord N. Smith

Student Name:
Answer sheet for requirements 3, 4, 5, and 6



Dividends required:
     A        B            C               D          E         F          G           H          I        J      K
 1 Student Name:
 2                                            FTRANS
 3                                      Corporate Transactions
 5                       Assets                       =     Liabilities    +             Equity                Running
 6                      Accounts        Office              Accounts             Common        Retained         Cash
 7           Cash      Receivable      Supplies             Payable               Stock        Earnings        Balance
 8   a)       $5,000            $0             $0                     $0            $5,000            $0        $5,000
 9   b)                                                                                                           5,000
10   c)                                                                                                           5,000
11   d)                                                                                                           5,000
12   e)                                                                                                           5,000
13   f)                                                                                                           5,000
14   g)                                                                                                           5,000
15   h)                                                                                                           5,000
16   i)                                                                                                           5,000
17   j)                                                                                                           5,000
18   k)                                                                                                           5,000
19   l)                                                                                                           5,000
20   m)                                                                                                           5,000
21         FORMULA1    FORMULA2       FORMULA3             FORMULA4             FORMULA5     FORMULA6
23                                                              Balance Verification
24                                   Total assets                                                     $0
25                                   Total liabilities and equity                                      0
26                                   Difference                                                       $0
  Percentage Breakdown


      Data Table
Cash       FORMULA1
A/R        FORMULA2
Supplies   FORMULA3
      Chart Tickler
       Data Table

Description: Excel Applications for Accounting Principles document sample