wk 3

profilekeodar
ch13_excel.xlsx
Home>Business & Finance homework help>Accounting homework help>wk 3

Instructions

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

&F, Page &P of &N, &D&T

E13-3

Name: Date:
Instructor: Course:
Accounting, Fourth Edition by Kimmel, Weygandt, and Kieso
Primer on Using Excel in Accounting by Rex A Schildhouse
E13-3, Prepare horizontal analysis.
Here is financial information for Pauletti Inc.
December 31, 2012 December 31, 2011
Current assets $106,000 $90,000
Plant assets (net) 400,000 350,000
Current liabilities 99,000 65,000
Long-term liabilities 122,000 90,000
Common stock, $1 par 130,000 115,000
Retained earnings 155,000 170,000
Instructions:
Prepare a schedule showing a horizontal analysis for 2012 using 2011 as the base year.
PAULETTI INC.
Condensed Balance Sheet
December 31
Increase or (Decrease)
2012 2011 Amount Percentage
Assets
Current assets Amount Amount Formula Formula
Plant assets (net) Amount Amount Formula Formula
Total assets Formula Formula Formula Formula
Liabilities
Current liabilities Amount Amount Formula Formula
Long-term liabilities Amount Amount Formula Formula
Total liabilities Formula Formula Formula Formula
Stockholders’ Equity
Common stock, $1 par Amount Amount Formula Formula
Retained earnings Amount Amount Formula Formula
Total stockholders’ equity Formula Formula Formula Formula
Total liabilities and stockholders’ equity Formula Formula Formula Formula

&F, &A, Page &P of &N Page(s), &D&T

E13-4

Name: Date:
Instructor: Course:
Accounting, Fourth Edition by Kimmel, Weygandt, and Kieso
Primer on Using Excel in Accounting by Rex A Schildhouse
E13-4, Prepare vertical analysis.
Operating data for Gladow Corporation are presented below.
2012 2011
Sales $800,000 $600,000
Cost of goods sold 520,000 408,000
Selling expenses 120,000 72,000
Administrative expenses 60,000 48,000
Income tax expense 30,000 24,000
Net income 70,000 48,000
Instructions:
Prepare a schedule showing a vertical analysis for 2012 and 2011.
GLADOW CORPORATION
Condensed Income Statement
For the Years Ended December 31
2012 2011
Amount Percent Amount Percent
Account title Amount Formula Amount Formula
Account title Amount Formula Amount Formula
Title Formula Formula Formula Formula
Account title Amount Formula Amount Formula
Account title Amount Formula Amount Formula
Title Formula Formula Formula Formula
Title Formula Formula Formula Formula
Account title Amount Formula Amount Formula
Title Formula Formula Formula Formula

&F, &A, Page &P of &N Page(s), &D&T

P13-3A

Name: Date:
Instructor: Course:
Accounting, Fourth Edition by Kimmel, Weygandt, and Kieso
Primer on Using Excel in Accounting by Rex A Schildhouse
P13-3A, Perform ratio analysis, and discuss change in financial position and operating results.
Condensed balance sheet and income statement data for Glassgow Corporation are presented here.
GLASSGOW CORPORATION
Balance Sheets
December 31
2012 2011 2010
Cash $30,000 $20,000 $18,000
Receivables (net) 50,000 45,000 48,000
Other current assets 90,000 95,000 64,000
Investments 55,000 70,000 45,000
Plant and equipment (net) 500,000 370,000 358,000
$725,000 $600,000 $533,000
Current liabilities $85,000 $80,000 $70,000
Long-term debt 145,000 85,000 50,000
Common stock, $10 par 320,000 310,000 300,000
Retained earnings 175,000 125,000 113,000
$725,000 $600,000 $533,000
GLASSGOW CORPORATION
Income Statement
For the Years Ended December 31
2012 2011
Sales $740,000 $600,000
Less: Sales returns and allowances 40,000 30,000
Net sales 700,000 570,000
Cost of goods sold 425,000 350,000
Gross profit 275,000 220,000
Operating expenses (Including income taxes) 180,000 150,000
Net income $95,000 $70,000
Additional information:
1. The market price of Glassgow’s common stock was $7.00 , $7.50 and
$8.50 for 2010, 2011, and 2012, respectively.
2. You must compute dividends paid. All dividends were paid in cash.
Instructions:
(a) (1) Compute the profit margin ratios for 2011 and 2012.
2012 2011
Amount = Formula Amount = Formula
Amount Amount
(a) (2) Compute the gross profit ratio for 2011 and 2012.
2012 2011
Amount = Formula Amount = Formula
Amount Amount
(a) (3) Compute the asset turnover ratio for 2011 and 2012.
2012
Amount = Formula Times
Amount + Amount
Number
2011
Amount = Formula Times
Amount + Amount
Number
(a) (4) Compute the earnings per share ratio for 2011 and 2012.
2012
Amount = Formula Per share
Amount + Amount
Number
2011
Amount = Formula Per share
Amount + Amount
Number
(a) (5) Compute the price-earnings ratio for 2011 and 2012.
2012 2011
Amount = Formula Times Amount = Formula Times
Amount Amount
(a) (6) Compute the payout ratio for 2011 and 2012.
2012 2011
Amount = Formula Amount = Formula
Amount Amount
(a) (7) Compute the debt to total assets ratio for 2011 and 2012.
2012 2011
Amount = Formula Amount = Formula
Amount Amount
(b) Based on the ratios calculated, discuss briefly the improvement or lack thereof in the financial position and operating results from 2011 to 2012 of Glassgow Corporation.
Enter text answer here.

&F, &A, Page &P of &N Page(s), &D&T