Sonae is a Portuguese company that operates food chains and retail stores. Sonae needed to improve its process for sourcing products in international markets. The company relied on formulas in spreadsheets to calculate costs, but the spreadsheets soon became difficult to manage. Maintaining version control, data consistency, and security was challenging. Sonae used Excel Services in Microsoft Office SharePoint Server 2007 to create a product called PowerSheets, which takes advantage of centrally managed spreadsheets to help users access data and share information to react quickly to changing market conditions. Now, the IT department can ensure higher data accuracy. Efficiency among users has risen, and by improving the quality of its business intelligence, Sonae makes better purchasing decisions, which contributes to lower costs and higher profit margins.
Sonae is a privately held Portuguese company with approximately 35,000 employees and 800 stores. It operates retail food chains that include hypermarkets (large-scale supermarkets), mini-hypermarkets, supermarkets, coffee shops, health and beauty stores, as well as nonfood retail stores that sell consumer goods, such as sporting goods, computers and electronics, apparel, and do-it-yourself garden and construction supplies. Sonae also operates travel agencies, a telecommunications provider, and books and stationery stores. The company is a subsidiary of the Sonae Group and, in 2008, its retail sales amounted to EUR €4.2 million (U.S.$6.1 million).
As a major retailer, Sonae sources products internationally and relies on a forecasting or "cost-simulation" system that analyzes all costs incurred in the process of purchasing items wholesale and transporting them to Sonae stores. Costs include the actual price of the item, as well as shipping, taxes, and other fees. In 2004, the company formed an International Trade department dedicated solely to managing the sourcing process and ensuring its efficiency to bring the highest possible value to customers.
||With Excel Services and PowerSheets, our users can react to change because they have better business intelligence.
International Trade Manager, Sonae
There are approximately 200 traders at Sonae who are responsible for conducting cost simulations and purchasing goods. These employees relied on custom-developed formulas, stored in Microsoft Office Excel 2003 spreadsheets, to run approximately 30,000 cost simulations each year. The formulas helped the product managers make informed decisions and find the best deals possible by factoring in a range of variables. Marcelo Tupan, IT Director at Sonae, says, “Users continually enriched their own spreadsheets, making them more complex, and ultimately more useful and precise in the results they returned. They really liked Office Excel 2003. It allowed them to build new formulas and modify the spreadsheets with autonomy.” However, because users managed and maintained their spreadsheets individually, storing them on their local computers or in folders on the company’s corporate network, the consistency of the data and the results produced were unreliable.
The effects of this autonomy affected the accuracy of decisions being made. Users had the freedom to change formulas and business rules in their copies of the spreadsheet-based simulation tool. But because there was no system in place to ensure that the modifications were implemented as standard practice, the consistency and accuracy of the data being generated could be compromised. “Office Excel is a fantastic tool with lots of possibilities. However, from a systems and architecture point of view, the problems created by its growth became unmanageable,” says Tupan.
There was also scant opportunity for one manager to share information with another manager to get better deals on products or reduce certain costs. The spreadsheets could not be easily cross-referenced by others who were conducting similar cost simulations. For example, Sonae can realize significant savings on transportation costs if a shipping container is shipped completely full. But because information wasn’t shared or consolidated, two different product managers may have sourced products from the same region and shipped their items in separate, less-than-full containers. “Resources were wasted, and less value was passed on to the customer,” explains Adelino Miranda, International Trade Manager at Sonae.
To run the cost simulations, product managers also drew on data from the company’s business systems, including its enterprise resource planning (ERP) system, which contains a wealth of information about regularly purchased items, suppliers, tax rates, and shipping. The cost-simulation process wasn’t connected to the ERP system, so users had to manually enter high volumes of data into their spreadsheets. “These were hugely time-consuming tasks” says Tupan.
The company was limited in how quickly it could react to changing business conditions. “We may source an item to be sold at Christmas, eight months earlier, in April,” explains Miranda. “If conditions change—for instance, fuel prices rise in a particular country—a static spreadsheet on a user’s computer doesn’t know this. In modern retail, with very tight margins, a cost difference of even a few cents might have a great impact on results.”
Finally, because the spreadsheets, and the data being analyzed, were not centrally managed, they were also not protected by corporate data security policies. The business-critical competitive information contained in the spreadsheets was at risk of loss or theft as was the quality of the cost simulation data and the formulas used.
In 2007, Sonae decided to improve the cost-simulation process. Tupan says, “The process needed to be light in terms of resources consumed, highly flexible, and iterative. We needed cost simulations to help us react quickly to changes in the business climate.”
||Office Excel is a fantastic tool with lots of possibilities. However, from a systems and architecture point of view, the problems created by its growth became unmanageable.
IT Director, Sonae
Sonae embarked on a two-month procurement process, in which the IT department worked with the International Trade department to analyze the cost simulations, including all of the data that went into the calculations, where it came from, what type of data transformation was required, and how the data could be reused to inform a range of other business processes. “Initially, we considered developing a solution from scratch,” says João Piedade, Product Manager at the software products provider Tlantic, one of Sonae’s main IT partners. “But this would have been costly, would have taken a long time to implement, and wouldn’t have provided the flexibility that users needed. Also, Sonae already had years of knowledge invested in Office Excel. We didn’t want to throw away a successful tool that business users knew and loved. We wanted to build on and enhance the fundamental capabilities of Office Excel.”
Sonae evaluated Microsoft Office SharePoint Server 2007. The IT department was familiar with the product because it had used it as the foundation for the company’s employee intranet and its supplier portal. Specifically, the IT department evaluated Excel Services, a technology in Office SharePoint Server 2007, as the solution for revamping the cost-simulation process. “As we looked at all the data and how it was used, it became obvious that Excel Services in Office SharePoint Server 2007 was part of the answer,” says Piedade. “These products allowed us to bridge the gap between what our users needed—flexible tools with which they could manipulate data easily—and what the IT department needed—security, version control, and data consistency.”
In early 2008, with the help of Microsoft Services, Tlantic, and another Portuguese partner, Sonae conducted a brief pilot implementation of a cost-simulation process built using Excel Services. As a component of Office SharePoint Server 2007, Excel Services makes it simpler to use, share, and manage Office Excel workbooks in a more secure and consistent way throughout the enterprise. Sonae enhanced the Office SharePoint Server 2007 environment by developing client-server components that provide additional functionality, such as automatic version updating, automated cell and worksheet protection, collaborative concurrent work, sequential fill-in, data integration, background processing, and more.
This became the origin of a Tlantic-owned product named PowerSheets, which takes advantage of Office Excel spreadsheets and the robust technology of Office SharePoint Server 2007 to address users’ need for autonomy and flexibility, while meeting security, efficiency, and data consistency requirements. PowerSheets uses Office Excel spreadsheets as part of the IT architecture, integrating them with existing core systems.
The development team used Microsoft Visual Studio 2008 Team System and Microsoft Visual Studio 2008 Team Foundation Server to customize the business logic within the server-based Office Excel spreadsheets. Sonae upgraded from Office Excel 2003 to Office Excel 2007. The spreadsheets are supported by a database running the Microsoft SQL Server 2008 data management software.
Users have the option of opening an Office Excel 2007 spreadsheet from a document list on a SharePoint site or they can launch a local version of Office Excel 2007 on their computers, and PowerSheets automatically synchronizes the version of the spreadsheet from the server.
In early 2009, the solution was deployed to the International Trade department and traders responsible for sourcing goods internationally. Immediately successful, it became clear that the PowerSheets solution could be replicated to other areas of the business with similar results. The IT department has already implemented the solution for other prominent business processes within the company. For instance, Sonae employees use the server-based spreadsheets to negotiate with suppliers in regard to marketing activities, promotions, shelf-space, and discounts for goods regularly stocked at Sonae stores.
“Users can now easily draw from historical data and factor in the company’s objectives to determine a particular item’s optimal price point. They can see how much we have purchased from a supplier in the past and see what our goals are for the current negotiation. For instance, last year the company might have accomplished a 7 percent profit margin on a product, but this year the goal is 8 percent,” explains Miranda. “Now, users can run cost simulations that factor in many variables, and the data returned helps them negotiate and close the deal.”
Using Excel Services in Office SharePoint Server 2007 and PowerSheets, Sonae has revitalized its cost-simulation process. Now, business users have real-time access, with proper security, to updated information that is factored in to their cost simulations. And, because the cost-simulation spreadsheets are centrally managed, the IT department can implement version control, enhance the security of confidential data, and allow users to modify aspects of the solution while maintaining the overall integrity of the system and the data it contains.
Capitalizes on Existing Knowledge
With Excel Services and PowerSheets, Sonae is taking advantage of the skills its business users already possess. “Office Excel was a starting point,” says Piedade. “With this solution, which is based on Excel Services and Office SharePoint Server 2007, we were able to add the server element to the solution, which allowed us to provide more structure, centralize management, enforce rules, and automate certain tasks.” Sonae believes that its familiarity with Office Excel and Office SharePoint Server, and the fact that it did not have to engage in a highly customized development process, has led to a much less costly solution.
Supports Higher Efficiency
The new solution helps increase the speed and reliability of the business processes involved in international trade. Miranda says, “With PowerSheets, people work more efficiently because their time is not spent on quality assurance of data, but rather on what they were hired to do, which is sourcing products.” He adds, “Users can run individual cost simulations approximately twice as fast as they could before we implemented the PowerSheets solution, and there is still room for improvement.”
||Users can now easily draw from historical data and factor in the company’s objectives to determine a particular item’s optimal price point.
International Trade Manager, Sonae
Enables Better Business Decisions
With the ability to import data from the company’s ERP software, and because the spreadsheets are continually synchronized with updated information on the server, Sonae’s users have access to higher-quality business intelligence. This results in better business decisions being made by the company’s product managers. Miranda says, “With Excel Services and PowerSheets, our users can react to change because they have better business intelligence.”
Improves Data Accuracy and Security
When a Sonae user changes the data in an Office Excel spreadsheet and saves it to a document list in Office SharePoint Server 2007, the new data is made available to all users. “Excel Services and PowerSheets ensure that all product managers are using the most current version of the spreadsheet. We now have one version of the truth,” says Tupan. “The automatic synchronization takes care of 99 percent of the maintenance and management that used to be required. Business users who manage the template of the simulator can perform changes to the formulas themselves, without assistance from the IT department. This will result in significant cost reductions.”
Finally, with the Excel Services and Office SharePoint Server 2007–based solution, the IT department can better ensure that critical data, such as competitive pricing information or the formulas behind the cost simulations, is not shared outside the company. “The IT department’s administrators now have control over how information is stored, secured, and shared,” says Piedade. “This is a huge change over our previous system in which spreadsheets were stored locally on users’ computers. Implementing the Excel Services–based solution has helped us to protect our cost-simulation data and formulas.”
Tupan adds, “We’re looking forward to implementing this Office SharePoint Server 2007–based solution to many other areas of the business. The value it provides is universal.”
Microsoft Office System
The Microsoft Office system is the business world’s chosen environment for information work, providing the programs, servers, and services that help you succeed by transforming information into impact.
For more information about the Microsoft Office system, go to:
For More Information
For more information about Microsoft products and services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Information Centre at (877) 568-2495. Customers in the United States and Canada who are deaf or hard-of-hearing can reach Microsoft text telephone (TTY/TDD) services at (800) 892-5234. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information using the World Wide Web, go to:
For more information about Tlantic products and services, call +5551 3320 1600 or visit the Web site at:
For more information about Sonae products and services, call 351 229 561 943 or visit the Web site at: