Analyzing Subsidies in Microsoft Excel

J. Wilson Mixon, Jr.
Berry College*

General Notes for reading this document and using the workbook.

Browning and Zupan (1999, Chapter 5) state how useful the budget line/indifference curve apparatus can be in examining important issues. They include this separate chapter on applications for two reasons: to derive important and sometimes counterintuitive policy implications and to give students practice in using this newly-discovered apparatus. This paper addresses the application of these tools to the analysis of subsidies. The analysis herein  follows Browning and Zupan (and some other textbooks) rather closely, but generalizes at some important points. In particular, the present analysis removes any suspicion that subsidies represent "free lunches" by explicitly modeling consumer behavior when the consumer is also the taxpayer.

Subsidies may be of two general types: variable quantity and fixed quantity. The variable quantity subsidy is one in which the government pays part of the good's price and leaves the consumer free to choose the quantity. (Restrictions may be placed on this freedom, but the present analysis ignores these limitations. In any event, incorporating limitations involves only slight changes in the models employed here.) Variable-quantity subsidies themselves can be either of two types: excise or ad valorem. An excise subsidy involves the government (taxpayers) paying a fixed number of dollars per unit of the good. With an ad valorem subsidy, the government pays a fraction of the good's cost. At the present level of abstraction, the two can be treated as equivalent, but when the "good" is hard to define, they may give different results; see Barzel (1977).

When referring the a variable-quantity subsidy, this paper refers to the subsidized good as housing. The analysis shows that consumers receiving a subsidy on each unit of housing purchased will choose more housing than otherwise (assuming housing is not a Giffen good--in our anaysis, housing is a normal good). It also shows that the consumer would be better off (in the consumer's view) if given an equal amount of cash. Finally, it shows that if the consumer is also the taxpayer, the result of the subsidy/tax is to move the consumer to a suboptimal point on the original budget line.

A fixed-quantity subsidy provides the consumer with a specified amount of the good in question. Food stamps and government-operated schools are two important examples. This paper looks at schooling policy options: laissez faire; government provision of a politically-determined quantity of schooling (a fixed-quantity subsidy); and "schooling stamps," aka vouchers (a variable-quantity excise subsidy). The analysis leads to the conclusion, stated by Browning and Zupan (1999, 121) that the second option can result in less schooling than the laissez-faire level. The present analysis differs from that of Browning and Zupan in two respects. The less important of the two involves terminology: This paper refers to "schooling" and not "education." We presume, but do not know, that more schooling leads in some proportionate fashion to more education. In terms of substance, the paper adds a third class of consumer to the story, those who opt out of the government-operated schooling system and purchase private schooling. It shows that they, too, choose less schooling as a result of the policy of government-operated schools, assuming that schooling is a normal good.

Subsidizing Housing

For many goods subsidies result in consumers' paying less than the market price. We buy health insurance and some other "fringe benefits" with pre-tax income. Those who itemize on their tax returns can deduct mortgage interest from their taxable income. Low-income households may receive housing vouchers that reduce the price they pay for housing significantly. 

Subsidy only. To see how Excel provides insights into the model, consider the utility maximizing behavior of the consumer. The consumer acts to maximize utility where it is defined as:

U = H^0.25Y^0.75.

The constraint is the budget line with the post-subsidy price. Figure 1 below shows the result with a specified set of values. The subsidy is of the ad valorem sort. One could as easily consider this as an excise tax of $0.50 per unit of housing. Given the assumption of the model that "housing" is well defined, nothing turns on the choice. In fact, however, one of the effects of the ad valorem subsidy embedded in the U. S. tax code is the encourage the building of larger and more luxurious homes. (The workbook housing_subsidy.xls contains four spreadsheets plus a "menu" sheet. To open that sheet, click here or on the highlighted file name just above.) The first spreadsheet derives the initial values shown in Figure 1. The third sheet shows the effect of replacing the variable-quantity subsidy with a lump-sum cash payment equal to the cost of the subsidy. For brevity, they are not included here.) The fourth sheet is discussed below. The MENU button takes the user back to the menu sheet; the RESET button restores values to a predefined subsidy level, and the "About reset" cell contains a more detailed discussion of the RESET button.
   

Figure 1. Utility Maximization with a 50 Per Cent Subsidy

To accomplish constrained maximization in Excel, use the "Solver" option. Selecting "Solver" from the "Tools" menu yields a dialog box like the one below. This dialog box tells Excel to maximize the value of the formula in cell C10 (the utility function) subject to the budget constraint displayed in the "Subject to the Constraints:" window (that C9 = C7 - C6*C8, or that M = H + PY). The optimization (i. e., constrained maximization) is achieved by selecting values for cells C8 and C9, or H and Y.

Figure 2. Solver Dialog Box to Maximize Utility

 

The next spreadsheet in the workbook (not shown here) demonstrates that the consumer's utility level increases if the same subsidy ($1000 given the data above) is given to the consumer in a lump sum. The final spreadsheet takes a different tack than most textbooks by showing that if the consumer is also the taxpayer (that is, if transfers are ruled out), then the consumer is forced to a suboptimal point on the original budget line. Figures 3 and 4 below illustrate this point.

Figure 3. Utility Maximization with Subsidy and Tax

Subsidy = Tax. Figure 3 shows the results of the tax/subsidy: The consumer's consumption of housing increases, and the consumer's utility level falls. Figure 4 below shows the two constraints that must be satisfied. One is the original condition that M = Y + PH, since the subsidy/tax program does not change the real price of housing. The second requires a bit more development. For the Cobb-Douglas utility function, the consumer spends a constant amount of income on housing. Specifically, for the present representation, the consumer spends one third of income on housing. Substituting this condition into the budget line implies H = M/(3P' + P). P' is P(1 - s), where s is the percentage subsidy rate, 50 percent in the current illustration. (Not all households can be subsidized, but some can. An integral part of any subsidy program, once the inevitable taxes are considered, is a set of transfers. Burns describes intercity transfers.)

Figure 4. Solver Dialog Box to Maximize Utility Subject to Tax

   

A Fixed-Quantity Subsidy: Schooling Policy Options

Friedman (1962) argues that a voucher system could be used to introduce the advantages of competition into schooling. This proposal lay largely dormant until the mid-1980s when concerns over the quality of education called for alternatives to the dominant American system of schools operated through the political process. In broad outline, policymakers have three options regarding education: laissez faire, politically operated schools, and some variant of a voucher system. Of course, the latter two are not mutually exclusive: One can envision politically-operated schools with vouchers that provide choice among these schools and that, perhaps, provide access to some privately-operated schools as well.

This paper examines the three pure options. It develops the budget sets (not simple lines, in the cases of politically-provided schooling and vouchers). It then introduces three classes of consumers/citizens and generates the following results: For consumers/citizens who, under laissez faire, choose less than the politically-determined amount of schooling, politically-operated schools and vouchers result in the same amount of schooling; and for those who choose more than the politically-determined amount under laissez faire, vouchers are the same as laissez faire, and they result in more schooling than when the level is politically determined.

This section proceeds as follows. It compares the analysis of schooling to the analysis of other fixed-quantity subsidies and shows why this application is a useful one. It then develops the representation of the budget set that results from each of the three policy options. Next, it applies the analysis to each of three classes of consumers/citizens. Finally, it shows a spreadsheet in which the user can specify the relevant parameters and use "Solver" to determine the implications. (The workbook schooling_subsidy.xls contains four spreadsheets plus a "menu" sheet. To open that sheet, click here or on the highlighted file name just above.)
 
 

Comparison to Other Fixed-Quantity Subsidies

The analysis of fixed-quantity subsidies like the food stamp and housing programs is a mainstay in intermediate economics courses. Analysis of these programs is attractive for several reasons. Most apparently, it applies economic analysis to in important policy issue. As a mechanical matter, it allows the demonstration that budget set need not be a simple straight (or even curved) line, encouraging students to think about the meaning of the budget set in a way not demanded by the usual straight budget line. Finally, it elicits discussion of the important normative question of whether paternalism is an appropriate basis for policy by pointing out that the recipient of the earmarked subsidy (often food stamps) would be "better off"—on a higher indifference curve—if given cash.

As an application, schooling policy is an attractive target for analysis. The issue is both current and immediate to students, most of whom are recent graduates of "public" schools. As a matter of mechanics, the budget line for the currently dominant delivery system differs from the simple linear budget set in an interesting way, and a voucher program replicates food stamps or housing vouchers. As with food stamps or housing vouchers, the analysis elicits the same issues regarding normative values. In addition, it makes issues of redistribution an essential part of the discussion.(1)  Furthermore, it evokes issues of government provision of services, as compared for government provision for these services (vouchers), thus introducing the question of relative costs of governmental versus private-sector production. Also, issues of why and whether government should have a role in education at all—relevant externalities, redistribution, socialization, etc.—can enliven the development.(2)

Developing the Model: Budget Sets

For the purpose of the analysis, assume that schooling can be measured in some standard unit, perhaps years per person. Also, assume that the price per unit is independent of both the number of units consumed and the method of delivery. This latter assumption may generate some discussion. Of course the model could be extended to allow for the possibility that privately-produced schooling could be delivered at a lower cost. Finally, we assume that consumers pay for the education received; that is, issues of redistribution are ignored.(3)

Given these assumptions, the budget sets are as in Figure 5. To be concrete, we assign the consumer(s) an income of $52,000 per period and schooling a price of $1000 per unit. The composite good Z has a unit price of $1. Absent any government policy, the budget line is the line with endpoints S = 52 units of schooling and Z = 52,000 units of the composite good. This is labeled as the Laissez-Faire budget line.

Figure 5. Budget "Lines" under Various Policy Options

The currently-dominant approach of politically-provided schooling results in a budget set that consists of either of two line segments. The horizontal segment marked with circles pertains up to the politically-determined schooling level. (This drawing of the line segment assumes that the consumer could choose less than the quantity supplied via the political process. If a  the politically-determined schooling level is mandated, the point at the extreme right end of the horizontal segment  is the only one on the consumer's budget "line." More generally, if a minimum level of schooling is mandated, the horizontal line segment begins at the mandated level.) To consume a larger than politically-determined level of schooling, the consume must pay the tax and opt out of the system, moving to the segment of the "After-tax budget line" that is covered with circles.

The third option, a voucher policy, results in a budget that is essentially identical to that used to analyze other fixed-quantity subsidies. This budget set is the kinked line denoted by circles up to the politically-determined schooling level and diamonds thereafter..
 
 

Applying the Model: Consumer Behavior

Next, we face three different individuals with the budget lines developed above. Under laissez faire, Consumer A prefers 4 units of schooling, less than the politically-determined level of schooling. Consumer B prefers more than the politically-determined level under laissez faire, but subsequent analysis shows that this consumer opts for the smaller, politically-determined, level. Finally, Consumer C demand much more schooling, and subsequent analysis shows that Consumer C opts out of the politically-provided school system in favor of a larger amount that that which is determined through the political process. All consumers have Cobb-Douglas utility functions.

 

Figure 6. The Three Types of Consumers to Be Analyzed(4)

Consumer A. The analysis of Consumer A is developed much as that of food stamps or housing vouchers. Absent any government policy, Consumer A would consume 4 units of schooling, as Figure 6 shows. Faced with either a voucher for 12 units of schooling or with the option to accept the 12 units or buy more than 12, Consumer A’s equilibrium is the politically-determined level. For such a consumer, the voucher option yields the same result as providing the schooling directly. Of course, this consumer is worse off in his/her own view.

Figure 7. Consumer A

To make this analysis more like standard analysis of fixed-quantity subsidies, let the consumer’s income equal $40,000. Then, absent any government policy, "After-tax Budget Line" is the budget line and the consumer chooses fewer than 4 units of schooling, assuming schooling is a normal good. This consumer, given vouchers for 12 units of schooling (at a cost to taxpayers of $12,000), uses them and chooses the full 12 units no more, spending nothing on schooling.  Finally, given $12,000, this consumer buys 4 units of education and attains a higher level of utility than if given 12 units of schooling.

Consumer B. Consumer B, absent any government schooling policy, consumes more than the politically determined quantity of education. Figure 8 depicts this consumer’s reactions to the various policy options. The analysis reveals that for this class of consumers, the effects of policy options are as follows: Vouchers and laissez faire yield identical results, while political determination of the schooling level results in less schooling and a lower level of utility for this consumer. While the utility level associated with the politically- determined schooling level is lower than with laissez faire or vouchers, it is higher than if this consumer opts out of the politically- delivered schooling system. Therefore, Consumer B accepts the politically-determined schooling level.

Figure 8. Consumer B

 

Consumer C. Consumer C is the easiest of the three to analyze. This consumer’s utility is maximized when she/he chooses to pay the tax and then buy schooling in the private sector. As with B, the voucher system yields the same result as a laissez faire policy.

Figure 9. Consumer C

The presence of consumers like C does introduce one difficulty into the analysis. Since C pays for politically-provided schooling but does not use it, then A and B have access to more schooling than they must pay for. One could adapt the model to incorporate this factor, perhaps by letting per-capita spending on schooling for those using politically-provided schooling receive more schooling than they pay for by a factor equal to the reciprocal of the ratio of the number using these schools to the number being schooled. Thus, if 90 percent (approximately the current percentage) of the schooled are in politically-provided schooling, then make payment on education for A and B equal to 10/9 of what they pay. Alternatively, if the fraction opting into private schools is small enough, ignoring this complication seems reasonable. We do so here.

A More General Representation. A final spreadsheet allows a more general approach. This worksheet allows both the laissez-faire and politically-chosen levels of schooling to vary. Setting a value for the former implies the exponent in the Cobb-Douglas utility function, as shown in Figure 10 below. The consumer depicted, like Consumer B above, opts to use the politically-provided schooling. In this case, the person's schooling level falls to one-half its laissez-faire level. The worksheet provides a note showing the relationship between the schooling level that dictates whether the consumer opts to pay taxes and use private schooling.(5)

Figure 10. A General Model

Summary, Schooling Policy

This paper applies a simple model of utility maximization, a standard of intermediate microeconomics courses, to the analysis of alternative methods of providing schooling. This illustration has numerous pedagogical attractions, especially as regards the development of the budget set available to the representative consumer(s) of schooling.

Summary and Conclusion

Spreadsheet packages like Microsoft Excel workbooks can provide insights into the models that economists use. This paper shows how subsidies can be analyzed within the framework of consumer theory. It shows that a variable-quantity subsidy such as the one provided to homeowners increases the quantity of the good subsidized and, absent income transfers, places the consumer/taxpayer at a lower utility level. With the fixed-quantity subsidy, the consumer taxpayer is also worse off (again, absent transfers). Furthermore, with this type of subsidy the quantity consumed may be less than without the subsidy.
 
 

Footnotes

1. Food stamps and housing vouchers, of course, take redistribution as given; it is their reason for being. [Return to text.]
2. West (1994) and Lott (1987) provide excellent critical discussion of the logical bases for government-provided schooling.  [Return to text.]
3. One attraction of using a spreadsheet to illustrate theory is that is provides a relatively open environment. The user may suggest alternative specifications, such as a different unit cost when the good is privately produced and incorporate that difference into the model. [Return to text.]
4. Rounding can cause lines indicating consumers' choice to not quite reach the budget line. [Return to text.]
5. Once the utility level from private schooling (after paying the requisite tax) and that from using government-provided schooling are determined, the consumer's option if also determined. Setting the two utility levels equal to each other and solving for b yields the result that the consumer will setting for the politically chosen level  (SPC) as long as it is more than the following value:

            SPC > (M/P)*B/(1 + B), where
            B = beta*(1 -beta)^{((1 -beta)/beta)},
where beta is as defined in Figure 10. [Return to text.]

References

Barzel, Yoram. 1976. "An Alternative Approach to the Analysis of Taxation,”"Journal of Political Economy. [Return to text.]

Browning, Edgar K. and Mark A Zupan. 1999. Microeconomic Theory and Applications, Sixth Edition. Reading, MS: Addison-Wesley. [Return to text.]

Burns, Scott. 2003 "Tax Break May Have Left Home,"  Dallas Morning News, 4/27/2003.  [Return to text.]

Friedman, Milton. 1962. Capitalism and Freedom. Chicago: University of Chicago Press. [Return to text.]

Lott, John R, Jr. 1987. "Why Is Education Publicly Provided? A Critical Survey." Cato Journal. [Return to text.]

West, E. G. 1994. Education and the State: A Study in Political Economy. 3rd edition. Indianapolis: Liberty Fund. [Return to text.]

---

At various points in the text a compass  appears. Clicking on the compass will bring you here, from where you can move to the sites indicated by the text.

• To the top of the document.
  
• To the beginning of the section on the housing subsidy, an example of a variable-quantity subsidy.
  
• To the section on the use of the Solver feature of Excel. See the note below on Solver.
  
• To the beginning of the section on schooling policy, an example of a fixed-quantity subsidy.
  
• To a section that compares schooling policy with other fixed-quantity subsidies, notably food stamps.
  
• To the section that applies the model to three types of consumers.
• To a more general representation than the ones developed in the preceding section.
  
• To the article's summary and conclusion.
  

A number of the worksheets in the workbook makes use of the Solver feature of Excel. If this feature is not implemented in your Excel, select "Tools/Add-Ins/Solver Add-In" to activate Solver.

The workbooks contain macros that facilitate much of the analysis. If the Excel security level is "high" then macros will be stripped from the program when it is opened. To avoid this select "Tools/Macro/Security/Medium." This will cause Excel to prompt before enabling the macros.