Summer in the City

Summer in the City (SITC) a nonprofit organization based in Detroit that works to bring volunteers together to address the immediate needs of the city through activities like creating murals, building and maintain urban gardens, and hosting youth enrichment programs.

Prompt

Come up with a solution to this problem: SITC volunteers come mostly from area high schools within an hour radius of the city. They had around 7 carpool locations and a set number of drivers, vans, and cars. Due to uncertainty and variations in the number of volunteers who signed up in each area versus the number who showed up, they had empty cars driving to Detroit from some sites and had to turn volunteers away at other sites because there weren't enough seats. There were planning issues and time-consuming data entry tasks that exacerbated the problem.

Solution Overview

Most of the first day of work was spent discussing process and issues with the SITC representative, finding out what was important to them and what pieces of the puzzle we were able to manipulate. We then had a group brainstorming session to put together possible solutions. To address the issue of vehicle distribution among volunteer sites, we created a system of "dispatch points" where the drivers, vans, and cars would meet in a centralized location the morning of a volunteer event. To assist with this process and save hours of time the coordinator spend doing cumbersome data-entry, we put together an Excel document (this is the program Summer in the City was already using) that contained the entire volunteer list, site list, and driver list. It used drop-down menus (and autofill) grabbed from those three sheets to allow the coordinator to easily populate fields while assigning drivers and volunteers to cars and location based on volunteer turnout and needs of the volunteer site.

What I Learned

  • How to think creatively about data and use it to address problems
  • How to create complex spreadsheets in Microsoft Excel
  • How to have productive discussions with large and fluid groups of people

One of the whiteboards during situation analysis and solution brainstorm

Map showing proposed solution for maximizing volunteer-pickup sufficiency. Each green dot represents a pickup point, and the blue dots represent vehicle "hubs" for dispersal after final volunteer counts are made at each pickup point.