Can you set up and solve this case below as an LP problem using Excel Solver LP Simplex function? Please show the excel solver's screenshot that captures values with constrains and Sensitivity Analysis Report.

Situation

ODC had built five 20-story apartment buildings in the NYC area, planning to sell units as top-market condominiums. They had developed many properties before, and had a reputation for high-quality construction and furnishings, and therefore they were concerned that the proper materials were to be used in construction. Moreover, they knew that perspective buyers were very interested in the type of finish-materials used. This five-building project would include 800 condos, with 8 units on each floor of each building. All apartments had two bathrooms and two bedrooms, a large entry hallway, living room and large kitchen, and were approximately 1500 square-feet each in area.

Construction Materials

Specifically, they believed that the materials that were utilized in the three major areas of the units - the bathrooms, kitchens and entryways (called the BKE area, a total of 450 square-feet per apartment), would have the greatest impact on the pricing of the condos. Other than appliances, the most important features were the type of flooring and surface materials that were used in these rooms. These materials would affect the designation of the apartment unit as a luxury, high-end, or average-market unit and determine the selling price of the condo.

The materials to be chosen were marble, ceramic tile or wood. Any one of these could be used for the entire 450 square-feet of BKE flooring and surface throughout the entire unit or they could be combined in any square-footage proportion. ODC had developed a material-rating system that calculated a weighted average of the quality level of the materials used. Marble surfaces were rated 9.0 (the highest being 10.0), ceramic tiles were rated 6.0, and the wood surfaces had a 4.0 rating. The overall quality rating for an apartment was the weighted average of the material-ratings multiplied by the number of square-feet of each material used in the BKE area divided by the total BKE area. For example, a unit that had an equal amount of marble, ceramic and wood (150 square-feet of each), would have a quality-rating of:

9 (150) + 6 (150) + 4 (150) = 6.33

450

ODC could buy all the wood they needed. Ceramic tile was more expensive and in moderate supplies on the market. For the time period relevant to this particular project, they felt that they could buy up to 100,000 square-feet of ceramic tile. Marble was the most expensive material and in short supplies. Their importer could promise at most 50,000 sq.-ft. of marble.

Selling Prices

The luxury apartments, that could bring top prices of $700 per square-foot, required quality ratings of at least 8.0. High-end units required a quality-rating of 6.0 or more, and varied in selling price, but it was believed that the average price would be $500 per sq.-ft. They felt that the average-market units would sell for $400 per sq.-ft. Since ODC had certain minimum requirements for any of their apartments, they would not allow any unit to be below 4.0 in quality-rating. The mark-ups on the condos were proportionately equal at varying cost levels, and therefore ODC thought that they could concentrate on maximizing their sales revenue, to maximize profit margins.

The Market

The real estate market at this time was booming. Buyers were everywhere and everyone wanted to purchase their own condominium rather than rent. Wall Street had ended one of its most prosperous years, employees in the financial industries were flush with bonus money, and there seemed to be an unending demand for quality apartments. The real estate brokers who were marketing the properties for ODC informed them that they could sell a maximum of 300 of the units as luxury condos if they were constructed accordingly. The demand for high-end condos was also strong, and the brokers estimated that they could easily get high-end condo buyers for at most 200 units, and a maximum of 500 of the average-market units could also be sold.