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 thatthe hotels restaurant is often empty even when the hotels occupancy is high, andhopefully 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-yearforecasted income statement is done in a Microsoft Excel spreadsheet using ExcelsSolver, and you are required to write Excel formula and constraints to help with the calculations of this problem see spreadsheet below. Microsoft Excels Solver willcalculate 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).

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 lots construction cost (cell B43) is the function of the lotssquare 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 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 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).

  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 aroom cant be built.

• Write formulae and constraints for the above problem. You may use Excel Solver to solve the above problem.

	A	B
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	Net Income	1 mark