Introduction to Goal Seek & Solver capabilities in Excel:

Standard

What-if Analysis is a pretty common analysis done by decision makers. Often, they would just create simple excel tables and adjust their variables manually until they get an answer that works. But instead of doing it manually there are features available in excel that will make your life much easier and analysis much more accurate. So, the goal of this blog post is to introduce you to the Goal Seek and Solver feature to help you do what-if analysis in Excel.

#1. Goal Seek:

Let’s say you are a CEO of an e-commerce startup and wondering about what factors you need to focus on to increase revenue. Here’s what the data (*assume per month) looks like when you start out:

excel-goal-seek-1And you want to increase the Revenue to $150K from $125K. The three levers you can pull are website visitors, conversion and revenue per customer.

Now you could manually tweak the values for this variables till you get to $150K but as I promised earlier, there’s a better way!

Let’s start with Goal Seek.

You need to set two variables for Goal Seek.

a. Your goal — which in this case is 150K

b. The variable that needs to be changed to achieve that goal — note that you can specify just one variable to do so. So you need to choose out of the three above what you would like to focus on. Let’s say you want to focus on conversion rate.

So once you have these two things — from the Data Tab in Excel, Go To What-if Analysis, Goal Seek:

excel-goal-seek-2Now, specify the values. For this example, we want to figure out what should be the new conversion rate so that our revenue will be $150K. So here’s an example of how that would look on Goal-seek:

excel-goal-seek-3After entering the values, you will see the status — you can click “OK” to keep the solution and cancel to go back to what you had:

excel-goal-seek-4Perfect! So you need to increase the conversion rate from 1.25% to 1.5% to get to the goal that you had set!

#2: Solver add-in

So, you worked on improving the conversion rate for next month or two and you & your team found out that it’s getting really hard to increase it above 1.35% — And also you found that with the less effort you can move the needle on other variables (website visitors & revenue per customer). Now Goal Seek allows you just set one variable so if you more variables than it doesn’t serve the purpose that well! That is where Solver add-in helps.

Think of Solver as advanced Goal seek where you can set more than one cell that can change. You can also set constraints on what the values could be for all the variables that can change.

Now, for our scenario, the conversion rate is at 1.35% but you want to see the possible changes that you can make for website visitors and revenue per customer to reach $150K.

You also know that you can’t above 1,100,000 Website visitors per month and also need to have less than $11 as revenue per customer.

You will need to enable the Solver add-in in Excel and once you do that you will see that in the Data Tab.

Once you have it, open it and fill up the information needed in the dialog box:

a,. Set objective to Total Revenue with value of 150000

b. By changing cells to: Website Visitors and Revenue per Customer

c. Constraints. Website Visitors <= 1,100,000 and Revenue Per Customer < $11

solver-excel-1After that click on Solve.

if it found a solution, it would show you that on Excel and also give you additional options to whether you want to keep the solver solution or restore it to original values:

For our scenario, it suggesting that with website visitors to 1,010,101 and revenue per customer to $11, we should hit our goal.

solver-excel-2Click on OK when you’re done.

Conclusion:

In this post, we saw how you can use Goal Seek and Solver add-in using an e-commerce scenario but you these techniques can be applied to wide variety of data analysis problems that can be solved using “what-if” techniques.

Hope this was helpful and I would love to hear from you about how will you use this in your work? Or if you use it already then what do you use it for?

As a data analyst for the CEO in an e-commerce company, what kind of reports are expected of me?

Standard

Someone asked this on Quora and here’s my reply:

As a data analyst, you should work with the CEO (or other decision makers) on a quarterly (or more frequent if possible) and learn about #1 Strategic objectives and initiatives — #2 after that, you should work together and figure out how analytics could help these initiatives.

So why is learning about strategic initiatives from the executives important?

  1. Because analytics could be applied to lot of problems but you and your team might just have limited bandwidth.
  2. Also, executives want to stay focused on what’s important now and so if your priorities align then you are much likely to succeed in the role.

Let’s take an example:

Scenario 1: As a data analyst, you create bunch of reports from let’s say Google Analytics and throw them at the CEO! It has everything like visitor stats, acquisition stats, retention stats, behavior stats, conversion stats among others! Now by doing so, executives might get what they asked for but then they will still have to go through the reports and map it back to their strategic initiatives and figure out the recommendations on their own. Also, executives might not have the time to do this and may miss critical insights.

Scenario 2: You know that the one of the strategic initiate for the quarter is to improve the conversion rate from landing pages to order-complete page from 1.25% to 1.40% — so your analysis that you send to the executive would not only be focused on just that but also include “recommendations” — like it seems that there is a significant drop-off after customers learn about shipping cost. Then the executive could use those recommendations to drive actions. There’s also another benefit: Any ad-hoc data request that doesn’t align with the strategic objectives can be postponed (or de-prioritized) and let’s you focus on what’s most important for the company.

I prefer scenario #2. And try to create this culture wherever I am working. Executives should be open to sharing strategic initiatives at high-level with everyone in the company and help align everyone’s priorities.

Note: This doesn’t mean that you don’t create reports, you still do that for broader consumption — especially the Key Performance indicators that are key for success but you should look at automating most of that and focus on data analysis and find recommendations that the executives could take some action on.

VIEW QUESTION ON QUORA

How do I pursue career in data warehousing?

Standard

Someone asked this on quora, and here’s my reply:

In the data world there are two broad sets of jobs available:

  1. Engineering-oriented: Date engineers, Data Warehousing specialists, Big Data engineer, Business Intelligence engineer— all of these roles are focused on building that data pipeline using code/tools to get the data in some centralized location
  2. Business-oriented: Data Analyst, Data scientist — all of these roles involve using data (from those centralized sources) and helping business leaders make better decisions. *

*smaller companies (or startups) tend to have roles where small teams(or just one person) do it all so the distinction is not that apparent.

So, it seems like you are interested in engineering-oriented roles — the role that focused on building data pipelines. Since you are starting out, I would suggest that you broaden the scope to learn about other tools as well. While data warehousing is still relevant and will be in some form or another for next few years, Industry (especially tech companies) have been slowly moving towards Big Data technologies and you need to be able to adapt to these changes. So learn about data warehousing, may be get a job/internship as a ETL/BI engineer but keep an eye out on other data engineering related tools like Hadoop ecosystem, spark, python, etc.

VIEW QUESTION ON QUORA

Book Giveaway: Head First Data Analysis — Ends 07/22/2016

Standard

<< THIS GIVEAWAY IS CLOSED NOW! Thanks for Participating! >>

Head First Data Analysis

Book Giveaway: Head First Data Analysis — A learner’s guide to big numbers, statistics and good decisions!

I love Head First series — if you haven’t read one of these books, you should — it’s great! So when I learned that they had a Data Analysis one, I had to read it. So I bought one and skimmed through it.

Now, Instead of letting it sit on my shelf, I think it might better serve its life purpose if more people read it so I have decided to do this little experiment.

Rules:

  1. You need to have an US-based address so that I can ship it to you (no cost to you!)
  2. You need to comment on this blog post on or before 07/22/2016 — just put your name & email. I’ll contact you if you win*

*Random selection!

Go!

How to create a Histogram in Excel?

Standard

Histogram is a powerful data analysis technique — it let’s you quickly see the distribution of the data you have. So in this post, I am going to list the steps to create histogram in Excel.

It’s a two-step process.

  1. Install “Data Analysis Tool Pak” (free Excel add-in)
  2. Format the data and build the histogram

Step 1: Install Data Analysis Tool Pak.

One of the most useful data analysis add-in in excel is not available by default! It’s called “Analysis ToolPak”

To activate it. Go to File > Excel options > Addins > For the manage field, select Excel add-ins

Histogram Manage Excel add-insMake sure that ToolPak is activated and click OK.

Histogram analysis toolpak excel(Also, Solver is a great add-in as well! It’s not in the scope of this article to discuss that add-in but it’s a powerful add-in as well. For instance, it let’s you work on optimization problems)

Step 2: Format Data and build the Histogram

So now let’s format the data.

You need two things to create a Histogram. 1) Data 2) Range

Here’s an example: (I have about 3000 numbers and need to see the distribution)

You could have other fields on the sheet as well but you need at least the data field. Range is optional but I recommend that you specify the Range so that your histogram would have the bins that you specified — otherwise you could have just used a bar chart!

Note that both of them are numerical.

Data Histogram

Now go to Menu Bar > Data > Data Analysis

Data Analysis HistogramOut of the options available, click on Histogram and select the Input Range and Bin Range > after you’re done, click OK.

Data Analysis Histogram ToolpakYou should see a new worksheet with raw data (ready for charting!). Now, create a Bar chart using the raw data and you have your histogram:

Histogram Excel Data AnalysisConclusion:

In this post I listed the steps you can take to create a Histogram in Excel. Note that there are other options as well — like R (hist function) that let’s you build histogram as well so you do have choice of tools but if you want to stick with excel and it’s good enough then you now know how. Cheers!

Related Post: What is the difference between Histogram & Bar Chart?

What is the difference between Histogram & Bar Chart?

Standard
Histogram Bar Chart
 Histogram Bar Chart
The x-axis represents bins. So if you have a continuous variable like age which has values from 0-100 then you can create bins like 0-10, 10-20 and so on (and here bin size = 10). You can change the bin size to analyze the distribution of the data.
X-axis has a numerical (quantitative) variable.
The x-axis represents distinct categories from your data.
The variable on the x-axis is usually qualitative
The order of the bins is important since it is used to understand the distribution of the data. The order of the categories in the bar chart doesn’t matter. We can sort it if we want but it’s not needed.

How do I prepare myself to be a data analyst?

Standard

Originally published on Quora: How do I prepare myself to be a Data Analyst?

Based on how you are framing your question, it seems that you currently don’t have “Data Analysis” Background but want to build a career in this field. Here are three things you could do:

  1. Learn Tech Skills: You will need technical knowledge to be successful at analyzing data. SQL and Excel are a good starting point. You could do a lot with these tools — then depending on the bandwidth that you might have you could explore R. How do you learn this? Here’s a learning pathway: Learn #Data Analysis online – free curriculum ; Also search for free courses on Coursera or other platforms.
  2. Learn Soft/Business Skills: This is as important as tech skills (if not more!) when it comes to Data Analysis. Finding Insights from your data is half the battle, you will need to put the insights in a context/story and influence business decisions and sometimes influence business change. we know change is always hard! So your soft/business skills will be very important. Also, you will benefit a lot from learning about how to break down problems, communicate your solution by using “business” language vs tech-speak.
  3. Apply them (and keep improving): Now that you have picked up some tech and soft/biz skills, apply them! Get an internship, Help out a non-profit in your free time (Data Kind, Statistics Without borders, Volunteer Match are good resources to find a non-profit) and start applying your skills! It would also help you get some “Real” world experience and applying what you have learned while “learning-on-the-job” is arguably the BEST way to pick something up!

Hope that helps!