Purpose: Conducting sensitivity analysis in a multi-objective optimization problem.
a. Consider the multi-objective optimization problem discussed in Checkpoint 3.3. The data is given in the following table. Rank the plans according to the weighted average dose to the healthy organs (lowest to greatest). Make sure to include your calculations.
Organ Heart Spinal cord Kidney
Weight 0.6 0.3 0.1
Plan 1 100 Sx 40 Gy 50 Gx
Plan 2 80 Sx 50 Sx 30 Gx
Plan 3 no fit 30 Gy io Sx
a. The best plan according to the current weights is Plan 2. Change the weights so that Plan 1 becomes the best plan (make sure weights sum to 1).
b. Change the weights so that Plan 3 becomes the best plan (make sure weights sum to 1).
c. Reset the weights to the base values in the above table. Assume the weight of kidney must remain at 0.1 (therefore the weights of heart and spinal cord must sum to 0.9). If you gradually reduce the weight of heart and increase the weight of spinal cord, at some point, the best plan will no longer be Plan 2. Find that point at which the best plan switches. Hint: You can use the Goal Seek tool in Excel as well.

Purpose: Conducting sensitivity analysis in a multi-objective optimization problem Grader – Instructions Excel 2016 Project
YO_Excel16_Ch01_PS2_v2
Project Description:
Darin Learn recently open a small sporting goods store next to the local community college. He wants to use Excel to track supply inventory, the total cost of inventory, as well as items on back order. Darin has created a workbook containing a list of the products in inventory, a list of items on back order, and a documentation worksheet. You have been asked enhance the appearance of the worksheets, edit content, insert comments, and improve overall formatting, making the information easier to review and analyze.
Steps to Perform:
Step Instructions Points Possible
1 Start Excel. Open the file named e01ch01_grader_a1_Athletics.xlsx. Save the file with the name e01ch01_grader_a1_Athletics_LastFirst using your last and first name. 0
2 Rename Sheet1 as Inventory. Rename Sheet2 as BackOrders. 8
3 On the Inventory worksheet, in cell A27, insert the comment Unisex. 6
4 In cell A17, edit the contents to read Inventory. 4
5 Center align and wrap the text in the following cell ranges: B4:L4, B10:L10, B17:D17, and B25:D25. 6
6 In cell A25, using the ALT and ENTER keys, insert a hard return between Miscellaneous Items. Be sure to remove the space. 6
7 Insert a column to the left of column E. Insert a column to the left of column J. Change the width of columns E and J to 5. 6
8 Copy the contents of cell D18 down through D23. Copy the contents of cell D26 down through D28. 8
9 Move the information in the cell range A25:D28 to F17:I20. 2
10 Merge and center the contents in cell A1 through the range A1:N1. 6
11 Delete row 3. Change the color of the Inventory worksheet tab to Red. 4
12 Change to the width of the worksheet columns as follows: Columns A, F, and K to 17 Columns B, C, D, G, H, I, L, M, N to 8 8
13 On the BackOrders worksheet tab, apply Center Across Selection to range A1:D2. 5
14 In cell C6, enter Date Ordered as 12/15/18. In cell D6, enter the Date to be Received as 12/29/18. Use the AutoFill handle to copy cell range C6:D6 through C8:D8. 8
15 Delete Sheet3. 5
16 On the Inventory worksheet, insert a page header into the middle section. Type All Sports Athletics. Insert the file name code into the left footer of all worksheets in the workbook. Return all worksheets to Normal view. 5
17 On the Documentation worksheet, in cell A8, type today’s date. In B8, type your name in Firstname Lastname format. In cell C8, type Formatted Inventory worksheet In cell C9, type Updated the BackOrders worksheet 3
18 For the Inventory and Documentation worksheets, set Orientation to Landscape, and set the scaling to Fit All Columns on One Page. 10
19 Save the workbook, exit Excel, and then submit your files as directed by your instructor. 0