How Microsoft helped this bar figure out that vodka was costing it a fortune

Credit:Marnie Joyce via Flickr

Do you have any idea where your business makes and loses money? Would you like to find out? 

Take a bar. If your business is serving drinks, you have to expect that the odd bottle will get broken or that bar staff will pour extra generous measures -- because the stronger the drinks, the bigger the tips. But suppose you could actually see what that means to your bottom line? If you could look at the data and work out which are your most profitable products and what costs you money, could you find a way of maximizing profit, minimizing loss while still keeping customers and staff happy?

For one Seattle bar that tried out an early version of Microsoft's Power BI cloud business intelligence service, the discrepancy between the alcohol bartenders were getting through and what they were charging for turned out to about $5,000 a month. The owner wasn't surprised; that's par for the industry. But then he found something that did surprise him: When he looked at sales and losses in more detail using Power BI's Q&A natural language tools for exploring data, the problem wasn't evenly spread across the 139 different kinds of alcohol in stock. Almost half of the missing money came from vodka sales, and of the six vodka brands the bar serves, just two brands -- Crown Russe and Stolichnaya -- were responsible for the majority of the losses. 

Credit: Microsoft
Half the inventory losses at the bar came from pouring too much vodka

And those two brands were making a big difference. The bar sells a lot of Bud Lite and even more vodka but the vodka was only making half as much profit as the Bud.

If you own a bar, you can count the bottles of beer and wine to see what you're serving and what you're losing. But when it comes to spirits, you have to weigh the bottles on a very precise scale, calculate the viscosity for that type of liquor and turn it into the ounces left in the bottle. It's a lot of work for everything you have on the shelves so this bar used a weekly inventory service that did the weighing. But when he saw how much money he was losing on just six bottles, the owner bought his own scales and started weighing the vodka every night and putting the results in a CSV file; he saved those in a folder that Power BI's Power Query tool was set to import and clean up automatically. 

Credit: Microsoft
Processing inventory data in Power Query

Then he used Q&A again to compare that to the employee records showing who was working the bar each night. He wanted to see how close each of them was getting to the 1.5 ounces of vodka that makes for the Goldilocks of cocktails; not too weak and not too strong.

Q&A lets you type in queries in close to normal English and automatically displays the results in the most relevant chart type. That made it clear that one bartender who had been working at the bar for a while was putting an average of 1.7 ounces in his cocktails, making strong drinks that bought in big tips, but costing the bar money. Another bartender was new and was only pouring 1.4 ounces each time -- and while they save money, those weaker drinks are likely to send customers to another bar. 

Credit: Microsoft
Some of the bar staff were pouring too much vodka; also bad, making the drinks too weak

To give the bar staff an incentive to be more accurate, the owner set up a competition; the bartender who got the closest to consistently pouring 1.5 ounces every time would win a trip to Hawaii. Everyone had access to Power BI -- in the browser or using a Windows 8 tablet app -- so they could see how they were doing, and how the competition was stacking up. Amir, the more experienced bartender, quickly cut down his pours; in fact he overcompensated and started making his drinks too weak. Just looking at the running total for his average 1.67 ounce pour wouldn't show that, but the chart of pours week by week made it obvious.

Credit:Microsoft
Share reports so everyone can see them

Michael, the newer employee, also started pouring too little but the graphs helped him correct that and get much closer to the ideal pour and the holiday in Hawaii. And the bar is getting closer to clawing back $2,500 a month in revenue.

Business in the cloud

Over the last decade, business intelligence software has gone from expensive, specialized tools that business analysts use to create reports you run once a month, to much more accessible solutions -- especially Microsoft Office. 

Excel is near-universal as a data analysis tool; recent versions have added more tools to the familiar pivot tables and pivot charts. Power Pivot handles large data models with thousands of rows that used to need a database like SQL Server and Power View lets you create visualizations with charts and graphs that combine information from more than a simple range. 

Excel 2013 adds Power Map (codenamed GeoFlow) for visualizing data on maps and Power Query (codenamed Data Explorer) for pulling data from a wide range of sources -- including government statistics and big data. 

The tools are good, but they rely on your business having SharePoint set up so you can share your BI reports and visualizations with colleagues. The insight you get by being able to dig into your data isn't much use stuck in your Excel worksheets -- or languishing in someone's inbox along with all their other mail. After all, if you learn something from the data you have about the business, you probably need to work with someone else to do something about it, and they might want to do their own analysis. 

If you don't want to run SharePoint yourself, Microsoft offers SharePoint Online as part of Office 365. The new Power BI service Microsoft announced last month at its partner conference will be available as a preview this month. It combines the BI tools in Excel, the BI publishing tools in SharePoint, the new natural language Q&A analysis tools and tablet apps (initially for Windows and later for iPad). It also includes a Data Management Gateway you can run on premise to give cloud users access to data sources you maintain inside your firewall, so you could mix your own inventory and sales figures with official statistics for population figures, health reports and other public data. 

Credit: Microsoft
Q&A suggests how to analyse data as well as visualizing it

"End user expectations are changing for tools," Microsoft's Michael Tejedor told us; "They need something to deal with the meaty workloads that are being required. They want to discover data, clean it up and present it. With Power BI, I can take that BI workbook I've spent time on that I want to share out. This is a cloud service where I can publish up into Office 365 to share that workbook, and I can also share the queries I've created." 

You can view those shared queries in any HTML5 browser (which means pretty much any tablet or smartphone); the Windows 8 and iPad apps will also let you interact with the queries, if you want to do your own exploring.

The emphasis is on making it easy to start digging into your data. Power Query remembers the processing you do to clean up a data source like splitting columns, removing duplicates and pivoting or unpivoting data (a feature Microsoft has been asked for regularly), and shows those steps when you look at the data. You can schedule it to import more data at regular intervals, which updates both the figures in your reports and the data model they're based on. So if you introduce new products, they'll show up in reports as soon as they're refreshed. 

The natural Q&A tool is particularly powerful; as you type in what you want to look at, it autocorrects what you type so it matches the data model, analyses the semantics of what you're looking for and suggests ways of exploring the data. If you type in "profits," the search box suggests looking at it "by day" or "by week day" if those will show different patterns. And as you change your query, Q&A changes how it shows the data to give you the best visualisation -- the data might change from a table to a bubble graph to a line chart. This is the kind of automatic visualisation Tableau is so good at, but you don't have to learn a whole new set of tools for accessing your data.   

Credit: Microsoft
Compare two or three sets of data in Q&A

Having all this as a cloud service means you can save the time it usually takes to set up servers and install software, and get on with the part where you learn about what's actually going on inside your business. It's also interesting to see Microsoft pushing SharePoint as a platform for publishing and sharing "live" data in a way that moves beyond standard Office documents -- something Steve Ballmer highlighted as a key area for the company to work on when he announced the recent Microsoft re-org.

If you want to see what insights you can get into your own business. Power BI will be available as a preview later in August along with the Windows tablet apps (although the Q&A feature won't launch until November and the rest of the tablet apps won't be available until the service launches). 

Join the discussion
Be the first to comment on this article. Our Commenting Policies