Show the work manually on the page provide in addition to the excel spreadsheet
Quiz #6 (Due July 9, 2021). Name: __________________________ Date: _____________ This online quiz is worth 100 points and due by the end of Friday July 9, 2021 (11:59 p.m.). Please use Excel and write all of requested answers on this quiz (see the specific problem for details). You may either type your work directly onto this Word file, or print the quiz write out your answers by hand, and then scan and upload the PDF document. Please be sure to upload all files to the Canvas site (i.e., this quiz file and your Excel file). Showing the work and process is very important to help with partial credit in case you make a mistake. In this case, providing the actual Excel file will help me to award partial credit where appropriate. Grading will be based on both the answers and the organized calculations/work provided. There is no collaboration allowed and any communication or sharing of files will result in a zero. Please feel free to ask if there are any problems that are not clear. By signing this quiz, you are agreeing to abide by the academic integrity policies established by Molloy College. Thank you and good luck!! “Plagiarism involves using words or ideas, orally or in a paper, obtained in books, articles, the Internet, interviews, radio, or TV programs, without appropriate citation. It also includes the following: Presenting someone else’s words or ideas as one’s own in a paper or exam without acknowledgement, incorporating published passages into a paper without quotation marks or footnotes, quoting word-for-word from a source without using quotation marks, a citation, or a reference. Handing in another’s paper or project as one’s own or reusing papers” (Molloy College, Academic Integrity). Linear Programming - Production Planning for Grimace, Inc. The global electronics company Grimace, Inc. is planning its weekly production for the following four products: (1) Tablets, (2) Cell Phones, (3) Laptops, and (4) Business Computers. The profit contribution for each unit sold is $800 per Tablet, $350 per Cell Phone, $700 per Laptop, and $1,500 per Business Computer. Production for each product is limited due to the following resource constraints: · Labor Hours (maximum 4,000 hours available per week) · 5 hours per Tablet · 5 hours per Cell Phone · 6 hours per Laptop · 8 hours per Business Computer · Chassis Units (maximum 400 units available per week) · 1 unit per Tablet · 1 unit per Cell Phone · 1 unit per Laptop · 1 unit per Business Computer · Disk Drives (maximum 300 units available per week) · 2 units per Tablet · 1 unit per Cell Phone · 2 units per Laptop · 1 unit per Business Computer · Hard Drives (maximum 20 units available per week) · 1 unit per Business Computer · No hard drives are used in the production of the Tablets, Cell Phones, or Laptops · Memory Chips (maximum 22,000 units available per week) · 16 units per Tablet · 8 units per Cell Phone · 32 units per Laptop · 64 units per Business Computer · Circuit Boards (maximum 10,000 units available per week) · 1 unit per Tablet · 1 unit per Cell Phone · 2 units per Laptop · 4 units per Business Computer Furthermore, it is required that Grimace produce at least 55 Cell Phones per week, and no more than 10 Business Computers. The goal is to determine the optimal quantity of Tablets, Cell Phones, Laptops, and Business Computers to produce in order to maximize profits. a) On the blank spreadsheet setup page provided, please construct the problem using linear programming by writing out the formulas similar to as was presented in the Module 6 lessons (i.e., Documents #17, #18, and #19) and in the homework solutions. (10 points) More specifically: · Identify the “decision variables” (e.g., T = # of tablets to produce, C = # of cell phones to produce, etc.) · Construct the “objective function” (e.g., $800T + $350C + ……) · Mathematically write out the eight constraints (i.e., maximum labor hours, chassis units, disk drive units, hard drive units, memory chip units, and circuit board units; minimum production quantity of Cell Phones; maximum production of Business Computers). · NOTE: Please see Documents #17, #18, and #19 and the homework solutions for more details regarding these setups. The formulas should all be written similar to the algebraic format shown in these documents. b) Create an Excel file that models this problem. (40 points) NOTE: You may use your homework template files if they help you, but please do not share files or collaborate. The setup example you created in part (a) can be directly translated into Excel. Please remember that the actual formulas are in the “objective function” and the LHS columns of the “constraints”. · Finally, please add the constraint that all decision variables should be INTEGER (i.e., in Solver, click “ADD” Constraint; then “cell reference” should include the highlighted decision variables; in the drop-down menu, select “int” to force the decision variables to be “integers”). This will eliminate producing a fraction of a product (e.g., we cannot produce half of a laptop, etc). c) Run “Solver” and write down the following: (40 points) · Optimal number of Tablets, Cell Phones, Laptops, and Business Computers produced (i.e., the “Decision Variable” answers; note that you may not necessarily produce every item): Tablets = Cell Phones = Laptops = Business Computers = · Maximum Profit (i.e., the overall result from the “Objective Function” cell) = d) Are any of the constraints binding? If so, please explain what it means in the context of this problem. Remember that a binding constraint will be one where the “LHS = RHS” in the final solution answer. (10 points) Linear Programming - Production Planning for Grimace, Inc. Grimace, Inc. Decision Variables: TabletCell PhoneLaptopBusiness Number of units = Objective Function: Maximize Profit = Constraints: LHSSignRHS Max. Labor Hours (hrs/week) Max. Chassis (units/week) Max. Disk Drives (units/week) Max. Hard Drives (units/week) Max. Memory Chips (units/week) Max. Circuit Boards (units/week) Min. Production Cell Phones Max. Production Business Computers ObjectiveFunction Excel formula goes in this cellConstraintExcel formulas go in this column Flair Furniture Media Selection Media Selection - The Win Big Gambling Club Decision Variables: TV spotsNewspaper AdsRadio (30 sec)Radio (1 min) X1X2X3X4 Number of units = Objective Function: Maximize Audience Coverage = Constraints: LHSRHS Max. Weekly Advertising Budget Max. Spent on Radio Max. TV Spots (X1) Maximum Newspaper Ads (X2) Max. 30 sec. Radio Spots (X3) Max. 1 min. Radio Spots (X4) Min. Radio Spots Contracted Sheet1 Flair Furniture Holiday Meal Turkey Ranch Holiday Meal Turkey Ranch Decision Variables: Brand 1Brand 2 B1B2 Number of pounds = Objective Function: Minimize Cost = Constraints: LHSRHS Min. Ingredient A Min. Ingredient B Min. Ingredient C Sheet1