ScottishPower operates in the United Kingdom through several publicly listed subsidiaries, and is part of the Iberdrola Group, a multinational company present in 40 countries, one of the world’s top energy companies and the world leader in wind energy.
Without Magnitude Kalido), it would be extremely difficult to speed up our monthly reporting, let alone keep pace with business changes and new reporting requirements. Our staff would likely still be tied down consolidating spreadsheets.
Deon Dell-Robertson, Systems Accountant and Project Manager
ScottishPower maintains a single general ledger and chart of accounts, but had to issue monthly management reports. The Company undertook an overhaul of its “Excel as database” financial reporting system. The initiative, called IMAS, was intended to replace a tangle of over 200 spreadsheets in order to streamline the monthly closing, which historically took 30 finance professionals seven to eight days to complete.
Deon Dell-Robertson, Senior Systems Accountant, served as Business Analyst and System Designer on the IMAS initiative from the outset. His role was to gather requirements, evaluate software vendors, and drive progress through implementation and solution go-live.
Challenges Included “Massive Manual ETL”
Every month, ScottishPower had to reenact a difficult and complicated financial closing process. The finance department had evolved Excel into the role of an enterprise database; its mosaic of 200-plus spreadsheets drew from over 200 data feeds containing over 1,300 different data elements. According to Dell-Robertson, working this spreadsheet system through some 2,500 calculations going back 13 months kept the finance professionals fully occupied “for at least one week just producing numbers, with no time to analyze or digest them. Some of the calculations took 2 days to refresh once we kicked off the process. To complete them was a massive exercise in manual ETL."
ScottishPower’s implementation of Magnitude Kalido Dynamic information Warehouse (DIW) began with the contract outsourced to a third-party, but the utility’s circumstances changed and ScottishPower took over with its internal staff and drew on outside expertise as needed to undertake the system build.
“We were able to undertake the Magnitude implementation with targeted use of outside consultants. Five of our people worked on IMAS full-time, along with two from Magnitude and eight from a software contractor.”
All 200-plus modeling/processing spreadsheets were replaced by a model-driven Magnitude data warehouse; although ScottishPower still receives Excel and text CSV files from various areas of the business, Tibco is utilized to process these files into Magnitude Kalido DIW. The Magnitude warehouse is applied across all of ScottishPower’s financial work streams, which correspond to the company’s main processes: wholesale power, wholesale gas, retail power, retail gas, emissions and coal.
The output from the data warehouse, which at the time had roughly 2.5 million records, feeds journals into SAP and some 60 BusinessObjects reports which are used for margin calculation and ad-hoc reporting.
ScottishPower's monthly closing timeline was cut in half from eight days to four.
The amount of modeling/processing spreadsheets replaced by a model-driven Magnitude data warehouse
Monthly closing is considerably streamlined, taking 25 people about four days to carry out. Rather than consolidating data feeds, they are freed up to analyze the information and trends.
“The data warehouse delivers more information to the Margins team, which is able to undertake ad hoc queries, and drill down into calculations where needed,” says Dell- Robertson.
Magnitude has made the time-consuming process of calculating financial margins into a much easier, faster and efficient process. Now the data feeds and calculations are handled in a controlled and easily auditable environment. “We instituted a stringent approach and set procedures for changing data in Magnitude Kalido,” explains Dell-Robertson.
The Finance users are able to handle data structure changes independently—without waiting for intervention from IT—and this has proven beneficial, as they are able to keep IMAS in step with the constantly changing business and reporting requirements.
The Magnitude project quickly led to higher confidence and trust, eliminating manual manipulation errors and performing a controlled and replicable process each month.
ScottishPower structured its Magnitude warehouse for flexibility, because the calculations it must perform to calculate margin change fairly frequently. Now ScottishPower management can be more responsive to business changes. “We’ve developed a good design that ensures that modifications are made easy,” said Dell-Robertson.
Modifications and new journal entries are routinely handled by just two super-users. Magnitude Kalido DIW provides a reliable, structured and consistent source of data to feed ScottishPower’s calculations and the resulting BusinessObjects reports.
The better-governed data is helpful in driving budgets and forecasts which require comparisons to prior-year data.
ScottishPower’s parent company, Iberdrola, subsequently introduced a new accounting system. The coding scheme of the Magnitude warehouse had to be remapped to ensure that its outputs conformed to Iberdrola’s coding structure. With Magnitude’s adaptive capability, the model-based data warehouse was updated easily. Dell-Robertson commented, “We had to change. Without Magnitude we could not meet Iberdrola’s timeline.”
A Better, More Intelligent Work Day
The accessibility to consistent information that Magnitude Kalido enables has led to more ad hoc, analytical queries. It has also released the finance staff from the need to be “experts” in dozens of spreadsheets that received the data feeds and performed calculations. This in turn enabled these 30-odd professionals to widen their scope of involvement to handle more aspects of the data and reports.
“With this flexibility, we can better cover when someone goes on holiday or maternity leave,” says Dell-Robertson. “But the transition has been more than that. Our people can see the overall processes, and move more easily from one role to another. Their jobs have become less restricted, they are able to operate with a wider scope and some of the stress involved with producing the monthly numbers has been eased.”
He adds, “There are at least three to four more days per month when the entire team can focus on evaluating and analyzing results instead of having each person pick through their half-dozen spreadsheets.”
Today ScottishPower loads directly into Magnitude Kalido DIW from hundreds of sources. The gains from implementing a model-based data warehouse include the expected simplicity and reliability that come with retiring hundreds of hard-to-audit spreadsheets. Monthly closings are handled in about half the time and are more reliable with consistent output across the department.
ScottishPower enjoyed an unexpected benefit as well: its finance employees have more flexibility, wider operating scope, and less stress. Magnitude provides a robust base for some 60 standard reports created with BusinessObjects software, and enables further ad-hoc analysis. Perhaps most importantly over the long term, the finance department can adapt immediately to changes in methods and reporting.