By Cassandra @ Setmore
Writer, editor and scheduling product expert at Setmore Appointments.
Setmore’s New Year’s resolution for 2018 is to help you make more money. But before we can do that, first we have to see how much you made last year, so we can measure our progress. Alternatively, if you need a way to find your total revenue from 2017 for tax purposes, this is the guide for you.
Please note, in order for this to work, you’ll need to have had either the Stripe payments integration or the Setmore Cash Register system activated in your account. You’ll also need to have tracked all payments (for both appointments and classes) in Setmore through the entirety of 2017.
If you didn’t track payments in Setmore through 2017, then now is a great time to get started for 2018!
Look up payment history in Setmore
Setmore lets you easily look up your past payment history over a custom date range. To get started, navigate to Settings > Payments > History.
- From the Payment Transaction History screen, use the date picker to select a beginning and end date range. Choose January 1st, 2017 through December 31st, 2017. Click anywhere outside the date picker and Setmore will begin recalling the data.
- Once your data loads, you can browse through it here. Setmore does not currently display sum totals for any columns listed. However, you can click the “Export as .XLS” button in the top right to download a spreadsheet of all the data.
For the next step, you’ll need to open the .XLS file with your preferred spreadsheet software. Don’t have spreadsheet software? No worries – you can use Google Sheets, which comes free with any Gmail or Google account. Learn how to import a .XLS file to Google Sheets >
Formatting dollar amounts as numerical values
Before we begin, you’ll need to verify that all data in the “Amount” column is formatted as a numerical value. This basically means that the software in question will recognize dollar amounts as a number, rather than as text, which the software won’t be able to sum.
- Click and drag to select all values in the “Amount” column. This will be the amount you charged for each service, and its location in the spreadsheet may vary. By default, the values should appear under Column F.
- Navigate to Format > Number > Number. Again, this feature’s location may vary depending on your software.
- Once all the numbers are correctly formatted as numerical data, we’re now ready to sum the total, which will give us the total revenue for the year.
A special note on refund values
If at any point during the year you issued a refund for a service or a charge already levied on a customer, then you’ll need to take one additional step to ensure that these values don’t inflate your overall total revenue.
Unfortunately, Setmore tracks refund values in the same column as “charges,” which means they’ll get totaled together. To prevent this, we’ll need to convert the positive refund values into negatives, so they’ll cancel out the original charge and your total revenue will be accurate.
- Select all values in the “Type” column. In our spreadsheet, this default column for “Type” is Column H.
- Navigate to Data > Sort sheet by… and choose either ascending or descending, it doesn’t matter which one. We simply want to isolate the Refund values.
- Now all the Refund values should redistribute so they’re all grouped at the top or bottom of the column. In the corresponding Amount column, convert the value shown for each Refund to a negative. So, if the original value was 50.00, the updated value will be (-50.00).
You’re now ready to find the sum total revenue for all your 2017 appointments.
Finding the sum total revenue
- In the bottom-most cell of the Amount column, input a SUM function and select all the values in that column, or just type =SUM(your column range).
- Your column range should be “FX:FY”, where…
F corresponds with the column letter that you see “Amount” listed in,
X corresponds with the top-most row that has “Amount” data in it,
and Y corresponds with the bottom-most row that has “Amount” data in it.
- Once the function has been entered into the cell, hit the Enter key and you should see the sum total of your annual revenue for all appointments in Setmore.
Mining the data for insights
You can continue to shuffle the data based on different sorting actions to unlock some insights about your business:
Busiest staff member – Sort by the Staff column. Whichever staff member name occurs most often is your busiest staff member. Whichever staff has the highest sum total revenue is your highest earning staff member.
Most popular service – Sort by the Services column. Whichever service occurs most often is your most popular one. Whichever service has the highest sum total revenue is your highest earner.
Best returning customer – Sort by the Customer Name column. Whichever customer name occurs most frequently is your most regular customer. Whichever customer name generates the highest sum total revenue is your business’ new best friend.
Thank you for reading 🙂
Have any tricks of your own for tracking your revenue and growth in Setmore? Post a comment below to help educate others!
In the meantime, start logging your customer payments and make sure 2018 is a blast.
Categorized in: Resources