Number-Crunching

A new spreadsheet literally puts San Francisco commissioners on the same page.

by / October 31, 1997 0
Although reviewing a 500-page spending plan that allocates more than $400 million between four divisions and 10 support bureaus, members of the San Francisco Public Utilities Commission (PUC) won't lose sight of the big picture when the agency's 1998-99 budget hearings begin in January.


Why? A one-page spreadsheet distills the PUC's six-inch-thick annual budget into a "snapshot," giving commissioners a quick look at total costs for running the agency's divisions and bureaus and putting a clear price tag on new spending proposals. The summary form, developed by the PUC's Finance Bureau, lets commissioners instantly see how complex pieces of the agency's budget fit together.

Commissioners used the form for the first time last year and were extremely pleased, said PUC Finance Director Steven Carmichael. He expects the commission to use the summary again this year without changes.

"The form probably broke our working time in half. It was dynamite," said PUC President Victor Makras, who chaired last year's budget hearings.

"It allowed staff and the commission to stay more focused on each division on how it interplayed with the entire budget," said Makras. "In the past, you would be reaching to see the ultimate numbers."

A Sprawling Budget

PUC provides drinking water and sewer services to the city and county of San Francisco. It also wholesales water to neighboring communities and generates hydroelectric power from a string of reservoirs located near California's Yosemite National Park.

The agency pipes water more than 150 miles from the mountain reservoirs to the city, and electric power generated at its dams travels over miles of transmission lines.

Keeping financial tabs on these diverse undertakings results in a budget that's not particularly easy to digest, according to Anson Moran, the PUC's general manager.

"There are 12 or 13 major budgets that commissioners deal with," Moran said. "From an accounting standpoint, we deal with an awful lot of allocated costs. So, it's difficult to look at any one budget expense and attach meaning to it."

What's more, the PUC's five commissioners, appointed to four-year terms by the mayor of San Francisco, often don't have a public-agency background.

"They aren't necessarily utility people or government people, and they typically don't serve for a great length of time," said Moran. "Things that are kind of ordinary for those of us who deal with this insane bureaucracy aren't ordinary to them."

Searching for a Summary

Commissioners asked for a simple overview of the entire budget several years ago, said Carmichael. Working with Moran, the Finance Bureau spent a month tinkering with format and contents, finally settling on a large spreadsheet created with Microsoft's Access database software.

It was ready in time for five three-hour PUC budget review sessions held last January and February. In previous years, commissioners shuffled through some 15 pages of summary material to piece together a complete overview.

Measuring 11x17 inches, last year's summary spreadsheet listed the PUC's four operating divisions and 10 support bureaus in vertical columns. Budget amounts for 1996-97, adjustments for 1997-98, new budget proposals for 1997-98 and totals for the 1997-98 budget appeared in a series of horizontal rows.

"We crammed as much data onto that one piece of paper as we possibly could," said Carmichael. "Basically all you have is a snapshot of the total dollars, where they're being spent and what we're spending them on."

Commissioners could refer to the complete budget package to fill in financial details. "A lot of people looked at the spreadsheet, and that was enough information for them," he said.

"I think the biggest advantage of the summary was that it showed commissioners how we fit together as one entity," Carmichael said. "The message that we were trying to present became much clearer. We've had commissioners who have been here seven or eight years who've said they never had seen how it all fits together."

Form and Function

Moran said last year's summary was tailored to the needs of the 1997-98 commission, resulting in a spreadsheet with varying levels of detail based on decision-making significance rather than accounting significance.

"There are some awfully big numbers with no detail on them; commissioners were not being asked to make decisions in those areas," he explained. "In some cases you have a number in a cell that is pretty tiny -- that's something new where the commission was being asked to say 'yes' or 'no.'"

The form's content can evolve annually with the changing needs and decision-making styles of commissioners. But, with the basic framework in place, modifying the summary is relatively simple, Moran said, though holding its length to a single page remains tricky.

Last year's summary form seemed to squarely accommodate the needs of the 1997-98 commission, a body which took an unconventional approach to the budget review process, according to Moran.

"It was remarkably different from any other that I've been through. The commission ended up focusing very little on accounting details." he said. "I wouldn't lay that onto the summary form, but the form certainly was fully compatible with it."

Rather than concentrating on traditional number-crunching, commissioners talked individually with PUC division and bureau managers about their needs for the upcoming year. Since each column on the summary spreadsheet corresponds to a manager, commissioners had quick reference to each manager's budget requests.

"It was a very high-level discussion and the commission, I think, felt good about that," Moran said. "In some ways, we unburdened them of all that number-crunching kind of detail."

Efficiency and Focus

While last year's commission members held various stances on individual budget decisions, the summary spreadsheet provided a common reference point during the hearings, said Makras. "It didn't allow us to go in five different directions." In fact, from his perspective, it sparked new efficiency and unity.

"In the past, there would be discussions on divisions, then you would get to a summary stage and you didn't know where the numbers would add up. So there was always a re-review of what you had done," Makras said. "With the summary form, you knew the result of almost every decision as you made it.

"It also allowed department heads to convey their recommendations and never lose sight that we're one team and one family -- and ultimately every decision we make drops down to a rate payer."


November Table of Contents