JUST WANT ANSWERS FOR THIS Thank you
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 goverment 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. 225 One Two Three Bedroom Bedroom Bedroom Square feet used per apartment (cell 400 (cell 800 (cell B27) B11) 319) Expected daily rental revenue (59 83 (cell B12) 115 (cell|175 (cell B28) per apartment 320) Expected occupancy rate (%) 70 (cell B13)65 (cell B21) 60 (cell B29) Construction cost (S) per square 250 (celi 220 (cell 200 (cell B30) foot B14) B22) Average daily upkeep cost (5) per 20 (cell B15) 25 (cell B23) 30 (cell B31) apartment Average number of people per (cell B16) 2 (cell B24) (cell B32) apartment Expected daily shopping mall 10 (cell B17) 25 (cell B25) 20 (cell B33) revenue (S) 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 apartments 3 Number of two bedroom apartments 4 Number of three bedroom appartments 5 Constants Taxat expected 03 7 Total footage aailable 20000 8 Number of business days 3600 9 Parlong square feet per space 200 10 Parking construction cost per a 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 sa 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 065 22 Two bedroom Construction cost per sq foot 220 23 Two bedroom Upkeep cost per day 25 24. Two bedroom: Aug 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 29 Three bedroom Occupancy rate 06 30 Three bedroom Construction cost per sq foot 200 31 Three bedroom Upkeep cost per day 30 32 Three bedroom Aug number of people 4 33. Three bedroom Shopping mall revenue per person 20 34. Three bedroom Shopping mall cost percentage 06 175 B 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 56. 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 54 Total costs 65 Pre-tax revenue 66 Income tax expense 57 Net Income ? [1 mark) ? [2 marks] ? 11 mark] 211 mark) ? [1 mark] 2015 marks! 2115 marks] 2015 marks] [15 marks] 2015 marks) 2015 marks] 7115 marks] 2015 marks ? 15 marks] ? [15 marks] ? 115 marks] ? 115 marks) ? [1 mark] ? [1 mark] ? [1 mark] ? [1 mark] 21 mark] ? [1 mark) 21 mark) ? [1 mark) ? [1 mark] ? 11 mark] ? [1 mark] ? [1 mark) ? 12 marks] 211 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. Information Management - Assignment 2 answer sheet Student FULL Name: Student ID: B marks 35 Calculations 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 Income Statement 57 58 59 60 61 62 63 64 65 66 67 1 2 1 1 1 15 1.5 15 1.5 15 15 1.5 1.5 15 1.5 1.5 1.5 1 --- 1 1 1 1 1 1 1 1 1 2 1 Constraints: Constraints: UN- 1 2 4 5 6 7 8 9 Page 1 of 2 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 goverment 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. 225 One Two Three Bedroom Bedroom Bedroom Square feet used per apartment (cell 400 (cell 800 (cell B27) B11) 319) Expected daily rental revenue (59 83 (cell B12) 115 (cell|175 (cell B28) per apartment 320) Expected occupancy rate (%) 70 (cell B13)65 (cell B21) 60 (cell B29) Construction cost (S) per square 250 (celi 220 (cell 200 (cell B30) foot B14) B22) Average daily upkeep cost (5) per 20 (cell B15) 25 (cell B23) 30 (cell B31) apartment Average number of people per (cell B16) 2 (cell B24) (cell B32) apartment Expected daily shopping mall 10 (cell B17) 25 (cell B25) 20 (cell B33) revenue (S) 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 apartments 3 Number of two bedroom apartments 4 Number of three bedroom appartments 5 Constants Taxat expected 03 7 Total footage aailable 20000 8 Number of business days 3600 9 Parlong square feet per space 200 10 Parking construction cost per a 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 sa 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 065 22 Two bedroom Construction cost per sq foot 220 23 Two bedroom Upkeep cost per day 25 24. Two bedroom: Aug 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 29 Three bedroom Occupancy rate 06 30 Three bedroom Construction cost per sq foot 200 31 Three bedroom Upkeep cost per day 30 32 Three bedroom Aug number of people 4 33. Three bedroom Shopping mall revenue per person 20 34. Three bedroom Shopping mall cost percentage 06 175 B 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 56. 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 54 Total costs 65 Pre-tax revenue 66 Income tax expense 57 Net Income ? [1 mark) ? [2 marks] ? 11 mark] 211 mark) ? [1 mark] 2015 marks! 2115 marks] 2015 marks] [15 marks] 2015 marks) 2015 marks] 7115 marks] 2015 marks ? 15 marks] ? [15 marks] ? 115 marks] ? 115 marks) ? [1 mark] ? [1 mark] ? [1 mark] ? [1 mark] 21 mark] ? [1 mark) 21 mark) ? [1 mark) ? [1 mark] ? 11 mark] ? [1 mark] ? [1 mark) ? 12 marks] 211 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. Information Management - Assignment 2 answer sheet Student FULL Name: Student ID: B marks 35 Calculations 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 Income Statement 57 58 59 60 61 62 63 64 65 66 67 1 2 1 1 1 15 1.5 15 1.5 15 15 1.5 1.5 15 1.5 1.5 1.5 1 --- 1 1 1 1 1 1 1 1 1 2 1 Constraints: Constraints: UN- 1 2 4 5 6 7 8 9 Page 1 of 2 10 11 12 Note: There are 12 blank cells provided, but it does not mean that there are 12 constraints
