Apr 30, 2013

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;

Apr 29, 2013

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?

Apr 26, 2013

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.

Apr 19, 2013

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.

Apr 18, 2013

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.

Apr 8, 2013

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.

Apr 3, 2013

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.

Apr 2, 2013

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.