please use the template answer sheet provided at he end to answer
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. T 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 faxour 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 Bedroom Square feet used per apartment 225 (cell 400 (cell B19) 800 (cell B27) B11) 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 220 (cell B22) 200 (cell B30) B14) Average daily upkeep cost (S) 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 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 B B 83 (cell B7). 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 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 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 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 25 A B 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 28 Three bedroom: Rental per day 29 Three bedroom: Occupancy rate 30 Three bedroom: Construction cost per sq foot 31 Three bedroom: Upkeep cost per day 32 Three bedroom: Avg number of people 33 Three bedroom: Shopping mall revenue per person 34 Three 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 800 175 0.6 200 30 4 20 0.65 ? ? ? ? ? ? ? ? ? - 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 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 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 2 L Information Management - Assignment 2 answer sheet Student FULL Name: Student ID: B marks 35 Calculations 36 1 2 37 1 1 38 39 40 41 1 1.5 42 1.5 1.5 43 44 45 1.5 1.5 1.5 46 47 48 49 1.5 1.5 1.5 1.5 1.5 50 | 51 52 1.5 1 53 54 1 1 55 56 Income Statement 57 1 58 1 1 59 60 1 61 1 1 62 63 1 64 1 65 1 2 66 67 1 Constraints: Page 1 of 2 Constraints: 1 2 N 3 4 5 6 7 8 colo 9 10 11 12 Note: There are 12 blank cells provided, but it does not mean that there are 12 constraints. 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. T 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 faxour 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 Bedroom Square feet used per apartment 225 (cell 400 (cell B19) 800 (cell B27) B11) 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 220 (cell B22) 200 (cell B30) B14) Average daily upkeep cost (S) 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 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 B B 83 (cell B7). 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 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 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 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 25 A B 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 28 Three bedroom: Rental per day 29 Three bedroom: Occupancy rate 30 Three bedroom: Construction cost per sq foot 31 Three bedroom: Upkeep cost per day 32 Three bedroom: Avg number of people 33 Three bedroom: Shopping mall revenue per person 34 Three 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 800 175 0.6 200 30 4 20 0.65 ? ? ? ? ? ? ? ? ? - 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 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 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 2 L Information Management - Assignment 2 answer sheet Student FULL Name: Student ID: B marks 35 Calculations 36 1 2 37 1 1 38 39 40 41 1 1.5 42 1.5 1.5 43 44 45 1.5 1.5 1.5 46 47 48 49 1.5 1.5 1.5 1.5 1.5 50 | 51 52 1.5 1 53 54 1 1 55 56 Income Statement 57 1 58 1 1 59 60 1 61 1 1 62 63 1 64 1 65 1 2 66 67 1 Constraints: Page 1 of 2 Constraints: 1 2 N 3 4 5 6 7 8 colo 9 10 11 12 Note: There are 12 blank cells provided, but it does not mean that there are 12 constraints
