Docstoc

ch03 Instructions for the Microsoft Excel Templates by

Document Sample
ch03 Instructions for the Microsoft Excel Templates by Powered By Docstoc
					Instructions for the Microsoft Excel Templates by Rex A Schildhouse

   Be advised, the template workbooks and worksheets are not protected.
                     Overtyping any data may remove it.
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 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
presentation.

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 text 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, 2012, in the format set in the template.

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
provided or previously entered.

Where a yellow highlighted cell shows "Account Title" enter the appropriate account title for that step of the challenge. This
is a text entry and most of those cells are set for the proper indentation for that step. Frequently the chart of accounts
appropriate to the challenge is provided and you can use the "look to" formula to reference the appropriate account title
without typing it.
Check with your instructor to see if abbreviated account titles are acceptable. For example "A/R" for Accounts Receivable,
"A/P" for Accounts Payable. If your instructor is using a comparison process between workbooks for grading, these
abbreviates may not be acceptable.

Where a yellow highlighted cell shows titles such as "Values," "Amounts," or "Quantities" enter the appropriate numerical
value for that step of the challenge. The cell is formatted for proper presentation of the entered information. If a dollar sign is
appropriate, it should not be entered, Microsoft Excel will place it there through formatting. Commas and significant digits
(decimals) are also set through formatting for common presentation. Since the formatting of the templates is not protected by
any password, you may change any of the formatting found in the templates to meet your desires.

Where a yellow highlighted cell shows titles such as "Formula" you may enter the appropriate formula or enter a numerical
value appropriate for that step of the challenge. Most of the values necessary for the appropriate formula are located on the
template in cells with borders or in other yellow highlighted cells. The formula may be a simple "Look to" formula, an equal
sign and a cell reference, "=E27" or more complex as "=E27*5," or something similar to the time-value-of-money formula.
These are addressed in the tutorial text provided for Microsoft Excel.
Where a yellow highlighted cell shows titles such as "Formula" you may enter the appropriate formula or enter a numerical
value appropriate for that step of the challenge. Most of the values necessary for the appropriate formula are located on the
template in cells with borders or in other yellow highlighted cells. The formula may be a simple "Look to" formula, an equal
sign and a cell reference, "=E27" or more complex as "=E27*5," or something similar to the time-value-of-money formula.
These are addressed in the tutorial text provided for Microsoft Excel.

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 them 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
Name:                                                           Date:
Instructor:                                                     Course:
Intermediate Accounting, 14th Edition by Kieso, Weygandt, and Warfield
Primer on Using Excel in Accounting by Rex A Schildhouse

E3-5 (Adjusting Entries) The ledger of Chopin Rental Agency on March 31 of the current year includes
the following selected accounts before adjusting entries have been prepared.
                                                                Debit        Credit
                  Prepaid Insurance                              $3,600
                  Supplies                                        2,800
                  Equipment                                      25,000
                  Accumulated Depreciation - Equipment                        $8,400
                  Notes Payable                                               20,000
                  Unearned Rent Revenue                                         6,300
                  Rent Revenue                                                60,000
                  Interest Expense                                     0
                  Salaries and Wage Expense                      14,000
                    Totals:                                     $45,400      $94,700

An analysis of the accounts shows the following:
1. The equipment depreciation per month is:                                      $250
2. One-third of the unearned rent was earned during the quarter.
3. Interest accrued for the month on notes payable is:                           $500
4. Supplies on hand total:                                                       $650
5. The monthly insurance expiration rate is:                                     $300

Instructions:
Prepare the adjusting entries at March 31, assuming that adjusting entries are made quarterly. Additional accounts
are: (Omit explanations.)
                 Depreciation Expense                      Interest Payable
                 Insurance Expense                         Supplies Expense

       1         Account Title                                                 Amount
                   Account Title                                                                 Amount
                 Text / Memo Line - Optional

       2         Account Title                                                 Amount
                   Account Title                                                                 Amount
                 Text / Memo Line - Optional

       3         Account Title                                                 Amount
                   Account Title                                                                 Amount
                 Text / Memo Line - Optional

       4         Account Title                                                 Amount
                   Account Title                                                                 Amount
                 Text / Memo Line - Optional

       5         Account Title                                                 Amount
                   Account Title                                                                 Amount
                 Text / Memo Line - Optional




               3ce2004e-e2d4-4524-aea9-bbfd1d03e4f1.xlsx, E3-5, Page 3 of 10, 5/5/2012, 6:17 PM
Name:                                             Date:
Instructor:                                       Course:
                           th
Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield
Primer on Using Excel in Accounting by Rex A Schildhouse

E3-8 (Adjusting Entries) William Bryant is the new owner of Ace Computer Services. At the end of
August 2012, his first month of ownership, Bryant is trying to prepare monthly financial statements. Below
is some information related to unrecorded expenses that the business incurred during August.
1. At August 31, Bryant owed his employees salaries and wages that will be paid on September 1 in the
     $2,900
2. At the end of the month he had not yet received the month's utility bill. Based on past experience, he
estimated the bill would be approximately:        $600
3. On August 1, Bryant borrowed                  $60,000    from a local bank on a                 15
-year mortgage. The annual interest rate is         8%
4. A telephone bill in the amount of              $117      covering August charges is unpaid at
August 31.

Instructions:
Prepare the adjusting journal entries as of August 31, 2012.

                 Account Title                                                    Amount
       1          Account Title                                                                     Amount
                 Text / Memo line

                 Account Title                                                    Amount
       2          Account Title                                                                     Amount
                 Text / Memo line

                 Account Title                                                    Amount
       3          Account Title                                                                     Amount
                 Text / Memo line

                 Account Title                                                    Amount
       4          Account Title                                                                     Amount
                 Text / Memo line




           3ce2004e-e2d4-4524-aea9-bbfd1d03e4f1.xlsx, E3-8, Page 4 of 10, 5/5/2012, 6:17 PM
Name:                                                            Date:
Instructor:                                                      Course:
Intermediate Accounting, 14th Edition by Kieso, Weygandt, and Warfield
Primer on Using Excel in Accounting by Rex A Schildhouse

P3-2 (Adjusting Entries and Financial Statements) Mason Advertising Agency was founded in January
2008. Presented below are adjusted and unadjusted trial balances as of December 31, 2012.

                                 MASON ADVERTISING AGENCY
                                        Trial Balance
                                     December 31, 2012
                                                Unadjusted                          Adjusted
                                            Debit        Credit               Debit         Credit
Cash                                         $11,000                          $11,000
Accounts Receivable                           20,000                           23,500
Supplies                                       8,400                            3,000
Prepaid Insurance                              3,350                            2,500
Equipment                                     60,000                           60,000
Accumulated Depreciation - Equipment                     $28,000                              $33,000
Accounts Payable                                            5,000                               5,000
Interest Payable                                                0                                  150
Notes Payable                                               5,000                               5,000
Unearned Service Revenue                                    7,000                               5,600
Salaries and Wages Payable                                      0                               1,300
Common Stock                                               10,000                              10,000
Retained Earnings                                           3,500                               3,500
Service Revenue                                            58,600                              63,500
Salaries and Wages Expense                    10,000                           11,300
Insurance Expense                                                                 850
Interest Expense                                 350                              500
Depreciation Expense                                                            5,000
Supplies Expense                                                                5,400
Rent Expense                                   4,000                            4,000
Totals:                                    $117,100     $117,100             $127,050        $127,050

Instructions:
(a) Journalize the annual adjusting entries that were made. (Omit explanations.)
                  Account Title                                                  Amount
     Dec 31
                    Account Title                                                              Amount

                 Account Title                                                 Amount
    Dec 31
                  Account Title                                                                Amount

                 Account Title                                                 Amount
    Dec 31
                  Account Title                                                                Amount

                 Account Title                                                 Amount
    Dec 31
                  Account Title                                                                Amount

                 Account Title                                                 Amount
    Dec 31
                  Account Title                                                                Amount



        3ce2004e-e2d4-4524-aea9-bbfd1d03e4f1.xlsx, Problem 3-2, Page 5 of 10, 5/5/2012, 6:17 PM
Name:                                                          Date:
Instructor:                                                    Course:
                           th
Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield
                Account Title                                                Amount
    Dec 31
                 Account Title                                                                Amount

                Account Title                                                Amount
    Dec 31
                 Account Title                                                                Amount

(b) Prepare an income statement and a statement of retained earnings for the year ending December 31, 2012, and
an unclassified balance sheet at December 31.

                                          MASON ADVERTISING AGENCY
                                                 Income Statement
                                       For the Year Ended December 31, 2012
                Revenues
                Account Title                                                                 Amount
                Expenses
                Account Title                                                Amount
                Account Title                                                Amount
                Account Title                                                Amount
                Account Title                                                Amount
                Account Title                                                Amount
                Account Title                                                Amount
                 Total expenses                                                               Formula
                Net income                                                                    Formula

                                         MASON ADVERTISING AGENCY
                                          Statement of Retained Earnings
                                      For the Year Ended December 31, 2012
                Retained Earnings, Jan 1                                                      Amount
                Add:         Title                                                            Amount
                Retained Earnings, Dec 31                                                     Formula

                                          MASON ADVERTISING AGENCY
                                                Balance Sheet
                                              December 31, 2012
                                                   Assets
                Account Title                                                                 Amount
                Account Title                                                                 Amount
                Account Title                                                                 Amount
                Account Title                                                                 Amount
                Account Title                                                Amount
                Account Title                                                Amount           Formula
                 Total Assets                                                                 Formula




        3ce2004e-e2d4-4524-aea9-bbfd1d03e4f1.xlsx, Problem 3-2, Page 6 of 10, 5/5/2012, 6:17 PM
Name:                                                            Date:
Instructor:                                                      Course:
                           th
Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield
                                  Liabilities and Stockholders' Equity
                 Liabilities
                 Account Title                                                      Amount
                 Account Title                                                      Amount
                 Account Title                                                      Amount
                 Account Title                                                      Amount
                 Account Title                                                      Amount
                   Total Liabilities                                                                 Formula

                 Stockholders' Equity
                 Account Title                                                      Amount
                 Account Title                                                      Amount           Formula
                   Total liabilities and stockholders' equity                                        Formula

(c) Answer the following questions:
  (1) If the note has been outstanding                3         months, what is the annual interest rate on that note?

Enter text answer here.




 (2) If the company paid      $12,500    in salaries and wages in 2012, what was the balance in Salaries and
     Wages Payable on December 31, 2011?

Enter text answer here.




        3ce2004e-e2d4-4524-aea9-bbfd1d03e4f1.xlsx, Problem 3-2, Page 7 of 10, 5/5/2012, 6:17 PM
d in January




               3ce2004e-e2d4-4524-aea9-bbfd1d03e4f1.xlsx, Problem 3-2, Page 8 of 10, 5/5/2012, 6:17 PM
cember 31, 2012, and




                       3ce2004e-e2d4-4524-aea9-bbfd1d03e4f1.xlsx, Problem 3-2, Page 9 of 10, 5/5/2012, 6:17 PM
st rate on that note?




lance in Salaries and




                        3ce2004e-e2d4-4524-aea9-bbfd1d03e4f1.xlsx, Problem 3-2, Page 10 of 10, 5/5/2012, 6:17 PM

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:97
posted:5/6/2012
language:English
pages:10