Go To TechNet Home Page

MS Excel Development in the Real World

by Don L. Powells

ABSTRACT: Micro Modeling Associates, Inc. (MMA) uses Microsoft Excel to develop real-world business solutions for financial clients. In a series of questions and answers, MMA Director in charge of Excel development Rob Erman explains how MMA evaluates the tools to use when creating business solutions, why it often decides on Microsoft Excel, and how it uses Excel and its features and functionality.

Introduction

Around the corner from the World Trade Center in New York City is one of the largest Microsoft Office application development shops in the world: Micro Modeling Associates, Inc. MMA has approximately 75 Microsoft Office developers on staff, about one-third of whom concentrate on Microsoft Excel. MMA caters to the needs of some of the best and brightest on Wall Street and throughout the Fortune 500. Their client list includes Merrill Lynch, Bankers Trust, PaineWebber, Ernst and Young, and 8 of the top 20 Fortune 500 firms.

Rob Erman, MMA Director in charge of Excel development, graciously offered to explain why and how MMA uses Microsoft Excel to develop applications by providing thorough answers to these questions:

· Why use Microsoft Excel as a development environment over other products?
· What is an ideal Microsoft Excel application? What components commonly apply?
· What steps create a good Microsoft Excel application?
· What other development tools do you use with Microsoft Excel?
· How do you integrate Microsoft Excel applications with other Microsoft Office products and third-party products?

Why Use Excel as a Development Environment Over Other Products?

Basically there are four types of competition:

· A database application such as Microsoft Access or Microsoft FoxPro®
· Visual Basic® or Visual C++™
· Other presentation tools such as Microsoft Word, Microsoft PowerPoint®, Freelance® or Harvard Graphics®
· Other spreadsheet products

Rob explained that the evaluation process considers some key questions in each case. His answers below deal with each of these situations in more detail.

Excel versus a Database Application

One of the basic reasons to use a true database application instead of Excel is that Excel is not the best application for dealing with complex data architectures that require data input and validation. However, many important business applications do not have large data requirements or complex relationships, so the choice becomes less obvious. To determine if Excel is the right tool for a specific application, we consider the following issues:

· Size-An application with more than 10,000 rows (records) is usually a database application, but there are exceptions when the data can be broken up into smaller categories that can be put into separate files.

· Data Security-Record-by-record security is difficult to implement in Excel, but it can be a good choice for read-only EIS (Executive Information System) applications, or when the data needs to be manipulated further. Excel is not usually a good choice when the application must be completely user-proof to lock users out of native Excel functionality.

· Multi-user requirements-While Excel is not traditionally thought of as a multi-user application, we have developed many applications in Excel that simulate a multi-user environment. Pure transaction processing, however, is better handled by database management software.

· Application objectives-If "What-if" analysis and reporting are the main objectives of the system, Excel may be appropriate. Applications with intensive data-entry and validation requirements may be best suited to another development tool.

· Calculation and modeling requirements-Excel's strengths are its calculation handling and modeling abilities. It is an excellent tool for dealing with complex calculations, such as variable calculations with a lot of special cases or that change frequently. Simple calculations or those that don't need to change very often can be handled quite easily in a database environment.

· End-user objectives-Often, users want data to wind up in Excel anyway, because they are comfortable in Excel or because they want to manipulate the data further. This is one of the most important reasons for doing the application in Excel.

· Cross-platform issues-Excel is one of the few PC/Mac® cross-platform development environments.

We often develop applications using Excel in conjunction with a database component. In these cases, we normally use Excel as a reporting engine for the database application or as a calculation engine to blow data into the database (for example, storing complex derivatives or tax calculations).

Excel versus VB or VC++

The major factor in evaluating Excel against these development tools is how much Excel is buying you in the application. In other words, how much customization has to occur versus the benefits of leveraging Excel's built-in functionality? Applications that can take advantage of the spreadsheet environment, pivot tables, graphing, etc., may be better suited to Excel than to Visual Basic or VC++. For VC++. The major considerations are execution speed and how close you need to get to the operating system.

While Excel is preferable for handling complex calculations, Visual Basic has some advantages over it:

· VB is preferable for high-end GUIs. Excel doesn't support OCXs, doesn't have the same level of control over the interface, and doesn't have the same availability of interface tools.

· VB has superior database handling features regarding connectivity, security, and forms design. With VB's data controls, VB forms are much better for entering and administering data.

· VB applications can be distributed without royalty fees, so it may be a better choice from a cost perspective if the intended users don't already have Excel.

Excel versus Other Presentation Tools

This can be a hard call. Excel is an excellent presentation environment in terms of table handling and graphics. None of the alternatives have the same level of automation capability as Excel. Some other issues include:

· How much information is text? With mostly text, Word or PowerPoint may be a better alternative.

· Does the presentation have a lot of tables? Excel is ideal for tabular data.

· Does the data appear within sentences? If it does, Excel allows you to concatenate text and have it update instantly.

· How much automation is required? Microsoft PowerPoint and Harvard Graphics currently have no automation capability.

Once again, we often use Excel in conjunction with other applications for presentation-intensive applications. For example, we have automated many, many equity research publications using a combination of Word and Excel. Word controls the document templates, and Excel drives the tables and graphs.

Excel versus Other Spreadsheet Products

Microsoft Office has the lion's share of the desktop application suite market. Our clients have made the choice to go to Excel for these reasons:

· Excel's rich VBA development language allows users to create a robust GUI for applications.

· Microsoft Office offers better integration between applications than do other desktop application suites.

· Pivot tables translate normalized data structures into user friendly analytical reports-a unique and very powerful tool.

· Excel is becoming an industry standard.

What is an Ideal Excel Application? What Components Commonly Apply?

There are no easy answers to these questions. There are some general categories of applications and a few areas of functionality that find their way into most Excel applications. But spreadsheet applications diverge greatly in many different directions from there. Some common categories of applications include:

· Financial Models-Typically involving input/calculations and processing/results, these systems provide answers to user input. We have done hundreds of corporate finance, product and sales analysis, and financial analysis models.

· Presentation and Reporting-These applications retrieve data and report on it. Excel is a very powerful tool in getting business critical data to users so that they can use it in a meaningful way.

· Utilities-We often add capabilities to Excel to improve user productivity. These utilities can include standardizing graph formats for a department, automating the retrieval of data from a database or other source, or developing tools to facilitate consolidations.

· EIS-We have developed several highly-graphical management information systems that take advantage of the inherent capabilities of Excel, yet users can't really tell that they are using Excel.

As for components, nearly every Excel application we develop includes the following functionality:

· Print routines-especially to print selected areas
· File management utilities-the system must know what data to save and how to handle it
· Menu handling-to simplify navigation for the user

What Steps Create a Good Excel Application?

MMA uses this process when developing an Excel application for a client:

1. Obtain a user/department/company profile for the client and application. Understand the client's working environment, existing level of expertise, internal support resources, budgetary constraints and expectations.

2. Find out the users' daily process. We often use a GANTT chart or timeline to document workflow or perform a use/case analysis and identify the actors.

3. Think about the application in generic terms and identify similarities with previous projects. Put together a "guesstimate" of how long it will take to develop the application.

4. Put together a design shell for the application in Excel. Understand the ancillary utilities that may be required.

5. Review functionality with the user.

6. Make sure Excel is really the correct tool. Identify other tools if appropriate.

7. Identify the technical hurdles. Is there a showstopper? Is there any beta technology in this and what is the exposure? Do critical links to other data and systems need to be tested? Plan to do the hardest things first so that the major risks can be mitigated early in the development cycle.

8. Build a development plan. Who does what? When? Who's relying on what? What code is going to be shared? Define participants' communication schedules. Set up a phased development approach with tasks and milestones. Identify library code that can be used.

9. Review the plan with the user.

10. Follow the standard MMA development process:

· Follow the MMA proprietary standards document.
· Provide consistent error handling. Nothing should break for the user.
· Make sure that reusable tasks are created in sub-routines and not repeated. Modularize code.
· Prepare for uncertainty and anticipate changes.
· Do not hard code anything (paths, cell references, etc.).
· Don't overcode. Use the spreadsheet functionality as much as possible.
· Restore the user's environment to its starting point.
· Stay consistent with how Excel works. At all costs, try to use the same paradigms.
· Get someone else to test it.
· Don't take things personally. Take a step back and listen. There may be a better way than yours.
· Work smart. Do only what you're asked to do. Don't overcode just to add cool things. Don't add unneeded complexity.
· Don't be naive. Don't oversimplify the task by, for example, trying to rewrite the charting engine or solver in a short time.
· Get regular user feedback. Manage change carefully.

11. Get business content from the user and review all business rules.

12. Give the user a version of the system to use in checking the validity of the business rules.

13. Work toward code completion.

14. Alpha test with users.

15. Complete the knowledge transfer with responsible client staff for use of application and on-going maintenance and support if possible.

16. Plan roll-out strategy and help the user with roll-out.

17. Obtain user sign-off for alpha release. The project should be what they wanted; change no functionality even though there may be bugs to fix.

18. Complete documentation and testing. Documentation can be both written and on-line depending on what the client wants and is willing to pay for.

19. Beta test with users.

20. Obtain sign off of final delivery.

21. Provide support as needed

22. Review the project with the client after a month or so of use to discuss your future relationship (support, phase 2, etc.).

What Other Development Tools Do You Use with Excel?

MMA uses these tools:

· Visio®-for flow diagrams of the user's process and the system architecture
· Microsoft Word-for specs and documentation
· Microsoft Excel-for time planning and variance analysis and also for time reporting
· Microsoft Access-for bug reporting database
· ErWin™-for data modeling (MMA is also starting to use System Architect)
· Microsoft Project-for GANTT charts and project planning
· Custom MMA Tools-including code indenters, error-handler builders, a toolbar-generation utility, and a custom data-connectivity tool
· PVCS®-for version control. MMA is investigating Microsoft SourceSafe® to see if it can be used on a wide-scale
· Doc-To-Help® and Help engine-for documentation and on-line help

How Do You Integrate Excel Applications with other Microsoft Office Products and Third-party Products?

Excel is an important component in many mutli-product solutions. We often use Excel as the reporting engine for database applications, in which case it interacts with VB, Access, SQL Server and other SQL database packages (Oracle®, Sybase® etc.). We also use Excel as the reporting engine for a variety of commercial data products such as Compustat®, FactSet, One Source®, ADP®, IRI and a host of other financial and industry specific commercially available data products. As mentioned above, Excel is also an important component in publication and presentation applications driven by Microsoft Word and is used for charting and table handling.

In addition, we have seen a recent surge of interest in using Excel as a component of some commercial applications our clients are creating to sell or distribute to their clients. We are using Excel as a primary component in an international tax application and a portfolio management system, both being developed for retail sale.

MMA Background and Applications

Micro Modeling Associates has been developing financial applications for Fortune™ 1000 companies and Wall Street firms since 1989. They have delivered over 500 solutions using Microsoft Office (Excel, Word, Access, Mail, PowerPoint) and associated tools (Visual Basic, Visual C++, SQL Server®) for over 100 corporations. Their clients include 8 of the top Fortune 20, all of the Big 6 accounting firms, the major investment banks, and a variety of the top pharmaceutical, consumer products, health care organizations. MMA provides four basic services:

· custom applications development
· training for developers and end-users in Microsoft products
· migration assistance (converting a firm to Microsoft products)
· strategic advice about business information needs and technology

Half of MMA's staff have advanced business degrees or have worked extensively in major corporations in business line roles. Originally, MMA hired employees with a financial background and trained them in programming. Now, they're finding that they need people with a financial background to understand the user's business content as well as experienced programmers to create more complex systems.

MMA focuses on business-critical analysis and decision support systems: making business-critical information available to key decision makers with appropriate, easy-to-modify analytical support. They focus exclusively on the Microsoft tool set. Their applications include:

· models and decision support systems for corporate finance, sales and product analysis, budgeting/forecasting and financial analysis

· publications and presentations including equity research publications, proposal and sales presentation generators and other marketing tools

· client/server and workgroup applications

You can contact Micro Modeling Associates, Inc. at:
111 Broadway
18th floor
New York, NY 10006
(212) 233-9890

Microsoft TechNet
July 1995
Volume 3, Issue 7

Go To TechNet Home Page

Go To Microsoft Home Page