Chapter 3 Spreadsheet Solution

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
A B C D E F G
c03problem 9/25/2014 18:37 3/6/2001
Laiho Industries' December 31 Balance Sheets
(in thousands of dollars)
Assets 2001 2000
Cash and cash equivalents $102,850 $89,725
Accounts Receivable $103,365 $85,527
Inventories $38,444 $34,982
Total current assets $244,659 $210,234
Fixed assets $67,165 $42,436
Total assets $311,824 $252,670
Liabilities and equity
Accounts payable $30,761 $23,109
Accruals $30,477 $22,656
Notes payable $16,717 $14,217
Total current liabilities $77,955 $59,982
Long-term debt $76,264 $63,914
Total liabilities $154,219 $123,896
Common stock $100,000 $90,000
Retained Earnings $57,605 $38,774
Total common equity $157,605 $128,774
Total liabilities and equity $311,824 $252,670
Laiho Industries December 31 Income Statements
(in thousands of dollars)
2001 2000
Sales $455,150 $364,120
Expenses excluding depr. and amort. $386,878 $321,109
EBITDA $68,272 $43,011
Depreciation and Amortization $7,388 $6,752
EBIT $60,884 $36,259
Interest Expense $8,575 $7,829
EBT $52,309 $28,430
Taxes (40%) $20,924 $11,372
Net Income $31,385 $17,058
Common dividends $12,554 $6,823
Addition to retained earnings $18,831 $10,235
INPUT DATA 2001 2000
Year-end Stock Price $17.25 $14.75
# of shares (in thousands) 10,000 9,000
Lease payment $75,000 $75,000
Sinking fund payment $0 $0
Tax rate 40% 40%
Chapter 3. Solution to end-of-chapter spreadsheet problem
Harcourt, Inc. items and derived items copyright 2002 by Harcourt, Inc.
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
A B C D E F G
Once we have this information set, we can calculate the necessary ratios for this analysis.
Ratio Analysis 2001 2000 Industry Avg
Liquidity Ratios
Current Ratio 3.14 3.50 N
Quick Ratio 2.65 2.92 O
Asset Management Ratios T
Inventory Turnover 11.84 10.41
Days Sales Outstanding 81.76 84.56 A
Fixed Assets Turnover 6.78 8.58 V
Total Assets Turnover 1.46 1.44 A
Debt Management Ratios I
Debt Ratio 49.46% 49.03% L
Times-interest-earned ratio 7.10 4.63 A
EBITDA coverage ratio 1.71 1.42 B
Cash fixed charge coverage ratio 1.71 1.42 L
Profitability Ratios E
Profit Margin 6.90% 4.68%
Basic Earning Power 19.53% 14.35%
Return on Assets 10.06% 6.75%
Return on Equity 19.91% 13.25%
Market Value Ratios
Earnings per share $3.14 $1.90
Price-to-earnings ratio 5.50 7.78
Cash flow per share $3.88 $2.65
Price-to-cash flow ratio 4.45 5.58
Book Value per share $15.76 $14.31
Market-to-book ratio 1.09 1.03
Generally, we would have industry average data which could be used for comparative purposes, but
such data is not available for this problem.
a. Has Laiho's liquidity position improved or worsened? Explain.
Since the current ratio decreased from 3.50 to 3.14, and the quick ratio from 2.92 to 2.65, we
conclude that Laiho's liquidity position has deteriorated. If industry average data were available,
we would compare Laiho with those averages.
b. Has Laiho's ability to manage assets improved or worsened? Explain.
Results of Laiho's asset management ratios are inconclusive. The inventory turnover ratio and
day's sales outstanding both improved, which suggests better management. However, the fixed and
total asset turnover ratios both worsened. Therefore, it is impossible to say definitively if asset
management improved or declined. Again, industry average data would be useful.
c. How has Laiho's profitability changed over the last year?
Laiho's profitability greatly improved in the year 2001. All profit measures increased greatly. As
we shall see from the DuPont analysis below, the primary reason for this improvement was that
costs increased less rapidly than sales, i.e., the company's cost controls were good.
d. Perform an extended Du Pont analysis for Laiho for 2000 and 2001.
ROE = PM x TA Turnover x Equity Multiplier
2001 19.91% 6.90% 1.46 1.98
2000 13.25% 4.68% 1.44 1.96
Here we see that the TA turnover and Equity Multiplier were both essentially unchanged, while the
Profit Margin improved substantially. That improvement in the PM led to the sizeable increase in
the ROE.
With only two years of data, it is not worthwhile constructing graphs, but if we had several more
years of data, we would make some graphs to facilitate interpreting the results. Similarly, if we had
industry average data, we would surely compare Laiho with its peers.
Harcourt, Inc. items and derived items copyright 2002 by Harcourt, Inc.

Chapter 3 Spreadsheet Solution

Uploaded by

Copyright:

Available Formats

Chapter 3 Spreadsheet Solution

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Chapter 3 Spreadsheet Solution

Uploaded by

Copyright:

Available Formats

1

You might also like