Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
1. Data Analysis and Visualization
Gender Ratio of Applicants
- Create a pivot chart showing the distribution of applicants by gender.
Car Owner/Non-Owner Ratio
- Use a pivot chart to display the ratio of clients who own a car versus those who don’t.
House Owner/Non-Owner Ratio
- Similar to the car ownership, visualize the ratio of house owners versus non-owners.
Client Income Type
- Use a pivot chart to show the distribution of clients based on their income type.
Client’s Level of Education
- Create a pivot chart depicting the education level of the clients.
Client’s Housing Type
- Visualize the types of housing clients live in using a pivot chart.
Client’s Family Status
- Use another pivot chart to display the family status (e.g., married, single, etc.) of clients.
Client’s Occupation Type
- Create a pivot chart showing the types of occupations clients are engaged in.
2. Separate Worksheets for Data Summaries
Data Summary of All Female Clients for Income and Loan Amount
- Utilize Excel's dynamic array functions (`UNIQUE`, `SORT`, `FILTER`) to summarize income and loan amount data for female clients.
Data Summary of All Male Clients for Income and Loan Amount
- Use Excel's list functions (`DSUM`, `DAVERAGE`, etc.) to summarize income and loan amount data for male clients.
Data Summary for All Flag Columns
- Use variants of `DCOUNT` function to summarize data across flag columns (`FLAG_DOCUMENT_2`, `FLAG_DOCUMENT_3`, etc.).
3. Other Deliverables
Conditional Formatting and Freezing Panes
- Write VBA code to automate conditional formatting rules and freezing panes in the workbook.
VBA Code to Convert Selected Data into PDF
- Develop a macro in VBA to select specific data (e.g., applicant data) and convert it into a PDF document.
Check Spelling Mistakes in the Workbook
- Implement VBA code to programmatically check for spelling mistakes throughout the workbook.