Appendix 2 Data

LP Solver template Define the decision variable names in this row (cells B5:E5) Give each decision variable a starting value (a guess will do!) Parameters Parameters corresponding to Constraint Name Parameters corresponding to Constraint Name Parameters corresponding to Constraint Name Parameters corresponding to Constraint Name Parameters corresponding to Constraint Name Add or delete rows and information for constrained resources as needed Objective function Min or Max (specifiy which one!) Constraints LHS >=,<= or = RHS Your initials + Constraint Name Your initials + Constraint Name Your initials + Constraint Name Your initials + Constraint Name Your initials + Constraint Name Your initials + Constraint Name Add or delete rows and information for constrained resources as needed

With the flagship Cluster Adelaide located at the exclusive address of 77-91 Rundle Mall, Adelaide SA 5000, you are currently in discussion with a local award-winning architect to redesign the floor space on levels one and two of the former iconic Harris Scarfe space.

Objective:Using the floorplan provided to you by Centre Management at Rundle Place, you need decide how to maximise the monthly revenue from membership fees for Cluster Adelaide.

Decision Variables: To cater to a range of potential members, you have chosen to include a mixture of Hot Desks, Reserved Desks, Team Suites and Private Offices in the design layout planned for the site.The monthly membership fee for the various office designs is given below in Table 1:

Hot Desk

Reserved Desk

Team Suite

Private Office

Membership Fees

$300

$520

$1,260

$940

Table 1: Summarised membership fees for each office design

Constraints: Cluster Adelaide has a number of constraints to meet when designing the office layout:

Space Allocation: Cluster Adelaide will use utilise at most the 3300 square metres (m²) available for hire on levels one and two of Rundle Place.You intend to keep the layout flexible with the potential to open up the space for event hire post COVID-19.From your market research, a comfortable Hot Desk should be allocated with 3m². The space allocated for a Reserved Desk should double a hot desk. A Meeting Suite suitable for start-up teams requires 40 m² of space while a Private Office designed for professional services (e.g. accountants, lawyers, entrepreneurs) should be 24 m² in size.

Diversity: To allow for a Harris Scarfe collaborative environment, you would like the number of Hot Desks and Reserved Desks combined to be at least 4 times the number of Team Suites.

Team Suites:With the potential of start-up teams being a strong focus for Cluster, you would like at least 30 Team Suites on the premises.

Private Offices:Members utilising Private Offices are expected to meet with clients on a regular basis.The architect recommends at most 40 Private Offices in the design to limit external access.

Cloud Storage:Cluster offers every membership account with fast and secure data storage on the cloud.Members for Hot Desk and Reserved Desk are allocated with 75GB (Gigabyte) respectively.Team Suite enjoys 250GB while a Private Office is allocated 120GB.Cluster's head office in Copenhagen has negotiated a commercial account supporting no more than 30TB (Terabyte).

Maintenance: Apart from the regular housekeeping to maintain cleanliness, you also awarded a tender to a local company for a monthly steam clean and general maintenance at Cluster Adelaide.With a strong crew of 11 staff, they will spend 5 minutes checking each Hot Desk and another 5 minutes checking each Reserved Desk, ensuring the safety of power points and comfort of ergonomic chairs offered at Cluster Adelaide.Each Team Suite is expected to take 15 minutes with more amenities in the room while a Private Office will take 10 minutes to do an inspection and the necessary upkeep.The team is expected to complete the task between 5am to 8am on the first day of every month.

(a)(8 marks) Before you begin!Your model for this part will look like the model in the Week 4 notes (Slide 12).Formulate a linear programming model for this problem, filling in the template below. Type up the full mathematical model in Word and include it here.

For full marks fill in the template provided below, clearly indicating:

The decision variables. Define them precisely.

The objective. Using your decision variables, formulate the objective function.

The constraints. Using your decision variables, formulate these constraints.

Decision Variables

Objective and Objective Function

Constraints

(b)(3 marks) Before you begin!Part (a) needs to be completed first before entering the model in Excel you'll miss out on 7 marks!Want an Excel template that you just have to fill in? Read below J

To finish this question:

1.The Linear Programming template for this question is in Assignment 2 Data.xlsx in the worksheet Appendix 2. Use this to enter your LP model.

2.Enter your model from (a) into this template. All EXCEL output will need to bear your e-mail ID. To ensure this, you will need to save your EXCEL file as 'E-mail ID Assignment 2.xlsx' BEFORE you run Solver. In addition, your constraint names should begin with your initials, e.g. JT Space Allocation if your initials are JT.

3.Use EXCEL Solver to obtain a solution to the linear programming model from part (a), together with an Answer Report and a Sensitivity Report.

4.Provide a screenshot of your solved EXCEL spreadsheet and the Answer and Sensitivity reports below.

discuss the output

(c)For the problem solved in part (b), interpret the shadow price for the Cloud Storage and Maintenance constraints.For each interpretation state:

i.The range of feasibility and

ii.Interpret the shadow price for the constraint in question.

Which service would you recommend to maximise monthly revenue from membership fees? All else as initially specified.Explain briefly.

(d)In preparation for the opening, you are actively in talks with different government departments and agencies operating from the Adelaide CBD.The Department of the Premier and Cabinet expressed their interest in leasing all Private Offices at Cluster Adelaide.

However, the leasing agreement is conditional to additional upgrades such as sound proofing, video conferencing equipment, tinted glass for privacy and secured storage space.You did the running around obtaining quotes and consulted Lars R in Copenhagen making a joint decision to charge a new price of $1,800 per month for Private Offices.

Explain whether the solution obtained in part (b) would still be optimal in this case, and how the solution would change, if at all? Which of the Solver reports helps you answer this question and how?In this Appendix, explain briefly making reference to the appropriate range of optimality and attach, if appropriate,