Sheet1 Sheet2 Historical sales data 9 3 2231 976 4 2861 677 12 1986 1067 UAE Oman Kuwait KSA Bahrain 1 1864 1411 649 4620 361 2 2489 1014 500 5518 490 Week 6 1347 926 $21 1370 632 5 2144 686 566 S017 869 7 2219 759 465 2994 1267 10 1971 1239 542 6761 401 1548 1076 572 7142 770 $35 11 2106 672 507 6994 705 1534 894 480 4045 992 $14 752 3762 727 7800 573 3866 884 Sheet1 Sheet2 + Demand 121 27 E 18 1 2 3 4 5 . 7 130 103 63 X ISI 37 9 10 11 65 12 112 65 78 El 14 15 16 17 18 19 20 139 99 131 120 85 63 129 104 113 163 IZ 22 23 24 25 26 27 SIL 98 16 79 91 136 70 89 27 S6 19 30 21 10 11 34 35 16 17 18 39 40 137 00 SEI 94 92 72 R? 124 3 1+ 42 43 44 ST 125 101 46 48 49 50 SI 52 SU 3 5:22 ISS 44 55 95 121 57 0 65 106 Sheet1 Sheet2 59 09 61 62 63 64 65 106 75 77 73 79 76 95 22 68 67 65 69 70 71 72 73 74 75 76 77 78 79 72 88 IM 87 147 84 SI! 191 68 19 40 R2 114 6R 122 R2 92 81 69 EN 14 RS 36 7 191 WO 130 TOR 136 92 99 16 04 92 93 94 95 96 97 81 112 56 30 114 98 001 100 101 137 128 145 63 96 102 76 126 119 126 18 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 217 09 46 114 139 106 58 29 84 Sheet1 Sheet2 112 53 57 65 101 72 119 117 124 98 $7 124 125 126 127 12% 129 110 131 132 133 144 115 116 137 138 119 140 141 142 143 144 145 146 147 145 73 135 $3 SS 150 151 152 153 194 155 156 157 198 IS2 119 128 132 140 140 103 147 129 R3 90 134 IS2 67 63 67 87 121 92 144 90 151 68 49 153 73 169 77 651 160 161 162 163 164 165 166 167 168 169 170 171 172 0 63 71 174 175 176 177 176 179 49 77 128 105 152 97 87 111 118 126 95 11 OSI 181 182 163 184 185 4:55 Case 1 Epson V2 - Team A-data XLSX - 17 KB Sheet1 Sheet2 176 177 178 179 180 151 12 183 184 IRS 136 16 189 190 191 192 193 194 195 196 197 195 199 200 201 202 203 204 205 206 2017 2008 209 210 211 212 213 214 215 91 128 105 152 97 87 111 118 126 95 123 123 141 97 97 54 134 3 145 101 6 ? 107 106 14 154 107 83 136 133 116 99 150 104 142 100 114 110 99 122 28 Use Office to work on this file Get Office 4:55 Case 1 Epson V2 - Team A-data XLSX - 17 KB Sheet1 Sheet2 25 227 228 229 210 231 212 213 234 215 216 237 23 219 240 241 242 77 14 TOK 102 74 103 TOS 149 100 81 41 121 IOS 811 ETT 140 90 99 82 93 RR 88 28 162 SIL 244 245 246 247 245 249 250 251 232 253 254 255 256 257 258 259 200 261 22 263 264 265 26 97 117 110 90 127 93 61 104 34 X1 82 13 59 130 136 2 93 ! Use Office to work on this file Get Office 140 90 66 16 240 241 242 243 244 245 246 247 248 249 250 25 88 NR 28 162 118 97 117 110 90 127 93 19 104 34 81 82 13 253 254 255 256 257 255 239 260 261 262 26 264 265 266 267 26 269 270 271 130 136 7R 93 65 101 92 111 78 67 88 71 78 08 1 149 49 16 96 65 273 274 275 276 277 278 279 250 281 262 281 284 25 236 287 263 289 290 2991 292 293 294 295 295 297 298 123 51 78 132 152 99 127 RS 95 300 301 302 303 304 305 306 107 TOK 156 TOS 92 7 84 40 28 94 76 141 147 175 100 ZN 269 290 291 292 293 294 SO 152 66 127 85 56 65 TOR 156 105 92 78 84 40 28 29 297 16 76 141 147 175 100 300 B01 302 303 304 305 306 307 300 309 310 311 312 313 314 315 316 317 318 319 TOS 64 123 46 142 126 151 149 104 64 126 OR 76 124 OCE $6 27 97 S6 322 323 324 325 326 327 328 329 110 331 332 333 314 335 136 115 127 24 99 LEE SEE 339 71 123 47 124 155 49 118 146 77 102 12 52 72 TOS 44 86 Oh 141 542 343 344 345 19 347 34 350 351 352 127 94 00 111 81 66 ESE 68 354 355 356 100 4:55 Case 1 Epson V2 - Team A-data XLSX - 17 KB 305 306 907 147 175 100 88 105 64 123 46 142 126 151 149 309 310 311 312 313 314 315 316 317 31% 319 320 321 322 1 101 324 325 336 327 33% 126 OR 76 124 95 27 97 95 115 127 24 66 71 123 47 124 155 49 118 146 77 310 331 333 333 FEL 315 336 137 338 339 340 201 it 142 343 344 345 146 347 12 52 72 TOS 46 M6 61 MTU Use Office to work on this file Get Office 297 299 300 301 300 100 101 305 306 307 MS 109 310 311 312 313 314 315 316 317 318 319 320 321 322 92 78 84 40 28 94 76 141 14? 175 100 RR 105 64 123 46 14 126 IST 611 524 325 104 66 126 98 76 124 95 27 97 95 115 127 24 66 71 123 327 328 330 TEE 212 333 334 SEE 336 337 318 339 340 124 ISS 49 118 146 201 12 $2 72 SOT 342 340 144 345 346 47 44 86 19 122 94 615 06 111 350 351 352 350 18 06 68 SSE 356 357 100 77 87 73 49 72 173 359 360 361 Epson's management decided to conduct a study to improve the inventory management at its facility (warehouse)inthe UAE. The management wants to achieve a target service level of 90%. In the attached Excel file (sheet2), you find the order (=demand) data for the last year (365 days) & Plot the demand data. What is the average demand and standard deviation of the demand? Please make the following assumptions. Assume that: on day 1, the starting (on-hand) inventory level was 200; the lead time is 1 day, which means that anorder that is placed in periodt will be delivered in period t+1; the following current order and replenishment policy for the whole year. Delivery Day 1 2 3 Order 300 0 0 300 4 300 0 0 300 0 0 300 5 6 7 8 9 10 300 300 0 0 Etc BC etc h. Calculate the inventory level for each day and plot these data (the inventory level over time). Describe what you observe. Hint: Inventory level (t) = inventory level (t-1)-Demand (t) + Delivery (t) 1. What was the servicelevel that the UAE facility was able to achieve last year? Hint the service level is the average non-stock probobility. In the last question of this case, you are expected to improve the inventory management practice of the UAE facility. You can still assume the same historical demand data and that the lead time is 1 day. Also, the system can be considered to bea periodic review system with a review period of O days, as the inventory level is Inspected each day. Also, the target service level is still90%. 1 Recommend the appropriate inventory management policy for the UAE facility. Please describe this policy indetail with the policyparameter(s). k Whatisthe new service level after this policy (as suggested in answer )isimplemented? 1. Given that each printer costs Epson 200 AED and the holding costis 25 percent, how much cost saving will be achieved by the policy suggested in answer j? You can ignore backorder or penaltycosts for shortages and assure the same costincase the inventory level is negative. Please answer questions a. til L. in a report form. You need to dearly explain in the report how each question has been answered and you need to attach the Excel file with all calculations. The deadline is to submit your report (Word and Excel file) and presentation is on Wednesday February 91 at 3:30 pm. We shall have presentations on the same day during class. Epson produces printers in its Taiwan factory for sale in the GCC countries. Currently, Epson assembles and packs printers for sale in individual countries (UAE, Oman, Kuwait, KSA, and Bahrain). The attached Excel file provides historical data and includes weekly demand for Epson printers for the last 12 weeks in each of the countries (see sheet 1). Assume that demand in the different countries is independent. Table 1 Weekly Demand (Historical data) for Epson Printers in GCC Week 1 2 3 4 5 6 7 8 9 10 11 12 UAE Oman Kuwait KSA See Excel file Bahrain Questions: a. Given that the lead time from the Taiwan factory is eight weeks, how much safety stock should Epson store in GCCifit targets a service level of 95 percent? b. Epson decides to build a central DC in the GCC. It will ship allprinters for the GCC market to this central DC. When anorder is received, the printers will be shipped from the central DC to the appropriate country. The printers are still manufactured in Taiwan and the lead time from the factory to the central DC is still 8 weeks. The target service level remains 95% and you can assume that the lead time from the central DC to the customers is zero. How much safety stockscan Epson save (in items) as a result of having the central DC? c. Each printer costs Epson 200 AED and the holding costis 25 percent. How much will Epson save (in AED) as a result of the central DC? d. Wat would be your recommendation to the management of Epson regarding the central DC? Please give a clear explanation. Suppose that Epsonis able to cut the production and delivery lead time from its Taiwan factory from 8 to 4 weeks by using better information systems. e. How much savings (in AED) willEpson make in the holding cost (without the central DC)? 1. How much savings (inAED) will Epson make in the holding cost (with the central DC)? Sheet1 Sheet2 Historical sales data 9 3 2231 976 4 2861 677 12 1986 1067 UAE Oman Kuwait KSA Bahrain 1 1864 1411 649 4620 361 2 2489 1014 500 5518 490 Week 6 1347 926 $21 1370 632 5 2144 686 566 S017 869 7 2219 759 465 2994 1267 10 1971 1239 542 6761 401 1548 1076 572 7142 770 $35 11 2106 672 507 6994 705 1534 894 480 4045 992 $14 752 3762 727 7800 573 3866 884 Sheet1 Sheet2 + Demand 121 27 E 18 1 2 3 4 5 . 7 130 103 63 X ISI 37 9 10 11 65 12 112 65 78 El 14 15 16 17 18 19 20 139 99 131 120 85 63 129 104 113 163 IZ 22 23 24 25 26 27 SIL 98 16 79 91 136 70 89 27 S6 19 30 21 10 11 34 35 16 17 18 39 40 137 00 SEI 94 92 72 R? 124 3 1+ 42 43 44 ST 125 101 46 48 49 50 SI 52 SU 3 5:22 ISS 44 55 95 121 57 0 65 106 Sheet1 Sheet2 59 09 61 62 63 64 65 106 75 77 73 79 76 95 22 68 67 65 69 70 71 72 73 74 75 76 77 78 79 72 88 IM 87 147 84 SI! 191 68 19 40 R2 114 6R 122 R2 92 81 69 EN 14 RS 36 7 191 WO 130 TOR 136 92 99 16 04 92 93 94 95 96 97 81 112 56 30 114 98 001 100 101 137 128 145 63 96 102 76 126 119 126 18 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 217 09 46 114 139 106 58 29 84 Sheet1 Sheet2 112 53 57 65 101 72 119 117 124 98 $7 124 125 126 127 12% 129 110 131 132 133 144 115 116 137 138 119 140 141 142 143 144 145 146 147 145 73 135 $3 SS 150 151 152 153 194 155 156 157 198 IS2 119 128 132 140 140 103 147 129 R3 90 134 IS2 67 63 67 87 121 92 144 90 151 68 49 153 73 169 77 651 160 161 162 163 164 165 166 167 168 169 170 171 172 0 63 71 174 175 176 177 176 179 49 77 128 105 152 97 87 111 118 126 95 11 OSI 181 182 163 184 185 4:55 Case 1 Epson V2 - Team A-data XLSX - 17 KB Sheet1 Sheet2 176 177 178 179 180 151 12 183 184 IRS 136 16 189 190 191 192 193 194 195 196 197 195 199 200 201 202 203 204 205 206 2017 2008 209 210 211 212 213 214 215 91 128 105 152 97 87 111 118 126 95 123 123 141 97 97 54 134 3 145 101 6 ? 107 106 14 154 107 83 136 133 116 99 150 104 142 100 114 110 99 122 28 Use Office to work on this file Get Office 4:55 Case 1 Epson V2 - Team A-data XLSX - 17 KB Sheet1 Sheet2 25 227 228 229 210 231 212 213 234 215 216 237 23 219 240 241 242 77 14 TOK 102 74 103 TOS 149 100 81 41 121 IOS 811 ETT 140 90 99 82 93 RR 88 28 162 SIL 244 245 246 247 245 249 250 251 232 253 254 255 256 257 258 259 200 261 22 263 264 265 26 97 117 110 90 127 93 61 104 34 X1 82 13 59 130 136 2 93 ! Use Office to work on this file Get Office 140 90 66 16 240 241 242 243 244 245 246 247 248 249 250 25 88 NR 28 162 118 97 117 110 90 127 93 19 104 34 81 82 13 253 254 255 256 257 255 239 260 261 262 26 264 265 266 267 26 269 270 271 130 136 7R 93 65 101 92 111 78 67 88 71 78 08 1 149 49 16 96 65 273 274 275 276 277 278 279 250 281 262 281 284 25 236 287 263 289 290 2991 292 293 294 295 295 297 298 123 51 78 132 152 99 127 RS 95 300 301 302 303 304 305 306 107 TOK 156 TOS 92 7 84 40 28 94 76 141 147 175 100 ZN 269 290 291 292 293 294 SO 152 66 127 85 56 65 TOR 156 105 92 78 84 40 28 29 297 16 76 141 147 175 100 300 B01 302 303 304 305 306 307 300 309 310 311 312 313 314 315 316 317 318 319 TOS 64 123 46 142 126 151 149 104 64 126 OR 76 124 OCE $6 27 97 S6 322 323 324 325 326 327 328 329 110 331 332 333 314 335 136 115 127 24 99 LEE SEE 339 71 123 47 124 155 49 118 146 77 102 12 52 72 TOS 44 86 Oh 141 542 343 344 345 19 347 34 350 351 352 127 94 00 111 81 66 ESE 68 354 355 356 100 4:55 Case 1 Epson V2 - Team A-data XLSX - 17 KB 305 306 907 147 175 100 88 105 64 123 46 142 126 151 149 309 310 311 312 313 314 315 316 317 31% 319 320 321 322 1 101 324 325 336 327 33% 126 OR 76 124 95 27 97 95 115 127 24 66 71 123 47 124 155 49 118 146 77 310 331 333 333 FEL 315 336 137 338 339 340 201 it 142 343 344 345 146 347 12 52 72 TOS 46 M6 61 MTU Use Office to work on this file Get Office 297 299 300 301 300 100 101 305 306 307 MS 109 310 311 312 313 314 315 316 317 318 319 320 321 322 92 78 84 40 28 94 76 141 14? 175 100 RR 105 64 123 46 14 126 IST 611 524 325 104 66 126 98 76 124 95 27 97 95 115 127 24 66 71 123 327 328 330 TEE 212 333 334 SEE 336 337 318 339 340 124 ISS 49 118 146 201 12 $2 72 SOT 342 340 144 345 346 47 44 86 19 122 94 615 06 111 350 351 352 350 18 06 68 SSE 356 357 100 77 87 73 49 72 173 359 360 361 Epson's management decided to conduct a study to improve the inventory management at its facility (warehouse)inthe UAE. The management wants to achieve a target service level of 90%. In the attached Excel file (sheet2), you find the order (=demand) data for the last year (365 days) & Plot the demand data. What is the average demand and standard deviation of the demand? Please make the following assumptions. Assume that: on day 1, the starting (on-hand) inventory level was 200; the lead time is 1 day, which means that anorder that is placed in periodt will be delivered in period t+1; the following current order and replenishment policy for the whole year. Delivery Day 1 2 3 Order 300 0 0 300 4 300 0 0 300 0 0 300 5 6 7 8 9 10 300 300 0 0 Etc BC etc h. Calculate the inventory level for each day and plot these data (the inventory level over time). Describe what you observe. Hint: Inventory level (t) = inventory level (t-1)-Demand (t) + Delivery (t) 1. What was the servicelevel that the UAE facility was able to achieve last year? Hint the service level is the average non-stock probobility. In the last question of this case, you are expected to improve the inventory management practice of the UAE facility. You can still assume the same historical demand data and that the lead time is 1 day. Also, the system can be considered to bea periodic review system with a review period of O days, as the inventory level is Inspected each day. Also, the target service level is still90%. 1 Recommend the appropriate inventory management policy for the UAE facility. Please describe this policy indetail with the policyparameter(s). k Whatisthe new service level after this policy (as suggested in answer )isimplemented? 1. Given that each printer costs Epson 200 AED and the holding costis 25 percent, how much cost saving will be achieved by the policy suggested in answer j? You can ignore backorder or penaltycosts for shortages and assure the same costincase the inventory level is negative. Please answer questions a. til L. in a report form. You need to dearly explain in the report how each question has been answered and you need to attach the Excel file with all calculations. The deadline is to submit your report (Word and Excel file) and presentation is on Wednesday February 91 at 3:30 pm. We shall have presentations on the same day during class. Epson produces printers in its Taiwan factory for sale in the GCC countries. Currently, Epson assembles and packs printers for sale in individual countries (UAE, Oman, Kuwait, KSA, and Bahrain). The attached Excel file provides historical data and includes weekly demand for Epson printers for the last 12 weeks in each of the countries (see sheet 1). Assume that demand in the different countries is independent. Table 1 Weekly Demand (Historical data) for Epson Printers in GCC Week 1 2 3 4 5 6 7 8 9 10 11 12 UAE Oman Kuwait KSA See Excel file Bahrain Questions: a. Given that the lead time from the Taiwan factory is eight weeks, how much safety stock should Epson store in GCCifit targets a service level of 95 percent? b. Epson decides to build a central DC in the GCC. It will ship allprinters for the GCC market to this central DC. When anorder is received, the printers will be shipped from the central DC to the appropriate country. The printers are still manufactured in Taiwan and the lead time from the factory to the central DC is still 8 weeks. The target service level remains 95% and you can assume that the lead time from the central DC to the customers is zero. How much safety stockscan Epson save (in items) as a result of having the central DC? c. Each printer costs Epson 200 AED and the holding costis 25 percent. How much will Epson save (in AED) as a result of the central DC? d. Wat would be your recommendation to the management of Epson regarding the central DC? Please give a clear explanation. Suppose that Epsonis able to cut the production and delivery lead time from its Taiwan factory from 8 to 4 weeks by using better information systems. e. How much savings (in AED) willEpson make in the holding cost (without the central DC)? 1. How much savings (inAED) will Epson make in the holding cost (with the central DC)