please see the attachement

profileDevi123
2_questions.xlsx
Home>Business & Finance homework help>please see the attachement

Question1

Question 1 (90 points + 5 bonus points) Lella, Deepak Lella, Deepak
Quarterly revenues (in $1,000,000's) for a national supermakret for a five-year period were as follows: Point distribution Either calcuate b0 and b1 manually (refer to pp.213-214 or in-class practice for Chapter 6-part2) as below
Year Question points
Quarter 1 2 3 4 5 1) 42
Q1 Winter 64 66 68 73 77 2) 10
Q2 Spring 103 103 104 120 125 3) 8
Q3 Summer 152 160 162 176 184 4) 30
Q4 Fall 73 72 78 88 96 5) 5 bonus points
1). Please compute forecasts and measures of forecast accuracy using the following five forecasting techniques (you must provide a fomula for each cell in the following boxes; however, for actual revenue column, you may type or copy/paste those numbers). 42 points in total.
1a) Please compute forecasts and measures of forecast accuracy using naïve forecast. 1b) Please compute forecasts and measures of forecast accuracy using moving average (k=3, unweighted) 1c) Please compute forecasts and measures of forecast accuracy using moving average. 1d) Please compute forecasts and measures of forecast accuracy using exponential smoothing 1e) Please compute forecasts and measures of forecast accuracy using linear treand projection.
k=3, weighting factors: 0.2, 0.35, 0.45. For instance, Q4=0.2*Q1+0.35*Q2+0.45*Q3 (smoothing constant alpha=0.4) You either calcaute b0 and b1 manually or obtaind them from the regression report you generate (see the right)
8 points (including 3 points for MAE, MSE, and MAPE) 8 points (including 3 points for MAE, MSE, and MAPE) 8 points (including 3 points for MAE, MSE, and MAPE) 8 points (including 3 points for MAE, MSE, and MAPE) 10 points (including 2 points for obtaining b1 and b0, and 3 points for MAE, MSE, and MAPE)
Quarters Actual revenue Forecast revenue Forecast Error Absolute Error Squared Error Abs.% Error Quarters Actual revenue Forecast revenue Forecast Error Absolute Error Squared Error Abs.% Error Quarters Actual revenue Forecast revenue Forecast Error Absolute Error Squared Error Abs.% Error Quarters Actual revenue Forecast revenue Forecast Error Absolute Error Squared Error Abs.% Error Quarter (t) Actual revenue Forecast revenue Forecast Error Absolute Error Squared Error Abs.% Error
1 Keep these red cells blank 1 Keep these red cells blank 1 Keep these red cells blank 1 Keep these red cells blank 1 64
2 2 2 2 2 103
3 3 3 3 3 152
4 4 4 4 4 73
5 5 5 5 5 66
6 6 6 6 6 103
7 7 7 7 7 160
8 8 8 8 8 72
9 9 9 9 9 68
10 10 10 10 10 104
11 11 11 11 11 162
12 12 12 12 12 78
13 13 13 13 13 73
14 14 14 14 14 120 Or you can generate a regression report here (please use Cell AQ27 as output Range:
15 15 15 15 15 176 SUMMARY OUTPUT
16 16 16 16 16 88
17 17 17 17 17 77 Regression Statistics
18 18 18 18 18 125 Multiple R 0.2628636809
19 19 19 19 19 184 R Square 0.0690973148
20 20 20 20 20 96 Adjusted R Square 0.0173804989
Standard Error 39.3189664089
MAE= must include formula 1 point MAE= must include formula 1 point MAE= must include formula 1 point MAE= must include formula 1 point MAE= must include formula 1 point Observations 20
Lella, Deepak MSE= must include formula 1 point MSE= must include formula 1 point MSE= must include formula 1 point MSE= must include formula 1 point MSE= must include formula 1 point
MAPE= must include formula 1 point MAPE= must include formula 1 point MAPE= must include formula 1 point MAPE= must include formula 1 point MAPE= must include formula 1 point ANOVA
df SS MS F Significance F
2). Based on MAE, MSE, and MAPE of each forecasting method, please indicate (10 points) Regression 1 2065.5398496241 2065.5398496241 1.3360705533 0.2628406719
Residual 18 27827.6601503759 1545.9811194653
The method with the smallest MAE is 2 points Total 19 29893.2
The method with the smallest MSE is 2 points
The method with the smallest MAPE is 2 points Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 88.6947368421 18.2648967173 4.8560218114 0.0001269105 50.3216127659 127.0678609183 50.3216127659 127.0678609183
Please briefly explain why the method in cell G44 has the smallest MSE as below (4 points) [refer to your textbook or my slides] Quarter 1.762406015 1.5247241187 1.1558851817 0.2628406719 -1.4409204913 4.9657325214 -1.4409204913 4.9657325214
Generate and move your series plot here (3 points)
3). Construct a time series plot based on the data range B15:C35 (including the label in the first row). What pattern(s) you can observe from this plot and briefly explain why. (8 points)
time series plot (3 points)
Pattern (s) & reason(s) as below (5 points)
4). Seasonality without/with trend (20 points + 5 bonus points)
please complete the following table first (4 points) and your regression reports will be based on this.
Year Quarter Period Qtr1 Qtr2 Qtr3 Revenues Regression report for seasonality without trend (5 points): the report is based on the data in the left.
1 1 1 SUMMARY OUTPUT
1 2 2
1 3 3
1 4 4
2 1 5
2 2 6
2 3 7
2 4 8
3 1 9
3 2 10
3 3 11
3 4 12
4 1 13
4 2 14
4 3 15
4 4 16
5 1 17
5 2 18
5 3 19
5 4 20
Regression report for seasonality with trend (5 points): the report is based on the data in the left.
Forecast the revenues in the four quarters of year 6 as below (16 points) SUMMARY OUTPUT
Quarter T Qtr1 Qtr2 Qtr3 forecasts (seasoanbility without trend) forecasts (seasoanbility with trend)
1 21
2 22
3 23
4 24
5) Based on the two regression reports and forecasts, please indicate whether or not your observations in question 3) are confirmed. (5 bonus points)
Lella, Deepak

Ft = b0 + b1t

a) Seasonality without trend: Use a multiple regression model with dummy variables as follows to account for seasonal effects in the data. Qtr1 = 1 if Quarter 1, 0 otherwise; Qtr2 = 1 if Quarter 2, 0 otherwise; Qtr3 = 1 if Quarter 3, 0 otherwise. Forecast the revenues in the four quarters of year 6 (cells G98:G101) b) Seasonality and with trend: Let Period = 1 to refer to the observation in quarter 1 of year 1; Period = 2 to refer to the observation in quarter 2 of year 1; … and Period = 20 to refer to the observation in quarter 4 of year 5. Using the dummy variables defined in part (a) and Period, compute estimates of quarterly sales for year 6 (cells H98:H101).

Question2a

Lella, Deepak
Daily Production Plan for winter and spring (20 points):
Constratints LHS Coefficients RHS Values
Process 1 Process 2
Lella, Deepak
Obj. Func. Coeff.
Decision Variables
Process 1 Process 2
Minimized Obj. Func.
Constraints Amount Used Inequality (>= or <=) RHS Values
Based on sensivity report and answer report, please answer the following questions (10 points)
a) How many constraints which include slack variables? 2 points
b) How many constraints are binding in this question? 2 points
c) Supposed that the cost of running process 1 per hour decreases to 200, would the solution above be still optimal? 2 points
d) Supposed that the cost of running process 1 per hour increases to 500, would the solution above be still optimal? 2 points
e) If this company needs to produce at least 810 units of Y in spring and winter, how much would the total cost change? 2 points
(note: you must use a formula to show how you calcuate this change).
Lella, Deepak

Question 2 (60 points + 5 bonus points) : Eastern Chemicals manufactures three chemicals: X, Y, and Z. The chemicals are produced via two production process: 1 and 2. Running process 1 for an hour costs $400 and yields 300 units of X, 100 units of Y, and 50 units of Z. Running process 2 for an hour costs $100 and yields 100 units of X and 100 units of Y. In addition, running processes 1 and 2 per hour will generate 8 and 6 units of wastes, respectively. In order to meet the this comany's environmental policy, the total units of wastes generated daily must not exceed 300 units. In the following two scenarios, please follow the five-step instruction and use Solver to determine a daily production plan (that is how many hours to run processes 1 and 2 respectively) that minimize the cost of meeting the company’s daily demands. 1) To meet customer demands in winter and spring, at least 2000 units of X, 800 units of Y, and 300 units of Z must be produced daily (Answer this question in worksheet Question2a). [30 points] 2) During summer and fall, Eastern Chemicals’ customers have a stronger demand. To meet customer demands in these seasons (summer and fall), at least 9000 units of X, 3500 units of Y, and 1200 units of Z must be produced daily (Answer this question in worksheet Question2b).

Step 1: Enter the problem data in the top part of the worksheet

Step 2: Specify cell locations for decision variables

Step 3: Select a cell and enter a formula for computing the value of objective function

Step 4: Select a cell and enter a formula for computing the left-hand side of each constraint

Step 5: Select a cell and enter a formula for computing the right-hand side of each constraint

Question2b

Lella, Deepak
Daily Production Plan for summer and fall (20 points):
Constratints LHS Coefficients RHS Values
Process 1 Process 2
Lella, Deepak
Obj. Func. Coeff.
Decision Variables
Process 1 Process 2
Minimized Obj. Func.
Constraints Amount Used Inequality (>= or <=) RHS Values
Please answer the following questions; some of them should be based on sensitivity report and answer report (10 points)
a) Does this question include the same number of constraints as Question 2a? 2 points
b) How many constraints are binding in this question? 2 points
c) Supposed that the cost of running process 1 per hour increase to 460, would the solution above be still optimal? 2 points
d) Supposed that the cost of running process 2 per hour decrease to130, would the solution above be still optimal? 2 points
e) If this company needs to produce at least 8500 units of X in summer and fall, how much would the total cost change? 2 points
(note: you must use a formula to show how you calcuate this change; use a negative number if the total cost decreases).
Bonus question: compared the sensitivity reports generated in Questions 2a and 2b, what differences can you find? Try to explain why. (5 points)
Lella, Deepak

Question 2 (60 points + 5 bonus points): Eastern Chemicals manufactures three chemicals: X, Y, and Z. The chemicals are produced via two production process: 1 and 2. Running process 1 for an hour costs $400 and yields 300 units of X, 100 units of Y, and 50 units of Z. Running process 2 for an hour costs $100 and yields 100 units of X and 100 units of Y. In addition, running processes 1 and 2 per hour will generate 8 and 6 units of wastes, respectively. In order to meet the this comany's environmental policy, the total units of wastes generated daily must not exceed 300 units. In the following two scenarios, please follow the five-step instruction and use Solver to determine a daily production plan (that is how many hours to run processes 1 and 2 respectively) that minimize the cost of meeting the company’s daily demands. 1) To meet customer demands in winter and spring, at least 2000 units of X, 800 units of Y, and 300 units of Z must be produced daily (Answer this question in worksheet Question2a). 2) During summer and fall, Eastern Chemicals’ customers have a stronger demand. To meet customer demands in these seasons (summer and fall), at least 9000 units of X, 3500 units of Y, and 1200 units of Z must be produced daily (Answer this question in worksheet Question2b). [30 points + 5 bonus points]

Step 1: Enter the problem data in the top part of the worksheet

Step 2: Specify cell locations for decision variables

Step 3: Select a cell and enter a formula for computing the value of objective function

Step 4: Select a cell and enter a formula for computing the left-hand side of each constraint

Step 5: Select a cell and enter a formula for computing the right-hand side of each constraint

image1.png