Module 12 Assignment – Excel Tutorials

The following tutorial is an optional resource meant to assist you in navigating the databases and collecting the necessary data for your analysis.

Note on recording your responses: In the assignment you will find all the necessary fields to record your answers and the data you collect. You will also find an optional Answer Sheet Excel file to keep record of the collected data and your findings. You will find this file in the assignment’s introduction.

Step 1: Among respondents without a financial institution or mobile money account, what percentage lists “lack of trust” as a reason for not having an account?

  1. Open the “M12 – Step-by-step guide to analyze low trust in providers – databases.xlsx” file > Open the “Step 1 – Findex” sheet.
  1. Right-click on the status bar at the bottom of the Excel window > In the dialog box that appears, ensure that the checkbox for “Average” is checked.
  1. Select the header row > click the Data menu > Select “Filter”. ADD SCREENSHOT Findex 3
  1. Now that “Filter” is enabled, click on the dropdown arrow in the “Country” column header > Uncheck “Select All” > Check the box for your country.
  1. Locate the “No account because of a lack of trust in financial institutions (% without an account, age 15+)” column > Find the value and answer the question on the platform.

Step 1: Is the percentage of individuals without a financial account due to lack of trust above your income group average?

  1. In the “Step 1 – Findex” sheet, locate the “Country” column > Check “Select all.”
  2. Locate the “Income group” column > Filter by your income group.
  1. Locate the “No account because of a lack of trust in financial institutions (% without an account, age 15+)” column > Click and drag to select the entire range of cells under this column > Find the average at the bottom of the sheet.
  1. Compare your country’s value with the income group average and answer the question on the platform.

Step 1: Is the percentage of individuals without a financial account due to lack of trust above countries in your geographical region?

  1. In the “Step 1 – Findex” sheet, locate the “Country” column > Check “Select all.”
  2. Locate the “Region” column > Filter by your regional group.
  3. Locate the “No account because of a lack of trust in financial institutions (% without an account, age 15+)” column > Click and drag to select the entire range of cells under this column > Find the average at the bottom of the sheet.
  4. Compare your country’s value with the regional group average and answer the question on the platform.

Step 1: Where is your country located in the graph?

  1. In the “Step 1 – Findex” sheet, locate an empty cell outside the data range, not associated with any row or column containing data > Click on the empty cell.
  1. Click the Insert menu > Locate the charts icons > click the Scatter plot icon > select “Scatter.”
  1. Click on the chart area > Click the Chart Design menu > click “Select data.”
  1. Make sure the “Show data in hidden rows and columns” checkbox from the “Hidden and Empty Cells” options is unmarked.
  1. Click on the + sign to add a series > In the “Series name” field, type “Trust/Account.”
  1. Click the “X values” field > Select all cells under the “No account because of a lack of trust in financial institutions (% without an account, age 15+)” column.
  1. Click the “Y values” field > Select all cells under the “Account (% age 15+)” column. Press “OK.”
  1. Right click on one of the data points > select “Add data label.”
  1. Right click on one of the data points > select “Format data label.”
  1. Under “Label contains” check “Value from cells” > Select all cells with values under the “Country” column > Press OK.
  1. Uncheck “Y value.”
  1. Analyze the output and answer the question on the platform.

Step 1: What is your country’s score for trust in online privacy?

  1. Open the “Step 1 – EII scores” sheet.
  1. Locate the “Country” column > Filter by your country.
  2. Locate the “Trust in online privacy” column > Find the value and answer the question on the platform.

Step 1: Is your country’s score below your income group average in the Trust in online privacy indicator?

  1. Open the “Step 1 – Online privacy av.” sheet.
  1. Locate the “All countries” column > Locate the “Income group” cell > Find the value.
  1. Compare your country’s value with the income group average and answer the question on the platform.

Step 1: Is your country’s score below your regional group average?

  1. In the “Step 1 – Online privacy av.” sheet, locate the “All countries” column > Locate the “Region” cell > Find the value.
  2. Compare your country’s value with the regional group average and answer the question on the platform.

Step 1: What is your country’s score for e-commerce safety indicator?

  1. Open the “Step 1 – EII scores” sheet.
  2. Locate the “Country” column > Filter by your country.
  3. Locate the “E-commerce safety” column > Find the value and answer the question on the platform.

Step 1: Is your country’s score below your income group average in the E-commerce safety indicator?

  1. Open the “Step 1 – E-commerce safety av.” sheet.
  1. Locate the “All countries” column > Locate the “Income group” cell > Find the value.
  2. Compare your country’s value with the income group average and answer the question on the platform.

Step 1: Is your country’s score below your regional group average in the E-commerce safety indicator?

  1. In the “Step 1 – E-commerce safety av.” sheet, locate the “All countries” column > Locate the “Region” cell > Find the value.
  2. Compare your country’s value with the income group average and answer the question on the platform.

Step 2a: Is the score less than 80?

  1. Open the “Step 2 – GSMA MMRI” sheet.
  1. Locate the “Country” column > Filter by your country.
  2. Locate the “Year” column > Filter by the most recent year.
  1. Locate the “Consumer Protection” column > Find the value and answer the question on the platform.

Step 2a: Is your country’s score below your regional group average in the Consumer protection indicator?

  1. In the “Step 2 – GSMA MMRI” sheet, locate the “Country” column > Check “Select all.”
  2. Locate the “Region” column > Filter by your regional group.
  3. Locate the “Year” column > Filter by your country’s most recent year.
  4. Locate the “Consumer protection” column > Click and drag to select the entire range of cells under the “Consumer protection” column > Find the average at the bottom of the sheet.
  5. Compare your country’s value with the regional group average and answer the question on the platform.

Step 2a: What is the weakest variable for this indicator?

  1. In the “Step 2 – GSMA MMRI” sheet.
  2. Locate the “Country” column > Filter by your country.
  3. Locate the “Year” column > Filter by the most recent year.Locate the “Safeguarding of funds” column > Find the value and answer the question on the platform.
  1. Repeat these steps for the “Consumer protection rules” and “Deposit insurance” variables.

Step 2a: What is your country’s strongest variable for this indicator?

  1. In the “Step 2 – GSMA MMRI” sheet.
  2. Locate the “Country” column > Filter by your country.
  3. Locate the “Year” column > Filter by the most recent year.
  4. Locate the “Safeguarding of funds” column > Find the value and answer the question on the platform.
  5. Repeat these steps for the “Consumer protection rules” and “Deposit insurance” variables.

Step 2a: Is there a consumer protection law that explicitly refers to financial services?

  1. Open the “Step 2 – FICP – FCP” sheet.
  1. Locate the “Country” column > Filter by your country.
  2. Locate the “F1.1 What type(s) of legal framework exists in your country pertaining to financial consumer protection?” cell > Locate the “General consumer protection law with explicit reference to financial services” cell > Find the value and answer the question on the platform.

Step 2a: Which of the following activities are conducted by the Financial Consumer Protection Agency/Unit in your country?

  1. In the “Step 2 – FICP – FCP” sheet, locate the “Country” column > Filter by your country.
  2. Locate the “F4. What are the main activities of the separate unit(s) or team(s) with respect to financial consumer protection for FSPs?” cell > Locate the activities below this cell > Find the values and answer the question on the platform.

Step 2a: What actions can your agency take to enforce consumer protection laws and regulations?

  1. In the “Step 2 – FICP – FCP” sheet, locate the “Country” column > Filter by your country.
  2. Locate the “F5. What actions can your agency take to enforce consumer protection laws and regulations?” cell > Locate the actions below this cell > Find the values and answer the question on the platform.

Step 2a: Does your agency collect aggregated consumer complaints data from supervised financial service providers or alternative dispute resolution entities (i.e., not directly from consumers)?

  1. In the “Step 2 – FICP – FCP” sheet, locate the “Country” column > Filter by your country.
  2. Locate the “F6a. Does your agency collect aggregated consumer complaints data from supervised financial service providers or alternative dispute resolution entities (i.e. not directly from consumers)” cell > Find the value and answer the question on the platform.

Step 2a: Is there a requirement in law or regulation that customers’ e-money funds be separated from the funds of the e-money issuer?

  1. Open the “Step 2 – FICP – E-money” sheet.
  1. Locate the “Country” column > Filter by your country.
  2. Locate the “E.9 Is there a requirement in law or regulation that customers’ e-money funds be separated from the funds of the e-money issuer?” cell > Find the values and answer the question on the platform.