Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jun 28, 2024

Question 2: Calculate the provided 15 ratios for BOTH hospitals The first ratio calculation has been completed for you as an example. Areas in which

Question 2: Calculate the provided 15 ratios for BOTH hospitals The first ratio calculation has been completed for you as an example. Areas in which you are required to input data are shaded in blue.

Ratio Calculation Procedure Steps Entering Excel Formula Help Video

1. Write the formula. https://youtu.be/4HdZx29SdoM

2. Use excel formulas to do the calculation * See help video - link in Cell D4

3. Format the answer

4. In the cell immediately below each set of calculations it will say "Correct" if your

calculated value in part 2 is correct. If it is incorrect a red highlight will show "incorrect".

1 (Example) Ratio Name Procedure Step 1. Write the formula Explain what the formula means

Current Ratio Current Assets/Current Liabilities The current ratio is a liquidity ratio that measures a company's ability to pay short-term obligations or those due within one year. It tells investors and analysts how a company can maximize the current assets on its balance sheet to satisfy its current debt and other payables.

Pima Medical Center Tucson Regional

Procedure Step FY2019 FY2020 FY2021 FY2021

2. Use excel formulas to do the calculation 0.946957 0.963350 0.964296 1.661200

3. Format the answer 0.95 0.96 0.96 1.66

Correct Correct Correct Correct

Calculate the given 15 Ratios for BOTH hospitals.

2 Ratio Name Procedure Step 1. Write the formula Explain what the formula means

Acid Test Ratio

Pima Medical Center Tucson Regional

Procedure Step FY2019 FY2020 FY2021 FY2021

2. Use excel formulas to do the calculation

3. Format the answer

Incorrect Incorrect Incorrect Incorrect

3 Ratio Name Procedure Step 1. Write the formula Explain what the formula means

Profit Margin

Pima Medical Center Tucson Regional

Procedure Step FY2019 FY2020 FY2021 FY2021

2. Use excel formulas to do the calculation

3. Format the answer

Incorrect Incorrect Incorrect Incorrect

4 Ratio Name Procedure Step 1: Write the formula Explain what the formula means

Operating Margin

Pima Medical Center Tucson Regional

Procedure Step FY2019 FY2020 FY2021 FY2021

2. Use excel formulas to do the calculation

3. Format the answer

Incorrect Incorrect Incorrect Incorrect

5 Ratio Name Procedure Step 1: Write the formula Explain what the formula means

Return on Assets

Pima Medical Center Tucson Regional

Procedure Step FY2019 FY2020 FY2021 FY2021

2. Use excel formulas to do the calculation

3. Format the answer

Incorrect Incorrect Incorrect Incorrect

6 Ratio Name Procedure Step 1: Write the formula Explain what the formula means

Inventory Turnover

Pima Medical Center Tucson Regional

Procedure Step FY2019 FY2020 FY2021 FY2021

2. Use excel formulas to do the calculation

3. Format the answer

Incorrect Incorrect Incorrect Incorrect

7 Ratio Name Procedure Step 1: Write the formula Explain what the formula means

Days in Inventory

Pima Medical Center Tucson Regional

Procedure Steps FY2019 FY2020 FY2021 FY2021

2. Use excel formulas to do the calculation

3. Format the answer

Incorrect Incorrect Incorrect Incorrect

8 Ratio Name Procedure Step 1: Write the formula Explain what the formula means

Days in Accounts Receivable

Pima Medical Center Tucson Regional

Procedure Steps FY2019 FY2020 FY2021 FY2021

2. Use excel formulas to do the calculation

3. Format the answer

Incorrect Incorrect Incorrect Incorrect

9 Ratio Name Procedure Step 1: Write the formula Explain what the formula means

AR Turnover

Pima Medical Center Tucson Regional

Procedure Steps FY2019 FY2020 FY2021 FY2021

2. Use excel formulas to do the calculation

3. Format the answer

Incorrect Incorrect Incorrect Incorrect

10 Ratio Name Procedure Step 1: Write the formula Explain what the formula means

Days in Accounts Payable

Pima Medical Center Tucson Regional

Procedure Steps FY2019 FY2020 FY2021 FY2021

2. Use excel formulas to do the calculation

3. Format the answer

Incorrect Incorrect Incorrect Incorrect

11 Ratio Name Procedure Step 1: Write the formula Explain what the formula means

Cash Conversion Cycle

Pima Medical Center Tucson Regional

Procedure Steps FY2019 FY2020 FY2021 FY2021

2. Use excel formulas to do the calculation

3. Format the answer

Incorrect Incorrect Incorrect Incorrect

12 Ratio Name Procedure Step 1: Write the formula Explain what the formula means

Debt Ratio

Pima Medical Center Tucson Regional

Procedure Steps FY2019 FY2020 FY2021 FY2021

2. Use excel formulas to do the calculation

3. Format the answer

Incorrect Incorrect Incorrect Incorrect

13 Ratio Name Procedure Step 1: Write the formula Explain what the formula means

Times Interest Earned

Pima Medical Center Tucson Regional

Procedure Steps FY2019 FY2020 FY2021 FY2021

2. Use excel formulas to do the calculation

3. Format the answer

Incorrect Incorrect Incorrect Incorrect

14 Ratio Name Procedure Step 1: Write the formula Explain what the formula means

Debt to Equity Ratio

Pima Medical Center Tucson Regional

Procedure Steps FY2019 FY2020 FY2021 FY2021

2. Use excel formulas to do the calculation

3. Format the answer

Incorrect Incorrect Incorrect Incorrect

15 Ratio Name Procedure Step 1: Write the formula Explain what the formula means

Days Cash on Hand

Pima Medical Center Tucson Regional

Procedure Steps FY2019 FY2020 FY2021 FY2021

2. Use excel formulas to do the calculation

3. Format the answer

Incorrect Incorrect Incorrect Incorrect

Hospital Financial Statements

Financial Statements

Income Statements Pima Medical Center Tucson Regional

2019 2020 2021 2021

Revenues $51,336 $51,533 58,752 11,789

Salaries and Benefits 23,560 23,874 26,779 5,411

Supplies Expense 8,481 8,369 9,481 1,963

Other Operating Expenses 9,481 9,307 9,961 2,857

Depreciation/Amortization 2,596 2,721 2,853 458

Interest Expense 1,824 1,584 1,566 1,031

Total Operating Expenses 45,942 45,855 50,640 11,720

Operating Income 5,394 5,678 8,112 69

Income Taxes 1,099 1,043 2,112 14

Net Income 4,295 4,635 6,000 55

Note: Amounts in 1,000s of dollars

Balance Sheets

2019 2020 2021 2021

Assets

Cash 621 1,793 1,451 1,676

Short-Term Investments 147 116 103 -

Accounts Receivables, Net 7,333 7,159 8,095 1,927

Supplies Inventory 1,849 2,025 1,986 335

Other Current Assets 1,190 1,340 1,896 519

Total Current Assets 11,140 12,433 13,531 4,457

Net Property, Plant & Equipment 24,549 25,223 26,176 5,322

Long-Term Note Receivable 37 31 44 59

Intangible Assets 8,269 8,578 9,540 4,219

Total Assets 43,995 46,265 49,291 14,057

Liabilities & Shareholders' Equity

Accounts Payable 2,357 2,581 2,842 383

Short Term Debt 145 209 237 980

Current Portion of Long Term Debt 4,905 5,535 6,111 533

Other Current Liabilities 2,303 2,267 2,461 150

Accrued Payroll 2,054 2,314 2,381 637

Total Current Liabilities 11,764 12,906 14,032 2,683

Long-Term Debt 23,107 20,301 23,765 11,033

Total Liabilities 34,871 33,207 37,797 13,716

Total Shareholders' Equity

9,124 13,058 11,494 341

Liabilities & Shareholders' Equity 43,995 46,265 49,291 14,057

Step by Step Solution

There are 3 Steps involved in it

Step: 1

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

Step: 3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Statistics Unlocking The Power Of Data

Authors: Robin H. Lock, Patti Frazer Lock, Kari Lock Morgan, Eric F. Lock, Dennis F. Lock

1st Edition

0470601876, 978-0470601877

Students also viewed these Accounting questions

Question

★★★★★

Which, if any, of the following situations represent improper segregation of functions? a. The billing department prepares the customers invoices, and the accounts receivable department posts to the...

Answered: 1 week ago

Question

★★★★★

How does Juan Huarte serve as a pioneer in the study of individual differences?

Answered: 1 week ago

Question

★★★★★

Westan Corporation uses a predetermined overhead rate of $23.10 per direct labor-hour. This predetermined rate was based on 12,000 estimated direct labor-hours and $277,200 of estimated total...

Answered: 1 week ago

Question

★★★★★

Develop a context diagram and as many nested DFDs as you consider necessary to represent all the details of the training logistics system described in the following narra- tive. If you discover that...

Answered: 1 week ago

Question

★★★★★

Why is this wrong and how do I fix it? Pretzel Corporation owns 60 percent of Stick Corporation's voting shares. On January 1, 20X2, Pretzel Corporation sold $150,000 par value, 6 percent first...

Answered: 1 week ago

Question

★★★★★

Michelle Walker is the sole owner of Sandhill Vista Park, a public camping ground near the Crater Lake National Recreation Area. Michelle has compiled the following financial information as of...

Answered: 1 week ago

Question

★★★★★

The chart of accounts used by Notwist Copy Company is listed below. You are to indicate the proper accounts to be debited and credited for the following transactions. CHART OF ACCOUNTS Cash Accounts...

Answered: 1 week ago

Question

★★★★★

To influence the way in which managers make decisions within organisations, which of the following approaches should be adopted?" Performance measurement system. Reward structure. Performance...

Answered: 1 week ago

Question

★★★★★

Tutorial 10: Real Trigonometric Integrals Using a suitable contour integral, evaluate the following trigonometric integrals. 2T 2T 1. S cos 30 de 2. 5 - 4 cos 0 So 4 de 5+4 sin 0 2T 2T de de 4. 3. 2...

Answered: 1 week ago

Question

★★★★★

Personal Relationship O Criminal Intent Q.13. What type of Workplace Violence does this scenario match? Scenario: An Emergency Department patient kicks the attending doctor in the chest...

Answered: 1 week ago

Previous Question Next Question

Question 2: Calculate the provided 15 ratios for BOTH hospitals The first ratio calculation has been completed for you as an example. Areas in which you are required to input data are shaded in blue.

Ratio Calculation Procedure Steps		Entering Excel Formula Help Video
1. Write the formula.		https://youtu.be/4HdZx29SdoM
2. Use excel formulas to do the calculation * See help video - link in Cell D4
3. Format the answer
4. In the cell immediately below each set of calculations it will say "Correct" if your
calculated value in part 2 is correct. If it is incorrect a red highlight will show "incorrect".

1 (Example)	Ratio Name	Procedure Step 1. Write the formula	Explain what the formula means
Current Ratio	Current Assets/Current Liabilities	The current ratio is a liquidity ratio that measures a company's ability to pay short-term obligations or those due within one year. It tells investors and analysts how a company can maximize the current assets on its balance sheet to satisfy its current debt and other payables.
	Pima Medical Center	Tucson Regional
Procedure Step	FY2019	FY2020	FY2021	FY2021
2. Use excel formulas to do the calculation	0.946957	0.963350	0.964296	1.661200
3. Format the answer	0.95	0.96	0.96	1.66
	Correct	Correct	Correct	Correct
Calculate the given 15 Ratios for BOTH hospitals.
2	Ratio Name	Procedure Step 1. Write the formula	Explain what the formula means
Acid Test Ratio
	Pima Medical Center	Tucson Regional
Procedure Step	FY2019	FY2020	FY2021	FY2021
2. Use excel formulas to do the calculation
3. Format the answer
	Incorrect	Incorrect	Incorrect	Incorrect

3	Ratio Name	Procedure Step 1. Write the formula	Explain what the formula means
Profit Margin
	Pima Medical Center	Tucson Regional
Procedure Step	FY2019	FY2020	FY2021	FY2021
2. Use excel formulas to do the calculation
3. Format the answer
	Incorrect	Incorrect	Incorrect	Incorrect

4	Ratio Name	Procedure Step 1: Write the formula	Explain what the formula means
Operating Margin
	Pima Medical Center	Tucson Regional
Procedure Step	FY2019	FY2020	FY2021	FY2021
2. Use excel formulas to do the calculation
3. Format the answer
	Incorrect	Incorrect	Incorrect	Incorrect

5	Ratio Name	Procedure Step 1: Write the formula	Explain what the formula means
Return on Assets
	Pima Medical Center	Tucson Regional
Procedure Step	FY2019	FY2020	FY2021	FY2021
2. Use excel formulas to do the calculation
3. Format the answer
	Incorrect	Incorrect	Incorrect	Incorrect

6	Ratio Name	Procedure Step 1: Write the formula	Explain what the formula means
Inventory Turnover
	Pima Medical Center	Tucson Regional
Procedure Step	FY2019	FY2020	FY2021	FY2021
2. Use excel formulas to do the calculation
3. Format the answer
	Incorrect	Incorrect	Incorrect	Incorrect

7	Ratio Name	Procedure Step 1: Write the formula	Explain what the formula means
Days in Inventory
	Pima Medical Center	Tucson Regional
Procedure Steps	FY2019	FY2020	FY2021	FY2021
2. Use excel formulas to do the calculation
3. Format the answer
	Incorrect	Incorrect	Incorrect	Incorrect

8	Ratio Name	Procedure Step 1: Write the formula	Explain what the formula means
Days in Accounts Receivable
	Pima Medical Center	Tucson Regional
Procedure Steps	FY2019	FY2020	FY2021	FY2021
2. Use excel formulas to do the calculation
3. Format the answer
	Incorrect	Incorrect	Incorrect	Incorrect

9	Ratio Name	Procedure Step 1: Write the formula	Explain what the formula means
AR Turnover
	Pima Medical Center	Tucson Regional
Procedure Steps	FY2019	FY2020	FY2021	FY2021
2. Use excel formulas to do the calculation
3. Format the answer
	Incorrect	Incorrect	Incorrect	Incorrect

10	Ratio Name	Procedure Step 1: Write the formula	Explain what the formula means
Days in Accounts Payable
	Pima Medical Center	Tucson Regional
Procedure Steps	FY2019	FY2020	FY2021	FY2021
2. Use excel formulas to do the calculation
3. Format the answer
	Incorrect	Incorrect	Incorrect	Incorrect

11	Ratio Name	Procedure Step 1: Write the formula	Explain what the formula means
Cash Conversion Cycle
	Pima Medical Center	Tucson Regional
Procedure Steps	FY2019	FY2020	FY2021	FY2021
2. Use excel formulas to do the calculation
3. Format the answer
	Incorrect	Incorrect	Incorrect	Incorrect

12	Ratio Name	Procedure Step 1: Write the formula	Explain what the formula means
Debt Ratio
	Pima Medical Center	Tucson Regional
Procedure Steps	FY2019	FY2020	FY2021	FY2021
2. Use excel formulas to do the calculation
3. Format the answer
	Incorrect	Incorrect	Incorrect	Incorrect

13	Ratio Name	Procedure Step 1: Write the formula	Explain what the formula means
Times Interest Earned
	Pima Medical Center	Tucson Regional
Procedure Steps	FY2019	FY2020	FY2021	FY2021
2. Use excel formulas to do the calculation
3. Format the answer
	Incorrect	Incorrect	Incorrect	Incorrect

14	Ratio Name	Procedure Step 1: Write the formula	Explain what the formula means
Debt to Equity Ratio
	Pima Medical Center	Tucson Regional
Procedure Steps	FY2019	FY2020	FY2021	FY2021
2. Use excel formulas to do the calculation
3. Format the answer
	Incorrect	Incorrect	Incorrect	Incorrect

15	Ratio Name	Procedure Step 1: Write the formula	Explain what the formula means
Days Cash on Hand
	Pima Medical Center	Tucson Regional
Procedure Steps	FY2019	FY2020	FY2021	FY2021
2. Use excel formulas to do the calculation
3. Format the answer
	Incorrect	Incorrect	Incorrect	Incorrect

Hospital Financial Statements


Financial Statements
Income Statements		Pima Medical Center	Tucson Regional
		2019	2020	2021	2021
	Revenues	$51,336	$51,533	58,752	11,789

	Salaries and Benefits	23,560	23,874	26,779	5,411
	Supplies Expense	8,481	8,369	9,481	1,963
	Other Operating Expenses	9,481	9,307	9,961	2,857
	Depreciation/Amortization	2,596	2,721	2,853	458
	Interest Expense	1,824	1,584	1,566	1,031
	Total Operating Expenses	45,942	45,855	50,640	11,720
	Operating Income	5,394	5,678	8,112	69
	Income Taxes	1,099	1,043	2,112	14
	Net Income	4,295	4,635	6,000	55



Note: Amounts in 1,000s of dollars

Balance Sheets
		2019	2020	2021	2021
	Assets
	Cash	621	1,793	1,451	1,676
	Short-Term Investments	147	116	103	-
	Accounts Receivables, Net	7,333	7,159	8,095	1,927
	Supplies Inventory	1,849	2,025	1,986	335
	Other Current Assets	1,190	1,340	1,896	519
	Total Current Assets	11,140	12,433	13,531	4,457

	Net Property, Plant & Equipment	24,549	25,223	26,176	5,322
	Long-Term Note Receivable	37	31	44	59
	Intangible Assets	8,269	8,578	9,540	4,219
	Total Assets	43,995	46,265	49,291	14,057

	Liabilities & Shareholders' Equity
	Accounts Payable	2,357	2,581	2,842	383
	Short Term Debt	145	209	237	980
	Current Portion of Long Term Debt	4,905	5,535	6,111	533
	Other Current Liabilities	2,303	2,267	2,461	150
	Accrued Payroll	2,054	2,314	2,381	637
	Total Current Liabilities	11,764	12,906	14,032	2,683
	Long-Term Debt	23,107	20,301	23,765	11,033
	Total Liabilities	34,871	33,207	37,797	13,716

	Total Shareholders' Equity
	9,124	13,058	11,494	341

	Liabilities & Shareholders' Equity	43,995	46,265	49,291	14,057