Need help with constructing an excel file for Exhibits 1 & 2.? Instructions for the assignment are attached on word document, along with the excel file & the PDF for the
Need help with constructing an excel file for Exhibits 1 & 2.
Instructions for the assignment are attached on word document, along with the excel file & the PDF for the CVP Analysis
Case 3 – CVP instructions
1. Use the Excel file for Exhibits 1 & 2. There are a few typos in the tables within the case, but the excel file is correct.
2. Add a 3rd tab to the excel file (change the name of the file to your last name + case 3) and you can provide your responses and prepare your calculations in the excel file.
3. Answer questions 1 – 8 in the case
4. Question 9 (new). What happens to Bridgestone BHC operating profit under the following scenarios:
a. Revenues increase 10% (FC remain same)
b. Revenues decrease 10% (FC remain same)
c. Fixed costs increase 5% (revenues remain same)
d. Fixed costs decrease 5% (revenues remain same)
5. Question 10 (new). Using this case as an example, how can managers use CVP analysis for planning and control?
,
Ex 1
Exhibit 1 | ||||||||||||||
Assessment | Urinalysis | Case Management | Group Counseling | individual Counseling | Crisis Intervention | Intensive Outpatient | Medical Somatic | Methadone Maint. | Ambul Detox | Total | ||||
Revenue | ||||||||||||||
Medicaid | ||||||||||||||
Rate | $ 40 | $ 100 | $ 80 | $ 30 | $ 90 | $ 140 | $ 110 | $ 200 | $ 15 | $ 260 | ||||
Units | 4,000 | 11,550 | 6,000 | 28,000 | 12,000 | 1,500 | 2,000 | 800 | 8,000 | 1,300 | ||||
Revenue | $ 160,000 | $ 1,155,000 | $ 480,000 | $ 840,000 | $ 1,080,000 | $ 210,000 | $ 220,000 | $ 160,000 | $ 120,000 | $ 338,000 | $ 4,763,000 | |||
State | $ 3,000 | $ 205,000 | 208,000 | |||||||||||
Private pay | $ 10,000 | $ 500 | $ 5,000 | $ 10,000 | $ 3,500 | $ 29,000 | ||||||||
Total | $ 170,000 | $ 1,155,500 | $ 480,000 | $ 845,000 | $ 1,090,000 | $ 210,000 | $ 226,500 | $ 160,000 | $ 120,000 | $ 543,000 | $ 5,000,000 | |||
Variable Costs | ||||||||||||||
Medications/Tests | $ 30,000 | $ 404,250 | $ 140,000 | $ 120,000 | $ 30,000 | $ 200,000 | $ 200,000 | $ 96,000 | $ 260,000 | $ 1,480,250 | ||||
Other | 2,500 | 2,000 | 10,000 | 2,000 | 2,000 | 1,000 | 250 | 19,750 | ||||||
Total | 32,500 | 406,250 | 10,000 | 142,000 | 122,000 | 31,000 | 200,250 | 200,000 | 96,000 | 260,000 | 1,500,000 | |||
Contribution Margin | $ 137,500 | $ 749,250 | $ 470,000 | $ 703,000 | $ 968,000 | $ 179,000 | $ 26,250 | $ (40,000) | $ 24,000 | $ 283,000 | $ 3,500,000 | |||
Fixed expenses | ||||||||||||||
Salaries & Benefits | 1,827,607 | |||||||||||||
Consulting fees | 174,685 | |||||||||||||
Rent | 419,244 | |||||||||||||
Transportation | 104,811 | |||||||||||||
Office supplies | 69,874 | |||||||||||||
Depreciation | 373,424 | |||||||||||||
Other | 524,055 | |||||||||||||
Total Fixed expenses | 3,493,700 | |||||||||||||
Ex 2
Exhibit 2 | |||||||||||||
Assessment | Urinalysis | Case Management | Group Counseling | individual Counseling | Crisis Intervention | Intensive Outpatient | Medical Somatic | Methadone Maint. | Ambul Detox | Total | |||
Revenue | |||||||||||||
Medicaid | |||||||||||||
Rate | $ 40 | $ 100 | $ 80 | $ 30 | $ 90 | $ 140 | $ 110 | $ 200 | $ 15 | $ 260 | |||
Units | 1,000 | 2,888 | 1,500 | 7,000 | 3,000 | 375 | 500 | 200 | 2,000 | 325 | |||
Revenue | $ 40,000 | $ 288,800 | $ 120,000 | $ 210,000 | $ 270,000 | $ 52,500 | $ 55,000 | $ 40,000 | $ 30,000 | $ 84,500 | $ 1,190,800 | ||
State | $ 750 | $ 51,250 | 52,000 | ||||||||||
Private pay | $ 2,500 | $ 125 | $ 1,250 | $ 2,500 | $ 875 | $ 7,250 | |||||||
Total | $ 42,500 | $ 288,925 | $ 120,000 | $ 211,250 | $ 272,500 | $ 52,500 | $ 56,625 | $ 40,000 | $ 30,000 | $ 135,750 | $ 1,250,050 | ||
Variable Costs | |||||||||||||
Medications/Tests | $ 7,500 | $ 101,063 | $ 35,000 | $ 30,000 | $ 7,500 | $ 50,000 | $ 50,000 | $ 24,000 | $ 65,000 | $ 370,063 | |||
Other | 625 | 500 | 2,500 | 500 | 500 | 250 | 63 | 4,938 | |||||
Total | 8,125 | 101,563 | 2,500 | 35,500 | 30,500 | 7,750 | 50,063 | 50,000 | 24,000 | 65,000 | 375,001 | ||
Contribution Margin | $ 34,375 | $ 187,362 | $ 117,500 | $ 175,750 | $ 242,000 | $ 44,750 | $ 6,563 | $ (10,000) | $ 6,000 | $ 70,750 | $ 875,050 | ||
Fixed expenses | |||||||||||||
Salaries & Benefits | 456,901 | ||||||||||||
Consulting fees | 43,671 | ||||||||||||
Rent | 104,811 | ||||||||||||
Transportation | 26,203 | ||||||||||||
Office supplies | 17,469 | ||||||||||||
Depreciation | 93,356 | ||||||||||||
Other | 131,014 | ||||||||||||
Total Fixed expenses | 873,425 | ||||||||||||
,
A. Ronald Kucic
University of Denver
James E. Sorensen
University of Denver
Lisa M. Victoravich
University of Denver
INTRODUCTION
In June of the current year Dr. Thomas Russell, Executive
Director, and Susan Smyth, Accountant, at the Bridgestone
Behavioral Health Center were discussing the necessity
of gaining a better understanding of how to monitor the
Center’s operating and financial performance. Located
in Cleveland, Ohio, Bridgestone provides prevention,
intervention, and treatment services for individuals with
substance abuse problems. Bridgestone’s management is
concerned about its financial performance after realizing
a loss in the prior year although a small profit was
projected. Despite management’s concern and attention
of Bridgestone’s profitability troubles, the Center’s annual
budget once again contains a projection for a meager profit of
$7,000 (see Exhibit 1).
Thomas: According to the financial reports that you have
prepared, we have been fighting to reach breakeven over
the past three years. We have been able to just get past
breakeven recently, but I am worried—it seems like only
a slight variation in our operations could throw us into an
operating loss. I’ve been actively involved in managing the
Center, and I don’t understand how we avoid showing a loss!
Susan: Unfortunately, I have no oversight with respect to
monitoring performance since my job is to input the financial
and units of service numbers into the accounting system
as they occur. I am currently working more than 40 hours a
week so I am not sure what else I could do. Maybe we can
hire someone like a consultant to help us?
Thomas: In reality, securing some outside assistance seems
to be a good option to avoid future losses. Since my training
is in psychology, I don’t have the accounting background to
take the task on myself. We need someone who specializes
in financial management for nonprofit human service
organizations.
BRIDGESTONE BACKGROUND
HISTORY AND MISSION Bridgestone is a comprehensive outpatient substance
abuse treatment center located in the Midwest United
States. Since 1985, Bridgestone has offered a continuum of
outpatient services, including counseling, crisis intervention,
detoxification, and methadone maintenance. The Bridgestone
mission is to be a leader in healing and changing lives
by providing high quality behavioral health care and
rehabilitation to all individuals in need. With this mission, the
Center promises to advance behavioral healthcare through
the creation of innovative services and enriching the lives of
patients one by one.
EXECUTIVE DIRECTOR AND SUPPORTING STAFF Dr. Thomas Russell, Executive Director, has over 30 years
of behavioral health addiction experience and holds a
Masters of Social Work from Ohio University and a Ph.D.
in Clinical Psychology with a specialization on Addiction
Counseling from Northwestern University. He is a fellow
I M A E D U C AT I O N A L C A S E J O U R N A L V O L . 3 , N O . 4 , A R T. 1 , D E C E M B E R 2 0 1 01
ISSN 1940-204X
Bridgestone Behavioral Health Center: Cost-Volume-Profit (CVP) Analysis for Planning and Control
of the American College of Addiction Examiners and is a
former faculty member of the Ohio University Department
of Psychology. Dr. Russell is the author of numerous papers
in the field of addictions and presents frequently at major
conferences.
In addition to a highly qualified Executive Director,
Bridgestone has an outstanding support staff. They
consistently maintain a low client-to-staff ratio with highly
trained professionals who are well prepared to support
patients in a large array of circumstances. The staff members
at Bridgestone are like a family and are defined by their
integrity and expertise. They are the key reason for the
Center’s success in terms of the thousands of lives that they
have changed.
PATIENT TESTIMONIALS Patient and family member testimonials include:
“ I wanted to say thank you for helping my daughter!
I know of lots of therapists…in fact I am one.
After 20 years of practice I was very frustrated
sending my daughter to treatment until Bridgestone.
Yes there are therapists…but your folks are
THERAPISTS, with a capital T.”
“ Today is my one year anniversary of sobriety, which
would not have been possible without the help
of the counselors at Bridgestone. I can’t say thank
you enough!”
“ I’m thankful to Bridgestone for the treatment I
received. The road to recovery was not an easy road
back. I still do the same elementary things today
that I had to do to achieve recovery. I realized that
honesty and open-mindedness was a must. I had to
surrender all—I wanted real success.”
BEHAVIORAL HEALTH AND ADDICTION SERVICES Bridgestone offers 10 services designed to treat a large array
of substance dependencies from the point of detoxification
to supporting long-term sobriety and preventing relapses.
Patient Assessment – Patient assessment is performed by
an addiction counselor who uses a number of methods to
determine if an alcohol or drug use problem truly exists. If
such a diagnosis is established, it is necessary to determine
the extent or severity of disease, if there is a need for
medical detoxification, and the services or level of care
required to safely and successfully achieve sobriety.
Lab Urinalysis – Laboratory urinalysis services are performed
for screening and intervention purposes. This is aimed
at helping the patient withstand urges to use drugs. Such
monitoring also can provide early evidence of drug use so
that the individual’s treatment plan can be properly adjusted.
Case Management – Case management serves to monitor
patient progress and compliance with recommended
addiction treatment plans. Reports to outside parties such as
courts, children’s protective services, licensing boards, and
employers are provided as needed.
Group Counseling – Groups are considered the most effective
method of breaking patterns of isolation, which is typical
of clients during their active addiction stage. During group
counseling, clients have the opportunity to examine their
thoughts through sharing, listening, and receiving feedback
from their peers. In a group setting, clients also learn to
develop social skills.
Individual Counseling – Individuals with addictions often
consider their problems unique and, therefore, are best
understood in a one-on-one session with their counselor.
Individual counseling sessions focus on the patient’s
progress in the educational process, group participation, drug
monitoring, compliance, 12–step work, and identification of
areas for improvement to achieve long-term sobriety.
Crisis Intervention – Due to the volatile nature of the disease
of addiction, Bridgestone provides crisis intervention or
emergency counseling for stressful situations that may lead
to relapse or to help patients work through cravings or urges
that could lead to a relapse.
Intensive Outpatient – Outpatient care focuses on education
and counseling in both individual and group settings to
achieve abstinence and to develop a self-responsible plan of
addiction recovery for each patient. The program lasts six to
eight weeks depending on individual progress and involves
three three-hour sessions per week.
Medical Somatic – The service evaluates and monitors
the needs of adults, children, and adolescents exhibiting
symptoms associated with a Diagnostic and Statistical Manual
of Mental Disorders, Fourth Edition, (DSM-IV) diagnosis.
(DSM-V is expected in 2013.) This consists of evaluating a
patient’s need for psychotropic medications associated with
psychotic disorders, severe mood disorders, impulse control
disorders, bi-polar disorders, and other psychiatric conditions
impairing daily functioning.
I M A E D U C AT I O N A L C A S E J O U R N A L V O L . 3 , N O . 4 , A R T. 1 , D E C E M B E R 2 0 1 02
Methadone Maintenance – Patients with an opiate addiction
are provided with individualized healthcare and medically
prescribed methadone to relieve withdrawal symptoms,
reduce the opiate craving, and bring about a biochemical
balance in the body.
Ambulatory Detoxification – Ambulatory detoxification (also
known as outpatient detoxification) is designed to safely
detoxify patients from drugs and alcohol without a hospital
admission. Ambulatory detoxification as an outpatient
treatment has the advantage of causing minimal disruption to
a patient’s normal day-to-day life.
MEDICAID FOR BEHAVIORAL HEALTH SERVICES
Ohio is one of the few states that includes behavioral health
services in its Medicaid benefits package, although it is not
required to do so by federal law. States that cover behavioral
health services must clearly define which services are
covered by Medicaid. To facilitate receiving payment for
Medicaid patients, the types of services Bridgestone offers
are consistent with those defined as covered services by the
state of Ohio.
When a Medicaid client receives mental health and/
or alcohol and drug addiction services, the local Alcohol,
Drug Addiction and Mental Health (ADAMH) Board,
Alcohol and Drug Addiction Services (ADAS) Board or the
Community Mental Health (CMH) Board pays the provider.
The Board is then reimbursed for 60% of the cost by the
federal government, which flows to the Boards through
the Ohio Department of Mental Health (ODMH) and the
Ohio Department of Alcohol and Drug Addiction Services
(ODADAS) via an interagency with the Ohio Department
of Job and Family Services (ODJFS). The remaining 40%
Medicaid match comes from both state and local sources.
The Medicaid reimbursement rates for each service
type and the respective level of service expected for the
upcoming year are used in preparing the Center’s annual
budget and quarterly budget as presented in Exhibit 1 and
Exhibit 2, respectively. Since there is modest fluctuation
in the demand for services from quarter to quarter, the
quarterly budget is 25% of the annual budget. Although
Medicaid reimbursement is the primary source of revenue
for Bridgestone, a minimal amount of revenue is generated
from private pay customers.
SERVICE CONSULTING PLUS, LLC
As identified earlier, Bridgestone has experienced significant
financial stress, and the Executive Director, Thomas, was
inquisitive about how to stabilize financial operations above a
break-even level. As well, he would like to gain the ability to
measure and monitor the Center’s continuing performance.
Faced with the lack of internal personnel who could help
with this issue, Thomas contacted Service Consulting Plus,
LLC, a local consulting firm with a reputation for working
with nonprofit organizations. The firm is owned and operated
by Sheryl Marshall, who is also an accounting professor
at Cleveland State University. Her firm specializes in the
management of governmental and nonprofit organizations.
At the end of their initial meeting to discuss the situation at
Bridgestone, Thomas stressed that he didn’t simply want to hire
Sheryl to get the job done for the current year, but rather that
he wanted to learn how to measure and monitor the financial
performance of Bridgestone on an ongoing basis. With this in
mind, Sheryl explained that Cost-Volume-Profit (CVP) analysis
would be useful to Thomas after he gathered some basic
information about costs, revenues, and units of service. She
explained that CVP is a fundamental management accounting
tool that is useful for planning, control, and decision making.
It explores important relationships between costs, volume or
activity levels, and profit.
Sheryl asked Thomas to provide her with Bridgestone’s
annual budgetary income statement in a contribution margin
format. She explained that this format is the basic foundation
for CVP analysis since it presents revenues and expenses
based on their variable or fixed behavior in relation to service
activity (namely, volume of services). Sheryl’s understanding
of Bridgestone’s operating environment and the contribution
margin income statement will enable to provide suggestions
regarding how to increase profitability. The three key
suggestions include a focus on maintaining targeted service
volume, reducing discretionary fixed costs, and offering off-
campus programs to companies and nonprofits.
SERVICE VOLUME Sheryl’s prior experience consulting for health centers that
rely on Medicaid reimbursement for revenue has taught
her the importance of achieving projected levels of service
volume in order to maintain profit targets. Missing the
projected levels of service will cause centers to pass up
potential revenue and contribution margin. Service levels
underscore the importance of monitoring the volume of
delivered service and to ensure that it doesn’t fall behind
the planned volume when Medicaid is involved. Thus, it’s
I M A E D U C AT I O N A L C A S E J O U R N A L V O L . 3 , N O . 4 , A R T. 1 , D E C E M B E R 2 0 1 03
I M A E D U C AT I O N A L C A S E J O U R N A L V O L . 3 , N O . 4 , A R T. 1 , D E C E M B E R 2 0 1 04
Ex hi
bi t 1
: B
ri dg
es to
ne B
eh av
io ra
l H ea
lt h
an d
A dd
ic ti
on C
en te
r N
ex t Y
ea r’s
A nn
ua l B
ud ge
t
C
as e
G ro
up
In di
vi du
al
C ri
si s
In te
ns iv
e M
ed ic
al
M et
ha do
ne
A m
bu l
A ss
es sm
en t
U ri
na ly
si s
M
an ag
em en
t C
ou ns
el in
g
C ou
ns el
in g
In
te rv
en ti
on
O ut
pa ti
en t
S om
at ic
M
ai nt
. D
et ox
To
ta l
R ev
en ue
M ed
ic ai
d
R at
e $
4
0 $
1
00
$
8 0
$
3 0
$
90
$
1 40
$
1 10
$
2 00
$
15
$
2 60
U ni
ts o
f S er
vi ce
4,
00 0
11 ,5
50
6, 00
0 28
,0 00
12
,0 00
1,
50 0
2, 00
0 80
0 8,
00 0
1, 30
0
R ev
en ue
$
1 60
,0 00
$
1, 15
5, 00
0 $
4 80
,0 00
$
8 40
,0 00
$
1, 08
0, 00
0 $
2 10
,0 00
$
2 20
,0 00
$
1 60
,0 00
$
1 20
,0 00
$
3 38
,0 00
$
4, 76
3, 00
0
S ta
te
– –
– –
– –
3,
00 0
– –
20
5, 00
0
20 8,
00 0
P ri
va te
P ay
10
,0 00
50
0
–
5, 00
0 10
,0 00
–
3,
50 0
–
–
–
2
9, 00
0
To ta
l $
1 70
,0 00
$
1, 15
5, 50
0 $
4 80
,0 00
$
8 45
,0 00
$
1, 09
0, 00
0 $
2 10
,0 00
$
2 26
,5 00
$
1 60
,0 00
$
1 20
,0 00
$
5 43
,0 00
$
5, 00
0, 00
0
Va ri
ab le
C os
ts
M ed
ic at
io ns
/T es
ts
$
30 ,0
00
$
40 4,
25 0
$
1 40
,0 00
$
12 0,
00 0
$
3 0,
00 0
$ 2
00 ,0
00
$ 2
00 ,0
00
$
96
,0 00
$ 26
0, 00
0 1,
48 0,
25 0
O th
er
2, 50
0 2,
00 0
$
1 0,
00 0
2
,0 00
2,
00 0
1,
00 0
25 0
– –
–
19 ,7
50
To ta
l $
32
,5 00
$
40
6, 25
0 $
10
,0 00
$
1 42
,0 00
$
12 2,
00 0
$
3 1,
00 0
$ 2
00 ,2
50
$ 2
00 ,0
00
$
96
,0 00
$ 26
0, 00
0 $
1, 50
0, 00
0
C on
tr ib
ut io
n M
ar gi
n
$ 1
37 ,5
00
$
74 9,
25 0
$ 4
70 ,0
00
$ 7
03 ,0
00
$
96 8,
00 0
$ 1
79 ,0
00
$
2 6,
25 0
$ (4
0, 00
0)
$ 2
4, 00
0 $
28 3,
00 0
$ 3,
50 0,
00 0
Fi xe
d Ex
pe ns
es
S al
ar ie
s &
B en
efi ts
1,
82 7,
60 7
C on
su lti
ng F
ee s
17 4,
68 5
R en
t
41
9, 24
4
Tr an
sp or
ta tio
n
10 4,
81 1
O ffi
ce s
up pl
ie s
69 ,8
74
D ep
re ci
at io
n
87
3, 42
4
O th
er
52 4,
05 5
To ta
l F ix
ed E
xp en
se
$ 3,
49 3,
70 0
I M A E D U C AT I O N A L C A S E J O U R N A L V O L . 3 , N O . 4 , A R T. 1 , D E C E M B E R 2 0 1 05
Ex hi
bi t 2
: B
ri dg
es to
ne B
eh av
io ra
l H ea
lt h
an d
A dd
ic ti
on C
en te
r Q
ua rt
er 1
N ex
t Y ea
r’s B
ud ge
t
C
as e
G r
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.