# 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

3×1 + 1×2 < 24

1×1 + 2×2 < 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?