Introduction to Excel Invoice Manager

What Is Excel Invoice Manager

Microsoft Excel provides comprehensive data formatting, calculation, reporting and analysis facilities. This is why so many people use it as an invoice creator. There are also a number of free or commercial templates you can download from the Internet. However, like it or not, this fact must be faced: Excel is basically a client oriented, general-purpose tool and is not specialized to store, organize, and manage large amounts of data.

To successfully store, organize, and manage large amounts of data, such as your daily invoices, the best choice is a Relational Database Management System (RDBMS), such as Microsoft Access and SQL Server.

Excel Invoice Manager is based on these two time-tested technologies and allows them to do what they are best at: using an Excel template/workbook to create, calculate invoices and reports, using a back-end database to store and manage all persistent data like customers, products, and invoices.

The Advantages

The advantages of this approach are obvious. With the front-end Excel workbook, you get not only a What You See Is What You Get tool to create invoices and reports, but also the thousands of features that come with Excel to customize everything you need. With the back-end database system, you have a safe, efficient mechanism to store and manage large amounts of data.

From the point of implementation, an Excel Invoice Manager system consists of three parts:

• The first part is Excel templates/workbooks with responsibility for creating and calculating invoices and displaying reports. The workbook itself does all calculations related to invoice. In other words, this workbook is able to run alone for invoice creation and print, without the help from other two parts.
• The second part is an Excel COM add-in, which runs in background and connects the Excel workbooks to the back-end databases. This COM add-in creates a bridge between the Excel workbook and the database, providing an interface for all database tasks, such as querying databases and returning results.
• The third part is a relational database that provides a secure, efficient, flexible mechanism mechanism to store and manage all persistent data.

The Architecture

The Excel COM add-in is a very flexible program that has the ability to connect multiple Excel workbooks to different databases. As a result, you can design your invoice management system as follows:

1. One workbook, one database: This is the simplest model. You create invoices using the invoice workbook and store them to the database through the COM add-in.
2. Multiple workbooks, one database: By using multiple workbooks, with each workbook having its own styles, colors, contents, or even a different tax system, you can use very different workbooks to create invoices and then store them to the same central database. These workbooks can be installed on different workstations on a LAN (Refer to Share Database), or they can also be installed on a single workstation.
3. One workbook, multiple databases: As mentioned above, all persistent data is stored in databases. You can use multiple databases to isolate different types of data. For example: one database for one business type.
4. Multiple workbooks, multiple databases. On a single workstation with one copy of Excel Invoice Manager installed, you can utilize multiple databases and workbooks to construct a complex invoice management system.