Chat with us, powered by LiveChat 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 - Essayabode

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

Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.

Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.

Do you need an answer to this or any other questions?