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.
High-low methods, accounting analysis, CVP, and leverage
EXERCISE4-5. High-Low Method
Campus Copy & Printing wants to predict copy machine repair expense at different levels of copying activity (number of copies made). The following data have been gathered: (see attached)
Determine the fixed and variable components of repair expense using the high-low
method. Use copies made as the measure of activity.
EXERCISE 4-8. Account Analysis
Reef Office Supplies is interested in estimating
the cost involved in hiring new employees. The following information is available regarding
the costs of operating the Human Resource department at Reef Office Supplies
in May when there were 50 new hires.
Human Resource Department
Staff salaries $25,000
Manager salary 7,000
Office supplies 200
Depreciation of office equipment 300
Share of building cost (based
on square feet occupied by
Human Resources) 1,500
a. Use account analysis to determine fixed cost per month and variable cost per new
b. The company is planning to hire 60 employees in June. Estimate the total cost of
Human Resources for June..
c. What is the expected incremental cost associated with hiring 10 more employees
than were hired in May?
EXERCISE 4-12. CVP Analysis, Profit Equation
Clyde's Marina has estimated that fixed costs per month
are $240,000 and variable cost per dollar of sales is $0.60.
a. What is the break-even point per month in sales?
b. What level of sales is needed for a monthly profit of $60,000?
c. For the month of July, the marina anticipates sales of $1,200,000: What is the expected level of profit?
EXERCISE 4-17. Operating Leverage
(see attached for data)
a. Calculate profit as a percent of sales in the prior year.
b. Suppose sales in the current year increase by 20 percent. Calculate profit as a percent
of sales for the new level of sales and explain why the percent is greater than the
one calculated in Part a.
EXERCISE 4-18. Constraints
Dvorak Music produces two durable music stands:
Stand A Stand B
Selling price $80 $70
Less variable costs 20 40
Contribution margin $60 $30
Stand Arequires 5 labor hours and standB requires 2 labor hours. The company has
only 320 available labor hours per week. Further, the company can sell all it can produce
of either product.
a. Which stand(s) should the company produce?
b. What would be the incremental benefit of obtaining 10 additional labor hours?
PROBLEM 4-12. Multiproduct CVP
Fidelity Multimedia sells audio and video equipment
and car stereo products. After performing a study of fixed and variable costs in the
prior year, the company prepared a product-line profit statement as follows:
For the Year Ended December 31,2007
Audio Video Car Total
Sales $3,000,000 $1,800,000 $1,200,000 $6,000,000
Less variable costs:
Cost of merchandise 1,800,000 1,260,000 600,000 3,660,000
Salary part-time staff 120,000 80,000 30,000 230,000
Total variable costs 1,920,000 1,340,000 630,000 3,890,000
Contribution margin 1,080,000 460,000 570,000 2,110,000
Less direct fixed costs:
Salary, full-time staff 300,000 250,000 210,000 760,000
Less common fixed costs:
Other administrative costs 560,000
Total common fixed costs 690,000
a. Calculate the contribution margin ratios for the audio, video, and car product lines.
b. What would be the effect on profit of a $100,000 increase in sales of audio equipment
compared with a $100,000 increase in sales of video equipment, or a $100,000
increase in sales of car equipment? Based on this limited information, which product
line would you recommend expanding?
c. Calculate the break-even level of sales for the company as a whole.
d. Calculate sales needed to achieve a profit of $1,500,000 assuming the current mix.
e. Determine the sales of audio, video, and car products in the total sales amount calculated
for Part d.