Download - Excel Solutions - Cases.xls
![Page 1: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/1.jpg)
Case SolutionsCases 1,2,4,5,7
Input boxes in tanOutput boxes in yellowGiven data in blueCalculations in redAnswers in green
![Page 2: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/2.jpg)
Case Solutions
![Page 3: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/3.jpg)
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:
![Page 4: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/4.jpg)
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
![Page 5: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/5.jpg)
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)
![Page 6: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/6.jpg)
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
![Page 7: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/7.jpg)
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
Assets Liabilities & EquityCurrent Assets Current Liabilities Cash $ 280,800 Accounts Payable $ 596,400 Accounts rec. $ 505,200 Notes Payable $ 1,006,000 Inventory $ 566,400 Total CL $ 1,602,400 Total CA $ 1,352,400
Long-term debt $ 2,595,000 Fixed assets Net PP&E $ 8,673,600 Shareholder Equity
Common stock $ 100,000 Retained earnings $ 5,023,418 Total Equity $ 425,700
Total Assets $ 10,026,000 Total L&E $ 4,623,100
EFN $ 5,402,900
EFN if minimum FA purchase is $ 3,000,000
Assets Liabilities & EquityCurrent Assets Current Liabilities Cash $ 280,800 Accounts Payable $ 596,400 Accounts rec. $ 505,200 Notes Payable $ 1,006,000 Inventory $ 566,400 Total CL $ 1,602,400 Total CA $ 1,352,400
Long-term debt $ 2,595,000 Fixed assets Net PP&E $ 10,228,000 Shareholder Equity
Common stock $ 100,000 Retained earnings $ 5,023,418 Total Equity $ 425,700
Total Assets $ 11,580,400 Total L&E $ 4,623,100
EFN $ 6,957,300
![Page 8: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/8.jpg)
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
![Page 9: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/9.jpg)
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
![Page 10: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/10.jpg)
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
SalvageBV of equipment $3,346,500Taxes 121,275Salvage CF $3,121,275
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
![Page 11: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/11.jpg)
$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
SalvageBV of equipment $3,346,500Taxes 121,275Salvage CF $3,121,275
Net CF Time0 ($15,000,000)1 $1,225,885 2 $5,032,385 3 $7,738,885
NPV/P
![Page 12: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/12.jpg)
4 $8,527,385 5 $13,900,760
NPV $8,921,684.04
$368.00 NPV/Q
![Page 13: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/13.jpg)
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
![Page 14: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/14.jpg)
$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
![Page 15: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/15.jpg)
$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
![Page 16: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/16.jpg)
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
![Page 17: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/17.jpg)
![Page 18: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/18.jpg)
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%
![Page 19: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/19.jpg)
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)
![Page 20: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/20.jpg)
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
![Page 21: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/21.jpg)
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
![Page 22: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/22.jpg)
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
![Page 23: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/23.jpg)
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
![Page 24: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/24.jpg)
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
![Page 25: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/25.jpg)
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
![Page 26: Excel Solutions - Cases.xls](https://reader033.vdocuments.us/reader033/viewer/2022042706/577cc09f1a28aba711909c48/html5/thumbnails/26.jpg)
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