{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["Sheet1"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Sheet1","index":0,"n_rows":42,"n_cols":27,"rows_kept":42,"cut":false,"hidden":false,"merged":[[18,1,18,13],[18,15,18,26],[22,1,37,4]]}
[null,"Allocations Table",null,null,null,null,null,null,null,null,null,null,null,null,null,"Budgets Table"]
[null,"Recipient","Budget source","2021","2022","2023","2024","2025","2026","2027","2028","2029","2030","10-year total",null,"Budget source","2021","2022","2023","2024","2025","2026","2027","2028","2029","2030","10-year total"]
[null,"R1","B19",3500000,1000000,10000,null,null,null,null,null,null,null,{"$f":"SUM(Allocations[[#This Row],[2021]:[2030]])","v":4510000},null,"B19",4000000,2000000,1000000,1000000,1000000,1000000,1000000,1000000,1000000,1000000,{"$f":"SUM(AvailableBudgets[[#This Row],[2021]:[2030]])","v":14000000}]
[null,"R2","B221",5000,195000,150000,50000,null,null,null,null,null,null,{"$f":"SUM(Allocations[[#This Row],[2021]:[2030]])","v":400000},null,"B221",250000,250000,250000,250000,null,null,null,null,null,null,{"$f":"SUM(AvailableBudgets[[#This Row],[2021]:[2030]])","v":1000000}]
[null,"R3","B331",null,5000,195000,150000,50000,null,null,null,null,null,{"$f":"SUM(Allocations[[#This Row],[2021]:[2030]])","v":400000},null,"B331",10000000,10000000,10000000,10000000,10000000,10000000,null,null,null,null,{"$f":"SUM(AvailableBudgets[[#This Row],[2021]:[2030]])","v":60000000}]
[null,"R4","B43",null,null,null,5000,195000,150000,25000,null,null,null,{"$f":"SUM(Allocations[[#This Row],[2021]:[2030]])","v":375000},null,"B43",200000,200000,200000,200000,200000,200000,200000,200000,200000,200000,{"$f":"SUM(AvailableBudgets[[#This Row],[2021]:[2030]])","v":2000000}]
[null,"R5","B331",null,5000,195000,150000,50000,null,null,null,null,null,{"$f":"SUM(Allocations[[#This Row],[2021]:[2030]])","v":400000},null,"AB221",250000,250000,250000,250000,250000,250000,250000,250000,250000,250000,{"$f":"SUM(AvailableBudgets[[#This Row],[2021]:[2030]])","v":2500000}]
[null,"R5","B19",null,null,500,19500,15000,5000,null,null,null,null,{"$f":"SUM(Allocations[[#This Row],[2021]:[2030]])","v":40000},null,"Total",{"$f":"SUBTOTAL(109,AvailableBudgets[2021])","v":14700000},{"$f":"SUBTOTAL(109,AvailableBudgets[2022])","v":12700000},{"$f":"SUBTOTAL(109,AvailableBudgets[2023])","v":11700000},{"$f":"SUBTOTAL(109,AvailableBudgets[2024])","v":11700000},{"$f":"SUBTOTAL(109,AvailableBudgets[2025])","v":11450000},{"$f":"SUBTOTAL(109,AvailableBudgets[2026])","v":11450000},{"$f":"SUBTOTAL(109,AvailableBudgets[2027])","v":1450000},{"$f":"SUBTOTAL(109,AvailableBudgets[2028])","v":1450000},{"$f":"SUBTOTAL(109,AvailableBudgets[2029])","v":1450000},{"$f":"SUBTOTAL(109,AvailableBudgets[2030])","v":1450000},{"$f":"SUBTOTAL(109,AvailableBudgets[10-year total])","v":79500000}]
[null,"R6","B221",null,null,null,null,5000,195000,150000,50000,null,null,{"$f":"SUM(Allocations[[#This Row],[2021]:[2030]])","v":400000}]
[null,"R7","B331",500000,12500000,3500000,1000000,null,null,null,null,null,null,{"$f":"SUM(Allocations[[#This Row],[2021]:[2030]])","v":17500000}]
[null,"R8","B43",null,500,19500,15000,5000,null,null,null,null,null,{"$f":"SUM(Allocations[[#This Row],[2021]:[2030]])","v":40000}]
[null,"R8","B19",null,500,19500,15000,5000,null,null,null,null,null,{"$f":"SUM(Allocations[[#This Row],[2021]:[2030]])","v":40000}]
[null,"R9","B221",null,null,null,null,null,5000,195000,150000,50000,null,{"$f":"SUM(Allocations[[#This Row],[2021]:[2030]])","v":400000}]
[null,"R10","B331",null,null,null,5000,250000,150000,50000,null,null,null,{"$f":"SUM(Allocations[[#This Row],[2021]:[2030]])","v":455000}]
[null,"R10","B221",500,19500,15000,5000,null,null,null,null,null,null,{"$f":"SUM(Allocations[[#This Row],[2021]:[2030]])","v":40000}]
[null,"Total",null,{"$f":"SUBTOTAL(109,Allocations[2021])","v":4005500},{"$f":"SUBTOTAL(109,Allocations[2022])","v":13725500},{"$f":"SUBTOTAL(109,Allocations[2023])","v":4104500},{"$f":"SUBTOTAL(109,Allocations[2024])","v":1414500},{"$f":"SUBTOTAL(109,Allocations[2025])","v":575000},{"$f":"SUBTOTAL(109,Allocations[2026])","v":505000},{"$f":"SUBTOTAL(109,Allocations[2027])","v":420000},{"$f":"SUBTOTAL(109,Allocations[2028])","v":200000},{"$f":"SUBTOTAL(109,Allocations[2029])","v":50000},{"$f":"SUBTOTAL(109,Allocations[2030])","v":0},{"$f":"SUBTOTAL(109,Allocations[10-year total])","v":25000000}]
[]
[]
[null,"Features of the Allocations table:\n- Will grow to many more rows, amounts, budgets and recipients, but normally always within a 10 year profile (former years may be hidden or grouped and hidden for ease of reference if need be). \n- The actual table cannot be adjusted to move the years columns to rows, but I can add more columns if need be.\n- Recipients may have multiple lines if they're receiving money from more than one budget source (e.g., R5, R8, R10). This and the amounts can change by the month.\n- The \"Budget source\" column is a dropdown pick list, auto-updated via power query when a new budget source comes online.",null,null,null,null,null,null,null,null,null,null,null,null,null,"Features of the Budgets table:\n- Will grow slightly over time with more budgets identified 1 to 3 times per year as they come on line.\n- The actual table cannot be adjusted to move the years columns to rows, but I can add more columns if need be.\n- Can adjust table if need be, though prefer to keep separate from other tables.\n- The \"Budget source\" column may be elaborated upon to be more explanatory via another column. This could also be achieved via a 'bridge table' in data model, but for the current purpose, I am keeping it as the unique entries table (many to one)."]
[]
[]
[]
[null,"Features of the table I want to have:\n- Overall look as outlined to the right.\n- Automatically updates without having to adjust it manually; just uses the data model and power query to update as new budgets, recipients and amounts are added, removed, or otherwise changed over time.\n- Uses Power Pivot / Power Query to facilitate automation and expansion of this regular tracking activity, reducing manual table creation and inherent mistakes associated with that.",null,null,null,null,null,null,"2021","2022","2023","2024","2025","2026","2027","2028","2029","2030","10-year total"]
[null,null,null,null,null,null,"Budget B19","Available","(as drawn from Budgets table)",null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,"Allocated","(as drawn from sum total of Budget source column in Allocations table)",null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,"Remaining / Variance","(calculation of available subtract allocated)",null,null,null,null,null,null,null,null,null,0]
[]
[null,null,null,null,null,null,"Budget B221","Available","(as drawn from Budgets table)",null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,"Allocated","(as drawn from sum total of Budget source column in Allocations table)",null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,"Remaining / Variance","(calculation of available subtract allocated)",null,null,null,null,null,null,null,null,null,0]
[]
[null,null,null,null,null,null,"Budget B331","Available","(as drawn from Budgets table)",null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,"Allocated","(as drawn from sum total of Budget source column in Allocations table)",null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,"Remaining / Variance","(calculation of available subtract allocated)",null,null,null,null,null,null,null,null,null,0]
[]
[null,null,null,null,null,null,"Budget …..","Available","(as drawn from Budgets table)",null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,"Allocated","(as drawn from sum total of Budget source column in Allocations table)",null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,"Remaining / Variance","(calculation of available subtract allocated)",null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,"…..","….."]
[null,null,null,null,null,null,"Grand Totals","Available","(as drawn from Budgets table or summed based on filters above)",null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,"Allocated","(as drawn from sum total of Budget source column in Allocations table, or summed based on filters above)",null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,"Remaining / Variance","(calculation of available subtract allocated, derived from above amounts)",null,null,null,null,null,null,null,null,null,0]