wk 3

Target

 

Download most recent Annual Report from their Website. ( Post with assignment)

 

Prepare an Excel Workbook.

Tab 1 – Horizontal Analysis of the Balance Sheet

Tab 2 – Vertical Analysis of the Income Statement

Tab 3 – Financial Ratios – Select 2 profitability and 2 liquidity ratios  (Use formate covered in class,  I want to see details of the calculations.)

 

Review analysis work and select 4 of the more critical observations made in the analysis.

 

Write a paper of no more that 1250 words in which you conduct a comparative and ratio analysis to measure profitability and liquidity.

Instructions

the template workbooks

worksheets are not protected.
Overtyping any data may remove it.

.” All of these will return January 01,

, in the format set in the template.

” 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.

s,” 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.

” 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.

” 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.”

Instructions for the Microsoft Excel Templates by Rex A Schildhouse

Be advised

,

and

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

2010

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

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,” “

Amount

Where a yellow highlighted cell shows titles such as “

Formula

=

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

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

, 2012

90,000

December 31
2012 2011 Amount

age

Current assets Amount Amount Formula Formula
Plant assets (net) Amount Amount Formula Formula

Formula Formula Formula Formula

Current liabilities Amount Amount Formula Formula
Long-term liabilities Amount Amount Formula Formula

Formula Formula Formula Formula

Common stock, $1 par Amount Amount Formula Formula
Retained earnings Amount Amount Formula Formula

Formula Formula Formula Formula

Formula Formula Formula Formula

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

December 31,

2011

Current assets

$106,000

$

90,000

Plant assets (net)

400,000

3

50,000

Current liabilities

99,000

65,000

Long-term liabilities

122,000

Common stock, $1 par

1

30,000

115,000

Retained earnings

1

55,000

1

70,000

Instructions:

Prepare a schedule showing a horizontal analysis for 2012 using 2011 as the base year.

PAULETTI INC.

Condensed Balance Sheet

Increase or (Decrease)

Percent

Assets

Total assets

Liabilities

Total liabilities

Stockholders’ Equity

Total stockholders’ equity

Total liabilities and stockholders’ equity

&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

2012 2011

30,000

70,000 48,000

Instructions:

2012

2011
Amount Percent Amount Percent

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

E13-4, Prepare vertical analysis.
Operating data for Gladow Corporation are presented below.
	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	24,000
	Net income
Prepare a schedule showing a vertical analysis for 2012 and 201	1.
GLADOW CORPORATION
Condensed	Income Statement
	For the Years Ended December 31
				Account title

&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

December 31
2012 2011 2010

50,000

48,000

90,000

55,000 70,000 45,000

$600,000

Current liabilities

85,000 50,000

Retained earnings

$725,000 $600,000 $533,000

GLASSGOW CORPORATION

Income Statement
For the Years Ended December 31
2012 2011
Sales

$600,000

40,000 30,000

Cost of goods sold

Net income

$70,000

1.

,

and

Instructions:

2012 2011
Amount = Formula Amount = Formula
Amount Amount

2012 2011
Amount = Formula Amount = Formula
Amount Amount

2012
Amount = Formula

Amount

Amount

Number

2011
Amount = Formula Times
Amount + Amount
Number

2012

Amount = Formula

Amount + Amount
Number

2011
Amount = Formula Per share
Amount + Amount
Number

2012 2011
Amount = Formula Times Amount = Formula Times
Amount Amount

2012 2011
Amount = Formula Amount = Formula
Amount Amount

2012 2011
Amount = Formula Amount = Formula
Amount Amount

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
Cash	$30,000	$20,000	$18,000
Receivables (net)		45,000
Other current assets	95,000	64,000
Investments
Plant and equipment (net)	500,000	370,000	358,000
	$725,000		$533,000
$	85,000	$80,000		$70,000
Long-term debt	145,000
Common stock, $10 par	320,000	310,000	300,000
175,000	125,000	113,000
$7	40,000
Less: Sales returns and allowances
Net sales	700,000	570,000
425,000	350,000
Gross profit	275,000	220,000
Operating expenses (Including income taxes)	180,000	150,000
$95,000
Additional information:
The market price of Glassgow’s common stock was	$7.00	$7.50
$8.50	for 2010, 2011, and 2012, respectively.
2.	You must compute dividends paid. All dividends were paid in cash.
(a) (1) Compute the profit margin ratios for 2011 and 2012.
(a) (2) Compute the gross profit ratio for 2011 and 2012.
(a) (3) Compute the asset turnover ratio for 2011 and 2012.
			Times
			+
(a) (4) Compute the earnings per share ratio for 2011 and 2012.
	Per share
(a) (5) Compute the price-earnings ratio for 2011 and 2012.
(a) (6) Compute the payout ratio for 2011 and 2012.
(a) (7) Compute the debt to total assets ratio for 2011 and 2012.
(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