Conduct a financial analysis using the three scenarios provided in the Excel Assignment Workbook. These are not based on your project but will help you learn the process. Complete th

Conduct a financial analysis using the three scenarios provided in the Excel Assignment Workbook. These are not based on your project but will help you learn the process.

Complete the following:

Ratio Analyses Worksheets:

Open your Excel Assignment Workbook. This assignment will be completed on 3 separate tabs named:

W8A5a Expense Forecasting
W8A5b Breakeven Analysis
W8A5c Marginal Profit and Loss

Using the Healthcare Budget Request Guide for guidance, complete the three scenarios: expense forecasting, break-even, marginal profit and loss for the scenarios provided.

USW1_NURS_6211_Excel_Assignment_Workbook_student.xlsx

W1A1 HealthWaysBudget

Table 1. HealthWays Clinic, Monthly Expense Budget Report, June 2018.
Item	June 2018			May 2018	2018 YTD
	Budget	Actual	Difference	Actual	Budget	Actual	All blue shaded cells require your answers.
Physician FTE	1.0	1.0		1.0	1.0	1.0
Nurse PractitionerFTE	3.0	3.0		3.0	3.0	3.0
Encounters:
Established patients	275	291		286	1650	1671
New patients	25	18		27	150	164
Total encounters
Expenses:
Physician Salaries & Benefits	$10,500	$10,502		$10,509	$63,000	$63,149
NP Salaries & Benefits	$20,000	$20,992		$20,191	$120,000	$122,001
Clerical (2 FTE) Salaries & Benefits	$6,667	$6,771		$6,683	$40,000	$41,978
Total personnel expense
Medical supplies	$7,500	$8,136		$7,994	$45,000	$47,883
Office supplies	$623	$583		$508	$3,498	$3,407
Rent	$2,917	$2,917		$2,917	$17,502	$17,502
Depreciation	$333	$346		$346	$1,998	$2,050
Capital Expenses	$3,333	$3,480		$3,480	$19,998	$20,439
Overhead	$167	$167		$167	$1,002	$1,002
Total non-personnel expense
Total health center expense
Interpretation:
I. Answer the following question related to the results of your calculations: What interpretations can you make based on the data? What is happening in regard to such measurables as:
1. The full-time equivalents (FTE) for HealthWay employees:
1. Answer:
2. The number of encounters, both new and established:
2. Answer:
3. Non-personnel expenses:
3. Answer:
4.Total expenses:
4. Answer:
II. If these trends continue, what could it mean for HealthWays? What strategies might they employ to address any issues your analysis suggests?
Answer:

W2A2 Practice Design

Refer to the Healthcare Budget Guide for an example of what to include and how it should look.

W4A3 Estimated Expenses

Refer to the Healthcare Budget Guide for an example of what to include and how it should look.

W6A4 Budget Development

Bring forward your work from W4A3 and add ratios as directed in the Healthcare Budget Guide

W8A5a Expense forecasting

W8A5 Estimated Expenses
Refer to the Healthcare Budget Guide for directions on completing this Expense Forecasting scenario
Expense Forecasting
Based on the information provided, prepare an expense forecast for 20X1 using the template below:
Spending during January- June 20X1 (6 months)
· Fixed expense items: $210,000
· Variable expense items: $1,200,000
· One time expense: $50,000 of fixed expense money was spent on preparing for a Joint Commission survey
Procedures preformed during January- June 20X1 (6 months)
· Your department has performed 20,000 procedures during the first six months
On November 1,20X1, two new procedure technicians will begin work. The salary and fringe benefit costs for each is:				$ 96,000.00	yearly
Description	Fixed	Variable	TOTAL
Year to Date Expense
Adjustments
Add back "One Time" credits
Deduct "one Time" expenses
Adjusted total for year to date expense
Annualization
Divide by months (fixed)	6
Multiple by months (fixed)	12
Divide by volume		20,000
Multiply by volume		40,000
Annualized Amounts
Adjustments
Add back "One Time" expenses
Deduct "One Time" credits
Expense two new technicians
Expense Forecast as of 12/31/X1

W8A5b Breakeven Analysis

W8A5 Breakeven Analysis

Refer to the Healthcare Budget Guide for directions on completing this Breakeven Analysis

Break-Even Analysis Scenario

You can charge $1,075 for a new service. Demand is anticipated to be 8,000 units a year. Your business is able to handle up to 16,500 units annually, so capacity should not be a problem. The average collection rate is 80%. The new service has annual fixed costs of $4,700,000. Variable cost per unit of service is $420.

Price to be Charged

Collection Rate

Average Collection per Service

Variable cost per unit of service

Fixed Operating Costs

Break-Even Point =Fixed Cost/(Net Revenue per Unit-Variable Cost per Unit)

Capacity:

Demand:

Breakeven:

Question: Use break-even analysis to determine if this new service is financially viable. If the business is not financially viable, what steps could you take to make a case to proceed with implementation? Explain your decision.

Answer:

W8A5c Marginal Profit and Loss

W8A5 Marginal Profit and Loss
Refer to the Healthcare Budget Guide for directions on completing this Marginal Profit and Loss scenario
Marginal Profit and Loss Statement Scenario
You are examining a proposal for a new business opportunity – a new procedure for which demand is expected to be 1,400 units the first year, growing by 600 units each year thereafter. The price charged per procedure is $1,000. The collection rate is anticipated to be 80%. Each procedure consumes $300 of supplies. Salary cost is estimated to cost $540,000 each year, fringe benefits are 25% of salaries, rent for the facility is $55,000/yr and operating cost are $120,000/yr.
	Year One	Year Two	Year Three	Year Four	Year Five
Marginal Revenue:
Units of Volume
Price Procedure
Collection Rate
Marginal Net Revenue
Marginal Costs:
Variable Costs
Units of Volume
Variable Cost Supplies per Unit/procedure
Marginal Variable Cost
Fixed Costs:
Salary Costs
Fringe Benefits
Rent
Operating Cost
Marginal Fixed Costs
Total Marginal Costs
Annual Marginal Profit
Cumulative Profit Margin
Question: Below is a marginal P&L for this business opportunity. Based on that analysis, should this opportunity be pursued. Explain your decision.
Answer:

W10-11A6 HealthWays Financials

Option 1 Healthways Finacials			* The cells where you complete these calculations are highlighted in blue.
You have 2 data options for completing the Week10/11A6 analysis. If you cannot obtain the finacial documents for your organization (your project) use this Healthways Financials option.
Nurse-Run Clinic Scenario
Patient Encounters	FY 2018	FY 2017
Established patients	3,348	3,204
New patients	331	287
Total Encounters	3,679	3,491
Cash	$5,675	$12,098
Financial Ratios:
Expense per Encounter = Total Operating Expenses / Total Encounters
Total Operating Revenue per Encounter = Total Operating Revenue / Total Encounters
Operating Margin = Net Income/Total Operating Revenue
Days Cash On Hand = (Cash + Cash Equivalents) / (Operating Expenses / Days in Time Period)
Table 2. HealthWays Clinic, Income Statement, FY 2018.					Table 3. HealthWays Clinic, Balance Sheet, December 31, 2018.
	FY 2018	FY 2017	Horizontal Analysis		Current Assets	December 31, 2018	December 31, 2017	Current Liabilities	December 31, 2018	December 31, 2017
Gross Revenue (charges)	$558,520	$497,221			Cash	5,032	9,877	Notes Payable	27,449	50,000
Less write-offs & adjustments	117,254	104,332			Short-term Investments	40,389	34,181	Accounts Payable	78,702	69,412
Net Patient Revenue (collected)	$441,266	$392,889			Accounts Receivable	63,392	59,359	Accrued Expenses:
+Other Revenue	209,671	234,953			Supply Inventories, at Cost	16,029	14,918	Salaries & Benefits	38,265	28,274
					Prepaid Expenses & Other	2,104	1,876	Taxes	1,419	1,398
Total Operating Revenue	$ 650,937	$ 627,842			Total Current Assets	$ 126,946	$ 120,211	Interest Payable	3,294	500
								Total Current Liabilities	$ 149,129	$ 149,584
Operating Expenses
Salaries & Benefits	459,171	445,396			Property, Plant & Equipment (Fixed Assets)			Long-Term Liabilities	$0	$0
Medical Supplies	97,627	92,418			Cost of PP&E	56,047	55,701
Office Supplies	7,471	7,302			Less Accumulated Depreciation	4,194	3,943	Net Assets
Rent & Depreciation	39,148	37,023			Net PP&E (Net Fixed Assets)	$ 51,853	$ 51,758	Unrestricted	28,541	20,569
Other	43,762	47,009			Other Assets	$ 1,289	1289	Restricted	2,418	3,105
				Percentage change
Total Operating Expenses	$ 647,179	$ 629,148			Total Assets	$ 180,088	$ 173,258	Total Net Assets	$ 30,959	$ 23,674
Net Income	$ 3,758	($1,307)						Total Liabilities &amp Order a plagiarism free paper now Need your ASSIGNMENT done? Use our paper writing service to score better and meet your deadlines. Order a Similar Paper Order a Different Paper Recent Posts John Doe has decided to clone himself. He is sterile Business, Taxation, and Consumer Choice How would you define implicit bias? Begin by describing the three-term contingency, and explain why it is described as the basic unit of analysis for operant behavior Find a quantitative journal article and draw a visual causal model that shows the variables and theories used (as described in Creswell & Creswell, 2018, Chapter 3) About us We will coordinate your paper to the most able essayist that we accept will deal with your paper the most ideal way that could be available. Company Home How it works Contact us Terms and conditions Quick actions Order Now Manage Orders Create account Latest posts Contact us info@essayabode.com We accept Facebook Twitter Instagram © Copyright Writeden.com. All rights reserved.