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...)
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
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
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
