B.C. Beer Blog

The who, what, where, when, why, and how of B.C. craft beer

SYWTOABP: Spreadsheets

with 7 comments

Pro Forma Cash Flow ExampleThis 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.

Need to know or estimate to open a brew pubPeople 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

Need to know for business planningHere 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.

7 Responses

Subscribe to comments with RSS.

  1. […] need to be revised and adapted for the new decade and BC.  Part of that work was done by me in my big spreadsheet post.  I thought we should look at the major sections of an actual business plan and the information […]

  2. […] outlining production and sales forecasts for a three year period, must be submitted.” I created a lot of spreadsheets for this series, but every business is unique and requires customized spreadsheets to perform accurate financial […]

  3. […] but also what to leave out when building this spreadsheet and previously when building similar spreadsheets for the So You Want To Open A Brew Pub series of blog posts. This spreadsheet goes further as it produces three years of projections as well as a hopefully […]

  4. I have been checking out your business analysis spread sheet and it has been easy to work with and adds some clarity to the planning process- thanks

    Bruce Miller

    October 19, 2015 at 7:57 am

  5. Thanks for all the information you have provided. Even though I have been very successful in several businesses, it is helping me greatly in my business plan for a microbrewery. I’ll keep you posted on how things progress.

    Bob Robinson

    December 11, 2015 at 12:45 pm

    • Good luck, let us know when you open, obviously this blog is focused on British Columbia but our readers have been known to travel and drink beer at the same time. It’s hard work I assure you.


      December 11, 2015 at 3:34 pm

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 )

Connecting to %s

%d bloggers like this: