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