Best practices for prototyping using Microsoft Excel®
October 31, 2014


The journey to successful completion for a software project involves many steps. One of the early steps from a development standpoint is the creation of software prototypes. A prototype is a mock-up of what you think your final application might look like along with some of the high-level logic which you plan to embed in the application.

This article will describe the benefits of creating a software prototype, the reasons for building it using Microsoft Excel and some ideas on how to reinforce good coding practices.


We speak to a number of FINCAD Developer users whose primary role is not a programming one but who find themselves involved in developing software to support their research work or other people in their organization. This article was written to provide some practical guidance for people like them who do not have a software engineering background but who can benefit from a quick introduction to some of the fundamental ideas. If you are an experienced software developer then you may also find it interesting to compare the software engineering ideas in this article to your own.

Why create a prototype?

A software prototype provides a more visual way of checking a software design and also a mechanism for demonstrating your design to others, including potential users of the completed software. It is important to get buy-in for the design before you invest significant resources into building the application. Building a good prototype can be a great help in achieving this. It is not a replacement for a good requirements specification for the project. It is more a way of checking that the requirements specification that has been written will actually meet the requirements of the end users who will be working with the application. It requires significantly less time and effort to rework a software prototype and a requirements specification than to solve the same problems after the full application has been built.

So why use Microsoft Excel as a prototyping tool?

  • flexibility – It is easy to add sheets, controls, and VBA code which enables you to quickly prototype user interface and business logic elements.
  • familiar tool – Most financial software developers are very familiar with Excel and can use it to build a prototype and benchmark analytics quickly which reduces cost.
  • ease of use – Using VBA rather than another language avoids the need to worry about memory management and other low-level programming tasks while developing your prototype. This saves a significant amount of time if the prototype needs to be redone several times before the right design for your application is found. It also allows for people to participate in the prototyping activity who may not have the right programming skills for developing the application but who have knowledge to contribute which will benefit its design.
  • data friendly – Excel provides import/export mechanisms and the ability to easily store or change data. This allows for the testing of financial analytics which will be incorporated into your application. Graphing based on data and calculated results can also be used to check the analytics.
  • visibility – Excel is a very visual tool. It is easy to create or embed graphs, pictures and other content which helps to convey your design ideas.
  • available tools – FINCAD XL and Developer both provide the ability to use FINCAD analytics in spreadsheets. There are also numerous other third party add-ins which extend the statistical and financial analytics available in Excel.

First things to consider

Here are some things to consider early on when prototyping an application:

  1. The goals. What are you trying to prove?
  2. Avoid prototyping in the same language that will be used to build your software.
  3. Choose a name for the prototype which could not be used as an application name.
  4. Avoid prototyping a large application in one stage.
  5. Avoid including too many features in your first version.

a) Set your goals

Think about what needs to be proven or confirmed so that you can be confident that your software design is correct and that developing the application will be successful and consider how to demonstrate the look and feel which the end application will have. After this you need to consider who will be evaluating your prototype. Make sure that the prototype speaks to them. Proving that the math is right, is a good start but getting buy-in from users who are less quantitative may require demonstrating this in a visual way, such as with a graph or by comparing the result to a known value from a deal sheet. Try to make your proof in a way which can be replicated or at least easily understood by your audience.

b) Avoid prototyping in the same language

You want to avoid reusing the prototype directly when it comes time to build your application since it was created quickly and will not be the best implementation for the design. It is not practical to invest too much in a good design for the prototype since it may need to be rewritten several times. Remember, the prototype is just a tool for testing your design ideas and verifying that the calculations in your application will work. It is only a mechanism to reduce the chances of an expensive rewrite that could result from building a software product that is not functionally correct or that is a poor fit for your users. If you build the prototype using different tools than those used to create the end application, it will be less tempting to use the prototype as more than just a design tool. Another option is to use a prototype for checking your design, then after verification provide only the updated design specification along with some user interface mock-ups rather than the prototype to your implementation team. Both these methods reduce the chances of code from your prototype being used directly in the application without taking the effort to ensure that it is a good implementation.

c) Choose a name which won't be reused for the end application

A name which is quickly chosen for a prototype may include words that are overly technical for your market or ones that are less used outside your organization. Names can live on much longer than you might expect so picking an internal name for a prototype which is obviously inappropriate as a product name will ensure that there is time and opportunity to select a good product name later that will speak to your users. The internal name should be something that fits the culture of your organization and the internal passion for the project, such as the name of a fast sailing ship, aggressive animal or fighter plane. If your application is intended as a commercial software offering then you will want to include input from customer facing staff when determining a good name for the final product to ensure that the product name will appeal to future buyers.

d) Avoid the scope becoming too large

You may have a larger vision for the killer application that will meet all the needs of its future users. However, this is not always the best place to start and you shouldn’t try to build a prototype which demonstrates the entire vision. If the scope of your vision is large, then try to break it up along logical lines into several parts and evaluate which part is the most important one to build first. It is advantageous if some of those parts are independent and have value outside
the larger application so that they can be useful on their own until your full vision is realized. Identifying such a part will allow you to reduce the initial scope of your project and simplify the requirements for your prototype but will require you to reassess your goals to reflect the changes. Your view for the larger project may change after you understand the first piece really well and for commercial offerings, delivering the first piece may lead to sales which can help to fund the rest of the project. Where development resources are fairly limited, this can be the difference between success and failure for the project.

e) How to pick the features to implement first

One option is to involve a group of people in deciding on the best features to implement in your prototype but instead of just voting arbitrarily on each option, consider using some sort of artificial currency to let each individual place a value on each option. This could be anything from points or paper-clips to dollars or donuts. Pick something that works for your group but like development resources, the amount of artificial currency available to each voter should be relatively scarce. Ideally, this step should be taken before you begin writing your prototype since it will reduce the work and the chance that you will later create an application which has too many, little used features. However, including some of these features in the prototype may be necessary as a way of demonstrating the ideas to your voting audience. The decision should be made based on how easy it is to describe the features to vote on without having the prototype available.

Other considerations

The high level considerations mentioned previously are a good starting point but you may want to go even further.

Although prototype code is typically thrown away before implementation time, you may want to consider developing some best practices for developers who are writing VBA code for your prototype or just using the same guidelines that you have for VB programming. This will reinforce the use of good coding practices and can be beneficial later, if the same developer becomes involved in creating the end solution as well.

Class modules can also be used behind Microsoft Excel spreadsheets to demonstrate an object oriented design in the prototype.


There are a lot of good reasons to consider using Microsoft Excel workbooks for prototyping your application and FINCAD tools are a great fit for developers who choose to do this. If you make the right decisions up-front then you can avoid numerous headaches and achieve greater success in your prototyping and application building efforts.

»To download the latest trial version of FINCAD Analytics, contact a FINCAD Representative.


Your use of the information in this article is at your own risk. The information in this article is provided on an “as is" basis and without any representation, obligation, or warranty from FINCAD of any kind, whether express or implied. We hope that such information will assist you, but it should not be used or relied upon as a substitute for your own independent research.