Sep 4 2010

How to Developed a Business Case Model in Google Docs

If I’ve had a dollar for every time someone come to me with a half baked business idea, that wouldn’t make for a good business plan but I would have enough for a latte. Everybody at some point has had a great idea about a new business or product, but very few people do anything about it beyond telling a friend. I’ve had way to many friends and family come to me telling me about how if we can get into such and such market and sell such and such product we could make such and such amount of money. It’s almost funny to see people like this just make up numbers up how much money we would make. Every time I hear someone make up a number, I ask for for simple spreadsheet with some basic formulas that describes the business case model. To understand the business risk, you need to understand the cost and benefit of the business.

Let’s imagine we are thinking of getting into the online t-shirt business, we can use a spreadsheet to calculate the estimated costs of running the business to profitability before even getting started. If you are thinking of selling t-shirts online its good to track the base cost per shirt, the markup per shirt, shipping costs, the monthly operating cost. Having these figures you can calculate the total price of the shirt (base cost + markup + shipping) to for each item and how many shirts you would need to sell to cover your operating costs. If your business plan is simple, such as this, the minimum you can do is create a spreadsheet to describe it and model your business.

You can use Microsoft Excel or with spreadsheet document in Google Docs. I’ll be using Google Docs but the following would also work in Excel. In Google Docs create a new spreadsheet. In the new spreadsheet add a header for each variable you would like to track, such as base cost, markup, shipping, operating cost, expected sales, and expected gross profit.

Let’s say that it costs $10 for the each shirt, and we would like to make $4 off of each shirt sold, and that on average it costs $3 to ship, we can easily calculate what our recommended sale price of each shirt is by adding all of this together. To add it all together in the Cost/Item cell, type into the cell and enter the equal (=) symbol. This would allow tell the spreadsheet that this cell will be a formula. You can add two values from two different cells by the something like the following = A2 + B2 + C2. The formula will add the value at A2 with that of B2 and C2 and put the computed value in the cell with the formula.

Using Formula on Google Docs Spreadsheet

Using Formula on Google Docs Spreadsheet

If we expect to sale 50 items at this price we can calculate how much money we will take in by using another formula, the cost per shirt times the number of shirts you plan to sale, and in this example the formula would be the following = E2 * D2.

Calculate Expected Revenue

Calculate Expected Revenue

Now the value of simplifying your business into a spreadsheet like this is that if you can easily visualize the effect on your business if the base cost per shirt goes up or if you don’t hit your target expected sales numbers. If you model your business in a spreadsheet in something other than a cocktail napkin you can simulate different business scenarios more easily and come up with numbers closer to reality and based on some thought. Of course, when modeling a business you will have a lot more variables to take into account, but fortunately spreadsheets like those supported in Google Docs and Excel have a wide range of formulas and functions.