Case Solutions

Cases 1,2,4,5,7
Input boxes in tan
Output boxes in yellow
Given data in blue
Calculations in red
Answers in green

Case #1 - Cash Flows and Financial Statements at Sunset Boards

Input area:

Cost of goods sold

Cash
Depreciation
Interest expense
Selling & Administrative
Accounts payable
Fixed assets
Sales
Accounts receivable
Notes payable
Long-term debt
Inventory
New equity
Tax rate
Dividend percentage
Output area:

2004
84,310
12,165
23,800
5,180
16,580
21,500
105,000
165,390
8,620
9,800
53,000
18,140
20%
30%

2005
106,450
18,380
26,900
5,930
21,640
24,350
134,000
201,600
11,182
10,700
61,000
24,894
10,000

2004 Income Statement

Sales
$
Cost of goods sold
Selling & Administrative
Depreciation
EBIT
$
Interest
EBT
$
Taxes
Net income
$
Dividends
$
Addition to retained earnings $

165,390
84,310
16,580
23,800
40,700
5,180
35,520
7,104
28,416
8,525
19,891

2005 Income Statement

Sales
$
Cost of goods sold
Selling & Administrative
Depreciation
EBIT
$
Interest
EBT
$
Taxes
Net income
$
Dividends
$
Addition to retained earnings $

201,600
106,450
21,640
26,900
46,610
5,930
40,680
8,136
32,544
9,763
22,781

Cash
Accounts receivable
Inventory
Current assets
Net fixed assets
Total assets

Balance sheet as of Dec. 31, 2004

12,165
Accounts payable
8,620
Notes payable
18,140
Current liabilities
$
38,925
Long-term debt
$
105,000
Owners' equity
$
143,925
Total liab. & equity
$

Net fixed assets

Total assets

Balance sheet as of Dec. 31, 2005

18,380
Accounts payable
11,182
Notes payable
24,894
Current liabilities
$
54,456
Long-term debt
$
134,000
Owners' equity
$
188,456
Total liab. & equity

Operating cash flow

Cash
Accounts receivable
Inventory
Current assets

2004
57,396

2005
65,374

$
$

21,500
9,800
31,300

$
$
$

53,000
59,625
143,925

$
$

24,350
10,700
35,050

$
$
$

61,000
92,406
188,456

Capital Spending
Ending net fixed assets
- Beginning net fixed assets
+ Depreciation
Net capital spending

134,000
105,000
26,900
55,900

Change in Net Working Capital

Ending NWC
$
-Beginning NWC
Change in NWC
$

19,406
7,625
11,781

Cash Flow from Assets

Operating cash flow
- Net capital spending
-Change in NWC
Cash flow from assets

65,374
55,900
11,781
(2,307)

Cash Flow to Creditors

Interest paid
-Net New Borrowing
Cash flow to Creditors

Cash Flow to Stockholders

Dividends paid
-Net new equity raised
Cash flow to Stockholders

$
$

$
$

5,930
8,000
(2,070)

9,763
10,000
(237)

Case #2 - Ratios and Financial Planning at S&S Air

Input area:

Sales
COGS
Other expenses
Depreciation
EBIT
Interest
Taxable income
Taxes (40%)
Net income

$ 12,870,000
$ 9,070,000
$ 1,538,000
$
420,000
$ 1,842,000
$
231,500
$ 1,610,500
$
644,200
$
966,300

Dividends
Add to RE

$
$

289,890
676,410

Assets
Current Assets
Cash
Accounts rec.
Inventory
Total CA
Fixed assets
Net PP&E

$
$
$
$

234,000
421,000
472,000
1,127,000

7,228,000

Total Assets

8,355,000

Growth rate
Minimum FA purchase

20%
3,000,000

Output area:

Current ratio
Quick ratio
Cash ratio
Total asset turnover
Inventory turnover
Receivables turnover
Total debt ratio
Debt-equity ratio
Equity multiplier
Times interest earned
Cash coverage ratio
Profit margin
Return on assets
Return on equity

0.75
0.44
0.16
1.54
19.22
30.57
0.49
0.96
1.96
7.96
9.77
7.51%
11.57%
22.70%

Retention ratio
Internal growth rate
Sustainable growth rate

0.70
8.81%
18.89%

Sales

$ 15,444,000

Liabilities & Equity

Current Liabilities
Accounts Payable
$
497,000
Notes Payable
$
1,006,000
Total CL
$
1,503,000
Long-term debt

2,595,000

Shareholder Equity
Common stock
Retained earnings
Total Equity

$
$
$

100,000
4,157,000
4,257,000

Total L&E

8,355,000

COGS
Other expenses
Depreciation
EBIT
Interest
Taxable income
Taxes (40%)
Net income

$ 10,884,000
$ 1,845,600
$
420,000
$ 2,294,400
$
231,500
$ 2,062,900
$
825,160
$ 1,237,740

Dividends
Add to RE

$
$

371,322
866,418

Assets
Current Assets
Cash
Accounts rec.
Inventory
Total CA

$
$
$
$

Fixed assets
Net PP&E

280,800
505,200
566,400
1,352,400

8,673,600

Total Assets

$ 10,026,000

EFN

Assets

Fixed assets
Net PP&E

$
$
$
$

Long-term debt

2,595,000

Shareholder Equity
Common stock
Retained earnings
Total Equity

$
$
$

100,000
5,023,418
425,700

Total L&E

4,623,100

5,402,900

EFN if minimum FA purchase is

Current Assets
Cash
Accounts rec.
Inventory
Total CA

Liabilities & Equity

Current Liabilities
Accounts Payable
$
596,400
Notes Payable
$
1,006,000
Total CL
$
1,602,400

280,800
505,200
566,400
1,352,400

$ 10,228,000

Total Assets

$ 11,580,400

EFN

6,957,300

$ 3,000,000
Liabilities & Equity
Current Liabilities
Accounts Payable $
596,400
Notes Payable
$
1,006,000
Total CL
$
1,602,400
Long-term debt

2,595,000

Shareholder Equity
Common stock
Retained earnings
Total Equity

$
$
$

100,000
5,023,418
425,700

Total L&E

4,623,100

Case #4 - Conch Republic Electronics

Input Area:

Equipment
Salvage value
R&D
Marketing study

Sales(units)
Depreciation rate
Sales of old PDA
Lost sales
Price
VC
FC
Price of old PDA
Price reduction
of old PDA
VC of old PDA
Tax rate
NWC percentage
Required return
Sensivity analysis
New price
Quantity change

$15,000,000
$3,000,000
$750,000
$200,000

sunk cost
sunk cost

Year 1
70,000
14.29%
80,000
15,000

Year 2
80,000
24.49%
60,000
15,000

Year 3
100,000
17.49%

$250
$86
$3,000,000
$240
$20
$68
35%
20%
12%

$260
100 NOTE: Change in units per year

Output Area:

Sales
New
Lost sales
Lost rev.
Net sales
VC
New

Year 1
$17,500,000
3,600,000
1,300,000
$12,600,000

Year 2
$20,000,000
3,600,000
900,000
$15,500,000

Year 3
$25,000,000

$6,020,000

$6,880,000

$8,600,000

$25,000,000

Lost sales

1,020,000
$5,000,000

1,020,000
$5,860,000

$8,600,000

Sales
VC
Fixed costs
Dep
EBT
Tax
NI
+Dep
OCF

$12,600,000
5,000,000
3,000,000
2,143,500
$2,456,500
859,775
$1,596,725
2,143,500
$3,740,225

$15,500,000
5,860,000
3,000,000
3,673,500
$2,966,500
1,038,275
$1,928,225
3,673,500
$5,601,725

$25,000,000
8,600,000
3,000,000
2,623,500
$10,776,500
3,771,775
$7,004,725
2,623,500
$9,628,225

NWC
Beg
End
NWC CF

$0
2,520,000
($2,520,000)

$2,520,000
3,100,000
($580,000)

$3,100,000
5,000,000
($1,900,000)

Net CF

$1,220,225

$5,021,725

$7,728,225

Salvage
BV of equipment
Taxes
Salvage CF

$3,346,500
121,275
$3,121,275

Net CF

Time
0
1
2
3
4
5

Payback period
PI
IRR
NPV

($15,000,000)
$1,220,225
$5,021,725
$7,728,225
$8,516,725
$13,885,100
3.121
1.592
27.79%
$8,884,884.28

Sensitivity to change in price

Sales

Year 1

Year 2

Year 3

New
Lost sales
Lost rev.
Net sales

$18,200,000
3,600,000
1,300,000
$13,300,000

$20,800,000
3,600,000
900,000
$16,300,000

$26,000,000

$6,020,000
1,020,000
$5,000,000

$6,880,000
1,020,000
$5,860,000

$8,600,000

Sales
VC
Fixed costs
Dep
EBT
Tax
NI
+Dep
OCF

$13,300,000
5,000,000
3,000,000
2,143,500
$3,156,500
1,104,775
$2,051,725
2,143,500
$4,195,225

$16,300,000
5,860,000
3,000,000
3,673,500
$3,766,500
1,318,275
$2,448,225
3,673,500
$6,121,725

$26,000,000
8,600,000
3,000,000
2,623,500
$11,776,500
4,121,775
$7,654,725
2,623,500
$10,278,225

NWC
Beg
End
NWC CF

$0
2,660,000
($2,660,000)

$2,660,000
3,260,000
($600,000)

$3,260,000
5,200,000
($1,940,000)

Net CF

$1,535,225

$5,521,725

$8,338,225

Salvage
BV of equipment
Taxes
Salvage CF

$3,346,500
121,275
$3,121,275

VC
New
Lost sales

Net CF

NPV

Time
0
1
2
3
4
5

($15,000,000)
$1,535,225
$5,521,725
$8,338,225
$9,099,225
$14,542,600
$10,742,189.61

$26,000,000

$8,600,000

NPV/P

$185,730.53

Sensitivity to change in quantity

Sales
New
Lost sales
Lost rev.
Net sales

Year 1
$17,525,000
3,600,000
1,300,000
$12,625,000

Year 2
$20,025,000
3,600,000
900,000
$15,525,000

Year 3
$25,025,000

$6,028,600
1,020,000
$5,008,600

$6,888,600
1,020,000
$5,868,600

$8,608,600

Sales
VC
Fixed costs
Dep
EBT
Tax
NI
+Dep
OCF

$12,625,000
5,008,600
3,000,000
2,143,500
$2,472,900
865,515
$1,607,385
2,143,500
$3,750,885

$15,525,000
5,868,600
3,000,000
3,673,500
$2,982,900
1,044,015
$1,938,885
3,673,500
$5,612,385

$25,025,000
8,608,600
3,000,000
2,623,500
$10,792,900
3,777,515
$7,015,385
2,623,500
$9,638,885

NWC
Beg
End
NWC CF

$0
2,525,000
($2,525,000)

$2,525,000
3,105,000
($580,000)

$3,105,000
5,005,000
($1,900,000)

Net CF

$1,225,885

$5,032,385

$7,738,885

Salvage
BV of equipment
Taxes
Salvage CF

$3,346,500
121,275
$3,121,275

VC
New
Lost sales

Net CF

Time
0
1
2
3

($15,000,000)
$1,225,885
$5,032,385
$7,738,885

$25,025,000

$8,608,600

4
5
NPV
NPV/Q

$8,527,385
$13,900,760
$8,921,684.04
$368.00

Year 4
85,000
12.49%

Year 5
75,000
8.93%

Year 4
$21,250,000

Year 5
$18,750,000

$21,250,000

$18,750,000

$7,310,000

$6,450,000

$7,310,000

$6,450,000

$21,250,000
7,310,000
3,000,000
1,873,500
$9,066,500
3,173,275
$5,893,225
1,873,500
$7,766,725

$18,750,000
6,450,000
3,000,000
1,339,500
$7,960,500
2,786,175
$5,174,325
1,339,500
$6,513,825

$5,000,000
4,250,000
$750,000

$4,250,000
0
$4,250,000

$8,516,725

$10,763,825

Year 4

Year 5

$22,100,000

$19,500,000

$22,100,000

$19,500,000

$7,310,000

$6,450,000

$7,310,000

$6,450,000

$22,100,000
7,310,000
3,000,000
1,873,500
$9,916,500
3,470,775
$6,445,725
1,873,500
$8,319,225

$19,500,000
6,450,000
3,000,000
1,339,500
$8,710,500
3,048,675
$5,661,825
1,339,500
$7,001,325

$5,200,000
4,420,000
$780,000

$4,420,000
0
$4,420,000

$9,099,225

$11,421,325

Year 4
$21,275,000

Year 5
$18,775,000

$21,275,000

$18,775,000

$7,318,600

$6,458,600

$7,318,600

$6,458,600

$21,275,000
7,318,600
3,000,000
1,873,500
$9,082,900
3,179,015
$5,903,885
1,873,500
$7,777,385

$18,775,000
6,458,600
3,000,000
1,339,500
$7,976,900
2,791,915
$5,184,985
1,339,500
$6,524,485

$5,005,000
4,255,000
$750,000

$4,255,000
0
$4,255,000

$8,527,385

$10,779,485

Case #5 - S&S Air's Convertible Bond

Input area:

Industry PE
Company EPS
Conversion price (stock)
Maturity (years)
Convertible bond coupon
Conversion value of bond
Plain vanilla coupon

$
$
$
$

12.50
1.60
25.00
20.00
6%
800
10%

Output area:

Stock price

20.00

Intrinsic bond value

$656.82

Floor value

$800.00

Conversion ratio
Conversion premium

32.00
25.00%

Case #6 - The Cost of Capital for Hubbard Computer, Inc.

Input area:

Bond 1 book value

Bond 2 book value
BV of debt (balance sheet)
BV of equity
Stock price
Market capitalization
Shares outstanding (billions)
Most recent dividend
Beta
3-month Treasury bill rate
Market risk premium
Bond 1 price
Bond 2 price
Bond 1 YTM
Bond 2 YTM
Tax rate

$
$
$
$
$
$

200
300
505
6,280
35.05
87,510
2.50
$
1.677
1.60%
8.60%
109.575
117.978
3.645087%
5.702993%
35%

Output area:

kE from CAPM

16.02%

Bond 1
Bond 2
Total

Book value
(millions)
$
200
300
$
500

Book value of company

Market value of company

$
$

Percent
of total
0.40
0.60
1.00

6,785 million
88,083.08 million

WACC using book value

15.07%

WACC using market value

15.94%

Market value
(millions)
$
219.15
353.93
$
573.08

er, Inc.

Percent
of total
0.38
0.62
1.00

Yield to
Book
Market
Maturity values values
3.65% 1.46% 1.39%
5.70% 3.42% 3.52%
4.88% 4.92%

Case #7 - Piepkorn Manufacturing Working Capital Management

2
3

Input Area:

4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

Sales (1st quarter of next year)

$
A/R
$
Percent uncollectible
Collection period
% of purchases for next Q sales
Suppliers paid
% of sales for expenses
Interest and dividends
$
Outlay in fourth Q
$
Beginning cash balance
$
Target cash balance
$
Borrowing rate
Invested securities
Beginning short-term borrowing
$
Change the following three lines for credit terms
Credit terms Piepkorn offers
Percentage of customers taking credit
Credit terms offered to Piepkorn

784,000
426,000
10%
57
50%
53
25%
85,000
240,000
164,000
100,000
1.5%
1.0%
0% / 10
25%
0% / 15
Q1

net
net

695,000

Q2
708,000

Q3
741,000

Sales after discount

Credit purchases after discount

$
$

695,000.00
354,000.00

$
$

708,000.00
370,500.00

$
$

741,000.00
378,500.00

Q1: Cash flow

Q2: Cash flow
Q3: Cash flow
Q4: Cash flow

$
$
$
$

29,311.11
76,983.33
76,061.11
(151,433.33)

Gross sales

27
28
29

Output Area:

30
31
32
33
34
35
36
37
38
39

Beginning cash balance

Net cash inflow
Ending cash balance
Minimum cash balance
Cumulative surplus (deficit)

43
44
45
46
47
48
49
50
51
52

Beginning cash balance

Net cash inflow
New short-term investments
Income on short-term investments
Short-term investments sold
New short-term borrowing
Interest on short-term borrowing
Short-term borrowing repaid
Ending cash balance
Minimum cash balance
Cumulative surplus (deficit)

53
54
55
56
57
58
59
60
61

Beginning short-term investments

Ending short-term investments
Beginning short-term debt
Ending short-term debt

62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

Q1:
Q2:
Q3:
Q4:

$
$
$

$
$
$

Excess funds at start of quarter of

Excess funds at start of quarter of
Excess funds at start of quarter of
Excess funds at start of quarter of
Net cash cost
Q1
Q2
Q3
Q4
Cash generated by short-term financing

Q1
164,000.00
$
29,311.11
193,311.11 ## $
100,000.00 ###
93,311.11 ### $

Short-term Financial Plan

100,000.00 ### $
100,000.00 $
29,311.11
76,983.33
(29,951.11)
(77,922.84)
640.00
939.51
100,000.00 ### $
100,000.00 $
(100,000.00)###
(100,000.00)
- ## $
$

100,000.00
76,061.11
(77,779.85)
1,718.74
100,000.00
(100,000.00)
-

64,000.00
93,951.11
$64,000.00
$93,951.11
$171,873.96
$249,653.81

640.00
939.51
1,718.74
2,496.54
5,794.79

Rate on credit offered by Piepkorn

0.00%

Rate on credit offered to Piepkorn

0.00%

79
80
81

Q3
270,294.44
76,061.11
346,355.56
100,000.00
246,355.56

77
78

Cash Balance

40
41
42

Q2
193,311.11
76,983.33
270,294.44
100,000.00
170,294.44

93,951.11
171,873.96
earns
earns
earns
earns

$
$

171,873.96
249,653.81
$640.00
$939.51
$1,718.74
$2,496.54

H
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

45
40

Q4
757,000

$
$

757,000.00
392,000.00

27
28
29
30
31
32
33
34
35
36
37
38
39

H
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

$
$
$

$
$
$

62
63
64
65
66

Q4
346,355.56
(151,433.33)
194,922.22
100,000.00
94,922.22

100,000.00
(151,433.33)
2,496.54
148,936.80
100,000.00
(100,000.00)
249,653.81
100,717.01
-

in income.
in income.
in income.
69 in income.
67
68
70
71
72
73
74
75
76
77
78
79
80
81

I
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

I
40
41
42
43
44
45
46
47
48
49
50
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