# Hi can this be done by 12 est today M5 Group Assignment Instructions Complete the Assignment,…

Hi can this be done by 12 est today M5 Group

Assignment Instructions

Complete the

Assignment, name it as GroupXX_Assign5.xls (where XX is your Group Name), and

upload and submit to the instructor through Dropbox. Do not enter

anything in the spreadsheet cells that are

black, labeled â€œGraderâ€.

You must

complete this assignment without the assistance of persons other than the

members of your Group. You may use any

other resources you deem necessary.

Answer the questions below by placing the appropriate graph and/or

answers in the designated cells of the spreadsheet.

DO NOT CHANGE THE APPEARANCE

OR FUNCTIONALITY OF THE SPREADSHEET UNLESS INSTRUCTED TO DO SO.

.jpg”>

QUESTION 1 (18

points):

The Hickory Cabinet and Furniture

Company produces sofas, tables, and chairs.

The plant uses three main resources to make furniture â€“ wood,

upholstery, and labor. The resource

requirements for each piece of furniture and the total resources available

weekly are as follows.

.gif”>

The furniture is produced on a

weekly basis and stored in the warehouse until the end of the week, when it is

shipped out. The warehouse has a total

capacity of 650 pieces of furniture.

Each sofa earns $300 in profit, each table $200, and each chair $150. The company wants to know how many pieces of

each type of furniture to make per week to maximize profit. The worksheet named â€œHickory 1â€ contains a

template for solving this LP problem.

Answer the parts below, and then

use the LP template to solve for the maximum profit. (4 points)

a.

1 Point: In cell B3, use the Excelâ€™s built-in function

named â€œSUMPRODUCTâ€ to write a formula for the objective function. Be sure to reference the appropriate cells.

b.

1 Point: In cells B9:D9, enter the profit coefficients

for the objective function.

c.

4 Points: In cells B12:D15, enter the constraint

coefficients for each of the four constraints.

d.

1 Point: In cells E12:E15, enter the appropriate

constraint values.

e.

3 Points: In cells F12:F15, use the â€œSUMPRODUCTâ€

function to write a formula for the amount of each resource used or limitation

imposed. Be sure to reference the

appropriate cells.

f.

3 Points: In cells G12:G15, write the appropriate

formulas to determine the amount of slack or surplus associated with each

resource or limitation.

g.

1 Point: In cells H12:H15, write the type of inequality

associated with each constraint using either â€œ<=,â€ â€œ>=,â€ or â€œ=.â€

QUESTION 2 (8 points):

The Hickory Cabinet and Furniture Company had some changes

in supplier and buyer contracts, as well as labor opportunities. The sofa retailer left the profit at $300 per

sofa, while a contract with another retailer increased the profit on chairs to

$225 and on tables to $275. Additionally,

the upholstery vendor decided to sell in lots of size 601, meaning the

available upholstery is now only 601 yards since the company doesnâ€™t want to

buy 1202 yards at a time (current is 1000 yds available at a time). As a result of another plant going out of

business, the labor supply increased to 401 hours. Based on changes in the profit amounts and

available resources, the sensitivity output for this formulation is provided in

the worksheet named â€œHickory 2.â€ Use the output the answer the following

questions.

a.

1 Point: In cells J5:L5 input the values of the

decision variables that result from the new model solution.

b.

1 Point: In cells J6:K6 input the lower and upper range

of sofa profit for which the optimal decision variables would remain the same.

c.

1 Point: In J7 input the value that represents how much

table profit would have to increase by to have a solution that includes making

tables.

d.

1 Point: In cell J8, input either the word(s) â€œchangeâ€

or â€œno changeâ€ to indicate what would happen to the optimal solution if chair

profit could be increased by $30.

e.

1 Point: In cell J13, input the value that indicates

the amount of the wood resource remaining.

f.

1 Point: In cell J14, input the value indicating the

maximum amount the company would be willing to pay for an additional yard of

upholstery.

g.

1 Point: In cell J15, input the value representing how

much more profit could be obtained by obtaining 10 additional hours of labor.

h.

1 Point: In cell J165, input the value representing how

much profit would be lost if warehouse capacity dropped by 500 pieces of

furniture.

QUESTION 3 (30

points):

As a 401K retirement plan

participant you have to make personal decisions regarding your own investment

objectives. Suppose you have $10,000 to

divide among four investment alternatives in your 401K retirement plan. The investment alternatives and historical

returns are as follows;

â€¢ Real

Estate (RE) = 8%

â€¢ CD

= 4%

â€¢ Mutual

Fund (MF) = 6%

â€¢ Bonds

= 7%

You will follow the investment allocation guidelines below.

1.

More money should be in real estate than in CDs, at a

rate of at least 2 to 1. [Hint: That means that RE > 2*CD]

2.

At least 50% ($5,000) must be in mutual funds and bonds

combined.

3.

No more than 20% ($2,000) can be in bonds alone.

4.

At least 2.5% ($250) must be invested in each

alternative.

5.

All of the $10,000 must be invested.

Use the spreadsheet template in the worksheet named â€œInvestment

1â€ to formulate and solve the LP problem for the maximum return. Note that the percentages must be expressed

as proportions in the solution.

Answer the parts below, and then use the LP template to

solve for the maximum profit. (4

points)

a.

4 Points: In cell B3, use the Excelâ€™s built-in function

named â€œSUMPRODUCTâ€ to write a formula for the objective function. Be sure to reference the appropriate cells.

b.

4 Points: In cells B9:E9, enter the investment return

coefficients for the objective function.

Make sure the coefficients are input as proportions, not percent.

c.

4 Points: In cells B12:E19, enter the constraint coefficients

for each of the eight constraints.

d.

4 Points: In cells F12:F19, enter the appropriate

constraint values.

e.

4 Points: In cells G12:G19, use the â€œSUMPRODUCTâ€

function to write a formula for the amount of each resource used or limitation

imposed. Be sure to reference the

appropriate cells.

f.

4 Points: In cells H12:H19, write the appropriate

formulas to determine the amount of slack or surplus associated with each

resource or limitation.

g.

2 Points: In cells I12:I19, write the type of

inequality associated with each constraint using either â€œ<=,â€ â€œ>=,â€ or

â€œ=.â€

Question 4 (8 points):

The sensitivity report for the investment problem in

question 3 is in the worksheet named â€œInvestment 2.â€ Use the report to answer the following

questions.

a.

2 Points: In cell J6, input the word â€œyesâ€ or â€œnoâ€ to

indicate if a greater return on RE could change the optimal solution.

b.

2 Points: In cell J7, input the word â€œyesâ€ or â€œnoâ€ to

indicate if a return on CD of 7% would change the optimal solution.

c.

2 Points: In cell J8, input the value that represents

the maximum amount the return on MF (in percent terms) could increase without

changing the optimal solution.

d.

2 Points: In cell J9, input the value that represents

the maximum decrease in return on Bonds (in percent terms) that would result in

the optimal solution remaining unchanged.

Question 5 (36

points):

Carlie City is located in a particular warm and dry part of

the United States, so it is especially prone to the occurrence of fires. The city has become a popular place for

senior to move to after retirement, so it has been growing rapidly and

spreading well beyond its original borders.

However, the city still has only one fire station, located in the

congested center of the original town site.

The result has been some long delays in fire trucks reaching fires in

the outer parts of the city, causing much more damage than would have occurred

with a prompt response. The cityâ€™s

residents are very unhappy about this, so the city council has directed the

city manager to develop a plan for locating multiple fire stations throughout

city (including perhaps moving the current fire station) that would greatly

reduce the response time to any fire. In

particular, the city council has adopted the policy about the maximum

acceptable response time of 10 minutes for fire trucks to reach a fire anywhere

in the city after being notified about the fire.

The city manager contacted your team for analyzing the

problem. To get started, she divides the

city into eight tracts and then gathers data on the estimated response time for

a fire in each tract from a potential fire station in each of the eight

tracts. These data are shown in

worksheet Fire. For example, if a

decision were to be made to locate a fire station in tract 1 and if that fire

station were to be used to respond to a fire in any of the tracts, the second column

shows what the estimated response time would be. The bottom row shows what the cost would be

of acquiring the land and constructing a fire station in any of the eight

tracts. Note that the cost is far less

for tract 5 because the current fire station already is there.

The objective is to determine which tract should receive a

fire station to minimize the total cost of the stations while ensuring that

each tract has at least one station close enough to respond to a fire in no

more than 10 minutes.

a.

20 Points: Set up your model in worksheet Fire

including decision variables, objective function, and constraints. Solve the model to optimality. [Hint: Construct a 0/1 table for the response

time table (1 indicates that response time is below 10 minutes and 0

otherwise)].

b.

4 Points: Under the optimal location plan reported in

part a, how many tracts will have more than one fire station that is within 10

minute response time?

c.

12 Points: What would be the increase in the total cost

if the maximum acceptable response time is now 8 minutes? Conduct your analysis in worksheet Fire8Min.