USA: +1(669)289-8683 
Sign in
unit06_assignment_excel_template_draft_copy.xlsx18-22 20-38 20-38_1-2_cont-1
Unit 06 Assignment
BRIEF Exercise 18-22 – found on page 825 of your textbook
Problem 20-38 – found on page 920-921 in the textbook
Submit your assignment using a single Excel workbook. Place each exercise/problem on a separate tab (sheet).
You must properly format Excel for the Journal Entries. And, for any discussion component (as in Part 2 of Problem 20-38), “Insert” a Textbox and use that to present your discussion on the Excel spreadsheet.
>GB 19 Unit 6 Rubric
6 Assignment/Homework Rubric:
:
5 Contribution Amount Correct
5 -CPC Amount Correct
5 5 5 5 5 (a) Is plan effective – why or why not?
3 2 Content Subtotal
0 40 0 $0 The spreadsheet will compute this for you. $0 The spreadsheet will compute this for you. $0 The spreadsheet will compute this for you. QUESTION: rooms. His job objectives include providing resourceful and friendly
40 100 because of the addition of the bonus. The goal measures used to compensate Ramon are as follows:
Total $60,000 $0.00 $0.00 ^Try again^ $60,000 $0.00 $0.00 $0.00 $60,000.00 (Rate * Units) (Rate * Units) 2. Comment on the expected effectiveness of this plan.
5
Thank you for the effort in the Unit 6 Assignment.
Remember, the Business Tutors are available on
Please review all tabs for comments/feedback.
Sunday through Tuesday nights.
Best regards
Unit
0
Category/Description
Points Possible
Points Earned
Instructor Additional Comments (If necessary)
BRIEF Problem 18-22
Please review all tabs for guidance. Also, if you have any questions, please bring them to the Virtual Office.
-Key Elements include:
Journal Entries:
1-Controllable Amount Correct
2-
Total
3
4-Operating Amount Correct
Problem 20-38:
Key Elements Include:
Part 1 –
-Part A – Compensation Correct
-Part B – Compensation Correct
-Part C – Compensation Correct
Part 2 –
(b) Thorough analysis?
40
Analysis/Format/Grammar/Spelling
*
*Points may be deducted if format or presentation does not meet instruction requirements.
Final Score
Brief Pr. 18-22 Narrative & Sol
BRIEF Problem 18-22
QUESTION:
18-22 Phelps Glass Inc. has reported the following financial data: net revenues of $1 0 million, variable costs of $5 million, controllable fixed costs of $2 million, noncontrollable fixed costs of $1 million, and untraceable costs of $500,000. The accounting manager has supplied you with this data and asked you to come up with the controllable margin, total contribution, CPC, and operating income.
For guidance, review Exhibit 18. on page 815 in the text.
Hint: Enter values in this color cell.
SOLUTION:
Hint: Excel will compute this for you.
Net Revenue
Variable Costs
Contribution Margin
$0
The spreadsheet will compute this for you.
Controllable Fixed Costs
Controllable Margin
Noncontrollable Fixed Cost
Total CPC
Untraceable Costs
Operating Income
^Try again^
^^^^^^^^
this field will let you know if the final total
for “Operating Income” is correct.
It will change to “Correct” when it is right.
Problem 20-38 Narrative
20-38 Incentive Pay in the Hotel Industry
Incentive Pay in The Hotel Industry
Ramon Martinez is the general manager of Classic Inn, a local mid-priced hotel with
100
service to the hotel’s guests, maintaining an 80 percent occupancy rate, improving the average rate received per room to $88 from the current $85, and achieving a savings of 5 percent on all hotel costs. The hotel’s owner, a partnership of seven people who own several hotels in the region, want to structure Ramon’s future compensation to objectively reward him for achieving these goals. In the past, he has been paid an annual salary of $72,000 with no incentive pay. The incentive plan the partners developed has each of the goals weighted as follows:
Measure
% of Total
Occupancy rate (also reflects guest service quality)
Operating within 95 percent of expense budget
25
Average room rate
35
Check total
If Ramon achieves all of these goals, the partners determined that his performance should merit a bonus of $23,000. The partners also agreed that his salary would be reduced to
$60,000
Occupancy goal: 29,200 room-nights = 80 percent occupancy rate x100 rooms x 365 days
Compensation: 40 percent weight x $23,000 target reward = $9,200
$9,200/29,200 = $0.31 5 per room-night
Expense goal: 5 percent savings
Compensation: 25 percent weight x $23,000 target reward = $5,750
$5,750/5 = $1 ,150 for each percentage point saved
Room rate goal: $3 rate increase
Compensation: 35 percent weight x $23,000 target reward = $8,050
$8,050/300 = $26.83 per each cent increase
Ramon’s new compensation plan will thus pay him a $60,000 salary plus 31 .5 cents per room-night sold plus $1,1 50 for each percentage point saved in the expense budget plus $26.83 per each cent increase in average room rate.
Required
1 . Based on this plan, what will Ramon’s total compensation be if his performance results are
a. 30,000 room-nights, 5 percent saved, $3.00 rate increase?
b. 25,000 room-nights, 3 percent saved, $1 .1 5 rate increase?
c. 28,000 room-nights, 0 saved, $1 .00 rate increase?
2. Comment on the expected effectiveness of this plan.
Problem 20-38 Solution
20-38 Incentive Pay in the Hotel Industry
Review textbook pages 898 through 902 for guidance. Also, see comments at right for additional guidance —->
Additional Guidance:
The problem is based on the material in the textbook found in Chapter 20, pages 894 to 902.
There is no direct example to fit this exact problem narrative. Rather, you are to synthesize the material and compile the information using the material presented.
Part 1 Solution:
To help get you started, let’s look at Part a. We are given a total base salary of $60,000. So, we know that is going to be included in the “total compensation”.
Category–>
Base Pay
Occupancy Goal
Expense Goal
Room Rate Goal
Total Pay
Only 1 of 3 have
Then, we are given 3 independent bonus variables to calculate possible bonus:
(Given)
$ Rate
Units
feedback:
a. 30,000 room-nights, 5 percent saved, $3.00 rate increase?
$0.00
$60,000.00
b. 25,000 room-nights, 3 percent saved, $1.1 5 rate increase?
1- 31 .5 cents per room-night sold
c. 28,000 room-nights, 0 saved, $1 .00 rate increase? $60,000 $0.00 $0.00 $0.00 $60,000.00
2- plus $1,1 50 for each percentage point saved in the expense budget
(Rate * Units)
Sum of all $ in row
3- plus $26.83 per each cent increase in average room rate.
Note: Enter data in these colored fields.
Note: Excel will compute.
You will compute the Bonus using those 3 elements for each part (a, b & c).
For Part a, you are told that:
a. 30,000 room-nights, 5 percent saved, $3.00 rate increase.
[Enter comments here]
So, you will add the $60,000 to the amount of bonus earned for all 3 categories of bonus, based on those results for a, b & c. Therefore, for Part a, the “room-night bonus”, the “percentage point” bonus & the “each cent increase” bonuses will all be added to the $60,000.
Specifically, for Part a, the solution would be =:
$60,000 + (31.5 cents bonus for every room-night sold). + ($1,150 for each % point saved) + ($26.83 for every CENT increase).
Repeat that process for parts b & c.
