Question

1. Refer to Problem 2-41B (below). Use an Excel spreadsheet to construct the required financial statements. To complete Requirement b, use formulas where normal arithmetic calculations are made within the financial statements (in particular the statement of changes in stockholders’ equity).

The following accounts and balances were drawn from the records of Shearer Company at December 31, Year 2:

Cash	$22,100	Accounts receivable	$21,000
Land	43,000	Cash flow from operating act.	8,600
Insurance expense	2,500	Beginning retained earnings	47,200
Dividends	5,000	Beginning common stock	5,500
Prepaid insurance	3,500	Service revenue	86,000
Accounts payable	15,000	Cash flow from financing act.	9,000
Supplies	2,100	Ending common stock	14,500
Supplies expense	1,000	Cash flow from investing act.	(6,000)
Rent expense	3,500	Other operating expenses	59,000

Required

Use the accounts and balances from Shearer Company to construct an income statement, statement of changes in stockholders’ equity, balance sheet, and statement of cash flows (show only totals for each activity on the statement of cash flows).

2. It is interesting to speculate about what would happen if certain operating results change for better or worse. After completing Requirement a, change certain account balances for each of the following independent operating adjustments. After each adjustment, notice how the financial statements would differ if the change in operations were to occur. After noting the effect of each adjustment, return the data to the original amounts in Problem 2-41A and then go to the next operating adjustment. At the end, return all numbers to the original balances noted above.

Input the following table into the bottom of your excel worksheet, calculate the new amounts on the financial statements for the various operating changes listed:

Original	1	2	3	4	5
Net income
Total assets
Total liabilities
Total stockholders’ equity
Total liabilities & stockholders’ equity

Independent Operating Adjustments

1. Revenue and the related Accounts Receivable increased $10,000.
2. Revenue and the related Accounts Receivable decreased $10,000.
3. Operating Expenses and the related Accounts Payable decreased $400.
4. Operating Expenses and the related Accounts Payable increased $400.
5. Dividends paid decreased $500 and cash changed accordingly.

Answer 1

Income Statement for the period Dec.31, year 2
Amont in $
serive revenue	86000
total revenue		86000
less:opertaing expense
rent expense	3500
insurance expense	2500
supplies expense	1000
other operating expense	59000
total operating expense		66000
net income		20000

Statement of changes in stockholders equity
for the period Dec.31, year (amount in $)
starting common stock	5500
add:additional capital	9000
ending common stock		14500
beginning retained earnings	47200
add:net income	20000
less:dividend	5000
ending retained earnings		62200
total stockholders equity		76700

Balance Sheet as of Dec.31, year 2
Amount in $
Assets
current assets:
cash	22100
accounts receivable	21000
prepaid insuance	3500
supplies	2100
total current assets		48700
Non-current assets
land	43000
total non-current assets		43000
total assets		91700
Liabilities
current liabilities
accounts payable	15000
total current liabilities		15000
non-current liabilities
long-term liabilities	0
total long-term liabilities		0
Stockholders equity
ending common stock	14500
ending retained earnings	62200
total stockholders equity		76700
total liabilities and stockholders equity		91700

Statement of cash flow
for the period Dec.31, year 2 (amount in $)
cash flow from operating activities	8600
cash flow from investing activities	-6000
cash flow from financing activities	9000
net change in cash	11600