Interactive Microsoft Excel Dashboard for Data Analyst

The dataset used in this dashboard is data pulled from income and expenses statements; it has 10 columns and 487 rows for 2021.

Your task (insight required from the dataset)

1) What is the stream of income, how much do we make in our income categories, and what are the categories we spent this money on, show the result hierarchically?

2) Inflow and outflow. Make Debit and credit plus amount left easy to read.

3) Analyze the income sources and show how much had been made on each source to date.

4) On a weekly basis show expenses by categories

5) On a single chart ? visualize and transit between income and expenses on a monthly basis

6) What are the top-5 things we spent money on, mostly?

7) Show important KPI’s on cards ? for easy readability and for quick decision making

Active filters needed to interact the data and the dashboard: Month and categories column

Week-1

Personal Expenses Dashboard….

The dataset used in this dashboard is data pulled from income and expenses statements; it has 10 columns and 487 rows for 2021.

Your task (insight required from the dataset)

1) What is the stream of income, how much do we make in our income categories, and what are the categories we spent this money on, show the result hierarchically?

2) Inflow and outflow. Make Debit and credit plus amount left easy to read.

3) Analyze the income sources and show how much had been made on each source to date.

4) On a weekly basis show expenses by categories

5) On a single chart ? visualize and transit between income and expenses on a monthly basis

6) What are the top-5 things we spent money on, mostly?

7) Show important KPI’s on cards ? for easy readability and for quick decision making

Active filters needed to interact the data and the dashboard: Month and categories column

Dashboard for shipping status….

This dashboard contained thousands of rows of data with one transactional table and 4 other tables called dimensional tables with data.

The company sells goods like furniture, office supplies, and tech-related products. The company shipped those goods to its warehouse before sales and distribution. They use shipping companies like COSCO, Hapag Lioyd, Evergreen Line, YMMT, APM Maersk, and MSC.

The goal here is to find insight using the historical data provided by the company to make features shipping, sales, or delivery faster and more efficient.

Your task (insight required from the dataset)

1) Using sub-category, what are the quantity ordered, delivered and how many of the orders are not delivered over time

2) From the insight gotten from the first step, make it easy to sort ascending and descending order dynamically by clicking on some option buttons. The idea is to be able to know where we have high and fewer orders delivered, and undelivered.

3) Make chart transition that can display quantity ordered, received, unreceived, and number of transactions over time using the contained transaction years in the dataset

4) Using the shipping companies, how many quantities were ordered and how many of those orders were received. Use a chart that will best show a comparison.

5) Which shippers do we use most to ship those goods?

6) Numbers of goods that are not delivered by the use shipping companies

7) Show these three insights on cards for easy readability: Quantity ordered, Quantity shipped, and Quantity unshipped

The active filter needed to interact with the data and the dashboard: Year slicer/filter and shippers slicer/filter.

Dashboard for shipping status….

This dashboard contained thousands of rows of data with one transactional table and 4 other tables called dimensional tables with data.

The company sells goods like furniture, office supplies, and tech-related products. The company shipped those goods to its warehouse before sales and distribution. They use shipping companies like COSCO, Hapag Lioyd, Evergreen Line, YMMT, APM Maersk, and MSC.

The goal here is to find insight using the historical data provided by the company to make features shipping, sales, or delivery faster and more efficient.

Your task (insight required from the dataset)

1) Using sub-category, what are the quantity ordered, delivered and how many of the orders are not delivered over time

2) From the insight gotten from the first step, make it easy to sort ascending and descending order dynamically by clicking on some option buttons. The idea is to be able to know where we have high and fewer orders delivered, and undelivered.

3) Make chart transition that can display quantity ordered, received, unreceived, and number of transactions over time using the contained transaction years in the dataset

4) Using the shipping companies, how many quantities were ordered and how many of those orders were received. Use a chart that will best show a comparison.

5) Which shippers do we use most to ship those goods?

6) Numbers of goods that are not delivered by the use shipping companies

7) Show these three insights on cards for easy readability: Quantity ordered, Quantity shipped, and Quantity unshipped

The active filter needed to interact with the data and the dashboard: Year slicer/filter and shippers slicer/filter.

Profit Analysis Dashboard ….

This is another dynamic and highly interactive dashboard that we built using DAX. DAX stands for Data Analysis Expression, which is a common language used on different software like Power BI and others.

The aim here is to help you get used to some simple DAX language should you have large data that you want to analyze in excel. DAX is always an ideal way to handle large data pushed into power pivot in Microsoft Excel.

The dataset used in this class has its base in a folder, in a single folder we have data from 2019 to 2021. We know the data in this folder as the Fact Table, the Fact Table is the table that houses the transaction details, like purchases, quantity, order dates, and some important IDs that will link the Dim Tables.

There is five or sex Dim Table that will use Power pivot to handle and create relationships in other to get a free flow of data when creating our analysis.

Your task (insight required from the dataset)

1) Need to see the cumulative sales from the beginning of the transaction year to the last transactional year which is 2021. It should be dynamic should there is new data in for the subsequent years to come.

2) Who 3 profitable customers? This will make us know who among our customers support our company growth.

3) Who are the 3 least customers? This will help our company to find why and what we need to do to motivate them to buy more from us in the future.

4) Show the yearly profit and growth

5) What is the trend of our profits on a monthly basis?

6) What is the trend of our profits quarterly?

7) Other insights needed are the numbers of transactions, total customers, numbers of products the company has currently, and numbers of countries that we sell to.

I needed the active filter to interact with the data and the dashboard: Country, products, and months

Cancer Mortality Dashboard ….

Let’s work on non-financial data and see how we can get more creative creating a dashboard to decide on how to curb and deal with cancers using historical data from 1914 to 2019 with 34 columns and over 3,000 rows of data.

The causalities had been spat into distinct buckets in other to make it easy to spot patterns and decide. I had categorized it into age groups from age-5 all the way to age 90+.

Your task (insight required from the dataset)

1) Show causalities by age groups categories. This will make it easy to read which age group has more causalities.

2) Create a comparison chart that can dynamically compare age groups based on selections.

3) What are the cancers that affect the younger people under the age of 5, 5-9, 10-14, and 15-19

4) Total mortality by gender.

Human Resource (HR) Dashboard ….

HR is the field of business concerned with recruiting and managing employees. In other to track and the progress of the recruited employees over time the management will need a good hand to handle the recruitment historical data and the track record of the individual employees in the organization.

The dataset is in CSV format and is very rough which might get you to fret and freeze at first glance. We used an inbuilt tool called Power Query in Microsoft Excel to have the data cleaned and transformed into what we want and what we can analyze in other to help the organization.

Your task (insight required from the dataset)

1) What some criteria provided in the worksheet are how many employees need to be promoted under the Bachelor’s degree holders?

2) What are some criteria provided in the worksheet how many employees need to be promoted under the Doctorates holders?

3) What some criteria provided in the worksheet how many employees need to have salary increment in Tech support department

4) What are some criteria provided in the worksheet how many employees need to be promoted under the Sales and Marketing department?

5) Show total employees by educational background, what are the numbers of male and female employees based on the educational breakdown.

6) Total numbers of employees by job types and salary

7) Employees attrition (active and inactive employees)

8) Total employees by business units and salary pay to those business units. There should be some kind of transition between the numbers of employees and salary in one chart.

9) What are the five departments with the highest employees in the organization?

10) Numbers of employees by gender categories

11) Show numbers of employees by job classifications and on a table-view make it easy-to-read job classifications and with the educational breakdown

The active filter needed to interact the data and the dashboard: Department and gender

International migration Dashboard ….

This is another non-financial dashboard but a dashboard to analyze the migration of people from their countries to foreign countries for several reasons according to the historical data captured.

The dataset captured from immigrants show different visa like student visa, work visa, visiting visa, and residential visa. We only have data for January and February 2020 with over 40 thousand rows and over 13 columns.

Your task (insight required from the dataset)

1) Numbers of male and female immigrants

2) Migration detail on a monthly basis (January and February) makes it dynamic to capture new data if any.

3) How many people migrate with pets and without pets?

4) How many people migrate from these focus countries (Nicaragua, Niger, Nigeria, and Niue)

5) Hierarchically show visa according to how it is obtained by immigrants

6) Age-group by travel history

7) Numbers of final and provisional (Visa status)

The active filter needed to interact with the data and the dashboard: Visa types and status

A & B Investment Dashboard ….

This is one of my favorite dashboards ever. This dashboard is about the movement of goods from the seller to the buyers. The company sells seven distinct products and for every shipment, there is always track of the status of the shipment like Shipped, Received, Canceled, In progress, Resolved, Dispute, and on hold.

The status is an area of focus as it is important for the company to know how all the products shipped went.

The dashboard has a search feature to help get a quick look at buyers' or company’s transactions. With a search feature you can get to know about a particular company that buys from you, all you need do is type in the buyer’s company’s name into the search bar and hit enter, there you go, you have the results of all the search transaction.

Your task (insight required from the dataset)

1) Analyze total orders from 2020 to the last years of the transaction with a trend line that show monthly purchases

2) Make the same analysis and show it in percentages

3) What are the min and max orders for every sales year and what month do we have the min and max in?

4) Transactional status is a key to the company in other to track the progress of its purchases, make sure you display orders values to every status and show it up in cards for easy readability and decision making

5) Create a search bar that can quickly search for buyers’ transactions

6) How many do we have on each product and what is the quantity sold. Use images to represent each product.

The active filter needed to interact with the data and the dashboard: Country.

On filter selection, let it display the countries selected, and if there is none, let it display this message: Showing all countries orders, use the filter to get a new view or analysis.