• Write the Excel formula for each cell marked with ? in column B of the spreadsheet below, and label each formula clearly.
• List all the constraints in terms of the cell positions in the spreadsheet below.

A small real estate company successfully won a bid from the local government for a piece of land (about 20,000 square-foot) to expand its business by building a new block of high-profile serviced apartments and a car parking lot in the basement of the block. A unique feature of this high-profile serviced apartment is its security and privacy. It offers flexibility in terms of the rental agreement. Such as Daily rental is offered instead of the monthly rental for busy international entrepreneurs who often fly overseas but would like a home-like serviced apartment. In order to attract more occupants for the serviced apartments, the company would also like to build a shopping mall with some convenience stores and supermarkets on the first two floors of the block of serviced apartments. The company has decided the new block would have three apartment types: one-bedroom (suitable for singles or couples without children), two-bedroom (suitable for working couples with 1 child) and three-bedroom (suitable for working couples with more than 1 child) but does not know the number of apartments to be built. The company wants to make as much net income from the new apartments as possible. Expenditures on the building project will be heavy in the beginning, but hopefully, the investment pays itself over time; the new block and parking lot would be fully depreciated after 10 years. This 10-year forecasted income statement is done in a Microsoft Excel spreadsheet using Excels Solver, and you are required to write Excel formulas and constraints to help with the calculations of this problem see spreadsheet below. Microsoft Excels Solver will calculate the number of apartments to be built in cells B2 to B4 for each kind of apartment.

The Constants section of the spreadsheet (see cells A6 to cells B34) contains the following:

The tax rate is expected to be 30% per year (cell B6).

The company reasons that revenue from the shopping mall depends on the number of people staying in each type of apartment, occupancy rate, and how much money people are likely to spend on supermarkets and stores. The company thinks that a single person will favor quick shopping with infrequent visits to supermarkets and will spend the least amount of money in supermarkets. However, a couple without children will spend more time shopping in supermarkets, with frequency about once a week visit in supermarkets and will be more likely to spend more than single persons. Families having kids are more likely to shop daily in supermarkets and spend the most in supermarkets. Based on these estimates, the company comes up with some expected daily shopping revenue for each kind of apartment see table below.

	One Bedroom	Two Bedroom	Three Bedroom
Square feet used per apartment	225 (cell B11)	400 (cell B19)	800 (cell B27)
Expected daily rental revenue ($) per apartment	83 (cell B12)	115 (cell B20)	175 (cell B28)
Expected occupancy rate (%)	70 (cell B13)	65 (cell B21)	60 (cell B29)
Construction cost ($) per square foot	250 (cell B14)	220 (cell B22)	200 (cell B30)
Average daily upkeep cost ($) per apartment	20 (cell B15)	25 (cell B23)	30 (cell B31)
The average number of people per apartment	1 (cell B16)	2 (cell B24)	4 (cell B32)
Expected daily shopping mall revenue ($) per person	10 (cell B17)	25 (cell B25)	20 (cell B33)
Expected shopping mall cost (% of shopping mall revenue)	75 (cell B18)	55 (cell B26)	65 (cell B34)

Each car park space will take up 200 square feet (cell B9). The cost of grading, paving, and painting lines of the parking lot is $50 per square foot (cell B10).

The number of business days is 360 days per year, thus 3600 business days (cell B8) total for 10 years.

The total available space to build the new block and the parking lot is 20,000 square feet (cell B7).

The Calculations section of the spreadsheet (see cell A36 to cell B55) contains immediate results that will be used in the income statement of the spreadsheet, and/or will be used as constraints. Write Excel formula for the following:

• The number of new apartments (cell B36) is the total of all new one-bedroom, two-bedroom, and three-bedroom apartments.
• Total square feet used for all new apartments and total square feet used for the new parking lot are in cells B37 and B38 respectively. Each new apartment will have one parking space.
• The parking lots construction cost (cell B40) is the function of the lots square footage and the cost per square foot.
• Total rental revenue for the next 10 years for each kind of apartment (cells B41, B42, B43) is a function of how many apartments there are, how often they are rented, and the rental price per day.
• The total construction cost for each kind of apartment (cells B44, B45, B46) is a function of how many apartments are built and how much each apartment costs to build.
• Daily upkeep cost for an apartment is only charged if that apartment is rented that day. Total upkeep cost for the next 10 years for each kind of apartment (cells B47, B48, B49) is a function of how many apartments are built, how often they are rented, and the upkeep cost per day.
• Shopping mall revenue for the next 10 years for each kind of apartment (cells B50, B51, B52) is a function of how many apartments are built, how often apartments are rented, how many people stay in them, and how much money each person is likely to spend in the shopping mall per day.
• Shopping mall costs for each kind of apartment (cells B53, B54, B55) are a percentage of shopping mall revenue for the respective apartment type.

The Income Statement section of the spreadsheet (see cell A57 to cell B67) contains the net income that the Solver is expected to maximize. Write Excel formula for the following:

• Rental revenue totaled from all three kinds of apartments (cell B57).
• Shopping mall revenue totaled from all three kinds of apartments (cell B58).
• Total revenue gained from apartment rentals and shopping malls (cell B59).
• Total apartment construction costs and total apartment upkeep costs for all three kinds of apartments are in cells B60 and B61 respectively.
• Construction cost for the car parking lot (cell B62).
• Shopping mall costs are for all three types of apartments (cell B63).
• Income tax expense is zero if pre-tax revenue is zero or negative; otherwise, apply the tax rate to the pre-tax revenue (cell B66)).
• Net income after tax (cell B67).

Constraints part

Formulate constraints from the following statements made by the company:

The company would like to use all 20,000 square feet available space for all the new apartments and the car parking lot, but the company understands this may not be possible, and it expects at least 19,500 square feet would be used.

The company does not want to build a block that has more than 30 apartments as too many apartments would be difficult to manage, but at least 20 apartments should be in the new block. Among these new apartments, there should be at least 5 one-bedroom, 5 two-bedroom, and 10 three-bedroom apartments. Net income over the 10 years should be positive. Also, a fractional part of an apartment cant be built.