DMS MS Excel Automation & Solving the Problem with Spreadsheet-Based Models

Eliminate the hassle and problems associated with large complex Excel models while maintaining Excel's ease of use and functionality.

Are Excel spreadsheets used in your company? Spreadsheets, although a powerful personal application, can have serious repercussions -- even when used properly. Spreadsheets empower employees to apply creative ideas that can have beneficial effects on productivity and the sharing of knowledge within a company. Unfortunately, the proliferation of spreadsheet models that have outgrown their original intent or their manageability often have a negative effect on an organization.

Where does Excel, well, excel?

Excel places its power in the hands of a decision maker: Employees hired to make decisions, the knowledge workers, need tools and Excel is one of the best. They can create simple to complex models quickly to provide themselves with better information.
The proving ground: Many data management and modeling ideas can be tested and implemented quickly by a knowledgeable person in a spreadsheet application. Spreadsheets can be used as a powerful proof of concept.
Increase the power: Once proof of concept is accomplished, the idea can be easily expanded within Excel in order to deliver increased benefit for a user. This approach eliminates the need for hiring a software developer or initiating an involved software development process.

Excel is an excellent tool for one person or a small team that works very closely together. Spreadsheet models can be quickly produced and adapted to provide better information.

Where do spreadsheets fail?

Sharing an Excel model does not share knowledge: A complex spreadsheet model is well known to only one or a few employees. These employees have taken their time and placed their knowledge into the model. The authors of the model know how to enter data and deal with the nuances found within every spreadsheet; others within the same company may not be capable of using the model. Providing other employees with the same model, does not ensure that employees share their knowledge and can result in erroneous information.
Data needs to be re-keyed to be used: One of the strengths of a common system is that once data enters the system, it can be used anywhere - without re-keying. Even spreadsheets that generate excellent information are flawed if the data sent to others in the company must be reentered into other spreadsheets or systems. Every time data is manually entered, there is an element of risk of the data being corrupted.
Copy and Pasting can create significant errors: Everyone saves time by copying and pasting significant amounts of information. Within a spreadsheet, this can create unseen errors due to the use of range names or the existence of hidden rows or columns. In many models, Excel tags data and cells in ways not obvious to a user. Cutting and pasting can easily bring in hidden values and references without a user's knowledge which can significantly corrupt a model.
Error proliferation and continuance: Powerful spreadsheet models grow over a long period of time and may involve several authors. Links and references grow to be elaborate and difficult to debug; errors become imbedded and are nearly impossible to find. As spreadsheets are copied and distributed to support additional projects, corrections of imbedded errors are virtually impossible to correct on all spreadsheets introducing an element of uncertainty in every model. In addition, enhancements to models for some projects are not implemented to all models.
Excel is not a database: While an Excel spreadsheet has some database look and feel it is not a database. Good database structure is virtually impossible in any spreadsheet-based model.
Meaningful data mining of Excel models is nearly possible: Information from Excel models remain in the Excel files. Combining individual files in a meaningful and productive manner is nearly impossible. Also, if any model has been changed, chances are that not all files are the same and even produce similar information.
Excel models are easily stolen or lost: Excel files can be easily copied and distributed. Some authors of complicated spreadsheet models take their work with them when they leave a company. If an employee does leave the company, all the effort that person expended in creating their models is gone. Their replacement often must start at the very beginning to learn the needs of the job and then develop their own models.
Problems with spreadsheet-based models are typically found when the application is used outside of the areas of maximum benefit. When a model is too complicated, there are too many people involved, it is used over a long period of time without verification, or is designed by someone not trained in the shortcomings of spreadsheets, the information the spreadsheet-based model produces must be suspect. Additionally, there may be no documentation, backup procedures or other basic security for a tool that is essential to an ongoing business process.

The DMS approach: The best of spreadsheets and code-based models.

Excel has many benefits and should be part of any knowledge employee's tools to create information valuable to their job. The part of Excel that is tedious, the maintenance, the enhancements of each outstanding spreadsheet, the verification required and the security that is necessary can be replaced and enhanced by a centralized and robust model in code.

DMS has assisted several companies to achieve a better process than one that relies on spreadsheet-based models. Established, business processes are modeled in code and the corresponding data is placed into a database. Data from other sources can also be retrieved electronically eliminating the need for reentry.

The output, or reports, from the system can be on the screen, an Internet browser or dynamically created Excel spreadsheets. These dynamically created spreadsheets add rows and columns as required producing a report with or without formulas - depending on the recipient. Once in a spreadsheet, the information can be verified and, as a powerful component to this process, employees can experiment with the spreadsheet model and have better ideas incorporated into the code model.

Benefits of the DMS Approach:

One model: Using one model, the knowledge of a few employees is shared throughout the company. Also, as the model is enhanced, all users benefit as there is no need to chase down all outstanding copies of a spreadsheet-based model.
Stays in the company: If one or more of the employees who contributed their knowledge to the model were to leave, the model (and their knowledge) remains with the company. In addition, if the employee were hired by a competitor, it is impossible to take the model with them.
Retains creativity: The use of a code model and Excel for reports does not stifle the creative process as employees can still modify a spreadsheet report to test new ideas.
Allows for Verification: Using a spreadsheet with its inherent capabilities, makes it easier to verify output than any other means. The verification of a static report would require the placement of that data into a spreadsheet - the DMS approach makes this step part of the process.
Administration: Documentation, backing up data and security are all possible using the DMS approach. A code-based model can have a simple administration interface to control access and maintain the integrity of the system.

The DMS approach adds the power of a relational database to a model producing the best of both worlds.

Proven modeling (errors in models are minimized in the coding process).
Continually improving models.
Database storage of information for all projects. When a model is improved, past projects can be "rerun" to take advantage of the new knowledge in the model.
User-friendly interface to add data.
Data mining of all data.
Easy sharing of all data, knowledge and resulting information.
Easy to merge or access data from multiple sources.
Elimination of errors due to data merging in spreadsheets.
Eliminates re-keying of data.