Overview and goals

As a small business that offers and build tools for (local) small to medium sized companies we often get the request to customer build a finance / invoice solution. This is especially true for smaller companies that where finance is done in a certain way for years and where the end users don’t want to spend a lot of time to send quotes / invoices or do general bookkeeping.

This build series is for companies that want to have a custom solution that fits their needs that is cost effective and (once its setup) last for years to come. Before we start this series I must mention the obvious other options: Both Freshbooks and Invoice Ninja are amazing programs that I can easily recommend. However they both lack the custom nature and flexibility of a spreadsheet.

In this guide I will mainly work with google sheets as it is freely available, has excellent sharing options and will make constand version backups that keep your data safe. If you are more inclined to MS office / excel you can still follow (as the approach and formulas remain similar)  along.

Goal of the file

As with many projects its very good to start with a goal and then work our way to a “future list”. So for this we want to build an all encompassing finance file.

• Graphical dashboard – a sheet where we can find information that is important for you to see at a glance. As a side note, this sheet also has to look good 🙂
• Cash Flow overview – a cash flow report that automatically updates with the information we put in the sheet.
• Invoice builder – Maybe the most important part of this tool (getting paid is important 😀 ). This should auto expert our invoices to PDF and preferably automatically save them to Google Drive so we don’t spend time archiving our send invoices.
• Quote/proposal builder:  –optional– A sheet where we can easily build a quote, this is similar to our invoice builder sheet.
• Expenses overview: a way for us to know when we have to pay what invoice.
• Customer sheet: a place where we can have an easy overview of our customers.
  • Name + location and contact information
  • Financial information such as unpaid invoices, aging etc.
• Vendor overview: a place where we can view vendor specific information
  • Name + location and contact information
  • Financial information such as unpaid invoices, aging etc.  
• Bank mutations: a file where we can enter bank mutations so we can easily see our current balance. It would be nice to also auto check if an invoice is being paid.
• VAT declaration – optional – This is needed if you are a Spanish freelancer as this will help you with your quarterly VAT declarations.

To support the functionality of the file we need to make some helper sheets that store data. These sheets can be hidden once the file is complete. As a side note, working this way will also result in a clear to understand structure which makes troubleshooting or adding functionality later on a lot easier!

What we need

As this file can be build we don’t need a lot:

• A bit of time (how much depends on which parts of the guide you want to follow.)
• Good music to work
• A company logo in either .PNG  or .JPEG
• A Google account (to access google sheets and drive)
• Some basic understanding about your local laws and your responsibilities as a business owner.