Question

You operate a luxury hotel in Baltimore that famous celebrities rent for extended periods. The daily price is per room is $1,950. Operating costs average $60,000 per day, regardless of the number of rooms rented. Construct a spreadsheet model to determine the profit if 60 rooms are rented. The manager has observed that the number of rooms rented during any given day varies between 50 and 80 (the total number of rooms available).

a.Use data tables to evaluate the profit for this range of unit rentals.

b.Suppose the manager is considering lowering or increasing the daily price by $100. How will profit be affected? (Hint: use a two-way data table).

Answer 1

Spreedsheet model to determine the profit if 60 rooms are rented

Operating cost of the rooms per day	60000
Rent price per room	1950
Number of rooms rented	60
Profit if 60 rooms are rented	57000

To Calculate Profit , Profit = (Rent*No of rooms rented)-Operating Costs

Enter the formula in the required cell

a)Given that number of rooms rented varies from 50 to 80, we can calculate the profit by one way table

Copy the formula for finding profit in C15 and enter the number of rooms in the cells B16 to B46, Select the range of the table

Go To Data and What If-Analysis and Choose Data Tables

In the opened dialog box, in column cell, select C7 and click Ok, The data table will be as below

	Profit amount if the room rent is 1950
Number of rooms rented	Profit
60	57000
50	37500
51	39450
52	41400
53	43350
54	45300
55	47250
56	49200
57	51150
58	53100
59	55050
60	57000
61	58950
62	60900
63	62850
64	64800
65	66750
66	68700
67	70650
68	72600
69	74550
70	76500
71	78450
72	80400
73	82350
74	84300
75	86250
76	88200
77	90150
78	92100
79	94050
80	96000

b)Profit calculation for the varying rent price and number of rooms rented can be done by using two way table .It can be created similar to One way table, except for giving C6 in row cell of the Data Table dialog box.

Number of rooms rented	Profit for the varying room rent
57000	1850	1950	2050
50	32500	37500	42500
51	34350	39450	44550
52	36200	41400	46600
53	38050	43350	48650
54	39900	45300	50700
55	41750	47250	52750
56	43600	49200	54800
57	45450	51150	56850
58	47300	53100	58900
59	49150	55050	60950
60	51000	57000	63000
61	52850	58950	65050
62	54700	60900	67100
63	56550	62850	69150
64	58400	64800	71200
65	60250	66750	73250
66	62100	68700	75300
67	63950	70650	77350
68	65800	72600	79400
69	67650	74550	81450
70	69500	76500	83500
71	71350	78450	85550
72	73200	80400	87600
73	75050	82350	89650
74	76900	84300	91700
75	78750	86250	93750
76	80600	88200	95800
77	82450	90150	97850
78	84300	92100	99900
79	86150	94050	101950
80	88000	96000	104000

The Profit increases with the increasing room rent

Formula Sheet

Operating cost of the rooms per day	60000
Rent price per room	1950
Number of rooms rented	60
Profit if 60 rooms are rented	=C6*C7-C4

	Profit amount if the room rent is 1950
Number of rooms rented	Profit
60	=C9
50	=TABLE(,C7)
51	=TABLE(,C7)
52	=TABLE(,C7)
53	=TABLE(,C7)
54	=TABLE(,C7)
55	=TABLE(,C7)
56	=TABLE(,C7)
57	=TABLE(,C7)
58	=TABLE(,C7)
59	=TABLE(,C7)
60	=TABLE(,C7)
61	=TABLE(,C7)
62	=TABLE(,C7)
63	=TABLE(,C7)
64	=TABLE(,C7)
65	=TABLE(,C7)
66	=TABLE(,C7)
67	=TABLE(,C7)
68	=TABLE(,C7)
69	=TABLE(,C7)
70	=TABLE(,C7)
71	=TABLE(,C7)
72	=TABLE(,C7)
73	=TABLE(,C7)
74	=TABLE(,C7)
75	=TABLE(,C7)
76	=TABLE(,C7)
77	=TABLE(,C7)
78	=TABLE(,C7)
79	=TABLE(,C7)
80	=TABLE(,C7)

Number of rooms rented	Profit for the varying room rent
=C9	1850	1950	2050
50	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
51	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
52	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
53	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
54	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
55	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
56	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
57	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
58	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
59	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
60	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
61	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
62	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
63	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
64	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
65	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
66	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
67	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
68	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
69	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
70	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
71	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
72	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
73	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
74	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
75	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
76	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
77	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
78	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
79	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)
80	=TABLE(C6,C7)	=TABLE(C6,C7)	=TABLE(C6,C7)