The client required their invoicing system updated so it can automatically generate invoices with minimal manual intervention. It had to be able to record client data, easily add new clients and pull this information into the main invoice. This would then be exported as PDF and sent to the client.
It had to have a section for receipts that the client could easily add scanned data or screenshots into, and the file would have to recognise the receipts were added and export the receipts sheet when required, without exporting a blank page when there were no receipts.
Without starting from scratch, we used the client’s existing template (as they liked this design) and flattened the design into the spreadsheet (by removing text boxes and combining images such as logos). We started by aligning data, simplifying data entry by grouping this together, preventing data entry replication and allowing greater flexibility by using things such as custom number formatting to increase functionality of cells.
The client field contains a drop down in the Organisation Name, which automatically updates the clients details in the lines below. The client list can easily be updated in a seperate sheet by adding a new row with client details. Then it automatically updates the dropdown without updating anything else.
The invoice number is automatically generated based on the previously entered invoice number and updates whenever the “reset” button is pressed; the reset button removes all manually updated fields, and repopulates all other fields with default formulas and entries.
The invoice date is always today, however the due date field is a freely changeable field, where you enter a number e.g. “14” or “30” and the field updates it appearance to say “14 days” or “30 days” to give a cleaner look. These two fields are then used to populate the field below, the due date. Because both fields are actually numbers, formatted in a different manner, it will automatically update to the correct due date.
Once you have populated all of the fields and generated a total, the data is recorded when exported automatically. The PDF is automatically saved in a local folder, in a given naming convention, for the user to send immediately.
Soon as the PDF is saved, the data is recorded on a record sheet which contains all of the required details and a payment status – due. The user can go here at any time and switch this to Paid in order to have a running record. It will also highlight, in orange, anything that is due; it will also highlight in red that is overdue. This allows the client to chase up their clients when required.
However, this allows us to very quickly provide a monthly summary of sales and overdue invoices.