Repositories of this project

In this project, the objective is to analyze from the dataset sample OF MICROSOFT FINANCIAL STATEMENTS 2019 - 2022

# DIGITAL MEDIA SPENDS DATA

## RESOURCES OF THE DATASETS USED IN THIS PROJECT

### <li><strong> (contains all information about the financial statements of Microsoft)</strong> -  <a href="https\://www\.macrotrends.net/stocks/charts/MSFT/microsoft/income-statement">Source</a><br>

##DATA MODELING

<iframe title="Finalcial analysis Microsoft" width="800" height="486" src="https://app.powerbi.com/view?r=eyJrIjoiYzFhOTdjMTYtMDlkNi00MzRkLWE1ZWYtZTI5ZWRlZDIwYTE1IiwidCI6ImU5OTQwNzJiLTUyM2UtNGJmZS04NmUyLTQ0MmM1ZTEwYjI0NCIsImMiOjR9" frameborder="0" allowFullScreen="true"></iframe>

##INFERENCES AND CONCLUSION

*   Revenue has been increasing in the last 4 years constantly, at an accelerated pace.

*   Gross profit has been increasing in the last 4 years but in the last 2 increased the same amount, pointing to an increased cost of goods.

*   Net profit has been increased, but in the last year decelerated the pace increased the amount

*   taxes have doubled since 2019, but the net profit hasn't, meaning as well an increase in the cost of goods and operative expenses.


With this project, I´ll go into the database of adventure works from Microsoft and dive into sales, customers, and product data.

#### Get the data

I get the latest data from Microsoft, then start working with SQL server management and uploaded the files to Power BI

#### Data that I'll use

I start the exploratory queries to find what type of data I have

```
-- Cleansed DIM_Date Table --
--Calendar
SELECT
[DateKey],
[FullDateAlternateKey] AS Date,
[EnglishDayNameOfWeek] AS Day,
[EnglishMonthName] AS Month,
Left([EnglishMonthName], 3) AS MonthShort,[MonthNumberOfYear] AS MonthNo,
[CalendarQuarter] AS Quarter,
[CalendarYear] AS Year
FROM
[AdventureWorksDW2019].[dbo].[DimDate]
WHERE
CalendarYear >= 2019
```

```
--- Customers
```

```
SELECT
c.customerkey AS CustomerKey,
c.firstname AS [First Name],
c.lastname AS [Last Name],
c.firstname + ' ' + lastname AS [Full Name],
CASE c.gender WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END AS Gender,
c.datefirstpurchase AS DateFirstPurchase,
g.city AS [Customer City] -- Joined in Customer City from Geography Table
FROM
[AdventureWorksDW2019].[dbo].[DimCustomer] as c
LEFT JOIN AdventureWorksDW2019.dbo.dimgeography AS g ON g.geographykey = c.geographykey
ORDER BY
CustomerKey ASC
```

```
-- Products
```

```
SELECT
p.[ProductKey],
p.[ProductAlternateKey] AS ProductItemCode,
p.[EnglishProductName] AS [Product Name],
ps.EnglishProductSubcategoryName AS [Sub Category],
pc.EnglishProductCategoryName AS [Product Category],
p.[Color] AS [Product Color],
p.[Size] AS [Product Size],
p.[ProductLine] AS [Product Line],
p.[ModelName] AS [Product Model Name],
p.[EnglishDescription] AS [Product Description],
ISNULL (p.Status, 'Outdated') AS [Product Status]
FROM
[AdventureWorksDW2019].[dbo].[DimProduct] as p
LEFT JOIN AdventureWorksDW2019.dbo.DimProductSubcategory AS ps ON ps.ProductSubcategoryKey = p.ProductSubcategoryKey
LEFT JOIN AdventureWorksDW2019.dbo.DimProductCategory AS pc ON ps.ProductCategoryKey = pc.ProductCategoryKey
order by
p.ProductKey asc
```

```
--Intertnet Sales
```

```
SELECT
[ProductKey],
[OrderDateKey],
[DueDateKey],
[ShipDateKey],
[CustomerKey],
[SalesOrderNumber],
[SalesAmount]
FROM
[AdventureWorksDW2019].[dbo].[FactInternetSales]
WHERE
LEFT (OrderDateKey, 4) >= YEAR(GETDATE()) -3
ORDER BY
OrderDateKey ASC
```

<iframe title="Report Section" width="800" height="486" src="https://app.powerbi.com/view?r=eyJrIjoiZDM4MmViM2ItMTM4MS00YmEzLWI4N2EtZjc3YmQ4YzEwNjM3IiwidCI6ImU5OTQwNzJiLTUyM2UtNGJmZS04NmUyLTQ0MmM1ZTEwYjI0NCIsImMiOjR9" frameborder="0" allowFullScreen="true"></iframe>

## INFERENCES AND CONCLUSION

*   The sales went up 2.715 M more than expected over the first 3 years
*   Around 96% of the sales were bikes
*   The month of June is when the sales sky up
*   The best customer is Jordan Turner with around 16000
*   The bike with more sales is the Mountain-200 Black, 42
*   The majority of sales are in the west of the states, Uk, german, France, and Australia

\##RECOMMENDATIONS

*Increase sales of accessories and clothing, since after a year of introducing these two segments it had a share of sales of around 5%


SALES FROM ADVENTURE WORKS DATABASE

In this project, the objective is to analyze from the dataset sample HR ANALYTICS

# HR ANALYTICS

## RESOURCES OF THE DATASETS USED IN THIS PROJECT

The given dataset was taken from the dataset in Kaggle Datasets.

### <li><strong> (contains all information about the digital media data)</strong> -  <a href="https\://www\.kaggle.com/datasets/vjchoudhary7/hr-analytics-case-study?select=general_data.csv">Source</a><br>

### <li> <strong>hr_analytics.csv-  </strong> -  <a href="https\://www\.kaggle.com/datasets/vjchoudhary7/hr-analytics-case-study/download?datasetVersionNumber=1">Download</a>

## DATA MODELING

<iframe title="Report Section" width="800" height="486" src="https://app.powerbi.com/view?r=eyJrIjoiZjJhMDNlNGMtNmZlOS00ZTVlLWI4MGMtYThjNzU4YWQ1OTA4IiwidCI6ImU5OTQwNzJiLTUyM2UtNGJmZS04NmUyLTQ0MmM1ZTEwYjI0NCIsImMiOjR9" frameborder="0" allowFullScreen="true"></iframe>

## INFERENCES AND CONCLUSION

*   The company has 4410 employees and they cost around 287 M per month, they range in age from 18 to 60 years old, and with a monthly payment range from 10,000 to 200,000 dollars.

*   60% of the employees are men, 85% of employees have a performance rating of 3 out of 4, and 15% of 4 out of 4, despite this the job involvement is low at 30% of employees.

*   65% of employees belong to research and development, 30% to sales, and 5%to hr, despite this, the role with most employees are sales executives.

*   The work balance is good for around 70% of employees, job satisfaction is good for 60% of the employees, and environment satisfaction is 60% as well, the last 2 metrics could improve


DIGITAL MEDIA SPENDS DATA

RESOURCES OF THE DATASETS USED IN THIS PROJECT

(contains all information about the financial statements of Microsoft) - Source

DATA MODELING

INFERENCES AND CONCLUSION

Repositories of this project