B 0.3 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 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 50 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 4 35 Family: Restaurant revenue per person 20 36 Family: Restaurant cost percentage 0.65 37 38 Calculations 39 Number of units built in new wing 1 mark 40 Square footage used-rooms 3 mark 41 Square footage used - parking lot 1 mark 42 Total square footage used 1 mark 43 Construction cost parking lot 1 mark 44 Total revenue - Single 3 mark 45 Total revenue - doublo 3 mark 46 Total revenue - family 3 mark 47 Total construction cost - Single 3 mark 48 Total construction cost - double 3 mark 49 Total construction cost-family 3 mark 50 Total upkeep cost - single 3 mark 51 Total upkeep cost - double 3 mark 52 Total upkeep cost-family 3 mark 53 Restaurant revenue - single 3 mark 54 Restaurant revenue - double 3 mark 55 Restaurant revenue - family 3 mark 56 Restaurant costs - Single 2 mark 57 Restaurant costs - double 2 mark 58 Restaurant costs - family 2 mark 59 60 61 Income Statement 62 Room rental revenue 1 mark 63 Restaurant revenue 1 mark 64 Total revenue 1 mark 65 Room construction costs 1 mark 66 Room upkeep costs 1 mark 67 Parking lot construction cost 0.5 mark 68 Restaurant costs 1 mark 69 Total costs 1 mark 70 Pre-tax revenue 1 mark 71 Income tax expense 3 mark 72 Net Income 1 mark 1 . The "Income Statement section of the spreadsheet (see cell A61 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 (cell B68). Income tax expense is zero if pre-tax revenue is zero or negative; otherwise, apply the tax rate to the pre-tax 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. 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. B 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 50 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 4 35 Family: Restaurant revenue per person 20 36 Family: Restaurant cost percentage 0.65 37 38 Calculations 39 Number of units built in new wing 1 mark 40 Square footage used-rooms 3 mark 41 Square footage used parking lot 1 mark 42 Total square footage used 1 mark 43 Construction cost parking lot 1 mark 44 Total revenue - Single 3 mark 45 Total revenue - double 3 mark 46 Total revenue - family 3 mark 47 Total construction cost - single 3 mark 48 Total construction cost - double 3 mark 49 Total construction cost-family 3 mark 50 Total upkeep cost - single 3 mark 51 Total upkeep cost - double 3 mark 52 Total upkeep cost-family 3 mark 53 Restaurant revenue - Single 3 mark 54 Restaurant revenue - double 3 mark 55 Restaurant revenue - family 3 mark 56 Restaurant costs - Single 2 mark 57 Restaurant costs - double 2 mark 58 Restaurant costs - family - 2 mark 59 60 61 Income Statement 62 Room rental revenue 1 mark 63 Restaurant revenue 1 mark 64 Total revenue 1 mark 65 Room construction costs 1 mark 66 Room upkeep costs 1 mark 67 Parking lot construction cost 0.5 mark 68 Restaurant costs 1 mark 69 Total costs 1 mark 70 Pre-tax revenue 1 mark 71 Income tax expense 3 mark 72 INet Income 1 mark 1 Sheet1 Sheet2 Sheet3 Small Hotel Expansion Decision Changing Cells Number of single units Number of double units Number of family units Constants Tax rate expected Total footage available Number of business days Parking: square feet per space Parking construction cost per sq foot Single: Square feet Single Rental per day Single: Occupancy rate Single Construction cost per sq foot Single Upkeep cost per day Single: Avg number of people Single: Restaurant revenue per person Single Restaurant cost percentage Double: Square feet Double: Rantal per day Double: Occupancy rate Double: Construction cost per sq foot Double: Upkeep cost per day Double: Avg number of people Double: Restaurant revenue per person Double: Restaurant cost percentage Family: Square feet Family: Rental per day Family: Occupancy rate Family: Construction cost per sq foot Family: Upkeep cost per day Family: Avg number of people Family: Restaurant revenue per person Family: Restaurant cost percentage 0.3 20000 3600 200 50 225 83 0.7 250 20 1 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 wing Square footage used-rooms Square footage used - parking lot Total square footage used Construction cost-parking lot Total revenue - Single Total revenue double Total revenue - family Total construction cost - Single Total construction cost double Total construction cost family Total upkeep cost-single Total upkeep cost double Total upkeep cost-family Restaurant revenue - Single Restaurant revenue-double Restaurant revenue-family Restaurant costs - Single Restaurant costs double Restaurant costs - family Income Statement Room rental revenue Restaurant revenue Total revenue Room construction costs Room upkeep costs Parking lot construction cost Restaurant costs Total costs Pre-tax revenue Income tax expense Net Income