[TRU Research] Discrepencies between Budget Archives and Open Data

Katie Wilson katie at transitriders.org
Sat Jun 13 19:28:30 PDT 2020 

Another random thought: might be interesting to look at SPD overtime spending as a percentage of base personnel spending and/or total budget.

> On Jun 13, 2020, at 3:09 PM, Katie Wilson <katie at transitriders.org> wrote:
> 
> Jim, those are good points. 
> 
> On the second point, though, I think even if the actual trend line ends up looking similar, there is a good story to be told:
> City human services spending has risen in the context of cuts to human services spending at other levels of gov’t (per McGinn’s email), so cities like Seattle are shouldering more of that role.
> Because of rising rents and income inequality over the past decade, there is way more work to be done in this area, and the spending hasn’t nearly kept up.
> By contrast, crime has not been increasing appreciably. In fact, there’s a decades-long trend of falling crime.
> Because we’re not funding appropriate responses to poverty and homelessness, we end up funding more police to deal with the social disorder that results from this failure. In other words, we criminalize poverty.
> 
> But clearly this story is a little complicated, and you can’t tell it in a single graph.
> 
> I am working on writing something about the police budget and I would also like to start sharing some of our work with the defund SPD folks. Because we haven’t yet figured out the best way to present HSD numbers, Jim, could you make a page just containing the SPD graph starting in 2012 (in whatever type of visual you think is most compelling), so I can link to it from my article and also share it with our allies? I think we should use 2012 as the starting point rather than 2008 or 2012 because it’s a low point for the inflation-adjusted and per capita numbers, and also 8 years is a nice round number in political terms. Spearking of per capita numbers, could you also make a graph (maybe a bar graph?) showing the per capita figures from 2012 to 2020? That could go on the same page I think.
> 
> - Katie
> 
>> On Jun 11, 2020, at 5:49 PM, Jim Walseth <jim.walseth at gmail.com <mailto:jim.walseth at gmail.com>> wrote:
>> 
>> Regarding the pass through line item, here are two considerations:
>> 1. RIght now the data attribution is simply
>> "From the CIty of Seattle budget archives https://www.seattle.gov/city-budget-office/budget-archives <https://www.seattle.gov/city-budget-office/budget-archives>, adjusted for inflation."
>> If we start tinkering with the numbers the attribution should explain what we did it and might appear to the casual reader that we are "juking the stats".
>> But in reality...
>> 2. If removing the pass through has the effect of removing the 'bump'  in early years, the Human Services trend line will look a lot more like the SPD one.
>> 
>> On Thu, Jun 11, 2020 at 5:16 PM Katie Wilson <katie at transitriders.org <mailto:katie at transitriders.org>> wrote:
>> One more thought on the human services data, remembering what I found out before about the $26 million dip from 2011 to 2012, as explained here <https://www.seattle.gov/Documents/Departments/FinanceDepartment/12adoptedbudget/HealthHumanServices_2012AdoptedBudget_000.pdf>:
>> 
>> "While the 2012 Adopted Budget preserves and increases General Fund support for HSD, the Department is experiencing reductions in State and Federal funding. The 2012 Adopted Budget recognizes impacts from changes in State funding in the Aging and Disability Services Division. Beginning in October 2011, pass-through funding for home care program health plan reimbursements is redirected to home care agencies and is no longer administered by HSD, resulting in a significant budget reduction to HSD but no impact on direct services, as it was a change to the method of payment.
>> 
>> Given that this is an accounting thing, it seems like maybe we should try adjusting the 2008-2011 numbers so that they don’t include this line item, which (it sounds like) doesn’t appear in the later years (unless it was added back in at some point)?
>> 
>>> On Jun 11, 2020, at 2:07 PM, Jim Walseth <jim.walseth at gmail.com <mailto:jim.walseth at gmail.com>> wrote:
>>> 
>>> Tip: you can go to https://public.tableau.com/profile/katie.wilson8886#!/ <https://public.tableau.com/profile/katie.wilson8886#!/> to browse all the samples.
>>> 
>>> On Thu, Jun 11, 2020 at 1:51 PM Jim Walseth <jim.walseth at gmail.com <mailto:jim.walseth at gmail.com>> wrote:
>>> Here's another version. Personally I find the filled overlay a bit misleading in the sense that the Human Services polygon covers part of the SPD spend, making it visually smaller.
>>> Here is the same viz just using lines.
>>> https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample6/Dashboard3?publish=yes <https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample6/Dashboard3?publish=yes>
>>> 
>>> 
>>> On Thu, Jun 11, 2020 at 1:03 PM Jim Walseth <jim.walseth at gmail.com <mailto:jim.walseth at gmail.com>> wrote:
>>> Katie, Taking a stab at your request:
>>> https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample5/Dashboard3?publish=yes <https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample5/Dashboard3?publish=yes>
>>> On Thu, Jun 11, 2020 at 11:43 AM Katie Wilson <katie at transitriders.org <mailto:katie at transitriders.org>> wrote:
>>> Looking good Jim! Visually I think I like the first graph better than the bar graph— is there a way to overlay the human services budget on that one in the same manner?
>>> 
>>>> On Jun 11, 2020, at 10:38 AM, Jim Walseth <jim.walseth at gmail.com <mailto:jim.walseth at gmail.com>> wrote:
>>>> 
>>>> I fussed with both of those some more. I'm going back to my day job now and will check in later for feedback.
>>>> Jim
>>>> 
>>>> On Thu, Jun 11, 2020 at 9:25 AM Jim Walseth <jim.walseth at gmail.com <mailto:jim.walseth at gmail.com>> wrote:
>>>> Fantastic work on the spreadsheet. Here are updates to the two vizzes I made yesterday, using inflation-based numbers. 
>>>> I also added data attribution, fixed up labels and things like that. Happy to make any changes that I am able to make!
>>>> 
>>>> https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample3/Dashboard1?publish=yes <https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample3/Dashboard1?publish=yes>
>>>> 
>>>> https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample4/SeattleCumulativeSpending?publish=yes <https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample4/SeattleCumulativeSpending?publish=yes>
>>>> On Thu, Jun 11, 2020 at 7:30 AM Katie Wilson <katie at transitriders.org <mailto:katie at transitriders.org>> wrote:
>>>> Re what police do with their time… This guy Bryan Kirschner who’s a tech employee (he’s the one organizing the Tech4Recovery group) used to do community policing work & he’s writing a series about policing/public safety. He sent me a link to this piece <https://medium.com/@contact_28544/everything-you-know-about-policing-is-wrong-part-1-2639186e101e> which I found enlightening.
>>>> 
>>>>> On Jun 11, 2020, at 1:02 AM, Douglas Sexton <sextondouglas at comcast.net <mailto:sextondouglas at comcast.net>> wrote:
>>>>> 
>>>>> Jumping off of Harry's last contribution.I tied to get homeless data for Seattle from 2008-2019 using the point in time count but there are years that only have data for King County and not specifically Seattle. Additionally as the years go on more areas are searched resulting in more thorough higher counts but I think the methodology in the last 3 years is relatively consistent. I added a column for King County # unsheltered homeless which was the only consistent measure I could find (though early years look very undercounted). A rough rule of thumb is ~60% of the count is Seattle. It's definitely unclear on if investment in housing helped with the deceleration of homelessness in the latest years but it sure is clear that police aren't helping. 
>>>>> 
>>>>> The salary per cop stat is super compelling to me. Maybe a breakdown of what a cop gets paid as a percentage of how their time is actually spent would be interesting.Thinking about that confessions of a bastard cop post that has been going around recently (https://medium.com/@OfcrACab/confessions-of-a-former-bastard-cop-bb14d17bc759 <https://medium.com/@OfcrACab/confessions-of-a-former-bastard-cop-bb14d17bc759>). My understanding is a lot of the time these cops might be responding to crimes after they happen and not catching anyone and possibly creating unnecessary citations.You have to click the "all other crime" stats on the dashboard here (http://www.seattle.gov/police/information-and-data/crime-dashboard <http://www.seattle.gov/police/information-and-data/crime-dashboard>) and one thing that jumps out is there are as almost as many trespassing crimes as violent crimes so far in 2020. That seems... odd (unless you already know cops exist to protect property interests). Maybe I can dig into that more later.
>>>>> 
>>>>> Doug
>>>>>> On June 10, 2020 at 10:38 PM Harry Maher <harryb.maher at gmail.com <mailto:harryb.maher at gmail.com>> wrote: 
>>>>>> 
>>>>>> Cool graphs, thanks, Jim! And yep, you're right re: inflation, Katie. I added that and the rest of the annual budgets.
>>>>>> 
>>>>>> I also looked at inflation adjusted $/population in another set of columns. I was thinking that, in addition to inflation, it might make sense to normalize things by population growth as well. To Jim's point, they are all experiencing the same population growth, but to Katie's point, the narrative can change/get a sense of clarity when you normalize the #s. (Or, at the very least, we can just report the amount that population has increased over the same period b/c there remains the open question if normalizing budget by population makes as much sense for all of these programs--if the cops are smart, they may argue that they "need to patrol" both ppl who live here as well as those visiting from surrounding areas for work/tourism/crime/etc., while housing and human services would mostly only serve Seattle residents.) 
>>>>>> 
>>>>>> Anyway, when we look at inflation adjusted $ spent per resident, it looks like we were defunding human services for a while there and essentially barely changing funding for housing over the years while substantially increasing funding for the police. We can look at the # of folks experiencing homelessness in Seattle over the same period and the # of Seattlites estimated to be rent burdened or severely rent burdened (both I assume have increased substantially more than population growth) to add to a compelling story. That said, it's also a little misleading when we can't use 2020 numbers due to a lack of 2020 population estimates from the Census thus far (and 2020 is the year they increased funding for human services and housing).
>>>>>> 
>>>>>> Another thought on the fact that salaries make up ~80% of the budget and will need to get cut, or personnel will need to get laid off: we can likely get some good traction if we compare what cops earn to what human services and housing employees earn. Something like, "for each cop that we have to lay off, we can hire 2 essential housing employees" or whatever the actual number is.
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> On Wed, Jun 10, 2020 at 9:16 PM Katie Wilson < katie at transitriders.org <mailto:katie at transitriders.org>> wrote: 
>>>>>> One reason I’m interested is that I’m pretty sure once you adjust for inflation, human services spending is more or less flat between 2010 and 2018 (taking into account the 2011-2012 dip which is an accounting thing), and I think that’s a good story to tell.
>>>>>>   
>>>>>> 
>>>>>>> On Jun 10, 2020, at 7:46 PM, Jim Walseth < jim.walseth at gmail.com <mailto:jim.walseth at gmail.com>> wrote:
>>>>>>> 
>>>>>>> Yes I filtered the data to start at 2013 because only the spd data exists from years before that in the spreadsheet. Once we have housing and services data for all years I can open it up.
>>>>>>> 
>>>>>>> I question whether inflation is needed when doing comparison since all would be affected. But yeah I always appreciate that effort.
>>>>>>> 
>>>>>>> Jim
>>>>>>> 
>>>>>>> On Wed, Jun 10, 2020 at 7:19 PM Katie Wilson < katie at transitriders.org <mailto:katie at transitriders.org>> wrote: 
>>>>>>> Cool!!
>>>>>>> 
>>>>>>> Jim, on the bar graph in the first link, is there a reason you started with 2013 instead of 2008 or 2010? 
>>>>>>> 
>>>>>>> I think we should play around with adjusting for inflation, too, perhaps using this:  https://www.usinflationcalculator.com <https://www.usinflationcalculator.com/> 
>>>>>>>  
>>>>>>> I would be interested in seeing that bar graph starting in 2008 and adjusting for inflation (to 2020 dollars, I guess).
>>>>>>>   
>>>>>>> 
>>>>>>>> On Jun 10, 2020, at 7:10 PM, Jim Walseth < jim.walseth at gmail.com <mailto:jim.walseth at gmail.com>> wrote:
>>>>>>>> 
>>>>>>>> Ooh I'm liking this
>>>>>>>> 
>>>>>>>> Sample 2
>>>>>>>> 
>>>>>>>> https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample2/SeattleCumulativeSpending?publish=yes <https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample2/SeattleCumulativeSpending?publish=yes>                      
>>>>>>>> 
>>>>>>>> On Wed, Jun 10, 2020 at 6:55 PM Jim Walseth < jim.walseth at gmail.com <mailto:jim.walseth at gmail.com>> wrote: 
>>>>>>>> I made it "tall" so scroll down to see part 2. This seems to be the fashion with web stuff now, rather than tabs, for instance. 
>>>>>>>> 
>>>>>>>> On Wed, Jun 10, 2020 at 6:53 PM Jim Walseth < jim.walseth at gmail.com <mailto:jim.walseth at gmail.com>> wrote: 
>>>>>>>> First sample viz
>>>>>>>> 
>>>>>>>> https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample1/Dashboard1?publish=yes <https://public.tableau.com/profile/katie.wilson8886#!/vizhome/SPDBudgetsample1/Dashboard1?publish=yes> 
>>>>>>>> 
>>>>>>>> On Wed, Jun 10, 2020 at 6:52 PM Harry Maher < harryb.maher at gmail.com <mailto:harryb.maher at gmail.com>> wrote: 
>>>>>>>>  Omg Ben. WHAT?
>>>>>>>> 
>>>>>>>> And ok, I've been lazily plugging in numbers while listening to the radio and now preparing dinner, hope to be done at some point, but feel free to jump in anyone who wants this done more quickly... 
>>>>>>>> 
>>>>>>>> And that's great, Jim!!
>>>>>>>>   
>>>>>>>> 
>>>>>>>> Some notes:
>>>>>>>> - These pdfs are still v. opaque and we may have to wait until we get more specific details from the city council. Definitely no line item for "military weapons" or other definite cuts...
>>>>>>>> - Personnel was 80% of the $398,561,697 budget in 2019 (I can't quickly find the % for 2020) and they report 2,172.35 FTE (with an asterisk that it's approximated, but...). That puts the average salary at $146,776 if I'm not mistaken. That's insane. I get that it's a hard job, but yeesh, also supposedly one of public service. Since they are city employees, I bet we'd be able to look at individual salaries and look at how overtime pay is allocated b/c I know that they get a huge chunk of their pay from overtime. I'm sure getting rid of overtime pay will go a very long way towards reducing the budget, but have no numbers to back that right now. 
>>>>>>>> - Slightly in jest, but... maybe we can completely slash the budget for East Precinct in addition to overtime cuts and see where that gets us. Keep on the facilities/maintenance/custodial staff, see what the Black community wants to do with the building, and reallocate those workers to the appropriate division(s).
>>>>>>>> 
>>>>>>>> On Wed, Jun 10, 2020 at 6:30 PM Jim Walseth < jim.walseth at gmail.com <mailto:jim.walseth at gmail.com>> wrote: 
>>>>>>>> I am throwing this into Tableau Public and will make a few basic samples. I removed the first row which just said "adopted budget numbers" because it was confusing Tableau.
>>>>>>>> Jim 
>>>>>>>> 
>>>>>>>> On Wed, Jun 10, 2020 at 3:53 PM Katie Wilson <katie at transitriders.org <mailto:katie at transitriders.org>> wrote: 
>>>>>>>> Hey folks,
>>>>>>>> 
>>>>>>>> Since the Open Data site is unreliable, it looks like our best bet for accurate budget data is the PDFs archived here: https://www.seattle.gov/city-budget-office/budget-archives <https://www.seattle.gov/city-budget-office/budget-archives> 
>>>>>>>> 
>>>>>>>> Here is a mini-project if anyone has time for it.  I created a spreadsheet here <https://docs.google.com/spreadsheets/d/1uDjqVID4gkBFfatMTM-hUbO-OAzR70Zc1-3GJEFZask/edit?usp=sharing>, where we can compile data from the adopted budgets (not proposed or endorsed) for SPD, human services, housing, and any other areas of interest. I think the years 2010-2020 are key, but I’ve started the spreadsheet in 2008 just to match with the recession, in case that shows us anything interesting. Filling out this spreadsheet will enable us to make a graph like the ones we were making from the Open Data site, only correct this time.
>>>>>>>> 
>>>>>>>> I am in touch with some of the folks leading the Defund SPD effort and they would love our help digging up data and making graphs etc. that tell a good story to boost the case. So, rest assured our work on this will be useful and appreciated! For example, I was able to send along this bit earlier today, which I believe was then passed along to a councilmember via a council aide to be shared aloud during the council meeting this afternoon:
>>>>>>>> 
>>>>>>>> The SPD budget has increased by  43% since 2010,  after you account for inflation. (Not accounting for inflation, it’s a 68% increase.)
>>>>>>>> 
>>>>>>>> Here’s the math and sources:
>>>>>>>> 
>>>>>>>> 2020 SPD adopted budget:  $409 million - https://www.seattle.gov/Documents/Departments/FinanceDepartment/20adoptedbudget/SPD.pdf <https://www.seattle.gov/Documents/Departments/FinanceDepartment/20adoptedbudget/SPD.pdf> 
>>>>>>>> 
>>>>>>>> 2010 SPD adopted budget:  $243 million - https://www.seattle.gov/Documents/Departments/FinanceDepartment/10adoptedbudget/PUBLIC_SAFETY.pdf <https://www.seattle.gov/Documents/Departments/FinanceDepartment/10adoptedbudget/PUBLIC_SAFETY.pdf> 
>>>>>>>> 
>>>>>>>> 2010 SPD adopted budget, injusted for inflation to 2020 dollars:  $286 million - https://www.usinflationcalculator.com <https://www.usinflationcalculator.com/> 
>>>>>>>> 
>>>>>>>> Increase from 2010 to 2020, i.e. 286 to 409, is (409 - 286)/286 x 100 =  43%
>>>>>>>> 
>>>>>>>> - Katie
>>>>>>>>   
>>>>>>>> 
>>>>>>>>> On Jun 9, 2020, at 5:34 PM, Katie Wilson <katie at transitriders.org <mailto:katie at transitriders.org>> wrote:
>>>>>>>>> 
>>>>>>>>> Budget office director to the rescue, kind of...
>>>>>>>>> 
>>>>>>>>>> Begin forwarded message:
>>>>>>>>>> 
>>>>>>>>>> From: "Noble, Ben" <Ben.Noble at seattle.gov <mailto:Ben.Noble at seattle.gov>>
>>>>>>>>>> Subject: RE: Discrepencies between Budget Archives and Open Data
>>>>>>>>>> Date: June 9, 2020 at 5:31:02 PM PDT
>>>>>>>>>> To: Katie Wilson <katie at transitriders.org <mailto:katie at transitriders.org>>
>>>>>>>>>> 
>>>>>>>>>> Katie – long and painful story here, but the Open Data site is not accurate.  I am frankly embarrassed by that reality.  Short-term we may take down the site, but obviously that is not a positive step in terms of transparency.  It was something that got set up in a rush a few years back and has been neglected since (and frankly has not been a priority in the last few months).  I say that by way of explanation not excuse.  In the term, the Budget Archives, while clunky, are the right source.  Based on your flagging of the issue, I have just asked the team whether we can get something else up in the near-term.  I will keep you posted. 
>>>>>>>>>>  
>>>>>>>>>> -ben.
>>>>>>>>>>  
>>>>>>>>>> From:  Katie Wilson <katie at transitriders.org <mailto:katie at transitriders.org>>   
>>>>>>>>>> Sent:  Tuesday, June 9, 2020 4:29 PM 
>>>>>>>>>> To:  Noble, Ben <Ben.Noble at seattle.gov <mailto:Ben.Noble at seattle.gov>> 
>>>>>>>>>> Subject:  Discrepencies between Budget Archives and Open Data
>>>>>>>>>>  
>>>>>>>>>> CAUTION: External Email
>>>>>>>>>> Hi Ben,  
>>>>>>>>>>  
>>>>>>>>>> Sorry to bother you with this, but I emailed the Open Data people first and they told me to ask the City Budget Office. I’m wondering why there are significant discrepencies between the  Budget Archives <https://www.seattle.gov/city-budget-office/budget-archives> and the budget numbers on the  Open Data site <https://openbudget.seattle.gov/#!/year/default>. 
>>>>>>>>>>  
>>>>>>>>>> To give one timely example,  this Open Data page <https://openbudget.seattle.gov/#!/year/2020/operating/0/department/Police/0/service?vis=barChart> puts the 2020 SPD budget at ~$357m, whereas the number in the 2020 adopted budget <https://www.seattle.gov/Documents/Departments/FinanceDepartment/20adoptedbudget/SPD.pdf> and reported in various news articles <https://www.seattletimes.com/seattle-news/crime/after-days-of-seattle-protests-city-will-withdraw-request-to-remove-police-force-from-federal-oversight/> is $400m+. I have been digging around and making graphs and there are a lot of discrepencies like this. I understand that proposed and adopted and actual budgets are all different, but that doesn’t seem to account for it.
>>>>>>>>>>  
>>>>>>>>>> Please feel free to direct me elsewhere if there’s someone else I should be asking about this.
>>>>>>>>>>  
>>>>>>>>>> Thanks!
>>>>>>>>>> Katie
>>>>>>>>>>  
>>>>>>>>>> Katie Wilson
>>>>>>>>>> General Secretary
>>>>>>>>>> Transit Riders Union
>>>>>>>>>> https://transitriders.org <https://protect2.fireeye.com/v1/url?k=23520a2f-7de29737-2352229f-86b2e136ff17-644b0fdb8c75a238&q=1&e=c19e905a-6ec5-4436-a6cd-f7acc13cf097&u=https%3A%2F%2Ftransitriders.org%2F>
>>>>>>>>>> 206-781-7204
>>>>>>>>>>  
>>>>>>>>>> The Transit Riders Union is a democratic organization of working and poor people, including students, seniors, and people with disabilities, taking control over our own lives, and building up the power we need to change society for the good of humanity and of the planet. We will fight to preserve, expand, and improve the public transportation system in Seattle and beyond, so that every human being has access to safe, affordable, and reliable public transit.
>>>>>>>>> 
>>>>>>>>> 
>>>>>>>> 
>>>>>>>> 
>>>>>>>> 
>>>>>>> 
>>>>>> 
>>>>>> 
>>>>> 
>>>>>  
>>>> 
>>>> 
>>> 
>> 
> 
> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.transitriders.org/pipermail/research/attachments/20200613/01b45248/attachment.html>

More information about the Research mailing list