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 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 Excel's Solver, and you are required to write Excel formula and constraints to help with the calculations of this problem - see spreadsheet below. Microsoft Excel's 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 Two Bedroom Bedroom Three Bedroom Square feet used per apartment 225 (cell 400 (cell 800 (cell B27) B11) B19) (cell 175 (cell B28) per apartment B20) Expected daily rental revenue (S) 83 (cell B12) 115 70 (cell B13) 65 (cell B21) 60 (cell B29) Expected occupancy rate (%) Construction cost ($) per square 250 foot B14) (cell 220 B22) (cell 200 (cell B30) Average daily upkeep cost (S) per 20 (cell B15) 25 (cell B23) 30 (cell B31) apartment Average number of people perl (cell B16) 2 (cell B24) 4 (cell B32) apartment Expected daily shopping mall 10 (cell B17) 25 (cell B25) 20 (cell B33) revenue ($) 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). 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 10 Parking: construction cost per sq foot 9 Parking square feet per space 11 One bedroom: Square feet 12 One bedroom Rental per day 03 20000 3600 200 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: Aug number of people 25 Two bedroom: Shopping mall revenue per person 26 Two bedroom Shopping mall cost percentage 27 Three bedroom Square feet 28 Three bedroom Rental per day 29 Three bedroom Occupancy rate 30 Three bedroom: Construction cost per sq foot 50 225 83 0.7 250 20 1 10 0.75 400 115 0.65 220 25 2 25 0.55 800 175 0.6 200 30 4 31 Three bedroom Upkeep cost per day 32 Three bedroom Aug number of people 33 Three bedroom Shopping mall revenue per person 20 34 Three bedroom Shopping mall cost percentage 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 065 B ?[1 mark] ? 12 marks] [1 mark] ? [1 mark] ?[1 mark] ?[1.5 marks] ?[1.5 marks] 211.5 marks] [1.5 marks] ? [1.5 marks] ?[1.5 marks] ?11.5 marks] ?[1.5 marks] ?[1.5 marks] ?[1.5 marks] 115 marks] ?11.5 marks] 2 [1 mark] ?[1 mark] ?[1 mark] ? [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 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 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. 2 Information Management - Assignment 2 answer sheet Student FULL Name: Student ID: 35 Calculations 36 37 338 339 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 B marks 1 2 1 1 1 1.5 1.5 5555555 1.5 1.5 1.5 1.5 1.5 1.5 1.5 1 1 54 55 56 Income Statement 57 1 58 59 60 61 62 63 64 65 66 67 Constraints: 1 Constraints: 234 5 6 7 8 9 10 11 12 Page 1 of 2 1 1 1 1 1 1 1 1 1 2 1 Note: There are 12 blank cells provided, but it does not mean that there are 12 constraints