Westley Fong, manager of The Lucky 88 Motel, has a contract with Appraisers Associates to appraise his 150-room motel, which is located in beautiful downtown Wahiawa. the consultant on the job determines the following:

1. Occupancy percentages for the most recent year were as follows:

Daily occupancy % = 80% Double occupancy % = 40% Average occupancy per room = 1.5 (use this only as a double check, not in a formula)

2. Average room rates were as follows:

Single rooms = $115.00 Double rooms = $145.00

3. Room expenses were as follows:

Labor--fixed = $85,000 Labor--variable = 17% of room sales Other variable expenses = 5% of room sales

4. Food sales for the most recent year were as follows:

a. Breakfast:

• 60% of guests staying in singles dined in the motel's restaurant
• 40% of guests staying in doubles dined in the motel's restaurant
• The average check was $15.95
• And average of 10 breakfast guests per day were non-room guests

b. Lunch:

• 20% of the hotel guests used the facilities
• 60% of lunch guests were non-room guests
• The average check was $21.79

c. Dinner

• 30% of guests staying in singles dined in the restaurant
• 10% of guests staying in doubles dined in the restaurant
• 20% of dinner guests were non-room guests
• The average check was $33.49

5. The food service expenses for a recent year were as follows:

a. Cost of sales: 35% b. Labor: fixed = $60,000, variable = 20% of food sales c. Other: fixed = $12,000, variable = 15% of food sales

6. Telephone sales and expenses for the most recent year were as follows:

a. Telephone sales were 5% of total revenue (NOT room + f&b) of the motel b. Telephone expenses were 95% of telephone sales

7. Other expenses of the motel were as follows:

	Fixed Expenses	Variable (% of Total Revenues)
Administrative & general	$100,000	4%
Marketing	50,000	3%
Property operation & maintenance	40,000	5%
Utilities	12,000	1.5%
Insurance	15,000	--
Property taxes	30,000	--
Rent	5,000	--

Required:

1. Prepare an operations statement for the most recent year (20x0) based on the above information.

2. Leave columns available for forecasting years 20x1-20x5.

How would this be done in a spreadsheet?

Please Show me the formula for each of the cells thank you.