On the first of every month I spend much of the day updating Carbonmade's finances with last month's data. I've got what I think is a good process down after years of doing it, so I thought I'd share it with you.
Get the Data / Back up the Data
The first task — and by far the least fun — is logging into all of our payment services to download last month's data. All either come in a PDF or Excel format and all are difficult to locate. Why do payment services make it so difficult to download statements?
These are the five services we use:
- American Express — Credit Card
- American Express — Merchant Account
- Paymentech
- PayPal Merchant Services
- Bank of America Small Business
All of them allow you to more or less easily download a copy of your statement except for PayPal. PayPal makes you log in to their website, browse to the history tab, select the dates, select the format, and then wait for them to generate and e-mail you a statement. It never takes more than ten minutes, but it's a pain. Why not simply generate the monthly statement automatically?
I then rename all five documents — because they download with weird names like "MSAF_1.pdf" — to easier-to-read names like "AmEx-CC-2010-01.pdf" for January 2010's American Express Credit Card statement. I then back up everything offline and with Dropbox.
Update The Books
The geeky accounting do-everything-myself side of me has always loved Excel. And while a company of our size should probably — although that's up for debate — use something like QuickBooks, I stick to what I know and love.
A friend of mine who is a bit older and runs a rather large group at Ernst & Young spent a very long weekend with me a little over a year ago to help get Carbonmade's finances in order. We went back to almost the first day we began accepting payments — nearly four years ago — and got everything in order.
Together we built an Excel file to handle Carbonmade's finances. It now automatically calculates an Income Statement, a Balance Sheet, and a Trial Balance all based on the monthly data I input. We separated all the different expenses into their own sheets (e.g. Software, Web Hosting, Revenue, Merchant Fees, etc.) and created a Cash sheet to project our cash flow.
I now simply have to take the charges from our American Express Credit Card statement and fill them in on the corresponding sheets. I then take those and copy them over to the Cash sheet to get an idea of our cash flow for the month, which also takes into account the concluding cash balances from the previous month.
Update the Total Signups Projections
My friend Vadim Tsipenyuk — formerly of Goldman Sachs — and I crafted a fairly simple spreadsheet to calculate our Last Twelve Months (LTM) growth. You input the new signups for the month and it projects out your LTM Growth (Compiled), Monthly Growth (Compiled), LTM Growth (Signups), and Monthly Growth (Signups).
It's not all that sophisticated, so it doesn't take into account future marketing launches or new releases to your website, but it does handle the basics fairly well. You can also adjust the percentages if you know in advance that you're going to be releasing a new version of your product in two months and you anticipate gaining an additional 10% in customer volume that month.
Update the Paid Signups Projections
Our paid signups projections spreadsheet is a bit more sophisticated than its total signsups projections counter-part. Vadim also helped me set up this additional spreadsheet, on which I input our Subscriptions (New), Subscriptions (Cancelled), Invoices (Billed), and Invoices (Paid) at the end of every month.
With those four pieces of data, it projects:
- Net Subs
- Total Subscribers (Cumulative Net Subs)
- Implied Growth in Total Subs
- New Subs / Last Month's Total Subs
- Churn Rate (Sub Loss / Last Month's Total Subs)
- Total Invoices / Cumulative Subs
- Paid Invoices / Total Invoices
Update the 6 Month Budget
The kingpin of them all, the 6 Month Budget, helps us plan our hiring and marketing. As I've always said, "cash flow is king" and once we've got an idea of our revenue from our Paid Signups Projections, I input that and our various expenses to get a sense of our cash flow.
Revenue I project out:
- Service Fees
- Consulting (we still have a number of web hosting clients from our consulting days)
These are the expenses I detail out:
- Merchant Fees
- Web Hosting
- Office Supplies
- Software
- Telephone
- Fax
- Internet
- Marketing
- Mailing
- Lawyer
- Accountant
- Healthcare
- Office Rent
- Apartment Rent
- Food & Drink
- Other Expenses
- Payroll
Taking all this together, I can project our cash flow over the next six months and see when and where we might hire new people or spend extra money on marketing. I'll also go in and add certain expenses at future dates. For example, I know I'll spend $1,500 for SXSW in March, and then our bill with our accountant will come due in May.
Off to the Accountant
I'm not a certified CPA, so at the end of the day I send everything off to our accountant to do things right. I mainly do the stuff I've discussed above to get a better sense of what we're spending our money on and how much we're making. Consulting that information and doing simple projections with it gives me a much better ability to make day-to-day decisions and long-term decisions.