The Spreadsheet Advisor

“It takes something more than intelligence to act intelligently.”
― Fyodor Dostoyevsky, Crime and Punishment

If you are standing on the corner and suddenly realize you are $10 short, and curse yourself thinking:"I really need a BI tool to administer my cashflow." it may be slightly overkill. A coin in the shoe may be a better solution for sudden cash shortage.

However, if you are even a small company, it may be a good idea to implement some kind of Business Intelligence.

Why?

First of all, let us be clear on what BI is all about. When people ask me what I do for a living, I reply:

"I make comprehensible information out of incomprehensible data".

Now, as I described in my post about "Value chains", your business, no matter how small, can be regarded as a serie of processes. Processes which all cost something when they are "activated", but which also add value to the end product. Needless to say, the sum of processes should add more value than they cost, otherwise you go out of business.

Let us say you have a café. The customers are willing to pay more for a cup of coffee on a café than at home, due to the services added.

You make and serve the coffee for them on a nice table provided by you. All processes which add value to the coffee.

When you run a business, naturally, you want to get the most out of your efforts, i.e. make your business as profitable as possible. In other words, you want to focus your ressources where they matter. But how can you tell, whether you improve your business by making better coffee, use a larger cup or put cloth on the table?

This is where Business Intelligence comes in.

In almost all modern business today, activities are registered one way or the other, due to the fact that a growing number of tools are computerized.

Your coffee machine makes more than coffee, it register the number and type of coffee you make during the day. Your cashier register the time you sell the coffee, the price you take, and to which table you sell it. Your surveillance camera does not only tape the activity in the café, but also counts the number - and time - of people arriving/leaving the café. Just to name a few.

All these activities are registered somewhere as data. The aim with BI is to collect these data and see, if you can find some synergies in your working processes, which can inspire you to make improvements, that will lower your cost and/or increase revenue. In other words, make your business (more) intelligent.

It may very well be,
- you say:"Hey, who needs Business Intelligence! Everybody knows the best time of day is the lunch-break. The café is full. Just take a look!". But your Customer count reveals the number of people arriving and leaving empty-handed increases, at the same time.
  In other words, The business may be running smoothly during lunchbreak, but it may make sense to hire extra people during lunch hour in order to satisfy the lack of capacity.
- You think:"Argh! I don't need some fancy tools to tell me that people sit in the back if there is room. Most of our time is spent serving people in the back!"
That may very well be, but the combination of the cashier and surveillance camera shows that a major part of small espressos are consumed at the bar by people on the move.
Maybe it is a good business case to move the espresso machine all the way out on the street for even easier access.

These examples are not fictitious, but results I have made for customers.

A very good salesperson who has spent a lot of time in his café, may have a gut feeling about these strategies and act accordingly. But the greatest power in BI is its potential in making a performance overview of vital activities and processes in the business, on which you can increase value or lower cost.

In short: No matter how big or small your business is, Business Intelligence helps you work smarter, not harder!

In the posts to come, I will go into detail with the various performance measures at hand and demonstrate how to analyse activities, so you can make excellent overviews of your business activities and manage them in an optimal way.

"Too much ends in smoke.”

― Toba Beta, My Ancestor Was an Ancient Astronaut

I have just attended a BI conference, where the major subject - again – was the concept "Big Data", i.e. the common name for unstructured  information, collected on the internet. Two days, where I have been sitting as a patient "disciple", listening to speakers from large corporations and consultant agencies, talking – again – about the “wonderful world of big data”.

The angle to the subject is almost always the same: information on facebook, twitter, instagram, blogs, newsletters, mails, affiliate traffic, etc. is so valuable for all business activity, that companies need to focus all analysis resources in collecting and analyzing Big Data in order to beat the competition and provide cutting-edge marketing!  “Better start today, than tomorrow! ”, seems to be the key strategy in order to avoid total market exit.

There I sit in the auditorium, being “all ears” everytime, due to the fact that I agree “Big Data” to be an invaluable amount of information regarding customer behavior, demand, opinions and thoughts. All flying around in cyberspace in an accumulating speed and scale.

Invaluable, due to two reasons:

• The amount of “participants”.  Hundreds of millions of “volunteers” of all ages, social classes,  nationalities and cultures all over the world. Needless to say, a survey on this scale and variety is practically impossible.
• The nature of the information. We are not talking about feedback based on predefined surveys or structured interviews with clearly defined focus groups. Procedures we normally use, when we collect information about customer behavior and mindset. No, what we have is a tremendous amount of unfiltered and unstructured thoughts and attitudes, knowledge sharing, “do’s and don’ts”, impulsive behavior, short term needs and long term dreams regarding almost everything. Due to the fact, that modern people have a need to practically write whatever they think on social medias and take pictures of whatever catches their attention, we are really talking about a "crystal ball" of feelings and mindsets to an extent, we won’t be able to collect elsewhere. Not even close.

So far so good. As professional information analyst, I have no difficulty in seeing the potential in “Big Data”.

However, every time I hear these speeches, I keep asking myself the question, which almost no speaker has an answer to: “How?”

Every specialist working with Business Intelligence knows, that it all comes down to one question: ”Does the end result give a true picture of the real world/business/market ?”.

If you are a small carpenter managing your own business, and the monthly finance report suddenly shows a turnover of £100 mill., you probably get the feeling, there “is something wrong” with the numbers. This is a natural reaction based on experience,  logic, and the fact you’re dealing with “numbers”, which are structured and easy to measure.

But “Big Data” is not only numbers, it’s EVERYTHING!! Numbers, text, pictures, symbols….the lot!!!.

You may be able to develop a programme, which is capable of recognizing one from the other, but how do you dechifer a statement, demand, written feeling or dream, all information based on cognitive(based on feeling) values rather than relative values as numbers ?
 
I mean, how do you tell a programme the difference in value of the word “Great!” in these two statements:

•“I took the best picture with this camera. Isn’t it GREAT!”
•“I only had this shitty camera on my holiday…..GREAT!”

When you read these two statements, you can tell the difference between sincerity and sarcasm. Right?

But how do you tell a programme to dechifer the cognitive values on all information on Facebook or Twitter and set it up in a way, which gives you a true picture of the overall attitude regarding a particular subject?

I have read about highly advanced holistic programmes developed in research laboratories, which are capable of dechifering cognitive based information. One thing is a controlled experiment in a closed lab, but to do the same on Facebook with millions of users…………..? We are talking "a giant step for mankind", here !!

Back in my seat in the conference room I raise my hand and ask my question again. This time to a Business Intelligence Director from a big financial institution in Denmark, who has just spent a hour explaining their “Big Data” visions: “ I have heard and understood your “Big Data” strategy, but how will you carry it out  in practice?”

The reply shows, quite well, the state I consider all the fuss about “Big Data” to be in: enthusiastic, but pointless – “We are not quite sure, but the sooner we start, the better!” 

Using another spreadsheet as datasource is one of the easiest dataloads to set up.

Why?

“What’s the point?” you might ask. Well, from a business point of view, it may have some advantages to keep the data source and data analysis apart. A few:

1) Security reasons. Situations where you need to add personal comments, -analysis and/or –calculations, which is for “your eyes only” or just to keep your own files, which others can’t edit.

2) Reduce workload. As mentioned in a prior post, the major advantage is the fact, that you only have to set up the analysis once, and then simply update the data ever after. No reason to explain the advantage in this solution.

3) Multiple data entry. In many cases you need to collect data from various people or colleagues (holidays, working hour registration, travel expenses, etc.) or systems (fx. logs of all sorts). In the cases where the number of people and data are small, a spreadsheet is a fine solution for this kind of data collection. With this approach, you have an “external” “data entry platform” (A) in which you can administer read/write access easily (through folder security), and a detached “data consolidation platform” (B), where you make your analysis.    

How – working example?

Situation: you are a leader of a sales team, who wants an easy overview of the mileage your team logs on an ongoing basis. Your goal is to get a picture of the number of miles each team member drives every week. 
Example of the mileage log:

Walk-through:

1) Open an empty spreadsheet.
2) Choose the "data"-ribbon and click on "from other sources" ("external data"-group)
3) Choose "Microsoft Query" 

4) Choose “Excel files*" (Note: be sure to mark “Use the Query Wizard to……”

5) browse your way to the spreadsheet (datasource) -> click "ok"

 
6) now you see all the sheets in the workbook (note: if you do not see any tables, click “options”-> mark “system tables”->click “ok”). Open the relevant datasheet (click on "+") and choose the columns needed.
In this example I will only be needing the columns:” Date”, “Person”, “Mileage”

7) click "next" through the guide -> click "ok"
8) in the popup "import data", click "pivotdiagram and pivottable"

9) now you setup your pivottable/chart for further analysis.
a. Add week or date in “axis fields”
b. “sum mileage” in values
c. “person” as filter
d. (layout is out of the scope of this post, but please ask me if you have any questions.)

And the final result:

10) finally you want to update your spreadsheet automatically everytime you open it:
Go to the ribbon "Data" -> click on "connection" (now you see the dataconnection you have created)and choose your dataconnection -> click on "properties" ->
choose the tab "use" and set a mark in "update data, when you open file" -> click "ok" -> click "close".
 

Now your pivottable and chart will update with the latest registrations everytime you open it.

Enjoy!

My posts in the category "Dataloads", may seem a bit more technical, than what you expect in this blog. You may think: "What on earth do I need this for ? I just paste my data in the spreadsheet, and work on them from there! That's the hard part, so let's get going!" 

However, I do consider setting up dataloads and dataconnections to be an important subject, due to the "ground rule", which we all try to work towards: "WORK SMARTER, NOT HARDER!".

Through my career, most Controllers, Business Analysts, Team Leaders, Process Managers, etc. I have seen using spreadsheets, make the same approach for data preparation: They cut the data from a datasource, paste it into a spreadsheet and then they begin to set the data up for analysis. The next time they need to update the data, they paste a new data set on top of the old one....and start the same datapreparation all over again. Lots of energy and time wasted. 

Why make the same work over and over again !?!

This approach can be improved significantly, by direct access to the relevant database. This "Direct-access" to the datasource have the advantage, you only need to make the datapreparation once. After that, you simply refresh the datamodel everytime new data are available and Voilá! It takes a splitsecond.  

It is true, that setting up a dataconnection to a database can be a tedious task for people who are not used to work with IT. Today, however, these tasks are, in many cases, much easier than you expect.

Therefore I will from time to time depart slightly from the primary scope of this blog, "advise business people in the use of business intelligence", and give some "tips´n tricks" and "walk-throughs" on how to set up dataconnections in Excel, in order to help you to "work smarter, not harder!"