Using microsoft excel and the solver ad in and solver sensitivity report solve t
ID: 3823259 • Letter: U
Question
Using microsoft excel and the solver ad in and solver sensitivity report solve the following question:
Cox Cable Company needs to lease warehouse storage space for five months at the start of the year. It knows how much space will be required in each month. However, since these space requirements are quite different, it may be economical to lease only the amount needed each month on a month-by-month basis. On the other hand, the monthly cost for leasing space for additional months is much less than for the first month, so it may be desirable to lease the maximum amount needed for the entire five months. Another option is the intermediate approach of changing the total amount of space leased (by adding a new lease and/or having an old lease expire) at least once but not every month. Two or more leases for different terms can begin at the same time.
The space requirements (in square feet) and the leasing costs (in dollars per thousand square feet) are given in the tables below.
Month
Space requirements
Lease length
Lease cost
Jan
15,000
1 month
$280
Feb
10,000
2
450
Mar
20,000
3
600
April
5000
4
730
May
25,000
5
820
The task is to find a leasing schedule that provides the necessary amounts of space at the minimum cost.
A.) Determine the optimal leasing schedule and the optimal total cost.
B.) Consider what happens when the cost of a 5-month lease (currently $820) changes. Construct a graph showing how the optimal total cost varies with the cost of a 5-month lease, over the range from $800 to $1000.
C.) Consider instead what happens when the timing of space requirements shifts. In particular, suppose that March requirements drop and April requirements rise by the same amount. Construct a table to show, for shift amounts up to 15,000 square feet, how the shift alters the optimal total cost.
Month
Space requirements
Lease length
Lease cost
Jan
15,000
1 month
$280
Feb
10,000
2
450
Mar
20,000
3
600
April
5000
4
730
May
25,000
5
820
Explanation / Answer
Decision Variables
Length
1
2
3
4
5
Cost
$ 280.00
$ 450.00
$ 600.00
$ 730.00
$ 820.00
Month
Jan
Feb
Mar
Apr
May
Rqd Sqft
15,000
10,000
20,000
5,000
25,000
Objective Function
Min Cost
$ 1,65,00,000
Constraints
Jan
Feb
Mar
Apr
May
Cost
Cost/sq ft
1
0
0
5000
0
10000
$ 42,00,000
$ 280
2
0
0
0
0
0
$ -
$ -
3
0
0
0
0
0
$ -
$ -
4
0
0
0
0
0
$ -
$ -
5
15000
0
0
0
0
$ 1,23,00,000
$ 820
$ 220
Jan
Feb
Mar
Apr
May
Amounts
15000
15000
20000
15000
25000
>=
>=
>=
>=
>=
Required
15000
10000
20000
5000
25000
a. Determine the optimal leasing schedule. What is the optimal total cost and the corresponding schedule
1 5mo lease in January for 15000 sq ft, 1 1mo lease in Mar for 5000 sq ft, and 1 1mo lease in May for 10000 sq ft. The total cost is $16,500,000.
Decision Variables
Length
1
2
3
4
5
Cost
$ 280.00
$ 450.00
$ 600.00
$ 730.00
$ 820.00
Month
Jan
Feb
Mar
Apr
May
Rqd Sqft
15,000
10,000
20,000
5,000
25,000
Objective Function
Min Cost
$ 1,65,00,000
Constraints
Jan
Feb
Mar
Apr
May
Cost
Cost/sq ft
1
0
0
5000
0
10000
$ 42,00,000
$ 280
2
0
0
0
0
0
$ -
$ -
3
0
0
0
0
0
$ -
$ -
4
0
0
0
0
0
$ -
$ -
5
15000
0
0
0
0
$ 1,23,00,000
$ 820
$ 220
Jan
Feb
Mar
Apr
May
Amounts
15000
15000
20000
15000
25000
>=
>=
>=
>=
>=
Required
15000
10000
20000
5000
25000
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.