Purpose of the Lab:
This lab introduces you to chain-level market area analysis. In this sort of work, the objective is to analyze demographics and trends across a variety of market areas, rather than try to evaluate single locations or markets (as you've been doing in your group projects). Again, we'll be working with the data lent me by my former student. A secondary purpose of the lab is to get you familiar with reading attachments directly into Excel.
The problem
In this case, imagine you are working for a company that does, oh, let's say, telemarketing. They want to set up shop in areas where they'll have an abundant supply of cheap part-time labor AND a large enough market to sell some sort of widgets by telephone (meet threshold, to use Christaller's idea). These are somewhat contradictory goals, as you might imagine. Cheap labor does not create a big consumer market.
So, you need to operationalize "abundant" and "cheap." You need to identify a pool of potential workers who will take cheap part-time jobs. You also need to identify market potential to ensure there's a density of demand for widgets.
Let's identify the density of market potential as per capita income times population.
Let's operationalize abundant, cheap labor as the likely product of a high rate of unemployment or, conversely, a low rate of employment.
Who would likeliest be looking for part-time, low-paying jobs? Young, college-age adults would come to mind.
So, you need to create a crude index (number made up by weighting a variety of factors), which is directly related to population and per capita income, inversely related to employment rate of working-age adults, directly related to the proportion of young adults in the population, and somehow related to the difference between local per capita incomes and average per capita incomes (such that incomes below average are positively related to the index and incomes above average are negatively related to it). Got all that? Oh, okay, I'll list it all, painstakingly, below.
Getting the Data
I will e-mail my former student's database to your ecst accounts. The data are in a big Excel worksheet attachment. Do NOT open this message in the Pine mail program, or you will have a big pain reading the attachment. Instead, get to a PC computer with both Netscape and Excel. Then, open your ecst mail account in Netscape. To configure Netscape for reading your ecst mail, you specify outgoing mail as smtp.ecst.csuchico.edu and incoming mail as a pop3 type with the name pop.ecst.csuchico.edu. When you find my message (entitled 216 spreadsheet), click on it and the message will come up. The spreadsheet attachment will be named "216MSA.xls." Click on it and open it. Excel will fire up and read the file. Then, it's a good idea to save it as a:\216USMSA.xls on your own diskette... ....
This file is a spreadsheet of 328 Metropolitan Statistical Areas (MSAs, or urban markets). The columns to look for are PERCAPI97 (per capita income for 1997), POP97 (total population for 1997), a pile of columns with such names as P0TO597 (population aged 0 through 5 in 1997), and EMPLOY97 (employment in 1997, or numbers of people with jobs).
You need to insert a column somewhere and in it create a formula multiplying PERCAPI97 by POP97 to get an estimate of the potential demand density. Let's call this new column "TOTLINC." Copy that formula down the rest of the column. At the bottom of your new column, sum the data in that column. At the bottom of the POP97 column, also sum that column (POP97). Now divide the sum of TOTLINC by the sum of POP97 to get the average per capita income for the 328 MSAs. Save your file about now....
You need to index TOTLINC (make it range to a maximum of 1). Find the biggest TOTLINC. In a new column, "TOTINCIND," divide each TOTLINC by that maximum value. Now, all TOTLINCs are expressed in values ranging from 0 to +1, so that you can combine it with all your other factors in common units.
You want to index income, so that it's in units comparable to the other factors you're putting in your model. We want this to range up to a maximum value of 1. So, find the highest income. Now, create another column, called INCINDEX. In it, divide each value in PERCAPI97 by the biggest value.
You need to insert yet another column somewhere. In this one, you'll be creating a new variable by subtracting the local MSA per capita income from the average per capita income you calculated at the bottom of your spreadsheet somewhere (bottom of TOTLINC?). Average per capita income minus local per capita income is the right order for this formula. You'll wind up with a column sporting both negative values (for those MSAs with incomes above the average) and positive values (for those MSAs with incomes below the average). Let's call that column "INCDIFF," for the difference between local and average per capita incomes. The values will be in the thousands of dollars. Save that file when you've finished this column.
Now, in order to create an index, you'll probably want to take the differences in local incomes from overall average income and convert it from thousands of dollars into some sort of rate. Find the biggest absolute difference from the average per capita income. Now, create yet another column, called DIFFINDEX, and divide each value in INCDIFF by that biggest absolute difference. This will create an index variable that can potentially range from -1 (highest per capita income to +1 (for the lowest income).
Another little headache will be to sum columns for adults aged 18 through 64 in another column you insert for the purpose. This column represents working age adults, so call it WRKGADLT. Now, create another column for employment rate. Call it EMPLRATE. In it, divide EMPLOY97 by the sum total of working age adults. You'll get an employment rate ranging from 0.00 to 1.00. Save your work.....
Your last pain will be to create a column, in which you sum columns for young adults, oh, somewhere from 18 to 24 or thereabouts (I believe there are two relevant columsn). Call this new columsn YNGADLTS. Now, in another new column, divide the sum of young adults by POP97 to get the proportion of potential telemarketers in the population. Alternatively, you could index that variable, too. Save that file.
Create an Index
Now, go off and on a sheet of paper come up with some formula that creates a number that satisfies these conditions:
When you're reasonable happy with your formula (at first, it will probably just be an English sentence, "this times that..."), create a column for it next to the MSA name column. Now, go over the spreadsheet finding all those relevant columns and noting their letters. In your index column, write your formula in terms like "=J2*Q2*X2 etc., etc." Then, copy it down the column.
The final column of numbers will look different for each of your spreadsheets, depending how how you build your total index (sums or multiplications): It could be some index ranging from -1 to +1 or maybe some other weird range of numbers. They just need to vary in the manners listed above.
Based on your index, come up with a list for the top 25 MSAs you would recommend your company invade with its telemarketing boiler rooms. It will be interesting to see the degrees of overlap or conflict between each of your recommendations. Turn in this list and the formula you used to create your index. Make sure I can read this formula without access to your spreadsheet (i.e., in terms of variable names, not column letters).