###### Youtube Analytics Report (Looker Studio)

<div style="text-align: center">![](/images/yt%20db-589777c9.png)</div>

###### Electronics Store Orders and Sales Dashboard Report (Looker Studio)

<div style="text-align: center">![](/images/es%20db.png)</div>

###### Hardware store branches report (multi paged) (Looker Studio)

<div style="text-align: center">![](/images/hs%20db.png)</div>



Hall of Dashboard Reports

### Objective:

The objective of this project is to automate the process of creating excel reports, in this case, a “Daily Sales” report generated at the end of every business day. The report includes the total number of sales and total number of orders for that day, based on date prompt. The report should be generated in excel, converted to PDF format, and sent via email using Microsoft Outlook.

### Problem Statement:


The manual process of creating daily sales report is time consuming and prone to errors. By automating this process using VBA, we aim to save time, improve accuracy and streamline the reporting and mailing process.

#### Requirements:

*   Excel 2016 and above

*   Microsoft Outlook

### Deliverables:

1.  VBA code that creates a daily sales report based on the provided date prompt

2.  A new Excel sheet containing the daily sales report, including the total sales and total number of orders.

3.  Conversion of the daily sales report sheet to PDF format.

4.  An email containing the PDF attachment sent to the specified recipient using Microsoft Outlook.

## Master Data Sheet: 

This is our data sheet containing all necessary data

![](/images/vba1.png)

Upon running the VBA macro, the date input prompt pops up …

### 
Prompt for Date Input: 

The program asks the user to input a specific date for which the daily sales report needs to be generated.

<div style="text-align: center">![](/images/vba%20date.png)</div>

#### Search for Date in Master Data: 

The VBA code applies a filter in the master sheet for the corresponding column using the date input as criteria

#### Calculate Total Sales and Total Number of Orders: 

Using the filtered data, the VBA calculates the total sales by summing up the sales values, and also counts the total number of orders for the specified date as well.

#### Creating a New Sheet for Daily Sales Report: 

The VBA code cleares the filter in the master sheet and creates a new sheet in the the Excel workbook, to hold the daily sales report.

#### Populating the Report Sheet: 

The VBA populates the newly created sheet with the following information.
•	Date: the specified date
•	Total Sales: calculated total sales for the specified date.
•	Total Number of Orders: The calculated total number of orders for the specified date.
It also properly formats all cells, figures and columns.

#### Convert the Daily Sales Report to PDF: 

The VBA code converts the created sheet into PDF format ready to be mailed.

#### Send Email Via Outlook:


•	The VBA code uses Microsoft Outlook Application to send an email to the recipient.
•	The email includes the PDF file as an attachment
•	The recipient’s email address is specified in the code.



![](/images/vba%20report%20and%20prompt-52537890.png)

<div style="text-align: center">*Populated Report in New sheet and Prompt showing successful completion of all steps***</div>

<div style="text-align: center">![](/images/vba%20email.jpg)</div>

<div style="text-align: center">*Displayed Sent Email in Recipient’s Inbox***</div>

<div style="text-align: center">![](/images/vba%20pdf%20report.jpg)</div>

<div style="text-align: center">*PDF Attachment Showing Daily Sales Report for Specified Date*</div>



#### Further Development:


The current code implementation can be extended to handle more complex requirements and accommodate additional functionalities. Such as:

1.  Date Range Input:
    The code could be further developed to accept date ranges as input, allowing the generation of sales reports for a specific range of dates. This would provide a more comprehensive view of sales trends and performance over a specific period.

2.  Sending Emails to Multiple Recipients:
    To increase the flexibility of the system, the code could be improved to support sending the daily sales report to multiple recipients. This would enable efficient distribution of the report to different stakeholders or departments within the organization.

3.  More complicated report types.

#### VBA Code:

```
Sub GenerateDailySalesReport()
Dim inputDate As Date
Dim wsData As Worksheet
Dim wsTemp As Worksheet
Dim wsReport As Worksheet
Dim lastRow As Long
Dim filteredRange As Range
Dim totalSales As Double
Dim orderCount As Long
```

```
' Ask for the input date
inputDate = InputBox("Enter the order date (dd/mm/yyyy):")

' Set the data sheet
Set wsData = ThisWorkbook.Worksheets("Master Sales Data")

' Set the report sheet or create a new one
On Error Resume Next
Set wsReport = ThisWorkbook.Worksheets("Daily Sales Report")
On Error GoTo 0

If wsReport Is Nothing Then
    Set wsReport = ThisWorkbook.Worksheets.Add
    wsReport.Name = "Daily Sales Report"
Else
    wsReport.Cells.Clear
End If

' Turn off the filter
wsData.AutoFilterMode = False

' Create a temporary sheet
Set wsTemp = ThisWorkbook.Worksheets.Add
wsTemp.Name = "Temp"

' Find the last row in the data sheet
' lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

' Filter the data for the input date and copy to the temporary sheet
wsData.Range("A1:R9801").AutoFilter Field:=3, Criteria1:=inputDate
wsData.Range("A1:R9801").SpecialCells(xlCellTypeVisible).Copy Destination:=wsTemp.Range("A1")

' Get the filtered range in the temporary sheet
Set filteredRange = wsTemp.UsedRange

' Calculate the total sales and order count from the filtered range
totalSales = Application.WorksheetFunction.SumIf(filteredRange.Columns("R"), ">0")
orderCount = filteredRange.Columns("B").Cells.Count - 1

' Turn off the filter
wsData.AutoFilterMode = False

' Create the report headers
wsReport.Range("A1").Font.Bold = True
wsReport.Range("A2:B2").Font.Bold = True

wsReport.Range("A1") = "Daily Sales Report for " & Format(inputDate, "mm/dd/yyyy")
wsReport.Range("A2") = "Total Sales"
wsReport.Range("B2") = "Number of Orders"

' Insert the calculated values
wsReport.Range("A3").NumberFormat = "$#,##0.00"
wsReport.Range("A3") = totalSales
wsReport.Range("B3") = orderCount

' Autofit columns
wsReport.Columns("A:B").AutoFit
```

```
' Save the report as PDF
Dim fileName As String
fileName = "Daily_Sales_Report_" & Format(inputDate, "yyyymmdd") & ".pdf"
Dim filePath As String
filePath = Environ("TEMP") & "" & fileName ' Use the user's temporary folder path
```

```
wsReport.ExportAsFixedFormat Type:=xlTypePDF, fileName:=filePath, Quality:=xlQualityStandard

' Create Outlook objects
Dim OutlookApp As Outlook.Application
Dim OutlookMail As Outlook.MailItem

' Initialize Outlook
Set OutlookApp = New Outlook.Application
Set OutlookMail = OutlookApp.CreateItem(olMailItem)

' Compose the email
With OutlookMail
    .BodyFormat = olFormatHTML
    .Display
    .HTMLBody = "Dear Sir" & "<br>" & "<br>" & "Please find the attached file." & "<br>" & "<br>" & .HTMLBody
    .To = "email@gmail.com"
    ' .CC = "xyz@gmail.com"
    .Subject = "Daily Sales Report - " & Format(inputDate, "mm/dd/yyyy")
    .Attachments.Add filePath ' Attach the PDF from the temporary folder
    .Send
End With

' Release the Outlook objects from memory
Set OutlookMail = Nothing
Set OutlookApp = Nothing

' Delete the temporary sheet
Application.DisplayAlerts = False
wsTemp.Delete
Application.DisplayAlerts = True


' Show a message with the file path
MsgBox "Daily sales report created and sent via email.", vbInformation
```

```
End Sub
```



Thank you.


Automated Excel Reporting and Mailing with VBA

### Introduction

Africa, a continent abundant in natural resources, continues to grapple with significant economic and social challenges, making it one of the most disadvantaged regions worldwide. The persistence of poverty, inadequate healthcare, and low life expectancy are among the critical issues confronting African nations. While progress has been made in recent years, urgent attention is required to address these persistent challenges.

Poverty, life expectancy, and Gross Domestic Product (GDP) are pivotal indicators that shed light on a country's economic and social conditions. In Africa, these indicators exhibit substantial variation among countries, making it complex to comprehend the intricate relationship between poverty, life expectancy, and GDP. This case study endeavors to delve deeply into the correlation among these indicators within African countries.

### Questions answered in this analysis

*   [Question 1: What is the distribution of poverty across African countries?](http://localhost:8888/lab#question1)

*   [Question 2: Which African countries have the highest and lowest GDP?](http://localhost:8888/lab#question2)

*   [Question 3: Which African countries have the highest and lowest life expectancy?](http://localhost:8888/lab#question3)

*   [Question 4: What is the trend for life expectancy in African countries based on historical data?](http://localhost:8888/lab#question4)

*   [Question 5: What is the projected trend for life expectancy in African countries based on historical data and current trends?](http://localhost:8888/lab#question5)

*   [Question 6: How do poverty, life expectancy, and GDP vary across different income groups in all countries?](http://localhost:8888/lab#question6)

*   [Question 7: How does life expectancy affect GDP and Poverty and vise versa](http://localhost:8888/lab#question9)

*   [Conclusions](http://localhost:8888/lab#conclusions)

*   [Recommendations](http://localhost:8888/lab#recommendations)

### Question 1: What is the distribution of poverty across African countries?

##### ![](/images/Aly1.PNG)

Insight

The dataset reveals that there is a wide range of poverty levels across African countries. Some countries, such as the **Democratic Republic of Congo (COD)**, **the Central African Republic (CAF)**, and **Mozambique (MOZ)** and a few others have almost **40-50%** of their population earning a daily income of less than one dollar.

On the other hand, majority of countries in Africa have up to **50% of their total population** earning between one dollar and two dollars and fifteen cents.
These findings highlight the need for more targeted poverty reduction efforts and policies that focus on improving income levels in these countries.

### **Question 2:** Which African countries have the highest and lowest GDP?

##### ![](/images/Aly2.PNG)

Insights

Based on the analysis of the GDP data for African countries in **2021**, we can see that **Nigeria (NGA)** has the highest GDP followed by **South Africa (ZAF)** and **Egypt (EGY)**. These countries are among the most populous on the continent and have some of the largest economies.

On the other hand, countries like **Sao Tome and Principe (STP)**, **Comoros (COM)**, and **Seychelles (SYC)** have the lowest GDPs among African countries in 2021.

### **Question 3:** Which African countries have the highest and lowest life expectancy?

##### ![](/images/Aly3.PNG)

Insights

Based on the data, we can see that some African countries have a relatively high life expectancy at birth, such as **Algeria DZA (76.4 years)**, **Cape Verde CPV (74.1 years)**, and **Morocco MAR (74.0 years)**.
However, other countries, such as **Chad TCD (52 years)**, **Nigeria NGA (53 years)**, and **Lesotho LSO (53 years)**, have much lower life expectancies.

The differences in life expectancy between these countries may be attributed to various factors, including healthcare access, disease prevalence, and lifestyle factors. Countries with higher life expectancies may have better access to healthcare and disease prevention measures, while countries with lower life expectancies may face challenges in providing adequate healthcare and managing infectious diseases.

To improve life expectancy in African countries with lower rates, efforts could be made to improve healthcare infrastructure and disease prevention measures. Additionally, education campaigns to promote healthy lifestyle choices, such as regular exercise and healthy eating, could also help improve overall health outcomes.

### **Question 4:** What is the trend for life expectancy in African countries based on the historical data?

##### ![](/images/Aly4.PNG)

##### Insights

Based on the available data from 1921 to 2021, we can see that there has been a general trend of increasing life expectancy in African countries over the years. **In the 1920s, the average life expectancy was around 25-30 years**, but this number has risen significantly to **an average of 62 years in 2021**.
This increase can be attributed to various factors such as advancements in healthcare, better access to education and information, improved living conditions, and disease prevention efforts.

However, with continued efforts towards improving healthcare infrastructure and education, it is hoped that the trend of increasing life expectancy in African countries will continue in the coming years.

###

**Question 5:** What is the projected trend for life expectancy in African countries based on historical data?

We would put our emphasis on 3 countries with lowest life expectancy (Chad, Nigeria and Lesotho)

![](/images/Aly5.PNG)

**We then made projections for Nigeria**

We predicted for next 10 years

##### ![](/images/Aly6.PNG)

![](/images/Aly7.PNG)

Insights

Based on the analysis of historical data from 1950 to 2021 and the projections made for the next 10 years, it can be observed that there could be a **gradual increase in life expectancy in Nigeria**, which can be attributed to various factors such as improved healthcare facilities, increased access to education and information, better nutrition, and advancements in medical technologies.

However, there are still challenges to be addressed to improve the overall life expectancy rate in Nigeria, such as reducing infant mortality rates, improving access to clean water and sanitation, and increasing public awareness of health issues.

Therefore, it is important for the federal government (FGN) to continue investing in healthcare infrastructure and promoting health education and awareness to sustain the current trend and improve the overall health of the population.

#####

### **Question 6:** How do poverty, life expectancy, and GDP vary across different income groups in all countries?

![](/images/Aly8-820512b5.PNG)

##### **Insights**

Poverty, life expectancy, and GDP vary across different income groups in all countries.

> Wealthier countries tend to have lower poverty rates, higher life expectancies, and higher GDPs.
> The poorest countries have the highest poverty rates, lowest life expectancies, and lowest GDPs.
> There is a great deal of variation within each income group.
>

The relationship between poverty, life expectancy, and GDP is clear. Wealthier countries tend to have lower poverty rates, higher life expectancies, and higher GDPs. This is because wealth can be used to invest in things like healthcare, education, and infrastructure, all of which contribute to improved health and well-being.

### 
**Question 7:** How does life expectancy affect GDP and Poverty and vise versa?


![](/images/Aly9-2f8edb09.PNG)

#### Insights

**GDP and life expectancy are positively correlated.** This means that countries with higher GDPs tend to have higher life expectancies. This is likely due to a number of factors, such as better access to healthcare, education, and nutrition in wealthier countries.

**GDP and poverty are weakly positively correlated.** This means that countries with higher GDPs tend to have slightly lower levels of poverty. This is likely due to the fact that wealthier countries have more resources to invest in social programs and infrastructure, which can help to reduce poverty.

**Poverty and life expectancy are negatively correlated.** This means that countries with higher poverty rates tend to have lower life expectancies. This is likely due to the fact that people who are poor are more likely to live in unhealthy environments, have less access to healthcare, and have lower incomes, which can all contribute to shorter life expectancies.

### 

Conclusions

> Poverty

*   Poverty is a major problem in Africa, with over **40%** of the population living on less than one dollar ninety cents a day.

*   The distribution of poverty is uneven, with some countries, such as the Democratic Republic of Congo and Mozambique, having much higher poverty rates than others.

*   There are a number of factors that contribute to poverty in Africa, including conflict, corruption, and climate change.

> Life expectancy

*   Life expectancy in Africa has been increasing in recent years, but it is still lower than the global average.

*   The highest life expectancies are found in countries with the highest GDPs, such as Algeria and Morocco.

*   The lowest life expectancies are found in countries with the lowest GDPs, such as Chad and Lesotho.

> GDP

*   GDP per capita in Africa has been increasing in recent years, but it is still lower than the global average.

*   The highest GDPs are found in countries with the largest populations, such as Nigeria and South Africa.

*   The lowest GDPs are found in countries with the smallest populations, such as Sao Tome and Principe and Seychelles.

In Conclusion, There is a clear relationship between poverty, life expectancy, and GDP in Africa.


*   Countries with higher GDPs tend to have lower poverty rates, higher life expectancies, and better healthcare and education systems.


*   Countries with lower GDPs tend to have higher poverty rates, lower life expectancies, and poorer healthcare and education systems.

There are a number of challenges that need to be addressed in order to reduce poverty, improve life expectancy, and improve GDP in Africa. These challenges include conflict, corruption, climate change, and lack of access to education and healthcare.

Despite the challenges, there is reason to be optimistic about the future of Africa. Africa has a young and growing population, and it is rich in natural resources. With the right policies and investments, Africa can continue to grow and develop, and it can improve the lives of its citizens.

### 
Recommendations

Africa is a vast and diverse continent with a population of over 1.2 billion people. It is also a continent with a great deal of potential, with abundant natural resources and a young and growing population. However, Africa also faces a number of challenges, including poverty, hunger, disease, and conflict.

There are a number of things that can be done to improve economic and social conditions in Africa. These include:

*   **Investing in education and healthcare:** Education and healthcare are essential for economic growth and development. By investing in these sectors, African countries can improve the skills and productivity of their workforce and reduce the burden of disease.

*   **Promoting good governance:** Good governance is essential for attracting investment and creating a stable and predictable environment for businesses. African countries need to strengthen their institutions and fight corruption in order to promote good governance.

*   **Encouraging trade and investment:** Trade and investment are essential for economic growth. African countries need to open their markets to trade and investment and create an environment that is attractive to businesses.

*   **Addressing climate change:** Climate change is a major threat to Africa. By addressing climate change, African countries can protect their natural resources, reduce the risk of disaster, and improve the lives of their people.

*   **Promoting peace and security:** Peace and security are essential for economic growth and development. African countries need to resolve their conflicts and build strong institutions that can maintain peace and security.

These are just a few of the things that can be done to improve economic and social conditions in Africa. By taking these steps, African countries can create a better future for their people.








A Deep Dive into Poverty, Life Expectancy and GDP (Gross Domestic Product) across African Countries

# Goal

Develop a database to Analyze and Visualize Hotel Booking Data

## Deliverable

Build a visual data story or dashboard using PowerBi to present to stakeholders

## Business Questions

*   “Is our hotel revenue growing by year?” – We have two hotel types so it will be great to 
    segment revenue by hotel type

*   “Should we increase our parking lot size” – We want to understand if there is a trend in 
    guests with personal cars

*   “What trends can we see in the data” - Focus on average daily rate and guests to explore 
    seasonality


## Project Pipeline

1.  Build a database

2.  Develo SQL Query

3.  Connect database to PowerBI

4.  Visualize

5.  Summarize findings

![](/images/pipeline.png)














Interactive KPI dashboard of Atrium Hospitality using PowerBI

Goal

Deliverable

Business Questions

Project Pipeline

Read next

Hall of Dashboard Reports

Automated Excel Reporting and Mailing with VBA

A Deep Dive into Poverty, Life Expectancy and GDP (Gross Domestic Product) across African Countries