Build an Excel Model to model the problem and answer the following questions (provide amodel template of
Fantastic news! We've Found the answer you've been seeking!
Question:
Build an Excel Model to model the problem and answer the following questions (provide amodel template of each question).
(a) Use goal programming proposed in the article to formulate this model
(b) From your proposed model solution, stated which goals can be satisfied?
(c) Write a brief essay to discuss in details your conclusions/rationales regarding the goals you are not able to meet.
Transcribed Image Text:
This problem is based on the following research article that you must read "Multi-objective Linear programming Methodology for Public Sector Tax Planning," by James Chrisman, Timothy Fry, Gary Reeves, Holly Lewis, and Robert Weinstein, Interfaces Vol. 19, No. 5, (1989) pp. 13 - 30. Suppose that you have been hired by the city council of Peoria, Illinois to help them meet their overall tax goals. Suppose the city government came up with the following four goals listed below in order of descending of priority: 1. Limit the tax burden on Lower Income (LI) people to 1.75 billion 2. Keep the property tax rate at 1%. 3. Minimize the "flight to the suburbs" by keeping the tax burden on Middle Income (MI) people less than $2.5 billion and keeping the tax burden on High Income (HI) people to less than 2.25 billion. 4. Try to eliminate the food and drug sales tax if possible. The city currently levies five types of taxes: (a) property taxes (where p is the tax rate). (b) sales tax on general items except on food and drug and durable goods (where s is the general tax rate), (c) sales tax on food and drugs), (d) sales tax on durable goods (d is the sales tax on durable goods), and (e) gasoline tax (g is the gasoline tax rate). Relevant information on revenue generated by a 1% tax is provided in the table seen below Income Level Gasoline Low (LI) 120 Middle (MI) High (Hi) . . . Property Sales 400 300 1500 450 1200 150 Food & Drug 200 Assume that 10% of the LI people will move out of the city into the suburbs if their tax burden exceeds 1.75 billion, 20% of MI people will move out of the city into the suburbs if their tax burden exceeds 2.5 billion, and 30% of HI people will move out of the city into the suburbs if their tax burden exceeds 1.25 billion. You must work within the following "hard" constraints: 125 80 Durable 50 30 15 LIN 80 60 The sales tax must be between 1% and 3%, as indicated in the accompanying research article. The total revenue raised must exceed the current level of $6.0 billion. The tax burden on the HI people can't exceed $1.5 billion The tax burden on the Mi people can't exceed $3,0 billion Build an Excel Model to model the problem and answer the following questions (provide a model template of each question). (e) Use goal programming proposed in the article to formulate this model (hint you can learn from and modify the model of the article to answer this question) (b) From your proposed model solution, stated which goals can be satisfied? This problem is based on the following research article that you must read "Multi-objective Linear programming Methodology for Public Sector Tax Planning," by James Chrisman, Timothy Fry, Gary Reeves, Holly Lewis, and Robert Weinstein, Interfaces Vol. 19, No. 5, (1989) pp. 13 - 30. Suppose that you have been hired by the city council of Peoria, Illinois to help them meet their overall tax goals. Suppose the city government came up with the following four goals listed below in order of descending of priority: 1. Limit the tax burden on Lower Income (LI) people to 1.75 billion 2. Keep the property tax rate at 1%. 3. Minimize the "flight to the suburbs" by keeping the tax burden on Middle Income (MI) people less than $2.5 billion and keeping the tax burden on High Income (HI) people to less than 2.25 billion. 4. Try to eliminate the food and drug sales tax if possible. The city currently levies five types of taxes: (a) property taxes (where p is the tax rate). (b) sales tax on general items except on food and drug and durable goods (where s is the general tax rate), (c) sales tax on food and drugs), (d) sales tax on durable goods (d is the sales tax on durable goods), and (e) gasoline tax (g is the gasoline tax rate). Relevant information on revenue generated by a 1% tax is provided in the table seen below Income Level Gasoline Low (LI) 120 Middle (MI) High (Hi) . . . Property Sales 400 300 1500 450 1200 150 Food & Drug 200 Assume that 10% of the LI people will move out of the city into the suburbs if their tax burden exceeds 1.75 billion, 20% of MI people will move out of the city into the suburbs if their tax burden exceeds 2.5 billion, and 30% of HI people will move out of the city into the suburbs if their tax burden exceeds 1.25 billion. You must work within the following "hard" constraints: 125 80 Durable 50 30 15 LIN 80 60 The sales tax must be between 1% and 3%, as indicated in the accompanying research article. The total revenue raised must exceed the current level of $6.0 billion. The tax burden on the HI people can't exceed $1.5 billion The tax burden on the Mi people can't exceed $3,0 billion Build an Excel Model to model the problem and answer the following questions (provide a model template of each question). (e) Use goal programming proposed in the article to formulate this model (hint you can learn from and modify the model of the article to answer this question) (b) From your proposed model solution, stated which goals can be satisfied?
Expert Answer:
Answer rating: 100% (QA)
Im unable to directly assist with creating an Excel model as it involves complex mathematical modeli... View the full answer
Related Book For
Accounting What the Numbers Mean
ISBN: 978-0078025297
10th edition
Authors: David H. Marshall, Wayne W. McManus, Daniel F. Viele
Posted Date:
Students also viewed these business writing questions
-
Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during...
-
You need to Read the discussion "Lynne Scholz" and give response: No company ever plans on having to deal with a public scandal. Unfortunately for Volkswagen, the actions of a few individuals at a...
-
Prepare a set of profile leveling notes for the data listed and show the page check. All data is given in feet. The elevation of BM A is 1364.58, and the elevation of BM B is 1349.26. Rod readings...
-
How have their discoveries and philosophies shaped our current system?
-
Maynard Corporation began the year with cash of $135,000 and land that cost $25,000. During the year, Maynard earned service revenue of $260,000 and had the following expenses: salaries, $185,000;...
-
In 2004, Jui-Chen Lin, a citizen of China, entered into an agreement with Robert Chiu and Charles Cobb, citizens of the United States, to form an LLC to acquire and operate a fast-food restaurant in...
-
You drive on it, walk on it, wear it, and even brush your teeth with it. What is it? Limestone. Its a versatile natural resource found in asphalt highways, concrete sidewalks, cosmetics and...
-
A point P is equidistant from R(-2, 4) and S(6, -4) and its x-coordinate is twice its y-coordinate. (i) Find the coordinates of P. (ii) Hence, determine whether P, R and S are collinear, showing your...
-
From the trial balance of Gabriella Leitz, Attorney-at-Law, given in Figure 2.7, prepare (a) An income statement for the month of January, (b) A statement of owners equity for the month ended January...
-
Bumi Minda Sdn Bhd (BMSB) closes its account on 31 December every year. It had obtained an overdraft facility of RM400,000 from Maybank for use in its business in December 2020. BMSB bought 1,000...
-
Should there be a single set of criminal laws that apply to all states and the federal government?
-
The Relationship Between Risk and Rates of Return Problem 8-16 CAPM and portfolio return You have been managing a $5 million portfolio that has a beta of 1.15 and a required rate of return of 10%....
-
How do I execute code in Python? Is there a certain script or program for it? I am having trouble figuring out how to work with it and check data to see if it works. For example, PowerShell has a box...
-
Accounting Information system Process map create a process map for performing a task of Accounting. Identify the steps that are needed to perform the task which will help anyone to perform the task...
-
What is the difference between Criminal and Civil law? Provide a real-world example of a dispute that would be classified as civil law and one that could be classified as criminal law. Please list...
-
Draw all constitutional isomers of molecular formula C7H6B12 that are aromatic!
-
Show that every group G with identity e and such that x * x = e for all x G is abelian.
-
Gandolfi Construction Co. purchased a used CAT 336DL earth mover at a cost of $325,000 in January 2013. The companys estimated useful life of this heavy equipment is 10 years, and the estimated...
-
Western Manufacturing produces a single product. The original budget for April was based on expected production of 14,000 units; actual production for April was 13,300 units. The original budget and...
-
Kleener Co. acquired a new delivery truck at the beginning of its current fiscal year. The truck cost $52,000 and has an estimated useful life of four years and an estimated salvage value of $8,000....
-
SpeedBurn Ltd manufactures DVD burners and is considering expanding production. A distributor has asked the company to produce a special order of 3000 DVD burners. The burners will be sold using a...
-
A to Z Pty Ltd has provided the following planned perunit cost and sales data for the year ended 30 June 2020. Fixed factory overhead costs are $1 056 000 per year, and the annual fixed selling and...
-
Aussie Sporting Company Pty Ltd produces two types of sporting balls: basketballs and footballs. During the year ended 30 June 2019 it sold the balls in the proportion of three basketballs for every...
Study smarter with the SolutionInn App