Background: Small Hotel Expansion decision problem A small hotel owner would like to expand his business by building a new wing of rooms and a car parking lot using an adjoining 20,000 square-foot piece of land because his business has been doing well. Another reason for the expansion is that the hotel's restaurant is often empty leven when the hotel's occupancy is high, and hopefully additional rooms would increase restaurant patrons. The owner has decided the new wing would have three room types: single, double and families but does not know the number of rooms to be built. The owner wants to make as much net income from the new rooms as possible. Expenditures on the expansion will be heavy in the beginning, but hopefully the investment pays itself over time, the new wing 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 wi calculate the number of rooms to be built in cells B3 to BS for each kind of room. The "Constants" section of the spreadsheet (see cells A7 to cells B36) contain the following data: Tax rate is expected to be 30% per year (cell B8). The owner reasons that revenue from the restaurant depends on the number of people staying in each type of room, occupancy rate, and how much money people are likely to spend on meals. The owner thinks that a single person will favour quick, simple meals and will spend the least amount of money on food. However, a couple will spend more time dining and will be more likely to have cocktails and dessert, and people in single or double rooms are likely to dine only once a day in the restaurant. Families having kids are more likely to dine more than once a day in the restaurant, but kids do not usually eat expensive meals. Based on these estimates. the owner comes up with some expected daily restaurant revenue for each kind of room - see table below Single Double Family Square feet used per room 225 (cell B13) 400 (cell B21) 800 (cell B29) Expected daily rental revenue 83 (cell B14) 115 (cell B22) 175 (cell B30) (S) per room Expected occupancy rate (%) 70 (cell B15) 65 (cell B23) 60 (cell B31) Construction cost (8) per 250 (cell B16) 220 (cell B24) 200 (cell B32) square foot 25 (cell B25) 30 (cell B33) 2 (cell B26) 4(cell B34) Average daily upkeep cost (S) 20 (cell B17) per room Average number of people per 1 (cell B18) room Expected daily restaurant 10 (cell B19) revenue (S) per person Expected restaurant cost (% 75 (cell B20) of meal revenue) 25 (cell B27) 20 (cell B35) 55 (cell B28) 65 (cell B36) Each car park space will take up 200 square feet (cell Bi1). The cost of grading, paving and painting lines of the parking lot is $50 per square foot fcell B12). Number of business days is 360 days per year, thus 3600 business days (cell B10) total for 10 years. Total available space to build the new wing and parking lot is 20,000 square foot (cell B9). The "Calculations section of the spreadsheet (see cell A38 to cell B58) 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 rooms (cell B39) is the total of all new single, double and family rooms. Total square feet used for all new rooms and total square feet used for the . new parking lot are in cells B40 and B41 respectively. Each new room will have one parking space. The parking lot's construction cost (cell B43) 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 room (cells B44, B45, B46) is a function of how many rooms there are, how often they are rented, and the rental price per day. Total construction cost for the cach kind of room (cells B47, B48, B49) is a function of how many rooms are built and how much each room costs to build. Daily upkeep cost for a room is only charged if that room is rented that day. Total upkeep cost for next 10 years for each kind of room (cells B50, B5I, B52) is a function of how many rooms are built, how often they are rented and the upkeep cost per day. Restaurant revenue for next 10 years for each kind of room (cells B53, B54, B55) is a function of how many rooms are built, how often rooms are rented, how many people stay in them, and how much money each person is likely to spend in the restaurant per day. Restaurant costs for each kind of room (cells B56, B57, B58) are a percentage of restaurant revenue for the respective room type. . . . The "Income Statement" section of the spreadsheet (see cell A6l to cell B72) 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 rooms (cell B62). Restaurant revenue totaled from all three kinds of rooms (cell B63). Total revenue gained form room rentals and restaurant meals (cell B64). Total room construction costs and total room upkeep costs for all three kinds of rooms are in cells B65 and 366 respectively, Construction cost for the car parking lot (cell B67), Restaurant costs are for all three types of rooms (eell B68), Income tax expense is zero if pre-tax revenue is zero or negative; otherwise, apply the tax rate to the pre-lax revenue (cell B71)). Net income after tax (cell B72). . Constraints part: Formulate constraints from the following statements made by the owner: The owner would like to use all 20,000 square feet available space for all the new rooms and the car parking lot, but he understands this may not be possible, and he expects at least 19,500 square feet would be used. 3 The owner does not want to build a wing that has more than 30 rooms as too many rooms would be difficult to manage, but at least 20 rooms should be in the new wing. Among these new rooms, there should be at least 5 singles, 5 doubles and 10 family rooms. Net income over the 10 years should be positive. Also, a fractional part of a room can't be built. Write formulae and constraints for the above problem. You may use Excel Solver to solve the above problem. I B 50 A 1 Small Hotel Expansion Decision 2 Changing Cells 3 Number of single units 4 Number of double units 5 Number of family units 6 7 Constants 8 Tax rate expected 0.3 9 Total footage available 20000 10 Number of business days 3600 11 Parking: square feet per space 200 12 Parking: construction cost per sq foot 13 Single: Square feet 225 14 Single: Rental per day 83 15 Single: Occupancy rate 0.7 16 Single: Construction cost per sq foot 250 17 Single: Upkeep cost per day 20 18 Single: Avg number of people 1 19 Single: Restaurant revenue per person 10 20 Single: Restaurant cost percentage 0.75 21 Double: Square feet 400 22 Double: Rental per day 115 23 Double: Occupancy rate 0.65 24 Double: Construction cost per sq foot 220 25 Double: Upkeep cost per day 25 26 Double: Avg number of people 2 27 Double: Restaurant revenue per person 25 28 Double: Restaurant cost percentage 0.55 29 Family: Square feet 800 30 Family: Rental per day 175 31 Family: Occupancy rate 0.6 32 Family: Construction cost per sq foot 200 33 Family: Upkeep cost per day 30 34 Family: Avg number of people 35 Family: Restaurant revenue per person 20 36 Family: Restaurant cost percentage 0.65 4 37 38 Calculations 39 Number of units built in new wing 40 Square footage used - rooms 41 Square footage used - parking lot 42 Total square footage used 43 Construction cost - parking lot 44 Total revenue - Single 45 Total revenue - double 46 Total revenue - family 47 Total construction cost - single 48 Total construction cost - double 49 Total construction cost - family 50 Total upkeep cost - Single 51 Total upkeep cost - double 52 Total upkeep cost-family 53 Restaurant revenue - Single 54 Restaurant revenue - double 55 Restaurant revenue - family 56 Restaurant costs - single 57 Restaurant costs - double 58 Restaurant costs - family 59 60 61 Income Statement 62 Room rental revenue 63 Restaurant revenue 64 Total revenue 65 Room construction costs 66 Room upkeep costs 67 Parking lot construction cost 68 Restaurant costs 69 Total costs 70 Pre-lax revenue 71 Income tax expense 72 Net Income 1 mark 3 mark 1 mark 1 mark 1 mark 3 mark 3 mark 3 mark 3 mark 3 mark 3 mark 3 mark 3 mark 3 mark 3 mark 3 mark 3 mark 2 mark 2 mark 2 mark 1 mark 1 mark 1 mark 1 mark 1 mark 0.5 mark 1 mark 1 mark 1 mark 3 mark 1 mark Constraints: 1 2 3 4 5 9 7 8 9 10 11 12 13 14 15 1 Cell Styles 3 x X & fx A B Small Hotel Expansion Decision Changing Cells Number of single units Number of double units Number of family units 0.3 20000 3600 200 50 225 83 0.7 250 20 1 10 Constants Tax rate expected Total footage available Number of business days Parking: square feet per space 2 Parking: construction cost per sq foot 3 Single: Square feet 4 Single: Rental per day 5 Single: Occupancy rate 6 Single: Construction cost per sq foot 7 Single: Upkeep cost per day 8 Single: Avg number of people 19 Single: Restaurant revenue per person 20 Single: Restaurant cost percentage 21 Double: Square feet 22 Double: Rental per day 23 Double: Occupancy rate 24 Double: Construction cost per sq foot 25 Double: Upkeep cost per day 25 Double: Avg number of people 27 Double: Restaurant revenue per person 28 Double: Restaurant cost percentage 29 Famy Square feet 30 Family Rental per day 31 Family, Occupancy rate 132 Family: Construction cost per sq foo! 33 Family: Upkeep cost per day 34 Family Avg number of people 35 Family: Restaurant revenue per person 30 Family Restaurant cost percentage 37 0.75 400 115 0.65 220 25 2 25 0.55 800 175 0.6 200 30 4 20 0.65 Calci C3 x v fx B A 37 38 Calculations 39 Number of units built in new wing 40 Square footage used-rooms 41 Square footage used - parking lot 42 Total square footage used 43 Construction cost - parking lot 44 Total revenue - Single 45 Total revenue - double 46 Total revenue - family 47 Total construction cost - single 48 Total construction cost - double 49 Total construction cost - family 50 Total upkeep cost - Single 51 Total upkeep cost - double 52 Total upkeep cost-family 53 Restaurant revenue - Single 54 Restaurant revenue - double 55 Restaurant revenue - family 56 Restaurant costs - Single 57 Restaurant costs - double 55 Restaurant costs - family 59 50 51 Income Statement 52 Room rental revenue 63 Restaurant revenue 64 Total revenue 65 Room construction costs 06 Room upkeep costs 07 Parking lot construction cost 5 Restaurant costs 69 Total costs 70 Pro tuvenue 71 Income tax expense T2 Not Income 74 Sheet1 SH Shoot Background: Small Hotel Expansion decision problem A small hotel owner would like to expand his business by building a new wing of rooms and a car parking lot using an adjoining 20,000 square-foot piece of land because his business has been doing well. Another reason for the expansion is that the hotel's restaurant is often empty leven when the hotel's occupancy is high, and hopefully additional rooms would increase restaurant patrons. The owner has decided the new wing would have three room types: single, double and families but does not know the number of rooms to be built. The owner wants to make as much net income from the new rooms as possible. Expenditures on the expansion will be heavy in the beginning, but hopefully the investment pays itself over time, the new wing 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 wi calculate the number of rooms to be built in cells B3 to BS for each kind of room. The "Constants" section of the spreadsheet (see cells A7 to cells B36) contain the following data: Tax rate is expected to be 30% per year (cell B8). The owner reasons that revenue from the restaurant depends on the number of people staying in each type of room, occupancy rate, and how much money people are likely to spend on meals. The owner thinks that a single person will favour quick, simple meals and will spend the least amount of money on food. However, a couple will spend more time dining and will be more likely to have cocktails and dessert, and people in single or double rooms are likely to dine only once a day in the restaurant. Families having kids are more likely to dine more than once a day in the restaurant, but kids do not usually eat expensive meals. Based on these estimates. the owner comes up with some expected daily restaurant revenue for each kind of room - see table below Single Double Family Square feet used per room 225 (cell B13) 400 (cell B21) 800 (cell B29) Expected daily rental revenue 83 (cell B14) 115 (cell B22) 175 (cell B30) (S) per room Expected occupancy rate (%) 70 (cell B15) 65 (cell B23) 60 (cell B31) Construction cost (8) per 250 (cell B16) 220 (cell B24) 200 (cell B32) square foot 25 (cell B25) 30 (cell B33) 2 (cell B26) 4(cell B34) Average daily upkeep cost (S) 20 (cell B17) per room Average number of people per 1 (cell B18) room Expected daily restaurant 10 (cell B19) revenue (S) per person Expected restaurant cost (% 75 (cell B20) of meal revenue) 25 (cell B27) 20 (cell B35) 55 (cell B28) 65 (cell B36) Each car park space will take up 200 square feet (cell Bi1). The cost of grading, paving and painting lines of the parking lot is $50 per square foot fcell B12). Number of business days is 360 days per year, thus 3600 business days (cell B10) total for 10 years. Total available space to build the new wing and parking lot is 20,000 square foot (cell B9). The "Calculations section of the spreadsheet (see cell A38 to cell B58) 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 rooms (cell B39) is the total of all new single, double and family rooms. Total square feet used for all new rooms and total square feet used for the . new parking lot are in cells B40 and B41 respectively. Each new room will have one parking space. The parking lot's construction cost (cell B43) 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 room (cells B44, B45, B46) is a function of how many rooms there are, how often they are rented, and the rental price per day. Total construction cost for the cach kind of room (cells B47, B48, B49) is a function of how many rooms are built and how much each room costs to build. Daily upkeep cost for a room is only charged if that room is rented that day. Total upkeep cost for next 10 years for each kind of room (cells B50, B5I, B52) is a function of how many rooms are built, how often they are rented and the upkeep cost per day. Restaurant revenue for next 10 years for each kind of room (cells B53, B54, B55) is a function of how many rooms are built, how often rooms are rented, how many people stay in them, and how much money each person is likely to spend in the restaurant per day. Restaurant costs for each kind of room (cells B56, B57, B58) are a percentage of restaurant revenue for the respective room type. . . . The "Income Statement" section of the spreadsheet (see cell A6l to cell B72) 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 rooms (cell B62). Restaurant revenue totaled from all three kinds of rooms (cell B63). Total revenue gained form room rentals and restaurant meals (cell B64). Total room construction costs and total room upkeep costs for all three kinds of rooms are in cells B65 and 366 respectively, Construction cost for the car parking lot (cell B67), Restaurant costs are for all three types of rooms (eell B68), Income tax expense is zero if pre-tax revenue is zero or negative; otherwise, apply the tax rate to the pre-lax revenue (cell B71)). Net income after tax (cell B72). . Constraints part: Formulate constraints from the following statements made by the owner: The owner would like to use all 20,000 square feet available space for all the new rooms and the car parking lot, but he understands this may not be possible, and he expects at least 19,500 square feet would be used. 3 The owner does not want to build a wing that has more than 30 rooms as too many rooms would be difficult to manage, but at least 20 rooms should be in the new wing. Among these new rooms, there should be at least 5 singles, 5 doubles and 10 family rooms. Net income over the 10 years should be positive. Also, a fractional part of a room can't be built. Write formulae and constraints for the above problem. You may use Excel Solver to solve the above problem. I B 50 A 1 Small Hotel Expansion Decision 2 Changing Cells 3 Number of single units 4 Number of double units 5 Number of family units 6 7 Constants 8 Tax rate expected 0.3 9 Total footage available 20000 10 Number of business days 3600 11 Parking: square feet per space 200 12 Parking: construction cost per sq foot 13 Single: Square feet 225 14 Single: Rental per day 83 15 Single: Occupancy rate 0.7 16 Single: Construction cost per sq foot 250 17 Single: Upkeep cost per day 20 18 Single: Avg number of people 1 19 Single: Restaurant revenue per person 10 20 Single: Restaurant cost percentage 0.75 21 Double: Square feet 400 22 Double: Rental per day 115 23 Double: Occupancy rate 0.65 24 Double: Construction cost per sq foot 220 25 Double: Upkeep cost per day 25 26 Double: Avg number of people 2 27 Double: Restaurant revenue per person 25 28 Double: Restaurant cost percentage 0.55 29 Family: Square feet 800 30 Family: Rental per day 175 31 Family: Occupancy rate 0.6 32 Family: Construction cost per sq foot 200 33 Family: Upkeep cost per day 30 34 Family: Avg number of people 35 Family: Restaurant revenue per person 20 36 Family: Restaurant cost percentage 0.65 4 37 38 Calculations 39 Number of units built in new wing 40 Square footage used - rooms 41 Square footage used - parking lot 42 Total square footage used 43 Construction cost - parking lot 44 Total revenue - Single 45 Total revenue - double 46 Total revenue - family 47 Total construction cost - single 48 Total construction cost - double 49 Total construction cost - family 50 Total upkeep cost - Single 51 Total upkeep cost - double 52 Total upkeep cost-family 53 Restaurant revenue - Single 54 Restaurant revenue - double 55 Restaurant revenue - family 56 Restaurant costs - single 57 Restaurant costs - double 58 Restaurant costs - family 59 60 61 Income Statement 62 Room rental revenue 63 Restaurant revenue 64 Total revenue 65 Room construction costs 66 Room upkeep costs 67 Parking lot construction cost 68 Restaurant costs 69 Total costs 70 Pre-lax revenue 71 Income tax expense 72 Net Income 1 mark 3 mark 1 mark 1 mark 1 mark 3 mark 3 mark 3 mark 3 mark 3 mark 3 mark 3 mark 3 mark 3 mark 3 mark 3 mark 3 mark 2 mark 2 mark 2 mark 1 mark 1 mark 1 mark 1 mark 1 mark 0.5 mark 1 mark 1 mark 1 mark 3 mark 1 mark Constraints: 1 2 3 4 5 9 7 8 9 10 11 12 13 14 15 1 Cell Styles 3 x X & fx A B Small Hotel Expansion Decision Changing Cells Number of single units Number of double units Number of family units 0.3 20000 3600 200 50 225 83 0.7 250 20 1 10 Constants Tax rate expected Total footage available Number of business days Parking: square feet per space 2 Parking: construction cost per sq foot 3 Single: Square feet 4 Single: Rental per day 5 Single: Occupancy rate 6 Single: Construction cost per sq foot 7 Single: Upkeep cost per day 8 Single: Avg number of people 19 Single: Restaurant revenue per person 20 Single: Restaurant cost percentage 21 Double: Square feet 22 Double: Rental per day 23 Double: Occupancy rate 24 Double: Construction cost per sq foot 25 Double: Upkeep cost per day 25 Double: Avg number of people 27 Double: Restaurant revenue per person 28 Double: Restaurant cost percentage 29 Famy Square feet 30 Family Rental per day 31 Family, Occupancy rate 132 Family: Construction cost per sq foo! 33 Family: Upkeep cost per day 34 Family Avg number of people 35 Family: Restaurant revenue per person 30 Family Restaurant cost percentage 37 0.75 400 115 0.65 220 25 2 25 0.55 800 175 0.6 200 30 4 20 0.65 Calci C3 x v fx B A 37 38 Calculations 39 Number of units built in new wing 40 Square footage used-rooms 41 Square footage used - parking lot 42 Total square footage used 43 Construction cost - parking lot 44 Total revenue - Single 45 Total revenue - double 46 Total revenue - family 47 Total construction cost - single 48 Total construction cost - double 49 Total construction cost - family 50 Total upkeep cost - Single 51 Total upkeep cost - double 52 Total upkeep cost-family 53 Restaurant revenue - Single 54 Restaurant revenue - double 55 Restaurant revenue - family 56 Restaurant costs - Single 57 Restaurant costs - double 55 Restaurant costs - family 59 50 51 Income Statement 52 Room rental revenue 63 Restaurant revenue 64 Total revenue 65 Room construction costs 06 Room upkeep costs 07 Parking lot construction cost 5 Restaurant costs 69 Total costs 70 Pro tuvenue 71 Income tax expense T2 Not Income 74 Sheet1 SH Shoot