It is not uncommon for Excel to be used as a tool for managing contracts, but generally it does not progress beyond being a simple list of records, often not collecting enough information to provide all of the answers to key questions about the contracts and more often than not, the data is corrupted, inconsistent and incomplete. What I will demonstrate in this series is how to leverage the features of Excel to produce a contract life cycle management solution that will:
- Protect the raw data
- Link complex relationships between contracts, suppliers, variations and procurement process
- Validate data before writing to the raw data tables
- Display the data in various formats enabling rapid and consistent reporting
- Provide a detailed dashboard
- Enable exporting and importing of data
- Simplify data entry
- Automated generation of contract numbers
You don’t need to be a programming expert, the articles will provide all the code you need, but you should do some research to ensure you understand the basics of variables, decision functions (such as IF statements) and looping methods.
The Basic Concept
I first developed the frame
work for the model I am going to demonstrate when my manager came to me with a list of projects that formed the formidable programme of work the business had underway. He asked if I could do something to protect the data and make sure it is complete and entered correctly. Apparently every time his boss went into it, he accidentally deleted information, corrupted it or simply entered incorrect information.
The workbook will have several basic data sheets, such as the contract list, variations, supplier details and look up values for managing validation. On top of those sheets, there will be a data entry, viewing and reporting sheet; a search sheet; and a couple of reporting views as well as a dashboard.
To prevent the raw data from getting damaged accidentally, you need to separate the viewing and updating of the data from the data itself. This way it will require a conscious decision to update the data. Data validation prevents inconsistent data being entered and significantly improves reportability of the data.
There are two options for data entry, one is a carefully formatted worksheet, the other leverages the User Form functionality of Excel. Each has it’s pro’s and con’s, so I will cover off both and you can select the method you find works best for you.
Database Concepts
The workbook functions as a simple kind of relational database. Where there is a one to many relationship in the data, such as a contractor having many contracts with the organisation, then the data is “normalised” into separate tables. Normalising means to take any repeating data out of a table and put it into a new table. A contracts table that has five fields for capturing the vendor name, business identifier and address fields is problematic. This is because a vendor is likely to have more than one contract, they could in fact have 20 or 30 contracts with an organisation over time. Not is there redundant repeated data, if ever their address changes, you would need to update 30 records instead of just one. Of course, then there is a need to bring the data together using VBA, but we will cover the reason for splitting and the method of re-joining the data as we go. The beauty of the model that I will demonstrate is that the storage of the data can be fully functional and extensive, but the viewing and maintenance of the data can be intuitive and easy. We will capture over 100 fields of data relating to each contract, yet the information will be viewable in a meaningful and useful format.
So in the next blog in this series, I’ll cover the fields that can be captured and using validation to ensure the data in critical fields is consistent and meaningful. So head on over to Advanced XL Contract Manager – Step 1 Contract fields and validation.
