Predictive Analytics and Prescriptive Analytics

Few days ago I read a post by Javier Lafuente Garcia clarifying the differences between predictive and prescriptive analytics. The author lays clear and concise definitions as well as points some applications of each type. I like the way he does that and I would like to share it.

Developing Big Data Analytics Strategy

The torrent of big data and analytics articles rarely touches on how to get to the point where all the nice charts and discoveries magically appear out of the analysts hands (keyboards actually). Getting an organization to the point of producing meaningful results requires a mountain of focused efforts. I have recently came across of an article that concisely and comprehensively covers the steps leading making (big) data analytics possible and meaningful.

Better Human Understanding, Not Big Data, Is the Future of Business

Recently I came across a nice article that articulates some of the issues with the data analytics in organizations and too much relying on it. In the height of the big data hype it is a sane reminder of the limitations of data as a business driver. No need retelling you the story - read it yourself here.

Markets' Natural Levels And Natural Drivers

Recent hype of all sorts of data analysis, big data and expanding application of complex math make more and more media and people refer to macroeconomic indicators when explaining what is going on. This is OK for overviews and inconsequential analysis to fill up air time and gap between drinks. When it comes to analysis that would have impact on business and government decision these factors have to be  accompanied with understanding of real drivers behind processes. Rarely I hear this type though. We should not forget that real-world processes are driven by real people and companies making decision based on their incentives, beliefs, legislation, etc. Maybe it is the scientist in me speaking but we should always search for the reasons  for one thing or another to happen. That leads to the idea of the "natural level" of each market or any other field of interest.

Faster Large Data Spredsheets: Alternative to the IF() Function

Spreadsheets with large data and large number of calculations are  part of the business landscape no matter we like it or not. These spreadsheets require greater focus on the design and the way calculations are performed. One of the culprits for poor performance in this type of spreadsheets is the overuse of the IF() function.This is probably the first of Excel function we learn and one we use the most, often neglecting its negative impact on performance. Recently I have done a project that required a calculation intensive spreadsheet model over relatively large data and good portion of these calculations were conditional. The effort for squeezing out the best performance led me to rethinking the way I handle the conditional formulas and I found an alternative of the IF() function that reaped great benefits. 

A New Book on Data Science: The Field Guide to Data Science

A great new book on data science is out. The authors are  the experts of Booz Allen Hamilton  - a name that does need an introduction.  The book covers in detail the definition and the basics of data science, provides insight on many topics and reveals some processes employed in the company. I like that it is very practical through the examples and guidelines for solving specific problems of many sorts a data scientist faces. It really is a field guide and helpful companion in contrast to some academical writing. And the best is that is free. Read more and download it here.

Friday Funny: Screen Size Matters

The life the modern person is a difficult one. From the suffer of choosing the where to eat to mind-numbing complexity of selecting the best AC temperature to the agony of the internet connection that loads Facebook for about 2 sec. I could add more and more but I would focus on just one. These days everyone is a big boss or phenomenal manager or colossal visionary according at least to job titles and Linkedin profiles (see more on that). It is not easy in the office environment either - everyone is sharply dressed and has an iPhone. It is very hard to navigate who is who, doing what and whom to show more respect. Here I offer my 5 cents in treating this terrible ailment.

Use Simulation to Make an Impact

The Data Warehouse Institute is a great  source for BI and data warehouse as well as analytics. The website has great articles, whitepapers, offers certification and education and organizes one of the best events in these areas. I have been following it for some time and I do recommend it for any professional.

Recently the website published a great article about the use of simulation in an organization - Use Simulation to Make an Impact. It covers the applications, recommends team structure and environment for proper and beneficial simulation. I like the clear structure and extensive coverage despite the small size. Enjoy!

The Almighty Linear Regression

If there is a stat for the most pervasive, used and abused analytical method I bet the linear regression would come at the top. No surprise in that - this method has same features as AK47 - simple, easy to use, reliable in most of its application, low entry level knowledge. It may not be the proper tool for some applications but still does a good job. Similarly to a weapon, it should be used with caution. Linear regression may not have the lethality of an AK47 but could cause significant damage if not used wisely.

Why Does US Have Such Stratospheric Healthcare Costs?

The debates related to Obama Care together with a project related to healthcare market I took part in revealed that healthcare costs in US are extremely high compared to other developed countries. Among the blizzard of who-said-what articles and media coverage on the topic perhaps many asked themselves the question of why are the costs so high on the first place. As soon I had a couple of hours I made a quick research and here is what I found.

There is More Than Just Knowing the Formulas

Sometimes I get "You did that because you know the Excel formulas". I beg to disagree. Excel formulas knowledge alone does not make a good solution in the same way as the guitar does not make Clapton or the painting techniques alone did not make Van Gogh. There is something more.

Signs You Need to Abandon An Excel Solution

Love it or hate it but MS Excel is an organic part of the business landscape. Tons of spreadsheets cross the space from one user to another to deliver data, information, dashboards and everything else that might come to mind. Excel is convenient, available, affordable and versatile. However, mindlessly sticking to it does not always pays back and hampers operations, information flow and decision making not to mention the timeliness and accuracy of data.

Better Dashboards with the Linked Picture Feature

One of the challenges with dashboards and reporting in Excel is the formatting of its components. Combining charts, tables and text of various size requires fine tuning of row heights, column widths, fonts and alignment of multiple objects. Often this is difficult and painstaking process that need to be redone every time a new requirement comes in and a piece of the dashboard needs to be added, removed or reformatted. A cure for saving a good portion of it comes with the use of linked picture feature in Excel.

Book Review: Predictive Analytics: Microsoft Excel by Conrad Carlberg

The hype around analytics and its applications somehow leads to the impression that it is aided by some very serious applications and lots of programming. However, it should not come as a surprise that major portion of it is done in MS Excel. The demand of advanced users familiar with analytical skills also seem to grow. There is a vast number of resources for the practitioners and but the good ones are few. Recently I came across a book that is a fine example of this type - Predictive Analytics: Microsoft Excel by Conrad Carlberg.

Excel on the Edge of Reason

Every now and then clients throws requests that challenge the usual way we do things. One of these challenges that come not that rarely is for Excel workbook working as a database. A recent request included a spreadsheet with over a thousand rows and about 250 columns with extensive set of extracts and charts based on it. The picture was completed with the perspective of growth in data size and analytics on it. The task challenged the usual methods for working with spreadsheet data and imposed some deeper and more careful selection of techniques. I would like to share some experience with such challenges.

Book Review: The Signal and the Noise by Nate Silver

Predictions are hard, especially for the future. However, there are people that make their living out of it. Some are good in it, others are successful and few are successful and famous. One of the latter kind is Nate Silver - a blogger and columnist in New York Times. His star rose with the accuracy of his predictions of the latest presidential vote in US and many of us have been willing to know the secret of his success. The thirst has been quenched by his book The Signal and the Noise.

Friday Funny: The End of Annoying Motorists

Motoring is a great human invention with an unprecedented contribution to our civilization. However, some part of the civilization indulging on it is not that civil. Еxamples are ample and include the motorcycle breaking the silence of the night, waking up the kids and turning on all the car alarms or the car stereo volume turned up so high that makes a party somewhere on the Moon not suitable place for a quite chat or the unpredictable driver disobeying the regulations of law, common sense and clinical sanity. Have you ever wondered how we could solve these problems and make our street safer and quieter as well as daily commute bit more pleasant?Violence is often the answer but I have a better idea - I have designed a simple yet very effective solution.

Using Big Data in Football (Soccer)

The Lewis's book Moneyball revealed clever application of data-driven analytic in baseball and initiated the so called "Moneyball"-era in this sport. Currently, wide range of teams employ similar methods together with the traditional ones. I have been wondering if similar methods are applied in football and why not much is known about it if they are. I know many performance metrics are tracked by the clubs but I wondered if any data-driven analytics is employed.

What If Nazi Germany Had Excel?

Map of Operation Bagration

Yesterday I read an article about how data analysis solution would significantly reduce the cost of healthcare in US. After some illustration of the rising healthcare costs, a solution was proposed with introduction of fraud detection methods and tools. Fraud does impact the overall costs but I am sure it is just a part of the problem. It was obviously a paid article to promote a product line as the savior of our world. However, it provoked me thinking once again about the place of analytics in the decision-making and management processes. Well balanced views are rare as everyone promotes their own subject as crucial success factor - analysts are no different and the current hype with analytics and Big Data multiplies (by a factor greater than one) their advertising. To make things more fun, this line of thought could answer the geeky question of would Nazi Germany be more successful if they had a powerful analytic tool at their hands?

From Data Modeler to Data Scientist

I came across an interesting parallel between the data modeler and the data scientist. I like the comparison of both positions on some key aspects. However, I find such distinction between these two roles not that applicable for an analytical company or department  as most projects involve methods and techniques attributable to both positions. The exception is probably the IT organizations dealing with database design and not so much with data analysis and. It is a good read. Check it out here.

The Dark Side of Data Abundance

If there is something that defines our digital era it is the abundance of data. It has been created out of the cheap computing power and storage, Internet ubiquity and ease of sharing, open source and open data movements as well as the new requirements from the the governments. It is all great of course but many people and companies join the Dark side of this phenomenon. I hope it does not come as a surprise that "more data" phenomenon has a dark side - almost everything does. I can judge just a narrow aspect of it from the perspective of data analysis and decision making. What I see is sometimes scary and sometimes funny.

To Macro Or Not To Macro, That Is The Question!

A genius once wrote a famous monologue and 400 years later it still stirs up emotions and thoughts. Not surprisingly, Excel macros do not have such a dramatic appeal but using or not macros in a workbook is a question worth asking. Including one in the workbook is decided on the fly without much of a consideration. Quite often macro is the gin coming out of a programmer's hands that could magically solve any problem. As a favorite TV presenter would exclaim "How hard could it be?". Macros are brilliant helping hand but should be applied with consideration.

Book Review: Engineers of Victory by Paul Kennedy

A good book makes vacation time even better. My choice this year was particularly good as I took a copy of The Engineers of Victory: The Problem Solvers who Turned the Tide in the Second World War by Paul Kennedy with me. it combined two of my favorite topics of history and analytics in a very exciting way. It explains how the tide of WW2 has been turned in Allied Forces favor in the period 1942-1944.

Friday Laughs: LinkedIn

LinkedIn is a nice networking platform for professionals. However, some things are wrong with it. I like to share some thoughts on that.

The Neglected Basics

We often start developing models forgetting or neglecting the questions about the underlying assumptions for these models or take them for granted. Unfortunately it is not always the case and pay with time and efforts for that. 

Where Should We Focus The Analytical Efforts?

It is the least to say that analytics today is more popular than ever before. It is cheap, affordable and organizations push towards greater application of its methods. It is all good and pays the mortgage of quite a few of us but my mind lately has been occupied with the focus and scale of the analytical efforts. It seems on numerous occasions the mighty power of modern analytical tools and methods have been applied on wrong problem. People have been trying to solve the wrong problems for ages. Analyzing a problem deeper and deeper, applying tools and methods that make the buzz words today and being in the trend could lead us astray. We could focus on the tree and forget it is part of a forest.

Some Issues In Modeling Life-Cycle Component Of Markets

Some markets have strong component coming from products that need to be replaced because of burn-outs, breaks , wearing off or changes in properties that render them dangerous or useless. Automotive market is a good example for such market - tires wear off, the oil, spark-plugs and other components loos properties and so on. Another example for this type of market is the lighting market - the lamps burn out and need to be replaced. Modeling this type of market is eased because of the "mechanical" relations between entities. However the challenges the modeler faces are not negligible and could seriously damage the model quality. 

Creative Chaos In Excel? I Got A Better Idea

Not me, just a puzzled guy

 There is such thing as "creative chaos" and it may work for writers, artists and inventors. However, I know for sure it does not work for organizing calculations and models in Excel. It is really frustrating to open an Excel file and it is a mess of fonts, borders, formulas, values, sheet names, lines without headers and other "design solutions" that make it look as if some ancient gods had a pagan feast and suddenly left. Aesthetics apart, such files are very difficult to navigate, maintain and even more difficult to update or change. I realize that most of it is not a result of a directed thought but a chaotic thoughts and randomly coming requirements for things to calculate (ASAP, as usual). There are few simple guidelines to follow in order to produce neat and tidy Excel files.They do not take time at all and relate more to discipline than some specific Excel or model-building knowledge. I could not cover everything in great details here but take a look to get some good ideas and have a base for further thoughts.

What You Need To Know About Business Intelligence Applications

There is lot of talk about Business Intelligence (BI) lately. Explicitly or not, it is embedded in the advances in collecting and analyzing data. But as it usually goes, there are few companies that have adopted the BI tools and school of though while many others still lag behind. BI has been around for a while and it makes its way to wider and wide audiences. Still though, there are lot of things that the business and other organizations do not know much about it. I have tried to put together some remarks that cover general gaps and misconceptions. These come from my experience with companies from the Balkans but I guess it is applicable to other regions as well.

Learn a Shortcut Save An Hour

I am serious - learn few Excel shortcuts and save tons of time. I cannot stop being surprised from heavy Excel users that keep reaching for the mouse for every simple tasks performed hundreds of times per week. With few keyboard tricks in your sleeve you would not only save time but also make it easy for your wrist and you would look like a pro in eyes of the uninitiated as a plus. There are many websites you could check for a full list of shortcuts but please find bellow some for a start.

Highlights On Model Types And Their Application

There are two types of models - statistical and I call "mechanical" ones. Statistical models step on data sets to extract properties, interconnections, current and future behavior. An example for that are models for customer categorization for the purposes of the financial industry, stock pricing and monthly sales. The the "mechanical model" embeds predetermined laws of nature or society, cause-and-effect dependencies, legislation and any other "hard" causative connections between entities. Examples for this sort of models include nuclear reactor model of the optimal working parameters, safety measures, energy output, etc. The general question what type model to build usually is not explicitly asked as each situation pushes toward one or another. However, a general discussion about applicability is welcome as it could give a guideline about models quality and efficacy.

Tip of the Day: A not so-well known cause for Excel files size inflation

Recently I had to work on a file provided by a client. The file was more than 70MB in size. Reducing the size was not part of the customer's request but my internal optimizer kicked in and after some investigation I found unlikely cause for this horrendous size.

Fancy Kung-fu Kicks and Getting the Job Done

Eyes closed, feet high off the ground and off the target, hands uselessly spread

You have all seen these movies where athletic guys make funny sharp noises, kick high in the sky and throw punches like machine guns. For my great delight some of them even fly from a tree to another. But this is not the movies only. Martial arts have been devalued to a simple aerobic exercise despite the tons of old Chinese, Korean and Japan proverbs written over the gym. One of the results is there too many fancy kicks, beautiful positions, tricks and very little tactics and blows that actually do the job and finish the combat. It makes me laugh to watch hard-trained guys a fight that is a display of brilliant technique and no result. The fights between real masters and professionals are rather short (don be confused by the movies). Methods and approach applied in analytics very often remind me about this lack of ability to address the real issue and hit the core of the problem.

The End of Theory: The Data Deluge Makes the Scientific Method Obsolete

Recently I came across a article in Wired with very interesting point of view about science and application of big data and analytics in it. The author suggests that the theories we build cannot be tested any more that is making further development of science impossible and new approach has to be applied among other things and I do recommend it for all of you interested in science, statistics and modelling.

Master Class: Dynamically Identify The Column And The Row For A Value In A Matrix

Need to dynamically find the position of a value in a matrix? I have came across this problem recently for the purposes of identification of some values in a huge market performance matrix. Please check my solution of this problem.

How Different is Big Data Than Just Data

One of the latest buzz words is "big data". Success stories about it are abundant and many people suddenly became big data experts and "data scientists". But where is this big data and how different it is from the data we know?

Are We Forgetting Something When Estimating Model Accuracy?

Expectations for model accuracy are usually high despite its  purpose. We get in the trap of thinking that the availability of huge data, powerful computing and advanced math methods would result in very accurate results about virtually everything. Hollywood also has its role. But what is the reality? The accuracy of a model is limited by the nature of the entity that is limited, methods employed and the accuracy of the input data. Very often we fail to realize the full extent of influence of the last one and even more so in models and calculations with many calculation steps.

You probably shouldn’t use a spreadsheet for important work? Really?!

A friend of mine sent me a link to a blog post where a professor in a reputable university makes the case not to use Excel for important work. He points out three major drawbacks of Excel:
- Spreadsheets do not support testing as it does allow some automated tests like unit testing;
- Spreadsheets make code reviews impractical as you need to go cell by cell to check the calculations;
- Spreadsheet encourage redundancies as it is employs copy and paste that lead to difficulties in updating;

What model is a good-enough model?

There is so much talk about models and it is seems they could do anything and give precise answers to many questions. This is almost true and many organizations build and models for their operations or planning. However, we often fail to realize that a good accuracy is matter of time, efforts and ultimately - money. As with everything else, the better and the more expensive the model is the more money, analysts and time is required. We usually face limited resources and we need to draw a line somewhere. One of the overlooked points in the process of planning for a model development is the level of accuracy we expect from it. So what model is a good enough?

BBC confirms Excel rules the world

Well, not entirely but a good portion of it at least. BBC posted a nice article about the powers of Excel and how an error there could make a big dent. This is another call for a better quality in spreadsheets.

The author makes a good point that the days of the Excel's rule are maybe coming to an end with the advances of the Big Data. I do not believe that it will disappear - the Big Data applications are far from the needs of small businesses and organizations. Excel is here to stay. So we'd better keep learning how to use it properly.

Automate the Update Of Big PPTs - Say NO to Copy/Paste

Periodic update of huge PowerPoint presentations is still part of deliveries of an analytical professional. Despite the advances in web based dashboards and reporting systems some customers still prefer to have their outputs nicely presented in a deck. The transfer of data from Excel to PowerPoint slides happens manually more often than we would like to admit. This is OK of course for small sized decks. MS Office offers lots of flexible options to link a slide to Excel data source. You could link or paste data or a chart with various options for formatting. They work pretty well and I welcome you to try them. However, the option to use with decks with large number of slides has to be chosen carefully. I consider huge a deck with more than 50 linked charts or data sources in it.

Tip: A Better Way For Dynamic Look-ups

Looking up a value in a range is probably the most common task in Excel after SUM and the basic operations. We usually want the look-up function we write to be dynamic. Look at the table on the picture below

How Reliable Is The Expert Evaluation Of Your Forecast?

A human evaluation is an essential part of the life of a forecasting project. This happens at least at the presentation of the results. In this evaluation kick in factors that are hard or impossible to measure or include in a math or stat model. The list of these is long - it could be political situation, attitude toward a brand name, expected changes of any kind, a conversation on a party, gut feeling, etc. The evaluation is also heavily influenced by biases and an the agenda of the reviewer. Dealing with these is a matter of countless research papers. A modeling and forecasting practitioner is regularly involved in discussions and opinions on the outputs and the question "How much can I trust this evaluation?"is a constant companion. This is a vast topic but please allow me to put my 10 cents in it.

Hm, A Forecasting Model. Now What?

Models are all around - model cars, model of thinking, fashion models and predictive models. We know how to use or enjoy most of them. However, forecasting models and predictions are something different and I feel there is a small confusion how to deal with them and their results. Some expect a model to tell them exactly what will be. Few of those are right as there are limited matters where a good prediction is achievable. Others totally disregard the forecasts. And rightfully due to the abundance of wrongfully placed and wrongfully communicated forecasting projects. The group of managers that use forecasting results just as something they have to have included in their reports without any consequences for future is large as well.

Generate All Combinations Of Reference Data

Data preparation and analyses in Excel often require a block with formulas based on combinations of reference data items. Populating large ranges with a formula is not a problem and the pesky problems is the generation of reference data combinations. For example, you need all the combination of region, segment and technology to generate the a sales report. This is not a problem in the case of few items in each reference data type - a quick manual copy and paste does the job. However, the case of  are tens of items in each list is time consuming and unpleasant to approach manually. You could avoid that with simple logic and formulas. Read further to see what I propose you can do.

The Achilles Heel of Analytics

Analytics has an overwhelming power and strength. Despite that it has a deadly vulnerability - very much like the Death Star. It has happened to us all - a seemingly very good, accurate and elaborate model with pieces of true gold in it has gone to the oblivion and has never been appreciated in the organization. It took me couple of years to realize the reasons for that.

The Reference Data - A Perfect Mess No More

Reference data is list of country names, department names, equipment type, products, customers, etc. Setting it right is a key for a good and flexible report or analysis. For example you would not like to have an analysis of sales per country with USA appearing there as USA and as United States. The problem is not acute when all the data comes from one source. However, do not underestimate the possibility for duplicate names and do the necessary to check them - Exlce has really nice tool for that. The problem is much more acute when analysis pulls data from few data sources. I would like to share few simple steps to deal with that and save time.

Do You Test Your Models Well Enough?

Numeric models and complex calculations have the unsurprising property of being quite important most of the time. That puts a lot of pressure on the developer for delivering good results. The question is how do you know the model works properly? Considering the expense and the high impact it is surprising to me that the testing phase is often being skipped or not enough efforts are put  assuring it adheres to high standards. Neglecting this phase  often results in unsatisfied and angry customers, extra time spent on the project after delivery, or bad and costly decisions based on a bad model.

An Exampel of A Successful Automation

The focus of my previous post previous post about manual processes and their automation was on the applications included in MS Office. However, the key points are applicable to processes involving all sort of software packages.

As promised, I will provide some examples for a successful automation of a manual process. Here's the first.

Automation of MS Office Tasks? What is This?

I see it over and over again - a team of expensive analysts spend big chunk of their time to process data - load, transform, copying and pasting and exporting. Very little time is left for actual analysis and getting the valuable insights. It is a total waste of time, resources and money. Not to mention the dissipating enthusiasm and energy of this highly qualified staff. I have always been amazed how otherwise clever people spend hours and hours of copying and pasting data from one source to another or from an Excel sheet to a PowerPoint presentation. Add to that the very high probability of human error and you  will have a grim picture.

In Search Of The Formula

No doubt, one of the most appealing features in Excel is the formulas. Clever usage of it could achieve excellent results in performing complex calculations, processing data, extracting and displaying information  and much more. Тhe majority of Excel users have not stepped beyond some simple operations. Мost people approach formulas as a daunting and complex task. Users get fixated on searching the formula and keep trying to find it while the real power to solve the problem with their own logical thinking. Here's my two cents to help you think of formulas as easy and fun.

Handling Formula Errors in Excel

Confucius says: there is more than IFERROR in error handling

Admit it or not, errors in formulas are a regular companion for everyone who uses formulas that are a bit more complicated than the simple subtraction and addition. There are two general types of error we make in spreadsheets - logical and formula errors. Producing error-free sheets and models is a huge topic and I will be covering it in this blog. Today I would like to focus on one the most popular ways to handle formula errors.

How To Keep Data Sources in Your Spreadsheet Under Control?

Data comes to the spreadsheet in a number of ways. It could be a short list of quarterly sales per department or a long list of customers and their balances copied from another workbook. It could be imported from a website or fed from a database query. If you are lucky, your spreadsheet will require one-off data loading. Unfortunately in most cases we need to review and update our spreadsheets. We then have to go back to the data and remember where and when it came from as well as many other details depending on the specific data source - it could be a query, report name, web-address and date extracted. The problem is even harder when you work with many spreadsheets serving different customers and purposes. This is why I think that proper management of data sources is an important step towards better models and better management of spreadsheets. Here’s how I do that.

How to Make Analytics a Success

The word "analytics" is everywhere. Its methods now appear quite often in business talk, lots of articles distribute the analytical values, there are best-selling books that describe the analytical framework and even some movies with major Hollywood stars. I believe we are witnessing levels of work occupations with some "analyst" in its titles never known before. Big data, statistics and modeling are the buzz words. However, application of analytic methods is very often far from the most-productive way to do that. There are lots of ways preventing analytics not to deliver true value..
I have spent lots of thinking on the keys for a successful application of analytics and what makes an analytical project to achieve its goals. Recently I have came across an article that illustrates some key points that I would like to share with you. The article is Big Data in the Big Apple published in Slate.  Take few minute to read it - it is a good read and good story (as if you could expect something else from Slate).

What is at the Bottom of the Horse-meat Scandal

The horse-meat scandal has been going on for some time now. There is lots of investigation, people talk and headlines. All that time there has been a question that was bugging me. So, where all that meat came from? Large herds are not roaming free the plains of Europe nor there are traditions in eating horse meats among most of the population there. Also, horse meat is relatively expensive and probably would not be the first choice if someone decide to cheat and reduce the amount of beef in a meal. So, it looks weird to me.