What the Spreadsheets Tell Us
What the Spreadsheets Tell Us
by Paul Bonneau ("Zxcv")
In this article I will examine different inferences that can be drawn from manipulations of the spreadsheets.
Some, who are not familiar with spreadsheets, may find this exposition a little slow-going. For them I'd just suggest skimming, and noting how the state rankings worked out in each section, along with the tentative conclusions following the rankings. The rankings are always presented with the most desirable states at the left.
I refer to two spreadsheets. The "standard" one is available on the state data page. The "big" spreadsheet is my modification of the standard spreadsheet, which I will make available to anyone who emails me. It is different in the following ways:
- It has many more rows, 80 at the time of writing. I add new rows whenever
quantifiable measures are discovered, as time permits.
- The normalization used is less unfriendly to low performers than the one
used in the standard sheet, for any given variable. For example, if one
variable had a 500 in the best state and 400 in the worst, the big spreadsheet
normalizes this to be 10 for the best state and 8 for the worst (in other
words, it is proportional). The standard spreadsheet would normalize to 10 for
the best and 0 for the worst.
- The normalization allows for intermediate values to be "best", rather than
a simple "more is better" or "less is better" rating. A few variables seem
better with intermediate values as optimum.
- There is much more potential for inadvertent "overlap" of the variables in
the big spreadsheet, due to the large number of variables there. For example, I
have a variable called "Population" and another, "Population2025". These two
are so highly correlated that they are almost the same thing. Assigning large
weights to both of them would be like assigning all that weight to one of them.
So, users must be careful when assigning weights, to avoid overloading the
highly correlated variables.
So, what sorts of things can you discover with spreadsheets?
You can get a good approximation for the "freedom culture" in the state. You can find out which states are good for personal freedom, which for economic freedom. You can get an idea which states have a government that loves to spend. You can find out how many activist opponents we will face. You can see how certain variables changed over time. You can even take a stab at predicting the FSP population that's predicted to move to the different states.
Let's try these out on the big spreadsheet, which is ideal for this purpose because of its numerous variables.
Freedom CultureTo discover what states have a general climate of freedom, we concentrate mainly on what Jason calls the CULTURE variables. We eliminate anything having to do with population, or FSP viability. We also ignore most items in the QUALITY area.
I used 3 possible weights: 1 for variables if lower importance, 2 for variables of middling importance, and 4 for variables of high importance. Variables get a 4 when they are better, more direct measures of a freedom culture. They get a 1 if they are either are indirect or less sure measures, or those that are not very important.
These variables were assigned weights of 4:
Ideology, RLCFreedomPers, RLCFreedomEc
These variables were assigned weights of 2:
Dependence, Spending, Taxes, DeathTaxes, Revenue, Debt, Prez, GunControl, Homeschooling, NEATeachers, NEAMonopoly, EdSpendingGSP, EdSpendingPupil, NTU, GovEmp%, GovEmp, RightToWork, EFI, SBSI, EFNA, LandPlanning, SmokingRegs, SeatBelts, BikeHelmets, MCHelmets, AutoInsRegs, HealthMandates, PublicHealthCare, LiquorLaws, MarijuanaLaws, MarijuanaArrests, PeoplePerCop
These variables were assigned a weight of 1:
GunOwners1, GunOwners2, AllTeachers, UnionMembers%, UnionMembers, UnionRep%, CPS, CPSAdoptedOut, HuntingRegs, HunterOrange, HunterTraining, DRCPerfRank, DRCBusVitRank, DRCDevCapRank
The definitions for these variables are found in the Appendix, and discussions about them are found via links in the spreadsheet itself. This particular weighing is stored for your use in the "Weight Vectors" page of the big spreadsheet, with the name "Culture"; if you have the big spreadsheet you may modify it as you like, of course.
I do not claim these weight assignments make perfect sense. One could obviously make many different choices than I have.
Let's see what the spreadsheet yielded:
WY ID NH SD AK ND MT VT DE ME 681 660 601 594 566 554 534 495 472 442
So, the conclusion, at least from my weighing, is that Wyoming, Idaho, New Hampshire and South Dakota are the 4 states in our list with the strongest culture of freedom in the people. This is, of course, an indirect measure, as we are assuming that the culture of the people is reflected in the laws and regulations they tolerate living under.
Personal FreedomI created another weight vector (stored in the spreadsheet weight vectors page under the name "Personal Freedom"). It is similar in concept to the culture exercise above except that the variables associated only with economic freedom were eliminated. And some others, such as the variable PrivateLand, were added, when they seemed to have something to do with personal freedom. The details of this vector may be examined by opening the spreadsheet.
Here are the results:
ID WY NH AK ND MT SD VT ME DE 1,941 1,860 1,747 1,744 1,529 1,518 1,506 1,393 1,192 1,097
With my weigh vector, Idaho rises to the top in personal freedom; South Dakota looks less friendly (it has dropped substantially from its overall freedom culture measurement). So we can see that we'd have work to do in the personal freedom area, if we picked South Dakota.
Economic FreedomI created another weight vector (strangely enough called "Economic Freedom", in the weight vectors page). Again it is like the above, except this time we are now considering only the economic side of freedom. Here is the result I get with my weight choice:
WY SD ID ND AK NH MT VT ME DE 1,609 1,448 1,446 1,307 1,294 1,233 1,136 959 956 953
While Wyoming is again in top spot for economic freedom, this is clearly South Dakota's strong suit, essentially tying in second place with Idaho. Somewhat of a disappointment, New Hampshire drops to 6th in this list. And the big surprise is Delaware, with its reputation of being a business haven, only making a poor 10th place showing. I suspect Delaware's reputation is based on its use by large corporations for registering there, not for small business and entrepreneurial advantages; and anyway, other statist trends have been noted recently, in that state.
In the above weighing I did not give QUALITY variables any weight, being concerned more with freedom per se rather than economic vitality and opportunity (which may be quite good even in very statist states, e.g. California). But I tried another one, like the above but this time factoring in a heavy weighing of QUALITY variables (called EcFreedomAndQuality in the weight vectors page):
SD WY ID ND NH AK DE MT ME VT 2,231 2,214 2,013 1,945 1,921 1,875 1,732 1,717 1,598 1,588
Government spending tendenciesI created a weight vector with only these weights: Spending=20, Debt=10, Revenue=Gov2PerCapita=5 (all but Spending are per capita measures; Spending is a per-Gross State Product measure). This was not stored anywhere. The results:
NH ID SD ND DE ME MT VT WY AK 330 325 305 284 283 260 257 256 242 176
Populous states with large Gross State Products and low spending, like New Hampshire and Idaho, do well on this measure of government spending tendencies. It does downgrade states like Wyoming and Alaska that benefit from having mineral resources and investment portfolios; such will tend to spend more than otherwise. Normally we might not downgrade states for spending this money, although the question has to be asked, what are they going to do when the minerals run out? (That's more a problem with Alaska, which has a small supply of oil.) Will they then be hooked on government spending?
I have to add that Wyoming is unique in this country in having a "no-frills" state-level government. As shown by the 2001 State Expenditure Report of the National Association of State Budget Officers (p. 19 of the .pdf file), outside of the basic spending categories of "education", Medicare, public assistance, corrections and transportation, Wyoming alone in the country spends 0% of its budget in the category "Other". The national average is 32.1% in that category, and my own state of Oregon tops the list with 48.4%!
Government taxing tendenciesI created a weight vector having only these weights: Taxes=20, DeathTaxes=NTU=5. This was not stored anywhere. The results:
AK NH WY ID SD MT ND VT ME DE 312 273 261 247 241 218 226 224 218 207
Interestingly, back in the '70's Alaska had the highest state and local taxes in the nation. For many years now, it has had the lowest in the nation. New Hampshire has also had consistently low taxes over a very long period of time.
There has been some justifiable concern on the FSP web forum that taxes are not a very reliable measure for us. Not only that, but federal taxes (not part of this measure) dominate state and local taxes. Personally I think focusing on just a few variables, as in the above tax and spending examples, is not a very good way to base one's decision on a state. It does indicate special issues or problems, however.
Our activist oppositionI created a weight vector having only these weights:
I should explain this choice. First, I have some pairs of variables like UnionMembers and UnionMembers%, that differ only in the "%". The first is the number of union members in the state, the second is the percentage of union members in the state population. The number is more important because it represents how many activists will be directly opposing us. The percentage is less important, but still matters some, because this percentage of the voting public can be assumed will vote against us.
I chose Dependence because those feeding at the trough will not want to see that trough emptied. I chose UrbArea% because large urban agglomerations are reliable sources of statist opposition. I chose PeoplePerCop because that indicates a bit about the strength of the corrections establishment in the state, which will resist cutting down the War on Drugs and the like. I chose NEAMonopoly because it places powerful tools in the hands of our opponents. The others should be self-explanatory. The results:
WY SD VT ND DE MT ID NH AK ME 814 760 731 711 572 522 521 511 490 482
The large states, Idaho, New Hampshire and Maine predictably fall to the bottom; since they are large, our opponents will be numerous there. Surprisingly, despite its socialist reputation, Vermont climbs pretty high. This is no doubt due to its small state status and lack of big cities. It probably shouldn't be that high because we will face strong opposition in dismantling all the statist measures they have enacted. I suppose that demonstrates the fallibility of spreadsheets.
Charting long range trendsI have a long term trend of some data. An example below shows one of our three comprehensive economic indices, the Economic Freedom of North America index (note the horizontal axis is not linear; Frasier Institute first started taking data for this index every 4 years, and now is doing it every year). Charting data this way, while time consuming and difficult, reveals that substantial changes happen over not very long periods of time. With FSP presence in one of these small states, our influence in these sorts of indices should be quite large.
The charts also suggest other lines of inquiry. For example, what happened in the '89-'93 time frame to harm New Hampshire's position in this index? What did they do later to fix things?
I include this only as an example of the kind of information long-term data may provide to us. It remains to chart and exploit this for numerous other variables.
Predicting FSP population in the different statesHere we get into the far out limits of what you can do with the spreadsheet. Making such a prediction is extremely problematical, in my opinion; only slightly better than pulling it out of... the air.
I have added an extra weighing column to the big spreadsheet, called FSPDrawWeight. The intention here was to weigh the spreadsheet variables not with any sense of FSP success, but simply to consider those variables that would influence people to move. For example, the QUALITY variables are weighed much higher here, and the SIZE variables not at all. The Jobs variable got far and away the highest weight (5 times higher than anything else), and this was used as a simple "more is better" variable, not an "intermediate value is better" as on the regular weighing. Then I assumed the state with the lowest draw would get 20,000 FSPers, and predicted the draw in the other states using that assumption and the result of the FSP Draw weighing, and from that the number of voters in the state per FSP activist. Here are the results of my weight choice:
State WY SD AK ND VT ID DE MT NH ME Predicted
24.6 25.0 22.3 20.8 20.0 30.5 20.4 24.2 26.2 20.3 Voters
8.7 12.7 13.1 14.0 14.6 16.0 16.1 17.0 21.6 31.9
I don't know what you can do with this, other than to say that in Maine, we'd be in serious trouble. And that Idaho is the state likely to draw the largest FSP population, a result that seems intuitively correct. This exercise was meant more as a fun spreadsheet exercise, than as something one could hang one's hat on. Actually, it is probably a better indicator of the draw we'd have with "friends", than with the members some have taken to calling our "broken glass eaters" (those who would go anywhere to be free). To estimate the draw of the latter you'd want to recalculate after having flattened or almost eliminated the QUALITY variables.
Using the full spreadsheetUp to this point we have been looking at the big spreadsheet which is useful for making inferences about different subjects, but we were not considering so much the needs of the project as a whole. Now we turn to the regular spreadsheet, available here, to consider the project itself.
Here are the weights that I came up with, along with Jason's, which are the default in the sheet:
Variable Paul's Jason's Voters 12 18.1 Finance 6 8.5 Population 6 2.0 Area 2 2.0 Coast 1 4.6 Border 1 1.0 Dependence 9 13.5 FedLand 2 5.1 Spending 7 3.9 Taxes 7 1.6 Prez 2 0.7 Gun Control 3 1.8 Homeschooling 3 1.1 Natives 1 1.5 UrbanAreas 4 1.6 UrbanClus 0 0.4 Variable Paul's Jason's NEA 1 1.9 Ideology 5 2.1 GovEmp 1 2.1 EFI 4 1.2 LandPlanning 2 1.0 SBSI 4 0.9 CPS 2 1.0 Smoking 1 1.2 SeatBelts 1 1.2 Marijuana 3 2.0 Livability 1 1.4 Crime 1 2.9 Income 1 1.0 Jobs 4 6.5 PrivLand 2 4.2 JanTemp 1 2.0
I believe the answer in the standard spreadsheet, as it currently stands, is to weigh these two variables rather low, and increase the weight of the population variables (because you will understand that larger populations will naturally give rise to larger numbers of NEA members and government employees, an undesirable outcome).
There is another difference in the Jobs variable. In the standard spreadsheet it is a simple "more is better" variable, while in the big spreadsheet I put 60,000 as the optimal number (3 times the number of FSPers, not all of whom will need jobs as some will be spouses). My reasoning was that if the jobs get much higher you have the potential for a lot of them to be filled by economic refugees who might be statists; actually I'm thinking even 60,000 was perhaps too high for an optimal number of projected jobs.
I have done a linear regression on the projected jobs vs. population, and it turns out (not surprisingly) that the more people you have in a state, the more job openings there will be in the future; so that, in effect, jobs and population are connected. The correlation is quite high. However in the standard spreadsheet Jobs is a simple "more is better" variable and Population/Voters are "less is better" variables. Some large state advocates have advised taking population variables (Population and Voters) to a weight of zero while weighing Jobs highly. This has the somewhat absurd effect, since Jobs and population are so closely connected, of penalizing Wyoming for having a small population!
The bottom line here is, leave at least some weight in the population variables (Population and Voters) and don't weigh Jobs too high. If you want to take Jobs higher, you must (to avoid a bogus result) do the same with the population variables.
My weighing generates the following results for the top 5 states:
WY AK NH DE ID 695 602 563 542 538
Just to experiment, I used my preferred weights but zeroed out the population variables:
NH WY ID AK DE 536 515 494 453 416
Again, this weighing makes no sense because it penalizes the states for having a small population. Even so it's interesting to see Wyoming is only barely bumped out of 1st place.
The result Jason got with the default weighs he has loaded in the spreadsheet is:
WY DE AK ID VT 640 573 567 552 530
Finally, getting back to the big spreadsheet, this is the top 5 result I get with my preferred weights:
WY SD ND VT NH 741 664 623 612 592
This is a useful cross-check between the two spreadsheets. The big spreadsheet is not kind to DE (and to a lesser extent, NH) because these two do not do so well with the numerous small variables added to the big spreadsheet. On the other hand, SD does do well on them, and with the added comprehensive economic index there (EFNA).
To concludeSpreadsheets are not the be-all or end-all. There are a fair number of factors that are not easily quantifiable, and thus are not found in the spreadsheets. But those factors ought not be the only thing you base your decision on. If you have a favorite state that, with a reasonable selection of weights, does poorly in the spreadsheets, you have a problem. At the very least, there is some justification that needs to be done, to keep this state as a favorite. I would say that we shouldn't rank states highly in the vote if they can't make it at least into the top 3 or 4 slots in the spreadsheets.
Some people have gotten a lot of mileage out of creating lists of "firsts" for their favorite state. This is interesting, but it unfortunately does not lend itself well to comparison with other states, and of course all the bad factors are left out. Spreadsheets allow you to organize your thinking about what is important and what is not, and to consider most of the quantifiable factors.
In the examination of such factors as economic freedom, personal freedom and others, Wyoming usually does quite well. With the weights I have chosen for running the entire spreadsheet for overall results (and these are done without regard to which state does better on what), I normally see Wyoming in first place with a commanding lead. I suspect most people will come out with a similar result.
Appendix: Variable Names
AllTeachers Number of government school teachers AutoInsRegs Lack of mandatory automobile insurance regulations** BikeHelmets 10 = No bicycle helmet law; 9 = No law except in Billings; 0 = Helmets required CPS Children removed from home by CPS CPSAdoptedOut Children adopted out of foster care after removal from their families by CPS DeathTaxes One point for not having each of the following: Inheritance Tax, Estate Tax, Generation Skipping Tax, Gift Tax Debt Total state and local debt per capita Dependence Federal expenditure to federal tax ratio DRCBusVitRank Development Report Card ranking for business vitality* DRCDevCapRank Development Report Card ranking for development capacity* DRCPerfRank Development Report Card ranking for performance* EdSpendingGSP Dollars spent per $1000 Gross State Product. A measure of teacher union influence. EdSpendingPupil Per pupil spending in public schools, adjusted for regional costs differences. A measure of teacher union influence. EFI Clemson University comprehensive economic freedom index* EFNA Economic Freedom of North America, an economic freedom index by Frazier Institute* GovEmp% % of population employed by state & local government at all levels GovEmp Number, in thousands, employed by state & local government at all levels GunControl State gun freedom** GunOwners1 Percentage of homes owning guns GunOwners2 Percentage of suicides that are gun suicides, a measure of firearms ownership HealthMandates Unfunded state mandates on health care providers** Homeschooling According to HSLDA, 10=no notification required; 7=minimal regulation; 3=moderate regulation; 0=high regulation HunterOrange Amount of hunter orange required: 10 = none, 5 = 1 garment, 0 = 2 garments or 400 sq in. HuntingRegs Lack of hunting regulations HunterTraining Hunter training course required Ideology Citizen ideologies in the states LandPlanning Lack of statewide land planning schemes LiquorLaws An index showing regulation of liquor, wine and beer** MarijuanaArrests Arrest rate per 100,000, averaged over the years 95-97. Note, arrest rate may be as much affected by population propensity to smoke as by police enthusiasm. MarijuanaLaws An index showing severity of marijuana possession laws** MCHelmets 10 = No MC helmet required; 8 = No MC helmet required for 18 or older; 0 = MC helmet required NEAMonopoly NEA/AFT monopoly bargaining power for all teachers - deduct 5 points. NEA/AFT forced dues for all teachers - deduct 5 points. NEATeachers Number of government school teachers in NEA NTU Percentage of time congress persons voted in agreement with National Taxpayers Union, averaged over several years* PeoplePerCop Ratio of the number of citizens to each LEO (Law Enforcement Officer) in the state Prez Vote for conservative & libertarian presidential candidates in 2000 PublicHealthCare Support for publicly-funded health care - tax expenditures Revenue Total state & local revenue per capita RightToWork 10=Right to work state (no forced union membership or dues), 0=not a Right to work state RLCFreedomEc 10-year index of congresspersons for economic freedom by Republican Liberty Caucus RLCFreedomPers 10-year index of congresspersons for personal freedom by Republican Liberty Caucus SBSI Small Business Survival Index, grades states on a collection of factors for entrepreneurial friendliness* SeatBelts 10 = Seatbelt not required; 5 = Seatbelt required, fine <= $10; etc. SmokingRegs Lack of smoking regulations.** Spending State and local government spending as a percentage of Gross State Product Taxes Total state and local taxes as a percentage of income - note, does not include "user fees" UnionMembers% Percentage of union affiliation of employed wage and salary workers by state, (union members only) UnionMembers Number of union members, in thousands UnionRep% Percentage of union affiliation of employed wage and salary workers by state, including all persons represented by collective bargaining contracts
* These variables are "comprehensive indices", meaning they are a collection of numerous related factors to give a more reliable overall picture, compiled professionally.
** These variables are "comprehensive indices", or at least indices containing more than one related factor. They give a more reliable overall picture. They are compiled by FSP researchers.