In yesterday’s post, I discussed how difficult it was to learn how to read the accounting tables in the multi-page budget worksheets submitted every year by each JCC (and all FJP beneficiary agencies) to the Federation Distribution Committee (FDC). I felt a brief moment of satisfaction when I managed to decode all the information included in these tables, but it quickly gave way to anxiety when I realized that there was much more data in each budget worksheet than my mind could keep track of—not to mention that I wanted to compare over twenty years of data. I needed to find a way to separate out the data I wanted to study from data that was a distraction.
Excel worksheets were the obvious solution to this problem. In one sheet, I could easily replicate the parts of the accounting tables that I wanted to study and input the relevant data for multiple years. For example, the first worksheet I created was to track the success of FJP's fundraising efforts. I began by compiling data on the total amount that FJP raised each year during their annual campaign. In the first column, I listed each fiscal year (FY), and in the second column I inserted the amount of dollars raised (which I found, with difficulty, in intermittent financial reports included in the minutes of the FJP Board of Trustees meetings, which the AJHS conveniently compiled and digitized for public use). This allowed me to track growth over time, without having to repeatedly consult individual archival documents. I made similar Excel sheets for each JCC that I am studying, to track the amount of the annual allocation they received from the FDC from year to year.
Federation Annual Campaign Totals By Year, 1946-1973. All work is property of Avigail S. Oren. Please do not use without permission.
Even more valuable to me was the ability to modify the worksheets while I was working on them, particularly to add new columns as I realized that I wanted more data, or to generate new data with a formula. I did not anticipate how frequently I would use the formula functions, but I have found formulas especially helpful for calculating percentages. For example, when I began to wonder how dependent the FJP was on the success of its annual campaign, I decided to see if they had other forms of revenue. They did, and so I created a new column listing their total revenue each year, and then another new column where I calculated what percent of that total was made up of the annual campaign contributions. Unsurprisingly, as FJP’s various endowments and investments matured, the annual campaign comprised a smaller percent of their income. I would not have necessarily seen this, or intuited just how dramatic the decline was, if I had not used the formula function to calculate this percentage.
Federation Annual Campaign Totals By Year, 1946-1973. All work is property of Avigail S. Oren. Please do not use without permission.
The most helpful part of using Excel, for me, has been the ability to create graphs. Like most people, I find that data visualizations are easier to interpret than a long string of numbers. My sister, who has a B.A. in Business Administration, helped me create line graphs that respond to a number of different questions: some compare streams of income, some track surplus and deficits over time, and others compare the nominal value of a dollar amount (for example, the total amount of an agency’s allocation) to its value once adjusted for inflation.
Budget Details for the YM-YWHA of Washington Heights-Inwood, 1954-1970. All work is property of Avigail S. Oren. Please do not use without permission.
Through the process of collecting and compiling this accounting data, I discovered so many new questions that I had never thought to ask before taking a deep dive into the weeds. It had never occurred to me, for example, that all income is not created equal. When I began my analysis, I simply looked at “total allocation” and “total income” as basic measures of a JCC’s financial health—similar to learning a human’s Body Mass Index and declaring them healthy or obese without examining the relationship between their height, weight, and age. It is significant whether a JCC ended its fiscal year with a deficit because they received a smaller allocation from the FDC or because they made less money from membership and special activities fees (their “other income”). I began calculating the allocation as a percent of the total income to see how dependent a JCC was each year on the money they received from Federation, as well as measuring the allocation as a percent of the total amount spent by the JCC that year to see how much of that allocation they needed in order to pay for their programs.
Without spending the time compiling, organizing, and creating visualizations of the historical accounting data from four different JCCs, I would have an overly simplified understanding of how each agency made its financial decisions. Check back tomorrow, when I will elaborate on how I discovered the important role that inflation played in JCC's financial decision making!