Skip to main content

Creating an Excel-Based Strategic Facilities Plan

Time-Consuming Documentation Yields Real-Time Information, Flexibility
Published 7/22/2015
Stanford University School of Medicine
Department Study

While a traditional space planning document is often outdated as soon as it’s finished, Stanford University’s School of Medicine created its strategic facilities plan as a living document that in minutes can show the impact of even the slightest change in space usage, and can be constantly updated and changed. The medical school spent a year compiling faculty and space needs data into an Excel spreadsheet, and has found the effort well worth the payoffs.

“Our new space master plan model is amazing. I use it constantly,” says Joslyn Gray, facilities planner at the School of Medicine. “I am asked questions all the time about space, such as ‘What if we add a wet lab building here?’ I can plug in the factors, and the program will tell us exactly how many people can be accommodated in that building. We can get the answers immediately, and also see how the change impacts the plan overall.”

The computer model provides the ultimate flexibility, she adds. Data inputs, which include current and projected faculty, faculty type, space needs per faculty type, and available locations, can be changed at any time and utilized to inform financial and space decisions.

Space master planning or strategic facilities planning differs from overall campus planning or master planning, notes Gray. An institutional master plan is concerned with broad factors like the placement of buildings, and program and student growth, while space master planning looks at furthering the mission, using space and resources efficiently, and the data and metrics needed to do so.

“In our model, we are able to change the composition of the buildings based on our needs."

Invention Born of Necessity

Stanford University’s School of Medicine occupies 87 buildings on the main campus and surrounding areas, comprising about 3 million gsf within six different governmental jurisdictions. The school is growing and running out of space, explains Gray, but local government agencies are reluctant to approve expansion, primarily because of traffic issues.

Niraj Dangoria, associate dean of facilities planning and management, challenged his department to look at the school’s space in a different way. “He said, ‘Let’s create something flexible that can accommodate our ever-changing faculty projections, the changing types of space needs we have, the fluctuations in funding, and our space restrictions,’ which are always coming and going,” says Gray.

They gathered a large group of people from throughout the university to work on the new plan. A faculty space committee oversaw the group, which consisted of a project manager (Gray); the university architect; consultants; a data manager; and representatives from the leadership working group in the School of Medicine, institutional planning, finance, and hospital planning. The diversity ensured the group got all relevant information, such as the strategic vision of the school, faculty headcounts, staff numbers, cost parameters, funding requirements, and overall fit into the University’s master plan.

“Having this big group of people actually helped in the end because everybody had their say and buy-in when it was finished,” notes Gray.

The group’s main tasks were to determine what data to use, gather it, and decide how to organize it in a meaningful way. They came up with an overall plan formula that took the total number of faculty; multiplied it by an average number of square feet for faculty type; added square footage for the additional space types, like services amenities and shared spaces; and factored in new space standards with space sharing in mind.

“All those inputs enabled us to come up with a big number of square feet that we would need over time. We did them in annual increments but focused on 10-year slots, at a 10- and a 20-year timeframe.”

Determining Faculty Space Needs by Phenotype

About two years ago, Stanford began looking at faculty space needs by developing five phenotypes, instead of using only the traditional wet bench, dry bench, and clinical designations. Stanford’s five faculty phenotypes are: lab scientist, computational, informatics/clinical outcomes, clinical trialists, and clinicians.

They calculated an average space need per phenotype:

  • Lab scientists—wet bench researchers—with an average group size of 11.2 (one faculty, 10 researchers, and a shared administrative assistant), need a faculty office, 10 desks, and eight wet benches, for a total of about 1,400 sf.
  • Computational faculty perform a combination of data analysis and wet bench research. The group size/makeup is the same as the lab scientist, but because they do less wet bench research, only four benches were allocated with 10 desks, for an average of 1,100 sf.
  • Informatics faculty work with data and have no wet bench research. They have a smaller average group size of 6.2 (one faculty, five researchers, and a shared administrative assistant). Each person gets a desk, but the desks are bigger because they spend more time there, so the space estimate is 500 sf.
  • Clinical trialists conduct clinical trials research; their space need is more like clinic space, and they are allocated about 560 sf.
  • Clinicians spend much of their day seeing patients, and are allocated an office and a small percentage of a shared administrative assistant—approximately 111 sf.

This gave Stanford a foundation for the faculty space, but there were additional space needs, notes Gray. They added estimates for vet services, core services, and service centers, based on Stanford’s research growth. Education space was another small percentage; plus administration and shared resources, such as conference rooms and huddle spaces (based on total population growth); and cafés, fitness centers, and other amenities. These extra spaces informed the second part of the formula, she adds.

“There is one more space type that I encourage everybody to put into their model—contingency space. Because there is always some new program they forgot to tell you about, or some great new idea. Try to put in a small percentage for vacancy, to make your life a little easier in the planning realm.”

A 20 percent vacancy contingency was desired, but wasn’t feasible, because some buildings allowed for only 5 to 10 percent vacant space. “We tried to put in as much as we could, but the immediate need for space trumped the vacancy contingency.”

Historical Accuracy

When the space planning team presented the school’s head of finance with an initial estimate of square footage needs over 10-year timeframes, she said it was not financially feasible, and urged them to look at the data from a historical perspective, says Gray. Department projections compared to historic growth differed significantly.

“The departments were telling us we were going to need space for 560 lab scientists in 20 years. But, if we looked at the historical growth, we might only need 400. That is a big difference. This was helpful for us in the process, and it would have been great if we had considered this at the beginning.”

The historical information enabled the planning team to determine that in a 20-year timeframe, the School of Medicine would need about 4.5 million gsf at the most, and 3.7 million gsf at the least. When considered alongside the school’s building plan, the model showed that Stanford would be short of space for the next few years.

“Creating that high-low model enabled us to plan for where we were going to grow and when,” says Gray. “It also enabled us to put out a funding projection. In 20 years, we thought we would need about $2 billion.”

Stanford also had to decide how to organize the data within the Excel model in order to maximize its potential. They chose three categories:

  • By building: Location, size, and type of building (wet lab, dry lab, clinicians, or a combination of all space types).
  • By year: Construction, funding, and demolition schedules.
  • By faculty: Projected growth, historic growth, and the five phenotypes.

Data Reporting and Implementation

Once data tracking was in place, all departments were evaluated on three basic metrics: money, people, and vacancy. Money is the indirect and the direct cost for the research space. The people category looked at the number of square feet per person. Vacancy was calculated as a percentage of each department’s total space, for equity, since department sizes differ greatly.

The data was plotted on a graph, and departments that fell into the lowest quartile were shown the results. “It didn’t necessarily mean they weren’t utilizing their space, so we didn’t go into the departments and say, ‘You are not using your space!’ We said ‘Here is where you fall in those three metrics. Let’s talk about ways where we can better utilize your space, so that when you are planning to grow, we might first make better use of existing space, because we don’t have that much more space to give,’” says Gray.

Each department was given a detailed document of its existing space, including location, kinds of space, usage, five-year recovery rates, faculty growth in the last five years, and projected faculty growth, along with floor plans.

“They have never had all this information in one package before, and that has been very useful for us. We are trying to use this to bridge the space availability gap for the next five years, and then, overall, make better use of the space.”

Making it Work Elsewhere

Stanford’s model can be duplicated elsewhere, Gray believes, and could represent a paradigm shift. Today, institutions update space plans anywhere from yearly to every 10 years, but with a flexible model, they can update it as often as needed without redoing the entire plan.

“We change our model often. We look at different options once a month; get updated faculty counts once a year. Everything else, as we investigate different options and factors that are changing all the time (e.g. leases, locations), comes whenever,” she notes.

“It took us a long time to develop the model, but now that we have it, it’s great, because we can continue to modify and add to it.”

Stanford looked at programs besides Excel, but has not yet found another platform that would work as well in-house, which enables them to quickly make the changes themselves.

Stanford may be unique in that it had 50 years of historical data to pull from, but what’s important is that each institution utilize and/or develop the data that will work for its purposes. Having someone manage the data also makes the process easier, adds Gray.

“I think you can build a model with any data inputs you want; we had to choose a model that worked for us. For anyone who wants to do this type of work, first make sure you have the type of data you can use, and figure out a way to use the data that makes sense to you.”

By Taitia Shelow

This report is based on a presentation Gray made at Tradeline’s 2015 Strategic Facility and Space Planning for Science and Research conference.