Profit enhancement using an ABC model: excel planning model optimizes process improvement efforts at a retirement and assisted living community.
The structure of the income statement is such that a planning model constructed using Microsoft Excel is capable of being optimized to determine which products should be emphasized in the product mix and which activity or activities should be the focal point of process improvement efforts. As demonstrated in this field study, the product mix choices suggested by an optimization of the computer model may be counter to the product mix choices that would be suggested by a more traditional ABC system that concentrated solely on the ABC operating profit per unit of each product.
The optimization of the computer model also identifies the activity that will use up all (or nearly all) of its capacity when the optimal product mix is produced and sold. This identification of the constraining activity gives management an opportunity to anticipate profit limitations imposed by the activity and focus its process improvement efforts on that constraining activity. The focus provided by the ABC model is such that management is not tempted to divert its process improvement efforts to the activities that have unused capacity when the optimal product mix is produced and sold. Thus, the ABC computer model contributes to focused process improvement, not costly indiscriminate process improvement of all activities.
The "intensity-of-usage factor" that is a critical element in ABC systems is the essential element for attributing activity costs to products. The attribution of costs to products coupled with the specification of each activity's practical capacity is what makes the calculation of each activity's unused capacity cost possible. The presence of each activity's unused capacity cost makes it possible for the computer model to be optimized using Microsoft Excel's "Solver" feature.
The XYZ Retirement and Assisted Living Community (XYZ) consists of 70 living units. (1) There are 10 two-bedroom units, 41 one-bedroom units, and 19 studio units. XYZ's product offerings can be divided into four primary services. Ranging from the lowest to the highest level of care, the three services offered are Care-Free Living, Semi-Assisted Living, and Assisted Living.
Care-Free Living is designed for minimal assistance for activities of daily living. The services include all utilities (except cable and telephone), emergency call response 24 hours a day, three daily restaurant-style meals, activities and social events, scheduled transportation, wellness program, weekly housekeeping, weekly laundering of bed and bath linens, and maintenance of apartments and common areas.
Semi-Assisted Living includes all of the services of Care-Free Living but also provides the resident additional assistance with activities of daily living such as monitoring meals, moving safely around the community, and coaching dressing and grooming.
Assisted Living includes all of the services of Care- Free Living and also provides a personalized assistance program such as full assistance with meals, dressing, grooming, and daily medication monitoring; nighttime checks, support, and transfers that only require one staff person; and weekly laundering of personal items.
A fourth service category is Short-Term Care, and it is the provision of short-term assisted living for individuals who want to move to the facility only while their family is away on vacation or otherwise unable to care for them for a short time period, for recuperation, or to give a caregiver a break. The studios and one-bedroom units are used for all care levels including Short-Term Care. The two-bedroom units, however, are used only for the lowest level of care (Care-Free Living). Short-Term Care residents are assessed and put into a care level that fits their needs.
ACTIVITY-BASED COSTING ELEMENTS
The elements of an ABC system include:
* Cost objects for which cost information is desired,
* Costs by activities,
* Cost drivers for each activity,
* Annual or monthly practical capacity of each activity in terms of the specified cost driver for the activity, and
* The extent to which each cost object uses the cost driver of each activity. We have chosen to call this element the "intensity-of-usage factor."
Given the types of apartments and categories of care, there are nine distinct service offerings at XYZ: Care-Free Living in three different types of living units (two-bedroom, one-bedroom, and studio), Semi-Assisted Living in two different types of living units (one-bedroom and studio), Assisted Living in two different types of living units (one-bedroom and studio), and Short-Term Care in two different types of living units (one-bedroom and studio). These nine service categories are the cost objects selected for XYZ's ABC system.
Having identified the nine cost objects of the ABC system, we turned our attention to the identification of activities necessary to provide the services to those nine cost objects. The objective was to disaggregate the operations of the facility into a limited number of activities in order to keep the ABC system simple. We also sought to disaggregate the operations into activities in a manner that would make use of the meaningful and significant activity cost drivers that were critical to the operations of the facility. The results, shown in Table 1, were the activities used in constructing the ABC computer model.
Because each of the activities was labor intensive, the activity cost driver selected was the number of labor hours associated with each activity. For example, the activity cost driver for the Resident Care activity was resident care labor hours, and the activity cost driver for the Housekeeping activity was housekeeping labor hours. Because one of the purposes of this ABC study was to identify the unused capacity associated with each activity, it was also necessary to estimate the practical capacity of each activity in terms of the activity cost driver for the activity. Based on the current staffing of the respective activities, for example, the Resident Care activity was estimated to have 1,517 resident-care labor hours of practical capacity during a month, and the Housekeeping activity was estimated to have 356 housekeeping hours of practical capacity during a month. Table 2 details the cost driver and monthly practical capacity for each of XYZ's activities.
Having established the cost objects, the activities, the activity cost drivers, and the monthly practical capacity for each activity cost driver, we needed to determine the extent to which each cost object used the activity cost driver of each activity (intensity-of-usage factor). For example, it was estimated by the appropriate XYZ employees that demands on the Resident Care activity during a month by each of the nine cost objects would be as shown in Table 3. Similar factors indicating intensity of usage by each cost object were estimated for each of the other activities, but those are not detailed in Table 3.
An examination of intensity-of-usage factors for the Resident Care activity reveals that the nine cost objects use the activities in varying degrees. For example, as the level of the care increases from Care-Free Living to Assisted Living, the amount of Resident Care time devoted to the residents in each living unit increases from 15.2 hours per month to as high as 50.7 hours per month. Although not shown or discussed here, there were similar differences in intensity of usage by the cost objects for the other activities.
The variable costs per resident and per living unit were identified as care supplies, general supplies, and food. Because these were variable costs, there was no need to attribute them to cost objects using the ABC system. These variable costs could be traced directly to the cost objects based on records maintained by the XYZ management. Moreover, the XYZ management provided information concerning the monthly rate that a resident was charged for each of the nine service cost objects. For those in Care-Free Living one-bedroom or two-bedroom units, two related persons (the primary resident and a second resident--usually a married couple) were permitted to share the same living unit for an additional monthly fee of $532.
THE ABC COMPUTER MODEL
The computer model used to analyze the operations of XYZ was developed using an approach suggested by Robin Cooper and Robert Kaplan. (2) Cooper and Kaplan demonstrated how the unused capacity of each activity could be isolated when there is an estimate of the practical capacity of each activity's cost driver. Further, the computer model was developed with the assumption that only the care supplies, general supplies, and food were variable costs and that the labor costs and facilities costs were fixed costs. Some may question the classification of labor costs as fixed costs. XYZ had a policy of workforce stability, however, in an attempt to enhance the quality of service provided to residents. This policy resulted in a very stable workforce over the years by minimizing employee turnover. The resultant computer model, assuming the current occupancy rate, is shown in Table 4.
The monthly projected income statement in Table 4 is prepared in the contribution margin format to reveal the contribution margin and the operating profit for each of the cost objects (Care-Free Living Studio, Care- Free Living One Bedroom, etc.). The contribution margin for each cost object is calculated by subtracting the variable costs from the revenues, and the operating profit is calculated by subtracting the fixed operating expenses from the contribution margin for each cost object.
The operating expenses are attributed to cost objects using an activity-based costing approach in which the monthly cost of each activity is divided by the activity's monthly practical capacity in terms of the activity's cost driver. For example, the Resident Care activity has a monthly activity cost of $16,568 and, as stated earlier, has a monthly practical capacity of 1,517 Resident Care hours. Thus, the cost of each Resident Care hour available is $10.92 ($16,568/1,517). Because there are 31 Care-Free Living One-Bedroom residents and each resident requires 15.2 hours of Resident Care each month, the estimated Resident Care expense for the month is $5,146 (31 residents 5 15.2 care hours per month 5 $10.92 hourly cost of care). That Resident Care expense is shown in Table 4 at the intersection of the Care-Free Living One-Bedroom cost object's column and the Resident Care activity's row. The operating expenses for the other cost objects and activities are calculated in the same manner.
An unusual feature of the monthly projected income statement shown in Table 4 is the column labeled "Unused Capacity." It is convenient to think of that column as a cost object that incurs cost but for which there is no revenue. The total cost of each activity is apportioned to each of the cost objects based on the extent to which each cost object uses each activity (the intensity-of-usage factor). Accordingly, the cost in the "Unused Capacity" column represents the cost of each activity's practical capacity that remained unused given the demands that all of the other cost objects placed on the activity. In the case of the Resident Care activity, the 56 total residents used 1,292. (2) hours (based on demands of each cost object on the Resident Care activity shown in Table 4 and the intensity-of-usage factors shown in Table 3) of the available Resident Care monthly practical capacity hours (1,292.2 of the 1,517 hours of practical capacity). The remaining 224.8 hours were not used during the month given the distribution of the 56 residents among the various cost objects. Those 224.8 hours at the rate of $10.92 explain the $2,455 of Resident Care unused capacity costs.
If the unused capacity cost was a negative amount, it would suggest the nonfeasible situation in which the cost objects used more activity hours than were available during the month. Thus, one would expect the unused capacity costs for each activity should be zero or greater, but never a negative amount. In Table 4, when the 56 residents are distributed among the cost objects as shown, each of the activities has some unused capacity as indicated by the positive dollar amounts in the "Unused Capacity" column.
It is important to note that the Administrative expense has no amount shown in the "Unused Capacity" column. That is because there was no meaningful activity cost driver that related the Administrative activity to each of the various cost objects. Without a cost driver, it is not possible to determine an unused capacity cost for an activity. This difficulty is common in activity-based costing systems because it is often not meaningful to relate the effort that takes place in a nonoperating activity to cost objects because nonoperating activities are so far removed from direct contact with the various cost objects. One way to deal with this difficulty is to allocate the cost of the Administration activity to all of the other activities that do have cost drivers. If that approach were used, the cost of the Resident Care activity would have been greater than the monthly cost of $16,568 that was used. Likewise, the cost of the other activities would have been greater, and there would have been no separate Administration activity on the income statement. We chose another approach in dealing with the Administration activity. We chose to allocate the Administration expense to the cost objects based on the quantity of unit sales in each category. Because the allocation is based on the estimated number of units sold rather than a practical capacity of the activity, there is no unused capacity cost associated with Administration.
Table 5 presents the information from the monthly projected income statement in a different format: per-unit terms (each unit being one occupant) for each of the cost objects. This table reveals the relative per-unit profit contribution of each cost object at both the contribution margin level and the operating profit level. From both a contribution margin perspective and from an operating profit perspective, the Assisted Living One-Bedroom cost object is the most profitable on a per-unit basis and the Care-Free Living Studio is the least profitable on a per-unit basis.
An examination of Table 5 suggests that XYZ, which has excess capacity in all activities, should strive to attract more Assisted Living residents, especially those requiring One-Bedroom units, because those units have the highest monthly profit per unit ($3,491 contribution margin and $1,118 operating profit). Further, the information suggests that very little marketing effort should be directed toward attracting Care-Free Living residents, especially those requiring Studios or One-Bedroom units, because those units have the lowest monthly contribution margin per unit and operating profit per unit. Thus, there is a strong suggestion that the unoccupied Studios and One-Bedroom units should be targeted for use by Assisted Living residents--those requiring the highest level of care.
As we will demonstrate, the contribution margin per unit and operating profit per unit information shown in Table 5 is not reliable as a basis for determining the most profitable mix of cost objects. Our use of an ABC model provides the information necessary to identify the most profitable mix of cost objects and also points to the activity or activities on which process improvement efforts should be directed in order to enhance profits.
MARKETING STRATEGY DEVELOPMENT
Any marketing strategy must deal with existing constraints. Table 6 shows the total number of units and the number of units currently occupied.
Management had established a policy that they wanted the facility to be regarded primarily as a retirement facility rather than an assisted living facility. Thus, they determined that no more than 19 of the 60 studio and one-bedroom living units should be devoted to Assisted Living residents. Further, management established a policy that no more than five of the units should be devoted to Short-Term Care in order to avoid a "transitory" atmosphere in the facility. Another policy was that any studio unit and any one-bedroom unit could be used for any care level; however, two-bedroom units could be used only for the lowest care level, Care-Free Living.
The income statement presented in Table 4 is based on a computer model created using Microsoft Excel. The policy constraints and the related physical capacity constraints, therefore, can be incorporated into the model to permit an analysis of the business situation. The "Solver" feature in Microsoft Excel permitted an optimization of the income statement model after establishing the "Solver" parameters (constraints) shown in Table 7. The optimization sought to maximize total company operating profit given the specified constraints.
Additional current occupancy constraints were included in the "Solver" parameter set to recognize that any optimal solution must consider that there should be no displacement of the current residents. Thus, the occupancy of any type of living unit must be greater than or equal to the current occupancy level. For example, 28 One-Bedroom units are currently occupied by Care-Free Living residents. Therefore, one of the constraints specified in the "Solver" parameter set is that Care-Free Living One-Bedroom Units >= 28. The constraints recognizing the current occupancy situation are detailed in Table 8.
"Solver" seeks to determine the product mix that will satisfy all of the specified constraints and maximize the operating profit of the company. The results of the optimization of the income statement model using "Solver" are shown in Table 9. The table reveals several items of information that are counterintuitive and helpful in developing a strategic marketing plan and the process improvement focus to enhance XYZ's monthly operating profit.
Contrary to the expectations suggested by Table 5 that the Assisted Living should be emphasized in a marketing campaign because of the high contribution margin per unit, the optimized model demonstrates that a more profitable approach would be to develop a marketing campaign to attract more Care-Free Living residents with an emphasis on the two-bedroom units.
The optimized model, when contrasted with the base model, demonstrates that profits could increase from $13,979 per month (current situation) in the base model to $47,135 per month if management could attract residents to 13 of the 17 unoccupied units without incurring additional operating expenses. Any additional advertising and promotional expenses, however, would reduce the amount of the suggested increase in operating profit.
The optimization model suggested four additional Care-Free Living Studio unit rentals, two more Care-Free Living One-Bedroom unit rentals, and seven more Care-Free Living Two-Bedroom unit rentals. There were no suggested increases in rental units devoted to Semi-Assisted Living, Assisted Living, or Short-Term Care. Thus, the cost object (Care-Free Living) that had the lowest profit per unit (as shown in Table 5) was targeted for emphasis, and the cost object (Assisted Living) that had the highest profit per unit (as shown in Table 5) had no suggested change in the number of units devoted to it. Further, only 66 of the 70 units available were rented in the optimized monthly income statement.
The reason for these unexpected results is that the optimized model recognizes that the Resident Care activity becomes the constraint as more residents are added to the facility. Once the Resident Care unused capacity is less than the amount necessary for the monthly care of one more resident ($131 of unused Resident Care capacity in Table 9), the optimization model is regarded as having reached the maximum profit potential given the established constraints. As indicated in Table 9, the "Unused Capacity" column reveals that all activities except the Resident Care activity have sufficient capacity to provide services to more residents.
Now that we understand that the operating expense portion of the income statement in Table 9 reveals that the Resident Care activity is the constraint, we can do some calculations to better understand why Care-Free Living residents are preferred over Assisted Living residents. Table 10 shows the per unit contribution margin of each cost object (as revealed in Table 5) divided by the hours per month (intensity-of-usage factors in Table 3) that the Resident Care activity has to devote to each resident in that cost object.
Table 10 shows that the Care-Free Living cost object has, on a monthly basis, the highest contribution margin per living unit per hour of Resident Care. We have called that quotient the "Index of Desirability," and it is useful in prioritizing the marketing emphasis when faced with a constraint. Further, within the Care-Free Living cost object, the Two-Bed units have the highest index of desirability ($181.91 of contribution margin per required hour of Resident Care). Notice that from an index of desirability viewpoint the Assisted Living cost object is the least attractive even though it had the highest contribution margin per unit and the highest operating profit per unit in Table 5. That is because the residents in that cost object require the most intensive Resident Care at the rate of 50.7 hours per month per resident.
Based on the index of desirability information provided above, we can begin to better understand why the optimization model recommended the renting to Care-Free Living residents rather than to any other group. (3) From a marketing strategy viewpoint, this analysis suggests that marketing should develop a campaign that targets Care-Free Living customers. In fact, the strategy should be to always have a waiting list of Care-Free Living customers who want a two-bedroom unit. There should be no advertising and promotional effort to attract Semi-Assisted Living or Assisted Living residents. Living units should be devoted to Semi-Assisted Living and Assisted Living residents only as the existing Care-Free Living residents age and require the higher levels of care that are associated with Semi-Assisted Living and Assisted Living.
Table 11 shows the resultant optimal model income statement results if we had erroneously used the per-unit profitability information in Table 5 as the basis of a marketing campaign. In that optimization of the income statement model, we forced "Solver" to consider increasing only the Assisted Living cost object. We did this by establishing a "Solver" parameter set that fixed the units for all cost objects, except the Assisted Living cost object, at the current-base-model unit sales levels and then used "Solver" to determine the new optimal income statement. The result was a monthly operating profit of only $27,639 versus the more appropriate optimal solution of monthly operating profit of $47,135 as shown in Table 9.
The optimization shown in Table 11 reveals that there are 10 Assisted Living units rented versus the six units that were revealed to be rented in the base model shown in Table 4. One might wonder why there was only an increase of four Assisted Living units in the optimal solution that forced the marketing attention on Assisted Living. The answer is revealed in the "Unused Capacity" column of the Resident Care activity in Table 11. That shows that there is only $240 of unused capacity remaining in the Resident Care activity, which is insufficient to care for even one additional Assisted Living resident.
PROCESS IMPROVEMENT EMPHASIS
Table 9 indicated that the Resident Care activity is the one that is most likely to become the constraint. It is also true that the people who work in that activity are paid the highest wages and are the most difficult to hire because they are among the most skilled of the employees. Thus, management, now knowing that Resident Care is critical to profit enhancement, might choose to examine the process by which Resident Care is performed.
Management might consider training on standardization in the care of residents. Such training might be based on management's understanding of best practices in an attempt to reduce the amount of time devoted to each cost object without sacrificing the quality of residential care. Another process improvement might include improved scheduling of resident care in order to reduce idle time among those employed in the Resident Care activity. Once management understands the increased profit potential associated with process improvement in the constrained activity, there is usually a strong motivation to identify process improvement opportunities that can be used to reduce the intensity-of-usage factors associated with the activity.
If management is successful in reducing the amount of time required to care for each resident by 10%, the result would be to create more unused capacity, which could be used to support the rental of additional units and the generation of additional monthly operating profit. Although not shown here, optimization after a 10% reduction in the Resident Care activity's intensity-of-usage factors resulted in an optimal solution that yielded operating profits of $59,798 per month versus the operating profits of $47,135 per month (Table 9) prior to the 10% reduction in the Resident Care intensity-of-usage factors. In the optimal solution that results in operating profits of $59,798 per month, all of the living units were rented and the Resident Care activity's "Unused Capacity" was less than enough to support one more resident.
ABC ANALYSIS FACILITATES OPTIMAL SOLUTIONS
The combination of activity-based costing and optimization using Microsoft Excel's "Solver" provides powerful accounting information support to a coordinated marketing and process improvement effort by management. The structuring of the projected income statement based on activity-based costing principles requires that each activity be related to each cost object in terms of the activity's cost driver. Further, ABC's relating of each activity's cost driver to each cost object using the intensity-of-usage factors provides the activity-cost object linkages that are necessary for the application of the optimization technique. When the ABC income statement is further structured to reveal the contribution margin for each cost object, the computer model of the projected ABC income statement may then be used, along with Microsoft Excel's Solver, to evaluate optimal solutions.
As indicated, the optimal solutions are often counterintuitive. Although they are not definitive solutions in the development of a marketing strategy, those solutions do provide a framework within which various alternatives may be evaluated. The assumptions in the computer model and/or in the Solver parameter set may be changed to provide insights to the management team as they seek to identify opportunities for operating profit enhancement. The cooperation of marketing management and operations management has the potential of enhancing profits significantly. This improvement may be accomplished by focusing process improvement on the constraint and by focusing marketing efforts on those cost objects that have the highest "desirability index" based on the identified constraining activity.
Table 1: Activity Descriptions ACTIVITIES DESCRIPTION OF ACTIVITIES Resident Care Assist with activities of daily living Housekeeping Clean the community Maintenance Maintain the community and grounds Food Service Perform all activities to provide three meals per day to each resident Resident Activities Plan and recruit resident involvement in activities Transportation Transport residents via van Administrative Operate and provide strategic direction to the community Table 2: Cost Drivers and Practical Capacity MONTHLY PRACTICAL ACTIVITIES COST DRIVERS CAPACITY Resident Care Number of Resident Care hours per month 1,517 hours Housekeeping Number of Housekeeping hours per month 356 hours Maintenance Number of Maintenance hours per month 165 hours Food Services Number of Food Services hours per month 928 hours Resident Number of Resident Activities hours per Activities month 235 hours Transportation Number of Transportation hours per month 124 hours Table 3: Resident Care Intensity-of-Usage Factors COST OBJECTS INTENSITY OF USAGE FACTORS C-Free Living Two-Bedroom 15.2 hours per month C-Free Living One-Bedroom 15.2 hours per month C-Free Living Studio 15.2 hours per month Semi-Assisted Living One-Bedroom 30.4 hours per month Semi-Assisted Living Studio 30.4 hours per month Assisted Living One-Bedroom 50.7 hours per month Assisted Living Studio 50.7 hours per month Short-Term One-Bedroom 30.4 hours per month Short-Term Studio 30.4 hours per month Table 4: Base Model-Current Situation XYZ Retirement and Assisted Living Projected Monthly Income Statement CARE-FREE LIVING One Two Studio Bedroom Bedrooms Residents Primary Residents 1 28 3 Second Residents 0 3 0 Total Residents 1 31 3 Revenues Primary Resident $2,342 $75,798 $8,942 Second Resident 0 1,596 0 Total Revenue $2,342 $77,394 $8,942 Variable Expenses Care Supplies $1 $39 $4 Food and Other Supplies 215 6,658 644 Total Variable Expense $216 $6,696 $648 Contribution Margin $2,126 $70,698 $8,294 Operating Expense Resident Care $166 $5,146 $498 Housekeeping 17 570 69 Maintenance 16 498 48 Food Service 84 2,590 251 Resident Activities 16 487 47 Transportation 8 240 23 Administrative 1,682 52,138 5,046 Total Operating Expense $1,988 $61,669 $5,982 Operating Profit $138 $9,029 $2,313 SEMI-ASSISTED LIVING ASSISTED LIVING One One Studio Bedroom Studio Bedroom Residents Primary Residents 3 8 5 1 Second Residents 0 0 0 0 Total Residents 3 8 5 1 Revenues Primary Resident $8,578 $25,307 $17,033 $3,711 Second Resident 0 0 0 0 Total Revenue $8,578 $25,307 $17,033 $3,711 Variable Expenses Care Supplies $8 $20 $25 $5 Food and Other Supplies 644 1,718 1,074 215 Total Variable Expense $652 $1,738 $1,099 $220 Contribution Margin $7,926 $23,569 $15,935 $3,491 Operating Expense Resident Care $996 $2,656 $2,769 $554 Housekeeping 52 147 115 24 Maintenance 48 129 80 16 Food Service 254 678 431 86 Resident Activities 37 99 16 3 Transportation 23 62 39 8 Administrative 5,046 13,455 8,409 1,682 Total Operating Expense $6,456 $17,226 $11,859 $2,373 Operating Profit $1,470 $6,343 $4,075 $1,118 SHORT-TERM CARE One Unused Studio Bedroom Capacity Total Residents Primary Residents 3 1 N/A 53 Second Residents 0 0 N/A 3 Total Residents 3 1 N/A 56 Revenues Primary Resident $9,125 $3,042 N/A $153,878 Second Resident 0 0 N/A 1,596 Total Revenue $9,125 $3,042 N/A $155,474 Variable Expenses Care Supplies $8 $3 N/A $111 Food and Other Supplies 644 215 N/A 12,027 Total Variable Expense $652 $217 N/A $12,138 Contribution Margin $8,473 $2,824 N/A $143,336 Operating Expense Resident Care $996 $332 $2,455 $16,568 Housekeeping 52 18 1,757 2,820 Maintenance 48 16 2,015 2,915 Food Service 254 85 4,495 9,207 Resident Activities 37 12 1,827 2,584 Transportation 23 8 645 1,079 Administrative 5,046 1,682 N/A 94,184 Total Operating Expense $6,456 $2,153 $13,194 $129,357 Operating Profit $2,017 $671 $(13,194) $13,979 Table 5: Base Model--Per-Unit Income Statement XYZ Retirement and Assisted Living CARE-FREE LIVING One Two Studio Bedroom Bedrooms Total Revenue $2,342 $2,497 $2,981 Variable Expenses Care Supplies $1 $1 $1 Food and Other Supplies 215 215 215 Total Variable Expense $216 $216 $216 Contribution Margin $2,126 $2,281 $2,765 Operating Expense Resident Care $166 $166 $166 Housekeeping 17 18 23 Maintenance 16 16 16 Food Service 84 84 84 Resident Activities 16 16 16 Transportation 8 8 8 Administration 1,682 1,682 1,682 Total Operating Expense $1,988 $1,989 $1,994 Operating Profit $138 $291 $771 SEMI-ASSISTED ASSISTED LIVING LIVING One Studio Bedroom Studio Total Revenue $2,859 $3,163 $3,407 Variable Expenses Care Supplies $3 $3 $5 Food and Other Supplies 215 215 215 Total Variable Expense $217 $217 $220 Contribution Margin $2,642 $2,946 $3,187 Operating Expense Resident Care $332 $332 $554 Housekeeping 17 18 23 Maintenance 16 16 16 Food Service 85 85 86 Resident Activities 12 12 3 Transportation 8 8 8 Administration 1,682 1,682 1,682 Total Operating Expense $2,152 $2,153 $2,372 Operating Profit $490 $793 $815 ASSISTED LIVING SHORT-TERM CARE One One Bedroom Studio Bedroom Total Revenue $3,711 $3,042 $3,042 Variable Expenses Care Supplies $5 $3 $3 Food and Other Supplies 215 215 215 Total Variable Expense $220 $217 $217 Contribution Margin $3,491 $2,824 $2,824 Operating Expense Resident Care $554 $332 $332 Housekeeping 24 17 18 Maintenance 16 16 16 Food Service 86 85 85 Resident Activities 3 12 12 Transportation 8 8 8 Administration 1,682 1,682 1,682 Total Operating Expense $2,373 $2,152 $2,153 Operating Profit $1,118 $672 $671 Table 6: Resident Care Intensity-of-Usage Factors TOTAL NUMBER NUMBER OF UNITS OF UNITS UNITS OCCUPIED AVAILABLE Studios 19 12 7 One-Bedroom 41 38 3 Two-Bedroom 10 3 7 Total 70 53 17 Table 7: Physical and Policy Constraints Maximum Studios Occupied <= 19 units Maximum One-Bedrooms Occupied <= 41 units Maximum Two-Bedrooms Occupied <= 10 units Maximum Short-Term Care Units <= 5 units Maximum Assisted Living Units <= 19 units Unused Capacity of each activity >= zero dollars Table 8: Current Occupancy Constraints Care-Free Living Studio Units >= 1 Care-Free Living One-Bedroom Units >= 28 Care-Free Living Two-Bedroom Units >= 3 Semi-Assisted Living Studio Units >= 3 Semi-Assisted Living One-Bedroom Units >= 8 Assisted Living Studio Units >= 5 Assisted Living One-Bedroom Units >= 1 Short-Term Care Studio Units >= 3 Short-Term Care One-Bedroom Units >= 1 Table 9: Optimized Model--XYZ Retirement and Assisted Living Projected Monthly Income Statement CARE-FREE LIVING One Two Studio Bedroom Bedrooms Residents Primary Residents 5 30 10 Second Residents 0 3 1 Total Residents 5 33 11 Revenues Primary Residents $11,710 $81,212 $29,808 Second Residents 0 1,596 532 Total Revenue $11,710 $82,808 $30,340 Variable Expenses Care Supplies $6 $41 $14 Food and Other Supplies 1,074 7,087 2,362 Total Variable Expense $1,080 $7,128 $2,376 Contribution Margin $10,630 $75,680 $27,964 Operating Expense Resident Care $830 $5,478 $1,826 Housekeeping 86 606 253 Maintenance 80 531 177 Food Service 418 2,757 919 Resident Activities 79 519 173 Transportation 39 256 85 Administrative 6,727 44,401 14,800 Total Operating Expense $8,259 $54,547 $18,233 Operating Profit $2,372 $21,133 $9,731 SEMI-ASSISTED LIVING ASSISTED LIVING One One Studio Bedroom Studio Bedroom Residents Primary Residents 3 8 5 1 Second Residents 0 0 0 0 Total Residents 3 8 5 1 Revenues Primary Residents $8,578 $25,307 $17,033 $3,711 Second Residents 0 0 0 0 Total Revenue $8,578 $25,307 $17,033 $3,711 Variable Expenses Care Supplies $8 $20 $25 $5 Food and Other Supplies 644 1,718 1,074 215 Total Variable Expense $652 $1,738 $1,099 $220 Contribution Margin $7,926 $23,569 $15,935 $3,491 Operating Expense Resident Care $996 $2,656 $2,769 $554 Housekeeping 52 147 115 24 Maintenance 48 129 80 16 Food Service 254 678 431 86 Resident Activities 37 99 16 3 Transportation 23 62 39 8 Administrative 4,036 10,764 6,727 1,345 Total Operating Expense $5,447 $14,535 $10,178 $2,037 Operating Profit $2,479 $9,034 $5,757 $1,454 SHORT-TERM CARE One Unused Studio Bedroom Capacity Total Residents Primary Residents 3 1 N/A 66 Second Residents 0 0 N/A 4 Total Residents 3 1 N/A 70 Revenues Primary Residents $9,125 $3,042 N/A $189,526 Second Residents 0 0 N/A 2,128 Total Revenue $9,125 $3,042 N/A $191,654 Variable Expenses Care Supplies $8 $3 N/A $129 Food and Other Supplies 644 215 N/A 15,033 Total Variable Expense $652 $217 N/A $15,162 Contribution Margin $8,473 $2,824 N/A $176,492 Operating Expense Resident Care $996 $332 $131 $16,568 Housekeeping 52 18 1,467 2,820 Maintenance 48 16 1,790 2,915 Food Service 254 85 3,325 9,207 Resident Activities 37 12 1,607 2,584 Transportation 23 8 537 1,079 Administrative 4,036 1,345 N/A 94,184 Total Operating Expense $5,447 $1,817 $8,858 $129,357 Operating Profit $3,026 $1,008 (8,858) $47,135 Table 10: Calculation of Index of Desirability CONTRIBUTION MARGIN COST OBJECT PER UNIT PER MONTH C-Free Studio $2,126 divided by C-Free 1 Bed $2,281 divided by C-Free 2 Bed $2,765 divided by Semi-Assisted Studio $2,642 divided by Semi-Assisted 1 Bed $2,946 divided by Assisted Studio $3,187 divided by Assisted 1 Bed $3,491 divided by Short-Term Studio $2,824 divided by Short-Term 1 Bed $2,824 divided by RESIDENT CARE INDEX OF COST OBJECT HOURS PER MONTH DESIRABILITY C-Free Studio 15.2 hours = $139.87 per hr. C-Free 1 Bed 15.2 hours = $150.07 per hr. C-Free 2 Bed 15.2 hours = $181.91 per hr. Semi-Assisted Studio 30.4 hours = $86.91 per hr. Semi-Assisted 1 Bed 30.4 hours = $96.91 per hr. Assisted Studio 50.7 hours = $62.86 per hr. Assisted 1 Bed 50.7 hours = $68.86 per hr. Short-Term Studio 30.4 hours = $92.89 per hr. Short-Term 1 Bed 30.4 hours = $92.89 per hr. Table 11: Optimized Solution--Forced Enhanced Living Solution XYZ Retirement and Assisted Living Projected Monthly Income Statement CARE-FREE LIVING One Two Studio Bedroom Bedrooms Residents Primary Residents 1 28 3 Second Residents 0 3 0 Total Residents 1 31 3 Revenues Primary Residents $2,342 $75,798 $8,942 Second Residents 0 1,596 0 Total Revenue $2,342 $77,394 $8,942 Variable Expenses Care Supplies $211 $6,639 $644 Food and Other Supplies 215 6,658 644 Total Variable Expense $216 $6,696 $648 Contribution Margin $2,126 $70,698 $8,294 Operating Expense Resident Care $1,166 $65,146 $498 Housekeeping 17 570 69 Maintenance 16 498 48 Food Service 84 2,590 251 Resident Activities 16 487 47 Transportation 8 240 23 Administrative 1,682 52,138 5,046 Total Operating Expense $1,988 $61,669 $5,982 Operating Profit $138 $9,029 $2,313 SEMI-ASSISTED LIVING ASSISTED LIVING One One Studio Bedroom Studio Bedroom Residents Primary Residents 3 8 5 1 Second Residents 0 0 0 0 Total Residents 3 8 5 1 Revenues Primary Residents $8,578 $25,307 $17,033 $3,711 Second Residents 0 0 0 0 Total Revenue $8,578 $25,307 $17,033 $3,711 Variable Expenses Care Supplies $658 $1,720 $1,025 $225 Food and Other Supplies 644 1,718 1,074 215 Total Variable Expense $652 $1,738 $1,099 $220 Contribution Margin $7,926 $23,569 $15,935 $3,491 Operating Expense Resident Care $6,996 $12,656 $12,769 $2,554 Housekeeping 52 147 115 24 Maintenance 48 129 80 16 Food Service 254 678 431 86 Resident Activities 37 99 16 3 Transportation 23 62 39 8 Administrative 5,046 13,455 8,409 1,682 Total Operating Expense $6,456 $17,226 $11,859 $2,373 Operating Profit $1,470 $6,343 $4,075 $1,118 SHORT-TERM CARE One Unused Studio Bedroom Capacity Total Residents Primary Residents 3 1 N/A 53 Second Residents 0 0 N/A 3 Total Residents 3 1 N/A 56 Revenues Primary Residents $9,125 $3,042 N/A $153,878 Second Residents 0 0 N/A 1,596 Total Revenue $9,125 $3,042 N/A $155,474 Variable Expenses Care Supplies $658 $213 N/A $12,111 Food and Other Supplies 644 215 N/A 12,027 Total Variable Expense $652 $217 N/A $12,138 Contribution Margin $8,473 $2,824 N/A $143,336 Operating Expense Resident Care $6,996 $2,332 $12,455 $116,568 Housekeeping 52 18 1,757 2,820 Maintenance 48 16 2,015 2,915 Food Service 254 85 4,495 9,207 Resident Activities 37 12 1,827 2,584 Transportation 23 8 645 1,079 Administrative 5,046 1,682 N/A 94,184 Total Operating Expense $6,456 $2,153 $13,194 $129,357 Operating Profit $2,017 $671 (13,194) $13,979
The authors acknowledge the contributions of S. Kempf, R. Sharmal, D. Page, and J. Sampson.
(1) The identity of the Retirement and Assisted Living Community has been disguised, and the data reported in this article have been systematically distorted in order to protect the identity of the business.
(2) Robin Cooper and Robert S. Kaplan, "Activity-Based Systems: Measuring the Costs of Resource Usage," Accounting Horizons, September 1992.
(3) For a comprehensive treatment of the use of constraints in conjunction with the distinction between variable costs and fixed costs as a basis for decision making, see Thomas Corbett, Throughput Accounting, North River Press, Great Barrington, Mass., 1998.
Sidney J. Baxendale, DBA, CPA, CMA, CFM, is professor of accountancy at the College of Business and Public Administration, University of Louisville, Louisville, Ky. He can be reached at (502) 852-4813 or firstname.lastname@example.org. Mahesh Gupta, Ph.D., is professor of management, and P.S. Raju, Ph.D., is professor and chair of the marketing department, also at the University of Louisville.
|Printer friendly Cite/link Email Feedback|
|Title Annotation:||activity-based costing|
|Author:||Baxendale, Sidney J.; Gupta, Mahesh; Raju, P.S.|
|Publication:||Management Accounting Quarterly|
|Date:||Jan 1, 2005|
|Previous Article:||Wireless instruction: a new dimension in course delivery: when distance learning classes go wireless, the students, faculty, and educational...|
|Next Article:||Merging management accounting with database design: management accountants have a greater opportunity to support the implementation of corporate...|