Data File needed for this exercise None German Motors (GM) produces four types of family vehicles

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

2. Production Schedule (50 Points) Data File needed for this exercise: None German Motors (GM) produces four types of family vehicles: subcompact, compact, intermediate, and luxury, and two types of commercial vehicles: trucks and vans. Vendor capacities limit total production capacity to at most 1.2 million vehicles per year. Subcompacts and compacts are built together in a facility with a total annual capacity of 620,000 vehicles. Intermediate and luxury vehicles are produced in another facility with capacity of 400,000; and the truck/van facility has a capacity of 275,000. GM's marketing strategy requires that subcompacts and compacts must constitute at least half of the product mix for the four vehicles types. The Corporate Average Fuel Economy (CAFE) standards in the Energy Policy and Conservation Act require an average fleet fuel economy of at least 27 mpg.

Profit margins, market potential, and fuel efficiencies are summarized as follows:

Your task is to use Excel Solver and determine how many vehicles from each type should GM produce to maximize profit? Be sure to select keep the Solver solution and generate answer report.

(a) Open a NEW workbook and save it as Production Schedule.xlsx. Insert a Documentation worksheet and add appropriate title for the workbook in cell A1. In cell

B3 enter your name. In cell B4 enter your student number. In cell B5 enter the completion date of your assignment and in cell B6 enter a sentence to describe the purpose of the workbook.

(b) Using a new worksheet, formulate and enter the above production problem.

Note: you can find several examples on Excel Solver on Canvas under Module Lecture 4. (c) Enter Solver Parameters (Set Objective, To, By Changing Variable Cells and Subject to the Constraints)

(d) Solve the problem. Be sure to select Keep the Solver Solution and generate Answer

report. Note: If you get an internal memory error message, remove Solver as an add-in, close the workbook, open the workbook, add Solver in again, and finish using Solver.

(e) Using the same worksheet, answer the following questions.

How many vehicles from each type should GM produce to maximize profit? What is the maximum profit?

(f) Format your workbook as desired (Headings, Font, Fill color, Number format...)

 

Attachments:

Expert's Answer

325 Times Downloaded

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme