Locked lesson.
About this lesson
Learn about the role of the Business Analyst and how Excel is used to drive business decisions.
Exercise files
Download this lesson’s related exercise files.
1.02 how-excel-is-used-by-business-analysts - Exercise.docx52.8 KB 1.02 how-excel-is-used-by-business-analysts - Exercise solution.docx
50.1 KB
Quick reference
How Excel is used by Business Analysts
Learn how Excel is used by Business Analysts.
When to use
This video is useful if you want to learn about how Excel is used by Business Analysts in real-world scenarios.
Instructions
Excel is an essential skill for any aspiring Business Analyst. A large part of the role is knowing how to deep dive into unruly datasets, make sense of them, and present key findings to colleagues, managers, and stakeholders so they can make better business decisions.
It's a key role when it comes to the success of the business.
Not all business analysts are intentional BAs. Data is such a huge part of everyone's lives these days, most of us will probably have to analyze data at some point in our careers.
Business Analysis in the Real World
Global Megastore Example
Maybe you are a Business Analyst who's been asked to extract meaning from a large amount of sales data for a global megastore.
He needs the data to answer questions that are important to the stakeholders.
- What are the yearly trends?
- Which months are most profitable?
- What are the top-selling products?
- Which products are not performing well?
- Which sales channel is most popular?
Excel can help us answer all of these questions.
Employee Demographics Example
The HR team has asked the Business Analyst to take a deep dive into their demographic data to help them with hiring decisions moving forward.
They would like the data to answer the following questions?
- How many employees are there?
- What's the split between male and female employees?
- How many contractors are there compared to full-time staff?
- What are the age demographics across the company?
- How many sick days has each staff member taken?
Finding answers to these questions can show gaps and policy issues. For example, maybe the company is hiring too many men in a certain role. Maybe the company is unknowingly discriminating against candidates over a certain age.
Presenting Results
Once we've analyzed the data it is important to know how to present the results effectively. What charts should we use to communicate the point clearly? What formulas do we need to extract the relevant information? Do we want to create an engaging dashboard? What colors should we use?
We need to think about all of this when we need to present key metrics.
Login to download- 00:03 Excel is an essential skill for any aspiring business analyst.
- 00:09 A large part of a business analyst's role is to deep dive into data,
- 00:14 make sense of that data, and present their findings to staff,
- 00:19 managers, and key stakeholders.
- 00:21 And in turn, that helps the company make better business decisions moving forward.
- 00:26 So it's a really important role when it comes to the overall success of
- 00:30 the business.
- 00:31 Now we might even find ourselves in a business analyst role to varying degrees,
- 00:36 even if we aren't officially a business analyst.
- 00:39 Maybe you're a business analyst who needs to extract meaning from the company sales
- 00:44 figures.
- 00:45 So your CEO can see trends throughout the year, peaks and troughs,
- 00:49 which products are selling well, which aren't, and
- 00:53 which sales channels are performing, so on and so forth.
- 00:57 And as business analysts, we need to take that raw, sometimes unruly data, know
- 01:02 how to clean it up, analyze it, and answer some of the most important questions.
- 01:08 For example, what was our total profit compared to cost for quarter one?
- 01:12 Which product has outperformed all others?
- 01:15 What are our slow months in the year?
- 01:17 What could we do to make our sales more consistent?
- 01:20 These are the types of questions that we can answer using Excel.
- 01:24 What about another type of scenario?
- 01:26 Maybe we're a business analyst and
- 01:29 we've been asked by HR to analyze demographic data.
- 01:33 How many employees do we currently have?
- 01:35 What's the split between male and female employees?
- 01:38 What about contractors versus full time staff?
- 01:41 What are age demographics?
- 01:43 How many sick days does each employee taken throughout the year?
- 01:46 It's a very valid question that again can all said using Excel.
- 01:50 And maybe once we've analyzed this data,
- 01:53 we start to uncover things that we'd never fully appreciated before.
- 01:57 Maybe we find that we're hiring a lot more men in certain positions than women.
- 02:01 And maybe that little revelation might determine how our hiring process moves
- 02:06 forward in the future.
- 02:08 So, as I said,
- 02:09 it is such an important role when it comes to really understanding our business.
- 02:14 And of course, it is imperative that you know how to present these results in
- 02:19 an engaging and informative way.
- 02:21 What charts do you want to use?
- 02:23 What chart type is going to be best to present this data clearly?
- 02:27 What about using an interactive dashboard to really present high level
- 02:31 key metrics to staff, managers, and stakeholders?
- 02:35 What about color, branding, clarity?
- 02:38 How do we include all of this in our analysis?
- 02:40 These are the types of issues that business analysts come across every
- 02:44 single day.
- 02:45 And throughout the balance of this course we're going to be exploring all of this
- 02:49 and then some.
Lesson notes are only available for subscribers.