Figure 1 is a schematic of the plan of a rainwater harvesting system of a public facility located in a high-rainfall region of the New Orleans. Rainwater is collected over the 302 m2 roof and is collected into reservoirs A and D. Water. Overflow from these two reservoirs then flows into reservoirs B and Cas shown on the Figure. Reservoirs B and C spill off any excess water to the ground. All the four reservoirs are cylindrical with internal heights and diameters of 1.41 and 1.50 m respectively. The following data were collected as part of a study on the performance of the system. Daily water supply from the four reservoirs The daily levels of water in the four reservoirs The daily rainfall recorded by a rain gauge station located within 50 metres of the facility. The spreadsheet contains this data for the period 10 December 2019 to 10 January 2020. A. Using the provided data, determine the efficiency of rain water collection of the system using an optimization approach that seeks to minimize the differences between the measured daily water levels and the simulated water levels of the reservoirs. Apply daily time-step simulation of water levels based on the Behaviour B. Analysis method and assume that the proportions of the roof area draining into reservoir A and D are not necessarily equal (to 0.5) and could range from 0.1 to 0.9. Additionally, consider that the respective rainwater collection efficiencies for the roof areas draining to the two reservoirs could be unequal and range from 0.3 to 0.9. Solve the problem on Ms Excel spreadsheet applying the GRG nonlinear and the evolutionary optimizers of the Solver Add-in. Compare the results obtained from the two optimizers and inform which of the solutions (if any) seems more realistic. In addition, also inform what additional information you would obtain in order to verify the solutions obtained from the optimization. A, B, C, D are 2.5m3 reservoirs Diameter = 1.50 m Height = 1.41 m 1 Supply from reservoir Roof Area = 302 m2 Inflow from roof into reservoir Overflow from one reservoir to another Spillage from reservoir Figure 1. Schematic of plan of rain water harvesting system. Date Rainfall (mm/day) 5 5 5 blololo 3 70,1 110 118 10 Supply (Litres/Day) from 5 pm of the previous day to 5 Water Level (cm) at 5 pm pm of the current day. Reservoir A Reservoir B Reservoir C Reservoir D Reservoir A Reservoir B Reservoir C Reservoir D 141 124 141 141 131,9 51,9 56,8 115,7 141 131 141 141 48,3 60 37,9 13 141 131 141 141 33,8 42,9 53,4 10,7 141 133 136 141 289,1 134 0,9 0,9 121 128 141 136 25,4 2,7 81,9 22,1 121 127 134 136 8 21,9 151,5 0 120 125 125 136 146,9 6 98,9 19,1 111 112 119 134 87,5 2,4 197,5 167,9 120 108 123 123 193,7 242,6 85,5 110 94 118 118 0 0 0 0 94 118 0 0,1 6,1 5,2 141 141 125 122 0 0 0 0 141 141 125 122 43,1 17 43,6 28,2 136 115 120 123 53,8 63 59,6 4,9 131 111 117 123 12,2 28,8 99,7 0 141 141 140 141 16,8 12,5 1,6 2,2 141 141 139 141 15,3 3,5 0,9 0 141 141 139 141 38,5 12,4 0 16,3 141 141 139 137 14 12,1 0 1,1 141 140 141 93,5 114,6 75,8 4,1 135 131 135 141 50 60,5 18 135,1 141 131 140 132 40,3 20,9 -0,5 7,5 137 130 139 132 58,6 47,1 63,5 10,4 138 131 135 133 53,5 305 11,4 137 128 118 132 65,5 4,6 0,7 0 133 127 118 132 19,7 15,5 0 0 130 127 118 132 9,7 85,7 63,6 0 141 126 128 133 1,1 178,1 165,1 47,5 141 132 141 132 0 0 19,6 141 141 141 141 118,2 133,1 64,3 4,6 141 141 141 0 53,5 65,6 0 141 141 141 141 10-Dec-19 11-Dec-19 12-Dec-19 13-Dec-19 14-Dec-19 15-Dec-19 16-Dec-19 17-Dec-19 18-Dec-19 19-Dec-19 20-Dec-19 21-Dec-19 22-Dec-19 23-Dec-19 24-Dec-19 25-Dec-19 26-Dec-19 27-Dec-19 28-Dec-19 29-Dec-19 30-Dec-19 31-Dec-19 01-Jan-20 02-Jan-20 03-Jan-20 04-Jan-20 05-Jan-20 06-Jan-20 07-Jan-20 08-Jan-20 09-Jan-20 10-Jan-20 10 10 5 8 2 141 4 6 62,6 5 9,5 15 20 135 Figure 1 is a schematic of the plan of a rainwater harvesting system of a public facility located in a high-rainfall region of the New Orleans. Rainwater is collected over the 302 m2 roof and is collected into reservoirs A and D. Water. Overflow from these two reservoirs then flows into reservoirs B and Cas shown on the Figure. Reservoirs B and C spill off any excess water to the ground. All the four reservoirs are cylindrical with internal heights and diameters of 1.41 and 1.50 m respectively. The following data were collected as part of a study on the performance of the system. Daily water supply from the four reservoirs The daily levels of water in the four reservoirs The daily rainfall recorded by a rain gauge station located within 50 metres of the facility. The spreadsheet contains this data for the period 10 December 2019 to 10 January 2020. A. Using the provided data, determine the efficiency of rain water collection of the system using an optimization approach that seeks to minimize the differences between the measured daily water levels and the simulated water levels of the reservoirs. Apply daily time-step simulation of water levels based on the Behaviour B. Analysis method and assume that the proportions of the roof area draining into reservoir A and D are not necessarily equal (to 0.5) and could range from 0.1 to 0.9. Additionally, consider that the respective rainwater collection efficiencies for the roof areas draining to the two reservoirs could be unequal and range from 0.3 to 0.9. Solve the problem on Ms Excel spreadsheet applying the GRG nonlinear and the evolutionary optimizers of the Solver Add-in. Compare the results obtained from the two optimizers and inform which of the solutions (if any) seems more realistic. In addition, also inform what additional information you would obtain in order to verify the solutions obtained from the optimization. A, B, C, D are 2.5m3 reservoirs Diameter = 1.50 m Height = 1.41 m 1 Supply from reservoir Roof Area = 302 m2 Inflow from roof into reservoir Overflow from one reservoir to another Spillage from reservoir Figure 1. Schematic of plan of rain water harvesting system. Date Rainfall (mm/day) 5 5 5 blololo 3 70,1 110 118 10 Supply (Litres/Day) from 5 pm of the previous day to 5 Water Level (cm) at 5 pm pm of the current day. Reservoir A Reservoir B Reservoir C Reservoir D Reservoir A Reservoir B Reservoir C Reservoir D 141 124 141 141 131,9 51,9 56,8 115,7 141 131 141 141 48,3 60 37,9 13 141 131 141 141 33,8 42,9 53,4 10,7 141 133 136 141 289,1 134 0,9 0,9 121 128 141 136 25,4 2,7 81,9 22,1 121 127 134 136 8 21,9 151,5 0 120 125 125 136 146,9 6 98,9 19,1 111 112 119 134 87,5 2,4 197,5 167,9 120 108 123 123 193,7 242,6 85,5 110 94 118 118 0 0 0 0 94 118 0 0,1 6,1 5,2 141 141 125 122 0 0 0 0 141 141 125 122 43,1 17 43,6 28,2 136 115 120 123 53,8 63 59,6 4,9 131 111 117 123 12,2 28,8 99,7 0 141 141 140 141 16,8 12,5 1,6 2,2 141 141 139 141 15,3 3,5 0,9 0 141 141 139 141 38,5 12,4 0 16,3 141 141 139 137 14 12,1 0 1,1 141 140 141 93,5 114,6 75,8 4,1 135 131 135 141 50 60,5 18 135,1 141 131 140 132 40,3 20,9 -0,5 7,5 137 130 139 132 58,6 47,1 63,5 10,4 138 131 135 133 53,5 305 11,4 137 128 118 132 65,5 4,6 0,7 0 133 127 118 132 19,7 15,5 0 0 130 127 118 132 9,7 85,7 63,6 0 141 126 128 133 1,1 178,1 165,1 47,5 141 132 141 132 0 0 19,6 141 141 141 141 118,2 133,1 64,3 4,6 141 141 141 0 53,5 65,6 0 141 141 141 141 10-Dec-19 11-Dec-19 12-Dec-19 13-Dec-19 14-Dec-19 15-Dec-19 16-Dec-19 17-Dec-19 18-Dec-19 19-Dec-19 20-Dec-19 21-Dec-19 22-Dec-19 23-Dec-19 24-Dec-19 25-Dec-19 26-Dec-19 27-Dec-19 28-Dec-19 29-Dec-19 30-Dec-19 31-Dec-19 01-Jan-20 02-Jan-20 03-Jan-20 04-Jan-20 05-Jan-20 06-Jan-20 07-Jan-20 08-Jan-20 09-Jan-20 10-Jan-20 10 10 5 8 2 141 4 6 62,6 5 9,5 15 20 135