Do-it-yourself billing with MS Office

Much as we hate to admit it, being a freelancer means being more than mere writers. If we want to be paid, we need to be staff our internal accounts receivable and billing departments, too. Yeah, it’s a pain, but it doesn’t need to be. Once you get your processes set up, keeping track of your cash flow becomes pretty brainless. Here’s my approach.

Though there are some off-the-shelf solutions you can try, as I noted in a previous post, I prefer Microsoft Office. For billing, I recommend creating an MS Word template (eg, MyInvoice.dot) that contains the following information:

  • Your name
  • Your address
  • Client contact name
  • Client billing address
  • Invoice number
  • Invoice date
  • Payment terms (eg, Net 30)
  • Your tax ID number (if appropriate — not your SSN)
  • Your client’s PO number (if appropriate)
  • An itemized description of the work, including submission date, fee rate (per-word or per-hour), word or hour count, and total
  • A box at the bottom “For office use only”, including room for an approval signature and date.
  • Put a nice logo on it too, if you can, to make the thing look professional

My invoices look like this:

I save each one by numbered filename, eg “Invoice 001.doc,” and keep them in a directory called Invoices. As checks come in, I print the invoices, attach the pay stub, and file them. At the same time, I move the electronic invoice into a subdirectory called Paid, so I know at a glance which invoices are outstanding.

For invoice tracking, I use Excel. My goal in setting this up was to track (a) how much money I’m billing per month, (b) how much money I’m owed, (c) how I’m doing overall (ie, is my work steady, increasing, or decreasing), and (d) my taxes. With that in mind, I created a spreadsheet that contains two tabs.

The first tab (called “Invoices”), is a table with the following columns:

  • Invoice number
  • Date
  • Customer ID
  • Billed amount
  • Date paid
  • Amount paid
  • Days past due (This is calculated automatically using the formula: “=IF((F4),””,DATEDIF(B4,TODAY(),”d”))”; this formula says, if there is an amount in the “amount paid” cell (in this case, F4), do nothing, otherwise, enter the number of days elapsed since the billing date (cell B4). For extra panache, I use conditional formatting to color the text red if the invoice is more than one month past-due and I need to reach out to my editor.)
  • Taxes paid (ie, in which financial quarter, such as Q3/2010)

The second tab, called “Overview,” data-mines the Invoices tab to calculate, by client, how many invoices I have submitted, their total, and how much I’m owed. In addition, I added a simple table that tabulates total billing per month (as well as average billing per month), and a graph of that data that includes a “moving average” trendline.

To make these calculations, I let Excel do the work, using formulas like this:

=COUNTIF(Invoices!C4:C11,Overview!A4)

and this:

=SUMIF(Invoices!C4:C11,Overview!A4,Invoices!D4:D11)

SUMIF and COUNTIF are Excel formulas. In this case, COUNTIF counts the number of times the Customer ID (specified in Overview tab cell A4) appears in the Invoices tab Customer ID column. SUMIF scans Invoices tab cells C4-C11 for the Customer ID (again specified in Overview tab cell A4), and when it finds it, adds the amount billed (specified in Invoices tab cells D4-D11).

Well, there you have it: my low-budget accounts receivable department. It took me a few days to work all the kinks out, but since then it’s been smooth sailing.

I’ve made the invoice spreadsheet available here. If you use it, I recommend double-checking the Overview tables as you add new clients; occasionally, in its zeal to simplify your life with autofill, Excel fudges up the formulas. Caveat emptor.

How do you track your clients and billing? Let us know in the comments!

Advertisements

About jeffreyperkel

I'm a freelance science writer who focuses on laboratory methods and technologies -- the "how of science."
This entry was posted in The Portable Office. Bookmark the permalink.

2 Responses to Do-it-yourself billing with MS Office

  1. John Pavlus says:

    These handmade mega-computational sheets are awesome!

    I used to use one very similar to your Sheet 1. Then I got tired of maintaining it (and always being paranoid that I programmed one of the cells wrong or something) and started using Harvest, a webapp that combines and automates all of the same data/tracking/invoice-generating that you’ve painstakingly programmed. It costs $12/mo, but the interface is fantastic, professional-looking to clients, idiotproof for me, and has exactly the level of easy customization (without TOO much) that fits my business’s needs. You get a 30 day free fully functional trial at http://www.getharvest.com, which was more than enough time, for me, to give it a spin and see if it’s worth the dough. Before I found Harvest I used Zoho Invoice, which is a the “Android” to Harvest’s “iPhone” (ie, more customizable, less sleek/intuitive)

    Harvest doesn’t make cool visualizations like your Sheet 2 does, although it generates the same overall figures. But one-glance bar graphs are really useful for checking in on Profit/Loss trends so I may just end up using your second sheet WITH Harvest.

  2. Thanks John! I’ll have to give Harvest a look.

    I should also add that my spreadsheet is definitely a home-brew blend of automated and manual. I manually tally up the monthly totals and add them to the “Billing by Month” table, and then manually update the graph to reflect the new data. (Which is simple BTW: select the bars on the graph and extend the data range boxes to include the new month(s)). I thought about trying to automate that, but it seemed considerably more trouble than it was worth, and, in the vein of your P vs NP analysis, was a problem that becomes more and more computationally intensive the more invoices you have. That said, the table automagically redoes the average/month calculation, and extends the trendline as well.

    One other point I should mention. In case it wasn’t obvious, the Customer ID field in both the Invoices and Overview tabs must match exactly. (eg, I use IDs like “SCIENCE”, “NATURE”, and “SCIAM”.)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s