Working remotely at QUID, it is critical that our business management processes could be performed by either myself or my co-founder Mo. One of these processes is managing vendor invoices.
Pretty much every company has the need to pay vendors and in all of the places I have worked the process is largely the same: the finance team receives an invoice, the invoice is entered into a financial management system, a copy of the invoice is sent to a business owner for review and approval, the approved invoice is sent back to the finance team, and the invoice is (eventually) paid. Working at a startup, we didn’t have a finance team. Sure, we have accountants but we don’t want to pay them to support day-to-day business functions.
I ended up building a custom tool using Airtable. Airtable is an online service that lets you build small bases (think of each base as a simple database) made up of one or more tables (think of each table as a single spreadsheet) without any coding.
Building a tool in Airtable has many advantages over using a spreadsheet including:
With Airtable you can define the data type for a column and the application will only allow data of that type to be entered. With a spreadsheet, you can define the format of the column (or cell) but you cannot ensure that the data entered into the column is the correct type. This means that in a spreadsheet, although you can format a cell with a particular date format, for example, someone can still enter text or a number in that cell.
You can add and access attachments to records in any table in an Airtable base. For example, the PDF version of an invoice can be added to the record in the invoice tracking table. This is probably one of the best features of this tool since it makes it really easy to look at any invoice in the future. With a spreadsheet, the only way to accomplish this would be to create a link to an externally stored file. The spreadsheet’s link will only work if the file is still accessible in the same location.
Easy table linking
Airtable has built-in data types that let you link to records in another table. This is similar to a foreign key reference in a database, but much simpler to setup. This can be done in a cumbersome way with spreadsheets using lookup functions, but it becomes difficult to maintain across multiple sheets.
Airtable lets you add collaborators to a base and collaborators can be given specific permissions that determine what they can and cannot do in the base. These permission levels are: owner, creator, editor, commenter, and read only. With a spreadsheet (e.g. Excel) file you can password-protect a spreadsheet but anyone with the password can change anything. With online spreadsheets like Google Sheets (and Office 365), you can grant similar levels of access to a file.
Also, with Airtable forms you can grant access to a single table in your base to, for example, allow someone to submit an invoice but not have access to any other tables:
Because Airtable is a cloud-based service, it is accessible anywhere there is an internet connection and is designed to support multiple concurrent users. G Suite and Office 365 spreadsheets are also cloud-based and thus are similarly accessible.
Did I mention that you can have an Airtable workspace (a collection of bases) for free? Note that the free version of Airtable comes with some restrictions on the amount of data stored and some limitations to the revision history retention, but these limits are significantly greater that what a small or medium sized business would need. G Suite and Office 365 both have (reasonable) monthly user license fees.
Building the vendor management tool.
The free Vendor and Invoice Management Airtable base has the following tables:
This is a table that identifies the approvers in your invoice and contract management processes. There is a single record for each approver and an approver must have a record in this table before their name can be added as the approver of an invoice or contract. Each approver record will also have links to the invoices and contracts that they have approved.
Not surprisingly, this is where vendor records are created and saved. There is a single record for each vendor and for each vendor you can store important information like tax ID, payment terms, contact information, etc. A record for a vendor must exist in this table before an invoice or contract can be saved for that vendor. Similar to the approvers table, each vendor record will have links to the invoices and contracts associated with the vendor.
Each contract you enter is saved in this table along with the status of the contract (e.g. new, active, inactive), the billing method, the start date and end date of the contract, and the renewal terms. A copy of the contract can be saved in the attachment field in this table, making it really easy to find at a later date. Each contract is associated with a vendor and can have one or more approvers.
This table is where invoices are tracked. For each invoice you can store the vendor, invoice number, date, currency, pre-tax amount, taxes, total amounts, invoice processing status (e.g. received or paid), approver name, payment method, and payment date. Each electronic invoice document can be saved in the attachment field of the corresponding record, which is possible the single biggest time saver for anyone involved in the invoice review and payment process.
Securing the tool.
Granting permission to an Airtable base works by granting access to a workspace that contains the base. This is pretty straightforward but does require some planning so that any Airtable bases you create in a specific workspace have the same permission requirements.
Additional security features are available if you upgrade to the Airtable Pro (paid) plan. If you do this you can password protect bases and forms as well as restrict access to users from specific email domains.
To start using the free Vendor and Invoice Management Airtable base simply:
- Signup for Airtable for free. You will begin a trial period with free access to the Pro plan, but the tool can be used on the free plan at the end of your trial.
- Create a Workspace
- Copy the free Vendor and Invoice Management Airtable base into your workspace.
Reach out if you have any questions on this or any other business technology concern.