Handling Accrual Based Inventory

[Tom Wilson]

I have a dataset where I am trying to calculate the total value of the assets a household may hold. Each observation concerns a particular asset-class of a house per given year. For example, TV's in 2004 for a particular household would be an observation. And TV's for that same household in 2005 would be another observation. Except in the case of the first year, I am never given the total value of the assets a household might own, but I do know the value of any assets gained or lost. Therefore using this data, I want to create a variable for each observation that displays the total value of the assets of that class that a household owns for a given year (after calculating the gains and losses).

Previously, when I needed to manipulate variables of an observation based on different variables of a different observation (thanks to the great advice on this board) I would use sort and use the index. While this has worked well for previous situations, I'm having a difficult time solving this problem through that technique.

So what are other well-known ways of tackling this type of problem in Stata? Thanks!

[Roberto Ferrer]

Assuming my own data structure (you give none), one example could be:

clear all
set more off

*----- example data -----

input ///
hhid year asset assval assvar
1 1998 1 120 .
1 1998 3 98 .
1 1999 1 . 65
1 1999 2 565 .
1 1999 3 0 .
2 1998 1 120 .
2 1998 3 98 .
2 1999 1 . 65
2 1999 3 0 .
2 1999 2 565 .
end

list, sepby(hhid year)

*----- what you want -----

gen combval = min(assval, assvar)
bysort hhid asset (year): gen currval = sum(combval)

list, sepby(hhid asset)

That's just a cumulative sum after doing some simple, previous work.

Asset value and asset (value) variation are in two different values to start with. Combine those in one variable, sort appropriately and then a running sum. The asset type is given by -asset-.

If that's not it, you need to give further details of your data.

[Tom Wilson]

Hmm I'm not sure if I understand your data structure, Roberto. Let me provide a simplified version of mine, and tell me if this is similar to yours:
Code:

*----- example data ----- input ///
hhid asset_class num_purchased value_purchased num_lost value_lost year
1 1 3 $150 0 . 1999
1 2 0 . 0 . 1999
2 1 0 . 0 . 1999
1 1 0 . 2 2000 end

In the above example, household 1 has purchased 3 quantities of asset-class 1 in 1999, for $150 total. In year 2000, household 1 loses 2 quantities of asset-class 1 for $90 total. So then perhaps the value of asset-class 1 would be 60*(1 - depreciation_constant) in year 2000. Does this make sense?

[Roberto Ferrer]

Same principle applies, only that losses are made negative to do the running sum:

clear all

*----- example data -----

input ///
hhid asset numpurch valpurch numlost vallost year
1 1 3 150 0 . 1999
1 2 0 . 0 . 1999
2 1 0 . 0 . 1999
1 1 0 . 2 90 2000
end

drop numpurch numlost // no need for this it seems
order year, after(asset)
list, sepby(hhid year)

*----- what you want -----

gen vallost2 = vallost*(-1)
gen combval = min(valpurch, vallost2)
bysort hhid asset (year): gen currval = sum(combval)

list, sepby(hhid asset)

Where

asset_class is asset
num_purchased is numpurch
value_purchased is valpurch
num_lost is numlost
value_lost is vallost

[Tom Wilson]

I am trying to parse your code and I have a few questions.

gen combval = min (valpurch, vallost2)

Why would combval not always be assigned to vallost2? Either vallost is 0, in which case it is the minimum, or it positive, in which case vallost2 is negative, and therefore also always the minimum.

And in the case where valpurch/vallost represent PER-UNIT costs, would the expression look as follows?:

gen combval = min(valpurch*num_purch, valllost2*num_lost)

[Roberto Ferrer]

Why would combval not always be assigned to vallost2? Either vallost is 0, in which case it is the minimum, or it positive, in which case vallost2 is negative, and therefore also always the minimum.

In the example, -vallost- is never zero. It is either positive or missing. Therefore, -vallost2- is negative or missing. On the other hand, -valpurch- is always positive or missing. The purpose of -min()- is to take whatever non-missing value there is among the two arguments, so they can be combined in one variable. The result can be seen clearly here:

     +---------------------------------------------------------------+
     | hhid   asset   year   valpurch   vallost   vallost2   combval |
     |---------------------------------------------------------------|
  1. |    1       1   1999        150         .          .       150 |
  2. |    1       2   1999          .         .          .         . |
  3. |    2       1   1999          .         .          .         . |
  4. |    1       1   2000          .        90        -90       -90 |
     +---------------------------------------------------------------+

You could take the -max()- as well, and get the same results. The important idea is that these functions will ignore the missings and consider only the relevant, non-missing values. If this still doesn't conform to your data structure, then you need to clarify that. See -help missing- if in doubt with Stata missings.

The strategy is quite simple: put all monetary amounts (starting asset values, purchases, and losses) in one variable, and add them up. Losses, of course, should be negative.

And in the case where valpurch/vallost represent PER-UNIT costs, would the expression look as follows?:

gen combval = min(valpurch*num_purch, valllost2*num_lost)

If the values -valpurch- and -vallost- are per unit, yes, you would need to adjust by quantities. It is my understanding that those values were totals already. I quote from post #3:

In the above example, household 1 has purchased 3 quantities of asset-class 1 in 1999, for $150 total. In year 2000, household 1 loses 2 quantities of asset-class 1 for $90 total.

[Tom Wilson]

But what about the case in years where assets are both lost and gained? Then there would two non-missing values. And you are correct, in my initial data structure, valpurch represented totals, but I do have datasets where this is not the case, and I was wondering how I would modify your code.

[Roberto Ferrer]

But what about the case in years where assets are both lost and gained?

Then you can use:

egen combval = rowtotal(valpurch vallost2)

instead of the -min()- function. -combval- will contain net values. See -help egen-. For example (changed one observation):

clear all

*----- example data -----

input ///
hhid asset numpurch valpurch numlost vallost year
1 1 3 150 0 . 1999
1 2 0 . 0 . 1999
2 1 0 . 0 . 1999
1 1 0 25 2 10 2000
end

drop numpurch numlost // no need for this it seems
order year, after(asset)
list, sepby(hhid year)

*----- what you want -----

gen vallost2 = vallost*(-1)
egen combval = rowtotal(valpurch vallost2)
bysort hhid asset (year): gen currval = sum(combval)

list, sepby(hhid asset)

This still assumes one observation per household/asset/year, so something like this doesn't occur in your database:

hhid asset numpurch valpurch numlost vallost year
1 1 0 . 2 90 2000
1 1 0 25 2 10 2000

All this speaks of the importance of providing a representative example to begin with.