Excel Service Enabled Internal Portal to Facilitate Sales Data Collection and Visualization

Situation

The customer is a leading multinational company that provides document management technology and services. Its revenue of the last year reached $17.6 billion. The company provides with document industry's broadest portfolio of offerings, categorized in office products, production equipments, and document services. The company’s subsidiary company in China was registered in 1995.

With its headquarters in Beijing, the company has 16 branches in different Chinese cities, including Shanghai, Guangzhou, Chengdu, Shenzhen, Shenyang, Wuhan, Hangzhou, Tianjin, Dalian, Qingdao and Nanjing. Besides, it operates two factories in Shanghai and Shenzhen.

The company needed an internal portal which would allow its nationwide sales teams to manage and share sales data on a single and efficient platform, in order to effectively monitor sales performance and achieve focused sales goals. MDT InfoTech was chosen to provide and implement a solution based on Microsoft SharePoint Service 3.0. The portal was expected to centralize all the sales data and visualize the progress made toward sales goals to company decision makers.

Inefficiency of Excel Spreadsheet Based Data Collection

The company’s national sales teams are geographically dispersed in different regions and cities of China. A sales person reports to the regional sales manager. Sales managers in different regions report to sales director located in Beijing. An assistant is responsible for gathering various sales data from each region to sales director on a quarterly basis. The following diagram shows the team report-to structure:

“We had used an Excel template to record and calculate various sales data. I regularly gathered reports from each sales person and regional sales manager, usually by email” says Shi Yi, assistant of sales director at the company. “A big trouble for me is that I have to manually copy the data from each individual Excel sheet and paste them into a master Excel sheet for senior managers’ review. The workload was huge and I have to examine each data carefully to avoid possible mistakes and typos”.

As the Excel template is very complicated – it contains more than ten sheets and some sheets had nearly one hundred fields, Shi Yi sometimes had to spend several weeks completing the data collection. If a report contained incorrect data, Shi Yi would send back the document to the sales person for correction. Emails sent back and forth even delayed the whole reporting process. It was also a headache for the sales team. As the Excel template only has very basic data verification function, each sales person had to pay high attention to data accuracy when filling the complicated Excel template.

The Sales Management System (XSales) project is designed based on Windows 2003 + WSS 3.0 + IIS6 + SQL Server 2005 Express Edition + .Net Frame work 3.5. Windows SharePoint Service 3.0 is deployed as the platform of XSales system, and SQL Server 2005 Express Edition used as the database. All functions of XSales are implemented through different Web Parts. These web parts do not access database directly, but through web services. Web services and web parts are implemented as SharePoint Feature. The following diagrams demonstrates the high-level architecture design of XSales system:

SQL Server and SharePoint Service are deployed on same computer. Due to the company's IT policy, this server is not published to Internet. Users out of the LAN network connect to Intranet by Virtual Private Network to access XSales portal. All users are required to have domain accounts to access XSales system. Anonymous access will be denied:

SharePoint Excel Service Leveraged to Enable Sales Data Collection and Visualization

In XSales system, MDT InfoTech leveraged SharePoint Excel Service to collect sales data. Excel Services is a SharePoint technology that makes it simple to share and manage Excel 2007 workbooks as interactive reports in a consistent way throughout the enterprise. Excel service, which is built on ASP.NET and WSS 3.0 technologies, empowers the users to publish and run spreadsheets on the server which enables the users to access these spreadsheets through a web browser without need of custom browser component. Following are the core components of the Excel Services used in XSales system:

• Excel web services
• Excel web access
• Excel calculation services

Excel web access is a web part that displays data and chart Excel workbook with the same appearance. The sheet tabs, outline buttons and drop down arrows in excel web access can be customized in different ways.

Excel web service is hosted in SharePoint Service that provides several methods that a developer can use as an application programming interface to build custom applications based on the Excel workbook.

Excel calculation services load workbooks and calculate it, refreshes external data and maintains the sessions.

Through the XSales system that leverages Excel Service, sales persons are able to complete their weekly reports easily from the web interface. The system captures key data such as the person’s ID, progress towards scheduled sales goals, successful sales projects, etc. Additional reports summarize sales by key variables, such as region, product line, sales calls per week, and the number of closed sales opportunities. When individual sales person completes the online reports, their regional sales manager and sales director can immediately see their sales numbers through the portal.

Major Features of XSales System

• Easy maintenance of basic sales information, including industry type of customers, source of sales leads, competitors in a sales project, service line related to a sales order, products related to a sales order, etc.
• Sales information management, including user ID, performance history of individual sales persons and managers.
• Sales goals management, scheduling and tracing sales goals during a time frame for each sales individuals and regional teams.
• Sales contract management, including adding, modifying, and deleting information of each sales contract. A contract contains various information including customer name, contract type, payment status, etc.
• Reporting, sales managers and sales director are able to view various sales reports generated from subordinates’ sales data. They can easily select filters to verify performance based on team, type of contract, sales revenue, etc.
• Version control, each change made to the data in the system is logged and can be reverted when necessary.