Excel Solutions - Cases

Excel Solutions - Cases - Case Solutions Cases 1,2,4,5,7...

Info iconThis preview shows pages 1–6. Sign up to view the full content.

View Full Document Right Arrow Icon
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
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
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 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:
Background image of page 2
2004 Income Statement Sales $165,390 Cost of goods sold 84,310 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 Statement Sales $201,600 Cost of goods sold 106,450 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, 2004 Cash $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, 2005 Cash $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 2005 Operating cash flow $57,396 $65,374
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Capital Spending Ending net fixed assets $134,000 - Beginning net fixed assets 105,000 + Depreciation 26,900 Net capital spending $55,900 Change in Net Working Capital Ending NWC $19,406 -Beginning NWC 7,625 Change in NWC $11,781 Cash Flow from Assets Operating cash flow $65,374 - Net capital spending 55,900 -Change in NWC 11,781 Cash flow from assets $(2,307) Cash Flow to Creditors Interest paid $5,930 -Net New Borrowing 8,000 Cash flow to Creditors $(2,070) Cash Flow to Stockholders Dividends paid $9,763 -Net new equity raised 10,000 Cash flow to Stockholders $(237)
Background image of page 4
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 Current 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 $7,228,000 Shareholder Equity Common stock $100,000 Retained earnings $4,157,000 Total Equity $4,257,000
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 6
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 25

Excel Solutions - Cases - Case Solutions Cases 1,2,4,5,7...

This preview shows document pages 1 - 6. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online