Question

44. Mega-Mart, a discount store chain, is to build a new store in Rock Springs. The parcel of land the company has purchased is large enough to accommodate a store with 140,000 square feet of floor space. Based on marketing and demographic surveys of the area and historical data from its other stores, Mega-Mart estimates its annual profit per square foot for each of the store's departments to be as shown in the table on the following page. Each department must have at least 15,000 square feet of floor space, and no department can have more than 20% of the total retail floor space. Men's, women's, and children's clothing plus housewares keep all their stock on the retail floor: however, toys, electronics, and auto supplies keep some items (such as bicycles, televisions, and tires in inventory. Thus, 10% of the total retail floor space devoted to these three departments must be set aside outside the retail area for stocking inventory. Mega-Mart wants to know the floor space that should be devoted to each department in order to maximize profit. a. Formulate a linear programming model for this problem. b. Solve this model by using the computer

pla solve the problem step by step using excel., pla describe in detail

Answer 1

Linear programing

we have to find

• the floor space for each department- decision variable
• so that profit is maximized- objective
• subject to constraints

Retail f nvin

ildreク Den VsnabFloo, Spau all.ttd art met 2 S 38灯

3. Consbr ains nimunm depaytment (C Masmun 20 Total reteil ouch depaitmert

Xsx,刈

Mens clothing womens cdothing childrens clothing House wares Electronics auto supplies Profit per ft2 4.25 5.1 4.5 4.9 3.8 Decision variables Mens clothing womens dothing childrens dothing House wares Electronics auto supplies floor space for toys, electronics, autospares 9+K9+19 09 F12 0.1 F12 floor space for mens, womens, children, housewares F9+69-H9+19 Totol floor spaceF12+H12 Total retoil floor spoce-F13+H13 F14+H14 12 retail floor space retail floor spoce inventory(10%) space 13 -H12 inventory(10%) space Inventory spaceF1 15 Maximize profit SUMPRODUCT(F6:16, 9:19) 17 18 19 Constraints Maximum 140000 Total floor space12 140000 minimum 15000 sqft for each dept. maximum 20% of totoal retail floor space Non negative- variables cannot be negative 919 F9L9 F91 0.2 113 25

Mens clathing womens clothing childrens cdothing House wares Electronics auto supplies Profit per ft2 3.В Decision variables Mens clothing womens clothing childrens clothing House wares Electronics auto supplies 10 OpenSolver Madel floor space for toys, electronics, utospares retoil fioor space inventory(10%) space fioor spoce for mens, womens, children, housewares retoil fioor spoce Inventory(10%) spoce Totul loor space Total retal floor spare inventory space What is AutoModelr 13 AutoModel is a feature of Ope Solver that tries to automaticaly determine the problen you are trying to optimise by structure of the spresdsheet. It wil urnts best quess into a Sclver model, hich you cen then edtin ths window objective Cell $Gs16 Variable Ce 9:$9 15 Meximize profit larget value: 17 18 19 Constraints Maximum 140000 Tatal floor space minimum 15000 sq.ft for each dept. maximum 20% of totoal retail floor space Non negstive-variables cannot be negstive 40000 21 23 25 Add constrer: 27 Delete seected const Make unconstraned var able c Show named rarges in corstrai 30

M21 Mens clothing womens clothing childrens clothing Toys House wares Electronics auto supplies Profit per ft2 4.25 5.1 5.2 4.9 Decision variables Mens clothing womens clothing childrens clothing Toys House wares Electronics auto supplies 9 26634.615 26634.615 15000 26634.62 floor space for toys, electronics, autospares retail floor space Inventory(10%) space 68269.23floor space for mens, womens, children, housewares 71730.769 Total floor space139999.999 12 13 14 15 16 17 retail floor spoce Inventory(10%) space 61442.307 71730,769 Total retail floor space 133173.076 6826.923 Inventory space 6826.923 Maximize profit 655028.841 Constraints Maximum 140000 Total floor space minimum 15000 sq.ft for each dept. maximum 20% of totoal retai ul floor space Non negative-variables cannot be negative 19 140000.00 140000 21 15000 26634.6152 15000 23 25 26 27