Estimate-To-Complete

Symbol Technologies
Holtsville, NY USA

Symbol Technologies uses the Estimate-To-Complete (ETC) tool for their plan managers to plan, track, analyze and predict the total cost of their project at any time until project completion. This helps them stay on time and within budget.

Symbol Technologies allocates resources, such as employees, materials, equipment, and overhead, to various divisions, cost centers, disciplines and activity codes. At the start of a new project, the plan manager for the project specifies the plan details, selects appropriate resources, and inputs in their estimated time (in hours) and cost (in dollars). ETC provides them with a running cumulative total of various costs and overhead rates based on the centralized database for each unique combination of division, cost center, discipline, and activity code.

As a further complication, projects can span a calendar year boundary, and the rates for various resources are set yearly and change from one year to the next. If the company reorganizes, departments may move into different cost centers, and ETC must handle the analysis and project planning automatically.

As projects are approved and go live, the actual time and cost data flows into ETC from the company's SAP accounting system. As the accounting months are closed, the plan manager loses the ability to make changes for that month and can make changes for future months only.

When the actual data comes in from SAP, various anomalies can occur which may be logged for review or may be handled automatically. For example, a plan manager may not have allocated any forecasted time or cost for certain resources. However, if the actuals for that resource are assigned to a particular project plan, ETC automatically creates the missing detail lines, allowing plan managers to review more accurate project detail. If some rates are missing for a particular year, an exception report will show the details.

ETC allows plan managers to copy existing project plans as a starting point for their next project. Some projects can take a full week to plan since plan managers are evaluated on their accurate planning and finishing the project within the allocated budget. At any time, plan managers can view a snapshot of their plan with the actual time and cost data (from SAP) and forecasts (from plan managers) to prepare reports and variance analysis.

Moreover, because the Finance department uses Microsoft Excel for most of their analysis, the ETC tool looks and feels like an Excel spreadsheet with row highlighting, instant calculations, the ability to shift columns, and the ability to enter comments at a specific cell, to name just a few of ETC's features.

Application size and scope

To allow the application to be flexible, the database is normalized to the third level. The complete application uses approximately 40 tables. Because there can be more than 10,000 unique combinations of division, cost center, disciplines and activity types for each plan, some of the tables, especially those tracking forecasts and actuals, can easily reach one million records.

When the ETC tool went live, most project plans had 50 to 100 plan detail lines, but after a year of using ETC and the auto-fill feature of the tool, most plans now have 200 to 400 plan detail lines. This allows the plan managers to get a complete picture of their project and make necessary changes to stay within budget.

Most project managers work within 10 to 12 very feature-rich web pages in ETC. For example, ten different reports can be executed for various combinations of division, cost center, discipline, year, project manager, etc. Most reports can be exported to Microsoft Excel (raw data) or sent to a PDF file. In addition, we integrated the Xceed Chart controls into ETC so project managers can see a graphical representation of their projects.

The project

The ETC tool is an enterprise application which is used by all plan managers throughout the company. Because making a Microsoft Excel-like grid with financial accuracy was a core requirement, the ETC project took around six months for a team of five from start to finish. A lot of JavaScript was written to implement the spreadsheet grid component. Triggers were used to update and maintain the financial tracking aspects of ETC.

The development team consisted of:

	Project Manager — Overall management of project
	SQL Developer — SQL Design, SQL stored procedure development, database trigger development, and conversion of old ETC data
	Iron Speed Designer Developer — Develop the application and enhance the user interface to make the ETC tool easy to use
	JavaScript / HTML Expert — In order to allow the project managers to see the final numbers as they are planning, it was necessary to create a lot of JavaScript with many variables to save rates and overheads while opening the grid
	Graphic Designer / Test User — Design the application graphics and test the application for financial accuracy

Code extensions and customizations

The major customizations included:

	Integrating the tabbed control in the Iron Speed Designer design theme templates
	Maintaining the vertical and horizontal position after a postback
	Implementing type-ahead in dropdown list boxes
	Changing the namespace from the default value

To make plan building a little easier and to reduce detail record duplication, a new concept was introduced where the valid combinations of division, cost center, discipline, and activity types are shown. The project manager can narrow them down or search for them. In addition, any selected combination is shown in a different color than the ones available to be selected.

In addition, the user may select his preferred page size and all tables (grids) are adjusted accordingly.

Page layout customizations

An application-specific design theme was designed to reduce the number of table tags in the final ASPX pages. The color scheme and page designs were modified to align with Symbol's corporate application guidelines.

Metrics for success

The plan managers are quite satisfied with ETC since they now get accurate financial numbers for their plans. After a slow learning curve, most of the plan managers accepted the new ETC tool. Some features, like copying an existing plan to create a new one, save the plan managers a lot of time.

During development, a lot of change requests from the plan managers made it difficult to rearrange various screens for consistency.

Iron Speed Designer impact

As we made design changes in ETC based on feedback from the project managers, development was a lot easier because Iron Speed Designer accommodated the underlying database changes. And once the design theme template was designed, updating all the supporting tables and corresponding web pages was very straightforward. We were able to focus on business process rather than worrying about changes not being applied to every screen for consistency.

The classes created by Iron Speed Designer for most of the tables and views proved to be a blessing as we never wrote a single command to get or update any of the values. Instead, all the changes were made using the application classes.

Next steps

Since the numbers of detail lines for a given plan now exceed 200, the next phase of the ETC project is under development where the plan grid is designed using web services with AJAX.

Visit www.symbol.com.

About the developer

Master of Sciences — Northwestern University
MCSE+I, MCSD, MCDBA, MCT

Akesh Gupta is co-founder of ExecuTrain of Long Island and Light Speed Solutions. He is passionate about technology and loves to share his knowledge with others. ExecuTrain of Long Island is a corporate training company specializing in bringing real-world knowledge to the training room. Light Speed Solutions is a professional consulting and software development company that focuses on turning business vision into reality. Akesh Gupta has over 14 years of experience in architecting, designing and developing successful applications. Light Speed Solutions has become a one-stop solutions center offering advanced, diversified expertise in all facets of development, with complementary expertise in systems, reporting and accounting.