Docstoc

Instructions

Document Sample
Instructions 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:
                           th
Intermediate Accounting, 14 Edition by Kieso, Weygandt, and Warfield
Primer on Using Excel in Accounting by Rex A Schildhouse

E7-1 (Determining Cash Balance) The controller for Weinstein Co. is attempting to determine the
amount of cash and cash equivalents to be reported on its December 31, 2012, balance sheet. The
following information is provided.
1. Commercial savings account has a balance of:                        $600,000 and a commercial checking
account has a balance of              $800,000 Both are held at First National Bank of Olathe.
2. Money market fund account held at Volonte Co. (a mutual fund organization) permits Weinstein to
write checks on this balance,        $5,000,000
3. Travel advances for executive travel for the first quarter of the next year (employee to reimburse
through salary reduction) are:        $180,000
4. A separate cash fund is restricted for the retirement of long-term debt. Its balance is         $1,500,000
5. Petty cash fund of:                  $1,000
6. An I.O.U. from Marianne Koch, a company customer, in the amount of                               $150,000
7. A bank overdraft of                $110,000 has occurred at one of the banks the company uses to
deposit its cash receipts. At the present time, the company has no deposits at this bank.
8. The company has two certificates of deposit, each totaling                           $500,000 These CDs have
a maturity of 120 days.
9. Weinstein has received a check that is dated January 12, 2013, in the amount of                  $125,000
10. Weinstein has agreed to maintain a cash balance of                 $500,000 at all times at First National
Bank of Olathe to ensure future credit availability.
11. Weinstein has purchased          $2,100,000 of commercial paper of Sergio Leone Co. which is due in
60 days.
12. Currency and coin on hand amounted to               $7,700

Instructions:
(a) Compute the amount of cash to be reported on Weinstein Co.’s balance sheet at
    December 31, 2012.
Cash for Weinstein includes the following:
1. Commercial savings account at First National Bank of Olathe with a balance of:                     Amount
Text line, as required                                                                                Amount
Text line, as required                                                                                Amount
Text line, as required                                                                                Amount
Text line, as required                                                                                Amount
Text line, as required                                                                                Amount
Text line, as required                                                                                Amount
Text line, as required                                                                                Amount
Cash reported on December 31, 2012, balance sheet:                                                    Amount

(b) Indicate the proper reporting for items that are not reported as cash on the December 31, 2012,
   balance sheet.
Text line, as required

                                                                                                      Amount
Text line, as required

                                                                                                      Amount
Text line, as required




      d9adae03-69e0-4b93-8097-191993e98f0c.xlsx, Exercise 7-1, Page 3 of 7, 12/13/2011, 2:09 PM
Name:                                               Date:
Text line, as required
Instructor:                                         Course:
Intermediate Accounting, 14th Edition by Kieso, Weygandt, and Warfield                    Amount
Text line, as required

                                                                                          Amount
Text line, as required

                                                                                          Amount
Text line, as required

                                                                                          Amount
Text line, as required

                                                                                          Amount
Text line, as required

                                                                                          Amount
Text line, as required

                                                                                          Amount
Text line, as required

                                                                                          Amount
Text line, as required

                                                                                          Amount




     d9adae03-69e0-4b93-8097-191993e98f0c.xlsx, Exercise 7-1, Page 4 of 7, 12/13/2011, 2:09 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

E7-5 (Record Sales Gross and Net) On June 3, Bolton Company sold to Arquette Company
merchandise having a sale price of              $2,000      with terms of 2/10, n/60, f.o.b.
shipping point. An invoice totaling               $90       , terms n/30, was received by Arquette on
June 8 from John Booth Transport Service for the freight cost. On June 12, the company received a check
for the balance due from Arquette Company.

Instructions:
(a) Prepare journal entries on the Bolton Company books to record all the events noted above under
    each of the following bases:
   (1) Sales and receivables are entered at gross selling price.

     Jun 3       Account Title
                  Account Title
                  Account Title

    Jun 12       Account Title
                 Account Title
                  Account Title

 (2) Sales and receivables are entered at net cash discounts.

     Jun 3       Account Title
                  Account Title
                  Account Title

    Jun 12       Account Title
                  Account Title
                  Account Title

(b) Prepare the journal entry under basis 2, assuming that Arquette Company did not remit payment
   until July 29.

     Jul 29      Account Title
                  Account Title
                  Account Title




     d9adae03-69e0-4b93-8097-191993e98f0c.xlsx, Exercise 7-5, Page 5 of 7, 12/13/2011, 2:09 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

P7-1 (Determine Proper Cash Balance) Francis Equipment Co. closes its books regularly on December
31, but at the end of 2012 it held its cash book open so that a more favorable balance sheet could be
prepared for credit purposes. Cash receipts and disbursements for the first 10 days of January were
recorded as December transactions. The information is given below.
1. January cash receipts recorded in the December cash book totaled             $45,640     , of which
    $28,000       represents cash sales, and       $17,640     represents collections on account for
which cash discounts of                $360      were given.
2. January cash disbursements recorded in the December check register liquidated accounts payable
of                   $22,450          on which discounts of        $250      were taken.
3. The ledger has not been closed for 2012.
4. The amount shown as inventory was determined by physical count on December 31, 2012.
The company uses the periodic method of inventory.
Instructions:
(a) Prepare any entries you consider necessary to correct Francis’s accounts at December 31.

  Dec 31, 12     Account Title                                                Amount
                 Account Title                                                Amount
                  Account Title                                                                Amount
                  Account Title                                                                Amount

  Dec 31, 12     Account Title                                                Amount
                 Account Title                                                Amount
                  Account Title                                                                Amount


(b) To what extent was Francis Equipment Co. able to show a more favorable balance sheet at
   December 31 by holding its cash book open? (Compute working capital and the current ratio.)
   Assume that the balance sheet that was prepared by the company showed the following amounts:
                                                 Per Balance Sheet             After Adjustment
                 Current assets                  Debit       Credit         Debit           Credit
                   Cash                          $39,000                     Amount
                   Receivables                    42,000                     Amount
                   Inventories                    67,000                     Amount
                     Totals:                    $148,000                     Formula

                 Current Liabilities
                  Accounts Payable                               $45,000                       Amount
                  Other Current Liabilities                       14,200                       Amount
                     Totals:                                     $59,200                       Formula
                       Working capital                           Formula                       Formula

                 Current ratio:                   Formula to 1               Formula to 1




        d9adae03-69e0-4b93-8097-191993e98f0c.xlsx, Problem 7-1, Page 6 of 7, 12/13/2011, 2:09 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

P7-3 (Bad-Debt Reporting—Aging) Manilow Corporation operates in an industry that has a high rate of
bad debts. Before any year-end adjustments, the balance in Manilow’s Accounts Receivable account
was                $555,000 and the Allowance for Doubtful Accounts had a credit balance of
   $40,000       The year-end balance reported in the balance sheet for the Allowance for Doubtful
Accounts will be based on the aging schedule shown below.

                        Days Account Outstanding              Amount          Probability of Collection
                 Less than 16 days                            $300,000                  0.98
                 Between 16 and 30 days                        100,000                  0.90
                 Between 31 and 45 days                         80,000                  0.85
                 Between 46 and 60 days                         40,000                  0.80
                 Between 61 and 75 days                         20,000                  0.55
                 Over 75 days                                   15,000                  0.00

Instructions:
(a) What is the appropriate balance for the Allowance for Doubtful Accounts at the year-end?

         Days Account Outstanding                Amount       Probability of Collection   Doubtful Accts:
0-15 days                                        $300,000               0.98                     Formula
16 - 30 days                                      100,000               0.90                     Formula
31 - 45 days                                       80,000               0.85                     Formula
46 - 60 days                                       40,000               0.80                     Formula
61 - 75 days                                       20,000               0.55                     Formula
Over 75 days                                       15,000               0.00                     Formula
                                                                                                 Formula

Enter text answer here.




(b) Show how Accounts Receivable would be presented on the balance sheet.
               Account Title                                              Amount
               Less: Account Title                                        Amount
                 Text Title                                                                      Formula

(c) What is the dollar effect of the year-end bad debt adjustment on the before-tax income?
Enter text answer as desired here.

                 Text Title                                                                       Amount
                 Text explanation as required.
                                                                                                 Amount
                 Text Title                                                                      Formula




      d9adae03-69e0-4b93-8097-191993e98f0c.xlsx, Problem 7-3, Page 7 of 7, 12/13/2011, 2:09 PM

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