Please help complete this assignment using excel.Week 10/11 Assignment An option

Please help complete this assignment using excel.Week 10/11 Assignment
An optional learning tool for Week 10/11 assignment
Part 1
Your First Decision
• You are provided an option to use your own data or the
provided HealthWays Financials.
• Many students have difficulty obtaining their organizations
data needed for this Horizontal Analysis.
• It is your choice; however, I recommend that you use the
HealthWays Financial data and in the following slides I will
try to guide you.
Horizontal Analysis
• Through horizontal analysis of financial statements, you would be able to see two
actual data for consecutive years and would be able to compare each item. And
based on that, you can forecast the future and understand the trend.
• The Current year is the newest year listed- 2018
• The previous year is considered the Base Year- 2017
Total Operating Revenue, Total Operating
Expenses, & Net Income
• We are finding the difference between
the Current or comparison(most
recent) year and the Base (previous)
year.
• Write a formula that does this for
D29, D38, D40.
• Remember you will simply be
subtracting. FY2018 minus FY2017.
• Use the cell designations in the
formula. For example: =B40-C40
A negative number can be confusing in a Horizontal
Analysis!
• Our formula for horizontal analysis says to subtract the previous year from the most
current or in our case cells B40-C40.
If you subtract these two cells, using the cell identification, it will be correct.
•
Remember the old math rule,
2 negatives make a positive.
4000
3000
2000
1000
0
-1000
-2000
Horizontal Analysis
Below I will try to explain why we would do this and how it shows the difference between the negative
number and the positive number.
B40 = 3,758
C40 = -1307
Imagine you need to stack bricks to the height of
3,758 feet.
However, the spot you are to do this is a hole
that is 1307 feet.
Even though the hole is below ground 0 (a
negative number), you must stack the bricks to
get them to baseline and then up to your 3,758.
So, you need to add the two numbers together
to determine how many bricks you need to stack.
5,064
bricks
Percentage Change
• The percentage change is calculated by dividing the dollar change between the Current
or comparison year (2018) and the Base year (2017) by the line-item value in the base
year, then multiplying the quotient by 100.
• The change is the difference between the Current or comparison(most recent) year and the
Base (previous) year that you just completed in D38.
• Next you change it to a percentage by either clicking the % icon in the tool bar or
multiplying times 100.
• For total operating expense as an example. The calculation is a given year’s total operating expense
minus the prior year’s total operating expense, divided by the prior year’s total operating expense.
The resulting figure is then multiplied by 100.
Return on Assets
• The final step is to determine the Return on Assets
• FY 2018’s Net Income divided by TOTAL ASSETS for 2018
• FY 2017’s Net Income divided by TOTAL ASSETS for 2017
Return on Assets = Net Earnings (Income) / Total Assets
Part 2
Summary of
Analyses and
Interpretation
of Results (1-2
pages)
• The sub-title for this section in the Healthcare
Budget Request Template is
• W10/11A6 Organizational Statement Analyses
• Describe the results of each statement analysis.
What do the results of each analysis mean?
• Income Statement ,Balances, Expense per
Encounter, Total Operating Revenue per
Encounter, Operating Margin, Days Cash On
Hand
Part 2
• Some things you might analyze or report:
• Income Statement-how is the clinic doing?
• Balance Sheet-how do the current liabilities stack against current
assets?
• Expense per encounter- are we breaking even? Compare both
years.
• Total Operating Revenue per Encounter- How do the costs stack
against the revenue?
• Operating Margin- Is it good or bad? How may it be improved?
• Days of Cash on Hand- is there enough to meet short-term
obligations?
Part 2
• What does your complete financial statement analysis suggest about the financial health of the organization?
• If using your current organization’s data, does your analysis help describe any observed organizational behaviors or
actions? Explain.
• What assumptions have you made in your analyses?
• What implications do these analyses have for your proposed healthcare product or service?
• *You could use these questions to make headings but please don’t use these exact words, just abbreviate. For example,
the second bullet: “What does your complete financial statement analysis suggest about the financial health of the
organization?” this subtitle could be “Financial Health of the Organization”
Part 3
Summary of Work and Final Healthcare Budget Request
• Compile and summarize your work in
previous assignments. You have already
done most of his work, just make any
corrections. You should use
the Healthcare Budget
Request template, that we have been using
all term.
Part 3 Your final Healthcare Budget Request should
include:
• These items are already completed:
• A final version of your Executive Summary
• W2A2 Executive Summary
• A final version of your projected expenses and revenues
• W4A3 Projected Expenses and Revenues (Five Year)
• A product/service budget for the launch and the first 5 years
• W6A4 Projected Budget (Five Year)
Part 3
• A summary of financial and SWOT analyses that you conducted,
including your interpretation of the results
• W10/11A6 Organizational Statement Analyses
• There is a discrepancy between the guide and your directions for
this part of the assignment.
• In the Blackboard directions, the above statement is made,
however the statement in the guide does not include the
SWOT analysis portion. I would suggest that you include the
SWOT analysis in your power point, since it can make a nice
slide.
Part 3
Power Point Presentation
• A 3- to 6-slide PowerPoint presentation containing the final version of your elevator
speech, incorporating selling points from your analyses that you believe make the
business case for nurse entrepreneurship and leadership’s commitment to your proposed
healthcare product or service.
• *Remember, this is a short PPT with your summary followed by your Elevator Speech
and SWOT analysis.
• For clarification: You should write out your elevator speech on one of the slides.
• I would suggest that you read your elevator speech out loud before you submit it and
time it not to exceed 60 seconds. I will do this when I read it to be sure it does not
exceed 60 seconds
Part 3
Power Point Slides
• I would suggest the following slides:
• Title page
• Introduction to topic/ selling points to make a business case
• Elevator Speech
• SWOT analysis
• Conclusion
• Reference page
• Note: You should post the pictures of the slides or handouts within the last section of the
Healthcare Budget Request Template with the subtitle
• W10/11A6 Summary/Elevator Speech (PPT slides)
To Summarize
• To summarize, you have 3 parts to this assignment
as outlined above. You will turn in 2-3 documents.
Remember to load all the files before you submit the
final assignment.
• The Healthcare Budget Request Template
which is the Excel document
• The Healthcare Budget Request Template
which is the Word document
• The power point fileThis criterion is linked to a Learning OutcomeCreate a worksheet that records the following: Conduct analyses as directed on either your organization’s financial statements or those provided in the Sample Financial Statements document. Your analysis will include spreadsheet calculations of financial statement ratios.Reflect on the previous Healthcare Budget Request assignment submissions, including your executive summary, your Expense/Revenue/ROI Analysis, your estimated budget, and your ratio analysis.This criterion is linked to a Learning OutcomeDescribe the results of each statement analysis. What do the results of each analysis mean? What does your complete financial statement analysis suggest about the financial health of the organization? If using your current organization’s data, does your analysis help describe any observed organizational behaviors or actions? Explain. What assumptions have you made in your analyses? What implications do these analyses have for your proposed healthcare product or service? Compile and summarize your work in previous assignment. You may choose to use the Healthcare Budget Request template, located in the Learning Resources, or create a format of your own. Your final Healthcare Budget Request should include: A final version of your Executive Summary A final version of your projected expenses and revenues A product/service budget for the launch and first 5 years A summary of financial and SWOT analyses that you conducted, including your interpretation of the results A 3- to 5-slide PowerPoint presentation containing the final version of your elevator speech, incorporating selling points from your analyses that you believe make the business case for leadership commitment to your proposed healthcare product or serviceWritten Expression and Formatting – Paragraph Development and Organization: Paragraphs make clear points that support well-developed ideas, flow logically, and demonstrate continuity of ideas. Sentences are carefully focused—neither long and rambling nor short and lacking substance. A clear and comprehensive purpose statement and introduction is provided that delineates all required criteria.I have had a few questions about the ” Wk10-11A6 Healthways Financials ” spreadsheet. There is a blue box at the bottom for you to analyze the financial reports. This area is for your use only. I will not grade this area. I will only grade the cells D29, D38, D40, B42, and C42.
Just a suggestion as some students get a little confused about the ” Wk10-11A6 Healthways Financials ” spreadsheet.” Remember you only use the financial statements from Healthways clinic if you are not able to retrieve your own organization’s financial reports. Most students are not able to acquire these reports, so we provide with the ” Wk10-11A6 Healthways Financials ” spreadsheet. It might help if you change the name of the document to “your organization’s Financials.”
Another tip is that many students miss within the summary expectation to discuss the implications the Healthways clinic analyses would have for your proposed healthcare product or service? This is where you were to consider your project for the health of the organization.
Review the Excel Assignment Workbook, W10/11A6 HealthWays Financials tab. These are sample financial statements.
Reflect on the information conveyed by these statements. Open your Excel Assignment Workbook and navigate to the “W10/11A6 Financials” worksheet.
You have 2 Options for completing this Assignment as noted in the Excel Assignment Workbook.
Using the Healthcare Budget Request Guide for guidance, conduct analyses as directed on either your organization’s financial statements or those provided in the HealthWays Financial Statements worksheet. Your analysis will include spreadsheet calculation of financial statement ratios. 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 & Net Assets $180,088 $173,258
Return on Assets
Financial Reports: Quick Tips for Interpretation
•income statement: positive net income indicates profitability
•balance sheet: positive equity indicates that there is a positive net worth, representing the amount remaining if an institution went bankrupt and had to liquidate
•compare changes in reports from prior year(s) to identify trends in financial performance, and with industry standards or internal benchmarks.
Financial Ratios FY 2018 FY 2017
Expense per Encounter $175.91 $180.22
Total Operating Revenue per Encounter $176.93 $179.85
Operating Margin 0.58% -0.21%
Days Cash On Hand 3.2 7.0