All articles in 'Analytics' Category

Sara’s Copy Shop – Break even analysis and what-if modeling in Excel [Videos]

Published on Sep 13, 2016 in Analytics, Financial Modeling
Sara’s Copy Shop – Break even analysis and what-if modeling in Excel [Videos]

Last week, I asked you to share an analysis problem that you couldn’t solve in Excel. We got quite a few very interesting problems in comments and email. In this post, let me explain how to solve Sara’s copy shop problem using Excel.

What is Sara’s copy shop problem?
Thanks to Caroline who posted this problem.

Sara wants to open a copy shop. Each copier costs $5,000 per year to lease. The rent & other fixed costs per month are $300. There is a $0.02 variable cost per copy. Each copier can print up to 100,000 copies per year. She plans to charge $0.11 per copy from her customers. Sara estimates that the demand can be any of the 4 values – 500, 1000, 1500 or 2000 copies per day.

  1. Build a model to estimate profit per given number of copiers & demand values
  2. Find the mix of copiers & demand values that can make maximum profit for Sara (copiers – 1 to 6, demand – 500 to 2000)
Continue »

Tell me about an analysis problem that you couldn’t solve with Excel?

Published on Sep 9, 2016 in Analytics

Time for a quick show & tell.

Tell me about an analysis problem that you couldn’t solve with Excel?

It can be because you didn’t know how to solve the problem or Excel isn’t the tool for it or any other reason.

Go ahead and speak up. Post your tricky analysis problems in the comments section.

Continue »

Announcing 50 ways to analyze data course to make you an awesome analyst – We are open

Published on Sep 7, 2016 in Analytics, Learn Excel

Hi friends & readers of Chandoo.org,

I am very happy to invite you to our newest online class, 50 ways to analyze your data. This program makes you an awesome analyst, training you on vital skills like data analysis, data science, visualization, modeling business problems and finding best solutions.

Please click here to know more about this program & enroll.

What is this course?

50 Ways to analyze your data - an online course from Chandoo.org to make you a better analyst

It is the age of big data. Alas, what we need is big insights. But finding even small insights buried in our data is a hard task. To find the stories hidden in your data, you need to follow a process like this:

  1. Collect & clean data
  2. Structure the data
  3. Model business problems
  4. Analyze the data (or solve the problem)
  5. Visualize results
  6. Find conclusions
  7. Add layers of complexity to the problem
  8. Build what-if scenarios
  9. Reach conclusions
  10. Take action

This is where the 50 ways to analyze your data course helps. In this program, we analyze 50 familiar, important and diverse business situations using several of the above steps.

Continue »

18 ways to turn analysis projects into a nightmare

Published on Sep 5, 2016 in Analytics, Learn Excel
18 ways to turn analysis projects into a nightmare

Every week, we read news about failed analysis projects. If you listen carefully, you can hear the grunts, screams and curses of thousands of analysts all over the world about their analysis nightmares.

At Chandoo.org, we talk a lot about best practices for data analytics. So today, let’s peek in to the dark side and understand the mistakes that can turn your analysis project into a nightmare.

There are 3 parts in any analysis project

To understand these worst practices in analysis world, first let’s break analysis projects in to 3 parts.

  • Requirements
  • Data Structure
  • Tools & Construction

Let’s deep dive in to each area of the analysis projects to see what can go wrong.

Continue »

Visualizing Financial Metrics – 30 Alternatives

Published on Aug 30, 2016 in Analytics, Charts and Graphs
Visualizing Financial Metrics – 30 Alternatives

Around 2 months back, I asked you to visualize multiple variable data for 4 companies using Excel.  30 of you responded to the challenge with several interesting and awesome charts, dashboards and reports to visualize the financial metric data. Today, let’s take a look at the contest entries and learn from them.

First a quick note:

I am really sorry for the delay in compiling the results for this contest. Originally I planned to announce them during last week of July. But my move to New Zealand disrupted the workflow. I know the contestants have poured in a lot of time & effort in creating these fabulous workbook and it is unfair on my part. I am sorry and I will manage future contests better.

Continue »

Analyzing 300,000 calls for help [case study]

Published on Aug 24, 2016 in Analytics, Learn Excel, Pivot Tables & Charts, Power Pivot
Analyzing 300,000 calls for help [case study]

Over the weekend, I got an email from Mr. E, one of my students. Mr. E works at a police department in California and as part of his work, he was looking at calls received by police. Whenever police get a call for help, multiple teams can respond to the call and go to the location. All of these dispatches are recorded. So a single call can have several such dispatches. And Mr. E wanted to findout which team responded the first. The problem?

Finding the first responded team is tricky.

Today let’s take up this problem as a case study and understand various methods to solve it.  We are going to learn about writing better lookups, pivot tables, power pivot and optimization. Put on your helmets, cause this is going to be mind blowingly awesome.

Continue »

Analyzing half a million consumer complaints [Part 1 of 3]

Published on Feb 16, 2016 in Analytics, Charts and Graphs
Analyzing half a million consumer complaints [Part 1 of 3]

How would you analyze data when you have lots of it? That is the inspiration for this series.

Let’s meet our data – Finance Industry Consumer Complaints

As part of open data initiatives, US government & Consumer Financial Protection Bureau maintain a list of all consumer complaints made against financial institutions (banks, credit unions etc.) You can download this data from the catalog page here. I have obtained the data on 1st of February, 2016. The download has 513,824 records. Each row contains one complaint.

In this and next two parts of the series, we are going to analyze these half a million complaints to find insights.

Continue »

Reconcile debits & credits using Solver [Advanced Excel]

Published on Aug 18, 2015 in Analytics, Excel Howtos
Reconcile debits & credits using Solver [Advanced Excel]

Here is a tricky problem often faced by accountants and finance professionals: Let’s say you have 5 customers. Each of them need to pay you some money. Instead of paying the total amount in one go, they paid you in 30 small transactions. The total amount of these transactions matches how much they need to pay you. But you don’t know which customer paid which amounts. How would you reconcile the books?

If you match the transactions manually, it can take an eternity – after all there are more than 931 zillion combinations (5^30).

This is where solver can be handy. Solver can find optimal solution for problems like this before you finish your first cup of coffee.

Continue »

15 Quick & powerful ways to analyze business data

Published on Jul 1, 2015 in Analytics, Charts and Graphs, Learn Excel
15 Quick & powerful ways to analyze business data

Here is a situation all too familiar.

You are looking at a spreadsheet full of data. You need to analyze and tell a story about it. You have little time. You don’t know where to start.

Today let me share 15 quick, simple & very powerful ways to analyze business data. Ready? Let’s get started.

Continue »

CP030: Detecting fraud in data using Excel – 5 techniques for you

Published on Feb 19, 2015 in Analytics, Chandoo.org Podcast Sessions

In the 30th session of Chandoo.org podcast, let’s learn how to uncover fraud in data.

How to detect fraud in data - 5 techniques for you - CP030 -  Chandoo.org podcast

What is in this session?

In the wake of hedge fund scams, accounting frauds and globalization, We, analysts are constantly second guessing every source of data. So how do you answer a simple question like, “am I being lied to?” while looking at a set of numbers your supplier has sent you.

That is our topic for this podcast session.

In this podcast, you will learn

  • Quick announcements about 50 ways & 200k BRM
  • Introduction to fraud detection
  • 5 techniques for detecting fraud
    • Benford’s law
    • Auto correlation
    • Discontinuity at zero
    • Analysis of distribution
    • Learning systems & decision trees
  • Implementing these techniques in Excel
  • A word of caution
Continue »

Who is the most consistent seller? [BYOD]

Published on Feb 18, 2015 in Analytics, Excel Howtos, Pivot Tables & Charts
Who is the most consistent seller? [BYOD]

Who is the most consistent of all?

Imagine you are a category manager at a large e-commerce company. Your site offers various products, but you don’t really make these products. You list products made by other vendors on your site. Every day, these vendors would send you invoices for the amount of product they have sold. Above is a snapshot of such invoices.

Looking at this list, you have a few questions.

  1. Who is the best seller?
  2. Who is the most active seller?
  3. Who is the most consistent seller?
  4. Which seller has fewest invoices?

Let’s go ahead and answer these using Excel. Shall we?

Continue »

Revenue vs. Commission growth – Getting the message across [BYOD]

Published on Feb 17, 2015 in Analytics, Charts and Graphs

Situation: Our commissions are growing way faster than revenues
Let’s say you are looking revenues & sales commissions of your company for last few years. The data looks like this:

revenue-growth-vs-commission-growth-data

And you want to highlight the fact that commissions are growing faster than revenues.

So you plot YoY growth rates for revenues & commissions.

Problem: The chart of YoY growth rates is not convincing

Take a look at the chart. It doesn’t convey the message that we want. At best it says “revenue growth is less than commission growth”

revenue-growth-vs-commission-growth-problem

How to convey the message “Commission growth is a problem for us”?

Continue »

Doing Cost Benefit Analysis in Excel – a case study

Published on Jan 28, 2015 in Analytics, Charts and Graphs, Financial Modeling
Doing Cost Benefit Analysis in Excel – a case study

Imagine you are the in-charge of finance department at Hogwarts. So one fine day, while you are practicing the spells, Dumbledore walks in to your office and says, “Our electricity bills are way too high. As the muggles don’t accept wizard money, we have to find a way to reduce our power consumption.”

So you summoned the previous 12 month utility bills to examine energy consumption patterns, and pretty soon you realized that most of the electricity consumption is due to the light bulbs. You suddenly have a brilliant idea. Why not replace the light bulbs with a variety that consumes low power? A light bulb moment indeed.

Your next step is to figure out what varieties of light bulbs are out there. Fortunately this is easier than catching a snitch in a game of quidditch. A quick search revealed that there are 3 types of light bulbs:

  • Regular incandescent bulbs (the kind Hogwarts currently uses)
  • Compact Fluorescent Light bulbs (CFL)
  • Light Emitting Diode bulbs (LED)

Now your job is to do a cost benefit analysis of these options and pick one.

Continue »

Please help me design our new course – “50 ways to analyze your data”

Published on Sep 10, 2014 in Analytics, Learn Excel
Please help me design our new course – “50 ways to analyze your data”

Hi friends & readers of Chandoo.org,

I have an exciting and awesome new training program for you. 50 ways to analyze your data is aimed to give you critical data analysis & charting skills necessary for your success as an analyst (or manager). I want to make sure that this course offers you best content & value. I need your help in designing it. Please take a few minutes to read this short post & share your feedback.

Continue »

Remove duplicates & sort a list using Pivot Tables

Published on Sep 27, 2010 in Analytics, Learn Excel, Pivot Tables & Charts
Remove duplicates & sort a list using Pivot Tables

Removing duplicate data is like morning coffee for us, data analysts. Our day must start with it. It is no wonder that I have written extensively about it (here: 1, 2, 3, 4, 5, 6, 7, 8). But today I want to show you a technique I have been using to dynamically extract and sort […]

Continue »