QUESTION 1: Graphical Method & Excel Solver Solutions (36 points) An Architect wishes to design the safety deposit room for a bank safe. He plans to place safety deposit boxes approximately 6 feet high by the approximate length of the room's walls, 40 feet (i.e., 240 square feet total space available). The bank has asked him to plan for two sizes of box: large and small. Large boxes consume 1 square foot and will rent for $90 per year. Small boxes consume 0.6 square feet and will rent for $60 per year. The bank has asked for at least 80 large boxes and at least 120 small boxes. The bank hopes to maximize its revenue from safety deposit boxes. A correct formulation for this problem is provided below: Let L and S denote the number of units of large and small boxes that the architect's design will provide MAX Z = 90L + 60S ($) subject to 1) Available Space L + 0.6S s 240 square feet 2) Minimum number of large boxes L 80 3) Minimum number of small boxes S 120 4) Non-negativity LS20 (a) Graph the constraint lines and mark them clearly with the numbers (1), (2) and (3) to indicate which line corresponds to which constraint. Darken the feasible region. (12 points) You can NOT use a software that automatically does the plotting: you are NOT allowed to use DESMOS or any similar tool. Plotting using Excel, a paint tool or hand plotting is OK. If you wish to use a graph paper" to assist you with plotting, one is provided below (check page 4). (b) Using the graphical method, how many of each size should the architect's design provide? What is the annual revenue? Report the solution in a managerial statement (i.e. describe verbally the optimal solution and its profit). Provide all necessary calculations to justify your answers. (8 points). (C) The bank would like to add a mini size box, which will rent for $35 per year and will consume 0.3 square feet of wall space. The bank will now require at least 30 mini boxes, at least 100 small boxes, and at least 40 large boxes. Further, the bank wants the total area of the large boxes to be at least 50% of the available space. How many boxes of each type should the architect include and what is the total annual revenue? Formulate the linear programming problem on a spreadsheet and SOLVE using Excel Solver (Provide the corresponding "Excel Spreadsheet" and the Answer Report" on this document). Include "managerial statements that communicate the results of the analysis. (i.e. describe verbally the results). (16 points)