76|EXCEL – SCF Analysis

Challenges
, , , , , ,
1

BYU Student Author: @Alex_Garrett
Reviewers: @Spencer, @Brett_Lowe
Estimated Time to Solve: 50 Minutes

We provide the solution to this challenge using:

  • Excel

Need a program? Click here.

Overview
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.

Challenge76_Image
Challenge76_Image1326×1024 159 KB

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.

Instructions
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.

  1. 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.”
  2. 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.
  3. 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.

Data Files

Suggestions and Hints
  1. Build a separate table for each company in both sheets that you create. Then, put the reporting dates across the top.
  2. Pay attention to what dollar amount each company is reporting in (millions, thousands, etc.).
  3. FCF = (Net cash from operating activities) – (Cash outflows for PPE purchases)

Solution

Challenge76_Solution.xlsx
Solution Video: Challenge 76|EXCEL – SCF Analysis

2

Challenge76_Datawheatley.xlsx (27.2 KB)
Here’s my solution. I will say mine looks a bit rougher than the example, and I classified a couple of things different. It was super interesting to learn about how to access the excel format of those 10ks!

9 Likes
3

Here’s my excel file for this problem. I mostly did the formatting differently, but the information is comparable!

Challenge76_Data_Jeffrey_Winters.xlsx (27.9 KB)

2 Likes
4

This was definitely a great review of conditional formatting! Thanks for the challenge!
Challenge76_Data.xlsx (24.4 KB)

5 Likes
5

Challenge76_AmyBlake_Solution.xlsx (27.0 KB)
This was a great challenge! I loved learning about extracting data and putting it in conditional formatting!

3 Likes
6

Here is my Excel file with the solutions. Thank you for the challenge!
Challenge76_Data_KeyyanLugo.xlsx (26.3 KB)

2 Likes
7

Here is my excel file with the solutions. Great challenge
Challenge76_Data.xlsx (27.1 KB)

2 Likes
8

Attached is my solution to this challenge. I appreciated learning about how to use the conditional coloring since that will be very helpful for organizing work books in the future.
Challenge76_Data.xlsx (30.3 KB)

3 Likes
9

Here is my data!
Challenge76_Data.xlsx (26.8 KB)

2 Likes
10

0301_Taylor_Challenge76_Data.xlsx (31.1 KB)

Here is my file with the solutions. It was really fun seeing everything work together to provide useful information. Deciding on the classifications took the most time for me.

4 Likes
11

This is my data. This was a great way to dust off some of the excel skills we’ve learned over the years at BYU.
Challenge76_Data_Micah_Nishimura.xlsx (27.5 KB)

2 Likes
12

Challenge76_Data (1).xlsx (21.6 KB)

13

Challenge76_Hunter_Auch.xlsx (26.6 KB)
Great challenge! I was able to learn how conditional formatting can make understanding information easier within a table!

2 Likes
14

This was a good challenge to help me review some Excel concepts such as conditional formatting as well as reviewing how to extract information from financial statements. My formatting differs slightly but the data should mostly match up with the solutions.
Challenge76_Data.xlsx (27.1 KB)

3 Likes
15

Here’s my solution.
Challenge76_Data.xlsx (27.8 KB)

1 Like
16

This is the solution I received from the problem. I found it a little tricky at first but got in the flow of things as I started working on it. Here is my solution!
Challenge76_Data (1).xlsx (28.1 KB)

17

This is my solution and excel file. I had a fun time getting back into the different functions of excel and learning conditional formatting. Thanks.
Challenge76_Data Skyler Beckstead.xlsx (29.0 KB)

3 Likes
18

Here is my solution! Thanks for the problem.
Challenge76_Data_Max Keyser_Solution.xlsx (27.5 KB)

19

Great challenge, thanks!
Challenge76_Data.xlsx (27.4 KB)

20

Here’s my results
Excel Challenge 76.xlsx (28.4 KB)