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.
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.
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.
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.
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.
CP030: Detecting fraud in data using Excel – 5 techniques for you
Podcast: Play in new window | Download
Subscribe: Apple Podcasts | Spotify | RSS
In the 30th session of Chandoo.org podcast, let’s learn how to uncover fraud in data.
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
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.
- Who is the best seller?
- Who is the most active seller?
- Who is the most consistent seller?
- Which seller has fewest invoices?
Let’s go ahead and answer these using Excel. Shall we?