complete

Q1 - Instructions
    For the LP problem presented in the 'Q1 Problem Formulation' worksheet, set up and solve the LP using Solver in the 'Solver Solution' worksheet to find the optimal solution and generate the Answer and Sensitivity Reports.
Once complete, answer the questions below and upload the complete spreadsheet with the Solver set-up and solution/sensitivity analysis intact to D2L.
    AFTER FINDING THE SOLUTION, USE THE ANSWER AND SENSITIVITY REPORTS TO ANSWER THE FOLLOWING QUESTIONS
    Based upon the Sensitivity Report, if the available amount of crude type A decreased by 3 (1000's of ba
els), determine the resulting net profit at the optimal solution?
        21948
    How low could the net profit on crude type B be and still have our cu
ent optimal solution remain optimal?
        3192
    Based upon the Sensitivity Report, if the minimum demand for Jet Fuel was to increase to 90 (in 1000s of gallons), what would be the resulting net profit and the optimal solution?
    net profit increases    22092
Q1 Problem Formulation
    As with most firms operating in a competitive environment, it is critical that refineries maximize the economic benefits of their operations. This involves purchasing of raw materials (crude oil) as well as creating the final products However, the resulting products are interdependent (i.e. multiple products are created simultaneously and with different proportions based upon the raw materials and refining/processing steps).
A simple refinery problem is presented below. Within the problem, five type of crude oil are available (denoted as A, B,C1,C2, and D), which can be used to generate various proportions of four different products. The four products are gasoline (G), heating oil (H), jet fuel (J) and lu
icants (L). The net profits resulting from the purchase/use of each crude type is based upon the proportion of each of the final products. This information is summarized in Table 1 below. Also given in the table are the amounts of each crude type available (in 1000's of ba
els) along with the minimum quantity of each product required to meet demand (in 1000's of gallons). The objective is to maximize net profits by determining the quantities of each type of crude to use to produce the required products while meeting the minimum demand for each.
The LP formulation for this problem is given below. Setup and solve the problem in the adjacent 'Solver Solution 1' worksheet, generate the answer and sensitivity reports, then answer the questions posted in the "General Instructions" worksheet.
    Decision Variables
     A = amount of crude type A to use                                    Crude type
     B = amount of crude type B to use                                TABLE 1     A      B      C1      C2      D
     C1 = amount of crude type C1 to use                                Net Profits on Crudes     48    28    50    46    52     Min Product Demand
     C2 = amount of crude type C2 to use                                 Products      Product Slate for each Crude type                    (1000s gallons)
     D = amount of crude type D to use                                Gasoline (G)     0.55    0.35    0.45    0.35    0.6    230
                                    Heating Oil (H)    0.35    0.25    0.1    0.3    0.25    95
    Objective Function                                Jet Fuel (J)     0.1    0.32    0.25    0.25    0.15    80
    Maximize...