Problem

In studying financial markets, it is helpful to identify periods when the stock markets move in a certain fashion.  Take the Nasdaq composite index, for example. Suppose we download its monthly data for the last twenty year.  We can compute its monthly changes during each month.  If the combined change of the current month and its previous two months (i.e. three-month total) exceed 300 points, then we call the market is “upbeat”.  If the three-month total drops more than 300 points, then we call the market is “downbeat”.  Identify those upbeat and downbeat months.

Problem:

In a small company with six employees, everyone’s phone extension is stored in a table, but everyone’s job function is in another table.  Combine this two table together using VLOOKUP.

Problem:

Use VLOOKUP to visualize the monthly changes of Nasdaq index over the past twenty years, which range widely between -700 to more than 700.

Problem:

As a stock analyst, Ava is following the stock of Google (GOOG).  She has in front of her the weekly price history for the stock.  She wants to find out when the stock experienced the largest price jump.

Problem:

A travel guidebook is updating its restaurant ratings in a town, which has 10 restaurants. It has collected numeric data, on a 0-100 scale on food quality, service quality, and dining environment of each of the dining places. Moreover, the weights for each of the dimensions are 40%, 30%, and 30%.  In addition, some restaurants have explicit agreed to be included in the guidebook.  Set up a spreadsheet to calculate their weighted scores and count, among those who have opted in the book, how many restaurants have a combined score of 80 or more.

Problem:

A marketing firm has sent out a survey to potential customers to gauge the purchase intention of a product., which is scored between 0 (no interest) to 100 (definitely buy).  All copies of the surveys are tracked with a unique ID.  As the table below indicates, some of the surveys are not returned and are marked as “n/a”. Use COUNT and COUNTA to compare the number of surveys received against the total numbers recorded.  Then use COUNTIF to calculate how many customers gave out scores higher than 80.

Problem:

Do the stocks of Google (GOOG) and McDonald’s (MCD)  tend to move in the same direction?

Problem:

In launching a new hand-held device, a company has collected estimated market demand information from a number of potential markets (see table in Excel file; all numbers in thousands).  The company decides to enter each market according to their estimated demand size. Use Excel to compute the ranking of each market in terms of demand.

Problem:

In the previous example analyzing market demand, the company also want to divide the 20 markets into four tiers, according to the quartiles in market demand.  Calculate the four quartiles of market demand.