In May 2012, JP Morgan Chase lost more than $2 billion. The cause wasn't some massive stock market crash. Nor a rogue trader. It was a shitty spreadsheet.

A spreadsheet that they were crazily relying on for their value-at-risk model had been copy-pasted to death. A single error introduced in one formula meant that the bank widely underestimated the risk of their investments. Goodbye $2 billion.

If spreadsheet errors can happen to a Big Four bank, they can happen to you. And if these errors are in your revenue recognition spreadsheets, they can easily be enough to ruin your company.

The Wonders (and Woes) of Excel

Excel is great. It excels at even the most complex calculations. You can plan the entire financial budget of your company using just a few cells. You can quickly visualize, manipulate and share data in a format everyone can understand.

You can even draw Pikachu.

f(Source: hongkiat)

But a wise man once said that great companies cannot be built on Pikachu drawings alone.

With company finances, spreadsheets let you get started with the basics. But once you move beyond that, issues quickly arise. According to Ventana Research, there are three common spreadsheet mistakes:

Data entry errors

 If you've never made a spreadsheet mistake in your life, you're lying. When this error skews your growth curve, it's a pain. When it completely changes your underlying revenue, pain is an understatement.

• • 35% of companies have fundamental data errors in their spreadsheets.

Example. The Fidelity Magellan fund was expecting to turn a tidy profit back in the mid-90s. When they didn't, their shareholders wondered what had happened to their dividend. Turns out, when filling out the company's spreadsheet by hand, an accountant had missed a minus sign. What the company thought was $1.3 billion in profit, was actually a -$1.3 billion loss.

Formula Errors

 With the exception of #DIV/0!, there isn't much error-catching built into Excel or its clones. If your calculations are incorrect but are still computable, it is 100% up to you to catch them.

• 26% of companies have formula errors in their spreadsheets.

• Example: This is what went wrong with JP Morgan Chase. A formula in their VaR model was dividing by the sum of the inputs rather than the average. This reduced the volatility in their risk model by at least a factor of two, making what turned out to be risky bets look a lot more secure.

Formatting errors

Excel has all sorts of formatting options that can help you better visualize complex data. But these can also easily hide data from you that your calculations are relying upon.

• • 19% of companies have formatting errors in their spreadsheets.

  • Example: When Lehman Brothers went under in 2008, Barclays Capital snapped up what was left of the investment bank's US assets. But due to a formatting error, they literally got more than they bargained for. Someone had “hidden” 179 lines in a spreadsheet containing over 1000 rows of contracts that Barclays were buying. It wasn't until after the deal had gone through that someone spotted the 179 extra contracts the bank had bought.

The Perils of DIY RevRec

Each of the above issues can trip you up when calculating revenue recognition in your own spreadsheets. A single data or formula error can have drastic implications.

But this is true of all finance and metric calculations. What makes DIY revenue recognition particularly nasty is the subjectivity of when revenue should be recognized, combined with the critical importance of getting it right.

In regular businesses this is difficult, in SaaS it is even more complex. Revenue recognition protocols aren't built for SaaS, so reconciling subscriptions to GAAP standards is time-consuming and error-prone.

In particular, there are four reasons why doing this manually will end up being bad for your business and brain.

It Will Quickly Grow Out of Control

Even with one subscription, you still have to work out when you are entitled to the payment. Is it when you bill? Incrementally over the course of the month? In relation to how your customer is using your product?

Now spool that up to cover multiple subscriptions, starting and finishing on different dates. Then add customers churning out while others upgrade. For a finale, add in the difference between your monthly subscriptions and your annual subscriptions.

Every time someone comes, goes, upgrades or changes their plan you have to take account of it within your spreadsheet and set the right calculations for recognizing the revenue from that customer.

You'll Lack Control and Auditing Abilities

Revenue recognition is part of your accounting process. It determines your end of year financial reports, which in turn might determine whether you get fundraising, how much you can expand your business, or how much you can pay out in profit to partners. This is important.

So you need tight controls on who can view and amend your revenue recognition reports. You also need an audit trail so that everyone who is depending on these numbers—investors, partners, banks, or even just you—can easily go back and see what happened to revenue throughout the year.

Neither of these are easy if you are doing this manually. In a spreadsheet, you'd have to restrict access to some people at your company, but allow access to others. Some of these authorized users might be allowed to change one part of the spreadsheet but not another part, meaning you would have to lock certain cells to certain people.

All the while you would be tracking each and every change to make sure you could go back and fish out any errors.

Every Change In Your Business Will Need to Be Accounted for

Here at Price Intelligently we like change. We think you should be updating your pricing constantly to keep in lock-step with your customers and your value.

But these changes don't happen in a vacuum. If you change your pricing page, you then have to change your underlying spreadsheets to account for this. If you are changing your pricing twice a year, then you effectively end up with two different revenue recognition spreadsheets to reconcile in Q4.

That is only the start of your headache. The same problem occurs if you offer a discount, start a new pricing tier, or have to give a refund. If you offer an enterprise tier then this adds further troubles. Now you'll likely have specific, customized contracts to recognize. These will not only have different pricing, but also have different services, all of which will be recognized at different points during the customer's lifecycle.

You'll Spend All Your Time Syncing Systems

Finally, it is just a monumental time-suck. If you are using a spreadsheet for revenue recognition, then there has to be a member of your team dedicated to:

• keeping this system of record updated throughout the month

• closing it out and reconciling at the end of the month

• syncing with your other financial, payment, and invoicing systems

Ultimately, you will spend too much time closing books and wrangling data, instead of working with your product and customers.

Confusion Abounds

Revenue recognition is extremely complicated, particularly in SaaS. This is something that trips up accountants with decades of experience. You can't do it all in a Google Sheet. It might seem like a great idea to start and a way to save a little cash, but there is no way it ends well.

Instead, get your revenue recognition set up properly from the outset so that you can always be confident in your accounts and know exactly where your revenue numbers are.