Evolution of Average Cash Ratio by industry cash flow volatility increase

[Lars Peter]

Hi, i try to replicate a paper called "Why do US Firms hold so much more cash than they used to"... I´m struggling with replicating the grafic on page 1996 the task is to summarize the average cash-to-assets ratio for quintiles of industries sorted by increases in idiosyncratic risk.



What i got so far is:
* Cash Flow
gen cashflow=ebitda-xint-txt-dvc
* Cash Flow to Assets
gen cftassets=cashflow/at
winsor2 cftassets, cuts(1 100) by(year) replace

sort gvkey year cftassets
tostring sic, gen(sicstring)
gen sic2digit=substr(sicstring, 1,2)´
tsegen cfvolatility = rowsd(L(1/10).cftassets, 3)
bys sic2digit : egen industrysigma = mean(cfvolatility)
winsor2 industrysigma, cuts(1 99) by(year) replace

I don´t understand how to sort the industries by the increase of idiosyncratic risk over the sample period?
How do i calculate the increase of the risk over the sample period?

[Lars Peter]

Forgot to mention: I´m using Stata 14 on OSX, my dataset is based on COMPUSTAT Fundamental Annual Germany.