Question

A property management firm is planning to acquire a residential building with multiple rooms and use it as a motel. The building monthly costs were estimated as follow: Building rent with utilities $17,000 Building insurance $500 Food supplies per room $150 Room service per room $250 Receptionist salary $2500 If the room monthly rent is $2400, perform breakeven analysis as follow:

a. Develop cost, revenue and profit models for this problem. b. Using analytical method, find how many rooms should be rented every month to breakeven? c. Find the breakeven point graphically, attach your graph. d. If the number of monthly rented rooms found to be 8 rooms in average, how much should be the room monthly rent to breakeven?

Answer 1

Formula sheet

A	B	C	D	E	F	G	H	I
2
3		a)
4
5		Variable Costs Per Month:
6		Room Service Per Room	250
7		Food Supplies Per Room	150
8		Total Variable Cost	=SUM(D6:D7)
9
10		Fixed cost per month:
11		Building Rent with Utilities	17000
12		Building Insurance	500
13		Receptionist Salary	2500
14		Fixed cost per month	=SUM(D11:D13)
15
16		Revenue per Room per month	2400
17
18		Number of rooms on rent per month	X
19
20		Revenue from rooms	2400X
21		Variable Cost	400X
22		Fixed Cost	17750
23		Profit	=2400X-400X - 17750
24			=2000X-17750
25
26		Hence Profit equation is	=2000X-17750
27
28		b)
29
30		Contribution margin	=Revenue per room - Variable cost per Room
31			=2400-400
32			=2400-400	=2400-400
33
34		Fixed cost	=D14
35
36		Breakeven Point	=Fixed cost / Contribution margin
37			=D34/D32	=D34/D32
38
39		Hence Breakeven Point is	=D37	Rooms
40
41		c)
42
43		Number of Rooms on Rent	Revenue	Variable Cost	Fixed Cost	Profit
44		1	=C44*$D$16	=C44*$D$8	=$D$34	=D44-E44-F44
45		2	=C45*$D$16	=C45*$D$8	=$D$34	=D45-E45-F45
46		3	=C46*$D$16	=C46*$D$8	=$D$34	=D46-E46-F46
47		4	=C47*$D$16	=C47*$D$8	=$D$34	=D47-E47-F47
48		5	=C48*$D$16	=C48*$D$8	=$D$34	=D48-E48-F48
49		6	=C49*$D$16	=C49*$D$8	=$D$34	=D49-E49-F49
50		7	=C50*$D$16	=C50*$D$8	=$D$34	=D50-E50-F50
51		8	=C51*$D$16	=C51*$D$8	=$D$34	=D51-E51-F51
52		9	=C52*$D$16	=C52*$D$8	=$D$34	=D52-E52-F52
53		10	=C53*$D$16	=C53*$D$8	=$D$34	=D53-E53-F53
54		11	=C54*$D$16	=C54*$D$8	=$D$34	=D54-E54-F54
55		12	=C55*$D$16	=C55*$D$8	=$D$34	=D55-E55-F55
56		13	=C56*$D$16	=C56*$D$8	=$D$34	=D56-E56-F56
57		14	=C57*$D$16	=C57*$D$8	=$D$34	=D57-E57-F57
58		15	=C58*$D$16	=C58*$D$8	=$D$34	=D58-E58-F58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75		Since profit becomes positive when number of rooms rented is about 10,
76		therefore breakeven point is 10 rooms.
77
78		d)
79
80		Number of room rented	8
81
82		Variable cost	=D80*D8
83		Fixed Cost	=D14
84		Total cost	=SUM(D82:D83)
85
86		Breakeven Room Rent	=Total cost / Number of rooms rented
87			=D84/D80	=D84/D80
88
89		Hence Breakeven Room Rent	=D87
90