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

Document Sample
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 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, 2010, 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 "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
sistent presentation.
           Name:                                                Date:
       Instructor:                                            Course:
Accounting Principles, Tenth Edition by Weygandt, Kieso, and Kimmel
Primer on Using Excel in Accounting by Rex A Schildhouse
E5-10, Prepare multiple-step and single-step income statements.
In its income statement for the year ended December 31, 2012, Fox Company reported the following
condensed data.
Operating expenses                     $725,000                 Interest revenue                28,000
Cost of goods sold                    1,289,000                 Loss on sale of plant assets    17,000
Interest expense                          70,000                Net sales                    2,200,000

Instructions:
(a) Prepare a multiple-step income statement.

                                          FOX COMPANY
                                         Income Statement
                               For the Year Ended December 31, 2012
           Title                                                                  Amount
           Title                                                                  Amount
           Title                                                                  Formula
           Title                                                                  Amount
           Title                                                                  Formula
           Title
             Account title                                              Amount
           Title
             Account title                                    Amount
             Account title                                    Amount Formula      Formula
           Net income                                                             Formula

(b) Prepare a single-step income statement.
                                           FOX COMPANY
                                          Income Statement
                               For the Year Ended December 31, 2012
            Revenues
              Account title                                                       Amount
              Account title                                                       Amount
                Title                                                             Formula
            Expenses
              Account title                                             Amount
              Account title                                             Amount
              Account title                                             Amount
              Account title                                             Amount
                Title                                                             Formula
            Net income                                                            Formula




8bb0fda2-1334-45ba-994b-5fa7b698b1bc.xlsx, Exercise E5-10, Page 4 of 16 Page(s), 12/13/20112:33 PM
              Name:                                                                           Date:
          Instructor:                                                                       Course:
Accounting Principles, Tenth Edition by Weygandt, Kieso, and Kimmel
Primer on Using Excel in Accounting by Rex A Schildhouse
P5-1A, Journalize purchase and sales transactions under a perpetual inventory system.
O’Quinn Co. distributes suitcases to retail stores and extends credit terms of 1/10, n/30 to all of its customers. At the end
of June, O’Quinn’s inventory consisted of suitcases costing                 $1,200      During the month of July, the following
merchandising transactions occurred.

    July 1     Purchased suitcases on account for              $1,800       from Emerson Manufacturers, FOB destination, terms
               2/10, n/30. The appropriate party also made a cash payment of                   $100      for freight on this date.
    July 3     Sold suitcases on account to Straume Satchels for                $2,000     The cost of suitcases sold is
                  $1,200
    July 9     Paid Emerson Manufacturers in full.
   July 12     Received payment in full from Straume Satchels.
   July 17     Sold suitcases on account to The Going Concern for               $1,800     The cost of the suitcases sold was
                  $1,080
   July 18     Purchased suitcases on account for              $1,900       from Hume Manufacturers, FOB shipping point,
               terms 1/10, n/30. The appropriate party also made a cash payment of                            $125
               on this date.
   July 20     Received          $300       credit (including freight) for suitcases returned to Hume Manufacturers.
   July 21     Received payment in full from The Going Concern.
   July 22     Sold suitcases on account to Desmond’s for                       $2,250     The cost of suitcases sold is
                  $1,350
   July 30     Paid Hume Manufacturers in full.
   July 31     Granted Desmond’s                $200            credit for suitcases returned costing         $120

Sansomite's chart of accounts includes the following:
                   101     Cash                                                 401      Sales Revenue
                   112     Accounts Receivable                                  412      Sales Returns and Allowances
                   120     Inventory                                            414      Sales Discounts
                   201     Accounts Payable                                     505      Cost of Goods Sold

Instructions:
Journalize the transactions for the month of July for O’Quinn using a perpetual inventory system.

    Jul. 1     Account title                                                                   Amount
                 Account title                                                                               Amount

      3        Account title                                                                   Amount
                 Account title                                                                               Amount

               Account title                                                                   Amount
                 Account title                                                                               Amount

      9        Account title                                                                   Amount
                 Account title                                                                               Amount
                 Account title                                                                               Amount

     12        Account title                                                                   Amount
               Account title                                                                   Amount
                 Account title                                                                               Amount

     17        Account title                                                                   Amount
                 Account title                                                                               Amount

               Account title                                                                   Amount
                 Account title                                                                               Amount




       8bb0fda2-1334-45ba-994b-5fa7b698b1bc.xlsx, Problem P5-1A, Page 5 of 16 Page(s), 12/13/20112:33 PM
              Name:                                                               Date:
         Instructor:                                                            Course:
   18     Account title
Accounting Principles, Tenth Edition by Weygandt, Kieso, and Kimmel               Amount
              Account title                                                                    Amount

            Account title                                                         Amount
              Account title                                                                    Amount

    20      Account title                                                         Amount
              Account title                                                                    Amount

    21      Account title                                                         Amount
            Account title                                                         Amount
              Account title                                                                    Amount

    22      Account title                                                         Amount
              Account title                                                                    Amount

            Account title                                                         Amount
              Account title                                                                    Amount

    30      Account title                                                         Amount
              Account title                                                                    Amount

    31      Account title                                                         Amount
              Account title                                                                    Amount

            Account title                                                         Amount
              Account title                                                                    Amount




      8bb0fda2-1334-45ba-994b-5fa7b698b1bc.xlsx, Problem P5-1A, Page 6 of 16 Page(s), 12/13/20112:33 PM
stomers. At the end
nth of July, the following


, FOB destination, terms
or freight on this date.
tcases sold is




 suitcases sold was

OB shipping point,
             for freight

anufacturers.

tcases sold is




e
 and Allowances
ts
 Sold




                             8bb0fda2-1334-45ba-994b-5fa7b698b1bc.xlsx, Problem P5-1A, Page 7 of 16 Page(s), 12/13/20112:33 PM
8bb0fda2-1334-45ba-994b-5fa7b698b1bc.xlsx, Problem P5-1A, Page 8 of 16 Page(s), 12/13/20112:33 PM
           Name:                                                                                                       Date:
       Instructor:                                                                                                   Course:
Accounting Principles, Tenth Edition by Weygandt, Kieso, and Kimmel
Primer on Using Excel in Accounting by Rex A Schildhouse
P5-8A, Complete accounting cycle beginning with a worksheet.
The trial balance of Mr. Eko Fashion Center contained the following accounts at November 30, the end of the company’s fiscal year.
                                                                  MR. EKO FASHION CENTER
                                                                           Worksheet
                                                                        November 30, 2012
                                        Trial Balance                Adjustments              Adj'd Trial Balance Income Statement             Balance Sheet
            Account titles              Dr.         Cr.             Dr.              Cr.         Dr.         Cr.        Dr.        Cr.         Dr.       Cr.
Cash                                    8,700
Accounts Receivable                    30,700
Inventory                              44,700
Supplies                                6,200
Equipment                             133,000
Accum Deprec - Equip                               28,000
Notes Payable                                      51,000
Accounts Payable                                   48,500
Owner's Capital                                    90,000
Owner's Drawings                       12,000
Sales Revenue                                     755,200
Sales Returns and Allowances            8,800
Cost of Goods Sold                    497,400
Salaries and Wages Expense            140,000
Advertising Expense                    24,400
Utilities Expense                      14,000
Maintenance and Repairs Exp            12,100
Freight-out                            16,700
Rent Expense                           24,000
Totals                                972,700     972,700
Supplies Expense
Depreciation Expense
Interest Expense                                           (c)
Interest Payable
Totals
Net Income / Net Loss
Totals

        Key: (a) Supplies used, (b) Depreciation expense, (c) Depreciation expense - Equipment, (d) Accrued interest payable, (e) Adjustment of inventory.




                                8bb0fda2-1334-45ba-994b-5fa7b698b1bc.xlsx, Problem P5-8A, Page 9 of 16 Page(s), 12/13/20112:33 PM
             Name:                                                                                                     Date:
        Instructor:                                                                                                  Course:
Adjustment data:
Accounting Principles, Tenth Edition by Weygandt, Kieso, and Kimmel
1. Store supplies on hand totaled   $2,000
2. Depreciation is        $11,500 on the equipment.
3. Interest of             $4,000 is accrued on notes payable at November 30.
4. Inventory actually on hand is               $44,400

Instructions:
(a) Review the trial balance on the worksheet, above, and complete the worksheet.
(b) Prepare a multiple-step income statement and an owner’s equity statement for the year, and a classified balance sheet as of November 30, 2012. Notes
payable of                $20,000 are due in January 2013.

                                     MR. EKO FASHION CENTER                                                     MR. EKO FASHION CENTER
                                          Income Statement                                                            Balance Sheet
                                For the Year Ended November 30, 2012                                                November 30, 2012
           Sales Revenues                                                                                                 Assets
             Account title                                                         Amount Current assets
             Account title                                                         Amount Account title                                        Amount
             Title                                                                 Formula Account title                                       Amount
           Account title                                                           Amount Account title                                        Amount
           Title                                                                   Formula Account title                                       Amount
           Operating Expenses                                                                 Title                                                        Formula
             Account title                                       Amount                   Property, plant, and equipment
             Account title                                       Amount                     Account title                                      Amount
             Account title                                       Amount                     Less: Account title                                Amount      Formula
             Account title                                       Amount                       Total assets                                                 Formula
             Account title                                       Amount
             Account title                                       Amount                                            Liabilities and Owner’s Equity
             Account title                                       Amount                      Current liabilities
             Account title                                       Amount                        Account title                                      Amount
                 Total operating expenses                                          Formula     Account title                                      Amount
           Income from operations                                                  Formula     Account title                                      Amount
           Other expenses and losses                                                             Title                                                     Formula
             Account title                                                         Amount    Long-term liabilities
           Net income                                                              Formula     Account title                                               Amount
                                                                                                 Title                                                     Formula
                                                                                             Owner’s equity
                                                                                               Account title                                               Amount
                                                                                                 Total liabilities and owner’s equity                      Formula




                               8bb0fda2-1334-45ba-994b-5fa7b698b1bc.xlsx, Problem P5-8A, Page 10 of 16 Page(s), 12/13/20112:33 PM
          Name:                                                                                                        Date:
      Instructor:                                                                                                    Course:
                    MR. EKO FASHION CENTER
Accounting Principles, Tenth Edition by Weygandt, Kieso, and Kimmel
                               Owner’s Equity Statement
                        For the Year Ended November 30, 2012
           Title                                                Amount
           Title                                      Amount
           Title                                      Amount    Formula
           Title                                                Formula

(c) Journalize the adjusting entries.                                                       (d) Journalize the closing entries.
              Nov. 30 Account title                             Amount
                           Account title                                          Amount     Nov. 30    Account title                 Amount
                                                                                                          Account title                        Amount
                   30   Account title                           Amount
                          Account title                                           Amount        30      Account title                 Amount
                                                                                                          Account title                        Amount
                   30   Account title                           Amount                                    Account title                        Amount
                          Account title                                           Amount                  Account title                        Amount
                                                                                                          Account title                        Amount
                   30   Account title                           Amount                                    Account title                        Amount
                          Account title                                           Amount                  Account title                        Amount
                                                                                                          Account title                        Amount
(e) Prepare a post-closing trial balance.                                                                 Account title                        Amount
                                            MR. EKO FASHION CENTER                                        Account title                        Amount
                                             Post-Closing Trial Balance                                   Account title                        Amount
                                                November 30, 2012                                         Account title                        Amount
                                                                Debit            Credit
                        Account title
                        Account title                                                           30      Account title                 Amount
                        Account title                                                                     Account title                        Amount
                        Account title
                        Account title                                                           30      Account title                 Amount
                        Account title                                                                     Account title                        Amount
                        Account title
                        Account title
                        Account title
                        Account title
                                                                Formula           Formula




                                 8bb0fda2-1334-45ba-994b-5fa7b698b1bc.xlsx, Problem P5-8A, Page 11 of 16 Page(s), 12/13/20112:33 PM
8bb0fda2-1334-45ba-994b-5fa7b698b1bc.xlsx, Problem P5-8A, Page 12 of 16 Page(s), 12/13/20112:33 PM
8bb0fda2-1334-45ba-994b-5fa7b698b1bc.xlsx, Problem P5-8A, Page 13 of 16 Page(s), 12/13/20112:33 PM
8bb0fda2-1334-45ba-994b-5fa7b698b1bc.xlsx, Problem P5-8A, Page 14 of 16 Page(s), 12/13/20112:33 PM
              Name:                                                                        Date:
          Instructor:                                                                    Course:
Accounting Principles, Tenth Edition by Weygandt, Kieso, and Kimmel
Primer on Using Excel in Accounting by Rex A Schildhouse
P5-1B, Journalize purchase and sales transactions under a perpetual inventory system.
Jeremy’s Book Warehouse distributes hardcover books to retail stores and extends credit terms of 2/10, n/30 to all of
its customers. At the end of May, Jeremy’s inventory consisted of books purchased for             $1,800      During
June, the following merchandising transactions occurred.

   June 1     Purchased books on account for                 $1,600    from Davies Publishers, FOB destination, terms
               2/10, n/30. The appropriate party also made a cash payment of              $50       for freight on this date.
   June 3         Sold books on account to Reading Rainbow for            $2,500      The cost of the books sold was
                 $1,440
   June 6     Received          $100      credit for books returned to Davies Publishers.
   June 9     Paid Davies Publishers in full, less discount.
  June 15     Received payment in full from Reading Rainbow.
  June 17     Sold books on account to Lapidus Books for                  $1,800      The cost of the books sold was
                 $1,080
  June 20     Purchased books on account for                 $1,500    from Fahey Publishers, FOB destination, terms
              2/15, n/30. The appropriate party also made a cash payment of               $50       for the freight on this date.
  June 24     Received payment in full from Lapidus Books.
  June 26     Paid Fahey Publishers in full, less discount.
  June 28     Sold books on account to Carlyle Bookstore for              $1,400      The cost of the books sold was
                  $850
  June 30     Granted Carlyle Bookstore                       $120         credit for books returned costing             $72

Jeremy’s Book Warehouse’s chart of accounts includes the following:
                101     Cash                                                 401      Sales Revenue
                112     Accounts Receivable                                  412      Sales Returns and Allowances
                120     Inventory                                            414      Sales Discounts
                201     Accounts Payable                                     505      Cost of Goods Sold

Instructions:
Journalize the transactions for the month of June for Jeremy’s Book Warehouse using a perpetual inventory system.

   Jun 1      Account title                                                  Amount
                Account title                                                               Amount

     3        Account title                                                  Amount
                Account title                                                               Amount

              Account title                                                  Amount
                Account title                                                               Amount

     6        Account title                                                  Amount
                Account title                                                               Amount

     9        Account title                                                  Amount
                Account title                                                               Amount
                Account title                                                               Amount

     15       Account title                                                  Amount
                Account title                                                               Amount

     17       Account title                                                  Amount
                Account title                                                               Amount

              Account title                                                  Amount
                Account title                                                               Amount

     20       Account title                                                  Amount



          8bb0fda2-1334-45ba-994b-5fa7b698b1bc.xlsx, Problem P5-1B, Page 15 of 16 Page(s), 12/13/20112:33 PM
             Name:                                                               Date:
         Instructor:                                                           Course:
           Account title
Accounting Principles, Tenth Edition by Weygandt, Kieso, and Kimmel               Amount

    24       Account title                                           Amount
             Account title                                           Amount
               Account title                                                      Amount

    26       Account title                                           Amount
               Account title                                                      Amount
               Account title                                                      Amount

    28       Account title                                           Amount
               Account title                                                      Amount

             Account title                                           Amount
               Account title                                                      Amount

    30       Account title                                           Amount
               Account title                                                      Amount

             Account title                                           Amount
               Account title                                                      Amount




         8bb0fda2-1334-45ba-994b-5fa7b698b1bc.xlsx, Problem P5-1B, Page 16 of 16 Page(s), 12/13/20112:33 PM
ba-994b-5fa7b698b1bc.xlsx, Problem P5-1B, Page 16 of 16 Page(s), 12/13/201110:30 PM

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:197
posted:12/13/2011
language:English
pages:16