Background: Small Hotel Expansion decision problem
A 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 even 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 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 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).
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 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 the 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 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.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
