Homework answers / question archive / 1) True or False? (5 pts) Shift functions are used to conduct identical operations on segments of a dataset

1) True or False? (5 pts) Shift functions are used to conduct identical operations on segments of a dataset

Computer Science

Share With

---

1) True or False? (5 pts) Shift functions are used to conduct identical operations on segments of a dataset.

If the statement is false, explain what shift functions are used for.

2. Large Firms and Profit Growth (20 pts). Both this exercise and the following one use the datasets compuannual clean.xlsx and topfirm id.xlsx. All data cleaning and quality checks have already been performed.
   1. 1. Load both datasets from above. The topfirm id dataset contains the identifiers of the 250 largest firms that have been active from 1990 to present. What are the names of the top 5 companies by asset size?

Use the pd.to datetime() function to convert the “datadate” column to a datetime object in the compuannual clean data.

Hint: As the main dataset is large it will take awhile to load (at least 1 minute). Don’t be alarmed by this.

1. 1. 2. Using the 6 digit CUSIPs from the topfirm id dataset, select all rows in the Compustat data that pertain to these 250 firms (each one of these firms will roughly have data from 1990 – 2019). Use the isin() operator. Store these data pertaining to the largest of firms, in a new data frame.^[1]
      3. Sort this data frame by “cusip6” and “fyear.” Within this new data frame, compute the growth in profits (“ebitda”) year over year, for each firm. You should not need to use a for loop. Profit growth at any point in time is computed as a percentage growth rate:

 

 

1. 1. 4. Compute three series by fiscal year: (1) median profit growth, (2) the 25 percentile value of profit growth and the (3) 75 percentile value. Each one of these series should have a data point each fiscal year. Use the groupby operator.
      5. Plot the three series from 1991 – 2019. Format the chart such that the interquartile range (25% through 75% values) shows up as a shaded grey area, while the median is a solid line on top.

Given our knowledge of past events, does the chart look reasonable?

2

3. Credit Conditions and Firm-Level Profits (15 pts).
   1. Start by loading in credspread.xlsx. You will notice that there are multiple columns with bond-data. We will be focused on “AvgCredSpread” which represents an average of the last 12 months of credit spread. This serves as a measure of aggregate credit conditions.
   2. Using the Compustat data frame from 2(c), pertaining to the largest firms, create columns for “Month” and “Year” using the command df name["datadate"].dt.month and ....dt.year.

Now merge this data frame with the one in part (a) above, using the appropriate columns.

1. 3. Our merged dataset now includes data on (i) firm-level profit growth and (ii) average credit spreads. Run the following regression and display its results:

?Profit_it = β₀ + β₁CS_t + ε_it

where ?Profit_it is year over year growth in profits for firm i at time t and CS_t is the average credit spread (“AvgCredSpread”).

If credit spreads increase 1%, what on average, happens to a firm’s profit growth that year?

Hint: Similar to the example from the live session, chop off observations that have profit growth in the top and bottom 1%.