Crystal Coast Ocean Resort The Crystal Coast Ocean Resort (CCOR) is a large, family-owned resort located on one of the finest beaches in North Carolina. The Resort is locally called the "Ramada Inn: Crystal Coast Ocean Resort, because it has a contractual relationship with Ramada Inn, Inc which requires Ramada to provide reservation services and certain marketing services. In turn CCOR pays an annual fee to Ramada and displays the Ramada Inn sign. Ramada has the right, under contract, to require CCOR to meet Ramada standards for resort services, cleanliness, etc, and regularly visits CCOR to make sure that these standards are being met. CCOR's main building has a T-configuration, where the top of the T is road-side and the bottom of the T is at the beach. This allows all rooms to have at least a partial view of the beach. There are five types of rooms for guests, ocean front (actually facing the ocean, at the bottom of the T), ocean view (along the sides of the T), pool side (first floor, with patios having direct access to the pool), and one and two bedroom suites that are located at the top of the T. These rooms are distributed as follows. Ocean front Ocean view Pool Side One Bedroom Suite Two Bedroom Suite No. of Rooms 50 250 18 16 16 350 The season for CCOR is year-round because of the mild winters, though the occupancy rates vary from 100% during the summer months to less than 50% in the winter months. The average annual occupancy rate for each type of rooms is 80%, 75%, 60%, 50% and 50% for the ocean front, ocean view, pool side, one room and two room suites, respectively. Because the Resort is popular with families, the average number of persons per room is greater than for some other motels or hotels. The average number of occupants is three for the ocean front, ocean view, and pool side rooms, four for the one bedroom suite, and 6 for the two bedroom suite. The suite are also somewhat larger, 500 square feet for the one bedroom suite, and 900 square feet for the two bedroom suite, while all other room are 300 square feet. CCOR does not have some of the amenities of competing resorts, but instead competes on value pricing and reliable service. It does not offer special services such as room service, exercise room, restaurant, or lounge but it sets high standards for room cleanliness and for the appearance and cleanliness of the pool and grounds. Also, CCOR offer a free breakfast and a free afternoon snack. Because of high demand in summer months, CCOR sets relatively high market prices during this season, ranging from $125 to $350 per room depending on room type and day of week. Prices are lower in the fall and spring, and substantially lower in the winter. In order to have better information about price setting and profitability analysis for each of the five room types: ocean front, ocean view, pool side, one bedroom and two bedroom suites, CCOR has gathered additional information to help it determine the cost for each occupied room. Les Broom, the accounting manager, assembled the following data. The data includes information on the four annual resource costs taken directly from CCOR's accounting reports, as well as activity and cost driver information developed by Les. Annual Resource Costs at CCOR Facilities Cost - Utilities Other Facilities Costs Depreciation Tax Interest Total General and Administrative S 210,000 800,000 500,000 600,000 $ 1,900,000 Accounting 165,000 General Administration 155,000 Purchasing and HR 88,000 Total S 408,000 Hotel Operations Wages 1,500,000 Supplies 180,000 Equipment and repair 240,000 Total $ 1,920,000 The following is a list of the nine activities at CCOR identified by Les, as well as information he developed to assign resource costs to these activities. Les performed a careful analysis and determined the approximate percentage of each type of resource costs that could be allocated to each of the nine activities. Resources Activities Utilities Other Facilities Gen and Adm Operations Housekeeping 5% 5% 20% Laundry 10% 10% 10% Grounds and Pool 40% 20% 10% Registration 15% 20% Breakfast room 10% 15% 10% Administration 5% 20% 0% Security 5% 10% 10% Room repair and maintenance 10% 20% 20% Utilities 100% 0% 0% 0% Total 100% 100% 100% 100% The following activity-consumption cost drivers are used to assign activity costs to cost objects: Activities Housekeeping Laundry Grounds and Pool Registration Breakfast room Cost Driver square feet number of occupants number of rooms number of occupied rooms number of occupants Administration Security number of occupied rooms number of rooms Room repair and maintenance square feet Utilities square feet The cost drivers are interpreted as follows. Housekeeping costs are allocated to the room type based on the number of square feet in the room. Laundry is allocated to the room type based on the number of occupants in that room type for the year. Grounds and pool expenses are allocated an equal amount to each room. Registration is allocated on the basis of the number of occupied rooms, that is, the number of nights a room type is occupied). The other activities are allocated using the cost drivers in a similar way.. In addition to the cost of the nine activities, CCOR has direct costs for each type of room, as shown below. These direct costs are incurred each night a room is occupied. These costs are called "external units. External Units (Direct Costs) Oceanfront Oceanview Pool Side Kitchenware replacement/repair 2.00 $ 1 Room Suite 2 Room Suite $ 3.00 Complimentary toiletries $ 2.00 $ Complimentary coffee and tea $ 3.00 $ 2.00 $ 3.00 $ 2.00 $ 3.00 $ 4.00 3.00 $ 5.00 $ 8.00 Required 1. Create a tab in your excel workbook to calculate your solution First Stage is the Allocation of Resources to Activities. 2. a. This is done by identifying Activity Consumption drivers per activity, Drivers are given in the example. b. Then Allocate cost to activities using those cost,. (Cost are Utilities, Facilities, Administration, Operations) You will need a total for both activities and cost 3. Second Stage Allocate Activity Cost to Room Type a. Create a table that calculates the percentage. You will need to take room information in the narrative and build a table that shows Cost Drivers per room. Once this is done you can calculate the % per room table. b. Next allocate activity cost to room type. C. Next create a table that calculates Total Direct Costs per room type. d. Now calculate total cost per room type e. Now calculate cost per occupied room. 4. Analyses the information you have completed and write a two page paper that describes what insights management gains from this analysis.