day of the week Daily Dem Average Daily price per pack Monday 297 11.1 Tuesday 293 10.055 Wednesday 327 9.77 Thursday 315 10.6 Friday 348 9.59 Saturday 447 8.965 Sunday 431 10 Monday 283 10.16 Tuesday 326 9.96 Wednesday 317 10.73 Thursday 345 11.445 Friday 355 10.965 Saturday 428 9.49 Sunday 454 9.855 Monday 305 10.94 Tuesday 310 9.995 Wednesday 350 8.98 Thursday 308 8.47 Friday 366 8.295 Saturday 460 8.69 Sunday 427 8.545 Monday 291 9.075 Tuesday 325 10.225 Wednesday 354 10.93 Thursday 322 9.705 Friday 405 9.23 Saturday 442 9.525 Sunday 454 8.4 Monday 318 9.34 Tuesday 298 9.42 Wednesday 355 8.98 Thursday 355 10.69 Friday 374 10.34 Saturday 447 9.27 Sunday 463 10.51 Monday 291 11.28 Tuesday 319 10.135 Wednesday 333 9.51 Thursday 339 9.7 Friday 416 10.685 Saturday 475 10.405 Sunday 459 10.395 Monday 319 10.06 Tuesday 326 8.945 Wednesday 356 9.515 Thursday 340 9.5 Friday 395 9.58 Dai 500 450 400 350 300 250 0 10 20 Average D 14 12 10 8 6 4 2 0 Saturday Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sunday 465 453 307 324 350 348 384 474 485 10.49 10.765 9.78 10.6 11.125 9.985 10.255 10.685 9.355 Daily Demand 10 20 30 Average Daily price per pack 40 50 60 Eli Orchid Company has designed a new pharmaceutical product, Orchid Relief that improves night sleep. Before mass production of the product, Eli Orchid has market tested Orchid Relief in only Orange County over the past 8 weeks. The daily demand as well as average daily price sold per package is recorded there have been some variation in the prices as some retail stores have offered price promotions over time. Eli Orchid is now trying to use the sales pattern over the past 8 weeks to predict sales in US for the upcoming few months. An accurate forecast of demand and total revenue would be much helpful in arrangements for the company's production processes and design of price promotions over each week. The data is provided in file EliOrchid_OC demand.xlsx. First, we would like to form forecasts for the daily demand. After reviewing the data, the marketing manager believes that there is a weekly repeating pattern in the demand likely due to changes in sleeping pattern of customers on different days. Create a regression model that captures the impact of time as well as the seasonality. 1. How many total predictor variables should be included inside the regression model? 2.What is the Rsquared of the regression model that you developed in the last question (enter as a percentage, that is, enter a number between 0 and 100)? 3.From the regression model that you created, we can conclude that there is no significant linear trend, at 5% level of significance. True or False 4.What would be your forecast for day 57 (the day after the data ends)? 5.What would be your forecast for day 63 (one week ahead)? 6.What is the forecasting accuracy of the regression model you have created based on the mean absolute error? 7.Now, we would like to create a forecast of the average daily prices for the future. Looking at the data, we can conclude that the data is a ... . (Use 5% level of significant where needed) A.Stationary time series B.Linear trend series C.Seasonal series with a significant linear time trend D.Seasonal series without a significant linear time trend 8.What would be a forecast of prices for day 57 using the 5day moving average method? 9.What would be a forecast of prices for day 63 using the 5day moving average method? 10.What would be a forecast of prices for day 57 using the exponential smoothing method with smoothing constant of 0.7? 11.Which of the following methods provide the best accuracy based on the mean absolute percentage error? A.Exponential Smoothing method with smoothing constant of 0.5 B.Exponential Smoothing method with smoothing constant of 0.7 C.5day moving average method Based on the forecast you have provided for the demand next week, and given than the company is planning to release the product for the whole western US, it has projected that it would need to have at least 50 pounds of Orchid Relief produced and packaged over the next week. The new pharmaceutical product that the company wishes to introduce, Orchid Relief, uses two new and relatively expensive ingredients. Eli Orchid can obtain limited amounts of each ingredient over this week that production needs to take place, that is, the company has 75 pounds of ingredient 1 and 60 pounds of ingredient 2 available each month. 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 amounts (number of pounds) of Orchid Relief produced (note the distinction between a batch of production and a pound of Orchid Relief produced). For each batch of production by each of the three processes, the table below outlines the cost per batch, amounts of the two ingredients required, and the number of pounds of Orchid Relief produced. Cost of production per batch Pounds of Ingredient 1 required per batch Pounds of Ingredient 2 required per batch Pounds of Orchid Relief yielded per batch Process 1 Process 2 Process 3 $14,000 $28,000 $13,000 3 2 7 1 7 2 2 5 1 Eli Orchid needs to find how many batches to produce with each process in the least costly way. First, verbally define the decision variables, state the objective function and all of the constraints, and provide an algebraic formulation of the optimization problem. Then answer the following questions. Make sure you are not skipping these steps before attempting the following questions. 12.Which of the following can represent the objective function? Select one: A.14000X1+28000X2+13000X314000X1+28000X2+13000X3 B.2X1+5X2+X32X1+5X2+X3 C.X1+7X2+2X3X1+7X2+2X3 D.3X1+X2+2X33X1+X2+2X3 13.Except the nonnegativity constraints, how many constraints the problem has? 14.After solving the problem with Excel solver, how many batches of process 1 needs to be produced optimally? 15.After solving the problem with Excel solver, what is the optimal cost of production of each pound of Orchid Relief produced? 16.Currently, at least 50 pounds of Orchid Relief is produced. If the company requires one more pound of Orchid Relief to be produced on top of the existing amount, how much more would it cost? (this is also commonly called the marginal cost of each additional pound of Orchid Relief produced, and is not necessarily the same as the answer to the previous question). If you cannot find the answer based on the sensitivity report, enter 1000. 17.How much more would it cost (on top of the current total cost) to produce 50 additional pounds of Orchid Relief? If you cannot find the answer based on the sensitivity report, enter 1000. 18.How much should Eli Orchid be willing to pay more to obtain an amount of 10 more pounds of ingredient 1? If you cannot find the answer based on the sensitivity report, enter 1000. 19.How much should Eli Orchid be willing to pay more to obtain an amount of 10 more pounds of ingredient 2? If you cannot find the answer based on the sensitivity report, enter 1000. 20.If the cost of process 2 increased to $32,000 per batch, would Eli Orchid change its decision about how to use each of the three processes? A.No B.Yes C.Cannot be determined only based on the sensitivity report 21.If the cost of process 2 increased to $32,000 per batch, how much would then be the total cost of the 50 pounds of Orchid Relief? If you cannot find the answer based on the sensitivity report, enter 1000