Data Finance
This assessment has three-parts. Click each of the items below to complete this assessment.
Part I: Analyze Organizational Financial Data
You are an administrator at Arizona Health Services (AHS), a large hospital system. You have been asked to assist in evaluating the financial feasibility of purchasing Jiranna Healthcare, a managed care organization in the San Jose area. For this part of the Performance Task, you will conduct a 5-year analysis of Jiranna Healthcares operational and financial data in order to determine whether or not it is an attractive acquisition. Refer to Jiranna Finances for financial statements to analyze.
Prepare a 2- to 3-page financial analysis. The focus must be on the content and the depth of your analysis. Unless otherwise indicated, a 5-year trend analysisincluding the most recent year of available datais expected.
Complete your analysis as follows:
Calculate the 5-year net sales, operating expenses, operating income, and net income of Jiranna Healthcare. Once calculations are complete, interpret the resulting data and explain the significance of the trend results.
Calculate the 5-year total profit margin, asset turnover, return on assets, and return on net worth. Once calculations are complete, interpret the resulting data and determine the companys profitability.
Calculate the 5-year current ratio, days cash on hand, and working capital. Once calculations are complete, interpret the resulting data and assess the companys liquidity.
Calculate the 5-year debt ratio and times interest earned ratio. Once calculations are complete, interpret the resulting data and explain the companys long term solvency.
Complete a DuPont analysis for each of the five most recent years. Once calculations are complete, interpret the resulting data and determine the companys individual DuPont characteristics (e.g., total margin, total asset turnover & equity multiplier) and trends across the analysis period.
Ultimately a decision has to be made. Would you recommend purchasing Jiranna Healthcare? Why?
Part II: Case Study #2
For the next part of this Performance Task, you are expected to complete a number of calculations, and then interpret the numbers to provide recommendations based on these analyses. It is essential both to show your work and calculations, and to demonstrate how these calculations support your conclusions. Be sure to explain your reasoning. You will be assessed on the accuracy of your quantitative analyses and the quality of the evidence you use to support your conclusions.
Imagine that you are an administrator at Jiranna Healthcare who has been asked to analyze cash-flow data to determine the costs and benefits of implementing a new capital project. For background, read Capital Project Case Study, Part 1. Then, complete the Capital Project Case Study, Part 2 spreadsheet, which provides cash-flow data (costs and benefits) for the proposal. Download and save the Excel spreadsheet, and use the information provided to complete the following:
Calculate the cash inflows and outflows for each year.
Calculate the following metrics:
Net present value (NPV)
Internal rate of return (IRR)
Modified internal rate of return (MIRR)
Payback period
Discounted payback period
In a 1- to 2-page report, provide your recommendation with rationale, as to whether the project is acceptable, assuming Jiranna has a corporate policy of not accepting projects that take more than 3.5 years to pay for themselves, and assuming an 11% cost of capital
Part III: Conduct a Budgeting Evaluation
You are an administrator at Jiranna Healthcare. You have been asked to conduct a budget variance analysis: analyzing performance by comparing budgeted workload, revenue, and expenses for a range of different service lines, with actual workload, revenue, and expenses for those service lines.
Open the document Variance Analysis Case Study, where you will find data on the expenses, revenue, and inpatient product lines at Jiranna Healthcare.
Analyze the data and prepare a 3- to 4-page report. In your report, address each of the five following questions. In each case, show the calculations that you conducted to answer the question. Then, explain your conclusion and how your calculations support your reasoning.
What was the hospital’s original profit forecast (assume away any issues with depreciation, taxes, etc.)? Halfway through the fiscal year, what is the hospital’s revised projection for FY11 profits?
Which inpatient service lines are over budget? Which inpatient service lines are over budget after accounting for workload increases?
What two actions would you recommend be taken at the mid-year point if you oversaw a fee-for-service hospital? In other words, where are the problem areas on which you would focus your attention, and who might provide ideas for “best practices” based on their performance?
What two actions would you recommend be taken at the mid-year point if you oversaw a capitated hospital? In this case, the revenue spreadsheet would be replaced with an overall budget of $50 million with which to operate (rather than being able to bill for each episode of patient care). Federal, state, county, and city hospitals normally operate under a capped budget. Additionally, many HMOs also operate under a fixed per member, per month (PMPM) capitated process.
Edit question’s body
FM007_Assessment_VarianceAnalysisCaseStudy.pdf
FM007_Assessment_JirannaFinances.xlsx
FM007_Assessment_CapitalProjectCaseStudyPart2.xlsx
Assessment_Writing_Checklist8.docx
FM007_Assessment_JirannaFinances.xlsx
Edit question’s attachments
Capital Project Case Study, Part 1
This case study considers the expected costs and benefits to a managed care organization resulting from a decision to design a centralized nurse triage line. This triage line would assist routine primary care patients in providing self-care and/or with seeking care at an urgent care facility in lieu of more expensive after-hours care in the emergency room.
Summary
Jiranna Healthcare owns and operates a 268-bed hospital in the San Jose area. The hospital is Jiranna Healthcares main facility and is home to more than 80 onsite specialty and surgery clinics, employing over 5,000 staff. In addition to the main hospital, Jiranna Healthcare has 18 satellite clinics, which can contain primary care services, such as pediatrics, family medicine, and geriatric health. These facilities (the hospital plus outlying clinics) serve a total enrollee population of 97,000.
Currently, Jiranna Healthcares centralized call center schedules primary care appointments, handling an average of 1,500 to 2,000 calls daily with a staff of 20. Patients routinely have difficulty obtaining urgent or acute care (primary care) in a timely fashion. Additionally, in three out of four cases, the majority of Jiranna Healthcares primary care centers are unable to meet access standards. These access issues have a secondary effect on the call center, which experiences a much higher call rate because members have to call back multiple times to find available appointments. The existing process leads to overutilization of emergency departments for urgent care and primary care concerns. In addition, patient satisfaction has steadily declined as a result of the continued lack of appointment availability.
To address this problem, there is a proposal to implement a centralized nurse triage line, an offsite phone center that would be staffed by registered nurses with a multitude of specialties, including emergency room (ER) nurses, critical care, surgical, and even some nurse practitioners. These nurses are able to offer callers medical advice encompassing the treatment of fevers, wound care, and emergent conditions, such as chest pain. The nurses are trained to triage conditions to the appropriate level of care, be that at home, at an urgent care center, or at an emergency department.
The major cost impact is the increased salary requirement for the phone center staff, which will entail approximately 33 multi-discipline employees, based on workload and enrollment data. Additional elements of the proposal include hiring an information technology (IT) specialist to manage the triage lines computer system and facility renovations. The main benefit of this proposal is the projected cost reductions in patient care as a result of moving primary care out of the expensive emergency-room setting.
Assignment
The Capital Project Case Study, Part 2 spreadsheet provides cash flow data (costs and benefits) for the proposal. Download and save this Excel spreadsheet, and use the information provided to complete the following:
1. Determine the cash inflows and outflows for each year.
2. Evaluate the capital project by calculating the following metrics:
a. Net present value (NPV)
b. Internal rate of return (IRR)
c. Modified internal rate of return (MIRR)
d. Payback period
e. Discounted payback period
3. Indicate whether the project is acceptable, assuming Jiranna has a corporate policy of not accepting projects that take more than 3.5 years to pay for themselves, and assuming an 11% cost of capital.
2015 Walden University 2 Sheet1
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5
Nurse Triage Salaries $ 523,800 $ 549,990 $ 577,490 $ 606,364 $ 636,682 $ 668,516
Forecasted ER Cost Reductions $ 400,000 $ 800,000 $ 848,000 $ 900,577 $ 955,512 $ 1,013,798
New IT Specialist’s Salary $ 150,000 $ 154,500 $ 159,135 $ 163,909 $ 168,826 $ 173,891
Costs of Facility Renovations $ 30,000 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0
Necessary Capital Equipment Purchases $ 117,000 $ 3,510 $ 3,510 $ 3,510 $ 3,510 $ 3,510
Net Cash Flow:
Present Values of Net Cash Flows:
Net Present Value:
Internal Rate of Return (IRR):
Modified Internal Rate of Return (MIRR):
Payback Period (# years):
Discounted Payback Period (# years): Academic Writing Expectations Checklist
The faculty Assessor will use this checklist to evaluate whether your written responses adhere to the conventions of scholarly writing. Review this checklist prior to submitting your Assessment to ensure your writing follows academic writing expectations. Click the links to access Writing Center resources:
Sentence-Level Skills
|_| Constructing complete and correct sentences
Note: See an explanation of sentence components and how to avoid sentence fragments and run-ons.
|_| Using and spelling words correctly
Note: See a list of commonly misused words and information on MS Words spell check.
|_| Using punctuation appropriately
Note: See the different types of punctuation and their uses.
|_| Using grammar appropriately
Note: See a Grammarly tutorial to catch further errors.
Paragraph-Level Skills
|_| Using paragraph breaks
Note: See a description of paragraph basics.
|_| Focusing each paragraph on one central idea (rather than multiple ideas)
Note: See an explanation of how topic sentences work.
Use of Evidence
|_| Using resources appropriately
Note: See examples of integrating evidence in a paper.
|_| Citing and referencing resources accurately
Note: See examples of citing and referencing resources in a paper.
|_| Paraphrasing (explaining in ones own words) to avoid plagiarizing the source
Note: See paraphrasing strategies.
Formatting Written Assignments
|_| Using appropriate APA formatting, including title page, margins, and font
Note: See APA overview and APA template from the Writing Center.
Comments:
2014 Walden University 1 Balance Sheet
Jiranna Healthcare Balance Sheet December 31, 2013 (in thousands)
2009 2010 2011 2012 2013
Current assets $ 280 $ 124 $ 136 $ 295 $ 355
Cash 30 45 50 75 88
Receivables, net 1,340 1,536 1,700 1,896 2,400
Inventory 140 175 250 276 266
Prepaid expenses 40 32 40 53 78
Total Current Assets 1,830 1,912 2,176 2,595 3,187
Long-term investments 600 1,010 1,200 1,300 1,510
Plant and equipment 6,580 6,780 6,900 7,200 7,500
Less accumulated depreciation -1,660 -1750 -1,800 -1,950 -2,350
Plants and equipment, net 4,920 5,030 5,100 5,250 5,150
Total Assets $ 7,350 $ 7,952 $ 8,476 $ 9,145 $ 9,847
Current liabilities
Accounts payable 370 302 356 370 375
Accrued expenses payable 220 208 212 210 215
Deferred revenues 60 77 87 87 94
Total current liabilities 650 587 655 667 684
Long-term liabilities 2,400 3,000 3,300 3,500 3,750
Total Liabilities 3,050 3,587 3,955 4,167 4,434
Net assets
Unrestricted 3,000 3,285 3,221 3,678 4,113
Temporarily restricted 700 700 700 700 700
Permanently restricted 600 600 600 600 600
Total Net Assets 4,300 4,585 4,521 4,978 5,413
Total Liabilities and Net Assets 7,350 8,172 8,476 9,145 9,847
Income Statement
Jiranna Healthcare Income Statement December 31, 2013 (in thousands)
2009 2010 2011 2012 2013
Gross patient services revenues (non-GAAP) 8,870 9,490 10,400 11,200 12,050
Less deductions from revenues (non-GAAP) (780) (890) (1,000) (1,500) (1,600)
Net patient service revenues 8,090 8,600 9,400 9,700 10,450
Other operating revenues 519 633 679 717 980
Total operating revenues 8,609 9,233 10,079 10,417 11,430
Operating expenses
Salaries and wages 5,497 5,678 5,890 6,170 6,800
Supplies 823 850 855 890 905
Utilities 558 576 590 595 620
Insurance 44 46 49 54 70
Depreciation 168 173 175 178 188
Interest 142 146 154 179 198
Bad debts 363 375 400 455 500
Other operating expenses 987 1,299 1,560 1,300 1,350
Total operating expenses 8,582 9,143 9,673 9,821 10,631
Operating income 27 90 406 596 799
Nonoperating income 154 195 245 220 290
Excess of revenue over expenses $ 181 $ 285 $ 651 $ 816 $ 1,089
Change in net assets
Unrestricted $ 181 $ 285 $ 651 $ 816 $ 1,089
Temporarily restricted – – – – –
Permanently restricted – – – – –
Total change in net assets 181 285 651 816 1,089
Cash Flows
Jiranna Healthcare Cash Flows, 2013 (in thousands)
Cash Flows from Operating Activities
Cash received from patient and third-party payers $ 10,671
Cash received from operating revenue sources 800
Cash received from nonoperating revenue sources 270
Cash payments to employees (5,600)
Cash payments to suppliers of goods and services (4,800)
Net cash flow from operating activities $ 1,341
Cash flows from Investing Activities
Cash payments for purchase of plant assets (1,200)
Cash payments for purchase of long-term investments (670)
Proceeds from sales of plant assets 80
Proceeds from sale of long-term investments 60
Net cash flow from investing activities (1,730)
Cash flows from Financing Activities
Proceeds from issuance of 6% bonds payable 4,000
Principal payments on long-term debt (300)
Cash payments to retire 7% bonds payable (3,200)
Net cash flow from financing activities 500
Net increase/(decrease) in cash $ 111 Inpatient
Inpatient Product Line Budget(FY10)
Actual
(FY10)
IP Expenses
(FY10)
Forecasted
(FY11)
FY11 Budget
(FY10+6% Inflation) Actual (FY11)
Actual IP Expenses
(FY11 YTD)
CIRC 5,475.81$ 690 3,778,309$ 724 4,202,356$ 421 2,472,822$
DIGEST 5,476.81$ 1,105 6,051,875$ 1,161 6,737,188$ 636 4,356,173$
ENT 5,477.81$ 124 679,248$ 130 754,842$ 72 462,822$
GYN 5,478.81$ 208 1,139,592$ 219 1,268,947$ 116 777,322$
MENTAL HEALTH 498.78$ 1,287 641,930$ 1,351 714,283$ 736 400,730$
NERVOUS 5,478.81$ 217 1,188,902$ 228 1,324,119$ 112 675,173$
NEWBORN 5,478.81$ 698 3,824,209$ 733 4,256,926$ 433 2,971,020$
OB 5,478.81$ 1,430 7,834,698$ 1,501 8,717,115$ 868 5,472,892$
ORTHO 5,478.81$ 601 3,292,765$ 631 3,664,557$ 274 1,771,063$
OTHER 5,478.81$ 2,050 11,231,561$ 2,152 12,497,823$ 800 4,662,277$
RESP 5,478.81$ 645 3,533,832$ 677 3,931,704$ 197 1,233,771$
Total Inpatient Expenses 43,196,922$ 9,506 48,069,860$ 4,664 25,256,062$
Jiranna Healthcare
Expenses Worksheet, Mid-Year Review
Inpatient
Inpatient Product Line ForecastedFor FY11
Projected IP Revenue
For FY11
Actual
(FY11 YTD)
Actual IP Revenue
(FY11 YTD)
CIRC 724 4,351,711$ 421 2,530,676$
DIGEST 1,161 6,988,125$ 636 3,830,402$
ENT 130 780,215$ 72 433,948$
GYN 219 1,313,983$ 116 697,128$
MENTAL HEALTH 1,351 679,539$ 736 369,949$
NERVOUS 228 1,391,632$ 112 682,744$
NEWBORN 733 5,141,782$ 433 3,033,900$
OB 1,501 8,576,189$ 868 4,960,622$
ORTHO 631 3,839,118$ 274 1,664,658$
OTHER 2,152 12,951,037$ 800 4,813,961$
RESP 677 4,142,380$ 197 1,202,960$
Total Inpatient Earnings 9,506 50,155,710$ 4,664 24,220,949$
Jiranna Healthcare
Revenues Worksheet, Mid-Year Review
Price
6,010.65$
6,021.65$
6,001.65$
6,013.65$
502.99$
6,103.65$
7,014.71$
5,713.65$
6,084.18$
6,018.14$
6,118.73$
Product Line MDC Description
CIRC Diseases and Disorders of the Circulatory System
DIGEST Diseases and Disorders of the Digestive System
ENT Diseases and Disorders of the Ear, Nose, Mouth, and Throat
GYN Diseases and Disorders of the Female Reproductive System
MH Mental Diseases and Disorders
MH Alcohol/Drug Use and Alcohol/Drug Induced Organic Mental Disorders
NERVOUS Diseases and Disorders of the Nervous System
NEWBORN Newborns and Other Neonates with Conditions Originating in Perinatal Period
OB Pregnancy, Childbirth, and the Puerperium
ORTHO Diseases and Disorders of the Musculoskeletal System and Connective Tissue
OTHER Unknown
OTHER Diseases and Disorders of the Eye
OTHER Diseases and Disorders of the Hepatobiliary System and Pancreas
OTHER Diseases and Disorders of the Skin, Subcutaneous Tissue and Breast
OTHER Endocrine, Nutritional and Metabolic Diseases and Disorders
OTHER Diseases and Disorders of the Kidney and Urinary Tract
OTHER Diseases and Disorders of the Male Reproductive System
OTHER Diseases and Disorders of the Blood, Blood Forming Organs, Immunological Dis
OTHER Myeloproliferative Diseases and Disorders, Poorly Differentiated Neoplasm
OTHER Infectious and Parasitic Diseases, Systemic or Unspecified Sites
OTHER Injuries, Poisonings and Toxic Effects of Drugs
OTHER Burns
OTHER Factors Influencing Health Status and Other Contacts with Health Services
OTHER Multiple Significant Trauma
OTHER Human Immunodeficiency Virus Infections
RESP Diseases and Disorders of the Respiratory System Inpatient
Inpatient Product Line Budget(FY10)
Actual
(FY10)
IP Expenses
(FY10)
Forecasted
(FY11)
FY11 Budget
(FY10+6% Inflation) Actual (FY11)
Actual IP Expenses
(FY11 YTD)
CIRC 5,475.81$ 690 3,778,309$ 724 4,202,356$ 421 2,472,822$
DIGEST 5,476.81$ 1,105 6,051,875$ 1,161 6,737,188$ 636 4,356,173$
ENT 5,477.81$ 124 679,248$ 130 754,842$ 72 462,822$
GYN 5,478.81$ 208 1,139,592$ 219 1,268,947$ 116 777,322$
MENTAL HEALTH 498.78$ 1,287 641,930$ 1,351 714,283$ 736 400,730$
NERVOUS 5,478.81$ 217 1,188,902$ 228 1,324,119$ 112 675,173$
NEWBORN 5,478.81$ 698 3,824,209$ 733 4,256,926$ 433 2,971,020$
OB 5,478.81$ 1,430 7,834,698$ 1,501 8,717,115$ 868 5,472,892$
ORTHO 5,478.81$ 601 3,292,765$ 631 3,664,557$ 274 1,771,063$
OTHER 5,478.81$ 2,050 11,231,561$ 2,152 12,497,823$ 800 4,662,277$
RESP 5,478.81$ 645 3,533,832$ 677 3,931,704$ 197 1,233,771$
Total Inpatient Expenses 43,196,922$ 9,506 48,069,860$ 4,664 25,256,062$
Jiranna Healthcare
Expenses Worksheet, Mid-Year Review
Inpatient
Inpatient Product Line ForecastedFor FY11
Projected IP Revenue
For FY11
Actual
(FY11 YTD)
Actual IP Revenue
(FY11 YTD)
CIRC 724 4,351,711$ 421 2,530,676$
DIGEST 1,161 6,988,125$ 636 3,830,402$
ENT 130 780,215$ 72 433,948$
GYN 219 1,313,983$ 116 697,128$
MENTAL HEALTH 1,351 679,539$ 736 369,949$
NERVOUS 228 1,391,632$ 112 682,744$
NEWBORN 733 5,141,782$ 433 3,033,900$
OB 1,501 8,576,189$ 868 4,960,622$
ORTHO 631 3,839,118$ 274 1,664,658$
OTHER 2,152 12,951,037$ 800 4,813,961$
RESP 677 4,142,380$ 197 1,202,960$
Total Inpatient Earnings 9,506 50,155,710$ 4,664 24,220,949$
Jiranna Healthcare
Revenues Worksheet, Mid-Year Review
Price
6,010.65$
6,021.65$
6,001.65$
6,013.65$
502.99$
6,103.65$
7,014.71$
5,713.65$
6,084.18$
6,018.14$
6,118.73$
Product Line MDC Description
CIRC Diseases and Disorders of the Circulatory System
DIGEST Diseases and Disorders of the Digestive System
ENT Diseases and Disorders of the Ear, Nose, Mouth, and Throat
GYN Diseases and Disorders of the Female Reproductive System
MH Mental Diseases and Disorders
MH Alcohol/Drug Use and Alcohol/Drug Induced Organic Mental Disorders
NERVOUS Diseases and Disorders of the Nervous System
NEWBORN Newborns and Other Neonates with Conditions Originating in Perinatal Period
OB Pregnancy, Childbirth, and the Puerperium
ORTHO Diseases and Disorders of the Musculoskeletal System and Connective Tissue
OTHER Unknown
OTHER Diseases and Disorders of the Eye
OTHER Diseases and Disorders of the Hepatobiliary System and Pancreas
OTHER Diseases and Disorders of the Skin, Subcutaneous Tissue and Breast
OTHER Endocrine, Nutritional and Metabolic Diseases and Disorders
OTHER Diseases and Disorders of the Kidney and Urinary Tract
OTHER Diseases and Disorders of the Male Reproductive System
OTHER Diseases and Disorders of the Blood, Blood Forming Organs, Immunological Dis
OTHER Myeloproliferative Diseases and Disorders, Poorly Differentiated Neoplasm
OTHER Infectious and Parasitic Diseases, Systemic or Unspecified Sites
OTHER Injuries, Poisonings and Toxic Effects of Drugs
OTHER Burns
OTHER Factors Influencing Health Status and Other Contacts with Health Services
OTHER Multiple Significant Trauma
OTHER Human Immunodeficiency Virus Infections
RESP Diseases and Disorders of the Respiratory System
