Tuesday, April 26, 2016

A Simple Illustration Of The Powerful Regression Tool In Excel

Regression is a statistical way of finding the relationship between a set of variables (inputs) and a particular variable you want to predict/compute.

Maybe for instance you are a regional sales manager for one of the telecommunications company. It has been extremely hard to grow subscribers and despite the huge spend on advertising, the subscribers count has been flat. So you decide to focus more on growing customer share (share of wallet) rather than kill yourself over the market share that isn't improving.

You got the business intelligence unit to give you historic data of subscribers -- both the ones still on your network and those who have left (churned). So yesterday, you got the data. You looked through it and noticed that there was a pattern everyone who left (churned) exhibited. And you also noticed that there were a set of subscribers who generated a high average revenue per user (ARPU) for the company.

What do you do with the insights?

Simple. With regression you will have a way of identifying customers who are most likely going to churn. And you won't stop there, you will also be able to identify customers with the same profile as those generating high ARPU. And you know what that will translate to business-wise? 

You can do targeted promotional campaign to those guys who are about to churn. It's an excellent customer retention strategy, focusing on the people who aren't happy and using your learning from them to ensure others won't become unhappy too. 

Then you also do targeted campaigns to people spending less than what their profiles predict they should spend. Another easy way of growing the revenue and customer share of wallet. Effective use of resources on areas there is a huge potential ROI.

So how does Regression work?

First you need to enable it. Go to File, Options, Add-Ins, Excel Addins and enable Analysis ToolPak.

Then under Data Menu, you can now access it.

So I have set up a simple example case.

I don't know the relationship between the area of a circle and its radius. I want Regression to figure out for me what that relationship is, so I can be able to predict/compute the area of any circle I have its radius.

See the result below.

The most important parts of a regression analysis results are the ones I highlighted in yellow. The Adjusted R Square indicates how fitting the model is. The coefficients are used to build the relationship equation. P-value should be less than 0.05. 

Hopefully, it now makes sense.

Tuesday, April 19, 2016

Excel's Most Multitasking Formula -- SUMPRODUCT

Sumproduct is one of the general purpose Excel formulas I learned last. I was deceived by its simple nature to assume that I had not much special use for it.

Today, I'll be showing you the amazing use of SUMPRODUCT for common issues you'll encounter at work (working with business data).

So let's say you manage a sales team and they sell two products. Every month, you get the report of the sales from each person which you combine to have a consolidated sales report. Below is a snapshot of the sample I created.

As part of your team building strategy and sales improving tactic, you do an in-depth monthly performance report of each sales person per product with recommendations of what they could have done better. And to make this report, you constantly find yourself picking the sales for a month for a product by each salesperson. Like a probe into each person's sales figures. So what if you want something that will help you automate part of that probing. Showing you total sales by a particular sales person per specified product and specified month.

A formula that you can supply the person, the product and the month, and it will pick for you the total sales figure. Something like VLOOKUP except that VLOOKUP doesn't accept multiple lookup criteria and sum. So what formula would you use?

The answer: SUMPRODUCT

Don't be put off by the length or complexity of the formula. It is easy to grasp. What I more importantly want you to pay attention to is that whenever you have a table and want to pick a value (numeric value and a summation) based on multiple criteria that VLOOKUP can't handle, then turn to SUMPRODUCT.

I want you first to expand your mind and know what it can do plus where you'll need to use it. It has a more simple use which I have skipped because you can always get that one done without using SUMPRODUCT. But this example I am sharing with you is one of the situations that you'll have no other/easier alternative.

So say you've worked yourself into a situation that you need SUMPRODUCT like I have explained (multiple criteria to consider). How do you now use SUMPRODUCT? How does it really work. Then please read one of the best and excellently illustrated tutorials on SUMPRODUCT here: https://www.sumproduct.com/thought/multiple-criteria.html 

You can sign up for our online training or in-class training to learn more about this and other useful tools in Excel

Tuesday, April 12, 2016

Multi-level Sorting In Excel

Excel can let you sort your table in multiple levels. It's a little difficult to explain without showing you. So I have made a sample data we can all relate with.

It is the result of our last year election (fictitious data as usual). 

The results are arranged by State and Wards.

So what if you want to sort it such that the states will show alphabetically and the wards in each state will also show alphabetically. For example, Abia state will show first, and all the wards under it arranged from A to Z before we move to Adamawa state.

To achieve this, we'll need to do multi-level sorting.

I'll select the entire table and click on Sort & Filter, then Custom Sort.

The Sort dialog box comes up. I add one more level by clicking "Add Level".

I, then, specify that I want to sort by State first and then Wards.

I click on OK.

And below is the final result.

Notice that Abia state comes before Adamawa and all the wards in Abia state are arranged alphabetically.

And that's how you do multi-level sorting in Excel.

Tuesday, April 5, 2016

Six New Functions In Excel and One New Chart Type

Good news!
Microsoft has added 6 new functions to Microsoft Excel and they are not functions that you might never find use for but functions that people have been asking for. In fact, I am already seeing the numerous ways they are going to make my Excel-ling life better.

Two complaints I hear people make a lot about CONCATENATE (the Excel function that lets you join the values/texts in different cells) are that why is the name that long? and can't we select a range?

Now Microsoft has nifty fixed those two issues in one cool function: CONCAT

CONCAT is short to write and allows you join entries selected as a range.

This takes CONCAT to a new level. So what if you want to put a comma and a space between the names as you are joining them? That is what TEXTJOIN enables you to do.

The formula that gets most people confused the most in Excel is nested IF. People find it hard to process the numerous brackets and IF statements that characterize a nested IF function. Now there's some relief. Excel has introduced IFS to help combat the strain of untangling brackets.

If you have a report and need to grade the performance with some conditions like we do for school marks to grades, then IFS is your new friend.

So what if you want to pass a comment on the grade performance? You want to say grade A means Excellent, grade B means Very Good, ... Well, that is what SWITCH allows you to do. It was borrowed from the programming community.

And there is also MAXIFS to let you do want you've been doing with SUMIFS and COUNTIFS, just this time you are picking the highest value that meets your criteria.

And this is the sixth and last: MINIFS. You've guessed right! It does similar stuff as MAXIFS but returns the minimum value that meets your criteria.

Black Theme
Office (Excel inclusive) now has a black theme.

And there's also added to the chart family, a funnel chart.