How to Improve Financial Planning By Keeping Spreadsheets

By Doug Bartholomew  |  Posted 2008-02-21

Jeffery Nemy is a spreadsheet kind of guy.

A career finance professional, Nemy, chief financial officer at Northern California Public Broadcasting, was reared on VisiCalc and Lotus1-2-3, and later graduated to Excel. Like most financial managers, he’s used to the rituals of putting together annual departmental budgets and rolling up an organization’s monthly financial results using the tried and true spreadsheet, with all its line items and columns. To the CFO, financial planner, or budget officer, the spreadsheet is as familiar as a chainsaw to a logger.

One big problem with depending on spreadsheets for financial reporting, though, is that for a company like NCPB, rolling up the numbers contained in 200 different spreadsheets representing the financial results of 200 cost centers, departments, and projects can be a major headache.

“It took us six weeks to consolidate the spreadsheets,” Nemy laments.

What’s more, the broadcast firm has a complex chargeout process whereby one department that uses the services of another is charged for that service.

“The result was that the budgets were never in balance, and we had to go through and manually check everything,” Nemy says. Querying financial data had to be done manually by researching individual spreadsheets.

After a year and a half on the job, having come to NCPB from Nextel, Nemy has solved the financial planning and consolidation problem, with a minimum of effort and change required by his staff and the rest of the organization. The change management issue was portent, because Nemy had seen how difficult it was to not only install new financial management software at other companies, but get sufficient user “buy-in” from the internal financial community to make the new system work

“It was a hassle because the people didn’t like having to learn new software, and whenever a new person came on, he or she had to be trained.” For example, he says that  Nextel had implemented a package called FYPlan. “It took a ton of coding time, we spent $3 million to implement the system, and we didn’t get good user feedback,” he says.

The biggest reason? The spreadsheet is the tool of choice for financial staff.

Department heads, budget officers, planners, and financial analysts are accustomed to using spreadsheets to perform budgeting and financial reporting.

At Nextel, Nemy was able to solve the problem by shelving the old system and putting in a different package called TM1. “This system was based on Excel for financial planning and consolidation, with an OLAP database” for BI queries, Nemy says. “We rolled it out nationwide and the company used it all the way up to the Nextel merger with Sprint.”

Nemy also had used a package called OutlookSoft when he was CFO at Foote Cone & Belding. Thus, when he joined Northern California Public Broadcasting, he considered using TM1 or OutlookSoft. Nemy decided to go with the latter package, which later was acquired by SAP in June 2007 and renamed SAP Business Planning and Consolidation. “We use it for complete business planning and rolling up our forecasts for the coming year,” Nemy explains.

Nemy had the company’s IT staff of 10 adapt the system to use an Excel front end. Thus, the beauty of the new system for spreadsheet addicts is that it doesn’t require them to go cold turkey—there’s no need for them to learn some totally new financial consolidation and budgeting package.

“Our developers customized the system to replicate the accounting system we had,” he says. “Now our new planning system replicates the way we’ve been doing business for a long time. Usually companies redesign their processes first and then put in the software, but we didn’t have the time to change all our processes, nor did we have the bench strength to retrain all our users.” 

One process that did change as a result of the new technology was the broadcasting firm’s planning regime. Nemy replaced the annual financial planning routine with a more timely and responsive rolling monthly forecast that can project both the balance of the year and the following 36 months. Each month financial planners and budget officers input their actual numbers into the spreadsheet, hit a button, and their numbers are automatically consolidated.

The biggest benefit to the CFO from this new system is greater transparency. For example, Nemy says the new system enabled him to project an impending $1 million shortfall against the budget following its acquisition of a San Jose TV station in October 2006.

“The management team took immediate action based on that forecast, initiating more pledge days, which brought in $750,000 more than we would have had,” Nemy says. The company was able to make other adjustments to manage through the resulting lesser shortfall. “To me, the system in that one instance already paid for itself,” the CFO adds.

When he arrived at the company, although it had a Microsoft Navision general ledger accounting system, Nemy says it lacked any means to match up actual income and expense figures year to date against what was budgeted, or to do accurate forecasts of financial performance for the remainder of the year.

“It met our reporting needs, but it didn’t give me the information I needed as a user—for instance, how are we doing with this particular group and how will it do in the future?” he says. “There was no way to take year-to-date actuals and look at variances from the plan. It’s important that our department heads have a bottoms-up look at how their plans have changed and how they are performing against them.”  

Northern California Public Broadcasting has about 35 financial system users, including the CEO. Besides Nemy’s financial staff of 10, the rest are mostly budget managers and department heads. All use OutlookSoft with an Excel front end. “This system is Excel,” says Nemy. “The only thing really different is that instead of saving the spreadsheet data, they press the send button and send the data to the database. Then when they open the spreadsheet again, they must first retrieve their numbers.”

“We had to overcome that initial fear some people had, that scary feeling they got of not seeing the numbers they’d put into the spreadsheet,” he admits.

One reason is that some power users of Excel tended to put their own financial formulas to perform specific calculations inside Excel to speed the filling out of budgets and other financial data. As a result some users worried they might lose these formulas, which they felt were essential to their work as budgeters and planners.

“We had to assure them that if they wrote the formula inside Excel, they would see the formula later when they retrieved it,” Nemy adds. No doubt the unique functionality afforded by the spreadsheet is the reason many financial organizations continue to use it, despite its shortcomings relative to more sophisticated financial management and business intelligence packages.   

With the Excel front end and the SAP Business Planning and Consolidation application (formerly OutlookSoft 5), Nemy believes he has the best of both worlds. For one thing, he likes having immediate access to any department or project budget. “This technology is absolutely essential to me in my job,” Nemy says. “The ability to pull up anyone’s budget, to immediately consolidate numbers, and to make an instant forecast of where we will be three months or six months out is extremely useful. We’re able to see things we couldn’t see before.”

For instance, any numbers that are significantly out of variance with the budgeted figures appear in either red (over budget) or green (under budget). “Whether it’s good or bad, I can see it right away and ask, ‘Where did this $400,000 come from?’” Nemy says.

Because Northern California Public Broadcasting is a nonprofit entity, the firm is not legally required to comply with the Sarbanes Oxley Act of 2002. Nonetheless, Nemy says this spring the company will seek to comply with the “Audit Risk Assessment Standards,” a set of financial standards applicable to nonprofits.