Finance-Analytics-using-SQL-and-EXCEL
Finance Analytics using SQL and EXCEL Portfolio
This repository/portfolio is for a project which was created for a computer hardware producing company. To help the company, better its yearly performance by getting enough insights from its database to make quick, smart data-informed decisions.
The situation in the project is that there is a computer hardware-producing organization, and we have its finance-related database. The database consists of details about the product, customers, cost of products, all the rebate offers, sales information with date of purchase, sold quantity, etc.
The task involves SQL queries to answer ad-hoc requests for which the business needs insights. The task also focuses on improving the performance of large queries using different measures. Apart from that, it also expects certain insights in the form of charts and tables.
Report-generating actions were taken using SQL for retrieving various details per business requirement. The tables were extracted in the form of comma-separated-values(CSV) files and for visualization of these insights, EXCEL charts were used. User-defined functions, stored procedures, etc were taken advantage of so that the same SQL queries can be used as a reusable asset for any past/future financial years.
Finally, a lot of the conclusions were made after the insights were drawn, some of them are listed below such as:
1- Amazon has the highest global market share percentage, it is the biggest customer/asset for our organization.
2- Nova pvt. ltd, Notebillig, etc have the lowest market share, and there is a need to improve the business strategy for such customers.
3- India is the best-selling country with the largest percentage of product sales.
4- South American countries like Chile, Brazil, and Columbia have very little business done by our organization. So, a better and more personalized marketing strategy would be necessary to attract these markets.
5- Region wise net sales percentage was drawn out of the data for APAC, EU, LATAM, and NA regions. Also, top n and bottom n products in these regions were extracted to know customer behavior better.
Below are some of the tasks involved in the project, snapshots of the queries and the generated reports:-
Task1
- The product owner want to have a report of individual product sales (aggregated on a monthly basis at the product level) for its customer Croma India for FY 2021.The
report must have these details:- Month of Purchase, Product name, Variant, Sold quantity, Gross Price per item, Gross Price Total. Assumption: Financial Year starts on 01/Sep.
Before writing the query to retrieve information, we first wrote a user defined function called ‘get_fiscal_year’ which takes any date as input and give out ‘financial year’ for that particular date as per the assumed financial year start date.
Now, below is the query to retrieve the required information:
It gives us the following report:
Task2
- Give reports to the product owner about top n products, top n customers and top n markets.
Below are the queries and their generated reports simultaneously:-
Challenge#1
While writing query to retrieve information, we encountered that the queries are taking too much time to calculate and output the results, so we needed performance improvement.
As we’re going to do lot of mathematical calculations ,joins, etc tasks it is going to get too much time to execute the query on the data, and one of the most problem was that we were calculating ‘fiscal year’ for each and every date in ‘fact_sales_monthly’ which is a repetitive process and is taking lot of time, so what we did is we just added one column named ‘fiscal_year’ using the date_add()formula and it gives fiscal year for every date and in fututure we’re going to use that fiscal_year column whenever needed.
It enhanced the performance quite much as shown below:-
After the performance improvments and added column, the below queries were used to generate reports:-
Task3
- Give a chart of top 10 markets by their net sales % to the product owner.
Below are the queries and their generated reports simultaneously:-
Task4
- Give a region wise % net sales report broken down by customers in that respective region for FY 2021.
Below are the queries and their generated reports simultaneously:-
It gave us the report which we exported and saved as a excel and further went on to create a Pie Chart for each region as shown below:-
Challenge#2
Also created stored procedure called ‘get_%_net_sales_of_customers_per_region’ as a reusable asset for any combination of inputs. Below is the code and working snapshots of the stored procedure:-
Task5
- Give a report which give top n products in each division of the product category by their quantity sold for FY 2021.
Below are the queries and their generated reports simultaneously:-
Task6
- Give a report that showcases top 2 markets in every region by their gross sales for FY 2021
Below are the queries and their generated reports simultaneously:-
If you reached till the end, really Appreciate you for your time! 😄: