Using fiscal year(t-1) values rather than calendar year(t-1) values

[Jearlon Martina]

Dear Statalist Community,

I'm currently analysing abnormal returns using company financials.
In computing a needed leverage variable I encounter some trouble

* Example generated by -dataex-. For more info, type help dataex
clear
input float(date year) double(f_yr f_yr_end) float leverage double(dlc dltt tot_assets)
531 2004 2003 4  .4638955 1.293 27.411 61.876
543 2005 2004 4 .46489215 1.175 25.355 57.067
555 2006 2005 4  .4284869 1.167  21.29  52.41
567 2007 2006 4  .4560486 1.035 25.139 57.393
579 2008 2007 4  .3888294  .632  19.71 52.316
591 2009 2008 4  .4533853  .567 19.221 43.645
603 2010 2009 4 .15166536  .271   5.94 40.952
615 2011 2010 4 .23005947  .334    5.7 26.228
end
format %tm date
format %ty year

Where,
date = current month/year
year = current year
f_yr = current fiscal year
f_yr_end = current fiscal year ending month
dlc = short term debt
dltt = long term debt
tot_assets = total assets

The leverage values have currently been calculated as:

Leverage_k,t=(dlc_k,t-1+dltt)/tot_assets_k,t-1

However, they need to be calculated using the values of dlc, dltt and tot_assets in the previous fiscal year (f-1):

Leverage_k,t= (dlc_k,f-1+dltt_k,f-1)/tot_assets_k,f-1

for firm k in year t

How can I accomplish this?

Thank you in advance!

[Hemanshu Kumar]

I'm not sure I follow. Are you computing averages over a year, and then using the lag to denote the average for the previous year? Or are you computing this for each month separately, taking the 12 month lagged values in your formula? Could you give us the Stata code for exactly how leverage is being calculated right now?

[Jearlon Martina]

Hi Hemanshu,

gen leverage = (l.dlc+l.dltt)/l.tot_assets

This is the code for my leverage variable right now, but it is not what I want it to be.
It currently takes the 12 month lagged values of dlc, dltt, and tot_assets which is no issue for firms who's f_yr_end (fiscal year ending month) ends after month 12 (Dec), but in this case it ends after month 4 (April).
For instance, in observation #8, the current date (month/year) = 04/2011, but it is still in f_yr (fiscal year) 2010. In such a case I require leverage to be calculated over the fiscal year 2009 values of dlc, dltt, and tot_assets rather than the 2010 values it uses currently in the code given above

[Andrew Musau]

Why not simply tsset using the fiscal year?

tsset f_yr 
gen leverage = (l.dlc+l.dltt)/l.tot_assets

[Jearlon Martina]

Thank you Andrew!

One would think one could come up with such a simple solution themselves, but alas, I think the hours behind my desk are getting to me.
Your suggestion works perfectly for what I wanted to achieve, thank you once again.