Write the Excel formula for each cell marked with ? in column B of the spreadsheet below, and label each formula clearly.

A small real estate company successfully won a bid from the local government 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 special feature of this high-profile serviced apartment is its security and privacy. It offers flexibility in terms of rental agreement such that daily rental is offered instead of 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 formula 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) contain the following:

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 supermarket and stores. The company thinks that a single person will favour quick shopping with infrequent visits in 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 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)
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).

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

Total available space to build the new block and parking lot is 20,000 square foot (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 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.
• 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 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 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 form apartment rentals and shopping mall (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: each constraint is worth 1 mark.

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.

1 Changing Cells 2 Number of one bedroom appartments 3 Number of two bedroom appartments 4 Number of three bedroom appartments 5 Constants 6 Tax rate expected 0.3 7 Total footage available 20000 8 Number of business days 3600 9 Parking: square feet per space 200 10 Parking: construction cost per sq foot 50 11 One bedroom: Square feet 225 12 One bedroom: Rental per day 83 13 One bedroom: Occupancy rate 0.7 14 One bedroom: Construction cost per sq foot 250 15 One bedroom: Upkeep cost per day 20 16 One bedroom: Avg number of people 1 17 One bedroom: Shopping mall revenue per person 10 18 One bedroom: Shopping mall cost percentage 0.75 19 Two bedroom: Square feet 400 20 Two bedroom: Rental per day 115 21 Two bedroom: Occupancy rate 0.65 22 Two bedroom: Construction cost per sq foot 220 23 Two bedroom: Upkeep cost per day 25 24 Two bedroom: Avg number of people 2 25 Two bedroom: Shopping mall revenue per person 25 26 Two bedroom: Shopping mall cost percentage 0.55 27 Three bedroom: Square feet 800 28 Three bedroom: Rental per day 175 29 Three bedroom: Occupancy rate 0.6 30 Three bedroom: Construction cost per sq foot 200 31 Three bedroom: Upkeep cost per day 30 32 Three bedroom: Avg number of people 4 33 Three bedroom: Shopping mall revenue per person 20 34 Three bedroom: Shopping mall cost percentage 0.65 B ? [1 mark] ? [2 marks] ? [1 mark] ? [1 mark] ? [1 mark] ? (1.5 marks] ? [1.5 marks] ? (1.5 marks] ? [1.5 marks] ? (1.5 marks] ? (1.5 marks] ? (1.5 marks] ? (1.5 marks] ? [1.5 marks] ? (1.5 marks] ? [1.5 marks] ? (1.5 marks] ? [1 mark] ? [1 mark] ? [1 mark] - A 35 Calculations 36 Number of units built in new block 37 Square footage used - apartments 38 Square footage used - parking lot 39 Total square footage used 40 Construction cost - parking lot 41 Total revenue - One bedroom 42 Total revenue - Two bedroom 43 Total revenue - Three bedroom 44 Total construction cost - One bedroom 45 Total construction cost - Two bedroom 46 Total construction cost - Three bedroom 47 Total upkeep cost - One bedroom 48 Total upkeep cost - Two bedroom 49 Total upkeep cost - Three bedroom 50 Shopping mall revenue - One bedroom 51 Shopping mall revenue - Two bedroom 52 Shopping mall revenue - Three bedroom 53 Shopping mall costs - One bedroom 54 Shopping mall costs - Two bedroom 55 Shopping mall costs - Three bedroom 56 Income Statement 57 Apartment rental revenue 58 Shopping mall revenue 59 Total revenue 60 Apartment construction costs 61 Apartment upkeep costs 62 Parking lot construction cost 63 Shopping mall costs 64 Total costs 65 Pre-tax revenue 66 Income tax expense 67 Net Income - ? [1 mark] ? [1 mark] ? [1 mark] ? [1 mark] ? [1 mark] ? [1 mark] ? [1 mark] ? [1 mark] ? [1 mark] ? [2 marks] ? [1 mark]