Managing Contracts Using Excel
For most businesses, Excel will generally have a role to play at some point in the management of contracts. It’s one of the most accessible and widely used pieces of software in the world and obviously well suited to recording the kinds of data captured in contracts.
As a business grows, and generates more contract agreements, so too does the need for a central repository, where the data can be tracked. Without a dedicated place to record contracts, it’s not uncommon to find them stored in fragmented fashion in email accounts or perhaps hard copies in filing cabinets for example.
For any legal representative, operations executive or supplier manager who recognises the risk that this poses, it’s only natural to reach for your trusty Microsoft product and start to build your database.
So how can you do this effectively and what are the risks and limitations with using Excel for managing your contracts and suppliers?
In this article, we’ll provide you with some practical tips to apply to your spreadsheets, as well as highlighting the key watch-outs and pitfalls of Excel in this particular use-case.
Top Tips for Managing Contracts In Excel
Know your dates
Effective contract management comes down to knowing your dates, so these need to be at the heart of your spreadsheet.
The first thing to do is set up a cell that will always have “today’s” date in — ie whenever you open the spreadsheet, this cell will be populated with the current date.
To do this, enter the following formula into your chosen cell: =today()
Now that you have this, you can use it to keep track of how long it is until specific milestones are up for each contract.
For each contract you may have different key dates, but let’s assume you’re capturing at least “renewal date” and “termination date” in your spreadsheet. In the column next to these you can subtract the cell for “Today’s Date” from these contract dates.
Doing this will give you the number of days from now until that date.
The next step is to add conditional formatting (format/conditional formatting) so that you can tell at a glance whether contracts are near to renewal.
You may want to set your own guidelines but you can use the following to start with:
Create a rule for the column that says if the number is greater than 30 but less than 90, to highlight the cell in orange. Add a further rule that says if it’s 30 or lower then highlight in red.
This way, your eye can be drawn directly to the most pressing contracts.
Top tip when entering dates — if you need to insert today’s date, you can simply press ctrl+; to populate it into a cell.
Security and Data Integrity
If it’s your responsibility to manage contracts for your business the you will likely want to lock down the data sheet as much as possible to ensure changes aren’t made without your permission.
The simplest way to achieve this with Excel is to set a password on the workbook. This can be found under Tools/Protection. Immediately, this will limit the ability for people to change the data in your document.
Next, you may also want to limit what data can be entered into specific fields if you’re going to be permitting other people to access the document. For example, using a free text field for contract type might lead to people entering multiple different versions of the same thing — eg “NDA” and “Non Disclosure Agreement”. This will then make it difficult later on to filter and monitor your data.
The solution is to use Data Validation and a specific list. Got to Data/Data Validation and then select to allow from a list. You can then enter your specific words and phrases, or select a list you’ve already created in the sheet, and then the only data that can be put into the cells will be from the list. This will ensure your data is consistent and can be filtered accurately.
You may also want to leave “Track Changes” enabled so that any time somebody updates fields then the changes are highlighted for you to accept.
Assuming your actual contract documents are also hosted on a shared drive or similar, you can insert the links to them into your Excel document as well. This will ensure that there’s a clear line of sight to the relevant documents.
However, your system for storing contracts will also need to be robust and monitored closely to ensure that the current versions are always linked to and previous versions are retained for comparison.
Key Pitfalls of Using Excel for Contract Management
What the above highlights is that Excel can be used as basic resource for managing contracts but there are some obvious drawbacks which can make it unsuitable.
The most crucial issue is that it generally relies on one specific person to build and maintain the sheet and be responsible for its integrity. They might delegate to colleagues to enter some of the data but that will need to be closely monitored to make sure of accuracy.
“Just use Contracts-V4.1-FinalComments-Dave-Dec2017.xlsx”
Version control is another major problem with Excel. How many times have you had to ask colleagues who are working on the same document which version you should be using? Everyone seems to have a different idea about what a logical sequence should look like and the more people who have a need to access a document the worse it gets.
Contract management, by its very nature is a collaborative process with sign-offs and comments to be acknowledged.
The latest Microsoft Office 365 solution has addressed some of these collaboration issues and now makes it easier to access documents concurrently and remotely. However, you still run the risk of someone overwriting previous work or breaking certain formatting.
Confidentiality and Information Security
Confidentiality is another issue when it comes to using Excel for managing contracts. A lot of agreements will contain sensitive data such as costs or salaries and will only be appropriate for certain people to view.
Linking to documents housed elsewhere, or including the information directly in the spreadsheet, can expose that information to an unnecessarily wide audience. Without different levels of access and the ability to restrict access by role, you can run the risk of breaching information security requirements.
If you have an information security team, it’s worth checking with them to see whether Excel and your current process meets the necessary standards.
Ultimately, managing contracts is a complex process, requiring a clear record of all agreements and then constant monitoring and updating. See our explainer article on Contract Management for more information on this.
As the number of contracts your business has grows, so too will the administration required and the need for collaboration. Excel can provide an adequate option up to a point but eventually a specialist solution will be necessary to ensure continued compliance and, ultimately, business success.
However, if Excel is your chosen system for managing your contracts for now, we’ve created 13 separate templates to help you manage all aspects of the contract lifecycle. You can download these templates for free in a single document, using the link below. We hope you find them useful!
Originally published at www.gatekeeperhq.com.
