Develop and analyze a budget for your proposed healthcare product or service. To do this, complete the following:
Part 1: Develop the Budget Worksheet:
Open your Excel Assignment Workbook and navigate to the "W6A4 Budget Development" worksheet.
- Using the Healthcare Budget Request Guide for guidance, create a 5-year budget for the healthcare product or service that records the projected expenses and revenues associated with the healthcare product or service you have proposed. Be sure to include startup and operating expenses in your budget. You may bring forward the work from the W4A3 Estimated Expenses assignment and add to it.
- Calculate the budget ratios as directed in the Healthcare Budget Request Guide.
NOTE: You will copy your worksheet and analysis onto the Healthcare Budget Request Template (Word document) for submission. You will need to submit both the Healthcare Budget Request (a Word Document) and an Excel Spreadsheet for your Assignment submission. Please be sure only to submit your Assignment once you have uploaded these two components of your W6A4 Assignment.
Part 2: Summary of Analysis and Interpretation of Results:
Create a brief (1- to 2-page) description of your budget and analysis that clearly describes the budget. Be sure to address the following:
- During the course of 5 years, is there an estimated surplus or deficit?
- What percentages of the budget are dedicated to various categories you have defined, such as startup costs, etc.?
- What does this budget mean for your organization?
Place your analysis on the Healthcare Budget Request Template under the section titled W6A4 Projected Budget (Five Year).
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 | 0 | 1.0 | 1.0 | 1.0 | |||
| Nurse PractitionerFTE | 3.0 | 3.0 | 0 | 3.0 | 3.0 | 3.0 | |||
| Encounters: | |||||||||
| Established patients | 275 | 291 | 16 | 286 | 1650 | 1671 | |||
| New patients | 25 | 18 | (7) | 27 | 150 | 164 | |||
| Total encounters | 300 | 309 | 9 | 313 | 1800 | 1835 | |||
| Expenses: | |||||||||
| Physician Salaries & Benefits | $10,500 | $10,502 | 2 | $10,509 | $63,000 | $63,149 | |||
| NP Salaries & Benefits | $20,000 | $20,992 | 992 | $20,191 | $120,000 | $122,001 | |||
| Clerical (2 FTE) Salaries & Benefits | $6,667 | $6,771 | 104 | $6,683 | $40,000 | $41,978 | |||
| Total personnel expense | $ 37,166.67 | $ 38,264.25 | $ 1,097.58 | $ 37,383.00 | $ 223,000.00 | $ 227,128.00 | |||
| Medical supplies | $7,500 | $8,136 | 636 | $7,994 | $45,000 | $47,883 | |||
| Office supplies | $623 | $583 | (40) | $508 | $3,498 | $3,407 | |||
| Rent | $2,917 | $2,917 | (0) | $2,917 | $17,502 | $17,502 | |||
| Depreciation | $333 | $346 | 13 | $346 | $1,998 | $2,050 | |||
| Capital Expenses | $3,333 | $3,480 | 147 | $3,480 | $19,998 | $20,439 | |||
| Overhead | $167 | $167 | (0) | $167 | $1,002 | $1,002 | |||
| Total non-personnel expense | $ 14,873.00 | $ 15,627.75 | $ 754.75 | $ 15,412.00 | $ 88,998.00 | $ 92,283.00 | |||
| Total health center expense | $ 52,039.67 | $ 53,892.00 | $ 1,852.33 | $ 52,795.00 | $ 311,998.00 | $ 319,411.00 | |||
| 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: The Full-Time Equivalents (FTEs) have been constant over the first half of 2018. | |||||||||
| 2. The number of encounters, both new and established: | |||||||||
| 2. Answer: The aggregate number of interactions (including both new and existing) has risen from 313 in May 2018 to 309 in June 2018. The count of existing patient contacts rose by 16, whilst the count of new patient encounters declined by 7. | |||||||||
| 3. Answer: There was a $754.75 rise in non-personnel expenditures between May 2018 and June 2018. The main factor behind this rise is the elevated costs in medical supplies, office supplies, and capital spending. | |||||||||
| 4.Total expenses: | |||||||||
| 4. Answer: The total costs saw a surge of $1,852.33 from May 2018 to June 2018. The rise in costs may be ascribed to both expenditures related to staff and those unrelated to staff. | |||||||||
| II. If these trends continue, what could it mean for HealthWays? What strategies might they employ to address any issues your analysis suggests? | |||||||||
| Answer: The consistent Full-Time Equivalents (FTEs) for physicians demonstrate a steady state in the levels of staff employed. If patient volumes increase significantly, HealthWays will continue to monitor workload and make necessary adjustments to staffing. The rise in overall encounters indicates an expanding need for services. HealthWays should evaluate the adequacy of the current staffing levels to manage the escalated workload and contemplate recruiting extra personnel if necessary. The escalation in non-personnel expenditures, specifically in medical supplies and capital expenditures, may suggest a surge in costs or heightened utilization of resources. HealthWays could investigate cost-reduction strategies, engage in negotiations for more favorable agreements with suppliers, or reevaluate the need for capital expenditures. The overall rise in aggregate expenditures underscores the significance of efficiently managing costs. In order to maintain financial stability, HealthWays should consider evaluating how its budget is allocated, pinpointing opportunities for reducing costs, and maximizing the efficient use of resources. Possible strategies might include doing an exhaustive cost analysis, reevaluating contracts with suppliers, investigating opportunities to enhance operational efficiency, and making budget modifications to better accommodate the increased demand for services. Conducting regular financial evaluations and engaging in strategic planning may enable HealthWays to effectively respond to changing circumstances and secure the clinic's sustained prosperity. | |||||||||
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
| Expenses/Cost | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Grand Total | |||
| Start up expenses | $ – 0 | |||||||||
| VR Equipment Dr. Debra Sullivan: Change these names to reflect your start-up expenses. Add more lines if you need more start up expenses or delete lines if you don't need all4 expenses. |
$ 200,000.00 | $ 200,000.00 | ||||||||
| Staff Training | $ 30,000.00 | $ 30,000.00 | ||||||||
| Infrastructure Changes | $ 50,000.00 | $ 50,000.00 | ||||||||
| Miscellaneous (e.g., marketing, legal) | $ 20,000.00 | $ 20,000.00 | ||||||||
| Total start up expenses | $ 300,000.00 | $ 300,000.00 | ||||||||
| $ – 0 | ||||||||||
| Operating Expenses | $ – 0 | |||||||||
| Disposable Materials Dr. Debra Sullivan: These costs may include human resources/personnel, equipment and supplies, marketing, training, and many more .Make sure you calculate for a full year for each column. |
$ 10,000.00 | $ 15,000.00 | $ 15,000.00 | $ 10,000.00 | $ 10,000.00 | $ 10,000.00 | $ 70,000.00 | |||
| Maintenance | $ 25,000.00 | $ 30,000.00 | $ 30,000.00 | $ 25,000.00 | $ 25,000.00 | $ 25,000.00 | $ 160,000.00 | |||
| Ongoing Staff Training | $ 15,000.00 | $ 20,000.00 | $ 20,000.00 | $ 15,000.00 | $ 15,000.00 | $ 15,000.00 | $ 100,000.00 | |||
| Other Operating Expenses | $ 5,000.00 | $ 5,000.00 | $ 5,000.00 | $ 5,000.00 | $ 5,000.00 | $ 5,000.00 | $ 30,000.00 | |||
| Total operating expenses | $ 55,000.00 | $ 70,000.00 | $ 70,000.00 | $ 55,000.00 | $ 55,000.00 | $ 55,000.00 | $ 360,000.00 | |||
| $ – 0 | ||||||||||
| Total Expenses | $ 355,000.00 | $ 70,000.00 Dr. Debra Sullivan: Total start-up and yearly expenses for each year in this row. |
Dr. Debra Sullivan: Change these names to reflect your start-up expenses. Add more lines if you need more start up expenses or delete lines if you don't need all4 expenses. | $ 70,000.00 | $ 55,000.00 | $ 55,000.00 | $ 55,000.00 | $ 660,000.00 | ||
| $ – 0 | ||||||||||
| Revenue/Savings | $ – 0 | |||||||||
| Patient Fees Dr. Debra Sullivan: Dr. Debra Sullivan: Revenue is the income derived from the reimbursement for providing goods or services. Revenue is based on the price or reveue per unit. Remember that charges may not be fully reimbursed. The calculation for a revenue stream could be revenue per unit (RU) multiplied by Units of service ((UOS) less reimbursement rate (RR)(for example 80%). For a service, you must first calcuate a unit of service (see chapter 4 of your textbook) and then follow the formula, (RU*UOS)*RR=Revenue. For a producr you would use the price. Cost avoidance has to do with any action that avoids having to incur costs in the future. In a business setting, cost avoidance is a measure that lowers potential increased expenses as a way of decreasing a company’s future costs. For purposes of this assignment, should your selected solution not generate a finite revenue (actual money being collected), you will use cost avoidance. An example is adding a new FTE to decrease overtime. An example in Penner, Table 10B.1 outlines costs of cleaning patient privacy curtains to decrease hospital acquired infection. Remember, if you choose this type proposal, you will need to break down costs and revenue in terms of units and explain a monetary value per unit. |
Dr. Debra Sullivan: This is where you total the start up expenses | $ – 0 | $ 150,000.00 | $ 200,000.00 | $ 250,000.00 | $ 300,000.00 | $ 350,000.00 | $ 1,250,000.00 | ||
| Grants | $ – 0 | $ 50,000.00 | $ 75,000.00 | $ 50,000.00 | $ 75,000.00 | $ 100,000.00 | $ 350,000.00 | |||
| Partnership Funding | $ – 0 | $ 30,000.00 | $ 40,000.00 | $ 30,000.00 | $ 40,000.00 | $ 50,000.00 | $ 190,000.00 | |||
| Miscellaneous Revenue | $ – 0 | $ 5,000.00 | $ 5,000.00 | $ 5,000.00 | $ 5,000.00 | $ 5,000.00 | $ 25,000.00 | |||
| Total Revenue/savings | $ 235,000.00 Dr. Debra Sullivan: Dr. Debra Sullivan: Total all revenue/saivings for each year in this row. |
Dr. Debra Sullivan: These costs may include human resources/personnel, equipment and supplies, marketing, training, and many more .Make sure you calculate for a full year for each column. | $ 320,000.00 | $ 335,000.00 | $ 420,000.00 | $ 505,000.00 | $ 1,815,000.00 | |||
| $ – 0 | ||||||||||
| Return on Investment | $ (1.00) | $ 2.36 Dr. Debra Sullivan: For each of the 5 years and the grand total ,calculate the ROI using the following formula ROI = (Total Revenue –Total expense) /Total Expenses |
$ 3.57 | $ 5.09 | $ 6.64 | $ 8.18 | $ 24.84 | |||
| $ – 0 |
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 | $ 210,000.00 | $ 1,200,000.00 | $ 1,410,000.00 | ||
| Adjustments | |||||
| Add back "One Time" credits | |||||
| Deduct "one Time" expenses | $ 50,000.00 | $ 50,000.00 | |||
| Adjusted total for year to date expense | $ 160,000.00 | $ 1,200,000.00 | $ 1,360,000.00 | ||
| Annualization | |||||
| Divide by months (fixed) | 6 | ||||
| Multiple by months (fixed) | 12 | ||||
| Divide by volume | 20,000 | ||||
| Multiply by volume | 40,000 | ||||
| Annualized Amounts | $ 320,000.00 | $ 2,400,000.00 | $ 2,720,000.00 | ||
| Adjustments | |||||
| Add back "One Time" expenses | $ 50,000.00 | $ 50,000.00 | |||
| Deduct "One Time" credits | |||||
| Expense two new technicians | $32,000.00 | $ 32,000.00 | |||
| Expense Forecast as of 12/31/X1 | $ 338,000.00 | $ 2,738,000.00 | |||
| Calculations: | |||||
| Annualization for Fixed: (Adjusted Total for Year to Date Expense/6) * 12 =Total Annualized Amounts | |||||
| Annualization for Variable (Adjusted Total for Year to Date Expense/ 20,000) * 40,000 =Total Annualized Amounts |
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 | $1,075 |
| Collection Rate | 80% |
| Average Collection per Service | $860 |
| Variable cost per unit of service | $420.00 |
| Fixed Operating Costs | $4,700,000.00 |
| Break-Even Point =Fixed Cost/(Net Revenue per Unit-Variable Cost per Unit) | 10681.8181818182 |
| Capacity: | 16,500 |
| Demand: | 8,000 |
| Breakeven: | 10,682 |
| 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: Not feasible due to the narrow profit margin. For every $860 deposited, $420 is withdrawn. It seems that the profit generated is insufficient to sustain the firm. In order to continue with implementation, my objective would be to optimize profit by minimizing variable costs. I would devise strategies to enhance the rate of collection, raise the price per service, and reduce the variable cost per unit of service.
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 | 1,400 | 2,000 | 2,600 | 3,200 | 3,800 |
| Price Procedure | $1,000 | $1,000 | $1,000 | $1,000 | $1,000 |
| Collection Rate | 80% | 80% | 80% | 80% | 80% |
| Marginal Net Revenue | $1,120,000 | $1,600,000 | $2,080,000 | $2,560,000 | $3,040,000 |
| Marginal Costs | |||||
| Variable Costs | |||||
| Units of Volume | 1,400 | 2,000 | 2,600 | 3,200 | 3,800 |
| Variable Cost Supplies per Unit/procedure | $300 | $300 | $300 | $300 | $300 |
| Marginal Variable Cost | $420,000.00 | $600,000.00 | $780,000.00 | $960,000.00 | $1,140,000.00 |
| Fixed Costs | |||||
| Salary Costs | $540,000 | $540,000 | $540,000 | $540,000 | $540,000 |
| Fringe Benefits | 135,000 | 135,000 | 135,000 | 135,000 | 135,000 |
| Rent | 55,000 | 55,000 | 55,000 | 55,000 | 55,000 |
| Operating Cost | 120,000 | 120,000 | 120,000 | 120,000 | 120,000 |
| Marginal Fixed Costs | $850,000 | $850,000 | $850,000 | $850,000 | $850,000 |
| Total Marginal Costs | 1,270,000 | 1,450,000 | 1,630,000 | 1,810,000 | 1,990,000 |
| Annual Marginal Profit | -150,000 | 150,000 | 450,000 | 750,000 | 1,050,000 |
| Cumulative Profit Margin | -150,000 | 0 | 450,000 | 1,200,000 | 2,250,000 |
| Question: Below is a marginal P&L for this business opportunity. Based on that analysis, should this opportunity be pursued. Explain your decision. | |||||
| Answer: Yes, Profit margin has grown from negative $150k to over a million dollars in five years. | |||||
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 & 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 | |||||||||
Interpretation/Analyses In your narrative analysis that you will write in the Healthcare Budget Request Template, you should address: Income Statement Balances Expense per Encounter Total Operating Revenue per Encounter Operating Margin Days Cash On Hand
W10-11A6 Your data
| Option 2 Your project data |
| You have 2 data options for completing the Week10/11A6 analysis. Assuming you have access to your organization's financial statements, you my use it. Bring forward your work from W6A4 and add any new data and calculations needed. |
