Eli Orchid has designed a new pharmaceutical product, Orchid Relief, which improves the night sleep. Before initiating mass production of the product, Eli Orchid has been market-testing Orchid Relief in Orange County over the past 8 weeks. The daily demand values are recorded in the Excel file provided. Eli Orchid plans on using the sales data to predict sales for the upcoming week. An accurate forecast would be helpful in making arrangements for the companys production processes and designing promotions. Before a forecasting model is built and a forecast for the next week is generated, the COO of the company has asked the data analyst for an exploratory analysis of the demand. Specifically, the COO has asked the analyst1: 1. To provide a bar chart (with data labels rounded to two decimal points) showing the average demand for each week day (Sun., Mon., etc.) [add chart here] 2. To fit a simple linear regression model to the data and to provide its equation (d = a*t + b), along with R2 d = R2= 3. To fit a multiple regression model with a dummy variable representing the weekend, and to provide the regression equation (d = a*t + b*w + c), along with R2. d = R2= 4. To provide a run-series plot of the actual demand with simple regression and multiple regression overlay. [add chart here] 5. To write a short paragraph explaining the observations and providing general recommendations for the next seven days demand forecast. [write your paragraph here] 6. To fit a NEW multiple regression model with dummy variables for seven weekdays (not the weekend), and to provide the regression equation (d = a*t + b1x1 + b2x2 + b3x3 + b4x4 + b5x5 + b6x6 + c), along with Adjusted R2. d = R2= 7. To use all three models: M1: d = a*t + b M2: d = a*t + b*w + c M3: d = a*t + b1x1 + b2x2 + b3x3 + b4x4 + b5x5 + b6x6 + c to predict the demand for seven days ahead (Mon, Tue, ..., Sun) and find the total weekly demand. M1 M2 M3 Mon. Tue. Wed. Thu. Fri. Sat. Sun. TOTAL: 8. Take advantage of the fact that new demand data became available and use this week 9 (new data) to compare the forecasts using MAPE for days 57-63. New: M: 311 T: 341 W: 357 Th: 363 F: 390 Sa: 490 Su: 492 MAPEM1: MAPEM2: MAPEM3:

9. To provide a line chart with the actual demand (including the new data) and M2 and M3.

10. To choose the best model for forecasting daily demand at Orchid Relief for 7 days ahead and write a short paragraph explaining your choice. [write your paragraph here]

Eli Orchid has designed a new pharmaceutical product, Orchid Relief, which improves the night sleep. Before initiating mass production of the product, Eli Orchid has been market-testing Orchid Relief in Orange County over the past 9 weeks. Now that the daily demand for Orange County can be predicted with reasonable accuracy using the M3 model, the COO of the company decided to use it to optimize the production of the new drug. The daily demand values and production process data are recorded in the Excel file provided. The new pharmaceutical product that the company wishes to introduce, Orchid Relief, uses two new ingredients. At this stage, Eli Orchid can procure limited amounts of each ingredient. The company has 4500 pounds of ingredient 1 and 3600 pounds of ingredient 2 available for this week. Eli Orchid can manufacture the new product using any of its three existing processes that have different capabilities. The production with each of the processes is done in batches (a batch typically represents one full run of a machine from when it starts a task until it finishes it). Each batch of production by each of the processes uses different amounts of ingredients 1 and 2, and results in different number of units of Orchid Relief produced (note the difference between a batch and units of Orchid Relief produced).

The table below outlines the cost per batch, amounts of the two ingredients required, and the number of units of Orchid Relief yielded per batch. Process 1 Process 2 Process 3 Cost of production per batch $14,000 $30,000 $11,000 Ingredient 1 required per batch (pounds) 180 120 540 Ingredient 2 required per batch (pounds) 60 420 120 Orchid Relief yielded per batch (units) 120 300 60 Eli Orchid needs to determine how many batches to produce with each process in the least costly way given the limited availability of the two ingredients. Also, the total production of Orchid Relief in units must be greater than or equal to the total forecasted demand (in units) for the following week. The COO of the company asked the analyst2: 11. To use the new M3 model updated with week 9 data(from Question 8) to predict the total demand (in units) for Week 10 (days 64-70). Note: this new M3 model is a little different from the M3 model in Question 6.

M3 Mon. Tue. Wed. Thu. Fri. Sat. Sun. TOTAL:

12. To state if this is a maximization or a minimization optimization problem?

13. To provide the mathematical formulation of the objective function assuming that X1, X2, and X3 are the decision variables representing the number of batches of each process to be used.

14. To provide the mathematical formulation of the model constraints.

Supply of ingr. 1 Supply of ingr. 2 Units produced Non-negativity X1, X2, X3 >= 0 Integer X1, X2, X3: Integer

15. To use the Production tab of the Excel file and complete the setup by: - entering the forecasted total demand in the pink cell - entering formulas in the five grey cells based on the mathematical formulation

Excel Formulas: Cost of Production Supply of Ingr. 1 Unit Cost 16. To set up Excel Solver (Assume Constraint Precision of 0.000001 and Integer Optimality (%) of 0) and provide the solution to the optimization problem. Number of batches Process 1 Process 2 Process 3

Cost of production (obj.) Unit cost ($###.##) 17. To label each constraint in the solution as binding or not-binding. Supply of ingr. 1 Supply of ingr. 2 Units produced 18. To consider a possible shortage of ingredients in the following week. What would the optimized production process look like if Eli Orchard could only procure 4300 pounds of Ingredient 1 and 2400 pounds of Ingredient 2? Number of batches Process 1 Process 2 Process 3 Cost of production (obj.) Unit cost ($###.##) 19. To label each constraint in the new solution (for the shortage of ingredients) as binding or not-binding. Supply of ingr. 1 Supply of ingr. 2 Units produced

20. To make recommendations about the production processes and pricing of Orchid Relief. [write your paragraph here] 21. Identify at least three cases in your daily life/work where the analytics could be applied. [write your paragraph here] 22. Choose one of the cases and describe how you will approach the problem. For example, you can start from where/how you collect data, how to use the data, what kind of problem can be solved, and etc. [write your paragraph here]

Day Date Weekday Daily Demand Weekend 1 4/25/2016 Mon 290 0 2 4/26/2016 Tue 293 0 3 4/27/2016 Wed 320 0 4 4/28/2016 Thu 315 0 5 4/29/2016 Fri 348 0 6 4/30/2016 Sat 447 1 7 5/1/2016 Sun 430 1 8 5/2/2016 Mon 283 0 9 5/3/2016 Tue 326 0 10 5/4/2016 Wed 317 0 11 5/5/2016 Thu 350 0 12 5/6/2016 Fri 355 0 13 5/7/2016 Sat 428 1 14 5/8/2016 Sun 455 1 15 5/9/2016 Mon 305 0 16 5/10/2016 Tue 310 0 17 5/11/2016 Wed 350 0 18 5/12/2016 Thu 309 0 19 5/13/2016 Fri 366 0 20 5/14/2016 Sat 460 1 21 5/15/2016 Sun 427 1 22 5/16/2016 Mon 291 0 23 5/17/2016 Tue 325 0 24 5/18/2016 Wed 354 0 25 5/19/2016 Thu 325 0 26 5/20/2016 Fri 405 0 27 5/21/2016 Sat 442 1 28 5/22/2016 Sun 454 1 29 5/23/2016 Mon 318 0 30 5/24/2016 Tue 298 0 31 5/25/2016 Wed 355 0 32 5/26/2016 Thu 355 0 33 5/27/2016 Fri 374 0 34 5/28/2016 Sat 450 1 35 5/29/2016 Sun 463 1 36 5/30/2016 Mon 291 0 37 5/31/2016 Tue 319 0 38 6/1/2016 Wed 333 0 39 6/2/2016 Thu 339 0 40 6/3/2016 Fri 416 0 41 6/4/2016 Sat 478 1 42 6/5/2016 Sun 459 1 43 6/6/2016 Mon 319 0 44 6/7/2016 Tue 326 0 45 6/8/2016 Wed 356 0 46 6/9/2016 Thu 340 0 47 6/10/2016 Fri 395 0 48 6/11/2016 Sat 465 1 49 6/12/2016 Sun 453 1 50 6/13/2016 Mon 310 0 51 6/14/2016 Tue 324 0 52 6/15/2016 Wed 350 0 53 6/16/2016 Thu 348 0 54 6/17/2016 Fri 384 0 55 6/18/2016 Sat 474 1 56 6/19/2016 Sun 485 1

# batches produced Decision variables Process 1 - X1 Process 2 - X2 Data for constraints Production requirements and output Ingredient 1 required per batch Ingredient 2 required per batch Orchid Relief yielded per batch Process 3 - X3 Process 1 Process 2 Process 3 Cost of production per batch Supply of ingredient 1 Supply of ingredient 2 Units of Orchid Relief produced Unit Cost Data for the objective function Process 1 Process 2 Cost of production Left hand side of constraints Objective function Constraints = Calculations Process 3 Right hand side of constraints # batches produced Decision variables Process 1 - X1 Process 2 - X2 Data for constraints Production requirements and output Ingredient 1 required per batch Ingredient 2 required per batch Orchid Relief yielded per batch Process 3 - X3 Process 1 Process 2 Process 3 Cost of production per batch Supply of ingredient 1 Supply of ingredient 2 Units of Orchid Relief produced Unit Cost Data for the objective function Process 1 Process 2 Cost of production Left hand side of constraints Objective function Constraints = Calculations Process 3 Right hand side of constraints