Profit enhancement using an ABC model: excel planning model optimizes process improvement efforts at a retirement and assisted living community

Management Accounting Quarterly, Wntr, 2005 by Sidney J. Baxendale, Mahesh Gupta, P.S. Raju

EXECUTIVE SUMMARY Articles and books written about Activity-Based Costing (ABC) systems over the past 18 years have focused on the use of an activity perspective to better understand the cost of products, services, and other cost objects, such as the cost of serving specific customers. We conducted a field study to explore a more precise use of ABC to enhance the profits of a business through a specially structured income statement that can be optimized using Microsoft Excel's "Solver" feature. This field study relies upon ABC's "intensity-of-usage factor" to create an income statement that has some of the features of a contribution margin income statement. In this income statement, the contribution margin of each product is identified, but, unlike a traditional contribution margin income statement, the fixed costs are attributed to products based on the extent to which each product uses each activity's practical capacity. The result is a "full-cost" income statement with a contribution margin subtotal. The other distinguishing feature of this income statement is that the cost of the unused capacity is explicitly revealed for each activity.

**********

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 SETTING

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,