FIN 300 Lecture Notes - Lecture 3: Inventory Turnover, Asset Turnover, Reserve Requirement
Document Summary
Get access
Related Documents
Related Questions
Excel Online Structured Activity: Statement of cash flows
You have just been hired as a financial analyst for BarringtonIndustries. Unfortunately, company headquarters (where all of thefirm's records are kept) has been destroyed by fire. So, your firstjob will be to recreate the firm's cash flow statement for the yearjust ended. The firm had $100,000 in the bank at the end of theprior year, and its working capital accounts except cash remainedconstant during the year. It earned $5 million in net income duringthe year but paid $700,000 in dividends to common shareholders.Throughout the year, the firm purchased $5.4 million of machinerythat was needed for a new project. You have just spoken to thefirm's accountants and learned that annual depreciation expense forthe year is $450,000; however, the purchase price for the machineryrepresents additions to property, plant, and equipment beforedepreciation. Finally, you have determined that the only financingdone by the firm was to issue long-term debt of $1 million at a 7%interest rate. The data has been collected in the Microsoft ExcelOnline file below. Open the spreadsheet and perform the requiredanalysis to answer the question below.
Statement of cash flows | |||
Cash at the Beginning of Year | $100,000.00 | ||
Change in Net Operating Working Capital Except Cash | $0.00 | ||
Net Income | $5,000,000.00 | ||
Common Dividends | $700,000.00 | ||
Machinery Equipment Purchases | $5,400,000.00 | ||
Depreciation Expense | $450,000.00 | ||
Long-Term Debt | $1,000,000.00 | ||
Interest Rate on Long-Term Debt | 7.00% | ||
Statement of Cash Flows | |||
I. Operating Activities | Formulas | ||
Net income | #N/A | ||
Depreciation expense | #N/A | ||
Change in net operating working capital exceptcash | $0.00 | ||
Net cash provided by (used in) operations | #N/A | ||
II. Long-Term Investing Activities | |||
Additions to property, plant and equipment | #N/A | ||
Net cash used in investing activities | #N/A | ||
III. Financing Activities | |||
Increase in long-term debt | #N/A | ||
Payment of common dividends | #N/A | ||
Net cash provided by financing activities | #N/A | ||
IV. Summary | |||
Net increase (decrease) in cash | #N/A | ||
Cash at beginning of year | $100,000.00 | ||
Cash at end of year | #N/A |
What was the firm's end-of-year cash balance? Recreate thefirm's cash flow statement to arrive at your answer. Write out youranswer completely. For example, 5 million should be entered as5,000,000. Round your answer to the nearest dollar, ifnecessary.
Avnet is one of the worldâs largest value-added distributors of electronic components, enterprise computer and storage products, IT services and embedded subsystems. Avnet creates a vital link in the technology supply chain that connects the worldâs leading electronic component and computer product manufacturers and software developers with a global customer base of original equipment manufacturers, electronic manufacturing services providers, original design manufacturers, and value-added resellers.8
8 Extracted from 10-K filings for Avnet Inc., 2013. Obtained from U.S. Securities and Exchange Commission. www.sec.gov.
Avnet, Inc. and Subsidiaries Consolidated Statements of Cash Flows | |||
---|---|---|---|
Years Ended | |||
June 29, 2013 | June 30, 2012 | July 2, 2011 | |
(Thousands) | |||
Cash flows from operating activities: | |||
Net income | $ 450,073 | $ 567,019 | $ 669,069 |
Non-cash and other reconciling items: | |||
Depreciation and amortization | 120,676 | 101,336 | 81,389 |
Deferred income taxes (Note 9) | (10,019) | 11,782 | 15,966 |
Stock-based compensation (Note 12) | 43,677 | 35,737 | 28,931 |
Gain on bargain purchase and other (Note 2) | (31,011) | (2,918) | (22,715) |
Other, net (Note 15) | 75,327 | 66,263 | 56,846 |
Changes in (net of effects from businesses acquired): | |||
Receivables | (94,203) | 72,267 | (421,457) |
Inventories | 225,667 | 133,178 | (321,939) |
Accounts payable | (78,834) | (319,094) | 165,185 |
Accrued expenses and other, net | (5,156) | (136,852) | 26,804 |
Net cash flows provided by operating activities | 696,197 | 528,718 | 278,079 |
Cash flows from financing activities: | |||
(Repayments of) borrowings under accounts receivable securitization program, net (Note 3) | (310,000) | 510,000 | 160,000 |
Issuance of notes in a public offering, net of issuance costs (Note 7) | 349,258 | â | â |
Repayment of notes (Note 7) | â | â | (109,600) |
(Repayments of) proceeds from bank debt, net (Note 7) | (179,861) | 86,823 | 1,644 |
(Repayments of) proceeds from other debt, net (Note 7) | (1,080) | (1,007) | 7,238 |
Repurchases of common stock (Note 4) | (207,192) | (318,333) | â |
Other, net (Note 12) | 4,792 | 5,590 | 3,930 |
Net cash flows (used for) provided by financing activities | (344,083) | 283,073 | 63,212 |
Cash flows from investing activities: | |||
Purchases of property, plant and equipment | (97,379) | (128,652) | (148,707) |
Cash proceeds from sales of property, plant, and equipment | 3,018 | 1,046 | 10,621 |
Acquisitions of operations and investments, net of cash acquired (Note 2) | (262,306) | (313,218) | (690,997) |
Cash proceeds from divestiture activities (Note 2) | 3,613 | â | 19,108 |
Net cash flows used for investing activities | (353,054) | (440,824) | (809,975) |
Effect of exchange rate changes on cash and cash equivalents | 3,419 | (39,437) | 51,916 |
Cash and cash equivalents: | |||
â increase (decrease) | 2,479 | 331,530 | (416,768) |
â at beginning of year | 1,006,864 | 675,334 | 1,092,102 |
â at end of year | $ 1,009,343 | $ 1,006,864 | $ 675,334 |
Additional cash flow information (Note 15) See notes to consolidated financial statements. |
Required:
Using the Consolidated Statements of Cash Flows for Avnet, prepare a summary analysis for all three years.
Write an analysis and interpretation of the cash flows for Avnet for all three years. Be sure to analyze the cash flows from operating activities, as well as the overall cash inflows and outflows for the firm.
Evaluate the credit worthiness of Avnet based on only the cash flow statements.
What would information from the balance sheet be useful to a creditor in determining whether to loan Avnet money?