My son, Isaac, has started a lawn service. To help him track who has paid, and who is outstanding I over-engineered a spreadsheet for him. Behold!
Customers The first sheet is a simple spreadsheet of customer data:
| Customer | Address | Phone | Size | Price | Day | Notes |
|---|---|---|---|---|---|---|
| John Doe | 123 Any Street | 801-555-0011 | Small, Medium, Large | $7 | Thursday | Notes about last mowing |
Inventory For lack of better name this is a sheet for tracking which jobs have been completed.
| Customer | Apr 22 | Apr 29 | May 6 | … |
|---|---|---|---|---|
| John Doe | [x] | [x] | [ ] | [ ] |
The function for each date is simply a boolean controlled by a checkbox for ease of use.
Payments Using a data constraint, the payments tab simply track who paid, how much and method of payment.
| Customer | Date | Method | Amount | Tips |
|---|---|---|---|---|
| John Doe | 2021-04-22 | Venmo | $10 | $3 |
| John Doe | 2021-04-29 | Cash | $10 | $3 |
Isaac’s customers have been more than generous with his tips, so we made sure to acknowledge it in the payments tab, so we can determine who is overpaying, and who is actually tipping. We only count a tip as a tip if the customer declares it as such.
Balance Finally the balance sheet. Here is where most of the magic works.
| Customer | Owed | Paid | Tips | Balance |
|---|---|---|---|---|
| John Doe | $14 | $20 | $6 | $0 |
| Field | Meaning/Value |
|---|---|
| Owed | from inventory the number of true for the customer, multiply that by their price. |
| Paid | from payments all payments where customer name matches |
| Tips | from payments all tips where customer name matches |
| Balance | Owed - Payments + Tips |
Obviously for the few customers Isaac has, this is over kill, and everyone has paid on time and immediately. I wanted to give Isaac a foot up, in case something happens, and to show him how important this type of simple work is to knowing exactly where you stand with customers and as a business person.
Next level
The more I think about this document, and how it is structured I can see the clear advantage to moving this to a simple application for the boy to use on his phone. With a few added bonuses:
- Ability to document work completed (pictures)
- Ability to update in real-time (no more waiting to get home to update the spreadsheet)
A few more features we could add, down the road:
- Send notifications to customers about missed days (rain delays, dog feces issues, etc)
- Send notifications of completed work, and request payment
- Send add-on messages (fertilizer, edging, dog fecal matter removal)
Being the over engineering dad I am, that is the goal, by the end of May to have a working application for my son. I just might rope him into programming some of it as well, just to make sure he gets the exposure to the additional type of work (less laborious) available in life.