Ask Experts Questions for FREE Help !
    onebutterybagel's Avatar
    onebutterybagel Posts: 1, Reputation: 1
    New Member

    Feb 23, 2016, 03:50 PM
    Linear Programming in Excel
    Evening, I am having trouble answering this problem in Excel. I believe I determined everything properly but I cant figure out how to create an excel model for it. The question reads,
    The company needs warehouse space for storing its goods. Plans now are being made for leasing warehouse storage space over the next five months. Just how much space will be required in each of these months is known. However, since these space requirements are quite different, it may be most economical to lease only the amount needed each month on a month-by-month basis. On the other hand, the additional cost for leasing space for additional months is much less than for the first month, so it may be less expensive 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.
    The space requirement and the leasing costs for the various leasing periods are as follows:

    Month Required Space Leasing Period
    Cost per Sq. Ft.
    1 30,000 sq. ft. 1 $65
    2 20,000 sq. ft. 2 $100
    3 40,000 sq. ft. 3 $135
    4 10,000 sq. ft. 4 $160
    5 50,000 sq. ft. 5 $190

    The objective is to minimize the total leasing cost for meeting the space requirements.

    a) Identify verbally the decisions to be made, the constraints on these decisions, and the overall measure of performance for the decisions.

    b) Convert these verbal descriptions of the constraints and measure of performance into quantitative expressions in terms of the data and decisions. Summarize the model in algebraic form by stating the decision variables, the objective function and constraints.

    c) Formulate a spreadsheet model for this problem. Identify the data cells, the changing cells, the target cell, and the other output cells. Please use the SUMPRODUCT function if possible. Use the Excel Solver to solve the model.

    The answers for decision variables, objective function, and constraints can be found here

    So my question is, how do I set up an excel model for this particular problem.
    paraclete's Avatar
    paraclete Posts: 2,704, Reputation: 171
    Ultra Member

    Feb 23, 2016, 06:27 PM
    I think a better question is are you able to solve the problem using linear programming without a spreadsheet. If so then you know the steps required

Not your question? Ask your question View similar questions


Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.

Check out some similar questions!

Linear Programming [ 0 Answers ]

A linear programming spreadsheet model, in which the objective function (target cell) is maximized contains, among other constraints, a constraint of the form: E4>=E5, where cell E5 contains a constant and E4 depends on the values chosen for the decision variables. The shadow price associated with...

Linear Programming [ 2 Answers ]

A publishing company makes 2 types of magazines on a monthly basis, a Restaurant and Entertainment guide and a Real Estate guide. It distributes the magazine free in Penang island. The company profits come from advertising. Each restaurant and entertainment guide distributed generates $0.50,...

Linear Programming [ 1 Answers ]

What is the usage and application of linear programming with particular reference to banking?

View more questions Search