2023.Six Easy Steps To Normalization

	A	B	C	D	E	F	G	H	I	J	K	L
1	worksheet created by @HenrikBechmann (twitter)
2
3	The "Working Data" tab is currently the live data source for an interactive dashboard here.
4
5	The 2023 open data set downloaded from			https://open.toronto.ca/dataset/budget-operating-budget-program-summary-by-expenditure-category/
6	"Operating Budget Program Summary by Expenditure Category"
7
8	Original data in Open Data tab
9	Modifications in Working Data tab
10
11	Modifications:
12	1	Added a "Line item index" column in the source data and populated that with unique index numbers for cross referencing
13	2	Modified column title (2023) to text so that the title would not be included in column quick total
14	3	Added 3 line items for water, waste, and parking, with surplus amounts to balance the spreadsheet to zero (they had been left out by the authors)
15		20128	Toronto Parking Authority	Not assigned	Not assigned	Expenses	Contribution To Capital	Contribution To Capital	Surplus	25,443,837.55
16		20129	Toronto Water	Not assigned	Not assigned	Expenses	Contribution To Capital	Contribution To Capital	Surplus	999,369,969.35
17		20130	Solid Waste Management Services	Not assigned	Not assigned	Expenses	Contribution To Capital	Contribution To Capital	Surplus	12,004,002.04
18	4	Eliminated original line item for debt charges
19		219	Capital & Corporate Financing	Corporate Accounts	Not assigned	Expenses	Other Expenditures	Debt Charges	Debt Charges	833,246,370.58
20	5	Replaced debt charges line item with two line items for estimated principal and interest
21			Capital & Corporate Financing	Corporate Accounts	Not assigned	Expenses	Cash Allocations for debt principal (est.)	Cash Allocations for Debt	Cash Allocations for debt principal repayment	416,623,185.29
22			Capital & Corporate Financing	Corporate Accounts	Not assigned	Expenses	Cash Allocations for debt interest (est.)	Cash Allocations for Debt	Cash Allocations for debt interest	416,623,185.29
23	6	Added column "Cash allocation basis"
24	7	Initialized "Cash allocation basis" column with "Expense/Revenue" values
25	8	Changed the "Cash allocation basis" values to "Capital" for the following Category Names:
26			Contribution From Reserves/Reserve Funds
27			Contribution To Reserves/Reserve Funds
28			Contribution To Capital
29			Debt Charges - principal
30	9	Changed the "Cash allocation basis" values to "Expense" for Transfers From Capital Category to recognize that they are expense offsets
31	10	Renamed "Transfers from Capital" to "Offsets from Capital Budgets" in the Category Name and Sub-Category Name columns for clarity
32	11	Changed the "Cash allocation basis" values to "Expense" for Interdivisional Recoveries to avoid double counting
33	12	Renamed "Inter-Divisional Recoveries" to "Offsets from Inter-Divisional Recoveries" in the Category Name and Sub-Category Name columns for clarity
34	13	Changed the "Cash allocation basis" values to "Revenues" for Waste Rebates and Tax Write offs to avoid inflating expenses and revenue.
35		5751	Solid Waste Management Services	Corporate Accounts	Not assigned	Expenses	User Fees & Donations	Services	Waste Collection Rebate	75,371,017.00	Revenues
36		5749	Non-Program Expenditures	Corporate Accounts	Not assigned	Expenses	Other Revenue	Tax Write Offs	Tax Write Offs	38,117,366.00	Revenues	39,737,384.44
37		5777	Non-Program Revenues	Corporate Accounts	Not assigned	Expenses	Other Revenue	Tax Write Offs	Tax Write Offs	1,620,018.44	Revenues
38		For Waste changed program from Non-Program Expenditures to Solid Waste Management Services. Changed Category Name to User Fees & Donations
39		For Tax Write Offs changed Category Name "Other Expenditures" to "Other Revenues"
40	14	Added "Cash allocation order", "Modified cash order","Program order", "Category order", "Organizational Taxonomy", and "Organizational Taxonomy order" columns to enable standard Toronto spreadsheet summary order
41	15	Populated "Cash allocation order", "Modified cash order","Program order", "Category order", "Organizational Taxonomy", and "Organizational Taxonomy order" columns with Lookup data (see "Lookup" tabs)
42	16	Added "Functional domain taxonomy", "Functional domain taxonomy order", "Functional taxonomy", Functional taxonomy order", "Resource taxonomy", "Resource taxonomy order" columns
43	17	Populated the taxonomy columns from 14 and 16. See Lookup tabs
44	18	Created various "Pivot" tabs
45	19	Created various "Analysis" tabs for presentation
46
47
48	Notes:
49	The organizational taxonomy is taken from City of Toronto budget summary spreadsheets
50	The functional domain, functional, and resource taxonomies are mine
51	The "order" columns are for my convenience only, so I've hidden them everywhere. They refer to report presentation order, and make it a bit easier to copy/paste/format pivot data to analysis tabs
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100