Finding Global Economic Opportunity

World Bank Challenge


Companies often expand their businesses into new markets across the globe. The future of world economic development depends on the continued progress of emerging economies in Africa, Asia, and South America. Nations such as Brazil are moving from regional economic powerhouse to a global force. These countries represent important new opportunities for business around the world, but when and where to invest remains a complex decision.

Develop a visualization (or visualization tool) that contains useful information for companies wanting to set up in developing economies, and suggests which countries certain businesses may prioritize.

Potentially relevant information for individual countries may include:

  • The factors that impact setting up and operating businesses
  • Market and potential labor force demographics
  • The existing tax and trade landscape



The Doing Business Report provide 10 indicators for each country. In addition to detailed data, this visualization shows the relative rank of a country by indicator (with the highest ranked country at the top) to offer a quick perspective of relative ease of doing business in that country:

Click image to see full size

From page 61 of the Doing Business in East Africa Community Report.

Creating Smarter Travel Policies

Merck Challenge


Visualize the impact and effectiveness of Merck’s travel policy regarding booking flights. Using Merck’s travel data and by collecting airline data, explore if the “lowest price” policy is truly the best one.

Merck’s Travel Policy stipulates that employees:

  • Book travel at least 14 days in advance
  • Accept the lowest fare for a flight regardless of restrictions

Merck would like to know if the current policy is the most effective option regarding:

  • Travel cost
  • Productivity, with regard to the administration of the policy and its impact on employees
  • Employee happiness and frustration with the travel experience

Every airline has different algorithms that determine how many tickets will be sold at a given price and how prices change as the departure date approaches. Some airlines drastically increase ticket prices as the departure date nears while others cut prices. Airlines also have unique policies regarding surcharges for ticket changes and/or cancellations. Merck collects data on how many times a traveler makes changes to their flights and how many non-refundable/non-transferable tickets are lost each year.

Does booking at least 14 days in advance reliably provide travelers with low-cost flight options? Are there destinations, times of year, days of the week, etc., where booking a different number of days in advance provides sufficient options for travelers? Is a low-cost option even the best option? How does this policy affect their work and personal lives?


Merck provides two data sets that cover flight information for all Merck travelers from January 2012 – June 2013:

    1. By Trip (Ticket Level Report)- In this data set each trip has one row that includes information such as trip dates, the traveler’s airport itinerary, airline, and price details.
      Get the trip data…
    2. By Flight (Origin/Destination Report) – In this data set each flight has one row that includes information on flight date, originating airport, destination, airline, price details and a unique trip identifier code. A single business trip will occupy multiple rows, so long as it involved multiple destinations, and/or was round trip. To link all segments of a trip together you will need to use the unique trip identifier code.
      Get the flight data…

You’ll also need the data definition, which explains what each column means in each data set.
Get the data definition…

Note: The international trip data does not require the Exec/employee field, so the data in that field may be inconsistent with the data definition. The US-based trips follow the data definition.

Some information overlaps in these files, but there are some variables unique to each file. You will also need to assemble external data such as:

  1. Ticket Change Surcharges – For the airlines Merck travelers use, collect data on the surcharges a traveler may experience if they change, transfer, or cancel their ticket.
  2. Optimal Booking Date – Considering origins/destinations and time of year, collect data on when airlines increase (or decrease) their fares as the departure date approaches.


This is an example of another important business question for Merck – can we detect evidence of bad behavior and patterns of abuse by analyzing individual employee expense reports submitted over time. The screenshot is a heatmap that shows the metrics used to detect fraud. Red blocks indicates an increase in “exceptions” (travel and expense policy violations) for a business unit. The panel on the right shows the trend for that department by month, and the panel at the bottom shows each individual exception for that department.

Some of the variables that generate exceptions include:

  • Meals Over Limit – Total value and number of meal expenses that exceeded the per deim limit
  • Lagged Submission – Count of expenses submitted by an employee over 30 days after the receipt date
  • Non-AMEX – Sum and percent of total expenses that were not placed on a corporate card
  • Duplicative Expenses – Sum of expenses where multiple transactions are identical
  • Round Expenses – Sum and number of non-corporate card expenses that end in a 0 or 5
  • Category Mismatch – Sum and number of expenses incorrectly categorized as “Miscellaneous”

Click the image to see full size


Moneyballing Economic Development

Philly Challenge


Philadelphia has a strong employment base in higher education and healthcare‐related industries. However, other sectors such as manufacturing continue to lose jobs. The task for government is to support workforce development and business assistance in industry sectors where Philadelphia has a competitive advantage.

Specific considerations that impact local development include:

  • Philadelphia’s concentration of talent in specific industries
  • “Hot” industries that are primed for growth
  • City policies, demographics, and geography

Visualize opportunities for economic development in the City of Philadelphia. The city wants to be able to focus its business attraction and retention and workforce development efforts in industries that will remain competitive in the future.

There are several things which have been useful in identifying economic opportunities:

  • Sectors in which Philadelphia has a competitive advantage relative to the region, state, or nation
  • Emerging sectors that are growing at a faster rate locally than in the region, state, or nation
  • Subsectors that are driving growth over time within a particular sector


The Bureau of Labor Statistics provides data about employment and businesses at the County level using NAICS (North American Industry Classification System) codes. You can learn more about NAICS, and get their definitions at the US Census Site.

Useful comparisons:

  • U.S. Total
  • Philadelphia-Camden-Wilmington, PA-NJ-DE-MD MSA
  • Pennsylvania — Statewide
  • U.S. Metropolitan Statistical Areas (Combined)


Bubble-Chart to Display Four Distinct Variables that Can Compare Philadelphia with the Country, the State, and the Region

A “location quotient” measures how specialized a local economy is in a particular sector. It is calculated by dividing the share of employment in a particular sector in the local economy by the share in that sector in the national economy. The local economy is specialized in that sector if that number is greater than one. It is the y-axis of the depicted graph.

A “shift-term” measures the competitiveness of that sector, or the local rate of change relative to the national rate of change. It is the x-axis of the depicted graph. Thus, a sector with a positive “Shift Term”, which would lie on the rightward quadrants of the graph, would be growing at a faster rate, or shrinking at a slower rate, than that sector nationally. The formula for calculating the shift-term can be found below:

(eit+n/eit) – (Eit+n/Eit) where:

  • ei = total employment of a particular sector in the local economy
  • Ei = total employment of a particular sector in the national economy
  • t = year one
  • t+n = final year


The size of the bubble represents size of local employment in a particular sector in the final year.

The color tells us if the sector is growing or shrinking (in raw numbers and not relative to the national economy). Green would represent sectors that have grown by more than 5%, yellow for stable sectors that have had employment changes between -5% and 5%, and red for sectors that have declined by more than 5%.

An analysis of private business sectors on the 2-digit level using this approach, a time-series of 2004-2011, and comparing Philadelphia with the nation.

Click image to see full size


The Challenges