Advance Excel
Budget: ₹1,500 – ₹12,500 INR
Specific Requirements
The data received from Vivino wine rating IT department comprises three data files: Wine.xlsx,
WineRating.txt and Region.xml. The Wine.xlsx file records the information of wine list across the
world which detailed information about their ratings is recorded in WineRating.csv and Region.xml
records the country of each wine region.
You are asked to:
1 Import all the data from the other files into the Excel file. Write Excel formulas to fill the last
three empty columns in the Wine.xlxs file with appropriate information provided from the
other files.
2 Assess the data and correct it if necessary. You are also required to use Excel functions to
automate the correction.
3 Investigate the data distribution of wine price by drawing its histogram and boxplot and
providing your insight.
4 The company wants to collaborate with the favourite wineries to expand its market. To
support managers’ decision making, you are required to:
a) Propose a ranking with at least 2 criteria to rank the wineries for the company to
choose for their next collaboration. Provide your justification.
b) Create a pivot table to show the best five wineries based on your ranking. Provide a
filter to allow users to narrow the result to a specific country that they are
interested. Display the list of the wineries in the descending order of your proposed
criteria, i.e. the best one first. Design the pivot tables to make it comprehensive
(having meaningful titles, column headers, applying appropriate data format,
applying conditional formatting, etc.)
https://drive.google.com/drive/folders/1NPnrxQEgDzWqkMKyGpeYWHaBtDVzL_DH?usp=sharing
The data received from Vivino wine rating IT department comprises three data files: Wine.xlsx,
WineRating.txt and Region.xml. The Wine.xlsx file records the information of wine list across the
world which detailed information about their ratings is recorded in WineRating.csv and Region.xml
records the country of each wine region.
You are asked to:
1 Import all the data from the other files into the Excel file. Write Excel formulas to fill the last
three empty columns in the Wine.xlxs file with appropriate information provided from the
other files.
2 Assess the data and correct it if necessary. You are also required to use Excel functions to
automate the correction.
3 Investigate the data distribution of wine price by drawing its histogram and boxplot and
providing your insight.
4 The company wants to collaborate with the favourite wineries to expand its market. To
support managers’ decision making, you are required to:
a) Propose a ranking with at least 2 criteria to rank the wineries for the company to
choose for their next collaboration. Provide your justification.
b) Create a pivot table to show the best five wineries based on your ranking. Provide a
filter to allow users to narrow the result to a specific country that they are
interested. Display the list of the wineries in the descending order of your proposed
criteria, i.e. the best one first. Design the pivot tables to make it comprehensive
(having meaningful titles, column headers, applying appropriate data format,
applying conditional formatting, etc.)
https://drive.google.com/drive/folders/1NPnrxQEgDzWqkMKyGpeYWHaBtDVzL_DH?usp=sharing