Following graduation, three students, let's call them Abigail, Bruce and Colton, launch a start$-$ Following graduation, three students, let's call them Abigail, Bruce and Colton, launch a start$-$

up company that produces bike locks. Abigail and Bruce each are available to work a maximum

of $40$ hours per week at the company, while Colton is available to work a maximum of $20$

hours per week. The company makes two different types of locks: a u$-$lock and a cable lock.

To make a lock, Abigail assembles the inside mechanical parts of the lock while Bruce

produces the casing. Colton is responsible for taking orders and shipping the locks. The amount

of time required for each of these tasks is shown below.

Each U$-$lock built and shipped yields a profit of $$30,$ while each cable lock yields a profit of

$$20.$ The three partners now want to determine how many locks of each type should be

produced per week to maximize the total profit.

a$)$ Formulate a linear programming model for this problem and solve the model using MS

Excel. Note that the optimal solution may be non$-$integer. Consider the answer as a

recommendation for the average number of locks of each type to produce each week over

the course of a year.

b$)$ Use Solver to find the optimal solution and total profit as the unit profit of each lock is

varied. Fill in the two tables below and estimate the allowable range for the unit profit of

each type of lock.

Table $1.$ Keeping Price of cable locks Fixed

Table $2$: Keeping Price of U$-$locks Fixed

c$)$ Use Solver to fill in the following tables to see the impact of multiple changes at the same

time by entering the optimal solution for both products in each cell.

d$)$ Use Solver to fill in the three tables below to see the impact of varying the righthand

sides on the constraints. For the last column, determine the difference between the profit

for that row and the row above it$.$ e$)$ Generate Solver's sensitivity report and use it to determine the allowable range for the unit

profit for each type of clock and the allowable ranges for the maximum number of hours

each partner is available to work per week. Do the above tables affirm these ranges?

f$)$ To increase the total profit, the three partners have agreed that one of them will slightly

increase the maximum number of hours available to work per week. The choice of which

one will be based on which one would increase the total profit the most. Use the sensitivity

report to make this choice. Assume no change in the original estimates of the unit profits.

g$)$ Explain why one of the shadow prices is equal to zero.

h$)$ Can the shadow prices in the sensitivity report be validly used to determine the effect if

Colton were to change his maximum number of hours available to work per week from $20$

to $25?$ If so$,$ what would be the increase in the total profit?

i$)$ If$,$ in addition to the change for Colton, from $($h$),$ Abigail also were to change her maximum

number of hours available to work per week from $40$ to $35,$ what would happen to the

optimal solution and the total profit? Use the $100\%$ rule to answer this question.

up company that produces bike locks. Abigail and Bruce each are available to work a maximum

of $40$ hours per week at the company, while Colton is available to work a maximum of $20$

hours per week. The company makes two different types of locks: a u$-$lock and a cable lock.

To make a lock, Abigail assembles the inside mechanical parts of the lock while Bruce

produces the casing. Colton is responsible for taking orders and shipping the locks. The amount

of time required for each of these tasks is shown below.

Each U$-$lock built and shipped yields a profit of $$30,$ while each cable lock yields a profit of

$$20.$ The three partners now want to determine how many locks of each type should be

produced per week to maximize the total profit.

a$)$ Formulate a linear programming model for this problem and solve the model using MS

Excel. Note that the optimal solution may be non$-$integer. Consider the answer as a

recommendation for the average number of locks of each type to produce each week over

the course of a year.

b$)$ Use Solver to find the optimal solution and total profit as the unit profit of each lock is

varied. Fill in the two tables below and estimate the allowable range for the unit profit of

each type of lock.

Table $1.$ Keeping Price of cable locks Fixed

Table $2$: Keeping Price of U$-$locks Fixed

c$)$ Use Solver to fill in the following tables to see the impact of multiple changes at the same

time by entering the optimal solution for both products in each cell.

d$)$ Use Solver to fill in the three tables below to see the impact of varying the righthand

sides on the constraints. For the last column, determine the difference between the profit

for that row and the row above it$.$