Crystal-Ball Glance Into Fiscal Future

Joel Jorgenson, financial planning and analysis manager at Ace Hardware Corp., would love to have a crystal ball to peer into the economic future. Lacking that, he's using financial-planning tools to forecast the hardware chain's sales one year ahead. Last month, mid-way through the year, sales were within .5% of forecasts made at the start of the year. "We haven't had sales so close to budget at this point of the year in five years," he says.

Budget planning and forecasting practices at most companies are inadequate in the best of times. Annual budgets are prepared far in advance, created through months-long processes that involve the laborious task of creating and consolidating large numbers of spreadsheets containing flimsy sales projections. By the time the year begins, the conditions upon which the budget was shakily built have changed and the financial plan is quickly forgotten.

Some businesses, however, are developing rolling revenue and spending forecasts 12, 15, even 18 months out that complement and even replace their annual budgets. (Rolling budgets are spending and revenue forecasts that are updated monthly or quarterly.) What's more, the accuracy of those forecasts is improving by involving more line managers, department heads, and other in-the-trenches employees in the planning process. Some are going further by leveraging data in sales-force automation and enterprise resource planning systems to boost forecast accuracy.

All this requires budget-planning and forecasting software that lets finance managers quickly collect, consolidate, and analyze financial information from all across a company. And the software has to be available to a broad range of department heads and line-of-business managers for them to contribute to the budget-planning process: The days of financial tools used by a handful of people in the CFO's office are in the past.

Until a year ago, Fossil Inc., a watch and fashion-accessory retailer in Richardson, Texas, manually consolidated sales and spending forecasts prepared in Excel spreadsheets by operational and product managers and sales directors. Pulling all those numbers together into a coherent budget left little time for top management to analyze the numbers, finance manager Christopher Lee says.

Last August, Fossil installed Cognos Inc.'s budgeting, forecasting, and financial-reporting applications. Department heads still fill out Excel budget forms, but those numbers now are loaded into Cognos applications that immediately generate a profit-and-loss forecast that financial executives scrutinize by product line, department, and distribution channel. The idea, Lee says, is that such analysis will boost Fossil's profitability through better expense management, improve factory order planning, and streamline project management for IT and other capital spending. The company used the data to prepare its annual budget and an 18-month rolling profit-and-loss forecast for the second half of 2002 and all of 2003. The new system has helped Fossil provide Wall Street with more accurate quarterly earnings forecasts. But with the system in use only a couple of quarters, Lee says it's too early to quantify the benefits.

Ace Hardware has been using software from Adaytum Inc. for three years to create budgets and began using its forecasting capabilities last year. Historical revenue and spending data is loaded into Adaytum from the company's J.D. Edwards & Co. general-ledger application. Sales managers use that information and their knowledge of individual stores to generate revenue forecasts while department heads create detailed expense forecasts. This is done monthly, compared with the quarterly forecasts created before using Excel. That arms top managers with the information they need to make decisions, such as whether to add employees or where and how much to cut expenses.

"Because they're more detailed, we have more confidence in the numbers we're submitting to our management team," Jorgenson says. "Our ultimate goal is to do an 18-month rolling forecast."

Finance managers doing the forecasting have more tools to aid them today. Comshare Inc., for example, added statistical forecasting to the latest release of its budget-planning and performance-management application. That capability lets managers take actual revenue and expense numbers from, say, the last six months and use them to forecast the next six months. Early this year, startup Armstrong Laing Group debuted its Predictive Planning for developing rolling budgets. By September, Adaytum Inc. will ship a new release of its Adaytum e.Planning business-planning application with enhanced forecasting capabilities.

VHA Inc., a for-profit cooperative purchasing organization for more than 2,200 community hospitals and health-care organizations, uses applications built on OutlookSoft Corp.'s Enterprise Analytical Portal to track the $350 million to $400 million in marketing fees it receives each year from suppliers of hospital supplies, equipment, and pharmaceuticals. Contract managers enter revenue forecasts into the system, which VHA financial managers use to develop reports for top management. They also develop monthly forecasts through the end of the year and annual forecasts three years out, says Mark Hebard, finance and system operations director.

RCN Corp. is using Hyperion Planning from Hyperion Solutions Corp. to create rolling 15-month budget forecasts that are updated every quarter. Before using Hyperion Planning, sales forecast data was collected, consolidated, and processed in Excel. "It was a nightmare," says Ernest Iocca, senior system developer and administrator. "Before, we were just getting the numbers in. No one was analyzing them." Now managers enter revenue and expenditure-forecast information directly into the Planning app or through Excel add-in software. The change has resulted in more accurate budget forecasts, and Iocca has reduced his staff from 15 to four. Top managers also can now see which sales managers have a better handle on their territory, Iocca says.

ABF Freight System Inc., a Fort Smith, Ark., shipping company, already has a rolling 15-month revenue forecast process, using Hyperion's Essbase online analytical-processor (OLAP) software and forecasting applications developed in-house, for planning facility, equipment, and staffing needs. Each month sales reps provide headquarters with sales forecasts for the next 15 months, while branch managers who run the company's 300 terminals forecast expenses. When combined and analyzed, the numbers show whether the company is over--or under--spending on buying new trucks or building and staffing new terminals to meet anticipated demand.

ABF has been using the process for less than a year and the rolling forecasts run in parallel with a traditional annual budget created as early as September for the following calendar year. "We think that's the old way of doing things," says controller Frank Ward. "Generally, by January (the budget) isn't worth anything." The rolling forecast is quickly replacing the annual budget, although he says it could be 20 years before the system is fully mature with enough historical data to accurately predict sales trends.

While it's certainly better for line managers and sales reps to make forecasts rather than financial managers back in the home office, they are still forecasts made by people. "These are the guys who know what's happening," Ward says of the sales reps and terminal managers. But he acknowledges the forecasts can be overly optimistic and finance managers sometimes must tweak the sales forecasts downward and cost forecasts upward.

"By nature, sales people are aggressive and optimistic," says Paul Murray, financial operations controller at Vastera Inc., a Dulles, Va., company that provides software and services for managing global trade. And salespeople often confuse sales quotas or goals with forecasts. Financial managers, however, have to create forecasts firmly grounded in reality. "Forecasting is such an art," Murray says. "If you really want to be accurate, you have to drill down to the root cause and the probability that you're going to actually book a sale."

To do that, Vastera developed its own budgeting application that runs on Applix Inc.'s TM1 OLAP database. Revenue stream data from a Softrax Corp. application that Vastera uses to manage its multi-year contracts with its clients is added to the database. Vastera managers even tap into sales-tracking and lead-generation applications from Onyx Software Corp. to gauge the sales pipeline and get a firm idea about how many potential deals are likely to generate revenue.

Murray says the next step is to automate this process by linking these operational and budget planning applications. In the future, a Web interface will be added to the TM1 forecasting database, providing managers outside the finance department with access to the forecast information.

Fossil has similar plans to collect revenue data from its SAP applications and use it for budget-forecasting purposes. "That is a much richer, more consistent data source," finance manager Lee says.

Dental equipment maker A-dec Inc. in Newberg, Ore., is using Essbase and Comshare Decision software from Comshare to improve sales forecasts for its products and synch production and finished-goods inventory with demand. Historical sales data is pulled from the company's Baan financial applications, loaded into Essbase, and put on a Web site. A-dec's 80 territory and regional sales managers use Comshare Decision to create sales forecasts based on historical data and their knowledge of their markets. The forecasts go back into Essbase and are used to plan production.

"In the past 18 months, we've maintained a very consistent lead time, with less than 10% fluctuation, compared with 30% to 40% lead fluctuation before," says CIO Keith Bearden. The shorter lead time has helped the company boost sales 18% and gain market share, he says.

Kana Inc. in Menlo Park, Calif., uses its own customer-relationship management applications to run its business. That includes tapping into the software's contact-management and opportunity-forecasting functions to help foresee which sales deals will close when and begin generating revenue. That information is combined with expense and revenue data from the financial-planning software from Closedloop Solutions Inc. to provide Kana with a window into its financial future.

ABF Freight's Ward is thinking even bigger. He foresees a day when ABF's forecasting system is tied directly into its customers' forecasting systems, thus improving ABF's forecasting abilities. When a customer's system foresees a jump in shipments several months down the road, ABF can plan accordingly, he says.