How to solve using spreadsheet model only A to d pls help thank you

Auto Assembly Automobile Alliance, a large automobile manufacturing com- capacity of 48,000 labor-hours during the month. She also knows pany, organizes the vehicles it manufactures into three families: a that it takes six labor-hours to assemble one Family Thrillseeker family of trucks, a family of small cars, and a family of midsized and 10.5 labor-hours to assemble one Classy Cruiser. and luxury cars. One plant outside Detroit, Michigan, assembles Because the plant is simply an assembly plant, the parts two models from the family of midsized and luxury cars. The required to assemble the two models are not produced at the first model, the Family Thrillseeker, is a four-door sedan with plant. Instead, they are shipped from other plants around the vinyl seats, plastic interior, standard features, and excellent gas Michigan area to the assembly plant. For example, tires, steering mileage. It is marketed as a smart buy for middle-class families wheels, windows, seats, and doors all arrive from various sup- with tight budgets, and each Family Thrillseeker sold generates plier plants. For the next month, Rachel knows that she will only a modest profit of $3,600 for the company. The second model, be able to obtain 20,000 doors from the door supplier. A recent the Classy Cruiser, is a two-door luxury sedan with leather seats, labor strike forced the shutdown of that particular supplier plant wooden interior, custom features, and navigational capabilities. for several days, and that plant will not be able to meet its produc- It is marketed as a privilege of affluence for upper-middle-class tion schedule for the next month. Both the Family Thrillseeker families, and each Classy Cruiser sold generates a healthy profit and the Classy Cruiser use the same door parts, with four needed of $5,400 for the company. for the Family Thrillseeker and two for the Classy Cruiser. Rachel Rosencrantz, the manager of the assembly plant, is In addition, a recent company forecast of the monthly currently deciding the production schedule for the next month. demands for different automobile models suggests that the Specifically, she must decide how many Family Thrillseekers demand for the Classy Cruiser is limited to 3,500 cars. There and how many Classy Cruisers to assemble in the plant to maxi- is no limit on the demand for the Family Thrillseeker within the mize profit for the company. She knows that the plant possesses a capacity limits of the assembly plant. a. Formulate and solve a linear programming model to deter- g. Automobile Alliance has determined that dealerships are mine the number of Family Thrillseekers and the number of actually heavily discounting the price of the Family Thrill- Classy Cruisers that should be assembled. seekers to move them off the lot. Because of a profit-sharing Before she makes her final production decisions, Rachel plans agreement with its dealers, the company is not making a to explore the following questions independently, except where profit of $3,600 on the Family Thrillseeker but instead is otherwise indicated. making a profit of $2,800. Determine the number of Family b. The marketing department knows that it can pursue a targeted Thrillseekers and the number of Classy Cruisers that should $500,000 advertising campaign that will raise the demand be assembled given this new discounted profit. for the Classy Cruiser next month by 20 percent. Should the h. The company has discovered quality problems with the campaign be undertaken? Family Thrillseeker by randomly testing Thrillseekers at the c. Rachel knows that she can increase next month's plant capac- end of the assembly line. Inspectors have discovered that in ity by using overtime labor. She can increase the plant's over 60 percent of the cases, two of the four doors on a Thrill- labor-hour capacity by 25 percent. With the new assembly seeker do not seal properly. Because the percentage of defec- plant capacity, how many Family Thrillseekers and how tive Thrillseekers determined by the random testing is so high, many Classy Cruisers should be assembled? the floor foreman has decided to perform quality control tests on every Thrillseeker at the end of the line. Because of the d. Rachel knows that overtime labor does not come without an added tests, the time it takes to assemble one Family Thrill- extra cost. What is the maximum amount she should be will- seeker has increased from 6 hours to 7.5 hours. Determine ing to pay for all overtime labor beyond the cost of this labor the number of units of each model that should be assembled at regular-time rates? Express your answer as a lump sum. given the new assembly time for the Family Thrillseeker. e. Rachel explores the option of using both the targeted adver- i. The board of directors of Automobile Alliance wishes to cap- tising campaign and the overtime labor-hours. The advertis- ture a larger share of the luxury sedan market and therefore ing campaign raises the demand for the Classy Cruiser by would like to meet the full demand for Classy Cruisers. They 20 percent, and the overtime labor increases the plant's labor- hour capacity by 25 percent. How many Family Thrillseek- ask Rachel to determine by how much the profit of her assem- bly plant would decrease as compared to the profit found in ers and how many Classy Cruisers should be assembled part a. They then ask her to meet the full demand for Classy using the advertising campaign and overtime labor-hours Cruisers if the decrease in profit is not more than $2,000,000. if the profit from each Classy Cruiser sold continues to be 50 percent more than for each Family Thrillseeker sold? j. Rachel now makes her final decision by combining all the new considerations described in parts f, g, and h. What are f. Knowing that the advertising campaign costs $500,000 and the maximum usage of overtime labor-hours costs $1,600,000 her final decisions on whether to undertake the advertising beyond regular time rates, is the solution found in part e a campaign, whether to use overtime labor, the number of Family Thrillseekers to assemble, and the number of Classy wise decision compared to the solution found in part a? Cruisers to assemble