Check and debug inter-table and inter-dimensional calculations for both exploded tables

[e-belfer]

In #2669 we added inter-table and inter-dimensional inter-table calculations into the process of the table explosion. We should look at these calculations and debug signs, component names etc. to improve calculation accuracy.

Tasks

Beta Give feedback

1. Identify troublesome calcs
2. For each calculation: identify components, look for sign errors, look for missing components
3. add a special calculation correction when a sizable minority of utilities report in a non-standard way #3064
   4 of 4
   data-repair ferc1 rmi +3
   

• Update accumulated_depreciation component to in_service plant status for depreciation_utility_plant_in_service
• add total plant_status for construction_work_in_progress link between balance_sheet_assets_ferc1 and utility_plant_summary_ferc1
• add total plant_status for the accumulated_provision_for_depreciation_amortization_and_depletion_of_plant_utility link between balance_sheet_assets_ferc1 and utility_plant_summary_ferc1

[e-belfer]

First round of investigations from August:
Balance sheet assets
depreciation_utility_plant_in_service_correction: 3873

• 3026 of these have a relative difference of 1 (i.e. the component is duplicated).
• Fixed plant status on component to "in service" - reduced to 3841 wrong.

utility_plant_in_service_classified_and_property_under_capital_leases_correction: 3530

• The relevant sub-dimensional factoid here is electric_plant_in_service_and_completed_construction_not_classified_electric from the plant_in_service_ferc1 table. As discussed in Identify and link inter-table relationships when they occur within a dimension (e.g. utility_type) #2669, this is a result of issues with the Account 101/106 breakdown of the Electric Plant in Service table.

utility_plant_net_correction: 2353

• 1:1 link from balance sheet assets to utility plant summary tables to remove duplicated calculation. There isn't a utility type for the parent factoid in the calculation components table, perhaps this is causing issues?

accumulated_provision_for_depreciation_amortization_and_depletion_of_plant_utility_correction: 2077

• 1:1 link from balance sheet assets to utility plant summary tables to remove duplicated calculation. There isn't a utility type for the parent factoid in the calculation components table, perhaps this is causing issues?

utility_plant_and_construction_work_in_progress: 2061

• 1:1 link from balance sheet assets to utility plant summary tables to remove duplicated calculation. There isn't a utility type for the parent factoid in the calculation components table, perhaps this is causing issues?

construction_work_in_progress_correction: 1904

• 1:1 link from balance sheet assets to utility plant summary tables to remove duplicated calculation. There isn't a utility type for the parent factoid in the calculation components table, perhaps this is causing issues?
• Added total to child factoid dimensions, to match other parallel examples.

Balance sheet liabilities
retained_earnings_correction: 159

• We're comparing retained earnings from Current Year End of Quarter/Year Balance (Balance sheet liabilities table) and Current Quarter/Year Year to Date Balance (Retained earnings table). Might this have something to do with the differences?

Income statement
General observations: errors are relatively distributed across years and utilities.

operation_expense_correction: 3061

• Tried removing power_production_expenses from sub-components (increases wrong calcs to 3410). This component sums operation + maintenance costs from all sub-sections and should be removed.

maintenance_expense_correction: 358

• Manually reviewed all components, no obvious source of error.
• Often appears just to match one maintenance component and not the other (e.g. just general maintenance, just distribution maintenance), so this is more likely user error than error on our end.

other_operating_revenues_correction: 217
depreciation_expense_correction: 163
administrative_and_general_expenses_correction: 102

[cmgosnell]

Balance sheet asset explosion inter-table calc debugging notes:

Starting w/ this error rate:

balance_sheet_assets_ferc1: has 12015 (59.92%) records whose calculations don't match. Adding correction records to make calculations match reported values.

High level # of factoids in the inter-table reported factoids that are off from their calculated value from their sub-components:

xbrl_factoid
utility_plant_in_service_classified_and_property_under_capital_leases                 3741
utility_plant_net                                                                     2106
accumulated_provision_for_depreciation_amortization_and_depletion_of_plant_utility    2080
utility_plant_and_construction_work_in_progress                                       2073
construction_work_in_progress                                                         1906
depreciation_utility_plant_in_service                                                  488
utility_plant_in_service_plant_purchased_or_sold                                        35
Name: count, dtype: Int64

^^ i removed utility_plant_in_service_classified_and_property_under_capital_leases for reasons listed below

utility_plant_in_service_classified_and_property_under_capital_leases

In trying my little debugging test on the calcs that checks whether the absolute difference between the reported and calculated values are the exact same value of other components I learned that the vast majority (3167/3741) of the errors in utility_plant_in_service_classified_and_property_under_capital_leases are because of the error that Jon id-ed when we were first starting this journey: many utilities include utility_plant_in_service_completed_construction_not_classified in utility_plant_in_service_classified_and_property_under_capital_leases. This happens across all years. It doesn't totally make sense because the sub-component oft included is specifically "not-classified". I. tried adding this into the mannual calc fixes and it caused a 67% error rate instead of the existing 59%.

this accounts for 24% of the errors in the balance sheet asset explosion.

another 680 records are of a similar fashion but with a smaller subset of records (~600) including utility_plant_in_service_property_under_capital_leases

depreciation_utility_plant_in_service

I noticed that a decent number of the calculated values here had a rel_diff of 2 because the sign of the accumulated_depreciation was negative while the depreciation_utility_plant_in_service was positive. The worst offender was utility_id_ferc1 == 211. i looked into it and 🧼-ed it up but it feels vv bespoke & time consuming for fixing ~20 calculated values.

so I'm planning on leaving in these bb's that fall into this clear pattern:

[zaneselvans]

You probably already saw this but it looks like there might be a small population of records with a sign error -- the ones that head down and to the right from the origin.

[cmgosnell]

You probably already saw this but it looks like there might be a small population of records with a sign error -- the ones that head down and to the right from the origin.

yes i already fixed some of them and described the problem in the section above depreciation_utility_plant_in_service

[cmgosnell]

After some work via #3064 and #3078... i got the balance sheet asset inter-table calcs cleaned up a bit but they are still not awesome and none of these fall into the "off-by fact x" pattern. Here is the summary outputs:

[zaneselvans]

Ugh I love your plotting theme.

[cmgosnell]

I'm going to call this good enough for now. If we get further direction for further cleaning we can certainly open a new more directed version of this issue.