step by step solution please

The city council of Aberdeen must determine the tax policy for the city for the coming year. Four types of taxes are used to raise money: Property tax Sales tax (a surcharge on the state sales tax) Entertainment tax Utility tax (on city-owned utilities) The city consists of three groups of people: low income, middle income, and high income. The amount of revenue (in thousands of dollars) raised from each group by setting a particular tax at a 1 percent level is given in the following table. (For example, a 3 percent sales tax will raise $1.2 million from low-income people.) Thousands of Dollars Collected per 1% Tax Rate Income Group Property Tax Sales Tax Entertainment Tax Utility Tax Low-income 400 Middle-income 350 100 120 High-income 1,200 250 FO 800 80 The city council has decided that the tax policy must satisfy the following restrictions The tax burden on middle-income people cannot exceed $2.5 million. The tax burden on high-income people cannot exceed $2.3 million. The total revenue raised must exceed the current level of $6 million. The sales tax must be between 1 percent and 3 percent. Given these restrictions, the city council has set the following three goals (listed in order of priority): Goal 1: Limit the tax burden on low-income people to no more than $2 million. Goal 2: Set the property tax rate at no less than 1 percent. If their tax burden becomes too high, 20 percent of the low-income people, 20 percent of the middle-income people, and 40 percent of the high-income people may consider moving. This will start to happen if the total tax burden of this subset of the population exceeds $1.5 million. Goal 3 is thus to limit the total tax burden on this group of people to no more than $1.5 million a. Use preemptive goal programming to determine how the various tax rates should be set. Use weighted goal programming to determine how the various tax rates should be set when using the following penalty weights: 1 per $1,000 in excess of goal 1; 90 per 1 percent short of goal 2; and 1 per $1,000 in excess of goal 3. (Solve using a spreadsheet model) The city council of Aberdeen must determine the tax policy for the city for the coming year. Four types of taxes are used to raise money: Property tax Sales tax (a surcharge on the state sales tax) Entertainment tax Utility tax (on city-owned utilities) The city consists of three groups of people: low income, middle income, and high income. The amount of revenue (in thousands of dollars) raised from each group by setting a particular tax at a 1 percent level is given in the following table. (For example, a 3 percent sales tax will raise $1.2 million from low-income people.) Thousands of Dollars Collected per 1% Tax Rate Income Group Property Tax Sales Tax Entertainment Tax Utility Tax Low-income 400 Middle-income 350 100 120 High-income 1,200 250 FO 800 80 The city council has decided that the tax policy must satisfy the following restrictions The tax burden on middle-income people cannot exceed $2.5 million. The tax burden on high-income people cannot exceed $2.3 million. The total revenue raised must exceed the current level of $6 million. The sales tax must be between 1 percent and 3 percent. Given these restrictions, the city council has set the following three goals (listed in order of priority): Goal 1: Limit the tax burden on low-income people to no more than $2 million. Goal 2: Set the property tax rate at no less than 1 percent. If their tax burden becomes too high, 20 percent of the low-income people, 20 percent of the middle-income people, and 40 percent of the high-income people may consider moving. This will start to happen if the total tax burden of this subset of the population exceeds $1.5 million. Goal 3 is thus to limit the total tax burden on this group of people to no more than $1.5 million a. Use preemptive goal programming to determine how the various tax rates should be set. Use weighted goal programming to determine how the various tax rates should be set when using the following penalty weights: 1 per $1,000 in excess of goal 1; 90 per 1 percent short of goal 2; and 1 per $1,000 in excess of goal 3. (Solve using a spreadsheet model)