See attached file.
Managerial Accounting, Third Edition by James Jiambalvo
"Solving Managerial Accounting Problems Using Microsoft Excel for Windows Templates by Rex A Schildhouse"
PROBLEM P4-6 - Account Analysis, High-Low, Contribution Margin
Information on occupancy and costs at the New Light Hotel for April, May, and June are indicated below:
April May June
Occupancy 1,500 1,650 1,800
Day manager salary $4,200 $4,200 $4,200
Night manager salary 3,700 3,700 3,700
Cleaning staff 15,300 15,600 15,900
Depreciation 12,000 12,000 12,000
Complimentary continental 4,600 5,300 5,800
breakfast:food and beverages
$39,800 $40,800 $41,600
a. Calculate the fixed costs per month and the variable cost per occupied room using account analysis for April.
Fixed costs per month (April~June data):
Day Manager Salary Amount
Night Manager Salary Amount
Variable costs per month (April data):
Cleaning Staff Amount
Complimentary continental breakfast: Amount
food and beverages Formula
Divided by number of rooms Number
Variable costs per room Formula
b. Calculate the fixed costs per month and the variable cost per occupied room using the high-low method.
Per occupied room of variable cost:
(Amount - Amount )÷ Amount = Formula
Fixed costs per month:
Amount - (Amount * Number)= Formula
c. Average room rates are $110 per night. What is the contribution margin per occupied room? In answering this question, use your variable cost estimate from Part b.
Contribution margin per occupied room:
Amount - Amount = Formula
The solution shows fixed cost, variable cost using high low method, accounting analysis.