SYWTOABP: Spreadsheets

This is, perhaps, the least sexy topic of a decidedly unsexy series of blog postings about opening a brew pub in BC. However, if there is one thing that becomes readily apparent as time passes by, is what a disadvantage people are at in the business world if they can’t use Microsoft Office, particularly Excel. Relying exclusively on consultants, accountants, bookkeepers and bloggers to tell you which numbers are the most important to your business, is not a recipe for long-term success and will cost you money.  This isn’t just some blogger talking. This is advice that the authors of “The Knack” also try to hammer home.

Accounting isn’t rocket science.  It relies, for the most part, on simple addition and subtraction.  Time Value of Money calculations are a bit more work, but Excel has ‘wizards’ that probably can walk you through it by now.  This post isn’t about accounting. It isn’t even about the time value of money. It’s about taking the numbers you have and finding out the numbers you need to know.

There have been several Excel spreadsheets created as part of this series on opening a brew pub. I’ve tried to keep each one  as simple and small as possible. But at some point, you need to take your costs and sales projections, and try to figure out how much money you need to open your establishment; how much money you need to make to stay in business; how much money you need to borrow; and how much effort it will take to pay back the money you borrow.

People are always searching the Internet for the answer to some question or a solution to some problem. There is no one spreadsheet to solve all problems. I know because I’ve made some spreadsheets that are so elaborate, most people can’t understand what the fuss is about until they try to duplicate the results. Even I wouldn’t want to try and duplicate the results of some spreadsheets I’ve made.  Another document people search the Internet for is a business plan they can just copy.  I found one for a pub.  It is older, but serves its purpose as an example.  One thing the author didn’t give away was the spreadsheet(s) that were created along with the MS Word document. These are either considered more valuable or just harder to explain. Regardless, I’m going to reproduce a working spreadsheet for one of the tables in this pub business plan, and then I’m going to try and use the numbers we already have to figure out the numbers we don’t have for our hypothetical brew pub.

Spreadsheets Previously Created

• Market Size & Sales Estimate # 1
• Equipment Needed for a Bar
• Equipment Needed for a Commercial Kitchen
• Sales Forecast Estimate # 2
• Audio & Video equipment for a small stage
• Brewing Equipment
• Brewing Equipment system # 2

Here is an example of a pub business plan.  The working clone spreadsheet I decided to make was for the first year pro forma cash flow statement, as most any investor or lender will want this plus 3- or 5-year projections too. This isn’t exactly how I would lay things out, but it matches the example business plan very closely, which was the goal.

The second spreadsheet I created for this post takes all the information we’ve learned so far, combined with a few more estimates and a few standard calculations, to produce key numbers such as:

• Startup Costs
• Daily Gross Revenue
• Monthly Gross Revenue
• Monthly Debt Payment
• Break even sales number
• Monthly Net Income
• Pretty graph of monthly costs

If the spreadsheet shows your proposed business plan making a lot of money, remember they are just paper profits. The spreadsheet is based on estimate on top of estimate. There are all sorts of obstacles and unexpected expenses to starting and running a business. Tenant improvements and construction costs are estimated simply using dollars per square foot. Office space can run $25-30 per square foot. A brew pub will cost considerably more. You also need cash on hand, even after you open. Never forget cash is king.

Guy Kawasaki’s Enchanting Financial Forecasts

Guy Kawasaki is one of the startup or entrepreneurial gurus out there, he wrote “Art of the Start“, the book I most recommend to folks looking to start an entrepreneurial venture. Guy was an early Apple employee and is now a venture capitalist and author. He and his partner, Bill Reichert, present their guide and template for creating and presenting financial forecasts to potential investors.

They too have a number of questions they think any entrepreneur and their financial projections should be able to answer:

• Do the capital requirements shown in your projections match the funding you are asking for?
• Do you know how many customers you have to land to generate the revenues you are projecting?
• Do you know how long it takes and how much it costs to acquire a customer?
• Do you know what resources will be required to support customers?
• Do you know how much you will have to spend to stay ahead of the competition with your product or service offering?

General Business Planning Spreadsheet

I created a general business planning spreadsheet, which should work for brew pubs just fine and might be of interest to people who end up here after searching in Google.