Data Analytics Project – Codebasics Resume Project Challenge 7

About Challenge

Challenge: Analyse Growth and Present Insights to the Telangana

The challenge is about analysing the Telangana State’s growth in various sectors and presenting insights to the stakeholders.

Stakeholders:

About Project

We are provided with 2 dimension tables and 3 fact tables.

All the data used in the project is real data available on the Government of Telangana’s Open Data portal.
https://data.telangana.gov.in/

Dimension tables:

• dim_date – This table contains the dates at the monthly level. Please be aware that the fiscal year of Telangana spans from April to March.
• dim_districts – The table contains information about districts.

Fact Tables:

• fact_stamps – The table provides data on the revenue generated from document registrations and E-stamp challan payments aggregated at the district and monthly level.
• fact_transport – The table provides information about the individual vehicle sales data from the RTA(Regional Transport Authority) of the state of Telangana categorized by fuel type, vehicle class, seating capacity, and other general categories aggregated at the district and monthly level.
• fact_TS_ipass – The TS-iPASS dataset in Telangana comprises data concerning units or businesses established within the state under the “Industrial Project Approval and Self-Certification System” (iPASS). This government initiative aims to foster industrial growth and investment by streamlining project approvals and enabling self-certification for businesses.

More details about the challenge are here:
Codebasics Resume Project Challenge

Objective:

• Explore Stamp Registration, Transportation and Ts-Ipass Datasets. Understand their attributes, categories and time periods.
• Analyze trends and patterns within each department.
• Identify growth opportunities and areas needing attention.
• Find correlations among these departments and report the overall growth of the state through insights and relevant visuals such as shape maps.

---

Telangana

Telangana is one of the fastest-growing states in India

Telangana’s growth from 2017 – 2022

• 128.3% (5th highest in India)
• Telangana
• Total 63 SEZ (Special Economic Zones)
• $21Bn investment attracted since 2014
• 1.5 Lakh Acres Land
• 3621 MW Solar Capacity

Resource: https://invest.telangana.gov.in/

Live Dashboard

Project

Problem Statements & Solutions

Stamp Registration

1. How does the revenue generated from document registration vary across districts in Telangana? List down the top 5 districts that showed the highest document registration revenue growth between FY 2019 and 2022.

2. How does the revenue generated from document registration compare to the revenue generated from e-stamp challans across districts? List down the top 5 districts where e-stamps revenue contributes significantly more to the revenue than the documents in FY 2022.

3. Is there any alteration of the e-Stamp challan count and document registration count pattern since the implementation of the e-Stamp challan? If so, what suggestions would you propose to the government?

4. Categorize districts into three segments based on their stamp registration revenue generation during the fiscal year 2021 to 2022.

I divided the districts into 3 categories. (From FY 2021 To FY 2022)

1. High – Top 10 districts with high revenue.
2. Medium – 10 District with average revenue.
3. Low – The rest of the districts with low Revenue.

Transportation

5. Investigate whether there is any correlation between vehicle sales and specific months or seasons in different districts. Are there any months or seasons that consistently show higher or lower sales rate, and if yes, what could be the driving factors? (Consider Fuel-Type category only)

6. How does the distribution of vehicles vary by vehicle class (MotorCycle, MotorCar, AutoRickshaw, Agriculture) across different districts? Are there any districts with a predominant preference for a specific vehicle class? Consider FY 2022 for analysis.

Districts with a predominant preference for a specific vehicle class in FY 2022. (based on the vehicle class like Motor Cycle, Motor Car, Auto Rickshaw, and Agriculture)

7. List down the top 3 and bottom 3 districts that have shown the highest and lowest vehicle sales growth during FY 2022 compared to FY 2021. (Consider and compare categories: Petrol, Diesel and Electric)

Ts-Ipass (Telangana State Industrial Project Approval and Self Certification System)

8. List down the top 5 sectors that have witnessed the most significant investments in FY 2022.

9. List down the top 3 districts that have attracted the most significant sector investments during FY 2019 to 2022. What factors could have led to the substantial investments in these particular districts?

One reason can be the positive news associated with these districts. News like Foxconn is committed to investing more than $300M in one of these districts.
Source: TOI
There are similar positive news associated with districts which attract more investments from different sectors.

10. Is there any relationship between district investments, vehicle sales and stamps revenue within the same district between FY 2021 and 2022?

11. Are there any particular sectors that have shown substantial investment in multiple districts between FY 2021 and 2022?

Sectors that have shown substantial investment in multiple districts between FY 2021 and 2022.

1. Plastic & Rubber
2. Pharmaceuticals & Chemicals

12. Can we identify any seasonal patterns or cyclicality in the investment trends for specific sectors? Do certain sectors experience higher investments during particular months?

There are certain sectors that experience higher investments during particular months.

February – Real Estate, Industrial Parks & IT Buildings
Reason – Financial year ending in March – The financial year in India ends in March and every year to save taxes, businesses and individuals invest money in multiple assets. Property is one such asset which requires huge investment and hence more tax can be saved.

December – Fertilizers, Organic and inorganic, Pesticides, Insecticides and Other Related
Reason – Farming Season Starts in November – Every year the main farming season starts in November as it is a winter or cold season which is suitable for farming. This brings more business or investment opportunities.

More Insights

1. What are the top 5 districts to buy commercial properties in Telangana? Justify your answer.

Rangareddy, Medchal Malkajgiri & Sangareddy –
It surrounds Hyderabad and is well connected with it. It capitalises on the benefits provided to the capital like good infrastructure and transportation.

Hyderabad –
Hyderabad is a capital and it is always the first priority for new businesses to start from here and capitalize the existing incentive scheme by local Govt.

Warangal –
Warangal is another rapidly growing district. There is also an approved Airport plan for Warangal. This will be a good opportunity to acquire land near the proposed Airport which will later increase the land value.
Source: Warangal Airport

2. What significant policies or initiatives were put into effect to enhance economic growth, investments, and employment in Telangana by the current government? Can we quantify the impact of these policies using available data?

The policies or initiatives that were put into effect to enhance economic growth, investments, and employment in Telangana by the current government are:

• Single Clearance Window – Businesses do not need to wait in most cases can start doing business from day 1.
• The Right to Clearances policy under which Entrepreneurs can know the reason for the delay in their application and penalise the responsible officers for the delay.
• Various incentives like reimbursement of stamp duties, Power Tariff, industrial land acquisition duties, employee training incentives etc.
• Meet Or Greet Policy – The Telangana government is ready to offer opportunities that will either match or supersede any package offered anywhere in the world for industries.
• New Approved Airports – There are 3 new approved airports in the Telangana state and they will improve the transportation for businesses and help in tourism and economy.

List Of Incentives
More Information About Incentives

3. Provide the top 5 Insights & 5 recommendations to the Telangana government for sustained growth in the next 5 years based on your analysis.

Top 5 Insights

1. More than 50% of the revenue is generated by 3 districts.
2. More than 50% of the investment is done only in 3 districts that too are neighbours to each other.
3. Even after COVID-19, the revenue increased by 19.42% and investment also grew by 142.79% which means the government’s policies are good. Currently, diversification is required.
4. Out of 7M vehicles sold, EVs are just 103k.
5. October month has an average of 700k vehicle sales from 2019 to 2022.

Top 5 Suggestions

1. Invest more in Agriculture.
2. Focus on solar projects.
3. Diversify investments in the rest of the districts.
4. Investment in Bhadradri Kothagudem is good but the revenue needs to be improved. This is an important district as this is close to the seaport and helps in improving exports by reducing travel time.
5. The government of India is currently focusing on bringing in EV makers and EV parts manufacturers, so this can be the chance to bring the most emerging sector to the state. This will boost employment.

---

Tools used in building the project:

MS Excel
PowerBI
Canva – For PPT

Thank You!

Please share your valuable feedback with me in the comments.