CASE its lose in ROSE TREE REGIONAL AIRLINE or ad- ying Decision Support Using Microsoft Excel Solver PREVIEW onse Tree Regional Airline provides passenger transportation services and small-parcel logistics to major met- Ros litan areas in the Mountain region. The company operates in a hub-and-spoke fashion with the headquar "or in Salt Lake City at its center, serving the following cities: Las Vegas, Albuquerque, Denver, Cheyenne, verse, Reno, and Phoenix. Considering the tremendous competition that the airline industry has seen in brent years, management has determined that the current manual scheduling system can no longer produce The most cost-effective plane allocation to the various routes. Furthermore, the company is interested in the possibility of selling any unused cargo space to companies like Amazon. You have been hired as a new MIS consultant to develop a DSS model for Rose Tree Regional Airline. Your completed model will be used to assign the plane fleet to its seven destinations while minimizing costs. Also, you will be asked to modify the model to calculate how taking on additional cargo will affect the company's profitability and operating costs. PREPARATION Review spreadsheet concepts discussed in class and in your textbook. Complete any exercises that your instructor assigns. Complete any part of Tutorial D that your instructor assigns, or refer to it as necessary. Review the file-saving procedures for Windows programs in Tutorial C. BACKGROUND You will use your Excel skills to build a decision support model and determine how to best allocate the com- pany's fleet to various destinations. The model requires the following data, which the management team has compiled for you: . Data for the four different types of planes in the fleet: o Passenger capacity o Cargo space o Operating cost per mile (includes fuel, labor, and overhead) 0 Number of available planes Ticket pricing to each destination Cargo pricing to each destination Distance from Salt Lake City to each destination In addition, the operations department has given you information about the passenger and cargo demand for each city: . Expected daily passenger load . Expected daily daily cargo load satisfy passenger requirements and cargo demand, your Solver model will assign planes by number and to each destination city. The model will also calculate daily revenues from both passenger service and 80, as well as the total daily operating cost. The results of these calculations will be used to create a daily152 Case 8 gross profit statement. You will run Solver first to minimize the total operating cost. Next, you will modify the model to examine taking on additional cargo and its effect on the total operating cost and profitability. Finally, you will run the modified model to maximize daily gross profits. Rose Tree Regional Airline Fleet The company's fleet consists of 50 planes divided into four different types. From an operating cost perspective (highest to lowest), this is the current fleet: Boeing 717 Bombardier CRJ700 Embraer E170 Airbus A220 ASSIGNMENT 1: CREATING SPREADSHEET MODELS FOR DECISION SUPPORT In this assignment, you will create spreadsheets that model the business decision Rose Tree Regional Airline is seeking. In Assignment 1A, you will create a spreadsheet and attempt to assign the planes manually to mini- mize the total operating cost. In Assignment 1B, you will copy the spreadsheet to a new worksheet, and then set up and run Solver to minimize the total operating cost. In Assignment 10, you will copy the Solver solu- tion to a new worksheet and modify it to add calculations for taking on additional cargo. You will then rerun Solver to determine how the extra cargo affects the company's total operating cost. In Assignment 1D, you will copy the modified Solver spreadsheet and rerun Solver to maximize daily gross profit. This section helps you set up each of the following spreadsheet components before entering the cell formulas: Constants Calculations and Results Income Statement The Calculations and Results section is the heart of the decision model. You will set up columns for travel distance, daily demand, plane assignment by type, plane use, and operating costs. The spreadsheet rows will represent destination cities. The Plane Assignment section will be the range of changing cells for Solver to manipulate. The total operating cost will serve as your optimization cell for Assignments 1B and 1C, and the daily gross profit will be your optimization cell for Assignment 1D. You will add formulas to the additional cargo cells in the Calculations and Results section for Assignments 1C and 1D. Assignment 1A: Creating the Spreadsheet for the Base Case A discussion of each spreadsheet section follows. This information helps you set up each section of the model and learn the logic of the formulas in the spreadsheet. If you choose to enter the data directly, follow the cell structure shown in the figures. You can also download the spreadsheet skeleton if you prefer. To access the base spreadsheet skeleton, select Case 8 from your data files and then select RoseTreeRegionalAirline.xIsx. Constants Section First, build the skeleton of your spreadsheet. Set up the spreadsheet title and Constants section as shown in Figure 8-1. An explanation of the column items follows the figure.Rose Tree Regional Airline 153 modify the D E F G H ty. Finally, Rose Tree Regional Airline Assignment Problem wa - Constants Section: Plane Data: Operating Operating Cargo Cost per Cost per rspective Passenger Capacity Passenger Cargo Available Capacity (ft ) Mile Mile Fleet Fill Plane Type 117 985 $0.110 $0.055 Legend 15 Joeing 717 78 150 $0.100 $0.050 Bombardier CR1700 Changing Cells 70 156 $0.090 $0.045 Embraer E170 Optimization Cell 8 133 838 $0.075 $0.035 Airbus A220 9 10 Fee Schedule: Average Ticket Cargo ORT Destination Price Price/ft3 11 $213 $300.00 12 Las Vegas Airline $263 $150.00 13 Albuquerque $127 $200.00 to mini- 14 Denver $678 15 Cheyenne $200.00 I then $133 $300.00 16 Boise solu- 17 $317 Reno $125.00 Phoenix $274 $175.00 erun you will FIGURE 8-1 Spreadsheet title and Constants section Spreadsheet title-Enter the spreadsheet title in cell Bl and then merge and center the title across cells Bl through Fl. Constants section, Plane Data table-Enter the column headings shown in cells B5 through G5. Plane Type-Enter each of the four planes listed in cells B6 through B9. . Passenger Capacity-Enter each of the four passenger capacities listed in cells Co through C9. ravel Cargo Capacity (fts)-Enter each of the four cargo capacities listed in cells Do through D9. will Operating Cost per Passenger-Mile-Enter each of the four operating costs per mile listed in cells E6 through E9. the Operating Cost per Cargo-Mile-Enter each of the four operating costs per mile listed in cells F6 through F9. Available Fleet-This value is the number of planes of each type that Rose Tree Regional Airline keeps in service. Enter these numbers in cells Go through G9. Constants section, Fee Schedule table-Enter the column headings shown in cells Bl1 through D11. del Destination-Enter the seven destination cities in cells B12 through B18. ell Average Ticket Price-Enter the passenger ticket prices for the seven destinations in cells C12 through C18. Cargo Price/fts-Enter the cargo price per cubic foot for the seven destinations in cells D12 through D18. Fill Legend-This section is actually adjacent to the Constants section. Enter "Fill Legend" in cell J5, fill cell Jo in yellow, fill cell J7 in blue, enter "Changing Cells" in cell Ko, and enter "Opti- mization Cell" in cell K7. Calculations and Results Section The Calculations and Results section (see Figure 8-2) will contain distances, daily passenger bookings, and daily cargo shipment data obtained from the operations department. Although these values are constants, keeping them in the m in the Calculations and Results section facilitates writing and copying formulas in the Plane Uti. lization, Costs, and Extra columns. This section also includes the Plane Assignment table, which contains the changing cells and calculations for plane usage, costs, and additional cargo. An explanation of the sections and columns follows the figure.154 Case 8 Plane Utilization Costs Calculations and Results Section: Daily Demand % of % of Total Total Daily Daily Embraer Cargo Cargu Operating Distance Cargo Boeing 717 Bombardier Airbus A220 Passenger CR1700 E170 Capacity Capacity Capacity Cost Cargo fe Capacity Utilized Utilized to be Destination Salt Lake Passenger hipments Bookings Added Hub ft 1,500 Las Vegas 313 750 23 421 500 1,500 Albuquerque 24 322 550 2,000 Denver 100 1,500 25 Cheyenne 321 26 Boise 257 750 1,000 750 Reno 372 500 Phoenix 439 1,250 500 total/Avg Total Cost FIGURE 8-2 Calculations and Results section Table headings-If you did not use the spreadsheet skeleton, enter the column headings shown in cells B20 through 021 in Figure 8-2. Destination-Cells B22 through B28 hold the seven cities serviced daily by Rose Tree Regional Airline. Distance from Salt Lake City Hub-Cells C22 through C28 hold the route distances in miles to each of the seven destinations. Daily Passenger Bookings-Cells D22 through D28 hold the average number of passenger tickets booked each day. Daily Cargo Shipments (fts)-Cells E22 through E28 hold the average number of cubic feet of cargo shipped daily. Plane Assignment section-Cells F22 through 128 are the heart of the Solver model-the chang- ing cells. The cells hold the amounts of each of the four aircraft types that Solver will assign to the seven destinations. Enter "1" in each of these cells for now. You should fill the cells with a background color to indicate that they are the changing cells for Solver. To fill the cells, select them and then click the Fill Color button in the Font group on the Home tab. In the spreadsheet skeleton, the cells are yellow. Plane Utilization section, Total Passenger Capacity-Cells J22 through J28 hold the total passen- ger capacity for each destination. The capacity is calculated by multiplying the number of each assigned plane type by its passenger capacity, which is taken from cells C6 through C9 of the Constants section. Next, take the sum of the total capacities for the four types of planes assigned. Be sure to use absolute cell references for the passenger capacity values from the Constants sec- tion so that you have to write the formula only for the first cell (J22); then you can copy the for- mula to cells J23 through J28. % of Passenger Capacity Utilized-Cells K22 through K28 hold the percentage of passenger capac- ity used for each destination. The value is calculated by dividing Daily Passenger Bookings by Total Passenger Capacity. Total Cargo Capacity-Cells L22 through L28 hold the total cargo capacity for each destination. The capacity is calculated by multiplying the number of each assigned plane type by its cargo capacity, which is taken from cells D6 through D9 of the Constants section. Next, you take the sum of the total capacities for the four types of planes assigned. Again, you must use absolute cell references for the freight capacity values from the Constants section so that you only have to write the formula for the first cell (L22); then you can copy the formula to the other six cells. % of Cargo Capacity Utilized-Cells M22 through M28 hold the percentage of cargo capacity used. The percentage is calculated by dividing the Daily Cargo Shipments by the Total Cargo Capacity. Operating Cost-Cells N22 through N28 hold the operating cost for each plane type to each des- tination. The cost is calculated by the following formula: Number of planes assigned x Operating Cost per passenger mile x Mileage to destination + Number of planes assigned x Operating Cost per cargo mile x Mileage to destination The costs for each of the four planes are then added to get the operating cost. Add'l Cargo fts to be Added-Leave cells 022 through 028 blank for now. You will place formulas in these cells in Assignment 1C.regional Airline Total/Avg-Total the cell entries for every column except column K (% of Passenger Capacity 155 Utilized) and column M (% of Cargo Capacity Utilized). Place the totals in cells D29 to J29, 129, and N29. For cell K29, divide the total Daily Passenger Bookings in cell D29 by the Total Passen- ger Capacity in cell J29 and format the result as a percentage. For cell M29, divide the total Daily Cargo Shipments in cell E29 by E29 by the Total Cargo Capacity in cell L29 and format the result as a percentage. These two averages are the overall utilization rates of your fleet. For now, leave cell 029 blank (the total for Add'l Cargo ft3 to be Added). If you wrote your formulas correctly, the Calculations and Results section should look like Figure 8-3. C D E F G H M Daily Demand ane Assignment 19 20 Calculations and Results Section: Daily Distance Daily Cargo Total % of from Embraer Passenger Boeing 717 Bombardier Total CR1700 E170 Airbus A220 Passenger senger Destination Salt Lake Bookings Capacity Capacity Cargo Cargo Operating Hub Utilized Capacity Cargo it Utilize 313 750 1,500 21 188 Las Vegas 421 500 1,500 398 2,079 22 126 2.079 72% 5 174,889 23 Albuquerque 322 550 2,000 138% 2.079 72% 5 195.765 24 Denver 321 100 1,500 398 96% $ 185.553 25 Cheyenne 257 750 1,000 25%% 398 2.079 72%% S 101.115 26 372 500 750 188% 2,079 398 48% 5 119.826 27 439 1,250 500 126%% 2.079 36% 5 121.365 Total/Avg 4,400 8,750 398 314% 2079 2.786 29 158% 14.553 $ 246.389 60% Total Cost FIGURE 8-3 Completed Calculations and Results section Income Statement Section The Income Statement section (see Figure 8-4) is actually a projection of daily gross profits and is based on SU the number of planes that will be assigned either manually or by Solver. An explanation of the line items follows the figure. A B C 30 31 Income Statement Section: 32 Passenger Revenues: 33 Cargo Revenues: 34 Additional Cargo Total Revenues: ubi less Operating Costs: I ca Daily Gross Profit: 1 th FIGURE 8-4 Income Statement section of Passenger Revenues-This value is calculated by multiplying the passenger tickets booked for ron each destination (cells D22 through D28) by their respective average ticket prices (cells C12 through C18), and then totaling the ticket revenues for the seven destinations. Cargo Revenues-This value is calculated by multiplying the daily cargo shipments for each on destination (cells E22 through E28) by their respective air freight prices (cells D12 through D18), and then totaling the cargo revenues for the seven destinations. Additional Cargo-This value is the additional revenue from cell 029, which will be used later in uld the assignment. Total Revenues-This value is the total of Passenger Revenues, Cargo Revenues, and Additional Cargo revenues. Less Operating Costs-This value is the Total Cost from cell N29. Daily Gross Profit-This value is the Total Revenues minus the Operating Costs. This cell will be used as the optimization cell for Assignment 1D. Colver156 Case 8 If your formulas are correct, the initial Income Statement section will appear as shown in Figure 8-5 B 31 Income Statement Section: 32 Passenger Revenues: 708,315.00 33 Cargo Revenues: 1,856,250.00 34 Additional Cargo 35 Total Revenues: $ 2,564,565.00 36 less Operating Costs: $ 1,144,901.25 37 Daily Gross Profit: $ 1,419,663.75 FIGURE 8-5 Initial income statement The initial income statement correctly reflects the revenues expected from the passenger and cargo book- ings, but the operating costs are not correct because the planes required to transport the passengers and cargo have not been completely assigned yet. Attempting a Manual Solution Attempt to assign your plane fleet manually in the spreadsheet. You have several good reasons for doing this. First, you can make sure your model is working correctly before you set up Solver to run. Second, assign- ing the plane fleet manually will demonstrate which constraints you must meet in solving the problem. For instance, if a passenger or cargo utilization rate is over 100%, you have not assigned enough planes to carry all the passengers and cargo to a particular destination. Therefore, one constraint is that the total passenger capacity for the planes assigned to a destination must be greater than or equal to the passenger bookings. Another constraint is that the total cargo capacity for the planes assigned must be greater than the cargo shipments booked. Given the fleet size, you can probably assign the fleet manually and meet all of your con- straints. However, will your total operating cost be the least expensive solution? Running the problem manu- ally will provide an initial operating cost to which you can compare your Solver solution later. The Solver optimization tool should give you a better solution than assigning the fleet manually. When attempting to assign the planes manually in the Plane Assignment section (the changing cells), you must satisfy both the passenger and cargo demands for each destination-in other words, the Total Passenger Capacity values in cells J22 through J28 and the Total Cargo Capacity values in cells L22 through L28 must be equal to or greater than the Daily Demand values in cells D22 through D28 and E22 through E28. If you have satisfied the passenger and air freight demands correctly, none of the utilization rates in cells K22 through K28 and M22 through M28 will exceed 100%. In addition, the total planes assigned for each type (cells F29 through 129) cannot exceed the available number of each plane type (cells Go to G9). Once you reach a solution that satisfies the preceding constraints, save your workbook. Name the work- sheet Rose Tree Guess and then right-click the worksheet name tab, click Move or Copy, copy the worksheet, the assignment. and rename the new copy Rose Tree Solver 1. You will use the new worksheet to complete the next part of Assignment 1B: Setting up and Running Solver Before using the Solver Parameters window, you should jot down the parameters you must define and their cell addresses. Here is a suggested list: . The cell you want to minimize (Total Cost, cell N29) F22 through 128) The cells you want Solver to manipulate to obtain the optimal solution (Plane Assignment, cells The constraints you must define: All the plane assignment cells must be non-negative integers. The total number of each type of plane assigned (cells F29 through 129) cannot exceed the number of available planes of each type (cells Go through G9).Rose Tree Regional Airline 157 The total passenger capacity to each destination (cells J22 through J28) must be equal to or greater than the total passenger bookings for each destination (cells D22 through D28). The total cargo capacity to each destination (cells L22 through L28) must be equal to or greater than the total cargo shipment for each destination (cells E22 through E28). Next, set up your problem. In the Analyze group on the Data tab, click Solver; the Solver Parameters window appears, as shown in Figure 8-6. Enter "Total Cost" in the Set Objective text box, click the Min hutton, designate your Changing Cells (cells F22 through 128), and add the constraints from the preceding list. Use the default Simplex LP solving method. If you need help defining your constraints, refer to Tutorial D. Solver Parameters X you Set Objective: ---you To: Max O Min O Value Of: It in inimi By Changing Variable Cells: 1 Subject to the Constraints: ight c Add e pla Change Use Delete e mid In OI Reset All Daily Load/Save Make Unconstrained Variables Non-Negative its Select a Solving Simplex LP v Options 28. Method: per Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP adde Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver 25 y problems that are non-smooth. er all Close hat Solve Help Ilatic FIGURE 8-6 The Solver Parameters window Next , you should click the Options button and check the Options window that appears (see Figure 8-7). hou The default Integer Optimality is 5%; change it to 1% to get a better answer. Make sure the Constraint Preci- sion is set to the default value of .000001 and that the Use Automatic Scaling option is checked. When you finish setting the options, click OK to return to the Solver Parameters window.158 Case 8 ? X Options All Methods GRG Nonlinear | Evolutionary | Constraint Precision: 0.000001 Use Automatic Scaling Show Iteration Results Solving with Integer Constraints Ignore Integer Constraints Integer Optimality (%): 1 Solving Limits Max Time (Seconds): Iterations: Evolutionary and Integer Constraints: Max Subproblems: Max Feasible Solutions: OK Cancel FIGURE 8-7 The Solver Options window Run Solver and click Answer Report when Solver finds a solution that satisfies the constraints. When you finish, print the entire workbook, including the Solver Answer Report Sheet. To save the workbook, click the File tab and then click Save. For the rest of the case, you either can use the Save As command to create new Excel workbooks or continue copying and renaming the worksheets. Both options offer distinct advantages, but having all of your worksheets and Solver Answer Reports in one Excel workbook allows you to compare different solutions easily, as well as prepare summary reports. Before continuing, examine the plane assign- ments that Solver chose for minimizing the total cost. If you set up Solver correctly, you should see a signifi- cant reduction in total cost from your manual assignment. Assignment 1C: Additional Cargo Revenues Using the model you created in Assignment 1A, Rose Tree Regional Airline can also determine the revenue benefit of accepting additional cargo from other companies. Taking on additional cargo might be a good option for Rose Tree Regional Airline if its cargo service has excess capacity. After contacting other parcel delivery companies in the area that service the same seven destinations from Salt Lake City, you determine that the rate for additional cargo will be discounted $15 per fts in order to be more competitive. You must modify the worksheet to accommodate selling the excess capacity and then rerun Solver.Rose Tree Regional Airline 158 Copy your Rose Tree Solver I worksheet and rename it Rose Tree Add'l Cargo. Perform the following tasks: through 028). You must insert a calculation for the cells in the Add'l Cargo it to be Added column (cells 022 You must recalculate the operating cost (cells N22 through N28) by incorporating the extra cargo. You must calculate the additional cargo revenue (cell C34 in the Income Statement section) by using the cargo cost per route and available excess cargo to be sold. Keep in mind that the excess cargo will be sold at a discount. By selling additional cargo space to companies like Amazon, the company still receives passenger rev- onues, and at the same time it collects additional revenues from exploiting excess capacity. You must use you place in cell 022: conditional statements to write the formulas for cells 022 through 028. Use the following logic for the formula . If the Total Cargo Capacity to Las Vegas (cell L22) is greater than the Daily Cargo Ship- ments (cell E22), the company can sell the difference between the two (its excess capacity) to competitors. . If the Total Cargo Capacity to Las Vegas (cell L22) is less than or equal to the Daily Cargo Ship- ments (cell E22), the company has no excess capacity to sell to competitors. Click the Insert Function button (fx) next to the formula bar below the Ribbon to help you write the If statement (see Figure 8-8). After you enter the formula in cell 022, you can copy and paste the formula into cells 023 through 028. Cell 029 is the total of cells 022 through 028. Function Arguments ? X IF Logical_test 1 = logical Value_if_true = any -planValue_if_false 1 = any Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE. Logical_test is any value or expression that can be evaluated to TRUE or FALSE. Formula result = OK Cancel Help on this function FIGURE 8-8 The If function window Although you are now taking extra cargo and increasing your revenues, is the current plane assignment still the most cost efficient? Run Solver with the additional cargo calculation to see if there's a more efficient plane assignment. The Solver Results window appears (see Figure 8-9) and warns that the linearity conditions required by the Simplex LP solving method are not satisfied. The default Simplex calculation method will not work because the model now includes the excess cargo equations, which are If functions and therefore nonlinear.Solver Results The linearity conditions required by this LP Solver are not satisfied. Reports Linearity Keep Solver Solution Restore Original Values Return to Solver Parameters Dialog Outline Reports OK Cancel Save Scenario... The linearity conditions required by this LP Solver are not satisfied. Create a linearity report to see where the problem is, or switch to the GRG engine. FIGURE 8-9 Error message when Simplex LP method is used on nonlinear models Fortunately, Solver has methods for working with nonlinear problems as well. Open the Solver Parameters window again and click the Select a Solving Method list arrow. Click GRG Nonlinear (see Figure 8-10), and then click Options. Under Options, click the All Methods tab to check that the Integer Optimality is set to 1% (see Figure 8-11), and then click OK to return to the Solver Parameters window. Run Solver again; the solu- tion will probably take longer than in the earlier problem. If the solution takes too long to calculate, you can cap the Max Time in seconds in the Options window (see Figure 8-11). Click Answer Report and then click OK to create a second Answer Report. Examine it and the worksheet to see if it provides a better plane assign- ment solution when compared with the first Solver solution. Make Unconstrained Variables Non-Negative Select a Solving GRG Nonlinear Options Method: Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP AT 830 Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. FIGURE 8-10 Selecting a new solving methodOptions Rose Tree Regional Airline 161 "All Methods|GRG Nonlinear | Evolutionary | ? X Constraint Precision: 0.000001 Use Automatic ScalingShow Iteration Results Solving with Integer Constraints Ignore Integer Constraints Integer Optimality (%): 1 Solving Limits Max Time (Seconds): Iterations: Evolutionary and Integer Constraints: Max Subproblems: Max Feasible Solutions: OK Cancel FIGURE 8-11 Solver Options window with Integer Optimality set to 1% Print the Rose Tree Add'l Cargo worksheet and Answer Report 2 and then save your workbook. What if, instead of minimizing costs, you were interested in maximizing revenues? In other words, is gross profit greater than that in the earlier solutions? Because you added the ability to sell excess cargo in the assignment model, you should probably maximize gross profit to account for any possible efficiencies. Assignment 1D: Rerunning Solver to Maximize Gross Profit Copy the worksheet that contains the solution for excess capacity, and rename the new worksheet Rose Tree Solver 3. Click Solver to open the Solver Parameters window, and then change the value in the Set Objective text box to SC$37 (the cell that contains Daily Gross Profit). Click the Max button to maximize the Set Object tive value, as shown in Figure 8-6 earlier, and then run Solver. The solution will probably take longer than in the earlier problem. If the solution takes too long to calculate, you can cap the Max Time in seconds in the Options window (see Figure 8-11). Click Answer Report and then click OK to create a third Answer Report, Examine it and the worksheet to see if maximizing daily gross revenues provides a better plane assignment solution when compared with the solutions that minimized total costs.62 Case 8 ASSIGNMENT 2: USING THE WORKBOOK FOR DECISION SUPPORT You have built a series of worksheets to determine the best plane assignments with and without selling ex cargo space and maximizing profit versus minimizing costs. You will now complete the case by using your solutions and Answer Reports to make recommendations in a memorandum. Use Microsoft Word to write a memo to the management team at Rose Tree Regional Airline. State the results of your analysis, whether you think the current plane assignment method is still profitable, and whether you think plane assignments sholl be based on lowest cost or maximum profit. Set up your memo as described in Tutorial E. In the first paragraph, briefly describe the situation and state the purpose of your analysis. . Next, summarize the results of your analysis and give your recommendations. Support your recommendation with appropriate screen shots or Excel objects from the Excel workbook. (Tutorial C describes how to copy and paste Excel objects.) In a future case, you might suggest revisiting the Solver analysis to determine the profitability of making changes to the plane fleet. ASSIGNMENT 3: GIVING AN ORAL PRESENTATION Your instructor may request that you summarize your analysis and recommendations in an oral presentation. If so, prepare a presentation for the CEO and other managers that lasts 10 minutes or less. When preparing your presentation, use PowerPoint slides or handouts that you think are appropriate. DELIVERABLES Prepare the following deliverables for your instructor: A printout of the memo Printouts of your worksheets and Answer Reports presentation Electronic media, which should include your Word document, Excel workbook, and PowerPoint Staple the printouts together with the memo on top. If you have more than one Excel workbook file for your case, write your instructor a note that describes the different files.signment 1C Create a copy of the Rose Tree Original worksheet. (Right click on the tab name, select Move/Copy, check the box for create a copy, tell it to be placed at (move to end)). Rename it Min Add Cargo. First three bullets on page 159: a. Add the IF function as directed to cells 022:028. Use the SUM function in cell 029. The IF function is explained with the two bullets in the middle of the page. b. Modifying N22:N28. The additional cargo in column O must also be included in the operating cost now. Modify your calculation to include the data in column O. i. You can combine the Added Cargo with the Daily Cargo Shipment into the same calculation. ii. For N22 - The part of your calculation where its C22*E22 can be changed to C22*(E22+022) iii. Apply this same type of change down to cell N28. c. Additional Cargo calculation for C34. i. Any additional cargo is sold at a discount of $15 per cubic foot. ii. Use the SUMPRODUCT function to first find the added cargo (cells 022:028) and the price per cubic foot (cells D12:D18). (This gives you the cargo revenue without any discount applied) iii. Next take the discount of $15 off by adding together all of the added cargo in column O and multiplying it by 15 and subtracting that from the SUMPRODUCT function. iv . Here is the syntax of what you are doing for the calculation in C34: =SUMPRODUCT(....)-SUM(...) * discount rate 3. Before Entering Solver a. Before modifying your changing cells the results in these cells should be: Office 2019 or older: i. N29 = $4,353,545 ii. C34 = $2,342, 100 Office 365: iii. N29 = $4,390,153 iv . C34 = $2,308,900 correctly). 4. Reseed your changing cell values to O. (Failure to do this will prevent Solver from running Page 2 of 25. Enter Solver and make the following changes: a. Chang the Solving method to GRG Nonlinear. (The IF function now makes this a nonline problem) b. Set the Constraint Precision to 0.01. c. Set the Max Time (Seconds) to 300. 6. Run Solver until a solution in generated. (Depending on computer performance this should tak 2-5 minutes) 7. The answer generated here can vary between $4,225,000 and $4,350,000. Create an answer report. Rename the answer report tab name as Add Cargo Min Cost. 8. Be sure to mentally note the difference between the original solution and the new solution yo just discovered. This will be documented later in the assignment. Assignment 1D 1. To aid in the analysis for Part 2 of the assignment, create a copy of the Min Add Cargo worksheet. Rename the worksheet Max Add Cargo. 2. Here you are optimizing gross profit. a. Remove the optimization color from cell N29. b. Apply the optimization color to cell C37