Financial Statement Analysis

a. Within this workbook there is a worksheet labeled "Adjusted Trial Balances".Within that worksheet you will find fully completed trial balances for 5 years.Also within this workbook you will find worksheets for comparative income statements and comparative balance sheets.Using the trial balances or comparative financial statements you need to calculate a variety of ratios.You will find the required ratios in the worksheet labeled "Ratios Historical". I am trying to Complete all the required ratios AND analysis (see Ratios Historical worksheet for further instructions). You MUST use formulas and linking to compute your ratios.

June 30, 2017June 30, 2016June 30, 2015June 30, 2014June 30, 2013

AdjustedAdjustedAdjustedAdjustedAdjusted

Trial BalanceTrial BalanceTrial BalanceTrial BalanceTrial BalanceAcct. No.Account TitleDr.Cr.Dr.Cr.Dr.Cr.Dr.Cr.Dr.Cr.100Cash384,504.00

407,574.24

392,194.08

419,109.36

376,813.92

102Accounts Receivable555,666.00

589,005.96

566,779.32

605,675.94

544,552.68

103Allowance for Doubtful Accounts

22,580.00

23,934.80

19,588.80

24,612.20

22,128.40104Merchandise Inventory456,897.00

484,310.82

466,034.94

498,017.73

447,759.06

105Estimated Returns Inventory32,500.00

34,450.00

33,150.00

35,425.00

31,850.00

106Office Supplies5,689.00

6,030.34

5,802.78

6,201.01

5,575.22

107Prepaid Insurance15,400.00

16,324.00

15,708.00

16,786.00

15,092.00

120Investments - Trading80,000.00

84,800.00

81,600.00

87,200.00

78,400.00

121Investments - Available for Sale35,400.00

37,524.00

36,108.00

38,586.00

34,692.00

122Investments - Held to Maturity80,000.00

84,800.00

81,600.00

87,200.00

78,400.00

123Valuation Allowance52,000.00

55,120.00

53,040.00

56,680.00

50,960.00

140Land1,500,000.00

1,500,000.00

1,500,000.00

1,500,000.00

1,500,000.00

145Building1,200,000.00

1,200,000.00

1,200,000.00

1,200,000.00

1,200,000.00

146Accumulated Depreciation - Building

750,000.00

720,000.00

690,000.00

660,000.00

630,000.00151Equipment85,000.00

80,000.00

80,000.00

80,000.00

75,000.00

152Accumulated Depreciation - Equipment

68,000.00

59,500.00

51,000.00

42,500.00

34,000.00153Office Furniture14,000.00

14,000.00

14,000.00

14,000.00

14,000.00

154Accumulated Depreciation - Office Furniture

12,000.00

10,000.00

8,000.00

6,000.00

4,000.00201Accounts Payable

680,980.00

721,838.80

694,599.60

168,900.00

170,000.00202Wages Payable

-

-

203Interest Payable

-

-

204Dividends Payable

-

-

205Unearned Rent

10,000.00

10,600.00

10,200.00

10,900.00

9,800.00206Customer Refunds Payable

18,795.00

19,922.70

19,170.90

20,486.55

18,419.10250Notes Payable

650,000.00

689,000.00

663,000.00

708,500.00

637,000.00251Bonds Payable

1,300,000.00

1,378,000.00

1,000,000.00

1,417,000.00

1,274,000.00252Premium on Bonds Payable

56,000.00

50,000.00

35,000.00

55,000.00

45,000.00253Discount on Bonds Payable16,000.00

10,000.00

10,000.00

12,000.00

15,000.00

252Mortgage (Warehouse) Payable

300,000.00

318,000.00

950,000.00

920,000.00

1,000,000.00300Common Stock, $2 Par, 100,000 Authorized; 50,000 shares Issued/Outstanding

100,000.00

100,000.00

100,000.00

100,000.00

100,000.00301Paid In Capital - Excess of Par

350,000.00

371,000.00

357,000.00

381,500.00

343,000.00330Retained Earnings

222,158.00

258,022.60

358,176.52

281,556.86

294,694.17331Cash Dividends200,000.00

250,000.00

300,000.00

300,000.00

250,000.00

340Treasury Stock50,000.00

60,000.00

20,000.00

20,000.00

10,000.00

341Unrealized (Gain) Loss Available for Sale Securities----

500Sales

4,056,800.00

4,137,936.00

4,302,480.00

4,097,368.00

3,935,096.00600Cost of Goods Sold1,285,698.00

1,311,411.96

1,464,267.80

1,298,554.98

1,247,127.06

700Wage Expense (hourly workers)756,894.00

772,031.88

832,583.40

764,462.94

734,187.18

701Salaries Expense (Exempt Staff)800,000.00

816,000.00

880,000.00

808,000.00

776,000.00

702Marketing Expense295,000.00

300,900.00

324,500.00

297,950.00

286,150.00

703Travel and Entertainment Expense56,789.00

57,924.78

62,467.90

57,356.89

55,085.33

704Bad Debt Expense42,185.00

43,028.70

46,403.50

42,606.85

40,919.45

705Property Tax Expense500,000.00

510,000.00

550,000.00

505,000.00

485,000.00

706Office Maintenance & Repair Expense2,000.00

2,040.00

2,200.00

2,020.00

1,940.00

707Accounting Expenses10,000.00

10,200.00

65,000.00

10,100.00

9,700.00

708Insurance Expense12,000.00

12,240.00

13,200.00

12,120.00

11,640.00

709Utilities Expense65,450.00

66,759.00

71,995.00

66,104.50

63,486.50

710Office Supplies Expense546.00

556.92

600.60

551.46

529.62

711Telecommunications Expense5,425.00

5,533.50

5,967.50

5,479.25

5,262.25

712Depreciation Expense - Building30,000.00

30,000.00

30,000.00

30,000.00

30,000.00

713Depreciation Expense - Equipment8,500.00

8,500.00

8,500.00

8,500.00

8,500.00

714Depreciation Expense - Office Furniture2,000.00

2,000.00

2,000.00

2,000.00

2,000.00

800Rent Income

8,000.00

2,000.00

10,000.00

15,000.00

5,000.00801Unrealized Gain - Trading Securities

5,500.00

1,200.00

5,600.00

16,000.00

8,000.00802Realized Gain - Investment Securities

85,460.00

56,485.00

8,690.00

28,560.00

15,000.00900Interest Expense25,000.00

26,500.00

27,500.00

27,250.00

24,500.00

901Unrealized Loss - Trading Securities2,500.00

2,650.00

2,750.00

2,725.00

2,450.00

902Realized Loss - Investment Securities33,230.00

35,223.80

36,553.00

36,220.70

32,565.40

Total8,696,273.008,696,273.008,927,439.908,927,439.909,282,505.829,282,505.828,953,883.618,953,883.618,545,137.678,545,137.67

Total Expenses and Revenue3,933,217.004,155,760.004,013,500.544,197,621.004,426,488.704,326,770.003,977,002.574,156,928.003,817,042.793,963,096.00

Net Income (Loss)222,543.00

184,120.46

(99,718.70)179,925.43

146,053.21

Panther MarineComparative Income StatementFor the Years end June 30, 2017, 2016, 2015, 2014, 2013

6/30/2017

6/30/2016

6/30/2015

6/30/2014

6/30/2013Sales Revenue

Net Credit Sales4,056,800.00

4,137,936.00

4,302,480.00

4,097,368.00

3,935,096.00Cost of Goods Sold1,285,698.00

1,311,411.96

1,464,267.80

1,298,554.98

1,247,127.06Gross Profit2,771,102.00

2,826,524.04

2,838,212.20

2,798,813.02

2,687,968.94Operating Expenses

Wage Expense (hourly workers)756,894.00

772,031.88

832,583.40

764,462.94

734,187.18Salaries Expense (Exempt Staff)800,000.00

816,000.00

880,000.00

808,000.00

776,000.00Marketing Expense295,000.00

300,900.00

324,500.00

297,950.00

286,150.00Travel and Entertainment Expense56,789.00

57,924.78

62,467.90

57,356.89

55,085.33Bad Debt Expense42,185.00

43,028.70

46,403.50

42,606.85

40,919.45Property Tax Expense500,000.00

510,000.00

550,000.00

505,000.00

485,000.00Office Maintenance & Repair Expense2,000.00

2,040.00

2,200.00

2,020.00

1,940.00Accounting Expenses10,000.00

10,200.00

65,000.00

10,100.00

9,700.00Insurance Expense12,000.00

12,240.00

13,200.00

12,120.00

11,640.00Utilities Expense65,450.00

66,759.00

71,995.00

66,104.50

63,486.50Office Supplies Expense546.00

556.92

600.60

551.46

529.62Telecommunications Expense5,425.00

5,533.50

5,967.50

5,479.25

5,262.25Depreciation Expense - Building30,000.00

30,000.00

30,000.00

30,000.00

30,000.00Depreciation Expense - Equipment8,500.00

8,500.00

8,500.00

8,500.00

8,500.00Depreciation Expense - Office Furniture2,000.00

2,000.00

2,000.00

2,000.00

2,000.00Total Operating Expenses2,586,789.00

2,637,714.78

2,895,417.90

2,612,251.89

2,510,400.33Income from Operations184,313.00

188,809.26

(57,205.70)

186,561.13

177,568.61Other Revenues and Gains

Rent Income8,000.00

2,000.00

10,000.00

15,000.00

5,000.00Unrealized Gain - Trading Securities5,500.00

1,200.00

5,600.00

16,000.00

8,000.00Realized Gain - Investment Securities85,460.00

56,485.00

8,690.00

28,560.00

15,000.00Total Other Revenue and Gains98,960.00

59,685.00

24,290.00

59,560.00

28,000.00Other Expenses and Losses

Interest Expense25,000.00

26,500.00

27,500.00

27,250.00

24,500.00Unrealized Loss - Trading Securities2,500.00

2,650.00

2,750.00

2,725.00

2,450.00Realized Loss - Investment Securities33,230.00

35,223.80

36,553.00

36,220.70

32,565.40Total Other Expenses and Losses60,730.00

64,373.80

66,803.00

66,195.70

59,515.40

Net Income (Loss)222,543.00

184,120.46

(99,718.70)

179,925.43

146,053.21

Common Dividends200,000

250,000

300,000

300,000

Weighted Average Common Shares50,000

50,000

50,000

50,000

Market Price per Common Share46.00

41.00

31.00

42.00

Panther MarineBalance SheetJune 30, 2017, 2016, 2015, 2014, 2013

6/30/2017

6/30/2016

6/30/2015

6/30/2014

6/30/2013ASSETS

Current assets

Cash384,504.00

407,574.24

392,194.08

419,109.36

376,813.92Accounts Receivable555,666.00

589,005.96

566,779.32

605,675.94

544,552.68Allowance for Doubtful Accounts(22,580.00)

(23,934.80)

(19,588.80)

(24,612.20)

(22,128.40)Merchandise Inventory456,897.00

484,310.82

466,034.94

498,017.73

447,759.06Estimated Returns Inventory32,500.00

34,450.00

33,150.00

35,425.00

31,850.00Office Supplies5,689.00

6,030.34

5,802.78

6,201.01

5,575.22Prepaid Insurance15,400.00

16,324.00

15,708.00

16,786.00

15,092.00Total Current Assets1,428,076.00

1,513,760.56

1,460,080.32

1,556,602.84

1,399,514.48Property, plant & equipment

Land1,500,000.00

1,500,000.00

1,500,000.00

1,500,000.00

1,500,000.00Building1,200,000.00

1,200,000.00

1,200,000.00

1,200,000.00

1,200,000.00Accumulated Depreciation - Building(750,000.00)

(720,000.00)

(690,000.00)

(660,000.00)

(630,000.00)Equipment85,000.00

80,000.00

80,000.00

80,000.00

75,000.00Accumulated Depreciation - Equipment(68,000.00)

(59,500.00)

(51,000.00)

(42,500.00)

(34,000.00)Office Furniture14,000.00

14,000.00

14,000.00

14,000.00

14,000.00Accumulated Depreciation - Office Furniture(12,000.00)

(10,000.00)

(8,000.00)

(6,000.00)

(4,000.00)Total Property, plant & equipment1,969,000.00

2,004,500.00

2,045,000.00

2,085,500.00

2,121,000.00Other Assets

Investments - Trading80,000.00

84,800.00

81,600.00

87,200.00

78,400.00Investments - Available for Sale35,400.00

37,524.00

36,108.00

38,586.00

34,692.00Investments - Held to Maturity80,000.00

84,800.00

81,600.00

87,200.00

78,400.00Valuation Allowance52,000.00

55,120.00

53,040.00

56,680.00

50,960.00Total Other Assets247,400.00

262,244.00

252,348.00

269,666.00

242,452.00Total Assets3,644,476.00

3,780,504.56

3,757,428.32

3,911,768.84

3,762,966.48

LIABILITIES & STOCKHOLDER'S EQUITY

LIABILITIES

Current Liabilities

Accounts Payable680,980.00

721,838.80

694,599.60

168,900.00

170,000.00Unearned Rent10,000.00

10,600.00

10,200.00

10,900.00

9,800.00Customer Refunds Payable18,795.00

19,922.70

19,170.90

20,486.55

18,419.10Total Current Liabilities709,775.00

752,361.50

723,970.50

200,286.55

198,219.10Long-term Liabilities

Notes Payable650,000.00

689,000.00

663,000.00

708,500.00

637,000.00Bonds Payable1,300,000.00

1,378,000.00

1,000,000.00

1,417,000.00

1,274,000.00Premium on Bonds Payable56,000.00

50,000.00

35,000.00

55,000.00

45,000.00Discount on Bonds Payable(16,000.00)

(10,000.00)

(10,000.00)

(12,000.00)

(15,000.00)Mortgage (Warehouse) Payable300,000.00

318,000.00

950,000.00

920,000.00

1,000,000.00Total Long-term Liabilities2,290,000.00

2,425,000.00

2,638,000.00

3,088,500.00

2,941,000.00Total Liabilities2,999,775.00

3,177,361.50

3,361,970.50

3,288,786.55

3,139,219.10

STOCKHOLDER'S EQUITY

Common Stock, $2 Par, 100,000 Authorized; 50,000 shares Issued/Outstanding100,000.00

100,000.00

100,000.00

100,000.00

100,000.00Paid In Capital - Excess of Par350,000.00

371,000.00

357,000.00

381,500.00

343,000.00Treasury Stock(50,000.00)

(60,000.00)

(20,000.00)

(20,000.00)

(10,000.00)Cash Dividends(200,000.00)

(250,000.00)

(300,000.00)

(300,000.00)

(250,000.00)Ending Retained Earnings444,701.00

442,143.06

258,457.82

461,482.29

440,747.38Total Stockholder's Equity644,701.00

603,143.06

395,457.82

622,982.29

623,747.38

Total Liabilities and Stockholder's Equity3,644,476.00

3,780,504.56

3,757,428.32

3,911,768.84

3,762,966.48

TOTAL ASSETS3,644,476.00

3780504.56

3757428.32

3911768.84

3762966.48TOTAL LIAB & EQUITY3,644,476.00

3780504.56

3757428.32

3911768.84

3762966.48VARIANCE0

0

0

0

0

Panther MarineRatio AnalysisJune 30, 2017, 2016, 2015Instructions: Calculate each of the below ratios for the current year. FOLLOW the below steps to complete the ratio analysis:1. Round ALL answers 2 decimal places2. Must SHOW ratio descriptions (see current ratio for example)3. MUST use excel formulas and links to IS comparative and BS comparative worksheets within this workbook (see current ratio for example)4. MUST indicate if current year answer is favorable, unfavorable, or same in realtion to prior year AND industry (see current ratio for example)5. USE information at bottom of this worksheet to calculate certain ratios6. If portion of ratio is not available, still use in ratio description and use0 as part of formula7. Inventory calculations do NOT include estimated returns inventory8. USE below ratios and comparative statements to write a memo to Panther Marine management explaining your findings

6/30/2017

6/30/2016

6/30/2015

INDUSTRY

Prior Year/Industry: Favorable, Unfavorable, SameLiquidity

Current Ratio2.0112.10

2.10

2.20

Prior Year - UnfavorableCurrent Assets1,428,076

1,513,761

1,460,080

Industry - UnfavorbleCurrent Liabilities709,775

752,362

723,971

Quick Ratio

1.41

1.41

1.62

Accounts Receivable Turnover

7.44

7.63

10.00

Inventory Turnover

2.76

3.04

2.50

Profitability

Profit Margin

4.45%

-2.32%

3.5%

Asset Turnover

1.10

1.12

1.02

Return on Total Assets

4.89%

-2.60%

10.50%

Return on Common Shareholders' Equity

36.88%

-19.58%

18.30%

Earnings per Share (EPS)

$3.68

$(1.99)

$3.05

Price-Earnings (P/E ) Ratio

11.13

(15.54)

12.54

Dividends per Share

$5.00

$6.00

$2.00

Solvency Ratios

Ratio of Fixed Assets to Long-Term Liabilities

0.83

0.78

0.80

Fixed Assets (net)

Long-Term Liabilities

6/30/2017

6/30/2016

6/30/2015

Common Dividends200,000

250,000

300,000

Weighted Average Common Shares50,000

50,000

50,000

Market Price per Share46

41

31