Closed cmgosnell closed 3 days ago
Okay @jrea-rmi now that #3309 is merged in and there we have (a draft!) of breaking down both the null in_rate_base tag and the utility_type
tag here are the update tables and outputs (plus a fun new Annual Sum of Rate Base by Rate Base Category graph)
out_ferc1__yearly_rate_base
:@cmgosnell This is looking pretty dang good. Here's what I see so far.
Checks that looked correct:
Checks that didn't match expectation
Questions for Catalyst:
Review still to do:
Major outstanding questions for RMI:
hey @jrea-rmi thanks for all of this!
cash_working_capital calculation didn't match what I expect. Duke Florida 2020 should be 2748792008/8 = 343,599,001 but it is 188,515,086.4 Duke Progress 2020 should be 2948863189/8 = 368,607,898 but it is 201,277,526.4 Consumers 2014 should be 2938149878/8 = 367,268,734.8 but it is 180,603,569 the ratio for each of these is 1.82, 1.83, 2.0. Is there a fraction being applied to records with is_breakdown_tags_in_rate_base = True?
Yes! This is_breakdown_tags_in_rate_base
indicates that this record got broken up between in rate base and not in rate base bc it wasn't labeled w/ tags_in_rate_base == "yes"
which is silly because we are building that factoid from semi-scratch. I will add the tags_in_rate_base == "yes"
into the creation of the cash_working_capital
factoid and this breakdown will no longer be applied! good catch!
should records with null ending_balance be removed?
I certainly could remove them! That seems like a fine idea especially at this stage.
only a few corrections have non-null tags_rate_base_category. At least some of these cases could be labeled - for example, xbrl_factoid "distribution_plant_correction" for utility_id_ferc1 230 in year 2021 could be labeled as tags_rate_base_category="distribution". Should other corrections have tags_rate_base_category="other"?
All of the corrections got the same tag as their parent fact. So this distribution_plant_correction
is correction the distribution_plant
xbrl_factoid's calculation and the correction inherited all of the tags and attributes of the parent. We could either add tags for all the corrections (or just add em to their parents) or we could attempt to propagate the existing tags. We are currently propagating the just the tags_in_rate_base
tag to any child facts or parent facts which have children that all share the same tag. I wasn't sure if we should do this tag propagation for the other tags. I'll pull out a list of all of the unlabeled corrections to see if that will be easier to tag and go poke at just applying the tag propagation to other tags to see how challenging & effective it would be.
current non-correction xbrl_factoid
's with null tags_rate_base_category
:
['amortization_of_underground_storage_land_and_land_rights_utility_plant_in_service', 'amortization_and_depletion_of_producing_natural_gas_land_and_land_rights_utility_plant_in_service', 'gas_stored_underground_noncurrent']
current correction xbrl_factoid
's with null tags_rate_base_category
(there are 26):
['steam_production_plant_correction', 'general_plant_correction', 'distribution_plant_correction', 'nuclear_production_plant_correction', 'electric_plant_in_service_and_completed_construction_not_classified_electric_correction', 'depreciation_utility_plant_in_service_off_by_amortization_of_other_utility_plant_utility_plant_in_service_correction', 'transmission_plant_correction', 'hydraulic_production_plant_correction', 'intangible_plant_correction', 'general_plant_excluding_other_tangible_property_and_asset_retirement_costs_for_general_plant_correction', 'other_production_plant_correction', 'production_plant_correction', 'accumulated_depreciation_subdimension_correction', 'depreciation_utility_plant_in_service_subdimension_correction', 'deferred_credits_correction', 'utility_plant_net_correction', 'utility_plant_net_subdimension_correction', 'accumulated_provision_for_depreciation_amortization_and_depletion_of_plant_utility_subdimension_correction', 'assets_and_other_debits_correction', 'accumulated_provision_for_depreciation_amortization_and_depletion_of_plant_utility_correction', 'deferred_debits_correction', 'utility_plant_and_construction_work_in_progress_subdimension_correction', 'current_and_accrued_assets_correction', 'utility_plant_and_construction_work_in_progress_correction', 'liabilities_and_other_credits_correction', 'current_and_accrued_liabilities_correction']
update: i did a quick check to see if i could add in the tag propagation for the rate_base_category
and it worked like a charm. distribution_plant_correction
did end up with a distribution
tag, but there are still 15 correction factoids w/o categories:
['accumulated_depreciation_subdimension_correction', 'depreciation_utility_plant_in_service_off_by_amortization_of_other_utility_plant_utility_plant_in_service_correction', 'electric_plant_in_service_and_completed_construction_not_classified_electric_correction', 'production_plant_correction', 'depreciation_utility_plant_in_service_subdimension_correction', 'deferred_credits_correction', 'utility_plant_net_correction', 'utility_plant_and_construction_work_in_progress_correction', 'accumulated_provision_for_depreciation_amortization_and_depletion_of_plant_utility_subdimension_correction', 'deferred_debits_correction', 'utility_plant_net_subdimension_correction', 'assets_and_other_debits_correction', 'accumulated_provision_for_depreciation_amortization_and_depletion_of_plant_utility_correction', 'utility_plant_and_construction_work_in_progress_subdimension_correction', 'liabilities_and_other_credits_correction']
I could add category tags of "other" for all of these, but let me know if there are any of these that you want something more specific.
what's the purpose of the column "is_breakdown_tags_in_rate_base"? All cases that are True are either cash_working_capital or corrections. Why is cash_working_capital labeled True here? Are these correction records cases where part of the correction corresponds to something in rate base, and part corresponds to something out of rate base? If so, can we estimate the amount of that correction that's in rate base, using fraction of that xbrl_factoid that's in rate base?
You've got the right idea here generally. The two is_breakdown_*
columns indicates whether or not an xbrl_factoid as originally reported is being broken down either as in rate base or not in rate base or broken down from utility_type total into various non-total/non-null utility types #3532. It makes sense that only the corrections have this in_rate_base breakdown because you gave me a pretty complete tagging on the most granular xbrl_factoids and the correction records are always generated from factoids that are less granular.
Sounds good on most of these!
The 3 current non-correction xbrl_factoids
with null tags_rate_base_category
have tags_aggregatable_utility_type = gas
. We never comprehensively made tags_rate_base_category
for gas rate base. So open to suggestion. With a quick review, it seems like these 3 facts could all get a tag of net_utility_plant
.
Here are my suggested tags for the 15 correction factoids without categories.
{
'utility_plant_net_correction': 'utility_plant_correction',
'utility_plant_net_subdimension_correction': 'utility_plant_correction',
'accumulated_depreciation_subdimension_correction': 'utility_plant_correction',
'depreciation_utility_plant_in_service_off_by_amortization_of_other_utility_plant_utility_plant_in_service_correction': 'utility_plant_correction',
'electric_plant_in_service_and_completed_construction_not_classified_electric_correction': 'utility_plant_correction',
'production_plant_correction': 'utility_plant_correction',
'depreciation_utility_plant_in_service_subdimension_correction': 'utility_plant_correction',
'utility_plant_and_construction_work_in_progress_correction': 'utility_plant_correction',
'accumulated_provision_for_depreciation_amortization_and_depletion_of_plant_utility_subdimension_correction': 'utility_plant_correction',
'accumulated_provision_for_depreciation_amortization_and_depletion_of_plant_utility_correction': 'utility_plant_correction', 'utility_plant_and_construction_work_in_progress_subdimension_correction': 'utility_plant_correction',
'deferred_debits_correction': 'other_deferred_debits_and_credits',
'deferred_credits_correction': 'other_deferred_debits_and_credits',
'assets_and_other_debits_correction': 'correction',
'liabilities_and_other_credits_correction': 'correction'
}
The outstanding question I have is the one on cases where is_breakdown_tags_in_rate_base=True
. If these are all corrections, and part of that line is in rate base and part is not, it's hard to tell whether we should include some value in our rate base estimate. A default could be to exclude from rate base - so take them out of this rate base table, though they'd stay in the balance sheet breakdown table. An upgrade to that default would be if we can find out for that given utility, if all components within the xbrl_factoid
that is being corrected are in rate base or not, then the correction should be labeled as fully in or out of rate base. A questionable case would be if the xbrl_factoid
being corrected does really have mixed in rate base or not components, and we could calculate the fraction of that correction that should be in rate base.
That may also have implications for the 15 correction tags I listed above. Considering the last 4, I think all of these would either be simply excluded from rate base or we calculate the fraction of them that should be included.
hey @jrea-rmi !! thank you for this suggestions and tags. I would suggest adding the tags w/o the _correction
suffix if that is okay with you. Mostly because right now we don't add correction into any of the tags. We could! but i would want to do it across the board.
I appreciate the extra insight into the is_breakdown_tags_in_rate_base
break down. For all/any of these we can directly tag them as in or out of rate base for sure so I will add "no"s for those last four! These correction records are the corrections for the leafy/granular records across the detail tables that feed into this rate base table but yea i think you are totall right that these broken down corrections are not necessarily connected to the in_rate_base==True
xbrl_factoids. we could check weather factoid x has any children in the tree that are in_rate_base==True
. but there aren't that many of these so I would suggest just id-ing them like you just did here with these last four because adding those tags will be very straightforward!
okay @jrea-rmi i tagged the last four facts as not in rate base and because we propagate tags to related facts when all other children or parent facts get the same in_rate_base tag most of these is_breakdown_tags_in_rate_base
corrections got automatically tagged as not in rate base! so your hunch on all these was spot on.
Here is a new version of the table:
Right now (w/ that new tag addition and dropping all null ending_balance
) there are only two xbrl_factoids
that are is_break_down_tags_in_rate_base==True
:
out_ferc1__yearly_rate_base[
out_ferc1__yearly_rate_base.is_break_down_tags_in_rate_base==True
].xbrl_factoid.value_counts()
xbrl_factoid
current_and_accrued_liabilities_correction 94
liabilities_and_other_credits_correction 4
Name: count, dtype: Int64
okay @jrea-rmi i did a tiny bit of digging into these two remaining partially in rate base correction factoids. From my vantage it seems like these are truly partially in rate base but I'd love to get your opinion!
liabilities_and_other_credits_correction
(xbrl link)this is the correction record for the seed node of the liabilities graph (which is to say it is the least granular/total of all of the other totals/calculated fields. So these 4 correction records represent some error found between the liabilities' seed node and the first layer of calculation sub-components. since there are definitely liabilities in the rate base table overall it would seem to make sense that these corrections should be partially included.
current_and_accrued_liabilities_correction
(xbrl_link)this guy is the correction record one of the calculated fields in first layer of the liabilities graph. Since you originally gave me in_rate_base
labels for all of the leaves/most granular factoids of the graph, i can see that current_and_accrued_liabilities
has three sub-components that are tagged as in_rate_base=="yes"
( tax_collections_payable
, customer_deposits
and taxes_accrued
).
tags_rate_base_category
gas_stored_underground_noncurrent
(xbrl link is a component of assets_and_other_debts
and is a leafy/granular factoid but it does not have a tags_rate_base_category
. It's not a huge deal - there are only 350 records with any ending_balance
in there from 31 utilities but it'd be nice to have them all tagged!
tags_rate_base_category
for this? nbd if you don't.so cool, glad you pushed this ahead!
One of the outstanding tag questions on our side was with ARCs/AROs. We still have some questions on how these are accounted for, and it might unfortunately involve components of regulatory assets and liabilities, which are messy free-form tables.
So sorry for late review here, but for now, until we have time to embark on an accounting project, we think it makes sense to change back to
yes, asset retirement obligations are in rate base, with tags_rate_base_category
of AROs
or asset_retirement_obligations
or another naming convention you like.
yes, asset retirement costs are in rate base (that's the way you currently have it), but with tags_rate_base_category
of asset_retirement_costs
rather than with tags_rate_base_category
matching tags_aggregatable_plant_function
Hi @jrea-rmi! Just wanted to clarify --
yes, asset retirement costs are in rate base (that's the way you currently have it), but with tags_rate_base_category of asset_retirement_costs rather than with tags_rate_base_category matching tags_aggregatable_plant_function
By this do you mean replace the steam
, nuclear
, hydro
etc. rate base category tags with asset_retirement_costs
for all of the asset_retirement_costs_for_XYZ
factoids?
Hi @jrea-rmi! Just wanted to clarify --
yes, asset retirement costs are in rate base (that's the way you currently have it), but with tags_rate_base_category of asset_retirement_costs rather than with tags_rate_base_category matching tags_aggregatable_plant_function
By this do you mean replace the
steam
,nuclear
,hydro
etc. rate base category tags withasset_retirement_costs
for all of theasset_retirement_costs_for_XYZ
factoids?
yes. Uday likes to consider these separate from the technology because of how they're accounted for and their ratepayer impacts. So that's how we'd use it - but if you feel strongly otherwise, or think other users want to label as the technology, then we can just change the label in our own processing after getting it from you.
okay @jrea-rmi i did a tiny bit of digging into these two remaining partially in rate base correction factoids. From my vantage it seems like these are truly partially in rate base but I'd love to get your opinion!
liabilities_and_other_credits_correction
(xbrl link)this is the correction record for the seed node of the liabilities graph (which is to say it is the least granular/total of all of the other totals/calculated fields. So these 4 correction records represent some error found between the liabilities' seed node and the first layer of calculation sub-components. since there are definitely liabilities in the rate base table overall it would seem to make sense that these corrections should be partially included.
- Proposal: keep this correction as is - partially in rate base.
current_and_accrued_liabilities_correction
(xbrl_link)this guy is the correction record one of the calculated fields in first layer of the liabilities graph. Since you originally gave me
in_rate_base
labels for all of the leaves/most granular factoids of the graph, i can see thatcurrent_and_accrued_liabilities
has three sub-components that are tagged asin_rate_base=="yes"
(tax_collections_payable
,customer_deposits
andtaxes_accrued
).
- Proposal: keep this correction as is - partially in rate base.
one last null
tags_rate_base_category
gas_stored_underground_noncurrent
(xbrl link is a component ofassets_and_other_debts
and is a leafy/granular factoid but it does not have atags_rate_base_category
. It's not a huge deal - there are only 350 records with anyending_balance
in there from 31 utilities but it'd be nice to have them all tagged!
- Request: Do you have a
tags_rate_base_category
for this? nbd if you don't.
Sorry for delayed response on this. My answers are...
I agree, yes, these two corrections appear to be partially in rate base. And it's difficult to determine what fraction, because we don't know what the source of the discrepancy is between subtotal & total values. If you think of a way to estimate it that seems legitimate, that's great. If not, RMI will probably just leave it out when we use the table.
We don't have a tags_rate_base_category
for this, because it's relevant to gas utility operations, not electric. But it's in the balance sheet section of "Utility Plant", so I suggest labeling as net_utility_plant
.
Hi again @jrea-rmi let's do this review again! Follow up #3479
This that are still in progress:
3309
3530 (the files below have a first draft of breaking down the utility type but not the null rate base tags.. progress in #3532)
Jon, if you'd rather wait until those two things are wrapped up just let us know!
Files:
Plotz
by input table & utility