pls use excel

Case#2 Module2 Instructions: Answer the questions with corresponding computation/solution to validate your position. You may use the template modeling in Wyndor Glass. Case 2-1 Auto Assembly Automobile Alliance, a large automobile manufacturing com- capacity of 48.000 labor-bours during the month. She also knows puny, organizes the vehicles it manufactures into three families that it takes six labor-hours to assemble one Family Thrillseeker family of tracks, a family of small cars, and a family of midsized and 10.5 labor-bors to assemiale one Clay Cruiser and luxury cars. On: plant outside Detroit, Michigan, assemble Because the plant is simply assembly plant, the parts two models from the family of machine luxury cars. The required to assemble the two models are not produced at the first model the Family Thrillseeker, is a far door dan with plant. Instead, they are shipped from other plants around the vinyl seats, plastic interior, standard features, and excellent gas Michigan za 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 $2.0 for the company. The second model be able to obtain 21.XIO doces from the one supplier. A recent the Classy Cruiser, is a two-door luxury sedan with leather seats, labc strike forced the shutdown of thuit particular supplier plu waxen interior custom features and musical capabilities for everal day, and to plant will be able to meet Il in marketed as a privilege of afflowme for upper-modelle-class tion schedule for the next month. Both the Family Thrillseeker families, and each Classy Cruiser sold generales 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 decading the production schedule for the next month demand for different le medelis su pests 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 mise 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- mine the number of Family Thrillers and the number of Classy Cruises that should be assembled. Before she makes her final production decisives, Rachel plans to explore the following questions independently, except where ocherwise indicated The marketing department know that it can pursue a targeted $500.000 avertising campaign that will raise the demand for the Classy Cruiser next month by 20 percent. Should the campaign he undertaken! c. Rachel knows that she can increase next month's plant capac ity by using overtime labor. She can increase the plant's Inher-hour capacity by 25 percent. With the new assembly plant capacity, bow my Family Thrillseekers and low many Classy Cruisers should be assembled? d Rachel knows that overtime laboe does not come without an extra cost. Wist is the maximum amount she should be will ing to pay for all overtime labor beyond the cost of this labor a regular-time mutes! Express your answer as a lump sum. Rachel explores the option of using both the targeted adver- tising campaign and the overtime labor hours. The advertis ing campaign raises the demand for the Classy Cruiser by 20 percent, and the overtime labor increases the plane's labor bour capacity by 25 percent. How many Family Thrillsek and how many Clanwy Cruisers should be assembled using the advertising campaign and overtime labor hours if the profit from each Classy Cruiser sold continues to he 50 percent more than reach Family Thriller old f. Knowing that the advertising campaign costs $500,000 and the maximum usage of overtime labor-hours costs $1,600,000 beyond regular time nates, is the solution found in partea wise decision compared to the solution found in parta? 3. Automobile Alliance has determined that dealerships are actually leavily discounting the price of the Family Thrill seekers to move them off the bot. Because of a profit sharing agreement with its dealers, the company is not making a profit of $3,600 on the Family Thrillseeker but instead is making a profit of 52,800. Determine the number of Family Thrillseekers and the number of Classy Cruisers that should be assembled given this new discounted profit The company has discovered quality problems with the Family Thrillecker by randomly testing Thrillseekers at the end of the assembly line. Inspectors have discovered that is over 60 percent of the cases, two of the four doors on a Thrill seeker do tal properly became the percentage of deles tive Thrillseekers determined by the random testing is so high. the floor foreman has decided to perform quality control tests on every Thrillseeker at the end of the line. Because of the added tests, the time it takes to assemble one Family Thrill seeker has increased from 6 hours to 75 hours. Determine the number of units of each model that should be assemble given the new assembly time for the Family Thrillseeker. i The board of directors of Automobile Alliance wishes to cap- Tue a larger share of the luxury sedan market and therefore would like to meet the full den and for Classy Cruisers. They ask: Rachel to determine by how much the profit of her assem Ny plant would decrease as compared to the profit Found in part a. They then ask her to meet the full demand for Classy Cruisers if the decrease in profit is not more than $2,000,000 Rachel now makes her final decision by combining all the new considerations described in parts and A. What are her final decisions on whether to undertake the advertising campaign, whether to se overtime labor, the number of Family Thrillseekers to assemble, and the number of Classy Cruisers assemble! 11 MGT 1107 Management Science Case#2 Module2 Instructions: Answer the questions with corresponding computation/solution to validate your position. You may use the template modeling in Wyndor Glass. Case 2-1 Auto Assembly Automobile Alliance, a large automobile manufacturing com- capacity of 48.000 labor-bours during the month. She also knows puny, organizes the vehicles it manufactures into three families that it takes six labor-hours to assemble one Family Thrillseeker family of tracks, a family of small cars, and a family of midsized and 10.5 labor-bors to assemiale one Clay Cruiser and luxury cars. On: plant outside Detroit, Michigan, assemble Because the plant is simply assembly plant, the parts two models from the family of machine luxury cars. The required to assemble the two models are not produced at the first model the Family Thrillseeker, is a far door dan with plant. Instead, they are shipped from other plants around the vinyl seats, plastic interior, standard features, and excellent gas Michigan za 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 $2.0 for the company. The second model be able to obtain 21.XIO doces from the one supplier. A recent the Classy Cruiser, is a two-door luxury sedan with leather seats, labc strike forced the shutdown of thuit particular supplier plu waxen interior custom features and musical capabilities for everal day, and to plant will be able to meet Il in marketed as a privilege of afflowme for upper-modelle-class tion schedule for the next month. Both the Family Thrillseeker families, and each Classy Cruiser sold generales 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 decading the production schedule for the next month demand for different le medelis su pests 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 mise 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- mine the number of Family Thrillers and the number of Classy Cruises that should be assembled. Before she makes her final production decisives, Rachel plans to explore the following questions independently, except where ocherwise indicated The marketing department know that it can pursue a targeted $500.000 avertising campaign that will raise the demand for the Classy Cruiser next month by 20 percent. Should the campaign he undertaken! c. Rachel knows that she can increase next month's plant capac ity by using overtime labor. She can increase the plant's Inher-hour capacity by 25 percent. With the new assembly plant capacity, bow my Family Thrillseekers and low many Classy Cruisers should be assembled? d Rachel knows that overtime laboe does not come without an extra cost. Wist is the maximum amount she should be will ing to pay for all overtime labor beyond the cost of this labor a regular-time mutes! Express your answer as a lump sum. Rachel explores the option of using both the targeted adver- tising campaign and the overtime labor hours. The advertis ing campaign raises the demand for the Classy Cruiser by 20 percent, and the overtime labor increases the plane's labor bour capacity by 25 percent. How many Family Thrillsek and how many Clanwy Cruisers should be assembled using the advertising campaign and overtime labor hours if the profit from each Classy Cruiser sold continues to he 50 percent more than reach Family Thriller old f. Knowing that the advertising campaign costs $500,000 and the maximum usage of overtime labor-hours costs $1,600,000 beyond regular time nates, is the solution found in partea wise decision compared to the solution found in parta? 3. Automobile Alliance has determined that dealerships are actually leavily discounting the price of the Family Thrill seekers to move them off the bot. Because of a profit sharing agreement with its dealers, the company is not making a profit of $3,600 on the Family Thrillseeker but instead is making a profit of 52,800. Determine the number of Family Thrillseekers and the number of Classy Cruisers that should be assembled given this new discounted profit The company has discovered quality problems with the Family Thrillecker by randomly testing Thrillseekers at the end of the assembly line. Inspectors have discovered that is over 60 percent of the cases, two of the four doors on a Thrill seeker do tal properly became the percentage of deles tive Thrillseekers determined by the random testing is so high. the floor foreman has decided to perform quality control tests on every Thrillseeker at the end of the line. Because of the added tests, the time it takes to assemble one Family Thrill seeker has increased from 6 hours to 75 hours. Determine the number of units of each model that should be assemble given the new assembly time for the Family Thrillseeker. i The board of directors of Automobile Alliance wishes to cap- Tue a larger share of the luxury sedan market and therefore would like to meet the full den and for Classy Cruisers. They ask: Rachel to determine by how much the profit of her assem Ny plant would decrease as compared to the profit Found in part a. They then ask her to meet the full demand for Classy Cruisers if the decrease in profit is not more than $2,000,000 Rachel now makes her final decision by combining all the new considerations described in parts and A. What are her final decisions on whether to undertake the advertising campaign, whether to se overtime labor, the number of Family Thrillseekers to assemble, and the number of Classy Cruisers assemble! 11 MGT 1107 Management Science