CORPORATE TRANSACTIONS
FTRANS
Excel Applications for Accounting Principles: Website Problem
by Gaylord N. Smith
LEARNING OBJECTIVES
* 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.
PROBLEM DATA
On June 1 of the current year, Wilson Wood opened Woody's Web Services, Inc.
The corporation had the following transactions during June.
a. Opened a business checking account and made a deposit, $4,000. In
exchange, Wilson was issued 200 shares of $20 par value stock.
b. Paid rent for June for office space and computer equipment, $2,000.
c. Purchased office supplies (stamps, pens, etc.) on account, $680.
d. Received cash for services rendered, $3,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, $4,400.
h. Paid the monthly internet service bill of $180.
i. Paid the secretary's salary of $2,400.
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 refund. REQUIRED
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 $8,000 and also
increases the common stock account by $8,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), $2,020
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 Woody's Web Services, Inc for the month of June.
WHAT-IF ANALYSIS
5) Wilson initially invested $8,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 answer. CHART ANALYSIS
6) Reset cell B8 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. Wilson
does not want his cash balance to exceed 40% of his total assets. How much
does he have to withdraw as dividends to reduce his cash percentage to 40%?
To find out, enter different (negative) values in cell B18 of the worksheet and click CHART ANALYSIS
6) Reset cell B8 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. Wilson
does not want his cash balance to exceed 40% of his total assets. How much
does he have to withdraw as dividends to reduce his cash percentage to 40%?
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 June 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. Preview the printout 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. CORPORATE TRANSACTIONS
Excel Applications for Accounting Principles: Website Problem
by Gaylord N. Smith FTRANS Student Name:
Michelle Tomlin
Answer sheet for requirements 3, 4, 5, and 6
3)
This is an adjusting entry. Such entries are needed because simply recording
external transactions does not always tell the whole story. The internal consumption
supplies affects the financial status of the firm just as an external transaction does
4) 5)
Any amount that is higher than 2270 because the expenses have not require more than 2270 5) 6)
Dividends required: more than 2270 A
1
2 B C Student Name: D 5
6 9
10
11
12
13
14
15
16
17
18
19
20
21 F G H I J a)
b)
c)
d)
e)
f)
g)
h)
i)
j)
k)
l)
m) Cash
$4,000
(2,000) Assets
Accounts
Receivable
$0 3,500
(500)
(190) Office
Supplies
$0 = Liabilities
Accounts
Payable
$0 680 680 + Equity
Common
Retained
Stock
Earnings
$4,000
$0
(2,000)
3,500 (500)
190
4,400 (180)
(2,400)
(500)
1,500
150
(300)
$3,080 4,400
(180)
(2,400)
(500) (1,500) $2,900 (150)
300
$1,020 $180 $4,000 $2,820 22
23
24
25
26
27 K FTRANS
Corporate Transactions 3
4 7
8 E Michelle Tomlin Balance Verification
Total assets
Total liabilities and equity
Difference $7,000
7,000
$0 Running
Cash
Balance
$4,000
2,000
2,000
5,500
5,000
4,810
4,810
4,630
2,230
1,730
3,230
3,380
3,080 ASSETS
Percentage Breakdown
Supplies
15%
Cash
44%
A/R
41% Chart
Data Table
Cash
A/R
Supplies 3080
2900
1020 Chart Tickler
Data Table
A/P
C/S
R/E

 

SuperTutor

(15)

Status NEW Posted 01 Jun 2017 04:06 AM My Price 20.00

-----------

Attachments

file 1496291080-Solutions file.docx preview (51 words )

S-----------olu-----------tio-----------ns -----------fil-----------e -----------Hel-----------lo -----------Sir-----------/Ma-----------dam----------- T-----------han-----------k y-----------ou -----------for----------- yo-----------ur -----------int-----------ere-----------st -----------and----------- bu-----------yin-----------g m-----------y p-----------ost-----------ed -----------sol-----------uti-----------on.----------- Pl-----------eas-----------e p-----------ing----------- me----------- on----------- ch-----------at -----------I a-----------m o-----------nli-----------ne -----------or -----------inb-----------ox -----------me -----------a m-----------ess-----------age----------- I -----------wil-----------l b-----------e q-----------uic-----------kly----------- on-----------lin-----------e a-----------nd -----------giv-----------e y-----------ou -----------exa-----------ct -----------fil-----------e a-----------nd -----------the----------- sa-----------me -----------fil-----------e i-----------s a-----------lso----------- se-----------nt -----------to -----------you-----------r e-----------mai-----------l t-----------hat----------- is----------- re-----------gis-----------ter-----------ed -----------onÂ----------- th-----------is -----------web-----------sit-----------e -----------Tha-----------nk -----------you----------- -----------

Not Rated(0)

• Buy Answer