Case Solution
Input boxes in tan
Output boxes in yellow
Given data in blue
Calculations in red
Answers in green
NOTE: Some functions used in these spreadsheets may requ
the "Analysis ToolPak" or "Solver Add-in" be installed in Exc
To install these, click on "Tools|Add-Ins" and select "Analysi
and "Solver Add-In."
ns
uire that
cel.
is ToolPak"
Chapter 2
Cash Flows and Financial Statements at Sunset Boards
Input area:
2010
2011
Cost of goods sold
$
163,849
$
206,886
Cash
23,643
35,721
Depreciation
46,255
52,282
Interest expense
10,056
11,526
Selling & Administrative
32,223
42,058
Accounts payable
41,786
47,325
Net fixed assets
204,068
248,625
Sales
321,437
391,810
Accounts receivable
16,753
21,732
Notes payable
19,046
20,796
Long-term debt
103,006
116,334
Inventory
32,255
43,381
New equity
-
20,500
Tax rate
20%
Dividend percentage
50%
Output area:
2010 Income Statement
Sales
$
321,437
Cost of goods sold
163,849
Selling & Administrative
32,223
Depreciation
46,255
EBIT
$
79,110
Interest
10,056
EBT
$
69,054
Taxes
13,811
Net income
$
55,243
Dividends
$
27,622
Addition to retained earnings
$
27,622
2011 Income Statement
Sales
$
391,810
Cost of goods sold
206,886
Selling & Administrative
42,058
Depreciation
52,282
EBIT
$
90,584
Interest
11,526
EBT
$
79,058
Taxes
15,812
Net income
$
63,246
Dividends
$
31,623
Addition to retained earnings
$
31,623
Balance sheet as of Dec. 31, 2010
Cash
$
23,643
Accounts payable
$
41,786
Accounts receivable
16,753
Notes payable
19,046
Inventory
32,255
Current liabilities
$
60,832
Current assets
$
72,651
Long-term debt
$
103,006
Net fixed assets
$
204,068
Owners' equity
$
112,881
Total assets
$
276,719
Total liab. & equity
$
276,719
Balance sheet as of Dec. 31, 2011
Cash
$
35,721
Accounts payable
$
47,325
Accounts receivable
21,732
Notes payable
20,796
Inventory
43,381
Current liabilities
$
68,121
Current assets
$
100,834
Long-term debt
$
116,334
Net fixed assets
$
248,625
Owners' equity
$
165,004
Total assets
$
349,459
Total liab. & equity
$
349,459
2010
2011
Operating cash flow
$
111,554
$
127,054
Capital Spending
Ending net fixed assets
$
248,625
- Beginning net fixed assets
204,068
+ Depreciation
52,282
Net capital spending
$
96,839
Change in Net Working Capital
Ending NWC
$
32,713
-Beginning NWC
11,819
Change in NWC
$
20,894
Cash Flow from Assets
Operating cash flow
$
127,054
- Net capital spending
96,839
-Change in NWC
20,894
Cash flow from assets
$
9,321
Cash Flow to Creditors
Interest paid
$
11,526
-Net New Borrowing
13,328
Cash flow to Creditors
$
(1,802)
Cash Flow to Stockholders
Dividends paid
$
31,623
-Net new equity raised
20,500
Cash flow to Stockholders
$
11,123
Chapter 3
Ratios and Financial Planning at S&S Air
Input area:
Tax rate
40%
Sales
$
36,599,300
COGS
26,669,496
Other expenses
4,641,000
Depreciation
1,640,200
EBIT
$
3,648,604
Interest
573,200
Taxable income
$
3,075,404
Taxes (40%)
1,230,162
Net income
$
1,845,242
Dividends
$
560,000
Add to RE
$
1,285,242
Assets
Liabilities
Current Assets
Current Liabilities
Cash
$
396,900
Accounts Payable
Accounts rec.
637,560
Notes Payable
Inventory
933,400
Total CL
Total CA
$
1,967,860
Long-term debt
Shareholder Equity
Fixed assets
Common stock
Net PP&E
$
15,411,620
Retained earnings
Total Equity
Total Assets
$
17,379,480
Total L&E
Output area:
Current ratio
0.71
Quick ratio
0.37
Cash ratio
0.14
Total asset turnover
2.11
Inventory turnover
28.57
Receivables turnover
57.41
Total debt ratio
0.45
Debt-equity ratio
0.82
Equity multiplier
1.82
Times interest earned
6.37
Cash coverage ratio
9.23
Profit margin
5.04%
Return on assets
10.62%
Return on equity
19.31%
s & Equity
$
844,550
1,928,500
$
2,773,050
$
5,050,000
$
322,500
9,233,930
$
9,556,430
$
17,379,480
Chapter 4
Planning for Growth at S&S Air
Input area:
Sales
$
36,599,300
COGS
26,669,496
Other expenses
4,641,000
Depreciation
1,640,200
EBIT
$
3,648,604
Interest
573,200
Taxable income
$
3,075,404
Taxes (40%)
1,230,162
Net income
$
1,845,242
Dividends
$
560,000
Add to RE
$
1,285,242
Assets
Liabilities & E
Current Assets
