excel solutions - cases.xls

Case SolutionsCases 1,2,4,5,7

Input boxes in tanOutput boxes in yellowGiven data in blueCalculations in redAnswers in green

Case Solutions

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

Input area:

2004 2005

Cost of goods sold $ 84,310 $ 106,450

Cash 12,165 18,380

Depreciation 23,800 26,900

Interest expense 5,180 5,930

Selling & Administrative 16,580 21,640

Accounts payable 21,500 24,350

Fixed assets 105,000 134,000

Sales 165,390 201,600

Accounts receivable 8,620 11,182

Notes payable 9,800 10,700

Long-term debt 53,000 61,000

Inventory 18,140 24,894

New equity - 10,000

Tax rate 20%

Dividend percentage 30%

Output area:

2004 Income StatementSales $ 165,390 Cost of goods sold 84,310 Selling & Administrative 16,580 Depreciation 23,800 EBIT $ 40,700 Interest 5,180 EBT $ 35,520 Taxes 7,104 Net income $ 28,416 Dividends $ 8,525 Addition to retained earnings $ 19,891

2005 Income StatementSales $ 201,600 Cost of goods sold 106,450 Selling & Administrative 21,640 Depreciation 26,900 EBIT $ 46,610 Interest 5,930 EBT $ 40,680 Taxes 8,136 Net income $ 32,544 Dividends $ 9,763 Addition to retained earnings $ 22,781

Balance sheet as of Dec. 31, 2004Cash $ 12,165 Accounts payable $ 21,500 Accounts receivable 8,620 Notes payable 9,800 Inventory 18,140 Current liabilities $ 31,300 Current assets $ 38,925

Long-term debt $ 53,000 Net fixed assets $ 105,000 Owners' equity $ 59,625 Total assets $ 143,925 Total liab. & equity $ 143,925

Balance sheet as of Dec. 31, 2005Cash $ 18,380 Accounts payable $ 24,350 Accounts receivable 11,182 Notes payable 10,700 Inventory 24,894 Current liabilities $ 35,050 Current assets $ 54,456

Long-term debt $ 61,000 Net fixed assets $ 134,000 Owners' equity $ 92,406 Total assets $ 188,456 Total liab. & equity $ 188,456

2004 2005Operating cash flow $ 57,396 $ 65,374

Capital SpendingEnding net fixed assets $ 134,000 - Beginning net fixed assets 105,000 + Depreciation 26,900 Net capital spending $ 55,900

Change in Net Working CapitalEnding NWC $ 19,406 -Beginning NWC 7,625 Change in NWC $ 11,781

Cash Flow from AssetsOperating cash flow $ 65,374 - Net capital spending 55,900 -Change in NWC 11,781 Cash flow from assets $ (2,307)

Cash Flow to CreditorsInterest paid $ 5,930 -Net New Borrowing 8,000 Cash flow to Creditors $ (2,070)

Cash Flow to StockholdersDividends paid $ 9,763 -Net new equity raised 10,000 Cash flow to Stockholders $ (237)

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

Input area:

Sales $ 12,870,000 COGS $ 9,070,000 Other expenses $ 1,538,000 Depreciation $ 420,000 EBIT $ 1,842,000 Interest $ 231,500 Taxable income $ 1,610,500 Taxes (40%) $ 644,200 Net income $ 966,300

Dividends $ 289,890 Add to RE $ 676,410

Assets Liabilities & EquityCurrent Assets Current Liabilities Cash $ 234,000 Accounts Payable $ 497,000 Accounts rec. $ 421,000 Notes Payable $ 1,006,000 Inventory $ 472,000 Total CL $ 1,503,000 Total CA $ 1,127,000

Long-term debt $ 2,595,000 Fixed assets Net PP&E $ 7,228,000 Shareholder Equity

Common stock $ 100,000 Retained earnings $ 4,157,000 Total Equity $ 4,257,000

Total Assets $ 8,355,000 Total L&E $ 8,355,000

Growth rate 20%Minimum FA purchase $ 3,000,000

Output area:

Current ratio 0.75 Quick ratio 0.44 Cash ratio 0.16 Total asset turnover 1.54 Inventory turnover 19.22 Receivables turnover 30.57 Total debt ratio 0.49 Debt-equity ratio 0.96 Equity multiplier 1.96 Times interest earned 7.96 Cash coverage ratio 9.77 Profit margin 7.51%Return on assets 11.57%Return on equity 22.70%

Retention ratio 0.70 Internal growth rate 8.81%Sustainable growth rate 18.89%

Sales $ 15,444,000

COGS $ 10,884,000 Other expenses $ 1,845,600 Depreciation $ 420,000 EBIT $ 2,294,400 Interest $ 231,500 Taxable income $ 2,062,900 Taxes (40%) $ 825,160 Net income $ 1,237,740

Dividends $ 371,322 Add to RE $ 866,418



Common stock $ 100,000 Retained earnings $ 5,023,418 Total Equity $ 425,700


EFN $ 5,402,900

EFN if minimum FA purchase is $ 3,000,000



Common stock $ 100,000 Retained earnings $ 5,023,418 Total Equity $ 425,700


EFN $ 6,957,300

Case #4 - Conch Republic Electronics

Input Area:

Equipment $15,000,000Salvage value $3,000,000R&D $750,000 sunk costMarketing study $200,000 sunk cost

Year 1 Year 2 Year 3Sales(units) 70,000 80,000 100,000Depreciation rate 14.29% 24.49% 17.49%Sales of old PDA 80,000 60,000 Lost sales 15,000 15,000

Price $250 VC $86 FC $3,000,000 Price of old PDA $240 Price reduction of old PDA $20 VC of old PDA $68 Tax rate 35%NWC percentage 20%Required return 12%

Sensivity analysisNew price $260 Quantity change 100 NOTE: Change in units per year

Output Area:

Sales Year 1 Year 2 Year 3New $17,500,000 $20,000,000 $25,000,000 Lost sales 3,600,000 3,600,000 Lost rev. 1,300,000 900,000 Net sales $12,600,000 $15,500,000 $25,000,000

VCNew $6,020,000 $6,880,000 $8,600,000

Lost sales 1,020,000 1,020,000 $5,000,000 $5,860,000 $8,600,000

Sales $12,600,000 $15,500,000 $25,000,000 VC 5,000,000 5,860,000 8,600,000 Fixed costs 3,000,000 3,000,000 3,000,000 Dep 2,143,500 3,673,500 2,623,500 EBT $2,456,500 $2,966,500 $10,776,500 Tax 859,775 1,038,275 3,771,775 NI $1,596,725 $1,928,225 $7,004,725 +Dep 2,143,500 3,673,500 2,623,500 OCF $3,740,225 $5,601,725 $9,628,225

NWCBeg $0 $2,520,000 $3,100,000End 2,520,000 3,100,000 5,000,000NWC CF ($2,520,000) ($580,000) ($1,900,000)

Net CF $1,220,225 $5,021,725 $7,728,225

SalvageBV of equipment $3,346,500Taxes 121,275Salvage CF $3,121,275

Net CF Time0 ($15,000,000)1 $1,220,225 2 $5,021,725 3 $7,728,225 4 $8,516,725 5 $13,885,100

Payback period 3.121 PI 1.592 IRR 27.79%NPV $8,884,884.28

Sensitivity to change in price

Sales Year 1 Year 2 Year 3

New $18,200,000 $20,800,000 $26,000,000 Lost sales 3,600,000 3,600,000 Lost rev. 1,300,000 900,000 Net sales $13,300,000 $16,300,000 $26,000,000

VCNew $6,020,000 $6,880,000 $8,600,000 Lost sales 1,020,000 1,020,000

$5,000,000 $5,860,000 $8,600,000

Sales $13,300,000 $16,300,000 $26,000,000 VC 5,000,000 5,860,000 8,600,000 Fixed costs 3,000,000 3,000,000 3,000,000 Dep 2,143,500 3,673,500 2,623,500 EBT $3,156,500 $3,766,500 $11,776,500 Tax 1,104,775 1,318,275 4,121,775 NI $2,051,725 $2,448,225 $7,654,725 +Dep 2,143,500 3,673,500 2,623,500 OCF $4,195,225 $6,121,725 $10,278,225

NWCBeg $0 $2,660,000 $3,260,000End 2,660,000 3,260,000 5,200,000NWC CF ($2,660,000) ($600,000) ($1,940,000)

Net CF $1,535,225 $5,521,725 $8,338,225


Net CF Time0 ($15,000,000)1 $1,535,225 2 $5,521,725 3 $8,338,225 4 $9,099,225 5 $14,542,600

NPV $10,742,189.61

$185,730.53

Sensitivity to change in quantity

Sales Year 1 Year 2 Year 3New $17,525,000 $20,025,000 $25,025,000 Lost sales 3,600,000 3,600,000 Lost rev. 1,300,000 900,000 Net sales $12,625,000 $15,525,000 $25,025,000

VCNew $6,028,600 $6,888,600 $8,608,600 Lost sales 1,020,000 1,020,000

$5,008,600 $5,868,600 $8,608,600

Sales $12,625,000 $15,525,000 $25,025,000 VC 5,008,600 5,868,600 8,608,600 Fixed costs 3,000,000 3,000,000 3,000,000 Dep 2,143,500 3,673,500 2,623,500 EBT $2,472,900 $2,982,900 $10,792,900 Tax 865,515 1,044,015 3,777,515 NI $1,607,385 $1,938,885 $7,015,385 +Dep 2,143,500 3,673,500 2,623,500 OCF $3,750,885 $5,612,385 $9,638,885

NWCBeg $0 $2,525,000 $3,105,000End 2,525,000 3,105,000 5,005,000NWC CF ($2,525,000) ($580,000) ($1,900,000)

Net CF $1,225,885 $5,032,385 $7,738,885


Net CF Time0 ($15,000,000)1 $1,225,885 2 $5,032,385 3 $7,738,885

NPV/P

4 $8,527,385 5 $13,900,760

NPV $8,921,684.04

$368.00 NPV/Q

Year 4 Year 585,000 75,000

12.49% 8.93%

Year 4 Year 5$21,250,000 $18,750,000

$21,250,000 $18,750,000

$7,310,000 $6,450,000

$7,310,000 $6,450,000

$21,250,000 $18,750,000 7,310,000 6,450,000 3,000,000 3,000,000 1,873,500 1,339,500

$9,066,500 $7,960,500 3,173,275 2,786,175

$5,893,225 $5,174,325 1,873,500 1,339,500

$7,766,725 $6,513,825

$5,000,000 $4,250,0004,250,000 0$750,000 $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 $19,500,000 7,310,000 6,450,000 3,000,000 3,000,000 1,873,500 1,339,500

$9,916,500 $8,710,500 3,470,775 3,048,675

$6,445,725 $5,661,825 1,873,500 1,339,500

$8,319,225 $7,001,325

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

$9,099,225 $11,421,325

Year 4 Year 5$21,275,000 $18,775,000

$21,275,000 $18,775,000

$7,318,600 $6,458,600

$7,318,600 $6,458,600

$21,275,000 $18,775,000 7,318,600 6,458,600 3,000,000 3,000,000 1,873,500 1,339,500

$9,082,900 $7,976,900 3,179,015 2,791,915

$5,903,885 $5,184,985 1,873,500 1,339,500

$7,777,385 $6,524,485

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

$8,527,385 $10,779,485

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

Input area:

Industry PE 12.50 Company EPS $ 1.60 Conversion price (stock) $ 25.00 Maturity (years) $ 20.00 Convertible bond coupon 6%Conversion value of bond $ 800 Plain vanilla coupon 10%

Output area:

Stock price $ 20.00

Intrinsic bond value $656.82

Floor value $800.00

Conversion ratio 32.00

Conversion premium 25.00%

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

Input area:

Bond 1 book value $ 200 Bond 2 book value $ 300 BV of debt (balance sheet) $ 505 BV of equity $ 6,280 Stock price $ 35.05 Market capitalization $ 87,510 Shares outstanding (billions) 2.50 Most recent dividend $ - Beta 1.677 3-month Treasury bill rate 1.60%Market risk premium 8.60%Bond 1 price 109.575 Bond 2 price 117.978 Bond 1 YTM 3.645087%Bond 2 YTM 5.702993%Tax rate 35%

Output area:

16.02%

Bond 1 $ 200 0.40 $ 219.15 Bond 2 300 0.60 353.93 Total $ 500 1.00 $ 573.08

Book value of company $ 6,785 millionMarket value of company $ 88,083.08 million

WACC using book value 15.07%

WACC using market value 15.94%

kE from CAPM

Book value(millions)

Percentof total

Market value(millions)

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

0.38 3.65% 1.46% 1.39% 0.62 5.70% 3.42% 3.52% 1.00 4.88% 4.92%

Percentof total

Yield toMaturity

Bookvalues

Marketvalues

Case #7 - Piepkorn Manufacturing Working Capital Management

Input Area:

Sales (1st quarter of next year) $ 784,000 A/R $ 426,000 Percent uncollectible 10%Collection period 57 % of purchases for next Q sales 50%Suppliers paid 53 % of sales for expenses 25%Interest and dividends $ 85,000 Outlay in fourth Q $ 240,000 Beginning cash balance $ 164,000 Target cash balance $ 100,000 Borrowing rate 1.5%Invested securities 1.0%Beginning short-term borrowing $ - Change the following three lines for credit termsCredit terms Piepkorn offers 0% / 10 net Percentage of customers taking credit 25%Credit terms offered to Piepkorn 0% / 15 net

Q1 Q2 Q3Gross sales $ 695,000 $ 708,000 $ 741,000

Output Area:

Sales after discount $ 695,000.00 $ 708,000.00 $ 741,000.00 Credit purchases after discount $ 354,000.00 $ 370,500.00 $ 378,500.00

Q1: Cash flow $ 29,311.11 Q2: Cash flow $ 76,983.33 Q3: Cash flow $ 76,061.11 Q4: Cash flow $ (151,433.33)

A B C D E F G

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

Cash Balance Q1 Q2 Q3

Beginning cash balance $ 164,000.00 $ 193,311.11 $ 270,294.44 Net cash inflow 29,311.11 76,983.33 76,061.11 Ending cash balance $ 193,311.11 ## $ 270,294.44 $ 346,355.56 Minimum cash balance 100,000.00 ### 100,000.00 100,000.00 Cumulative surplus (deficit) $ 93,311.11 ### $ 170,294.44 $ 246,355.56

Short-term Financial Plan Beginning cash balance $ 100,000.00 ### $ 100,000.00 $ 100,000.00 Net cash inflow 29,311.11 76,983.33 76,061.11 New short-term investments (29,951.11) (77,922.84) (77,779.85)Income on short-term investments 640.00 939.51 1,718.74 Short-term investments sold - - - New short-term borrowing - - - Interest on short-term borrowing - - - Short-term borrowing repaid - - - Ending cash balance $ 100,000.00 ### $ 100,000.00 $ 100,000.00 Minimum cash balance (100,000.00)### (100,000.00) (100,000.00)Cumulative surplus (deficit) $ - ## $ - $ -

Beginning short-term investments $ 64,000.00 $ 93,951.11 $ 171,873.96 Ending short-term investments 93,951.11 171,873.96 249,653.81 Beginning short-term debt - - - Ending short-term debt $ - $ - $ -

Q1: Excess funds at start of quarter of $64,000.00 earns $640.00Q2: Excess funds at start of quarter of $93,951.11 earns $939.51Q3: Excess funds at start of quarter of $171,873.96 earns $1,718.74Q4: Excess funds at start of quarter of $249,653.81 earns $2,496.54

Net cash costQ1 $ 640.00 Q2 939.51 Q3 1,718.74 Q4 2,496.54 Cash generated by short-term financing $ 5,794.79

Rate on credit offered by Piepkorn 0.00%

Rate on credit offered to Piepkorn 0.00%

A B C D E F G40

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

45

40

Q4 $ 757,000

$ 757,000.00 $ 392,000.00

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

27

28

29

30

31

32

33

34

35

36

37

38

39

Cash BalanceQ4

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

Short-term Financial Plan $ 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.in income.

H40

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

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

I40

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

excel solutions - cases.xls

Documents