15. The city of Abingdon is determining its tax rate structure for the coming year. The

city needs to generate $6 million in tax revenue via taxes of property, sales, prepared

food, and utilities. The following table summarizes how much tax revenue would be

generated from each segment of the population by the 1% increase in each tax category.

(For instance, a 2% tax on prepared food would generate $240,000 in tax revenue

from upper-income residents.)

Revenues (in $1,000s) per 1% Tax Rate

Income Group Sales Property Food Utility

Low $200 $ 600 $ 50 $ 80

Middle $250 $ 800 $100 $100

Upper $400 $1200 $120 $120

City commissioners have specified that the tax rate for each revenue category must

be between 1% and 3%, and the tax rate on prepared food cannot exceed half the

sales tax rate. Ideally, the commissioners have a goal of making up the $6 million tax

budget with $1.5 million from low-income residents, $2.1 million from middleincome

residents, and $2.4 million from high-income residents. If that is not possible,

the commissioners would like a solution that minimizes the maximum percentage

deviation from these tax revenue goals for each income group.

a. Create a spreadsheet model for this problem.

b. What is the optimal solution?