Simple scatter that shows mean of earnings at years t-3,t-2,t-1, t , t+1,t+2,t+3 where (t) is the event year ?

[Mike Kraft]

Dear All

I am rephrasing a post sent few days ago and had no response. I also attach a paper that shows a similar graph:

I have annual data with firm id(id) and year(yr) , an earnings variable (earnings); and another variable (writeoff) which has a value when the firm records a write off ( a type of expenses) and zero when there is no write off.

I want to have a graph that shows the mean of the earnings variable inter-temporally i.e. before and after the write off event.


Specifically, I want to visualize mean earnings over three years before and after the firm records write-off, using a two-way scatter with connected lines such as :

twoway(scatter mean_writeoff yr, connect(1) sort)


On the Y axis , I should have the mean earnings values , and on the X axis, I should have t-3, t-2, t-1 , t , t+1 , t+2, t+3 , where year t is the yer when the firm recorded a write off.

I calculated the mean value as :

sum writeoff
egen mean_writeoff=mean(writeoff) if writeoff !=0, by(yr)

I am not sure how to proceed to get the results I wanted....
I attach two paper Johnson et al 2011 where graph (3) has the exact idea.

Any help ?

Regards

[William Lisowski]

It seems to me you need to proceed roughly as follows. This is based on the assumption that each firm experiences exactly one writeoff, but shows the basic idea. Not tested, so just take this as a possible starting point.

1) by firm, generate a new variable T0 that has the year that firm took the writeoff; this will have the same value in every observation for the firm
2) generate a new variable T that is yr-T0 so it takes the values -3, -2, -1, 0, 1, 2, 3: time relative to the writeoff year
3) by T, generate a new variable ME that has the mean earnings; this will have the same value in every observation for a given value of T
4) use egen tag to select one observation for each value of T
5) plot ME against T for the tagged observations

[Nick Cox]

You did get a response to your previous post, but to the effect that the question was not clear enough.

This helps, but leaves some things still unclear.

Assuming that each firm experiences at most one write-off event, then

egen eventyear = max(cond(writeoff != 0, year, .)) , by(id)
gen timesinceevent = year - eventyear

You are then doing calculations for distinct values of the second new variable, using egen for example:

egen mean = mean(earnings), by(timesinceevent)
twoway connected mean timesinceevent if inrange(timesincevent, -3,3)

If each firm can experience one than one write-off event, then what are the rules? For example, a given year could be 2 years after one event and 3 years before the next. This may be standard stuff in your field, but for everyone else the rules need to be explained.

[Mike Kraft]

Hi Nick
I have tried your code, but the last line of the code produces the following error message:
inrange not found r(111)

I am using Stata 11, does this have anything with that ?
Thanks

[Nick Cox]

No; inrange() was added in Stata 7: see e.g. http://www.stata-journal.com/sjpdf.h...iclenum=dm0026

So, I guess something is wrong with the syntax you actually used, which we can't see yet.

There's a typo in my hypothetical code: timesincevent is not the name of the variable created, but a reference to that should provoke a different error message.

But in general you should certainly flag that you are using an old version of Stata (FAQ Advice makes this suggestion).

[Mike Kraft]

Thanks Nick for a quick response;

It works if I change it to:
twoway (scatter yy timesinceevent if inrange(timesinceevent, -3, 3)
Can't use connect(1) as it connects all values by all possible connections...Not sure how to connect the means using one line...

Nick, I think your code for mean earnings will calculate mean earnings for each year before and after the event year and including the event year. How can I adjust the calculation of mean to show only the mean of earnings for firms that reported a write off . I think you calculate mean of earnings for the full sample including those who do not have write off. Therefore, when I plot mean earnings before and after the write off, it doesn't seem to be consistent with intuition..

I tried to use :
egen mean=mean(earnings) if writeoff !=0, by(timesincevent)

However when I run the last code to produce the graph, the resulting graph does not show mean earnings after the event year..

[Nick Cox]

See post #3 for the suggestion of twoway connected. You may well need a sort option too.

I don't really understand your set-up (and I don't think you have answered my question about multiple write-offs), but the intention of my code was that the variable eventyear, and so the variable timesinceevent, would be missing if there was no write-off event. In short, firms without write-offs should be ignored.

None of us reading your posts can see real (or realistic) data to experiment with here, nor we can see printed results, nor graphs. So,much is on the level of "seems to be wrong". You could well be right, but I can't diagnose the problem if there is one. It's your job to provide sandbox data, please, if our guesses at code are wrong, or not easy to follow. We just need a sandbox with about three firms, two with write-offs and one not, and about 10 years of data in each case. You should be able to indicate by hand what the correct results should be.

See also FAQ Advice Section 12 on advice on posting questions.

[Robert Picard]

Perhaps this is closer to what you are looking for

* --------- data setup
set seed 12345
clear
set obs 5
gen firm = _n
gen earnings = runiform() * 1000
expand 15
bysort firm: gen year = 2000 + _n
by firm: replace earnings = earnings + runiform() * 10 + _n
gen writeoff = cond(runiform() < .1, runiform() * 100,0)
tsset firm year
* --------- end of data setup

* pre-compute earnings over a 7-year window for each observation
gen e1 = L3.earnings
gen e2 = L2.earnings
gen e3 = L1.earnings
gen e4 = earnings
gen e5 = F1.earnings
gen e6 = F2.earnings
gen e7 = F3.earnings

keep if writeoff > 0 & !mi(writeoff)
collapse (mean) e1-e7

gen dummy = 1
reshape long e, i(dummy) j(x)
replace x = x - 4
twoway connected e x

[Mike Kraft]

Robert
I think your code will show the mean of earnings for the subsample of firms that have actually reported write off over the period from L3 to F3. if I understand your code correctly (keep if writeoff>0) will limit the sample to writeoff firms only. This is not the aim though, because as I mentioned earlier I wanted to see if the mean of earnings would be lower when the firm report a write of this year comparing to 3 years before and after...
Any suggestions ?

[Mike Kraft]

To clarify;
Sample firms are either reporting write off or not. Writeoff is a continuous variable that has a value when firms report writeoff, and zero when firms do not report write off (no missing values).
The aim is to show how earnings, on average, are different before and after write off. I would expect that earnings will be lower in years t+1 to t+3 when the write off is at year t, and I do not have prediction for years before the write off. This is consistent with firms reporting write off at the current period and have lower earnings in next periods.
I am not sure if Robert's code is designed to show this inter-temporal differences as per the paper attached in my original post or not ? A firm can report a write off this year and have zero write off in prior of future periods in the sample. The objective is to show if earnings will be different ot the year of write off, and before and after the writeoff, on average !

Pages 527 (explains exactly what i need to do. It uses the wording special items instead of write off) and page 528 show the graphs!