ch04

Instructions for the Microsoft Excel Templates b y Rex A Schildhouse

B e a d v i s e d , t h e t em e m p l a te t e w o r k b o o k s a n d w o r k s h e e t s a r e n o t p r o t e c t ed ed . O v e r ty ty p i n g a n y d a t a m a y r e m o v e i t . Extensive detail and information is contained within the help function of Microsoft Excel and in the provided text. You should enter your name, date, instructor's name, and course into the cells at the top of the page. This information will be printed on the top of each page if the template requires more more than one page.

Each template is set to print with File Name, Page # of # Page(s), the print date, and the print time to assist in assembly of multiple pages. If more than one page is required by the template, manual page breaks have been set to provide consistent prese All of the cells have been correctly formatted for presentation and should not require any adjustment. For example, if the text requires one, two, or three significant digits in a presentation, the template has been set for that presentation in the appropriate cells. In general, the yellow highlighted cells are the cells which work and effort should be presented. These entries may include date(s), account title(s), values, memorandum appropriate to the entry, or t ext answers to questions. And information or data which may be required by the solution will be entered in cells with borders to help identify them. Where a yellow highlighted cell shows "Date" enter the appropriate date for that step of the challenge. This may be any date format that Microsoft Excel accepts. Some of these formats include "1/1/12", "01/01/ 12", and "01/01/2012. " All of these will return January 01, 2010, in the format set in the t emplate. Where a yellow highlighted cell shows "Acct Nbr" enter the appropriate account number, provided in the template and in the text for that step of the challenge. This is entry may be a "Look to" formula to another cell where that information has been

Where a yellow highlighted cell shows "Text" enter the appropriate text for that step of the challenge. This may be a memorandum entry for a journal entry or a lengthy text answer discussing the results of an analysis of a company's financials. These titles can simply be typed over. Where a yellow highlighted cell shows titles such as "Journal Number" or "Journ #" you should enter the appropriate number provided in the template and in the text for that step of the challenge. In general this will appear in instances such as "Record the following events in General Journal number six." The print area is defined to fit onto 8 1/2" × 11" sheets in portrait or landscape mode as required. Margins are generally set to no less than 1/2" so most printers can print them without a problem. If you printer cannot accept margins less than 1" you may have to reformat the margins through Page Setup. The display may have "Freeze Pane" invoked so column titles remain visible during data entry. This can be removed by utilizing the View menu and selecting "Unfreeze Panes" under "Freeze Panes." When negative values are required, enter t hem by starting with a minus sign, "-". Negative values may be shown as ($400) or -$400. Negative values in formulas can be created by putting a minus sign in front of the cell reference - "=E10*-E11" will return a negative value if both cells E10 and E11 contain positive values. Microsoft Office and Microsoft Excel are products of, and copyrighted by, Microsoft Corporation, One Microsoft Way, Redmond, Washington 98052-6399

tation.

Name:

Date:

Instructor:

Course:

Accounting Principles , Tenth Edition by Weygandt, Kieso, and Kimmel Primer on Using Excel in Accounting by Rex A Schildhouse E4-2, Complete the work sheet.

The adjusted trial balance columns of the worksheet for Pisa Company are as follows. PISA COMPANY

Work Sheet (Partial) For the Month Ended April 30, 2012 Account titles

Cash Accounts Receivable Prepaid Rent Equipment Accum. Depreciation-Equipment Notes Payable Accounts Payable Owner's Capital Owner's Drawings Service Revenue Salaries and Wages Expense Rent Expense Depreciation Expense Interest Expense Interest Payable Totals Net income Totals

Adjusted T/B Dr. Cr.

Income Statement Dr. Cr.

Balance Sheet Dr. Cr.

10,000 7,840 2,280 23,050 4,921 5,700 4,920 27,960 3,650 15,590 10,840 760 671 57 59,148

57 59,148

Formula Formula Formula




Name:

Date:

Instructor:

Course: PISA COMPANY Owner’s Equity Statement

For the Month Ended April 30, 2012 Title Title

Formula Amount Formula Amount Formula

Title Title PISA COMPANY

Balance Sheet April 30, 2012 Assets

Current assets Account title Account title Account title Total current assets Property, plant, and equipment Account title Less: Title Total assets

Amount Amount Amount Amount Amount Amount

Formula Formula

Liabilities and Owner’s Equity

Current liabilities Account title Account title Account title Total current liabilities

Amount Amount Amount Formula

Name:

Date:

Instructor:

Course:

E4-4 Instru ction s: (b) Post the closing entries to Income Summary and Owner’s Capital. Use T

accounts. Account title Amount Amount Formula

Amount

Owner's Capital Amount Bal.

Formula

Bal.

E4-4 Instru ction s: (c) Prepare a post-closing trial balance at April 30.

PISA COMPANY

Post-Closing Trial Balance April 30, 2012 Debit

Credit

Account title Account title Account title Account title Account title Account title Account title Account title Account title Formula

Formula

Amount Amount Formula

Name: Solution

Date:

Instructor:

Course:

Accounting Principles , Tenth Edition by Weygandt, Kieso, and Kimmel Primer on Using Excel in Accounting by Rex A Schildhouse E4-2, Complete the work sheet.

The adjusted trial balance columns of the worksheet for Pisa Company are as follows. PISA COMPANY

Work Sheet (Partial) For the Month Ended April 30, 2012 Account titles

Cash Accounts Receivable Prepaid Rent Equipment Accum. Depreciation-Equipment Notes Payable Accounts Payable Owner's Capital Owner's Drawings Service Revenue Salaries and Wages Expense Rent Expense Depreciation Expense Interest Expense Interest Payable Totals Net income Totals

Adjusted T/B Dr. Cr.


10,000 7,840 2,280 23,050


10,000 7,840 2,280 23,050 4,921 5,700 4,920 27,960

4,921 5,700 4,920 27,960

3,650

3,650 15,590

10,840 760 671 57 59,148

15,590 10,840 760 671 57

57 59,148

12,328 3,262

15,590

46,820

57 43,558 3,262

15,590

15,590

46,820

46,820

Name: Solution

Date:

Instructor:

Course: PISA COMPANY Owner’s Equity Statement

For the Month Ended April 30, 2012 Owner's Capital, April 1 $27,960 Add: Net income 3,262 31,222 Less: Drawings 3,650 Owner's Capital, April 30 $27,572 PISA COMPANY

Balance Sheet April 30, 2012 Assets

Current assets Cash Accounts receivable Prepaid rent Total current assets Property, plant, and equipment Equipment Less: Accum deprec-Equip Total assets

$10,000 7,840 2,280 20,120 23,050 4,921

18,129 $38,249


Current liabilities Notes payable Accounts payable Interest payable Total current liabilities

$5,700 4,920 57 $10,677

Name: Solution

Date:

Instructor:

Course:

E4-4 Instru ction s: (b) Post the closing entries to Income Summary and Owner’s Capital. Use T

accounts.

(2) (3)

Income Summary 12,328 (1) 3,262 15,590

15,590

(4)

15,590

Owner's Capital 3,650 Bal. (3) Bal.

E4-4 Instru ction s: (c) Prepare a post-closing trial balance at April 30.

PISA COMPANY

Post-Closing Trial Balance April 30, 2012 Debit Cash $10,000 Accounts Receivable 7,840 Prepaid Rent 2,280 Equipment 23,050 Accum. Depreciation-Equipment Notes Payable Accounts Payable Interest Payable Owner's Capital $43,170

Credit

$4,921 5,700 4,920 57 27,572 $43,170

27,960 3,262 27,572

Name: Instructor:

Date: Course:

Accounting Principles , Tenth Edition by Weygandt, Kieso, and Kimmel Primer on Using Excel in Accounting by Rex A Schildhouse P4-1A, Prepare worksheet, fi nancial statements, and adjusting and closing entr ies.

Omer Asik began operations as a private investigator on January 1, 2012. The trial balance columns of the worksheet for Omer Asik, P.I. at March 31 are as follows. OMER ASIK, P.I.

Worksheet For the Quarter Ended March 31, 2012 Account titles Cash Accounts Receivable Supplies Prepaid Insurance Equipment Notes Payable Accounts Payable Owner's Capital Owner's Drawings Service Revenue Salaries and Wages Expense Travel Expense Rent Expense Miscellaneous Expense

Trial Balance Dr. Cr.

Adjustments Dr.

Cr.

Adjusted Trial Balance Dr. Cr.


11,400 5,620 1,050 2,400 30,000 10,000 12,350 20,000 600 13,620 2,200 1,300 1,200 200 55,970

55,970

Supplies Expense Depreciation Expense Accumulated Depreciation - Equipment Interest Expense Interest Payable Insurance Expense Totals Net Income Totals Key: (a) Supplies Used; (b) Depreciation Expense; (c) Accrued Interest on note; (d) Insurance Expired; (e) Service Revenue Earned but unbilled. Other data: 1. Supplies on hand total $480 2. Depreciation per quarter is $800 3. Interest accrued on 6-month note payable, issued January 1, 4. Insurance expires at the rate of $200 per month. 5. Services provided but unbilled at March 31 total $1,030

$300

Instructions:

(a) Complete the worksheet, above.

176263949.xlsx.ms_office, Problem P4-1A, Page 10 of 19 Page(s), 9/30/20137:30 AM


Name: Instructor:

Date: Course:

(b) Prepare an income statement and owner’s equity statement for the quarter and a classified balance sheet at March 31. O. Asik did not make any additional investments in the business

during the quarter ended March 31, 2012. OMER ASIK, P.I.

OMER ASIK, P.I.

Income Statement For the Quarter Ended March 31, 2012

Balance Sheet March 31, 2012 Assets

Revenues Account title Expenses Account title Account title Account title Account title Account title Account title Account title Account title Total expenses Net income

Amount Amount Amount Amount Amount Amount Amount Amount Amount

Current assets Account title Account title Account title Account title Total current assets Property, plant, and equipment Account title Less: Account title Total assets

Formula Formula

For the Quarter Ended March 31, 2012 Owner's Capital, Jan 1 Add: Title Amount Title Amount Title Owner's Capital, March 31

Amount

Amount Amount

Formula Formula


Owner’s equity

Account title Formula Formula Amount Formula

Formula



OMER ASIK, P.I. Owner’s Equity Statement

Amount Amount Amount Amount

Total liabilities and owner’s equity


Amount #VALUE!

Name: Instructor:

Date: Course:

(c) Journalize the adjusting entries from the adjustments columns of the worksheet.

Mar. 31

31

31

31

31

Account title Account title

Amount


Amount


Amount


Amount


Amount

(d) Journalize the closing entries from the financial statement columns of the

worksheet. Mar. 31 Amount 31 Amount

Amount

Amount

Amount

31

31


Amount

Account title Account title Account title Account title Account title Account title Account title Account title Account title

Amount


Amount


Amount


Amount

Amount Amount Amount Amount Amount Amount Amount Amount

Amount

Amount

Name: Solution Instructor:

Date: Course:

Accounting Principles , Tenth Edition by Weygandt, Kieso, and Kimmel Primer on Using Excel in Accounting by Rex A Schildhouse P4-1A, Prepare worksheet, fi nancial statements, and adju stin g and closing entr ies.

Omer Asik began operations as a private investigator on January 1, 2012. The trial balance columns of the worksheet for Omer Asik, P.I. at March 31 are as follows. OMER ASIK, P.I.

Worksheet For the Quarter Ended March 31, 2012 Account titles Cash Accounts Receivable Supplies Prepaid Insurance Equipment Notes Payable Accounts Payable Owner's Capital Owner's Drawings Service Revenue Salaries and Wages Expense Travel Expense Rent Expense Miscellaneous Expense Supplies Expense Depreciation Expense Accumulated Depreciation - Equipment Interest Expense Interest Payable Insurance Expense Totals Net Income Totals


Adjustments Dr.

11,400 5,620 1,050 2,400 30,000

(e)

Cr.

1,030 (a) (d)

570 600


11,400 6,650 480 1,800 30,000

10,000 12,350 20,000


11,400 6,650 480 1,800 30,000 10,000 12,350 20,000

600

10,000 12,350 20,000

600 13,620

2,200 1,300 1,200 200 55,970


(e)

1,030

600 14,650

14,650

2,200 1,300 1,200 200

2,200 1,300 1,200 200

570 800

570 800

55,970 (a) (b)

570 800

(c)

300

(d)

600 3,300

(b)

800

800 300

(c)

300 3,300

800 300

300 600 58,100

58,100

300 600 7,170 7,480 14,650

Key: (a) Supplies Used; (b) Depreciation Expense; (c) Accrued Interest on note; (d) Insurance Expired; (e) Service Revenue Earned but unbilled. Other data: 1. Supplies on hand total $480 2. Depreciation per quarter is $800 3. Interest accrued on 6-month note payable, issued January 1, 4. Insurance expires at the rate of $200 per month. 5. Services provided but unbilled at March 31 total $1,030

$300

Instructions:


176263949.xlsx.ms_office, Solution P4-1A, Page 13 of 19 Page(s), 9/30/20137:30 AM

14,650

50,930

14,650

50,930

43,450 7,480 50,930


Date: Course:

(b) Prepare an income statement and owner’s equity statement for the quarter and a classified balance sheet at March 31. O . Asik did not make any additional investments in the business

during the quarter ended March 31, 2012. OMER ASIK, P.I.

OMER ASIK, P.I.

Income Statement For the Quarter Ended March 31, 2012


Revenues Service Revenue Expenses Salaries and Wages Expense Travel Expense Rent Expense Depreciation Expense Supplies Expense Insurance Expense Interest Expense Miscellaneous Expense Total expenses Net income

$14,650 $2,200 1,300 1,200 800 570 600 300 200 7,170 $7,480

For the Quarter Ended March 31, 2012 Owner's Capital, Jan 1 Add: Investment by owner $20,000 Add: Net income 7,480

$0

$20,330 30,000 800

29,200 $49,530

$10,000 12,350 300 $22,650

Owner’s equity

Owner's Capital 27,480 27,480 600 $26,880

$11,400 6,650 480 1,800


Current liabilities Notes Payable Accounts Payable Interest Payable Total current liabilities

OMER ASIK, P.I. Owner’s Equity Statement

Less: Drawings Owner's Capital, March 31

Current assets Cash Accounts Receivable Supplies Prepaid Insurance Total current assets Property, plant, and equipment Equipment Less: Accumulated Depreciation - Equipment Total assets

Total liabilities and owner’s equity


26,880 $49,530


Date: Course:


Mar. 31

31

31

31

31

Supplies Expense Supplies

570

Depreciation Expense Accumulated Depreciation - Equipment

800

Interest Expense Interest Payable

300

Insurance Expense Prepaid Insurance

600

Accounts Receivable Service Revenue

1,030


worksheet. Mar. 31 570 31 800

300

600

1,030

31

31

Service Revenue Income Summary

14,650 14,650

Income Summary Travel Expense Salaries and Wages Expense Rent Expense Insurance Expense Depreciation Expense Supplies Expense Interest Expense Miscellaneous Expense

7,170

Income Summary Owner's Capital

7,480

Owner's Capital Owner's Drawings


1,300 2,200 1,200 600 800 570 300 200

7,480 600 600

Name: Instructor: Accounting Principles , Tenth Edition by Weygandt, Kieso, and Kimmel Primer on Using Excel in Accounting by Rex A Schildhouse

Date: Course:

P4-1B, Prepare a work sheet, fin ancial statements, and adjusting and closing entri es.

The trial balance columns of the worksheet for Gibson Roofing at March 31, 2012, are as follows. GIBSON ROOFING

Worksheet For the Month Ended March 31, 2012 Account titles Cash Accounts Receivable Supplies Equipment Accum Deprec - Equipment Accounts Payable Unearned Service Revenue Owner's Capital Owner's Drawings Service Revenue Salaries and Wages Expense Miscellaneous Expense Totals Supplies Expense Depreciation Expense Salaries and Wages Payable Totals Net Income Totals


Adjustments Dr.

Cr.



4,500 3,200 2,000 11,000 1,250 2,500 550 12,900 1,100 6,300 1,300 400 23,500

23,500

Key:(a) Supplies Used; (b) Depreciation Expensed; (c) Service Revenue Earned; (d) Salaries Accrued. Other data: 1. A physical count reveals only $550 2. Depreciation for March is $250 3. Unearned service revenue amounted to 4. Accrued salaries are $700

of roofing supplies on hand. $210

after adjustment on March 31.

Instructions:


176263949.xlsx.ms_office, Problem P4-1B, Page 16 of 19 Page(s), 9/30/20137:30 AM


Name: Instructor:

Date: Course:

(b) Prepare an income statement and owner’s equity statement for the month of March and a classified balance sheet at March 31. T. Gibson did not make any additional

investments in the business in March. GIBSON ROOFING

GIBSON ROOFING

Income Statement For the Month Ended March 31, 2012


Revenues Account title Expenses Account title Account title Account title Account title Total expenses Net income

Amount Amount Amount Amount Amount Formula Formula

GIBSON ROOFING Owner’s Equity Statement

For the Month Ended March 31, 2012 Owner's Capital, March 1 Title Title Owner's Capital, March 31

Current assets Account title Account title Account title Total current assets Property, plant, and equipment Account title Account title Total assets

Amount Amount Formula Amount Formula


31

Account title Account title Account title Account title

Amount Amount

Formula Formula



Amount Amount Amount

Account title

worksheet. Mar. 31

31


Amount Amount

31


Amount Amount

Formula Amount Formula


Amount

31

Amount Amount Amount

Owner’s equity Total liabilities and owner’s equity

Mar. 31


31

31


Amount

Account title Account title Account title Account title Account title

Formula


Amount


Amount

176263949.xlsx.ms_office, Problem P4-1B, Page 17 of 19 Page(s), 9/30/20137:30 AM

Amount

Amount Amount Amount Amount

Amount

Amount

Name: Solution Instructor: Accounting Principles , Tenth Edition by Weygandt, Kieso, and Kimmel Primer on Using Excel in Accounting by Rex A Schildhouse

Date: Course:

P4-1B, Prepare a work sheet, fin ancial statements, and adjusting and closing entri es.

The trial balance columns of the worksheet for Gibson Roofing at March 31, 2012, are as follows. GIBSON ROOFING

Worksheet For the Month Ended March 31, 2012 Account titles Cash Accounts Receivable Supplies Equipment Accumulated Depreciation - Equipment Accounts Payable Unearned Service Revenue Owner's Capital Owner's Drawings Service Revenue Salaries and Wages Expense Miscellaneous Expense Totals Supplies Expense Depreciation Expense Salaries and Wages Payable Totals Net Income Totals


Adjustments Dr.

4,500 3,200 2,000 11,000 1,250 2,500 550 12,900

(c)

Cr.

(a)

1,450

(b)

250

(c)

340


1,500 2,500 210 12,900

1,500 2,500 210 12,900 1,100

6,640

6,640

(d)

700

2,000 400

2,000 400

(a) (b)

1,450 250

1,450 250

1,450 250

23,500

(d) 2,740

700 2,740

24,450

700 24,450

4,100 2,540 6,640

Key:(a) Supplies Used; (b) Depreciation Expensed; (c) Service Revenue Earned; (d) Salaries Accrued. Other data: 1. A physical count reveals only $550 2. Depreciation for March is $250 3. Unearned service revenue amounted to 4. Accrued salaries are $700


4,500 3,200 550 11,000

1,100 6,300

1,300 400 23,500

4,500 3,200 550 11,000

340

1,100


of roofing supplies on hand. $210

after adjustment on March 31.

Instructions:


176263949.xlsx.ms_office, Solution P4-1B, Page 18 of 19 Page(s), 9/30/20137:30 AM

6,640

20,350

6,640

20,350

700 17,810 2,540 20,350


Date: Course:

(b) Prepare an income statement and owner’s equity statement for the month of March and a classified balance sheet at March 31. T. Gibson did not make any additional investments in

the business in March. GIBSON ROOFING

GIBSON ROOFING

Income Statement For the Month Ended March 31, 2012

Balance Sheet March 31, 2012 Asset

Revenues Service Revenue Expenses Salaries and Wages Expense Supplies Expense Miscellaneous Expense Depreciation Expense Total expenses Net income

Current assets Cash Accounts Receivable Supplies Total current assets Property, plant, and equipment Equipment Less: Accum. depreciation-Euip. Total assets

$6,640 $2,000 1,450 400 250 4,100 $2,540

Less: Drawings Owner's Capital, March 31

$12,900 2,540 15,440 1,100 $14,340

31

31

31

Supplies Expense Supplies

worksheet. Mar. 31

1,450 1,450

Depreciation Expense Accumulated Depreciation - Equipment

250

31 250

Unearned Service Revenue Service Revenue

340

Salaries and Wages Expense Salaries and Wages Payable

700

11,000 1,500

9,500 $17,750

$3,410 14,340 $17,750



Mar. 31

$8,250

Liabilities and Owner's Equity Current Liabilities Accounts Payable $2,500 Salaries and Wages Payable 700 Unearned Service Revenue 210 Total current liabilities Owner's equity Owner's capital Total liabilities and owner's equity

GIBSON ROOFING Owner’s Equity Statement

For the Month Ended March 31, 2012 Owner's Capital, March 1 Add: Net income

$4,500 3,200 550

340 31 700 31

Service Revenue Income Summary

6,640

Income Summary Salaries and Wages Expense Supplies Expense Depreciation Expense Miscellaneous Expense

4,100

Income Summary Owner's Capital

2,540

Owner's Capital Owner's Drawings

1,100

176263949.xlsx.ms_office, Solution P4-1B, Page 19 of 19 Page(s), 9/30/20137:30 AM

6,640

2,000 1,450 250 400

2,540

1,100

ch04

Recommend Documents