We provide the solution to this challenge using:
Need a program? Click here.
Every public company in the United States is required to file an annual report of its financial statements with the SEC. These reports are publicly available, which help investors make informed decisions and help professionals develop their understanding of financial statements. One of these statements is the Statement of Cash Flows. This financial statement describes the change in cash within a business for a given period. Using information from the Statement of Cash Flows, we can analyze the performance and financial health of a company.
Cash inflows and outflows can be analyzed to estimate where a company operates in the business life cycle. Generally, young growing companies generate negative operating and investing cash flows and positive financing cash flows until they make enough money to finance their own business operations. Once this happens, these companies will typically generate positive operating and investing cash flows and negative financing cash flows. See Figure 1. Source: Financial Accounting, 6th Edition, 2022, David Spiceland, et al.
Free Cash Flow (FCF) is a metric that can be calculated from the Statement of Cash Flows to help determine a company’s financial health. FCF is calculated as cash flow from operating activities less capital expenditures (cash spent for PPE). The remainder, if any, represents “free” cash that can be used after supporting operating activities and maintaining capital assets. A negative FCF is an indicator of poor financial health.
Provided is an excel workbook with the Statement of Cash Flows for 4 companies. The information was pulled from each company’s most recent 10-K filing at the time this challenge was created. Click here to learn how to get this information for yourself. NOTE: you will need to know how to do this in a subsequent part of the challenge.
- Create a new sheet called “Analysis” and show the operating, investing, and financing cash inflows/outflows for each reporting period separated by company. Add the exchange rate effect to your tables to calculate the total change in cash. Use conditional formatting to differentiate between inflows and outflows of cash for the operating, investing, and financing cash flows. Then, use the chart above to classify each company as “Introductory,” “Growth,” “Maturity,” or “Decline.”
- Create a new sheet called “FCF” and calculate the Free Cash Flow for each reporting period separated by company. Use conditional formatting to differentiate between positive and negative FCF.
- Follow the steps in the YouTube video above to collect and import data for another company of your choice. Add the company’s data to your “Analysis” and “FCF” sheets.
Suggestions and Hints
- Build a separate table for each company in both sheets that you create. Then, put the reporting dates across the top.
- Pay attention to what dollar amount each company is reporting in (millions, thousands, etc.).
- FCF = (Net cash from operating activities) – (Cash outflows for PPE purchases)