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 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 the rental agreement such that 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 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 for each kind of apartment see table below.

Kindly please show all workings(formulas).

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 83 (cell B12) 115 (cell B20) 175 (cell B28) apartment 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 20 (cell B15) 25 (cell B23) 30 (cell B31) apartment Average number of people per 1 (cell B16) 2 (cell B24) 4 (cell B32) apartment Expected daily shopping mall revenue 10 (cell B17) 25 (cell B25) 20 (cell B33) ($) per person Expected shopping mall cost (% of 75 (cell B18) 55 (cell B26) 65 (cell B34) shopping mall revenue) 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). B 0.3 20000 3600 200 50 A 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 7 Total footage available 8 Number of business days 9 Parking: square feet per space 10 Parking: construction cost per sq foot 11 One bedroom: Square feet 12 One bedroom: Rental per day 13 One bedroom: Occupancy rate 14 One bedroom: Construction cost per sq foot 15 One bedroom: Upkeep cost per day 16 One bedroom: Avg number of people 17 One bedroom: Shopping mall revenue per person 18 One bedroom: Shopping mall cost percentage 19 Two bedroom: Square feet 20 Two bedroom: Rental per day 21 Two bedroom: Occupancy rate 22 Two bedroom: Construction cost per sq foot 23 Two bedroom: Upkeep cost per day 24 Two bedroom: Avg number of people 25 Two bedroom: Shopping mall revenue per person 26 Two bedroom: Shopping mall cost percentage 27 Three bedroom: Square feet 225 83 0.7 250 20 1 10 0.75 400 115 0.65 220 25 2 25 0.55 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 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 ? [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] 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] The "Calculations" section of the spreadsheet (see cell A36 to cell 055) 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 lot's construction cost (cell B40) is the function of the lot's 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 can't be built. A1 fx Changing Cells A B 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Changing Cells Number of one bedroom appartments Number of two bedroom appartments Number of three bedroom appartments Constants Tax rate expected Total footage available Number of business days Parking: square feet per space Parking: construction cost per sq foot One bedroom: Square feet One bedroom: Rental per day One bedroom: Occupancy rate One bedroom: Construction cost per sq foot One bedroom: Upkeep cost per day One bedroom: Avg number of people One bedroom: Shopping mall revenue per person One bedroom: Shopping mall cost percentage Two bedroom: Square feet Two bedroom: Rental per day Two bedroom: Occupancy rate Two bedroom: Construction cost per sq foot Two bedroom: Upkeep cost per day Two bedroom: Avg number of people Two bedroom: Shopping mall revenue per person Two bedroom: Shopping mall cost percentage 0.3 20000 3600 200 50 225 83 0.7 250 20 1 10 0.75 400 115 0.65 220 25 2 25 0.55 27 28 29 30 31 32 33 34 35 36 800 175 0.6 200 30 4 20 0.65 37 38 39 40 41 42 43 44 45 Three bedroom: Square feet Three bedroom: Rental per day Three bedroom: Occupancy rate Three bedroom: Construction cost per sq foot Three bedroom: Upkeep cost per day Three bedroom: Avg number of people Three bedroom: Shopping mall revenue per person Three bedroom: Shopping mall cost percentage Calculations Number of units built in new block Square footage used - apartments Square footage used - parking lot Total square footage used Construction cost - parking lot Total revenue - One bedroom Total revenue - Two bedroom Total revenue - Three bedroom Total construction cost - One bedroom Total construction cost - Two bedroom Total construction cost - Three bedroom Total upkeep cost - One bedroom Total upkeep cost - Two bedroom Total upkeep cost - Three bedroom Shopping mall revenue - One bedroom Shopping mall revenue - Two bedroom Shopping mall revenue - Three bedroom 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 Shopping mall costs - One bedroom Shopping mall costs - Two bedroom Shopping mall costs - Three bedroom Income Statement Apartment rental revenue Shopping mall revenue Total revenue Apartment construction costs Apartment upkeep costs Parking lot construction cost Shopping mall costs Total costs Pre-tax revenue Income tax expense Net Income 63 64 65 66 67 68 69 70