Urgent Help MGT 226 Excel Answer 4 of 5 questions. All work must be shown to qualify for partial credit.
Vollmer Manufacturing makes three components for
Urgent Help MGT 226 Excel Answer 4 of 5 questions. All work must be shown to qualify for partial credit.
Vollmer Manufacturing makes three components for sale to refrigeration companies. The components are processed on two machines: a sharper and a grinder. The times (in minutes) required on each machine are as follows:
The sharper is available for 120 hours, and the grinder is available for 110 hours. No more than 200 units of component 3 can be sold, but up to 1000 units of each other components can be sold. In fact, the company already has orders for 600 units of component 1 that must be satisfied. The profit contributions for components 1, 2, and 3 are $8, $6, and $9, respectively.
Formulate this LP problem and make sure you define all the decision variables.
Transform your formulation to the Standard LP form.
The Excel Solver Solution to this problem is presented below. Identify and present the optimal solution and the values of the decision variables.
Find the slack and/or surplus variables.
Will the Sharper or the Grinder or both need more time? Explain.
Identify the ranges of optimality and the ranges of feasibility. Discuss them in relation to this problem. Hint: Do not give a general discussion on this question. You must be very specific and use all the results you now have.
Consider the following linear program
Max 3×1 + 2×2
s.t.
1×1 + 1×2 < 10 3x1 + 1x2 < 24 1x1 + 2x2 < 16 And x1, x2 > 0.
Use Excel Solver to find the optimal solution to this problem. State the optimal values of x1, x2, and Z.
Assume that the objective function coefficient for x1 changes from 3 to 5. Does the optimal solution change?
Assume that the objective function coefficient for x1 remains 3, but the objective function coefficient for x2 changes from 2 to 4. Does the optimal solution change?
What are the shadow prices for these constraints?
What conclusions can you draw about changes to the right hand side of constraint 2?
Identify the binding and non-binding constraints in this problem and explain.
The national mean annual salary for school administrator is $90,000 a year (The Cincinnati Enquirer, April 7, 2012). A school official took a sample of 25 school administrators in the state of Ohio to learn about salaries in that state to see if they differed from the national average.
Formulate hypotheses that can be used to determine whether the population mean annual administrator salary in Ohio differs from the national mean of $90,000.
The sample data for 25 Ohio administrators is contained in the file named Administrator. What is the p-value for the hypothesis test in part (a)?
At α = 0.05, can your null hypothesis be rejected? What is your conclusion?
Repeat the preceding hypothesis test using the critical value approach.
A recent issue of the AARP Bulletin reported that the average weekly pay for a woman with a high school degree is $520. Suppose you would like to determine if the average weekly pay for all working women is significantly greater than that for women with a high school degree. Data providing the weekly pay for a sample of 50 working women are available in the file named WeeklyPay. These data are consistent with the findings reported in the AARP article.
State the hypotheses that should be used to test whether the mean weekly pay for all women is significantly greater than the mean weekly pay for women with a high school degree.
Use the data in the file named WeeklyPay to compute the mean, the test statistic, and the p-value.
Use α = 0.05, what is your conclusion?
Repeat the hypothesis test using the critical value approach.
The monthly sales for Telco Batteries, Inc., were as follows in Table 1:
Table 1: Monthly Sales
Forecast January sales using each of the following:
A 4-month moving average.
Exponential smoothing using an alpha = 0.25
The trend projection model
Which of the forecast models is better and why? Answer 4 of 5 questions. All work must be shown to qualify for partial credit.
1. Vollmer Manufacturing makes three components for sale to refrigeration companies. The components are processed on two machines: a sharper and a grinder. The times (in minutes) required on each machine are as follows:
Machine
Component
Sharper
Grinder
1
6
4
2
4
5
3
4
2
0.
The sharper is available for 120 hours, and the grinder is available for 110 hours. No more than 200 units of component 3 can be sold, but up to 1000 units of each other components can be sold. In fact, the company already has orders for 600 units of component 1 that must be satisfied. The profit contributions for components 1, 2, and 3 are $8, $6, and $9, respectively.
a. Formulate this LP problem and make sure you define all the decision variables.
b. Transform your formulation to the Standard LP form.
c. The Excel Solver Solution to this problem is presented below. Identify and present the optimal solution and the values of the decision variables.
d. Find the slack and/or surplus variables.
e. Will the Sharper or the Grinder or both need more time? Explain.
f. Identify the ranges of optimality and the ranges of feasibility. Discuss them in relation to this problem. Hint: Do not give a general discussion on this question. You must be very specific and use all the results you now have.
Microsoft Excel Report
Objective function
$10800
Variable Cells
Final
Reduced
Objective
Allowable
Allowable
Cell
Name
Value
Cost
Coefficient
Increase
Decrease
$C$19
Component 1
600
0
8
1
1E+30
$D$19
Component 2
700
0
6
3
0.666667
$E$19
Component 3
200
0
9
1E+30
3
Constraints
Final
Shadow
Constraint
Allowable
Allowable
Cell
Name
Value
Price
R.H. Side
Increase
Decrease
$C$23
Sharper LHS
7200
1.5
7200
240
2800
$C$24
Grinder LHS
6300
0
6600
1E+30
300
$C$25
Component 1 LHS
600
-1
600
400
85.71429
$C$26
Component 1 LHS
600
0
1000
1E+30
400
$C$27
Component 2 LHS
700
0
1000
1E+30
300
$C$28
Component 3 LHS
200
3
200
700
100
.
2. Consider the following linear program
Max 3×1 + 2×2
s.t.
1×1 + 1×2 < 10 3x1 + 1x2 < 24 1x1 + 2x2 < 16 And x1, x2 > 0.
a) Use Excel Solver to find the optimal solution to this problem. State the optimal values of x1, x2, and Z.
b) Assume that the objective function coefficient for x1 changes from 3 to 5. Does the optimal solution change?
c) Assume that the objective function coefficient for x1 remains 3, but the objective function coefficient for x2 changes from 2 to 4. Does the optimal solution change?
d) What are the shadow prices for these constraints?
e) What conclusions can you draw about changes to the right hand side of constraint 2?
f) Identify the binding and non-binding constraints in this problem and explain.
3. The national mean annual salary for school administrator is $90,000 a year (The Cincinnati Enquirer, April 7, 2012). A school official took a sample of 25 school administrators in the state of Ohio to learn about salaries in that state to see if they differed from the national average.
a. Formulate hypotheses that can be used to determine whether the population mean annual administrator salary in Ohio differs from the national mean of $90,000.
b. The sample data for 25 Ohio administrators is contained in the file named Administrator. What is the p-value for the hypothesis test in part (a)?
c. At α = 0.05, can your null hypothesis be rejected? What is your conclusion?
d. Repeat the preceding hypothesis test using the critical value approach.
4. A recent issue of the AARP Bulletin reported that the average weekly pay for a woman with a high school degree is $520. Suppose you would like to determine if the average weekly pay for all working women is significantly greater than that for women with a high school degree. Data providing the weekly pay for a sample of 50 working women are available in the file named WeeklyPay. These data are consistent with the findings reported in the AARP article.
a. State the hypotheses that should be used to test whether the mean weekly pay for all women is significantly greater than the mean weekly pay for women with a high school degree.
b. Use the data in the file named WeeklyPay to compute the mean, the test statistic, and the p-value.
c. Use α = 0.05, what is your conclusion?
d. Repeat the hypothesis test using the critical value approach.
5. The monthly sales for Telco Batteries, Inc., were as follows in Table 1:
Table 1: Monthly Sales
Month
Sales
January
10
February
14
March
15
April
14
May
12
June
10
July
14
August
18
September
20
October
20
November
22
December
23
Forecast January sales using each of the following:
a) A 4-month moving average.
b) Exponential smoothing using an alpha = 0.25
c) The trend projection model
d) Which of the forecast models is better and why?
PAGE 4
