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.
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
| W2A2 Practice Design |
| Refer to the Healthcare Budget Guide for an example of what to include and how it should look. |
W4A3 Estimated Expenses
| W4A3 Estimated Expenses |
| Refer to the Healthcare Budget Guide for an example of what to include and how it should look. |
W6A4 Budget Development
| 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 &
Collepals.com Plagiarism Free Papers Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers. Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS Why Hire Collepals.com writers to do your paper? Quality- We are experienced and have access to ample research materials. We write plagiarism Free Content Confidential- We never share or sell your personal information to third parties. Support-Chat with us today! We are always waiting to answer all your questions.  All Rights Reserved Terms and Conditions |