Analyzing Financial Data in Excel

In this Assessment, you will use information from several case studies and other documents to demonstrate your ability to make recommendations for organizational decisions based on analyses of financial data.

This Assessment considers a Capital Proposal Case Study to add a specialized nurse triage center, an offsite center that would be staffed by registered nurses with a multitude of specialties, including emergency department (ED) 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 the ED.

This Assessment considers a Capital Proposal Case Study to add a specialized nurse triage
center, an offsite center that would be staffed by registered nurses with a multitude of specialties,
including emergency department (ED) 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 the ED.
This assessment has three-parts. Click each of the items below to complete this assessment.
Part I: Analyze Organizational Financial Data
For this part of the Final Assessment, you will conduct a 5-year analysis of Jiranna Healthcare’s
outpatient operational and financial data in order to determine whether or not the capital project
is an attractive project. Refer to “Jiranna Finances” for financial statements to analyze.
Prepare a 2- to 3-page financial analysis including a supporting Excel spreadsheet to show your
work. The focus must be on the content and the depth of your analysis. Unless otherwise
indicated, a 5-year trend analysis is expected.
Complete your analysis as follows:
Calculate the 5-year net sales, operating expenses, operating income, and net income of Jiranna
Healthcare. Once the 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 the calculations are complete, interpret the resulting data and determine the company’s
profitability.
Calculate the 5-year current ratio, day’s cash on hand, and working capital. Once the calculations
are complete, interpret the resulting data and assess the company’s liquidity.
Calculate the 5-year debt ratio and times interest earned ratio. Once the calculations are
complete, interpret the resulting data and explain the company’s 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 company’s 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 the capital project to Jiranna
Healthcare? Explain the rationale for your decision.
Part II: Conduct an Operating Indicator Evaluation
For the next part of the Final Assessment, 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 calculation using current technology like Excel Workbook,
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 cashflow data to determine the costs and benefits of implementing a new capital project. For
background, read “Capital Project Case Study.” Then, complete the “Capital Project Case Study
Spreadsheet”, which provides cash-flow data (costs and benefits) for the proposal. Download and
save the Excel spreadsheet in your working Excel Workbook, and use the information provided
to complete the following:
Calculate the cash inflows and outflows for each year.
Evaluate the capital project by calculating 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 including a supporting Excel spreadsheet to show your work, provide
your recommendation with rationale, as to whether the project is acceptable, assuming Jiranna
Healthcare 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: Conducting 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 outpatient product lines at Jiranna Healthcare.
Analyze the data and prepare a 3- to 4-page report including a supporting Excel spreadsheet to
show your work. 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 Jiranna’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 FY21 profits?
Which outpatient service lines are over budget? Which outpatient 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-forservice healthcare organization? 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 healthcare organization? 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 healthcare organization normally operate
under a capped budget. Additionally, many HMOs also operate under a fixed per member, per
month (PMPM) capitated process.
FM007: Analyze Financial Data: Make recommendations for organizational decisions based on analyses of
financial data.
Assessment Rubric
Rubric Criteria
Needs Improvement
Meets Expectations
Exceeds Expectations
Analysis accurately calculates
the 5-year net sales,
operating expenses,
operating income, and net
income.
Analysis demonstrates the
same level of achievement
as “Meets,” plus the
following:
Part 1: Analyze Organizational Financial Data
Learning Objective
1.1:
Analysis does not include or
includes an incomplete or
inaccurate calculation of the 5Calculate and interpret year net sales, operating
net sales, operating
expenses, operating income, and
expenses, operating
net income.
income, and net
income data to
Analysis does not include or
determine significance includes an inaccurate, unclear,
of trend result.
or incomplete interpretation of the
data and/or explanation of the
significance of the trend result.
Learning Objective
Analysis does not include or
1.2:
includes an incomplete or
Calculate and interpret inaccurate calculation of the 5profit margin, asset
year total profit margin, asset
turnover, return on
turnover, return on assets, and/or
assets, and return on
return on net worth.
net worth data to
assess a company’s
Analysis does not include or
profitability.
includes an inaccurate, unclear,
or incomplete interpretation of the
data and/or explanation of the
company’s profitability.
© 2024 Walden University, LLC
Analysis accurately interprets
the data and effectively uses
it to explain the significance of
the trend result.
Analysis accurately calculates
the 5-year total profit margin,
asset turnover, return on
assets, and return on net
worth.
Analysis accurately interprets
the data and uses it to assess
the company’s profitability.
Analysis includes an
explanation of the
significance of the trend
results including detailed
examples or illustrative
graphics.
Analysis demonstrates the
same level of achievement
as “Meets,” plus the
following:
Analysis provides
additional context for
these calculations by
comparing them to other
healthcare organizations
or figures for the industry.
1
Rubric Criteria
Learning Objective
1.3:
Calculate and interpret
current ratio, day’s
cash on hand, and
working capital data to
evaluate a company’s
liquidity.
Needs Improvement
Analysis does not include or
includes an inaccurate or
incomplete calculation of the 5year current ratio, day’s cash on
hand, and/or working capital.
Learning Objective
1.4:
Calculate debt ratio
and times interest
earned ratio to
evaluate a company’s
long-term solvency.
Analysis does not include or
includes an inaccurate or
incomplete calculation of the 5year debt ratio and times interest
earned ratio.
Analysis does not include or
includes an inaccurate or unclear
interpretation of calculations to
evaluate the company’s liquidity.
Analysis does not include or
includes an inaccurate or
incomplete interpretation of
calculations to evaluate the
company’s long-term solvency.
Learning Objective
Analysis does not include or
1.5:
includes an inaccurate DuPont
Conduct a DuPont
analysis for each of the five years
analysis to determine
or the analysis is only provided
the organization’s
for some of the five most recent
DuPont characteristics years.
and trends across an
analysis period.
© 2024 Walden University, LLC
Meets Expectations
Analysis accurately calculates
the 5-year current ratio, day’s
cash on hand, and working
capital.
Exceeds Expectations
Analysis demonstrates the
same level of achievement
as “Meets,” plus the
following:
Analysis accurately and
clearly interprets the data in
order to explain the
company’s liquidity.
Analysis provides
additional context for
these calculations by
comparing them to other
healthcare organizations
or figures for the industry.
Analysis accurately calculates Analysis demonstrates the
the 5-year debt ratio and
same level of achievement
times interest earned ratio.
as “Meets,” plus the
following:
Analysis accurately and
clearly interprets the data in
Analysis provides
order to explain the
additional context for
company’s long-term
these calculations, by
solvency.
comparing them to other
healthcare organizations
or figures for the industry.
Analysis accurately applies a Analysis demonstrates the
DuPont analysis for each of
same level of achievement
the five years.
as “Meets,” plus the
following:
Analysis accurately and
clearly interprets the
Analysis provides
company’s individual DuPont additional context for
characteristics and trends.
these calculations, by
2
Rubric Criteria
Learning Objective
1.6:
Make
recommendations for
management
decisions based on
evaluation of financial
information.
Needs Improvement
Analysis does not include or
includes an inaccurate or unclear
interpretation of the company’s
DuPont analysis characteristics
and trends.
Analysis does not include or
includes an unclear
recommendation for or against
the proposal to add a centralized
nurse triage line of business.
Analysis does not include or
includes an unclear or incomplete
rationale supporting the
recommendation for or against
the purchase or the data cited in
the rationale is irrelevant or
unclear.
Meets Expectations
Exceeds Expectations
comparing them to other
healthcare organizations
or figures for the industry.
Analysis includes a
recommendation for or
against the proposal to add a
centralized nurse triage line of
business.
Analysis demonstrates the
same level of achievement
as “Meets,” plus the
following:
Analysis includes clear and
logical rationale supporting
the recommendation and data
is used to support the
recommendation.
Rubric Criteria
Needs Improvement
Meets Expectations
Part 2: Conducting an Operating Indicator Evaluation
Learning
Report does not include or
Report accurately calculates the
Objective 2.1:
includes an inaccurate or
cash inflows and outflows for
Apply mathematical incomplete calculation of cash
each year in the case
principles to
inflows and cash outflows or
presented.
accurately calculate response is not relevant to the
cash inflows and
case presented.
outflows.
© 2024 Walden University, LLC
Analysis provides
additional rationale for this
recommendation by
comparing this company’s
data to industry
benchmarks.
Exceeds Expectations
Report demonstrates the
same level of achievement
as “Meets,” plus the
following:
Report provides additional
context for these
calculations, by
3
Learning
Objective 2.2:
Apply strategies to
accurately calculate
the profitability of a
capital project.
Report does not include or
includes an incomplete or
inaccurate calculation of the NPV,
IRR, MIRR, payback period, and
discounted payback period of a
capital project.
Report accurately calculates the
NPV, IRR, MIRR, payback
period, and discounted payback
period of a capital project.
commenting on how they
compare to other
healthcare organizations
or figures for the industry.
Report demonstrates the
same level of achievement
as “Meets,” plus the
following:
Learning
Objective 2.3:
Make
recommendations
for management
decisions based on
operative indicator
evaluation.
Report does not include or
includes a vague
recommendation as to whether
the project in the case presented
is acceptable.
Report states a clear
recommendation as to whether
the project in the case
presented is acceptable.
Report provides additional
context for these
calculations, by
commenting on how they
compare to other
healthcare organizations
or figures for the industry.
Report demonstrates the
same level of achievement
as “Meets,” plus the
following:
Report does not include a
rationale or does not include
information in the rationale from
calculations or includes
misinformation in the rationale
from calculations, and/or it does
not highlight (or erroneously
highlights) the costs and benefits
of implementing the project,
and/or it does not take into
consideration (or misinterprets)
© 2024 Walden University, LLC
Report provides a clear and
logical rationale for supporting
the recommendation.
Rationale includes information
from calculations to support the
recommendation.
Rationale includes a
compelling argument for
the recommendation
based on the overall
evaluation.
Rationale highlights the costs
and benefits of implementing the
project.
4
the corporate policy for accepting
the capital project.
Rationale takes into
consideration the corporate
policy for accepting the capital
project.
Rubric Criteria
Needs Improvement
Meets Expectations
PS003: Technology: Use technology tools effectively.
Learning
Excel spreadsheet has some
Excel spreadsheet has effective
Objective PS 3.2:
formatting issues and is difficult to formatting, and the data can be
Apply the features
interpret.
easily read and interpreted.
of Excel to
communicate
information
effectively.
Rubric Criteria
Needs Improvement
Part 3: Conduct a Budgeting Evaluation
Learning
Report does not include or
Objective 3.1:
includes incomplete or inaccurate
Calculate Jiranna’s calculations of Jiranna’s original
profit forecast and
profits and related projections, or
related projections. the calculations are not relevant
to the case presented.
Report does not include or
includes an unclear explanation of
the calculations.
Learning
Objective 3.2:
Report does not include or
includes incomplete or inaccurate
© 2024 Walden University, LLC
Exceeds Expectations
Excel spreadsheet
includes format and
design elements that bring
enhanced clarity to the
spreadsheet.
Meets Expectations
Exceeds Expectations
Report includes complete and
accurate calculations of
Jiranna’s original profits and
related projections for the case
presented.
Report demonstrates the
same level of achievement
as “Meets,” plus the
following:
In addition to making a
Report includes clear
correct projection, report
explanations of each calculation. effectively considers
industry standards that
need to be considered in
deciding Jiranna’s profit
projection.
Report includes accurate and
Report demonstrates the
complete calculations of
same level of achievement
5
Identify outpatient
service lines that
are over budget
and those that are
over budget after
accounting for
workload
increases.
Learning
Objective 3.3:
Recommend
actions to be taken
to improve
performance at
Jiranna as a feefor-service
healthcare
organization.
Learning
Objective 3.4:
Recommend
actions to be taken
for Jiranna as a
capitated
healthcare
organization.
calculations of outpatient service
lines that are over budget and/or
outpatient service lines that are
over budget after accounting for
workload increases or
calculations are not relevant to
the case presented.
Report does not include or
includes unclear explanation of
the calculations.
Report does not recommend or
recommends only one act that
should be taken at the mid-year
point for Jiranna as a fee-forservice healthcare organization or
two actions are recommended but
are incomplete or illogical.
Report is not relevant to the case
presented in terms of focusing on
the problem areas and/or
identifying those who might
provide ideas for “best practices”
based on their performance.
Report does not recommend or
recommends only one action that
should be taken at the mid-year
point for Jiranna as a capitated
healthcare organization, or two
actions are identified but are not
logical or related to the case
presented.
© 2024 Walden University, LLC
outpatient service lines that are
over budget and outpatient
service lines that are over
budget after accounting for
workload increases for the case
presented.
as “Meets,” plus the
following:
Report includes a chart or
graph to support the
explanation of the
calculations.
Report provides a clear
explanation for each calculation.
Report clearly details
recommendations for two logical
actions that should be taken at
the mid-year point for Jiranna as
a fee-for-service healthcare
organization.
Report is relevant to the case
presented, focusing on the
problem areas and identifying
those who might provide ideas
for “best practices” based on
their performance.
Report clearly details
recommendations for two logical
actions that should be taken at
the mid-year point for Jiranna as
a capitated healthcare
organization.
Report demonstrates the
same level of achievement
as “Meets,” plus the
following:
Report recommends more
than two logical actions
that should be taken at the
mid-year point for Jiranna
as a fee-for-service
healthcare organization.
Report demonstrates the
same level of achievement
as “Meets,” plus the
following:
Report identifies more
than two clear and logical
actions that should be
6
Actions recommended take into
Actions recommended do not take consideration the expectations
into consideration the
of Jiranna as a capitated
expectations of Jiranna as a
healthcare organization.
capitated healthcare organization.
taken at the mid-year point
for Jiranna as a capitated
healthcare organization.
Rubric Criteria
Needs Improvement
Meets Expectations
PS001: Written Communication: Demonstrate graduate-level writing skills.
Learning
Multiple minor errors in grammar, Writing reflects competent use
Objective PS 1.1:
spelling, and/or mechanics are
of standard edited American
Use proper
distracting and negatively impact
English.
grammar, spelling, readability.
Errors in grammar, spelling,
and mechanics.
and/or mechanics do not
negatively impact readability.
Learning
Writing is loosely organized.
Writing is generally wellObjective PS 1.2:
Limited use of introductions,
organized. Introductions,
Organize writing to transitions, and conclusions
transitions, and conclusions
enhance clarity.
provides partial continuity.
provide continuity and a logical
progression of ideas.
Exceeds Expectations
Learning
Objective PS 1.3:
Apply APA style to
written work.
APA conventions for attribution of
sources, structure, formatting,
etc., are applied inconsistently.
APA conventions for attribution
of sources, structure, formatting,
etc., are generally applied
correctly in most instances.
Sources are generally cited
appropriately and
accurately.
Learning
Objective PS 1.4:
Vocabulary and tone have limited
relevance to the audience.
Vocabulary and tone are
generally appropriate for the
© 2024 Walden University, LLC
Grammar, spelling, and
mechanics reflect a high
level of accuracy in
standard American
English and enhance
readability.
Writing is consistently
well-organized.
Introductions, transitions,
and conclusions are used
effectively to enhance
clarity, cohesion, and
flow.
APA conventions for
attribution of sources,
structure, formatting, etc.,
are applied correctly and
consistently throughout
the paper. Sources are
consistently cited
appropriately and
accurately.
Vocabulary and tone are
consistently tailored to the
7
Use appropriate
vocabulary and
tone for the
audience and
purpose.
© 2024 Walden University, LLC
audience and support
communication of key concepts.
audience and effectively
and directly support
communication of key
concepts.
8
Jiranna Healthcare Income Statement December 31, 2021 (in thousands)
Gross patient services revenues (non-GAAP)
Less deductions from revenues (non-GAAP)
Net patient service revenues
Other operating revenues
Total operating revenues
2017
8,870
(780)
8,090
519
8,609
2018
9,490
(890)
8,600
633
9,233
2019
10,400
(1,000)
9,400
679
10,079
2020
11,200
(1,500)
9,700
717
10,417
Operating expenses
Salaries and wages
Supplies
Utilities
Insurance
Depreciation
Interest
Bad debts
Other operating expenses
Total operating expenses
5,497
823
558
44
168
142
363
987
8,582
5,678
850
576
46
173
146
375
1,299
9,143
5,890
855
590
49
175
154
400
1,560
9,673
6,170
890
595
54
178
179
455
1,300
9,821
Operating income
Nonoperating income
Excess of revenue over expenses
Change in net assets
Unrestricted
Temporarily restricted
Permanently restricted
Total change in net assets
$
$
27
154
181 $
90
195
285 $
406
245
651 $
596
220
816
181 $
181
285 $
285
651 $
651
816
816
2021
12,050
(1,600)
10,450
980
11,430
6,800
905
620
70
188
198
500
1,350
10,631
$
$
799
290
1,089
1,089
1,089
Jiranna Healthcare Balance Sheet December 31, 2021 (in thousands)
Current assets
Cash
Receivables, net
Inventory
Prepaid expenses
Total Current Assets
Long-term investments
Plant and equipment
Less accumulated depreciation
Plants and equipment, net
Total Assets
$
2017
280 $
30
1,340
140
40
1,830
600
6,580
-1,660
2018
124 $
45
1,536
175
32
1,912
2019
136 $
50
1,700
250
40
2,176
2020
295
75
1,896
276
53
2,595
1,010
1,200
1,300
6,780
-1750
6,900
-1,800
7,200
-1,950
4,920
7,350 $
5,030
7,952 $
5,100
8,476 $
5,250
9,145
Current liabilities
Accounts payable
Accrued expenses payable
Deferred revenues
Total current liabilities
Long-term liabilities
Total Liabilities
370
220
60
650
2,400
3,050
302
208
77
587
3,000
3,587
356
212
87
655
3,300
3,955
370
210
87
667
3,500
4,167
Net assets
Unrestricted
Temporarily restricted
Permanently restricted
Total Net Assets
Total Liabilities and Net Assets
3,000
700
600
4,300
7,350
3,285
700
600
4,585
8,172
3,221
700
600
4,521
8,476
3,678
700
600
4,978
9,145
$
$
2021
355
88
2,400
266
78
3,187
1,510
7,500
-2,350
$
5,150
9,847
375
215
94
684
3,750
4,434
4,113
700
600
5,413
9,847
$
8,214
Jiranna Healthcare Cash Flows, 2021 (in thousands)
Cash Flows from Operating Activities
Cash received from patient and third-party payers
Cash received from operating revenue sources
Cash received from nonoperating revenue sources
Cash payments to employees
Cash payments to suppliers of goods and services
Net cash flow from operating activities
Cash flows from Investing Activities
Cash payments for purchase of plant assets
Cash payments for purchase of long-term investments
Proceeds from sales of plant assets
Proceeds from sale of long-term investments
Net cash flow from investing activities
Cash flows from Financing Activities
Proceeds from issuance of 6% bonds payable
Principal payments on long-term debt
Cash payments to retire 7% bonds payable
Net cash flow from financing activities
Net increase/(decrease) in cash
$
10,671
800
270
(5,600)
(4,800)
$
1,341
(1,200)
(670)
80
60
(1,730)
4,000
(300)
(3,200)
$
500
111
Jiranna Financials Per Above
Net Sales
Operating Expenses
Operating Income
Nonoperating income
Net Income
(in thousands)
2017
2018
8609.00
9233.00
8582.00
9143.00
27.00
90.00
154
195
181.00
285.00
2019
10079.00
9673.00
406.00
245
651.00
2020
10417.00
9821.00
596.00
220
816.00
2021
11430.00
10631.00
799.00
290
1089.00
Five year trend Analysis
Operating Expenses
Operating Income
Net Income
14000.00
1200.00
12000.00
1000.00
10000.00
800.00
8000.00
600.00
6000.00
400.00
4000.00
200.00
2000.00
0.00
0.00
2017
2018
2019
Years
2020
2021
Values in 000
values in 000
Net Sales
in
2017
Profit Margin
Net Income/sales
Net Income
Sales
2018
2019
2020
2021
$ 181.00 $ 285.00 $ 651.00 $ 816.00 $ 1,089.00
$ 8,609.00 $ 9,233.00 $ 10,079.00 $ 10,417.00 $ 11,430.00
2.10%
3.09%
6.46%
7.83%
9.53%
Assets Turnover Ratio
2017
2018
2019
2020
2021
Sales/Average Total Assets
Sales
$ 8,609.00 $ 9,233.00 $ 10,079.00 $ 10,417.00 $ 11,430.00
Average Total assets $ 7,350.00 $ 7,651.00 $ 8,214.00 $ 8,810.50 $ 9,496.00
1.17
1.21
1.23
1.18
1.20
2017
2018
2019
2020
2021
Return on assets
Net income/Average total assets
Net income
$ 181.00 $ 285.00 $ 651.00 $ 816.00 $ 1,089.00
Average total assets $ 7,350.00 $ 7,651.00 $ 8,214.00 $ 8,810.50 $ 9,496.00
2.46%
3.73%
7.93%
9.26%
11.47%
2017
2018
2019
2020
2021
Return on Net worth
Net income/ Average Shareholder’s equity
Net income
$ 181.00 $ 285.00 $ 651.00 $ 816.00 $ 1,089.00
Average Sharehodler’s equity
$ 4,300.00 $ 4,442.50 $ 4,553.00 $ 4,749.50 $ 5,195.50
4.21%
6.42%
14.30%
17.18%
20.96%
(values in 000)
(values in 000)
2017
Current ratio
Current assets/current liabilities
Current assets
Current liabilities
2020
2018
2019
2020
$
30.00 $
45.00 $
50.00 $
75.00
$ 8,582.00 $ 9,143.00 $ 9,673.00 $ 9,821.00
$
23.51 $
25.05 $
26.50 $
26.91
1
2
2
3
2017
Working Capital
current assets-current liabilities
current assets
current liabilities
2019
$ 1,830.00 $ 1,912.00 $ 2,176.00 $ 2,595.00
$ 650.00 $ 587.00 $ 655.00 $ 667.00
2.82
3.26
3.32
3.89
2017
Day’s cash on hand
cash/(Total Operating Expenses / 365)
cash
Total operrating expense
Total Operating Expenses / 365
2018
2018
2019
2020
$ 1,830.00 $ 1,912.00 $ 2,176.00 $ 2,595.00
$ 650.00 $ 587.00 $ 655.00 $ 667.00
$ 1,180.00 $ 1,325.00 $ 1,521.00 $ 1,928.00
2021
$ 3,187.00 (values in 000)
$ 684.00
4.66
2021
$
88.00 (Values in 000)
$ 10,631.00
$
29.13
3 (Rounded off to a whole number)
2021
$ 3,187.00
$ 684.00
$ 2,503.00
2017
2018
2019
2020
2021
Debt ratio
Total liabilities/Total assets
Total Debt
$ 3,050.00 $ 3,587.00 $ 3,955.00 $ 4,167.00 $ 4,434.00 (Values in 000)
Total assets
$ 7,350.00 $ 7,952.00 $ 8,476.00 $ 9,145.00 $ 9,847.00
0.41
0.45
0.47
0.46
0.45
2017
2018
2019
2020
2021
Times Interest Earned Ratio
EBIT/Interest expense
EBIT (Operating income +$ interest
169.00expense
$ 236.00 $ 560.00 $ 775.00 $ 997.00
operating income
$ 27.00 $ 90.00 $ 406.00 $ 596.00 $ 799.00
Interest expense
$ 142.00 $ 146.00 $ 154.00 $ 179.00 $ 198.00
1.19
1.62
3.64
4.33
5.04
(Values in 000)
2017
2018
2019
Dupont Analysis (Net profit margin* asset turnover*equitymultiplier)
Total Asset
$ 7,350.00 $ 7,952.00 $ 8,476.00
Total equity
$ 4,300.00 $ 4,585.00 $ 4,521.00
Net income
$
181 $
285 $
651
total revenue
$ 8,609.00 $ 9,233.00 $ 10,079.00
Net Profit Margin (Net Income / Total Revenue)
Asset Turnover (Total Revenue / Total Assets)
Equity Multiplier (Total Assets / Total Equity)
DuPont Analysis
2.10%
1.17
1.71
4.21%
3.09%
1.16
1.73
6.22%
6.46%
1.19
1.87
14.40%
2020
2021
$ 9,145.00
$ 4,978.00
$
816
$ 10,417.00
$ 9,847.00
$ 5,413.00
$
1,089
$ 11,430.00
7.83%
1.14
1.84
16.39%
9.53%
1.16
1.82
20.12%
Year 0
Nurse Triage Salaries
Forecasted ER Cost Reductions
New IT Specialist’s Salary
Costs of Facility Renovations
Necessary Capital Equipment Purchases
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):
$
$
$
$
$
Year 1
523,800
400,000
150,000
30,000
117,000
$
$
$
$
$
549,990
800,000
154,500
3,510
Year 2
$
$
$
$
$
577,490
848,000
159,135
3,510
Year 3
$
$
$
$
$
606,364
900,577
163,909
3,510
Year 4
Year 5
$ 636,682
$ 955,512
$ 168,826
$
$
3,510
$
668,516
$ 1,013,798
$
173,891
$
$
3,510
Year 0
Year 1
Year 2
Year 3
Year 4
Year 5
Nurse Triage Salaries
$523,800.00 $549,990.00 $577,490.00 $606,364.00 $636,682.00 $668,516.00
rate of capital
Forecasted ER Cost Reductions
$400,000.00 $800,000.00 $848,000.00 $900,577.00 $955,512.00 $1,013,798.00
11%
New IT Specialist’s Salary
$150,000.00 $154,500.00 $159,135.00 $163,909.00 $168,826.00 $173,891.00
Costs of Facility Renovations
$30,000.00
$0.00
$0.00
$0.00
$0.00
$0.00
Necessary Capital Equipment Purchases
$117,000.00
$3,510.00
$3,510.00
$3,510.00
$3,510.00
$3,510.00
Net Cash Flow:
-$420,800.00
$92,000.00 $107,865.00 $126,794.00 $146,494.00 $167,881.00
Balance
-$420,800.00 -$328,800.00 -$220,935.00
-$94,141.00 $52,353.00 $220,234.00
Discounted Payback Period (# years):
2.36
Sum of terminal Cash flows
$139,662.48 $147,519.52 $156,222.89 $162,608.34 $167,881.00
Total terminal value
$ 773,894
Present Values of Net Cash Flows:
Net Present Value: at 11% $
(420,800) $
82,883 $
87,546 $
92,711 $ 96,500 $
99,629
Present Values of Net Cash Flows: $
38,469
Net Present Value: at 11% $
(420,800) $
79,310 $
80,161 $
81,232 $ 80,907 $
79,930
Internal Rate of Return (IRR):
Modified Internal Rate of Return (MIRR):
Payback Period (# years):
Discounted Payback Period (# years):
IRR
The sum of net present values for the cash flows at 11%=
$
Therefore,
38,468.6
Therefore, using two rates of 11% and 16%, the NPV at 16% is
$ (19,259.18)
IRR
14.33%
MIRR
Nurse Triage Salaries
Forecasted ER Cost Reductions
New IT Specialist’s Salary
Costs of Facility Renovations
Necessary Capital Equipment Purchases
Net Cash Flow:
Balance
Discounted Payback Period (# years):
Sum of terminal Cash flows
Total terminal value
Year 0
$523,800.00
$400,000.00
$150,000.00
$30,000.00
$117,000.00
-$420,800.00
Year 1
$549,990.00
$800,000.00
$154,500.00
$0.00
$3,510.00
$92,000.00
Year 2
$577,490.00
$848,000.00
$159,135.00
$0.00
$3,510.00
$107,865.00
-$420,800.00
-$328,800.00
-$220,935.00
Year 3
Year 4
Year 5
$606,364.00 $636,682.00 $668,516.00
$900,577.00 $955,512.00 $1,013,798.00
$163,909.00 $168,826.00 $173,891.00
$0.00
$0.00
$0.00
$3,510.00
$3,510.00
$3,510.00
$126,794.00 $146,494.00 $167,881.00
-$94,141.00 $52,353.00 $220,234.00
$139,662.48
$147,519.52
$156,222.89 $162,608.34
2.36
$167,881.00
$ 773,894
Therefore, MIRR = “Future value of positive cash flows /present value of negative cash flows (1/n) – 1.”
MIRR
1.84
12.96%
Payback Period
Nurse Triage Salaries
Forecasted ER Cost Reductions
New IT Specialist’s Salary
Costs of Facility Renovations
Necessary Capital Equipment Purchases
Net Cash Flow:
Balance
Payback Period (# years):
Year 0
$523,800.00
$400,000.00
$150,000.00
$30,000.00
$117,000.00
-$420,800.00
Year 1
$549,990.00
$800,000.00
$154,500.00
$0.00
$3,510.00
$92,000.00
Year 2
$577,490.00
$848,000.00
$159,135.00
$0.00
$3,510.00
$107,865.00
-$420,800.00
-$328,800.00
-$220,935.00
Year 3
Year 4
Year 5
$606,364.00 $636,682.00 $668,516.00
$900,577.00 $955,512.00 $1,013,798.00
$163,909.00 $168,826.00 $173,891.00
$0.00
$0.00
$0.00
$3,510.00
$3,510.00
$3,510.00
$126,794.00 $146,494.00 $167,881.00
-$94,141.00 $52,353.00 $220,234.00
Year 0
Year 1
Year 2
$523,800.00 $549,990.00 $577,490.00
$400,000.00 $800,000.00 $848,000.00
$150,000.00 $154,500.00 $159,135.00
$30,000.00
$0.00
$0.00
$117,000.00
$3,510.00
$3,510.00
-$420,800.00
$92,000.00 $107,865.00
-$420,800.00 -$328,800.00 -$220,935.00
Year 3
Year 4
Year 5
$606,364.00 $636,682.00 $668,516.00
$900,577.00 $955,512.00 $1,013,798.00
$163,909.00 $168,826.00 $173,891.00
$0.00
$0.00
$0.00
$3,510.00
$3,510.00
$3,510.00
$126,794.00 $146,494.00 $167,881.00
-$94,141.00 $52,353.00 $220,234.00
2.36 years
Discounted Period
Nurse Triage Salaries
Forecasted ER Cost Reductions
New IT Specialist’s Salary
Costs of Facility Renovations
Necessary Capital Equipment Purchases
Balance
Payback Period (# years):
Discounted payback period
Discounted cash flows
Payback
2.36 years
1 0.900900901 0.811622433 0.731191381 0.65873097
-$420,800.00
$82,882.88
$87,545.65
$92,710.68 $96,500.14
-$420,800.00 -$337,917.12 -$250,371.46 -$157,660.78 -$61,160.65
0.593451328
$99,629.20
$38,468.55
4.39 years
Inpatient
Inpatient product line Budget (FY20) Actual (FY20)
CIRC
$
DIGEST
$
ENT
$
GNY
$
MENTAL HEALTH
$
NERVOUS
$
NEWBORN
$
OB
$
ORTHO
$
OTHER
$
RESP
$
Total Inpatient expenses
5,475.81
5,476.81
5,477.81
5,478.81
498.78
5,478.81
5,478.81
5,478.81
5,478.81
5,478.81
5,478.81
$
$
$
$
$
$
$
$
$
$
$
IP Express (FY20)
690.00
1,105.00
124.00
208.00
1,287.00
217.00
698.00
1,430.00
601.00
2,050.00
645.00
$ 3,778,309.00
$ 6,051,875.00
$ 679,248.00
$ 1,139,592.00
$ 641,930.00
$ 1,188,902.00
$ 3,824,209.00
$ 7,834,698.00
$ 3,292,765.00
$ 11,231,561.00
$ 3,533,832.00
$ 43,196,922.00
Revenues Worksheet, Mid-Year Review
Inpatient
Inpatient product line Price
CIRC
$
DIGEST
$
ENT
$
GNY
$
MENTAL HEALTH
$
NERVOUS
$
NEWBORN
$
OB
$
ORTHO
$
OTHER
$
RESP
$
Total Inpatient earnings
Inpatient product
Actualline
(FY21)
CIRC
DIGEST
ENT
$421.00
$636.00
$72.00
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
Forecasted For FY21
Projected IP Revenue For FY21
$
$
$
$
$
$
$
$
$
$
$
724.00
1,161.00
130.00
219.00
1,351.00
228.00
733.00
1,501.00
631.00
2,152.00
677.00
$ 4,351,711.00
$ 6,988,125.00
$ 780,215.00
$ 1,313,983.00
$ 679,539.00
$ 1,391,632.00
$ 5,141,782.00
$ 8,576,189.00
$ 3,839,118.00
$ 12,951,037.00
$ 4,142,380.00
$
9,506.00 $ 50,155,710.00
Flexible overheads
Actual relative Actual
weighted
overhead
products
(actual
Cost
Actual
qty*actual
per unit(FY21YTD)
RWPs
cost)
(FY21)
$5,873.69
$2,472,822.00
$421.00
$6,849.33
$4,356,173.00
$636.00
$6,428.08
$462,822.00
$72.00
GNY
$116.00
MENTAL HEALTH
$736.00
NERVOUS $112.00
NEWBORN $433.00
OB
$868.00
ORTHO
$274.00
OTHER
$800.00
RESP
$197.00
$4,664.00
$6,701.05
$544.47
$6,028.33
$6,861.48
$6,305.18
$6,463.73
$5,827.85
$6,262.80
$777,322.00
$400,730.00
$675,173.00
$2,971,020.00
$5,472,892.00
$1,771,063.00
$4,662,277.00
$1,233,771.00
$116.00
$736.00
$112.00
$433.00
$868.00
$274.00
$800.00
$197.00
$25,256,065.00
$4,664.00
Therefore, the price variance= Actual overheads -Flexible
overhead, therefore, $2,055,009.23
the Quantity variance is $
1,667,748.45
Forecasted (FY21) FY 11Budget (FY20+6%
Actual
inflation)
(FY21)
$
$
$
$
$
$
$
$
$
$
$
724.00
1,161.00
130.00
219.00
1,351.00
228.00
733.00
1,501.00
631.00
2,152.00
677.00
$ 4,202,356.00
$ 6,737,188.00
$
754,842.00
$ 1,268,947.00
$
714,283.00
$ 1,324,119.00
$ 4,256,926.00
$ 8,717,115.00
$ 3,664,557.00
$ 12,497,823.00
$ 3,931,704.00
$
$
$
$
$
$
$
$
$
$
$
421.00
636.00
72.00
116.00
736.00
112.00
433.00
868.00
274.00
800.00
197.00
$
9,506.00 $ 48,069,860.00 $
4,664.00
Actual IP Expenses (FY21 YTD)
$ 303.00
$ 525.00
$ 58.00
$ 103.00
$ 615.00
$ 116.00
$ 300.00
$ 633.00
$ 357.00
$ 1,352.00
$ 480.00
$ 2,472,822.00
$ 4,356,173.00
$ 462,822.00
$ 777,322.00
$ 400,730.00
$ 675,173.00
$ 2,971,020.00
$ 5,472,892.00
$ 1,771,063.00
$ 4,662,277.00
$ 1,233,771.00
$ 25,256,062.00
Mid-Year Review
Actual (FY21 YTD) Actual IP Revenue (FY21 YTD
$
$
$
$
$
$
$
$
$
$
$
421.00
636.00
72.00
116.00
736.00
112.00
433.00
868.00
274.00
800.00
197.00
$
$
$
$
$
$
$
$
$
$
$
2,530,676.00
3,830,402.00
433,948.00
697,128.00
369,949.00
682,744.00
3,033,900.00
4,960,622.00
1,664,658.00
4,813,961.00
1,202,960.00
$
4,664.00 $ 24,220,949.00
Flexible overheads
Budgted cost per each
Flexible
unit(F11YTD)
overhead Budgeted cost-Forecasted
Budgted costBugdted
relative
per each
weighted
overheads
unit(F11YTD)
products
$5,804.36
$2,443,635.19
$303.00 $5,804.36
$1,758,720.81
$5,802.92
$3,690,655.96
$525.00 $5,802.92
$3,046,532.04
$5,806.48
$418,066.34
$58.00 $5,806.48
$336,775.66
$5,794.28
$528.71
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$672,136.31
$389,128.27
$650,444.42
$2,514,664.34
$5,040,943.25
$1,591,265.64
$4,646,030.86
$1,144,085.21
$23,201,055.77
$103.00
$615.00
$116.00
$300.00
$633.00
$357.00
$1,352.00
$480.00
$5,794.28
$528.71
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$596,810.69
$325,154.73
$673,674.58
$1,742,261.66
$3,676,171.75
$2,073,291.36
$7,851,792.14
$2,787,618.79
$24,868,804.23
Jiranna Healthcare
Expenses Worksheet, Mid-Year Review
Outpatient
Unit Expense (FY20)
Actual Units
(FY20)
Outpatient Product Line
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
PEDIATRICS
PODIATRY
Total Expenses
$ 5,478.81
$ 5,478.81
$ 5,476.81
$ 5,478.81
$ 5,475.81
$ 498.78
$ 5,478.81
$ 5,478.81
$ 5,478.81
$ 5,477.81
$ 5,478.81
$55,281
645
217
1,105
208
690
1,287
698
1,430
601
124
2,050
9,055
Total OP
Expenses
(FY20)
$ 3,533,832
$ 1,188,902
$ 6,051,875
$ 1,139,592
$ 3,778,309
$ 641,930
$ 3,824,209
$ 7,834,698
$ 3,292,765
$ 679,248
$ 11,231,561
$43,196,921
Jiranna Healthcare
Revenues Worksheet, Mid-Year Review
Outpatient
Outpatient Product Line
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
PEDIATRICS
PODIATRY
Total Revenue
Product Line
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
PEDIATRICS
PODIATRY
Unit Price (FY20)
Actual Units Total OP Revenue
(FY20)
(FY20)
$ 6,118.73
645
$ 3,946,580.85
$ 6,103.65
217
$ 1,324,492.05
$ 6,021.65
1,105
$ 6,653,923.25
$ 6,013.65
208
$ 1,250,839.20
$ 6,010.65
690
$ 4,147,348.50
$ 502.99
1,287
$ 647,348.13
$ 7,014.71
698
$ 4,896,267.58
$ 5,713.65
1,430
$ 8,170,519.50
$ 6,084.18
601
$ 3,656,592.18
$ 6,001.65
124
$ 744,204.60
$ 6,018.14
2,050
$ 12,337,187.00
$61,604
9,055
$47,775,303
MDC DescrOPtion
Diseases and Disorders of the Circulatory System (Hypertension, Heart Disease, etc.)
Diseases and Disorders of the Endocrin System (Diabetes Type 1 & 2, Thyroid Disorders, etc)
Family Primary Care and Disease Preventative Services
Diseases and Disorders of Women’s Reproductive Health
Diseases and Disorders of the Ear, Nose, Mouth, and Throat
Mental Diseases and Disorders
Newborns and Other Neonates with Conditions Originating in Perinatal Period
Pregnancy, Childbirth, and the Puerperium
Diseases and Disorders of the Musculoskeletal System and Connective Tissue
Pediatric Primary Care and Disease Prevention Services (0 – 18 years)
Diseases and Disorders of the Feet and Lower Limbs
Jiranna Healthcare
Expenses Worksheet, Mid-Year Review
Unit
Forecast
(FY21)
677
228
1,161
219
724
1,351
733
1,501
631
130
2,152
9,507
Unit
FY21 Budget (FY20+6% Inflation)
Acutal Mid
Expence
Year Units
(Budget
(FY21)
FY21)
$5,807.54
$ 3,931,704
197
$5,807.54
$ 1,324,119
112
$5,802.92
$ 6,737,188
636
$5,794.28
$ 1,268,947
116
$5,804.36
$ 4,202,356
421
$528.71
$ 714,283
736
$5,807.54
$ 4,256,926
433
$5,807.54
$ 8,717,115
868
$5,807.54
$ 3,664,557
274
$5,806.48
$ 754,842
72
$5,807.54
$ 12,497,823
800
$58,582
$48,069,860
4,665
Jiranna Healthcare
Revenues Worksheet, Mid-Year Review
Unit Forecasted
Unit Price (Budget Projected OP Revenue For FY21 Actual
For (FY21)
FY21)
(FY21 YTD)
677
$6,118.73
$ 4,142,380
197
228
$6,103.65
$ 1,391,632
112
1,161
$6,019.06
$ 6,988,125
636
219
$5,999.92
$ 1,313,983
116
724
$6,010.65
$ 4,351,711
421
1,351
$502.99
$ 679,539
736
733
$7,014.71
$ 5,141,782
433
1,501
$5,713.65
$ 8,576,189
868
631
$6,084.18
$ 3,839,118
274
130
$6,001.65
$ 780,215
72
2,152
$6,018.14
$ 12,951,037
800
9,507
$61,587
$50,155,711
4,665
pertension, Heart Disease, etc.)
tes Type 1 & 2, Thyroid Disorders, etc)
ating in Perinatal Period
and Connective Tissue
Actual OP Expenses
(FY21 YTD)
$ 1,144,085
$ 650,444
$ 3,690,656
$ 672,136
$ 2,443,635
$ 389,128
$ 2,514,664
$ 5,040,943
$ 1,591,266
$ 418,066
$ 4,646,031
$23,201,056
Actual OP Revenue
(FY21 YTD)
$ 1,205,390
$ 683,609
$ 3,828,120
$ 695,991
$ 2,530,484
$ 370,200
$ 3,037,369
$ 4,959,448
$ 1,667,066
$ 432,119
$ 4,814,512
$24,224,308
Projected FY 11
Revenues
Expences
Difference
Revenues
Inpatient
Inpatient product line
CIRC
DIGEST
ENT
GNY
MENTAL HEALTH
NERVOUS
NEWBORN
OB
ORTHO
OTHER
RESP
Total Inpatient earnings
Revenues
-$1,028,515.55
Inpatient product line
CIRC
DIGEST
ENT
GNY
MENTAL HEALTH
NERVOUS
NEWBORN
OB
ORTHO
OTHER
RESP
Total Inpatient expenses
Inpatient product line
CIRC
DIGEST
ENT
Actual relative weighted products Cost per unit(FY21YTD)
$5,873.69
$6,849.33
$6,428.08
GNY
MENTAL HEALTH
NERVOUS
NEWBORN
OB
ORTHO
OTHER
RESP
$6,701.05
$544.47
$6,028.33
$6,861.48
$6,305.18
$6,463.73
$5,827.85
$6,262.80
YTD Actual
Projected Actual 2019
Price
Forecasted RWPs For FY21
$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
IP Expenses FY20
$3,778,308.90
$6,051,875.05
$679,248.44
$1,139,592.48
$641,929.86
$1,188,901.77
$3,824,209.38
$7,834,698.30
$3,292,764.81
$11,231,560.50
$3,533,832.45
$43,196,921.94
Projected revenue
$724.00
$1,161.00
$130.00
$219.00
$1,351.00
$228.00
$733.00
$1,501.00
$631.00
$2,152.00
$677.00
$4,351,710.60
$6,991,135.65
$780,214.50
$1,316,989.35
$679,539.49
$1,391,632.20
$5,141,782.43
$8,576,188.65
$3,839,117.58
$12,951,037.28
$4,142,380.21
$9,506.00
$50,161,727.94
Forecasted RWPs FY21
Projected revenue FY21
$724.00
$4,351,710.60
$1,161.00
$6,991,135.65
$130.00
$780,214.50
$219.00
$1,316,989.35
$1,351.00
$679,539.49
$228.00
$1,391,632.20
$733.00
$5,141,782.43
$1,501.00
$8,576,188.65
$631.00
$3,839,117.58
$2,152.00
$12,951,037.28
$677.00
$4,142,380.21
$9,507.00
$50,161,727.94
Budgted cost per each unit(F11YTD)
Overbudget
$5,804.36
$5,802.92
$5,806.48
$70.77
$1,042.85
$633.80
$5,794.28
$528.71
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$6,172.34
-$5,263.07
$220.79
$1,053.94
$497.64
$656.20
$20.31
$6,262.80
Actual RWPs (FY21 YTD)
Actual IP revenue (FY21YTD) Actual IP Expenses
$421.00
$636.00
$72.00
$116.00
$736.00
$112.00
$433.00
$868.00
$274.00
$800.00
$197.00
$2,530,483.65
$3,829,769.40
$432,118.80
$697,583.40
$370,200.64
$683,608.80
$3,037,369.43
$4,959,448.20
$1,667,065.32
$4,814,512.00
$1,205,389.81
$2,472,822.00
$4,356,173.00
$462,822.00
$777,322.00
$400,730.00
$675,173.00
$2,971,020.00
$5,472,892.00
$1,771,063.00
$4,662,277.00
$1,233,771.00
$24,227,549.45
$25,256,065.00
FY21 budget FY20+6%inflation projected profit FY21
$4,202,356.00
$149,354.60
$6,737,188.00
$253,947.65
$754,842.00
$25,372.50
$1,268,947.00
$48,042.35
$714,283.00
-$34,743.51
$1,324,119.00
$67,513.20
$4,256,926.00
$884,856.43
$8,717,115.00
-$140,926.35
$3,664,557.00
$174,560.58
$12,497,823.00
$453,214.28
$3,931,704.00
$210,676.21
$48,069,860.00
$2,091,867.94
Actual profit (FY21YTD) Actual cost per unit (FY21YTD)
$57,661.65
-$526,403.60
-$30,703.20
-$79,738.60
-$30,529.36
$8,435.80
$66,349.43
-$513,443.80
-$103,997.68
$152,235.00
-$28,381.19
-$1,028,515.55
$5,873.69
$6,849.33
$6,428.08
$6,701.05
$544.47
$6,028.33
$6,861.48
$6,305.18
$6,463.73
$5,827.85
$6,262.80
Jiranna Healthcare
Projected Profit
Projected
Projected (FY21)
(mid-year)
Actual
(mid-year)
Revenue
Expenses
Profit
Outpatient Product Line
Jiranna Healthcare
FY21 YTD Expense Variances
I
II
Actual OP
FY21 YTD Budget Expenses (FY21
YTD)
III
Expense Variance
(I-II)
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
PEDIATRICS
PODIATRY
Total Outpatient Expenses
Outpatient Product Line
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
Jiranna Healthcare
Revenue Variances
I
II
Actual OP
FY21 YTD Budget
Revenue (FY21
YTD)
III
Revenue Variance
(I-II)
PEDIATRICS
PODIATRY
Total Outpatient Revenues
Outpatient Product Line
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
PEDIATRICS
PODIATRY
Total Profit
Jiranna Healthcare
Profit Variances
I
II
FY21 YTD Budget Actual OP Profit
Profit
(FY21 YTD)
III
Profit Variance
(I-II)
Revised FY21
Projection
IV
Favorable /
Unfavorable
IV
Favorable /
Unfavorable
IV
F/U
Capital Project Case Study
This case study considers the expected costs and benefits to Jiranna Healthcare resulting from a decision to design a
centralized nurse triage line. This triage line would assist Jiranna 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’s main facility 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 11 satellite clinics, which can contain healthcare services,
such as pediatrics, family medicine, and mental health etc. These facilities (the hospital plus outlying clinics) serve a total
enrollee population of 97,000.
Currently, Jiranna Healthcare’s centralized call center schedules patient appointments for each of the 11 satellite clinics,
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 in a timely fashion. Additionally, in three out of four cases, the majority of Jiranna Healthcare’s healthcare
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 non-emergent concerns. In
addition, patient satisfaction has steadily declined as a result of the continued lack of access to healthcare services.
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 line’s computer system and facility renovations. The main
benefit of this proposal is the projected cost reductions in patient care as a result of moving non-emergent care out of the
expensive emergency-room setting.
© 2020 Walden University
1
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 in your working Excel Workbook, and use the information provided to
complete the following:
1. Calculate 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. In a 1- to 2-page report, provide your recommendation with a rationale, as to whether the project is acceptable,
assuming Jiranna Healthcare 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.
© 2020 Walden University
2
Jiranna Healthcare Income Statement December 31, 2021 (in thousands)
Gross patient services revenues (non-GAAP)
Less deductions from revenues (non-GAAP)
Net patient service revenues
Other operating revenues
Total operating revenues
2017
8,870
(780)
8,090
519
8,609
2018
9,490
(890)
8,600
633
9,233
2019
10,400
(1,000)
9,400
679
10,079
2020
11,200
(1,500)
9,700
717
10,417
Operating expenses
Salaries and wages
Supplies
Utilities
Insurance
Depreciation
Interest
Bad debts
Other operating expenses
Total operating expenses
5,497
823
558
44
168
142
363
987
8,582
5,678
850
576
46
173
146
375
1,299
9,143
5,890
855
590
49
175
154
400
1,560
9,673
6,170
890
595
54
178
179
455
1,300
9,821
Operating income
Nonoperating income
Excess of revenue over expenses
Change in net assets
Unrestricted
Temporarily restricted
Permanently restricted
Total change in net assets
$
$
27
154
181 $
90
195
285 $
406
245
651 $
596
220
816
181 $
181
285 $
285
651 $
651
816
816
2021
12,050
(1,600)
10,450
980
11,430
6,800
905
620
70
188
198
500
1,350
10,631
$
$
799
290
1,089
1,089
1,089
Jiranna Healthcare Balance Sheet December 31, 2021 (in thousands)
2017
280 $
30
1,340
140
40
1,830
2018
124 $
45
1,536
175
32
1,912
2019
136 $
50
1,700
250
40
2,176
2020
295
75
1,896
276
53
2,595
600
6,580
-1,660
4,920
7,350 $
1,010
6,780
-1750
5,030
7,952 $
1,200
6,900
-1,800
5,100
8,476 $
1,300
7,200
-1,950
5,250
9,145
Current liabilities
Accounts payable
Accrued expenses payable
Deferred revenues
Total Current liabilities
Long-term liabilities
Total Liabilities
370
220
60
650
2,400
3,050
302
208
77
587
3,000
3,587
356
212
87
655
3,300
3,955
370
210
87
667
3,500
4,167
Net assets
Unrestricted
Temporarily restricted
Permanently restricted
Total Net Assets
Total Liabilities and Net Assets
3,000
700
600
4,300
7,350
3,285
700
600
4,585
8,172
3,221
700
600
4,521
8,476
3,678
700
600
4,978
9,145
Current Assets
Cash
Receivables, net
Inventory
Prepaid expenses
Total Current Assets
Total Assets
Short-term investments
Plant and equipment
Less accumulated depreciation
Plants and equipment, net
Total Assets
$
$
$
$
2021
355
88
2,400
266
78
3,187
1,510
7,500
-2,350
5,150
9,847
375
215
94
684
3,750
4,434
4,113
700
600
5,413
9,847
Jiranna Healthcare Cash Flows, 2021 (in thousands)
Cash Flows from Operating Activities
Cash received from patient and third-party payers
Cash received from operating revenue sources
Cash received from nonoperating revenue sources
Cash payments to employees
Cash payments to suppliers of goods and services
Net cash flow from operating activities
Cash flows from Investing Activities
Cash payments for purchase of plant assets
Cash payments for purchase of long-term investments
Proceeds from sales of plant assets
Proceeds from sale of long-term investments
Net cash flow from investing activities
Cash flows from Financing Activities
Proceeds from issuance of 6% bonds payable
Principal payments on long-term debt
Cash payments to retire 7% bonds payable
Net cash flow from financing activities
Net increase/(decrease) in cash
$
10,671
800
270
(5,600)
(4,800)
$
1,341
(1,200)
(670)
80
60
(1,730)
4,000
(300)
(3,200)
$
500
111
Nurse Triage Salaries
Forecasted ER Cost Reductions
New IT Specialist’s Salary
Costs of Facility Renovations
Necessary Capital Equipment Purchases
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):
Year 0
Year 1
Year 2
Year 3
Year 4
Year 5
$ 523,800
$ 400,000
$ 150,000
$ 30,000
$ 117,000
$ 549,990
$ 800,000
$ 154,500
$
$ 3,510
$ 577,490
$ 848,000
$ 159,135
$
$ 3,510
$ 606,364
$ 900,577
$ 163,909
$
$ 3,510
$ 636,682
$ 955,512
$ 168,826
$
$ 3,510
$ 668,516
$ 1,013,798
$ 173,891
$
$
3,510
Jiranna Healthcare
Expenses Worksheet, Mid-Year Review
Outpatient
Unit Expense (FY20)
Actual Units
(FY20)
Outpatient Product Line
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
PEDIATRICS
PODIATRY
Total Expenses
$ 5,478.81
$ 5,478.81
$ 5,476.81
$ 5,478.81
$ 5,475.81
$ 498.78
$ 5,478.81
$ 5,478.81
$ 5,478.81
$ 5,477.81
$ 5,478.81
$55,281
645
217
1,105
208
690
1,287
698
1,430
601
124
2,050
9,055
Total OP
Expenses
(FY20)
$ 3,533,832
$ 1,188,902
$ 6,051,875
$ 1,139,592
$ 3,778,309
$ 641,930
$ 3,824,209
$ 7,834,698
$ 3,292,765
$ 679,248
$ 11,231,561
$43,196,921
Unit Forecast
(FY21)
Unit Expence
(Budget
FY21)
677
228
1,161
219
724
1,351
733
1,501
631
130
2,152
9,507
$5,807.54
$5,807.54
$5,802.92
$5,794.28
$5,804.36
$528.71
$5,807.54
$5,807.54
$5,807.54
$5,806.48
$5,807.54
$58,582
Jiranna Healthcare
Revenues Worksheet, Mid-Year Review
Outpatient
Outpatient Product Line
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
PEDIATRICS
PODIATRY
Total Revenue
Unit Price (FY20)
Unit Price (Budget
Actual Units
Total OP Revenue Unit Forecasted
For (FY21)
FY21)
(FY20)
(FY20)
$ 6,118.73
645
$ 3,946,580.85
677
$6,118.73
$ 6,103.65
217
$ 1,324,492.05
228
$6,103.65
$ 6,021.65
1,105
$ 6,653,923.25
1,161
$6,019.06
$ 6,013.65
208
$ 1,250,839.20
219
$5,999.92
$ 6,010.65
690
$ 4,147,348.50
724
$6,010.65
$ 502.99
1,287
$ 647,348.13
1,351
$502.99
$ 7,014.71
698
$ 4,896,267.58
733
$7,014.71
$ 5,713.65
1,430
$ 8,170,519.50
1,501
$5,713.65
$ 6,084.18
601
$ 3,656,592.18
631
$6,084.18
$ 6,001.65
124
$ 744,204.60
130
$6,001.65
$ 6,018.14
2,050
$ 12,337,187.00
2,152
$6,018.14
$61,604
9,055
$47,775,303
9,507
$61,587
Product Line
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
PEDIATRICS
PODIATRY
MDC DescrOPtion
Diseases and Disorders of the Circulatory System (Hypertension, Heart Disease, etc.)
Diseases and Disorders of the Endocrin System (Diabetes Type 1 & 2, Thyroid Disorders, etc)
Family Primary Care and Disease Preventative Services
Diseases and Disorders of Women’s Reproductive Health
Diseases and Disorders of the Ear, Nose, Mouth, and Throat
Mental Diseases and Disorders
Newborns and Other Neonates with Conditions Originating in Perinatal Period
Pregnancy, Childbirth, and the Puerperium
Diseases and Disorders of the Musculoskeletal System and Connective Tissue
Pediatric Primary Care and Disease Prevention Services (0 – 18 years)
Diseases and Disorders of the Feet and Lower Limbs
FY21 Budget (FY20+6% Inflation)
$ 3,931,704
$ 1,324,119
$ 6,737,188
$ 1,268,947
$ 4,202,356
$ 714,283
$ 4,256,926
$ 8,717,115
$ 3,664,557
$ 754,842
$ 12,497,823
$48,069,860
Acutal Mid
Year Units
(FY21)
197
112
636
116
421
736
433
868
274
72
800
4,665
Actual OP Expenses
(FY21 YTD)
$ 1,144,085
$ 650,444
$ 3,690,656
$ 672,136
$ 2,443,635
$ 389,128
$ 2,514,664
$ 5,040,943
$ 1,591,266
$ 418,066
$ 4,646,031
$23,201,056
Projected OP Revenue For FY21
Actual (FY21 Actual OP Revenue
YTD)
(FY21 YTD)
$ 4,142,380
197
$ 1,205,390
$ 1,391,632
112
$ 683,609
$ 6,988,125
636
$ 3,828,120
$ 1,313,983
116
$ 695,991
$ 4,351,711
421
$ 2,530,484
$ 679,539
736
$ 370,200
$ 5,141,782
433
$ 3,037,369
$ 8,576,189
868
$ 4,959,448
$ 3,839,118
274
$ 1,667,066
$ 780,215
72
$ 432,119
$ 12,951,037
800
$ 4,814,512
$50,155,711
4,665
$24,224,308
This Assessment considers a Capital Proposal Case Study to add a specialized nurse triage
center, an offsite center that would be staffed by registered nurses with a multitude of specialties,
including emergency department (ED) 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 the ED.
This assessment has three-parts. Click each of the items below to complete this assessment.
Part I: Analyze Organizational Financial Data
For this part of the Final Assessment, you will conduct a 5-year analysis of Jiranna Healthcare’s
outpatient operational and financial data in order to determine whether or not the capital project
is an attractive project. Refer to “Jiranna Finances” for financial statements to analyze.
Prepare a 2- to 3-page financial analysis including a supporting Excel spreadsheet to show your
work. The focus must be on the content and the depth of your analysis. Unless otherwise
indicated, a 5-year trend analysis is expected.
Complete your analysis as follows:
Calculate the 5-year net sales, operating expenses, operating income, and net income of Jiranna
Healthcare. Once the 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 the calculations are complete, interpret the resulting data and determine the company’s
profitability.
Calculate the 5-year current ratio, day’s cash on hand, and working capital. Once the calculations
are complete, interpret the resulting data and assess the company’s liquidity.
Calculate the 5-year debt ratio and times interest earned ratio. Once the calculations are
complete, interpret the resulting data and explain the company’s 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 company’s 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 the capital project to Jiranna
Healthcare? Explain the rationale for your decision.
Part II: Conduct an Operating Indicator Evaluation
For the next part of the Final Assessment, 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 calculation using current technology like Excel Workbook,
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 cashflow data to determine the costs and benefits of implementing a new capital project. For
background, read “Capital Project Case Study.” Then, complete the “Capital Project Case Study
Spreadsheet”, which provides cash-flow data (costs and benefits) for the proposal. Download and
save the Excel spreadsheet in your working Excel Workbook, and use the information provided
to complete the following:
Calculate the cash inflows and outflows for each year.
Evaluate the capital project by calculating 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 including a supporting Excel spreadsheet to show your work, provide
your recommendation with rationale, as to whether the project is acceptable, assuming Jiranna
Healthcare 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: Conducting 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 outpatient product lines at Jiranna Healthcare.
Analyze the data and prepare a 3- to 4-page report including a supporting Excel spreadsheet to
show your work. 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 Jiranna’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 FY21 profits?
Which outpatient service lines are over budget? Which outpatient 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-forservice healthcare organization? 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 healthcare organization? 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 healthcare organization normally operate
under a capped budget. Additionally, many HMOs also operate under a fixed per member, per
month (PMPM) capitated process.
Capital Project Case Study
This case study considers the expected costs and benefits to Jiranna Healthcare resulting from a decision to design a
centralized nurse triage line. This triage line would assist Jiranna 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’s main facility 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 11 satellite clinics, which can contain healthcare services,
such as pediatrics, family medicine, and mental health etc. These facilities (the hospital plus outlying clinics) serve a total
enrollee population of 97,000.
Currently, Jiranna Healthcare’s centralized call center schedules patient appointments for each of the 11 satellite clinics,
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 in a timely fashion. Additionally, in three out of four cases, the majority of Jiranna Healthcare’s healthcare
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 non-emergent concerns. In
addition, patient satisfaction has steadily declined as a result of the continued lack of access to healthcare services.
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 line’s computer system and facility renovations. The main
benefit of this proposal is the projected cost reductions in patient care as a result of moving non-emergent care out of the
expensive emergency-room setting.
© 2020 Walden University
1
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 in your working Excel Workbook, and use the information provided to
complete the following:
1. Calculate 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. In a 1- to 2-page report, provide your recommendation with a rationale, as to whether the project is acceptable,
assuming Jiranna Healthcare 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.
© 2020 Walden University
2
Jiranna Healthcare Income Statement December 31, 2021 (in thousands)
Gross patient services revenues (non-GAAP)
Less deductions from revenues (non-GAAP)
Net patient service revenues
Other operating revenues
Total operating revenues
2017
8,870
(780)
8,090
519
8,609
2018
9,490
(890)
8,600
633
9,233
2019
10,400
(1,000)
9,400
679
10,079
2020
11,200
(1,500)
9,700
717
10,417
Operating expenses
Salaries and wages
Supplies
Utilities
Insurance
Depreciation
Interest
Bad debts
Other operating expenses
Total operating expenses
5,497
823
558
44
168
142
363
987
8,582
5,678
850
576
46
173
146
375
1,299
9,143
5,890
855
590
49
175
154
400
1,560
9,673
6,170
890
595
54
178
179
455
1,300
9,821
Operating income
Nonoperating income
Excess of revenue over expenses
Change in net assets
Unrestricted
Temporarily restricted
Permanently restricted
Total change in net assets
$
$
27
154
181 $
90
195
285 $
406
245
651 $
596
220
816
181 $
181
285 $
285
651 $
651
816
816
2021
12,050
(1,600)
10,450
980
11,430
6,800
905
620
70
188
198
500
1,350
10,631
$
$
799
290
1,089
1,089
1,089
Jiranna Healthcare Balance Sheet December 31, 2021 (in thousands)
2017
280 $
30
1,340
140
40
1,830
2018
124 $
45
1,536
175
32
1,912
2019
136 $
50
1,700
250
40
2,176
2020
295
75
1,896
276
53
2,595
600
6,580
-1,660
4,920
7,350 $
1,010
6,780
-1750
5,030
7,952 $
1,200
6,900
-1,800
5,100
8,476 $
1,300
7,200
-1,950
5,250
9,145
Current liabilities
Accounts payable
Accrued expenses payable
Deferred revenues
Total Current liabilities
Long-term liabilities
Total Liabilities
370
220
60
650
2,400
3,050
302
208
77
587
3,000
3,587
356
212
87
655
3,300
3,955
370
210
87
667
3,500
4,167
Net assets
Unrestricted
Temporarily restricted
Permanently restricted
Total Net Assets
Total Liabilities and Net Assets
3,000
700
600
4,300
7,350
3,285
700
600
4,585
8,172
3,221
700
600
4,521
8,476
3,678
700
600
4,978
9,145
Current Assets
Cash
Receivables, net
Inventory
Prepaid expenses
Total Current Assets
Total Assets
Short-term investments
Plant and equipment
Less accumulated depreciation
Plants and equipment, net
Total Assets
$
$
$
$
2021
355
88
2,400
266
78
3,187
1,510
7,500
-2,350
5,150
9,847
375
215
94
684
3,750
4,434
4,113
700
600
5,413
9,847
Jiranna Healthcare Cash Flows, 2021 (in thousands)
Cash Flows from Operating Activities
Cash received from patient and third-party payers
Cash received from operating revenue sources
Cash received from nonoperating revenue sources
Cash payments to employees
Cash payments to suppliers of goods and services
Net cash flow from operating activities
Cash flows from Investing Activities
Cash payments for purchase of plant assets
Cash payments for purchase of long-term investments
Proceeds from sales of plant assets
Proceeds from sale of long-term investments
Net cash flow from investing activities
Cash flows from Financing Activities
Proceeds from issuance of 6% bonds payable
Principal payments on long-term debt
Cash payments to retire 7% bonds payable
Net cash flow from financing activities
Net increase/(decrease) in cash
$
10,671
800
270
(5,600)
(4,800)
$
1,341
(1,200)
(670)
80
60
(1,730)
4,000
(300)
(3,200)
$
500
111
Nurse Triage Salaries
Forecasted ER Cost Reductions
New IT Specialist’s Salary
Costs of Facility Renovations
Necessary Capital Equipment Purchases
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):
Year 0
Year 1
Year 2
Year 3
Year 4
Year 5
$ 523,800
$ 400,000
$ 150,000
$ 30,000
$ 117,000
$ 549,990
$ 800,000
$ 154,500
$
$ 3,510
$ 577,490
$ 848,000
$ 159,135
$
$ 3,510
$ 606,364
$ 900,577
$ 163,909
$
$ 3,510
$ 636,682
$ 955,512
$ 168,826
$
$ 3,510
$ 668,516
$ 1,013,798
$ 173,891
$
$
3,510
Jiranna Healthcare
Expenses Worksheet, Mid-Year Review
Outpatient
Unit Expense (FY20)
Actual Units
(FY20)
Outpatient Product Line
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
PEDIATRICS
PODIATRY
Total Expenses
$ 5,478.81
$ 5,478.81
$ 5,476.81
$ 5,478.81
$ 5,475.81
$ 498.78
$ 5,478.81
$ 5,478.81
$ 5,478.81
$ 5,477.81
$ 5,478.81
$55,281
645
217
1,105
208
690
1,287
698
1,430
601
124
2,050
9,055
Total OP
Expenses
(FY20)
$ 3,533,832
$ 1,188,902
$ 6,051,875
$ 1,139,592
$ 3,778,309
$ 641,930
$ 3,824,209
$ 7,834,698
$ 3,292,765
$ 679,248
$ 11,231,561
$43,196,921
Unit Forecast
(FY21)
Unit Expence
(Budget
FY21)
677
228
1,161
219
724
1,351
733
1,501
631
130
2,152
9,507
$5,807.54
$5,807.54
$5,802.92
$5,794.28
$5,804.36
$528.71
$5,807.54
$5,807.54
$5,807.54
$5,806.48
$5,807.54
$58,582
Jiranna Healthcare
Revenues Worksheet, Mid-Year Review
Outpatient
Outpatient Product Line
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
PEDIATRICS
PODIATRY
Total Revenue
Unit Price (FY20)
Unit Price (Budget
Actual Units
Total OP Revenue Unit Forecasted
For (FY21)
FY21)
(FY20)
(FY20)
$ 6,118.73
645
$ 3,946,580.85
677
$6,118.73
$ 6,103.65
217
$ 1,324,492.05
228
$6,103.65
$ 6,021.65
1,105
$ 6,653,923.25
1,161
$6,019.06
$ 6,013.65
208
$ 1,250,839.20
219
$5,999.92
$ 6,010.65
690
$ 4,147,348.50
724
$6,010.65
$ 502.99
1,287
$ 647,348.13
1,351
$502.99
$ 7,014.71
698
$ 4,896,267.58
733
$7,014.71
$ 5,713.65
1,430
$ 8,170,519.50
1,501
$5,713.65
$ 6,084.18
601
$ 3,656,592.18
631
$6,084.18
$ 6,001.65
124
$ 744,204.60
130
$6,001.65
$ 6,018.14
2,050
$ 12,337,187.00
2,152
$6,018.14
$61,604
9,055
$47,775,303
9,507
$61,587
Product Line
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
PEDIATRICS
PODIATRY
MDC DescrOPtion
Diseases and Disorders of the Circulatory System (Hypertension, Heart Disease, etc.)
Diseases and Disorders of the Endocrin System (Diabetes Type 1 & 2, Thyroid Disorders, etc)
Family Primary Care and Disease Preventative Services
Diseases and Disorders of Women’s Reproductive Health
Diseases and Disorders of the Ear, Nose, Mouth, and Throat
Mental Diseases and Disorders
Newborns and Other Neonates with Conditions Originating in Perinatal Period
Pregnancy, Childbirth, and the Puerperium
Diseases and Disorders of the Musculoskeletal System and Connective Tissue
Pediatric Primary Care and Disease Prevention Services (0 – 18 years)
Diseases and Disorders of the Feet and Lower Limbs
FY21 Budget (FY20+6% Inflation)
$ 3,931,704
$ 1,324,119
$ 6,737,188
$ 1,268,947
$ 4,202,356
$ 714,283
$ 4,256,926
$ 8,717,115
$ 3,664,557
$ 754,842
$ 12,497,823
$48,069,860
Acutal Mid
Year Units
(FY21)
197
112
636
116
421
736
433
868
274
72
800
4,665
Actual OP Expenses
(FY21 YTD)
$ 1,144,085
$ 650,444
$ 3,690,656
$ 672,136
$ 2,443,635
$ 389,128
$ 2,514,664
$ 5,040,943
$ 1,591,266
$ 418,066
$ 4,646,031
$23,201,056
Projected OP Revenue For FY21
Actual (FY21 Actual OP Revenue
YTD)
(FY21 YTD)
$ 4,142,380
197
$ 1,205,390
$ 1,391,632
112
$ 683,609
$ 6,988,125
636
$ 3,828,120
$ 1,313,983
116
$ 695,991
$ 4,351,711
421
$ 2,530,484
$ 679,539
736
$ 370,200
$ 5,141,782
433
$ 3,037,369
$ 8,576,189
868
$ 4,959,448
$ 3,839,118
274
$ 1,667,066
$ 780,215
72
$ 432,119
$ 12,951,037
800
$ 4,814,512
$50,155,711
4,665
$24,224,308
Jiranna Healthcare Income Statement December 31, 2021 (in thousands)
Gross patient services revenues (non-GAAP)
Less deductions from revenues (non-GAAP)
Net patient service revenues
Other operating revenues
Total operating revenues
2017
8,870
(780)
8,090
519
8,609
2018
9,490
(890)
8,600
633
9,233
2019
10,400
(1,000)
9,400
679
10,079
2020
11,200
(1,500)
9,700
717
10,417
Operating expenses
Salaries and wages
Supplies
Utilities
Insurance
Depreciation
Interest
Bad debts
Other operating expenses
Total operating expenses
5,497
823
558
44
168
142
363
987
8,582
5,678
850
576
46
173
146
375
1,299
9,143
5,890
855
590
49
175
154
400
1,560
9,673
6,170
890
595
54
178
179
455
1,300
9,821
Operating income
Nonoperating income
Excess of revenue over expenses
Change in net assets
Unrestricted
Temporarily restricted
Permanently restricted
Total change in net assets
$
$
27
154
181 $
90
195
285 $
406
245
651 $
596
220
816
181 $
181
285 $
285
651 $
651
816
816
2021
12,050
(1,600)
10,450
980
11,430
6,800
905
620
70
188
198
500
1,350
10,631
$
$
799
290
1,089
1,089
1,089
Jiranna Healthcare Balance Sheet December 31, 2021 (in thousands)
Current assets
Cash
Receivables, net
Inventory
Prepaid expenses
Total Current Assets
Long-term investments
Plant and equipment
Less accumulated depreciation
Plants and equipment, net
Total Assets
$
2017
280 $
30
1,340
140
40
1,830
600
6,580
-1,660
2018
124 $
45
1,536
175
32
1,912
2019
136 $
50
1,700
250
40
2,176
2020
295
75
1,896
276
53
2,595
1,010
1,200
1,300
6,780
-1750
6,900
-1,800
7,200
-1,950
4,920
7,350 $
5,030
7,952 $
5,100
8,476 $
5,250
9,145
Current liabilities
Accounts payable
Accrued expenses payable
Deferred revenues
Total current liabilities
Long-term liabilities
Total Liabilities
370
220
60
650
2,400
3,050
302
208
77
587
3,000
3,587
356
212
87
655
3,300
3,955
370
210
87
667
3,500
4,167
Net assets
Unrestricted
Temporarily restricted
Permanently restricted
Total Net Assets
Total Liabilities and Net Assets
3,000
700
600
4,300
7,350
3,285
700
600
4,585
8,172
3,221
700
600
4,521
8,476
3,678
700
600
4,978
9,145
$
$
2021
355
88
2,400
266
78
3,187
1,510
7,500
-2,350
$
5,150
9,847
375
215
94
684
3,750
4,434
4,113
700
600
5,413
9,847
$
8,214
Jiranna Healthcare Cash Flows, 2021 (in thousands)
Cash Flows from Operating Activities
Cash received from patient and third-party payers
Cash received from operating revenue sources
Cash received from nonoperating revenue sources
Cash payments to employees
Cash payments to suppliers of goods and services
Net cash flow from operating activities
Cash flows from Investing Activities
Cash payments for purchase of plant assets
Cash payments for purchase of long-term investments
Proceeds from sales of plant assets
Proceeds from sale of long-term investments
Net cash flow from investing activities
Cash flows from Financing Activities
Proceeds from issuance of 6% bonds payable
Principal payments on long-term debt
Cash payments to retire 7% bonds payable
Net cash flow from financing activities
Net increase/(decrease) in cash
$
10,671
800
270
(5,600)
(4,800)
$
1,341
(1,200)
(670)
80
60
(1,730)
4,000
(300)
(3,200)
$
500
111
Jiranna Financials Per Above
Net Sales
Operating Expenses
Operating Income
Nonoperating income
Net Income
(in thousands)
2017
2018
8609.00
9233.00
8582.00
9143.00
27.00
90.00
154
195
181.00
285.00
2019
10079.00
9673.00
406.00
245
651.00
2020
10417.00
9821.00
596.00
220
816.00
2021
11430.00
10631.00
799.00
290
1089.00
Five year trend Analysis
Operating Expenses
Operating Income
Net Income
14000.00
1200.00
12000.00
1000.00
10000.00
800.00
8000.00
600.00
6000.00
400.00
4000.00
200.00
2000.00
0.00
0.00
2017
2018
2019
Years
2020
2021
Values in 000
values in 000
Net Sales
in
2017
Profit Margin
Net Income/sales
Net Income
Sales
2018
2019
2020
2021
$ 181.00 $ 285.00 $ 651.00 $ 816.00 $ 1,089.00
$ 8,609.00 $ 9,233.00 $ 10,079.00 $ 10,417.00 $ 11,430.00
2.10%
3.09%
6.46%
7.83%
9.53%
Assets Turnover Ratio
2017
2018
2019
2020
2021
Sales/Average Total Assets
Sales
$ 8,609.00 $ 9,233.00 $ 10,079.00 $ 10,417.00 $ 11,430.00
Average Total assets $ 7,350.00 $ 7,651.00 $ 8,214.00 $ 8,810.50 $ 9,496.00
1.17
1.21
1.23
1.18
1.20
2017
2018
2019
2020
2021
Return on assets
Net income/Average total assets
Net income
$ 181.00 $ 285.00 $ 651.00 $ 816.00 $ 1,089.00
Average total assets $ 7,350.00 $ 7,651.00 $ 8,214.00 $ 8,810.50 $ 9,496.00
2.46%
3.73%
7.93%
9.26%
11.47%
2017
2018
2019
2020
2021
Return on Net worth
Net income/ Average Shareholder’s equity
Net income
$ 181.00 $ 285.00 $ 651.00 $ 816.00 $ 1,089.00
Average Sharehodler’s equity
$ 4,300.00 $ 4,442.50 $ 4,553.00 $ 4,749.50 $ 5,195.50
4.21%
6.42%
14.30%
17.18%
20.96%
(values in 000)
(values in 000)
2017
Current ratio
Current assets/current liabilities
Current assets
Current liabilities
2020
2018
2019
2020
$
30.00 $
45.00 $
50.00 $
75.00
$ 8,582.00 $ 9,143.00 $ 9,673.00 $ 9,821.00
$
23.51 $
25.05 $
26.50 $
26.91
1
2
2
3
2017
Working Capital
current assets-current liabilities
current assets
current liabilities
2019
$ 1,830.00 $ 1,912.00 $ 2,176.00 $ 2,595.00
$ 650.00 $ 587.00 $ 655.00 $ 667.00
2.82
3.26
3.32
3.89
2017
Day’s cash on hand
cash/(Total Operating Expenses / 365)
cash
Total operrating expense
Total Operating Expenses / 365
2018
2018
2019
2020
$ 1,830.00 $ 1,912.00 $ 2,176.00 $ 2,595.00
$ 650.00 $ 587.00 $ 655.00 $ 667.00
$ 1,180.00 $ 1,325.00 $ 1,521.00 $ 1,928.00
2021
$ 3,187.00 (values in 000)
$ 684.00
4.66
2021
$
88.00 (Values in 000)
$ 10,631.00
$
29.13
3 (Rounded off to a whole number)
2021
$ 3,187.00
$ 684.00
$ 2,503.00
2017
2018
2019
2020
2021
Debt ratio
Total liabilities/Total assets
Total Debt
$ 3,050.00 $ 3,587.00 $ 3,955.00 $ 4,167.00 $ 4,434.00 (Values in 000)
Total assets
$ 7,350.00 $ 7,952.00 $ 8,476.00 $ 9,145.00 $ 9,847.00
0.41
0.45
0.47
0.46
0.45
2017
2018
2019
2020
2021
Times Interest Earned Ratio
EBIT/Interest expense
EBIT (Operating income +$ interest
169.00expense
$ 236.00 $ 560.00 $ 775.00 $ 997.00
operating income
$ 27.00 $ 90.00 $ 406.00 $ 596.00 $ 799.00
Interest expense
$ 142.00 $ 146.00 $ 154.00 $ 179.00 $ 198.00
1.19
1.62
3.64
4.33
5.04
(Values in 000)
2017
2018
2019
Dupont Analysis (Net profit margin* asset turnover*equitymultiplier)
Total Asset
$ 7,350.00 $ 7,952.00 $ 8,476.00
Total equity
$ 4,300.00 $ 4,585.00 $ 4,521.00
Net income
$
181 $
285 $
651
total revenue
$ 8,609.00 $ 9,233.00 $ 10,079.00
Net Profit Margin (Net Income / Total Revenue)
Asset Turnover (Total Revenue / Total Assets)
Equity Multiplier (Total Assets / Total Equity)
DuPont Analysis
2.10%
1.17
1.71
4.21%
3.09%
1.16
1.73
6.22%
6.46%
1.19
1.87
14.40%
2020
2021
$ 9,145.00
$ 4,978.00
$
816
$ 10,417.00
$ 9,847.00
$ 5,413.00
$
1,089
$ 11,430.00
7.83%
1.14
1.84
16.39%
9.53%
1.16
1.82
20.12%
Year 0
Nurse Triage Salaries
Forecasted ER Cost Reductions
New IT Specialist’s Salary
Costs of Facility Renovations
Necessary Capital Equipment Purchases
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):
$
$
$
$
$
Year 1
523,800
400,000
150,000
30,000
117,000
$
$
$
$
$
549,990
800,000
154,500
3,510
Year 2
$
$
$
$
$
577,490
848,000
159,135
3,510
Year 3
$
$
$
$
$
606,364
900,577
163,909
3,510
Year 4
Year 5
$ 636,682
$ 955,512
$ 168,826
$
$
3,510
$
668,516
$ 1,013,798
$
173,891
$
$
3,510
Year 0
Year 1
Year 2
Year 3
Year 4
Year 5
Nurse Triage Salaries
$523,800.00 $549,990.00 $577,490.00 $606,364.00 $636,682.00 $668,516.00
rate of capital
Forecasted ER Cost Reductions
$400,000.00 $800,000.00 $848,000.00 $900,577.00 $955,512.00 $1,013,798.00
11%
New IT Specialist’s Salary
$150,000.00 $154,500.00 $159,135.00 $163,909.00 $168,826.00 $173,891.00
Costs of Facility Renovations
$30,000.00
$0.00
$0.00
$0.00
$0.00
$0.00
Necessary Capital Equipment Purchases
$117,000.00
$3,510.00
$3,510.00
$3,510.00
$3,510.00
$3,510.00
Net Cash Flow:
-$420,800.00
$92,000.00 $107,865.00 $126,794.00 $146,494.00 $167,881.00
Balance
-$420,800.00 -$328,800.00 -$220,935.00
-$94,141.00 $52,353.00 $220,234.00
Discounted Payback Period (# years):
2.36
Sum of terminal Cash flows
$139,662.48 $147,519.52 $156,222.89 $162,608.34 $167,881.00
Total terminal value
$ 773,894
Present Values of Net Cash Flows:
Net Present Value: at 11% $
(420,800) $
82,883 $
87,546 $
92,711 $ 96,500 $
99,629
Present Values of Net Cash Flows: $
38,469
Net Present Value: at 11% $
(420,800) $
79,310 $
80,161 $
81,232 $ 80,907 $
79,930
Internal Rate of Return (IRR):
Modified Internal Rate of Return (MIRR):
Payback Period (# years):
Discounted Payback Period (# years):
IRR
The sum of net present values for the cash flows at 11%=
$
Therefore,
38,468.6
Therefore, using two rates of 11% and 16%, the NPV at 16% is
$ (19,259.18)
IRR
14.33%
MIRR
Nurse Triage Salaries
Forecasted ER Cost Reductions
New IT Specialist’s Salary
Costs of Facility Renovations
Necessary Capital Equipment Purchases
Net Cash Flow:
Balance
Discounted Payback Period (# years):
Sum of terminal Cash flows
Total terminal value
Year 0
$523,800.00
$400,000.00
$150,000.00
$30,000.00
$117,000.00
-$420,800.00
Year 1
$549,990.00
$800,000.00
$154,500.00
$0.00
$3,510.00
$92,000.00
Year 2
$577,490.00
$848,000.00
$159,135.00
$0.00
$3,510.00
$107,865.00
-$420,800.00
-$328,800.00
-$220,935.00
Year 3
Year 4
Year 5
$606,364.00 $636,682.00 $668,516.00
$900,577.00 $955,512.00 $1,013,798.00
$163,909.00 $168,826.00 $173,891.00
$0.00
$0.00
$0.00
$3,510.00
$3,510.00
$3,510.00
$126,794.00 $146,494.00 $167,881.00
-$94,141.00 $52,353.00 $220,234.00
$139,662.48
$147,519.52
$156,222.89 $162,608.34
2.36
$167,881.00
$ 773,894
Therefore, MIRR = “Future value of positive cash flows /present value of negative cash flows (1/n) – 1.”
MIRR
1.84
12.96%
Payback Period
Nurse Triage Salaries
Forecasted ER Cost Reductions
New IT Specialist’s Salary
Costs of Facility Renovations
Necessary Capital Equipment Purchases
Net Cash Flow:
Balance
Payback Period (# years):
Year 0
$523,800.00
$400,000.00
$150,000.00
$30,000.00
$117,000.00
-$420,800.00
Year 1
$549,990.00
$800,000.00
$154,500.00
$0.00
$3,510.00
$92,000.00
Year 2
$577,490.00
$848,000.00
$159,135.00
$0.00
$3,510.00
$107,865.00
-$420,800.00
-$328,800.00
-$220,935.00
Year 3
Year 4
Year 5
$606,364.00 $636,682.00 $668,516.00
$900,577.00 $955,512.00 $1,013,798.00
$163,909.00 $168,826.00 $173,891.00
$0.00
$0.00
$0.00
$3,510.00
$3,510.00
$3,510.00
$126,794.00 $146,494.00 $167,881.00
-$94,141.00 $52,353.00 $220,234.00
Year 0
Year 1
Year 2
$523,800.00 $549,990.00 $577,490.00
$400,000.00 $800,000.00 $848,000.00
$150,000.00 $154,500.00 $159,135.00
$30,000.00
$0.00
$0.00
$117,000.00
$3,510.00
$3,510.00
-$420,800.00
$92,000.00 $107,865.00
-$420,800.00 -$328,800.00 -$220,935.00
Year 3
Year 4
Year 5
$606,364.00 $636,682.00 $668,516.00
$900,577.00 $955,512.00 $1,013,798.00
$163,909.00 $168,826.00 $173,891.00
$0.00
$0.00
$0.00
$3,510.00
$3,510.00
$3,510.00
$126,794.00 $146,494.00 $167,881.00
-$94,141.00 $52,353.00 $220,234.00
2.36 years
Discounted Period
Nurse Triage Salaries
Forecasted ER Cost Reductions
New IT Specialist’s Salary
Costs of Facility Renovations
Necessary Capital Equipment Purchases
Balance
Payback Period (# years):
Discounted payback period
Discounted cash flows
Payback
2.36 years
1 0.900900901 0.811622433 0.731191381 0.65873097
-$420,800.00
$82,882.88
$87,545.65
$92,710.68 $96,500.14
-$420,800.00 -$337,917.12 -$250,371.46 -$157,660.78 -$61,160.65
0.593451328
$99,629.20
$38,468.55
4.39 years
Inpatient
Inpatient product line Budget (FY20) Actual (FY20)
CIRC
$
DIGEST
$
ENT
$
GNY
$
MENTAL HEALTH
$
NERVOUS
$
NEWBORN
$
OB
$
ORTHO
$
OTHER
$
RESP
$
Total Inpatient expenses
5,475.81
5,476.81
5,477.81
5,478.81
498.78
5,478.81
5,478.81
5,478.81
5,478.81
5,478.81
5,478.81
$
$
$
$
$
$
$
$
$
$
$
IP Express (FY20)
690.00
1,105.00
124.00
208.00
1,287.00
217.00
698.00
1,430.00
601.00
2,050.00
645.00
$ 3,778,309.00
$ 6,051,875.00
$ 679,248.00
$ 1,139,592.00
$ 641,930.00
$ 1,188,902.00
$ 3,824,209.00
$ 7,834,698.00
$ 3,292,765.00
$ 11,231,561.00
$ 3,533,832.00
$ 43,196,922.00
Revenues Worksheet, Mid-Year Review
Inpatient
Inpatient product line Price
CIRC
$
DIGEST
$
ENT
$
GNY
$
MENTAL HEALTH
$
NERVOUS
$
NEWBORN
$
OB
$
ORTHO
$
OTHER
$
RESP
$
Total Inpatient earnings
Inpatient product
Actualline
(FY21)
CIRC
DIGEST
ENT
$421.00
$636.00
$72.00
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
Forecasted For FY21
Projected IP Revenue For FY21
$
$
$
$
$
$
$
$
$
$
$
724.00
1,161.00
130.00
219.00
1,351.00
228.00
733.00
1,501.00
631.00
2,152.00
677.00
$ 4,351,711.00
$ 6,988,125.00
$ 780,215.00
$ 1,313,983.00
$ 679,539.00
$ 1,391,632.00
$ 5,141,782.00
$ 8,576,189.00
$ 3,839,118.00
$ 12,951,037.00
$ 4,142,380.00
$
9,506.00 $ 50,155,710.00
Flexible overheads
Actual relative Actual
weighted
overhead
products
(actual
Cost
Actual
qty*actual
per unit(FY21YTD)
RWPs
cost)
(FY21)
$5,873.69
$2,472,822.00
$421.00
$6,849.33
$4,356,173.00
$636.00
$6,428.08
$462,822.00
$72.00
GNY
$116.00
MENTAL HEALTH
$736.00
NERVOUS $112.00
NEWBORN $433.00
OB
$868.00
ORTHO
$274.00
OTHER
$800.00
RESP
$197.00
$4,664.00
$6,701.05
$544.47
$6,028.33
$6,861.48
$6,305.18
$6,463.73
$5,827.85
$6,262.80
$777,322.00
$400,730.00
$675,173.00
$2,971,020.00
$5,472,892.00
$1,771,063.00
$4,662,277.00
$1,233,771.00
$116.00
$736.00
$112.00
$433.00
$868.00
$274.00
$800.00
$197.00
$25,256,065.00
$4,664.00
Therefore, the price variance= Actual overheads -Flexible
overhead, therefore, $2,055,009.23
the Quantity variance is $
1,667,748.45
Forecasted (FY21) FY 11Budget (FY20+6%
Actual
inflation)
(FY21)
$
$
$
$
$
$
$
$
$
$
$
724.00
1,161.00
130.00
219.00
1,351.00
228.00
733.00
1,501.00
631.00
2,152.00
677.00
$ 4,202,356.00
$ 6,737,188.00
$
754,842.00
$ 1,268,947.00
$
714,283.00
$ 1,324,119.00
$ 4,256,926.00
$ 8,717,115.00
$ 3,664,557.00
$ 12,497,823.00
$ 3,931,704.00
$
$
$
$
$
$
$
$
$
$
$
421.00
636.00
72.00
116.00
736.00
112.00
433.00
868.00
274.00
800.00
197.00
$
9,506.00 $ 48,069,860.00 $
4,664.00
Actual IP Expenses (FY21 YTD)
$ 303.00
$ 525.00
$ 58.00
$ 103.00
$ 615.00
$ 116.00
$ 300.00
$ 633.00
$ 357.00
$ 1,352.00
$ 480.00
$ 2,472,822.00
$ 4,356,173.00
$ 462,822.00
$ 777,322.00
$ 400,730.00
$ 675,173.00
$ 2,971,020.00
$ 5,472,892.00
$ 1,771,063.00
$ 4,662,277.00
$ 1,233,771.00
$ 25,256,062.00
Mid-Year Review
Actual (FY21 YTD) Actual IP Revenue (FY21 YTD
$
$
$
$
$
$
$
$
$
$
$
421.00
636.00
72.00
116.00
736.00
112.00
433.00
868.00
274.00
800.00
197.00
$
$
$
$
$
$
$
$
$
$
$
2,530,676.00
3,830,402.00
433,948.00
697,128.00
369,949.00
682,744.00
3,033,900.00
4,960,622.00
1,664,658.00
4,813,961.00
1,202,960.00
$
4,664.00 $ 24,220,949.00
Flexible overheads
Budgted cost per each
Flexible
unit(F11YTD)
overhead Budgeted cost-Forecasted
Budgted costBugdted
relative
per each
weighted
overheads
unit(F11YTD)
products
$5,804.36
$2,443,635.19
$303.00 $5,804.36
$1,758,720.81
$5,802.92
$3,690,655.96
$525.00 $5,802.92
$3,046,532.04
$5,806.48
$418,066.34
$58.00 $5,806.48
$336,775.66
$5,794.28
$528.71
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$672,136.31
$389,128.27
$650,444.42
$2,514,664.34
$5,040,943.25
$1,591,265.64
$4,646,030.86
$1,144,085.21
$23,201,055.77
$103.00
$615.00
$116.00
$300.00
$633.00
$357.00
$1,352.00
$480.00
$5,794.28
$528.71
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$596,810.69
$325,154.73
$673,674.58
$1,742,261.66
$3,676,171.75
$2,073,291.36
$7,851,792.14
$2,787,618.79
$24,868,804.23
Jiranna Healthcare
Expenses Worksheet, Mid-Year Review
Outpatient
Unit Expense (FY20)
Actual Units
(FY20)
Outpatient Product Line
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
PEDIATRICS
PODIATRY
Total Expenses
$ 5,478.81
$ 5,478.81
$ 5,476.81
$ 5,478.81
$ 5,475.81
$ 498.78
$ 5,478.81
$ 5,478.81
$ 5,478.81
$ 5,477.81
$ 5,478.81
$55,281
645
217
1,105
208
690
1,287
698
1,430
601
124
2,050
9,055
Total OP
Expenses
(FY20)
$ 3,533,832
$ 1,188,902
$ 6,051,875
$ 1,139,592
$ 3,778,309
$ 641,930
$ 3,824,209
$ 7,834,698
$ 3,292,765
$ 679,248
$ 11,231,561
$43,196,921
Jiranna Healthcare
Revenues Worksheet, Mid-Year Review
Outpatient
Outpatient Product Line
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
PEDIATRICS
PODIATRY
Total Revenue
Product Line
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
PEDIATRICS
PODIATRY
Unit Price (FY20)
Actual Units Total OP Revenue
(FY20)
(FY20)
$ 6,118.73
645
$ 3,946,580.85
$ 6,103.65
217
$ 1,324,492.05
$ 6,021.65
1,105
$ 6,653,923.25
$ 6,013.65
208
$ 1,250,839.20
$ 6,010.65
690
$ 4,147,348.50
$ 502.99
1,287
$ 647,348.13
$ 7,014.71
698
$ 4,896,267.58
$ 5,713.65
1,430
$ 8,170,519.50
$ 6,084.18
601
$ 3,656,592.18
$ 6,001.65
124
$ 744,204.60
$ 6,018.14
2,050
$ 12,337,187.00
$61,604
9,055
$47,775,303
MDC DescrOPtion
Diseases and Disorders of the Circulatory System (Hypertension, Heart Disease, etc.)
Diseases and Disorders of the Endocrin System (Diabetes Type 1 & 2, Thyroid Disorders, etc)
Family Primary Care and Disease Preventative Services
Diseases and Disorders of Women’s Reproductive Health
Diseases and Disorders of the Ear, Nose, Mouth, and Throat
Mental Diseases and Disorders
Newborns and Other Neonates with Conditions Originating in Perinatal Period
Pregnancy, Childbirth, and the Puerperium
Diseases and Disorders of the Musculoskeletal System and Connective Tissue
Pediatric Primary Care and Disease Prevention Services (0 – 18 years)
Diseases and Disorders of the Feet and Lower Limbs
Jiranna Healthcare
Expenses Worksheet, Mid-Year Review
Unit
Forecast
(FY21)
677
228
1,161
219
724
1,351
733
1,501
631
130
2,152
9,507
Unit
FY21 Budget (FY20+6% Inflation)
Acutal Mid
Expence
Year Units
(Budget
(FY21)
FY21)
$5,807.54
$ 3,931,704
197
$5,807.54
$ 1,324,119
112
$5,802.92
$ 6,737,188
636
$5,794.28
$ 1,268,947
116
$5,804.36
$ 4,202,356
421
$528.71
$ 714,283
736
$5,807.54
$ 4,256,926
433
$5,807.54
$ 8,717,115
868
$5,807.54
$ 3,664,557
274
$5,806.48
$ 754,842
72
$5,807.54
$ 12,497,823
800
$58,582
$48,069,860
4,665
Jiranna Healthcare
Revenues Worksheet, Mid-Year Review
Unit Forecasted
Unit Price (Budget Projected OP Revenue For FY21 Actual
For (FY21)
FY21)
(FY21 YTD)
677
$6,118.73
$ 4,142,380
197
228
$6,103.65
$ 1,391,632
112
1,161
$6,019.06
$ 6,988,125
636
219
$5,999.92
$ 1,313,983
116
724
$6,010.65
$ 4,351,711
421
1,351
$502.99
$ 679,539
736
733
$7,014.71
$ 5,141,782
433
1,501
$5,713.65
$ 8,576,189
868
631
$6,084.18
$ 3,839,118
274
130
$6,001.65
$ 780,215
72
2,152
$6,018.14
$ 12,951,037
800
9,507
$61,587
$50,155,711
4,665
pertension, Heart Disease, etc.)
tes Type 1 & 2, Thyroid Disorders, etc)
ating in Perinatal Period
and Connective Tissue
Actual OP Expenses
(FY21 YTD)
$ 1,144,085
$ 650,444
$ 3,690,656
$ 672,136
$ 2,443,635
$ 389,128
$ 2,514,664
$ 5,040,943
$ 1,591,266
$ 418,066
$ 4,646,031
$23,201,056
Actual OP Revenue
(FY21 YTD)
$ 1,205,390
$ 683,609
$ 3,828,120
$ 695,991
$ 2,530,484
$ 370,200
$ 3,037,369
$ 4,959,448
$ 1,667,066
$ 432,119
$ 4,814,512
$24,224,308
Projected FY 11
Revenues
Expences
Difference
Revenues
Inpatient
Inpatient product line
CIRC
DIGEST
ENT
GNY
MENTAL HEALTH
NERVOUS
NEWBORN
OB
ORTHO
OTHER
RESP
Total Inpatient earnings
Revenues
-$1,028,515.55
Inpatient product line
CIRC
DIGEST
ENT
GNY
MENTAL HEALTH
NERVOUS
NEWBORN
OB
ORTHO
OTHER
RESP
Total Inpatient expenses
Inpatient product line
CIRC
DIGEST
ENT
Actual relative weighted products Cost per unit(FY21YTD)
$5,873.69
$6,849.33
$6,428.08
GNY
MENTAL HEALTH
NERVOUS
NEWBORN
OB
ORTHO
OTHER
RESP
$6,701.05
$544.47
$6,028.33
$6,861.48
$6,305.18
$6,463.73
$5,827.85
$6,262.80
YTD Actual
Projected Actual 2019
Price
Forecasted RWPs For FY21
$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
IP Expenses FY20
$3,778,308.90
$6,051,875.05
$679,248.44
$1,139,592.48
$641,929.86
$1,188,901.77
$3,824,209.38
$7,834,698.30
$3,292,764.81
$11,231,560.50
$3,533,832.45
$43,196,921.94
Projected revenue
$724.00
$1,161.00
$130.00
$219.00
$1,351.00
$228.00
$733.00
$1,501.00
$631.00
$2,152.00
$677.00
$4,351,710.60
$6,991,135.65
$780,214.50
$1,316,989.35
$679,539.49
$1,391,632.20
$5,141,782.43
$8,576,188.65
$3,839,117.58
$12,951,037.28
$4,142,380.21
$9,506.00
$50,161,727.94
Forecasted RWPs FY21
Projected revenue FY21
$724.00
$4,351,710.60
$1,161.00
$6,991,135.65
$130.00
$780,214.50
$219.00
$1,316,989.35
$1,351.00
$679,539.49
$228.00
$1,391,632.20
$733.00
$5,141,782.43
$1,501.00
$8,576,188.65
$631.00
$3,839,117.58
$2,152.00
$12,951,037.28
$677.00
$4,142,380.21
$9,507.00
$50,161,727.94
Budgted cost per each unit(F11YTD)
Overbudget
$5,804.36
$5,802.92
$5,806.48
$70.77
$1,042.85
$633.80
$5,794.28
$528.71
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$5,807.54
$6,172.34
-$5,263.07
$220.79
$1,053.94
$497.64
$656.20
$20.31
$6,262.80
Actual RWPs (FY21 YTD)
Actual IP revenue (FY21YTD) Actual IP Expenses
$421.00
$636.00
$72.00
$116.00
$736.00
$112.00
$433.00
$868.00
$274.00
$800.00
$197.00
$2,530,483.65
$3,829,769.40
$432,118.80
$697,583.40
$370,200.64
$683,608.80
$3,037,369.43
$4,959,448.20
$1,667,065.32
$4,814,512.00
$1,205,389.81
$2,472,822.00
$4,356,173.00
$462,822.00
$777,322.00
$400,730.00
$675,173.00
$2,971,020.00
$5,472,892.00
$1,771,063.00
$4,662,277.00
$1,233,771.00
$24,227,549.45
$25,256,065.00
FY21 budget FY20+6%inflation projected profit FY21
$4,202,356.00
$149,354.60
$6,737,188.00
$253,947.65
$754,842.00
$25,372.50
$1,268,947.00
$48,042.35
$714,283.00
-$34,743.51
$1,324,119.00
$67,513.20
$4,256,926.00
$884,856.43
$8,717,115.00
-$140,926.35
$3,664,557.00
$174,560.58
$12,497,823.00
$453,214.28
$3,931,704.00
$210,676.21
$48,069,860.00
$2,091,867.94
Actual profit (FY21YTD) Actual cost per unit (FY21YTD)
$57,661.65
-$526,403.60
-$30,703.20
-$79,738.60
-$30,529.36
$8,435.80
$66,349.43
-$513,443.80
-$103,997.68
$152,235.00
-$28,381.19
-$1,028,515.55
$5,873.69
$6,849.33
$6,428.08
$6,701.05
$544.47
$6,028.33
$6,861.48
$6,305.18
$6,463.73
$5,827.85
$6,262.80
Jiranna Healthcare
Projected Profit
Projected
Projected (FY21)
(mid-year)
Actual
(mid-year)
Revenue
Expenses
Profit
Outpatient Product Line
Jiranna Healthcare
FY21 YTD Expense Variances
I
II
Actual OP
FY21 YTD Budget Expenses (FY21
YTD)
III
Expense Variance
(I-II)
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
PEDIATRICS
PODIATRY
Total Outpatient Expenses
Outpatient Product Line
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
Jiranna Healthcare
Revenue Variances
I
II
Actual OP
FY21 YTD Budget
Revenue (FY21
YTD)
III
Revenue Variance
(I-II)
PEDIATRICS
PODIATRY
Total Outpatient Revenues
Outpatient Product Line
CARDIOLOGY
ENDOCRINOLOYGY
FAMILY MED
GYN
ENT
MENTAL HEALTH
NEWBORN
OB
ORTHO
PEDIATRICS
PODIATRY
Total Profit
Jiranna Healthcare
Profit Variances
I
II
FY21 YTD Budget Actual OP Profit
Profit
(FY21 YTD)
III
Profit Variance
(I-II)
Revised FY21
Projection
IV
Favorable /
Unfavorable
IV
Favorable /
Unfavorable
IV
F/U
Jiranna Healthcare Income Statement December 31, 2021 (in thousands)
Gross patient services revenues (non-GAAP)
Less deductions from revenues (non-GAAP)
Net patient service revenues
Other operating revenues
Total operating revenues
2017
8,870
(780)
8,090
519
8,609
2018
9,490
(890)
8,600
633
9,233
2019
10,400
(1,000)
9,400
679
10,079
2020
11,200
(1,500)
9,700
717
10,417
Operating expenses
Salaries and wages
Supplies
Utilities
Insurance
Depreciation
Interest
Bad debts
Other operating expenses
Total operating expenses
5,497
823
558
44
168
142
363
987
8,582
5,678
850
576
46
173
146
375
1,299
9,143
5,890
855
590
49
175
154
400
1,560
9,673
6,170
890
595
54
178
179
455
1,300
9,821
Operating income
Nonoperating income
Excess of revenue over expenses
Change in net assets
Unrestricted
Temporarily restricted
Permanently restricted
Total change in net assets
$
$
27
154
181 $
90
195
285 $
406
245
651 $
596
220
816
181 $
181
285 $
285
651 $
651
816
816
2021
12,050
(1,600)
10,450
980
11,430
6,800
905
620
70
188
198
500
1,350
10,631
$
$
799
290
1,089
1,089
1,089
Jiranna Healthcare Balance Sheet December 31, 2021 (in thousands)
Current assets
Cash
Receivables, net
Inventory
Prepaid expenses
Total Current Assets
Short-term investments
Plant and equipment
Less accumulated depreciation
Plants and equipment, net
Total Assets
$
2017
280 $
30
1,340
140
40
1,830
600
6,580
-1,660
2018
124 $
45
1,536
175
32
1,912
2019
136 $
50
1,700
250
40
2,176
2020
295
75
1,896
276
53
2,595
1,010
1,200
1,300
6,780
-1750
6,900
-1,800
7,200
-1,950
4,920
7,350 $
5,030
7,952 $
5,100
8,476 $
5,250
9,145
Current liabilities
Accounts payable
Accrued expenses payable
Deferred revenues
Total current liabilities
Long-term liabilities
Total Liabilities
370
220
60
650
2,400
3,050
302
208
77
587
3,000
3,587
356
212
87
655
3,300
3,955
370
210
87
667
3,500
4,167
Net assets
Unrestricted
Temporarily restricted
Permanently restricted
Total Net Assets
Total Liabilities and Net Assets
3,000
700
600
4,300
7,350
3,285
700
600
4,585
8,172
3,221
700
600
4,521
8,476
3,678
700
600
4,978
9,145
$
$
2021
355
88
2,400
266
78
3,187
1,510
7,500
-2,350
$
5,150
9,847
375
215
94
684
3,750
4,434
4,113
700
600
5,413
9,847
Jiranna Healthcare Cash Flows, 2021 (in thousands)
Cash Flows from Operating Activities
Cash received from patient and third-party payers
Cash received from operating revenue sources
Cash received from nonoperating revenue sources
Cash payments to employees
Cash payments to suppliers of goods and services
Net cash flow from operating activities
Cash flows from Investing Activities
Cash payments for purchase of plant assets
Cash payments for purchase of long-term investments
Proceeds from sales of plant assets
Proceeds from sale of long-term investments
Net cash flow from investing activities
Cash flows from Financing Activities
Proceeds from issuance of 6% bonds payable
Principal payments on long-term debt
Cash payments to retire 7% bonds payable
Net cash flow from financing activities
Net increase/(decrease) in cash
$
10,671
800
270
(5,600)
(4,800)
$
1,341
(1,200)
(670)
80
60
(1,730)
4,000
(300)
(3,200)
$
500
111
Jiranna Financials
Total Net Sales
Operating Expenses
Operating Income
Net Income
2017
8,609
2018
9,233
2019
10,079
2020
10,417
2021
11,430
Profitability
Total Profit Margin
Asset Turnover
Return on Assets
Return on Net Worth
2017
2018
2019
2020
2021
2017
2018
2019
2020
2021
2017
2018
2019
2020
2021
2017
2018
2019
2020
2021
Liquidity Analysis
Current Ratio
Days Cash on Hand
Working Capital
Longterm Solvency
Debt Ratio
Times Interest Earned
DuPont
Net Profit Margin
Asset Turnover
Equity Multiplier
DuPont Analysis
*example of how pull numbers from other worksheets
Nurse Triage Salaries
Forecasted ER Cost Reductions
New IT Specialist’s Salary
Costs of Facility Renovations
Necessary Capital Equipment Purchases
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):
Year 0
Year 1
$ 523,800
$ 400,000
$ 150,000
$ 30,000
$ 117,000
$ 549,990
$ 800,000
$ 154,500
$
$ 3,510
Year 2
$
$
$
$
$
577,490
848,000
159,135
3,510
Year 3
Year 4
Year 5
$ 606,364
$ 900,577
$ 163,909
$
$ 3,510
$ 636,682
$ 955,512
$ 168,826
$
$ 3,510
$ 668,516
$ 1,013,798
$ 173,891
$
$
3,510
Calculation for Discounted Payback Period
Net Cash Flow:
PV cash flow
Cumulative cash flow
Breakeven/ Disc cum Cum …