Write the Excel formula for each cell marked with cell 39 to 72"

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 hotels restaurant is often empty even when the hotels 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 Excels Solver, and you are required to write Excel formula and constraints to help with the calculations of this problem see spreadsheet below. Microsoft Excels Solver will calculate the number of rooms to be built in cells B3 to B5 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 ($) per room	83 (cell B14)	115 (cell B22)	175 (cell B30)
Expected occupancy rate (%)	70 (cell B15)	65 (cell B23)	60 (cell B31)
Construction cost ($) per square foot	250 (cell B16)	220 (cell B24)	200 (cell B32)
Average daily upkeep cost ($) per room	20 (cell B17)	25 (cell B25)	30 (cell B33)
Average number of people per room	1 (cell B18)	2 (cell B26)	4 (cell B34)
Expected daily restaurant revenue ($) per person	10 (cell B19)	25 (cell B27)	20 (cell B35)
Expected restaurant cost (% of meal revenue)	75 (cell B20)	55 (cell B28)	65 (cell B36)

Each car park space will take up 200 square feet (cell B11). The cost of grading, paving and painting lines of the parking lot is $50 per square foot (cell 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).

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 cant 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 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-tax 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 The Calculations section of the spreadsheet (see cell A38 to cell 058) 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 each 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, B51, 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 A61 to cell 072) 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 B66 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)