Creating a Spreadsheet for Decision Support - Business Decision Making - ثاني ثانوي

Creating a Spreadsheet for Decision Support In this section, you will create a spreadsheet that models the business decision the Airline Company is seeking. Here, you will need to put to use all of the skills you have learned in this book. QUICK TIP Once you have completed Tasks 2 and 3, running Solver can take a long time. Make sure you plan ahead for this by completing the work you need to do in one lesson and running Solver at the start of the next lesson. Solver will work more quickly if you are not performing other computer tasks at the same time. Your tasks 1. You will create a spreadsheet and attempt to assign the planes manually to minimize the total operating cost. 2. You will copy the data to a new worksheet, and then set up and run Solver to minimize the total operating cost. 3. You will copy the first Solver worksheet to a new worksheet and run 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. A discussion of each spreadsheet section follows. Scan the QR code to access the workbook (Chapter 15 data file.xlsx) you'll be using for the rest of this lesson. 490 15 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 490 30/06/2023 14:29

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 491 Constants Section First, build the skeleton of your worksheet. Set up the worksheet title and Constants section as shown in Figure 15-2. FIGURE 15-2: Spreadsheet title and Constants section AutoSave Off File Search Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Calibri Paste BIU Undo Clipboard Q19 EX fx B C A 1 Airline Company Constants section 3 4 Plane data Font A A == A Tu E ab Wrap Text General Merge & Center ▾ Alignment E E F G H Operating cost Operating Passenger Cargo per passenger cost per Plane type capacity capacity (m) (km) cargo (km) Available fleet 6 Boeing 717 117 285 0.575 0.3 7 Bombardier CRJ700 78 46 0.52 0.285 B Embraer E170 70 48 0.563 0.263 9 Airbus A220 133 255 0.45 0.225 15 18 12 5825 10 Fee schedule Average ticket Cargo 11 Destination price price/m³ Jeddah SAR 2250.00 CAD 4070.00 Worksheet title: Enter the worksheet title in cell B1 and then highlight cells B1:G1 and merge and center them (see Figure 15-3). FIGURE 15-3: The Merge & Center button AutoSave Off Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Calibri 11 AA Paste BIU 3 Wrap Text Merge & Center General % Font Fy Alignment N Undo Clipboard Б Q19 XVfx Constants section, Plane data table: Enter the column headings shown in cells B5:G5 (see Figure 15-4). The Airline Project 491 30/06/2023 14:29

492 15 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 492 FIGURE 15-4: Constants section column headings AutoSave off Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Calibri 11 A A ab Wrap Text General Paste BIU Y 3 Merge & Center Undo Clipboard Font Alignment Q19 fx D E F Passenger Cargo Operating cost Operating per passenger cost per 5 Plane type capacity capacity (m) (km) cargo (km) Available fleet 6 Boeing 717 117 285 0.575 0.3 15 7 Bombardier CRJ700 78 46 0.52 0.285 18 8 Embraer E170 70 48 0.563 9 Airbus A220 133 255 0.45 0.263 0.225 12 5 10 Fee schedule Average ticket 11 Destination price Cargo price/m³ Jeddah SAR 2.250.00 SAD 4070.00 H . . • . . . 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 C6:C9. Cargo capacity (m³): Enter each of the four cargo capacities listed in cells D6:D9. The capacity of an area is calculated by multiplying the length x width x height of cargo space that is available in each model of plane. This shows the maximum volume available for use. Operating cost per passenger (km): Enter each of the four operating costs per kilometer listed in cells E6:E9. Operating cost per cargo (km): Enter each of the four operating costs per kilometer listed in cells F6:F9. Available fleet: This value is the number of planes of each type that Airline Company keeps in service. Enter these numbers in cells G6:G9. Constants section, Fee schedule table: Enter the column headings shown in cells B11:D11 (see Figure 15-5). 30/06/2023 14:29

FIGURE 15-5: Column headings for the fee schedule AutoSave Off Search Paste Undo Clipboard Б Font File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Calibri 11 AA Wrap Text General BIU A Merge & Center Alignment ES N Q24 fx B D E F G H Average ticket 11 Destination price Cargo price/m³ 12 Jeddah 13 Medina SAR 2,250.00 SAR 4,970.00 SAR 2,100.00 SAR 4,235.00 14 Ad Damman SAR 2,850.00 SAR 4,475.00 15 Rafha SAR 2,250.00 SAR 4,475.00 16 Yanbu SAR 2,400.00 SAR 4,970.00 17 Abha 18 Najran SAR 2,400.00 SAR 4,110.00 SAR 2,450.00 SAR 4,355.00 19 20 Calculations and results section Daily demand Plane assignment . Destination: Enter the seven destination cities in cells B12:B18. • Average ticket price: Enter the average passenger ticket prices for the seven destinations in cells C12:C18. FIGURE 15-6: The Fill legend File AutoSave off . Cargo (price/m³): Enter the cargo price per cubic meter for the seven destinations in cells D12:D18. Fill legend: This section is adjacent to the Constants section. Enter "Fill legend" in cell 16, fill cell 17 in yellow and fill cell 18 in blue. Enter. "Changing cells" in cell J7, highlight J7 and K7 and click Merge & Center. Enter "Optimization cell" in cell J8, highlight J8 and K8 and click Merge & Center (see Figure 15-6). Search Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Arial Paste BIU Y Undo Clipboard E Font P33 IXV fx 562999 و 10 Plane type Boeing 717 Bombardier CRJ700 Embraer E170 Airbus A220 Fee schedule وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 493 10 A A v ES D E Operating Wrap Text General Merge & Center 4 30-> % 900 Alignment ES Number Z H cost per Operating Passenger capacity Cargo capacity (m) passenger cost per (km) cargo (km) Available fleet 117 285 0.575 0.3 15 Fill legend 78 46 0.52 0.285 18 70 48 0.563 0.263 12 133 255 0.45 0.225 5 Average ticket Cargo Conditional Format as Cell Formatting Table Styles Y Styles Changing cells Optimization cell Insert The Airline Project 493 30/06/2023 14:29

494 Calculations and Results Section The Calculations and results section (see Figure 15-7) contains travel distances, daily passenger bookings, and daily cargo shipment data obtained from the operations department. Although these values are constants, keeping them in the Calculations and results section facilitates writing and copying formulas in the Plane utilization, and Costs columns. This section also includes the Plane assignment table, which contains the changing cells and calculations for plane usage, costs, and additional cargo. FIGURE 15-7: Calculations and results section File AutoSave Off Search Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Arial 10 Paste BIU AA Ax Undo Clipboard Foot Q42 fx A C 19 20 Calculations and results section E PCd Wrap Text General Merge & Center ▾ % 08-08 Conditional Format as Cell Formatting Table Styles Insert Delete Format ΣAutoSum Fill-> Clear Sort & Find Filter Select Alignment Number Styles Cels Editing H M N Daily demand Plane assignment Daily Distance from passenger Daily cargo shipments Destination Riyadh hub bookings (m) Boeing 717 Bombardier Embraer CRJ700 E170 Airbus A220 Plane utilization Total % of passenger passenger capacity capacity used Costs Total cargo % of cargo capacity (m) capacity used Operating cost Jeddah 941 750 450 1 Medina 837 500 450 1 1 1 1 24 Ad Damman 408 550 600 1 1 1 1 25 Ratha 767 100 450 1 1 1 1 ANSAR 26 Yanbu 1045 750 300 1 1 1 1 27 Abha 948 500 225 1 1 1 1 28 Najran 944 1250 150 1 1 29 Total/Avg 30 Total cost 15 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 494 • . Table headings: If you did not use the spreadsheet skeleton, enter the column headings shown in cells B20 through N21 in Figure 15-7. Destination: Cells B22 through B28 hold the seven cities serviced daily by Airline Company (see Figure 15-8). FIGURE 15-8: The Destination section of the spreadsheet AutoSave Off Sea File Home Insert Page Layout Formulas Data Review View Automate Help Arial 10 AA 三 Paste BIU 田 V A Wrap Text Merge & C Undo Clipboard E Font P33 fx 8 E Alignment D E F G Average ticket 11 Destination price Cargo price/m³ 12 Jeddah 13 Medina 14 Ad Damman 15 Rafha 16 Yanbu 17 Abha SAR 2,250.00 SAR 4,970.00 SAR 2,100.00 SAR 4,235.00 SAR 2,850.00 SAR 4,475.00 SAR 2,250.00 SAR 4,475.00 SAR 2,400.00 SAR 4,970.00 SAR 2,400.00 SAR 4,110.00 18 Najran SAR 2,450.00 SAR 4,355.00 30/06/2023 14:29

QUICK TIP 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. . Distance from Riyadh hub: Cells C22 through C28 contain the route distances in kilometers to each of the seven destinations. Daily passenger bookings: Cells D22 through D28 contain the average number of passenger tickets booked each day. Daily cargo shipments (m³): Cells E22 through E28 contain 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 changing cells. The cells contain 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 (see Figure 15-9). FIGURE 15-9: The Plane Assignment section AutoSave On File Home Insert Page Layout Formulas Data Review ✗Cut Calibri 11 AA 三 Copy Paste BIU A Format Painter Clipboard P32 Font fx H G QUICK TIP |=(F22*$C$6)+(G22*$C$7) |+(H22*$C$8)+(122*$C$9) وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 495 20 20 Plane assignment Boeing Bombardier 28 29 2222222222 21 717 CRJ700 Embraer E170 Airbus A220 1 1 23 1 1 1 1 24 1 1 1 25 1 1 1 1 26 1 1 1 27 1 20 Total passenger capacity Total passenger capacity: Cells J22 through J28 hold the total passen- ger capacity for each destination. The capacity is calculated by multiply- ing the number of each assigned plane type by its passenger capacity, which is taken from cells C6 through C9 of the Constants section. Next, calculate the sum of the total capacities for the four types of planes assigned. For example, the Total Passenger Capacity to Jeddah is: (Number of Boeing Jeddah X Pass. Capacity Boeing) + (Number of Bombardier × Pass. Capacity Bombadier) + (Number of Embraer, Jeddah Pass. Capacity Embraer) + (Number of Airbus x Pass. Capacity AirBus). Jeddah Jeddah The Airline Project 495 30/06/2023 14:29

. Be sure to use absolute cell references for the passenger capacity values. from the Constants (C6:C9) section so that you have to write the for- mula only for the first cell (J22); then you can copy the formula to cells J23 through J28 (see Figure 15-10). FIGURE 15-10: Calculations and results section AutoSave Off File Home Insert Page Layout Formulas Data Review Arial 10 A A Paste BIU Y A Undo Clipboard Font E N40 fx J K 21 Total passenger capacity % of passenger used =D22/J22 =D23/J23 =D24/J24 =D25/J25 22 =(F22*$C$6)+(G22*$C$7)+(H22*$C$8)+(122*$C$9) 23 (F23 $C$6)+(G23*$C$7)+(H23*$C$8)+(123*$C$9) 24=(F24*$C$6)+(G24*SC$7)+(H24*$C$8)+(124*SC$9) 25 (F25 $C$6)+(G25*$C$7)+(H25*$C$8)+(125*$C$9) 26 (F26 $C$6)+(G26*$C$7)+(H26*$C$8)+(126*$C$9) =D26/J26 27 =(F27*$C$6)+(G27*$C$7)+(H27*$C$8)+(127*$C$9) 28 =(F28*$C$6)+(G28*$C$7)+(H28*$C$8)+(128*$C$9) 29 D29/SUM(J22:J28) =D27/J27 =D28/J28 QUICK TIP |=(F22*$D$6)+(G22*$D$7) +(H22*$D$8)+(122*$D$9) . Percentage of passenger capacity used: Cells K22 through K28 hold the percentage of passenger capacity used for each destination. The value is calculated by dividing Daily passenger bookings by Total passen- ger capacity. Also shown in Figure 15-10 above. Total cargo capacity: Cells L22 through L28 hold the total cargo capac- ity for each destination. Use the same formula that you developed for Total Passenger Capacity except that you will use the cargo capacity for each plane type, which is taken from cells D6 through D9 of the Constants section. Again, you should 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 for- mula to the other six cells. Percentage of cargo capacity used: 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 for each Destination (L22:L28). 496 15 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 496 30/06/2023 14:29

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 497 . Operating cost: Cells N22 through N28 contain the operating costs for each plane type to each destination. The cost is calculated by the follow- ing formula: Total operating cost (Destination) = Total passenger cost + Total cargo cost Where: Total passenger cost (Destination) = Distance (Destination) x Daily pas- senger bookings (Destination) x Total operating cost per passenger-km + Total cargo cost (Destination) = Distance (Destination) x Daily cargo shipments (Destination) x Total operating cost per cargo-km Try to work this formula out from the description provided above for Jeddah. Use a pencil and paper and try to list the cell addresses of the variables shown. Take your time and work through it carefully. Analysis problems often have one or two significant formulas that have to be developed. This is a good opportunity to practice this. you have a solution worked out, compare it to the sample solution: When you For Jeddah, the Operating cost would be: =C22*D22*(F22*$E$6+G22*$E$7+H22*$E$8+122*$E$9)+C22*E22*(F22*$ F$6+G22*$F$7+H22*$F$8+122*$F$9) Copy the formula for Jeddah (cell N22) to the other destinations (N23:N28). Did the formulas transfer correctly? The proper use of absolute cell refer- ences (for cells E6:F9) is key. Check your work against the solution in Figure 15-11: The Airline Project 497 30/06/2023 14:29

FIGURE 15-11: The formulas for calculating the operating costs AutoSave Off 20 Search File Home Insert Page Layout Formulas Data Review View Automate Help Acrobat Arial 10 A A Wrap Text General Paste BIU 3 A Merge & Center E Alignment ES Undo Clipboard Б Font R38 IX fx N Costs 21 Operating cost 22 =C22 D22 (F22*$E$6+G22*$E$7+H22*$E$8+122*$E$9)+C22*E22*(F22*$F$6+G22*$F$7+H22*$F$8+122*$F$9) 23 =C23 D23 (F23*$E$6+G23*$E$7+H23*$E$8+123*$E$9)+C23 E23"(F23*$F$6+G23*$F$7+H23*SF$8+123*$F$9) 24 =C24 D24"(F24*$E$6+G24*$E$7+H24*$E$8+124*$E$9)+C24 E24 (F24 $F$6+G24 $F$7+H24 SF$8+124*$F$9) 25 =C25 D25 (F25*$E$6+G25*$E$7+H25*$E$8+125*$E$9)+C25 E25 (F25*$F$6+G25 $F$7+H25*SF$8+125*$F$9) 26 =C26 D26 (F26*$E$6+G26*$E$7+H26 $E$8+126 *$E$9)+C26 E26*(F26*$F$6+G26 $F$7+H26 SF$8+126*$F$9) 27 C27 D27"(F27*$E$6+G27*$E$7+H27*$E$8+127*SE$9)+C27*E27 (F27*$F$6+G27 $F$7+H27 $F$8+127*$F$9) 28 C28 D28 (F28*$E$6+G28*SE$7+H28*$E$8+128*$E$9)+C28 E28 (F28*$F$6+G28*$F$7+H28*SF$8+128*$F$9) 29 SUM(N22 N28) 30 21 Total cost The Operating costs for each of the seven destinations are added to determine the Total operating cost in cell N29 (see Figure 15-12). Cell N29 is blue because it is an Optimization cell. FIGURE 15-12: Calculations and results section AutoSave Of Search File: Home Insert Page Layout Formulas Data Review View Automate Help Acrobat X Anal 10 A A Paste BIU -A- 11 h Σ Autosum 4 == Wrap Text General AY Fill Merge & Center % 5898 Conditional Format as Cell Formatting Table Styles Insert Delete Format Sort & Clear Filter Font E Alignment Number Styles Cells Editing Undo Clipboard L R37 1Xvfx A 19 20 Calculations and results section H M N Daily demand Plane assignment Destination Distance from Riyadh hub Daily passenger Daily cargo shipments Bombardier bookings (m) Boeing 717 CRJ700 Embraer E170 Airbus A220 Plane utilization Total % of passenger passenger capacity capacity used Costs Total cargo % of cargo capacity capacity (m used Operating cost Jeddah 941 750 450 1 1 398 188% 634 71% SAR 1,942,082 85 Medina 837 500 450 1 1 1 1 398 126% 634 71% SAR 1,286,343.45 Ad Damman 408 550 600 1 1 1 1 398 138% 634 95% SAR 735,705 60 Rafha 767 100 450 1 1 1 1 398 25% 634 71% SAR 532,029.55 26 Yanbu 1045 750 300 1 1 1 1 398 188% 634 47% SAR 1,988,530.50 27 Abha 948 500 225 1 1 1 1 398 126% 634 35% SAR 1,228,062.90 201 Najran 944 1250 150 1 1 1 1 398 314% 634 24% SAR 2,639,376.80 Total/Avg 4400 2625 7 7 7 7 2786 158% 4438 59% SAR 10,352.131.65 Total cost CHECK YOUR PROGRESS Compare your calculation results with the values shown. If yours are different, carefully re-check your formulas and cell addresses. Income Statement Section The Income statement section (see Figure 15-13) is a projection of daily gross profits and is based on the mix of planes that will be assigned either manually or by Solver. 498 15 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 498 30/06/2023 14:29

وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 499 FIGURE 15-13: Income statement section 31 AutoSave On File Home Insert Page Layout Formulas Data Re X Cut Arial 11 AA Paste Copy BIU v Format Painter Clipboard E Font B54 fx A C D E 32 Income statement section 33 Passenger revenues 34 Cargo revenues 35 Total revenues 36 Less operating costs 37 Daily gross profit 38 30 • . . ger Passenger revenues: This value is calculated by multiplying the passen- tickets booked for each destination (cells D22:D28) by their respec- tive average ticket prices (cells C12:C18), and then totaling the ticket. revenues for the seven destinations. =$C$12*D22+$C$13*D23+$C$14*D24+$C$15*D25+$C$16*D26+$C$ 17*D27+$C$18*D28 Cargo revenues: This value is calculated by multiplying the daily cargo shipments for each destination (cells E22:E28) by their respective air freight prices (cells D12:D18), and then totaling the cargo revenues for the seven destinations. =(E22*$D$12)+(E23*$D$13)+(E24*$D$14)+(E25*$D$15)+(E26*$D$ 16)+(E27*$D$17)+(E28*$D$18) Total revenues: This value is the total of Passenger revenues, and Cargo revenues, (C33+C34). 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 (C35-C36). Cell C37 should be given a blue fill color because it is an Optimization cell. If your formulas are correct, the initial Income statement section will appear as shown in Figure 15-14. The Airline Project 499 30/06/2023 14:29

AutoSave On CHECK YOUR PROGRESS Compare your calculation FIGURE 15-14: Completed Income statement section り results with the values File Home Insert Page Layout Formulas Data Rev shown. If yours are different, carefully re-check ✗Cut Arial 12 AA your formulas and cell Copy Paste BIU Format Painter addresses. Clipboard TS Font גן B68 fx A D ELI 31 32 Income statement section 33 Passenger revenues SAR 10,592,500.00 34 Cargo revenues SAR 11,910,000.00 35 Total revenues SAR 22,502,500.00 36 Less operating costs SAR 10,352,131.65 37 Daily gross profit SAR 12,150.368.35 38 39 500 15 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 500 The initial Income statement correctly reflects the revenues expected from the passenger and cargo bookings, but the operating costs are not correct because the planes required to transport the passengers and cargo have not been assigned yet. Initially, you entered "1" for the number of plane assignments. We now need to estimate how many planes will be assigned to serve each of the company's destinations. Attempting a Manual Solution Start by assigning your plane fleet manually in the spreadsheet. Change the "1" values to numbers that seem more reasonable to you. You have several good reasons for doing this. First, you can make sure your model is working correctly before spreadsheet should automatically update other related cells. Second, assigning the plane fleet manually will demonstrate which constraints you must meet in solving the problem. you set up Solver to run. As you change the plane assignments your 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 des- tination. 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. Adjust your plane assignments to improve the percentage passenger and percentage capacities. Do this by entering different values for each of the plane assignments and watch the change in the % Passenger capacity. Start with Boeing and change the "1" to different values. Then move on and change the Bombardier, Embraer, and Airbus values. Don't worry if you make a mistake; you will change these values multiple times. Try to assign the planes in such a way as 30/06/2023 14:29

QUICK TIP The Solver optimization tool should give you a better solution than assigning the fleet manually. to maximize the % Passenger capacity without exceeding 100%. Remember that you don't need to assign all the plane types to each destination. You could assign zero Airbus A220s to Yanbu and Abha. You should change your assign- ments until you think you've maximized your % Passenger capacity. 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 prob- ably assign the fleet manually and meet all of your constraints, but will your total operating cost be the least expensive solution? Repeat the process you followed above and try to maximize the % Cargo capacity. Keep an eye on the % Passenger though. Figure 15-15 shows a sample manual solution. Note the number of Airbus A220 exceeds the number available. The Total passenger capacity is below 100%, but the capacity at several destinations exceeds 100%. This manual analysis needs to be reworked. وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 501 FIGURE 15-15: Sample manual solution W AutoSave Off File Home Insert Page Layout Formulas Data Review View Search Automate Help Acrobat Arial 12 ~AA Paste 8 1 U- Tim Wrap Text Merge & Center General Font SA Alignment 140 Condition Formattin Б Number Б 20 Undo Clipboard Б F16 fx G H M N Plane assignment Bombardier Boeing 717 CRJ700 Embraer E170 Total passenger Plane utilization % of passenger Costs Total cargo % of cargo capacity capacity Airbus A220 capacity capacity used (m) used Operating cost 5 3 1 850 88% 914 49% SAR 4,910,185.05 1 2 0 1 406 123% 632 71% SAR 1,276,634.25 1 1 4 1 608 90% 778 77% SAR 1,307,864.40 2 1 1 1 515 19% 919 49% SAR 679,677.05 3 0 3 1 694 108% 1254 24% SAR 3,628,449.00 1 3 0 0 351 142% 423 53% SAR 1,258,351.50 4 0 1 5 1203 104% 2463 6% 13 12 12 10 4627 95% 7383 SAR 6,399,800.80 36% SAR 19,460,962.05 Total cost 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:L28 must be equal to or greater than the Daily demand values in cells D22:D28. If you have satisfied the passenger and air freight demands correctly, none of the utilization rates in cells K22:K28 and M22:M28 will exceed 100%. In addition, the total planes assigned for each type (cells F29:129) cannot exceed the available number of each plane type (cells G6:G9). Working the problem out manually will provide an initial operating cost to which you can compare your Solver solution later. The Airline Project 501 30/06/2023 14:29

Once you reach a solution that satisfies the preceding constraints, save your workbook. Name the worksheet Airline Company Guess. Then, right-click the worksheet name tab, click Move or Copy, and check Create a Copy to copy the worksheet. Right-click the tab of your new worksheet and rename it (see Figure 15-16) Airline Company Solver. You will use the new worksheet to complete the next part of the lesson. FIGURE 15-16: Copying and renaming the worksheet Insert... Delete X 18 | Najran SAR 2,450.00| SA n° Move or Copy ? 70,00 19 35.00 Insert 20 Calculations and results section Move selected sheets 75.00 Delete To book: 75.00 Rename 70.00 21 Destination Distance from Riyadh hub pa Move or Copy.... Chapter 6 data file.xlsx Before sheet: Bename 10.00 55.00 Move or Copy.... 22 Jeddah 941 Airline Company Guess View Code View Code (move to end) ally demai 23 Medina 837 Da Protect Sheet... 24 Ad Damman 408 Protect Sheet... ger sh Bon 25 Rafha 767 gs Iab Color > C Tab Color 750 26 Yanbu 1045 500 Hide Airline Company Guess Hide 550 Unnide Create a copy 100 Ready Accessibility: Investigate Unhide 750 Guess (2) Select All Sheets Select All Sheets OK Cancel 502 15 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 502 Setting Up and Running Solver Now, begin working in the spreadsheet you just created. Before using the Solver Parameters window, you should make notes of the parameters you must define and their cell addresses. For this project, you should identify the following: . . The cell you want to minimize (Total cost, cell N29, which has a blue fill color). The cells you want Solver to manipulate to obtain the optimal solution (Plane assignment, cells F22:128). 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:129) For this project, you should identify the following: the number of avail- able planes of each type (cells G6:G9). The Total passenger capacity to each destination (cells J22:J28) must be equal to or greater than the total passenger bookings for each destination (cells D22:D28). The Total cargo capacity to each destination (cells L22:L28) must be equal to or greater than the total cargo shipment for each destina- tion (cells E22:E28). 30/06/2023 14:29

CHECK YOUR PROGRESS Your constraints should look like: $F$22:$I$28 = integer $F$22:$I$28 >= 0 Next, set up your problem. In the Analysis group on the Data tab, click Solver; the Solver Parameters window will appear, as shown in Figure 15-17. FIGURE 15-17: The Solver Parameters window $F$29:$I$29 <= $G$6:$G$9 Solver Parameters Set Objective: N29 To: Max Min Value Of: 0 By Changing Variable Cells: Minimize $J$22:$J$28 >= $D$22:$D$28 $L$22:$L$28 >= $E22:$E$28 Subject to the Constraints: Total cost Force a non-negative number Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Solving Method وزارة التعليم Ministry of Education 2024-1446 Add Change Delete Х ← 1 Reset All Load/Save Options Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close Enter the cell address (N29) that contains the Total cost in the box. Then click the Min button to specify that the goal is to minimize the Total operating cost. Enter your Changing cells (cells F22:128) into the By Changing Variable Cells box. Finally, add the constraints from the preceding list in the Add Constraints window (see Figure 15-18). FIGURE 15-18: Add your constraints one at a time Add Constraint Business Decision Making S1 S2 S3.indb 503 Cell Reference: ← <= > Constraint: Х + OK Add Cancel Click the Add button to enter the constraints one at a time. Click the OK button The Airline Project 503 30/06/2023 14:29

after entering the last constraint. Select the Simplex LP solving method. If you need help defining your constraints, refer to Chapter 11. Your Solver Parameters window should now match the one in Figure 15-19. FIGURE 15-19: The Solver Parameters window after the data has been entered Solver Parameters 504 15 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 504 Set Objective: To: Max By Changing Variable Cells: $F$22:$I$28 Subject to the Constraints: $F$22:$I$28 integer $F$22:$I$28 >= 0 $F$29:$I$29 <= $G$6:$G$9 $J$22:$J$28 >= $D$22:$D$28 $L$22:$L$28>= SE$22:$E$28 SN$29 Min Value Of: 0 Make Unconstrained Variables Non-Negative Select a Solving Method: Solving Method Simplex LP < Add Change Delete Reset All Load/Save 1 Options Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve <- Close Next, you should click the Options button and check the Options window that appears (see Figure 15-20). The default Integer Optimality is 5%; change it to 1% to get a better answer. Make sure the Constraint Precision is set to the default value of 0.000001 and that the Use Automatic Scaling option is checked. When you have set the options, click OK to return to the Solver Parameters window. 30/06/2023 14:29

FIGURE 15-20: The Solver Options window Options All Methods GRG Nonlinear Evolutionary Constraint Precision: Use Automatic Scaling Show Iteration Results Solving with Integer Constraints Ignore Integer Constraints Integer Optimality (%): Solving Limits Max Time (Seconds): Iterations: Evolutionary and Integer Constraints: Max Subproblems: Max Feasible Solutions: 0.000001 1 ? X OK Cancel QUICK TIP If you set up Solver correctly, you should see a reduction in total cost from your manual assignment. 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. For the rest of the case, continue copying and renaming the worksheets. Before continuing, examine the plane assignments that Solver chose for minimizing the total cost. What if, instead of minimizing costs, you were interested in maximizing reve- nues? In other words, is gross profit greater than that in the earlier solutions? Rerunning Solver to Maximize Gross Profit You now have a lot of experience working with DSS and Solver. So, before you run Solver a final time to maximize gross profit, ask yourself: What result do I expect to produce? Then, copy the worksheet that contains the solution for minimum operating costs, and rename the new worksheet Airline Company Solver Max Daily Profit. Click Solver to open the Solver Parameters window, and then change the value in the Set Objective box to $C$37 (the blue cell that contains Daily gross وزارة التعليم Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 505 The Airline Project 505 30/06/2023 14:29

profit). Click the Max button to maximize the Set Objective value, as shown in Figure 15-17 earlier, and then run Solver. Click Answer Report and then click OK to create a second Answer Report. As with the prior Solver model, this solution will take some time to complete. Be patient as Excel works through the various sub-problems and trial solutions. You will see these calculations scrolling in the bottom-left corner of your screen (Figure 15-21): FIGURE 15-21: Answer Report calculations 45 46 Airline Company Guess Airline Company Solver Airline Company Solver 2 Subproblem: 73895 Trial Solution: 8 Objective Cell: SAR 1.06 Examine the reports and solutions that Solver generated to see if maximizing daily gross revenues provides a better plane assignment solution than the solu- tions that minimized total operating costs. Was the result the same, or different, from the one you expected? Why do you think that is? REVIEW QUESTIONS 1. Which is the best way for the airline to measure passenger capacity? a. Number of passengers b. Number of seats c. Number of tickets sold d. Number of bags 2. Which is the best way for the airline to measure cargo capacity? a. Number of bags b. Number of passengers c. Volume of cargo d. Duration of flight 506 15 Chapter رة ا Ministry of Education 2024-1446 Business Decision Making S1 S2 S3.indb 506 30/06/2023 14:29