Thought of opening another wine store? Me too. I’ve copied a spreadsheet below which I’m using to evaluate whether to open a wine store in a small coastal resort town nearby.
The town is a bit like the (Long Island) Gold Coast but not as much money, or population for that matter. It is also seasonal so in winter it’s dead and in summer its extremely busy. In fact there are so many things wrong with this venture that it probably won’t go ahead.
However I’d like to start up a concept store that shows the best of wine, marketing and technology. It would be an “Open Store”. A bit like Open Source Software or Open Research, that is the processes and techniques would be free for anyone to see and copy. Note the wine would be at standard retail prices! I’m helpful – not crazy.
So I’m interested enough to go through a feasibility study and I thought I’d share with you the start of this study – the financial model.
Feel free to download the spreadsheet yourself (it will immediately download as a Excel 33kb file, or see below for instructions on how to download it care of Google Docs).
The Wine Store Financial Model
The financial model is split into two sections – Input and Calculations.
The input section is all the variables. You change these to see how they effect cash flow at the bottom of the spreadsheet. There are no formulas in these cells – feel free to change them as much as you want and see how it affects Net Cash Flow (i.e. profit).
There are four calculation sections: Revenue, Variable Costs, Fixed Costs, and Cashflow.
Do not change these cells (unless you want to change how the spreadsheet works of course).
The key figure is “Cumulative cash flow periods 1-3“. I’m likely to be cash negative in Year 1 (fit out costs and inventory) but expect to be profitable after that. Usually I’d have a much higher expectation of profit but I’m willing to make less ROI for this venture.
Note that we are simply looking at feasibility. If I end up taking this to the next step I will complete a full monthly cashflow analysis for 36 months (companies fail because they run out of cash not necessarily because they are unprofitable).
Its a pretty standard feasibility model.
Apart from the way I treat Segments and Promotion
I’m a bit of a fan of Project Genome market research so I’ve split customer numbers into the segments – each with different market share, price points and bottle purchase volume.
I’ve done this because the revenue estimation is the most difficult, yet the most important. Costs are reasonably certain and controllable. I may go into ways to estimate revenue in later posts (prompt me if you’re interested).
I have outlined six marketing activities:
- Internet Marketing (what do you mean what’s this! read this blog:) )
- In-store tastings (regular tastings every week)
- Tasting Vending Machines i.e. machine based, nitrogen pumped, bottles (see enomatic)
- Concept Store (mainly PR of new retail technology aimed at a nearby large city)
- Price Promotions – a discount off everything
- Local newspaper adverts.
To turn the marketing activity on and see its effect on Net Cashflow type 1 in the relevant cell, in range D70 to F75. To turn it off type 0. Do not use any other value than 1 or 0 or your results will be nonsensical.
I suggest you download the spreadsheet, here’s the best I can do to show you it on the web:
(Email users – if it’s blank above then it’s due to the iframe code I use. Apologies, just download it as per below.)
Now I’ve changed the figures but believe me the real ones are not much more promising.
To download and edit this yourself
Download the spreadsheet directly or do the following:
- Click or Paste this Google Docs URL into your browser
- Go to the bottom of the page and click “Edit this page (if you have permission)”. This will open a new page.
- Under the Google docs brand, click File > Download as > Excel
Then open the spreadsheet up in Excel and start playing!
Change a variable, see how it affects cashflow, write it down, go back to the base case, change another variable… You’ll see what variable are the most important and sensitive variables. You can then spend more time on assessing these than the other less important variables.
The most sensitive variables are almost always gross margin ($) and volume
Here’s what I found most sensitive:
- Store Penetration in Y3 and Y2
- Image Seeker Segment Size, Market Share, and Volume purchased
- Enthusiast Segment Size and Market Share
- Luxury Enthusiast Segment Size
- Conversion rate from internet marketing campaigns
In my case I’m not looking at discounting so margin (price and cost) are not as important. Instead it all comes down to the volume. How big is the market and what share will I get?
By splitting out the segments I can be a lot more accurate in my estimates than if I just use a simple wine drinker average. In this case I need to research just how large the higher margin segments are in the local area.
The Craziness of Price Promotions
It also shows the craziness of competing on price (unless you’re Walmart).
Assuming a price elasticity of 1, i.e. if I drop prices by 20% I’ll get a 20% increase in volume, I’ll make a massive loss of about $280,000 on the promotion (i.e. 3 year cashflow comparison with Price Promotion turned on for all 3 years vs turned off for all 3 years).
If price elasticity is 1.5 that drops to $270K. If its 3 it drops to $228K, if its 11 I breakeven…
If price elasticity is 0.5 then I lose almost $300K.
Now I appreciate that you could limit the discount to a few bottles. So you advertise a special and hopefully the greater store traffic makes up for the lower average price.
However the numbers will most likely work against you – time to find another way to promote your wines. Frankly for the good of the industry if not your own business :).
So what did you see? What would you change?
Photos courtesy of: 039