Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Using Excel to solve this Problem 5 (6 points) Embassy Motorcycles (EM) manufacturers two lightweight motorcycles designed for easy handling and safety. The EZ-Rider model

Using Excel to solve this

image text in transcribed

Problem 5 (6 points) Embassy Motorcycles (EM) manufacturers two lightweight motorcycles designed for easy handling and safety. The EZ-Rider model has a new engine and a low profile that make it easy to balance. The Lady-Sport model is slightly larger, uses a more traditional engine, and is specifically designed to appeal to women riders. Each EZ-Rider engine requires 6 hours of manufacturing time and each Lady-Sport engine requires 3 hours of manufacturing time. The Des Moines plant has 2100 hours of engine manufacturing time available for the next production period. Embassy's motorcycle frame supplier can supply as many EZ-Rider frames as needed. However, the Lady-Sport frame is more complex and the supplier can only provide up to 280 Lady-Sport frames for the next production period. Final assembly and testing requires 2 hours for each EZRider model and 2.5 hours for each Lady-Sport model. A maximum of 1000 hours of assembly and testing time are available for the next production period. The company's accounting department projects a profit contribution of \$2400 for each EZ-Rider produced and \$1800 for each Lady-Sport produced. a. Formulate a linear programming model that can be used to determine the number of units of each model that should be produced in order to maximize the total contribution to profit. b. Using Excel, solve this model and generate the sensitivity report. Which constraints are binding and why? c. Using the sensitivity report, add 2 more columns about the range of optimality and the range of feasibility. d. If the EM decides to increase the price for each EZ-Rider to make a profit of \$3500. Find the new optimal number of unit for each model in order to maximize profit. e. Keep the profit contribution of each model unchanged, using the shadow price, find the new (maximum) total profit if the maximum available time for assembly and testing can be increased by 10%. f. Find the new optimal number of unit for each model in order to maximize profit in part e. Problem 5 (6 points) Embassy Motorcycles (EM) manufacturers two lightweight motorcycles designed for easy handling and safety. The EZ-Rider model has a new engine and a low profile that make it easy to balance. The Lady-Sport model is slightly larger, uses a more traditional engine, and is specifically designed to appeal to women riders. Each EZ-Rider engine requires 6 hours of manufacturing time and each Lady-Sport engine requires 3 hours of manufacturing time. The Des Moines plant has 2100 hours of engine manufacturing time available for the next production period. Embassy's motorcycle frame supplier can supply as many EZ-Rider frames as needed. However, the Lady-Sport frame is more complex and the supplier can only provide up to 280 Lady-Sport frames for the next production period. Final assembly and testing requires 2 hours for each EZRider model and 2.5 hours for each Lady-Sport model. A maximum of 1000 hours of assembly and testing time are available for the next production period. The company's accounting department projects a profit contribution of \$2400 for each EZ-Rider produced and \$1800 for each Lady-Sport produced. a. Formulate a linear programming model that can be used to determine the number of units of each model that should be produced in order to maximize the total contribution to profit. b. Using Excel, solve this model and generate the sensitivity report. Which constraints are binding and why? c. Using the sensitivity report, add 2 more columns about the range of optimality and the range of feasibility. d. If the EM decides to increase the price for each EZ-Rider to make a profit of \$3500. Find the new optimal number of unit for each model in order to maximize profit. e. Keep the profit contribution of each model unchanged, using the shadow price, find the new (maximum) total profit if the maximum available time for assembly and testing can be increased by 10%. f. Find the new optimal number of unit for each model in order to maximize profit in part e

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Auditing A Quality System For The Defense Industry

Authors: Charles B. Robinson

1st Edition

0873890787, 978-0873890786

More Books

Students also viewed these Accounting questions