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

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
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
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
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.