Excel Project: Fixing Cryptic Data Using Data Concatenation

You work at a non-profit that seeks to help people start businesses in third world countries so those people can lift themselves out of poverty. Your organization has found more success working in countries that have policies that make it easier to start a business. You have been asked to explore which countries your organization should consider entering. To do this, go to the student download page at http://www.pearsonglobaleditions.com and download the file labeled “P6-5StartingABusiness.xlsx”. This dataset provides information about the relative ease of starting a business and doing business in countries around the world. The Excel spreadsheet contains three years of data in multiple sheets. The data is not easy to understand as it is spread across multiple sheets and the “Final Report” sheet currently only shows cryptic data values for the Country and Year. The data for this problem comes from The World Bank: Doing Business dataset: see http://www.doingbusiness. org/en/data for more information.

REQUIRED

Do the following

a. Fill out the “Final Report” sheet so that it looks like the image below. You should use formulas to prepare the final report. Leave the formulas in your solution file for evaluation. As a hint, this problem can be solved using a combination of the VLOOKUP and CONCATENATE functions. Add columns to the sheets to complete the task with these formulas as needed.

b. Fill out the “Final Report – Advanced” sheet so that it looks like the image below. You should use formulas to prepare the final report. Leave the formulas in your solution file for evaluation. For this problem, you are only allowed to use combinations of the following functions: INDEX, MATCH, and CONCATENATE. You will NOT need to add columns to complete the task.

c. Compare and contrast the formulas you used to answer part a and part b. Research both types of formulas and discuss which formulas are better for use in practice.*