Develop the Budget Worksheet
Part 1: Develop the Budget Worksheet
For Part 1 of this Assessment, you will develop a sample budget for the healthcare product or service you have proposed. You will also apply ratios to analyze the budget and provide information that can guide choices. To do this:
1) Review the Learning Resources, particularly those on creating budgets.
2) Open the FE_workbook_firstinitial_lastname.xlsx file you created in FE001 and used in FE002.
3) Navigate to the A4 Budget Development worksheet.
4) 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.
5) Develop formulas or functions that calculate the following:
a) The total estimated startup expenses (those occurring in Year 0) for your product or service. Be sure to label each appropriately, with enough description to make it clear what the item is and any necessary details regarding sources of revenues (including reimbursements).
b) The total estimated expenses and costs per year and for the 5-year period for your product or service
PencilNote: “Total estimated expenses” should match those you presented on the A3 Estimated Expenses worksheet.
c) The payback period for your product or service idea
PencilNote: The values used for this calculation, including estimated revenues, should match those you presented on the A3 Estimated Expenses worksheet.
d) Include any other ratios or other analyses that you believe will strengthen your business case or help inform the decision-making process.
Part 2: Summary of Analysis and Interpretation of Results
For Part 2 of this Assessment, you will describe your budget and analysis. To do this:
1) Create a brief (1- to 2-page) description in a Word document of your budget and analysis. Your description should clearly describe the budget.
2) During the course of 5 years, is there an estimated surplus or deficit?
3) What percentages of the budget are dedicated to various categories you have defined, such as startup costs, etc.?
4) What does this budget mean for your organization?
5) Save and submit your assessment.
A3 Estimated Expenses
ESTIMATED EXPENSES
This worksheet presents the estimated expenses for the development, launch, and maintenance for the Heathway’s clinic.
A4 Budget Development
BUDGET DEVELOPMENT
This worksheet presents the budget development for Heathway’s clinic.
A5 Ratio Analysis
RATIO ANALYSIS
This worksheet presents the ration analysis for Heathway’s clinic.
A6 Financial Statement Analysis
FINANCIAL STATEMENT ANALYSIS
This worksheet presents the financial statement analysis for Heathway’s clinic.
A3 Wk4 Estimated Expenses
Expenses/Cost Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Grand Total
Start up expenses
expense 1
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.
Dr. Debra Sullivan: If you see a red tab on a cell, there are further tips. Do not enter any information in gray cells expense 2
expense 3
expense 4
Total start up expenses
Debra Sullivan, PhD: Debra Sullivan, PhD: This is where is total the start up expenses
Debra Sullivan, PhD: Debra Sullivan, PhD: This is the same as cell B7 representing the total start up expenses Operating Expenses
expense 1
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.
expense 2
expense 3
expense 4
expense 5
Total operating expenses
Total Expenses
Debra Sullivan, PhD: Debra Sullivan, PhD: This is total start up expenses
Dr. Debra Sullivan: Total operating expenses for year 1
Debra Sullivan, PhD: Debra Sullivan, PhD: Total operating expenses for year 2
Debra Sullivan, PhD: Debra Sullivan, PhD: Total operating expenses for year 3
Debra Sullivan, PhD: Debra Sullivan, PhD: Total operating expenses for year 4
Debra Sullivan, PhD: Debra Sullivan, PhD: Total operating expenses for year 5
Debra Sullivan, PhD: Debra Sullivan, PhD: This is the grand total of all startup and operating expenses. If you add cells B17 through G17 it should equal the sum of H7 though H15, so you can write your formula either way. Remember these cell numbers could change if you add additional expenses. Revenue/Savings
Source 1
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.
Source 2
Source 3
Source 4
Total Revenue/savings
Debra Sullivan, PhD: Debra Sullivan, PhD: You will not have revenue for startup unless you have a grant or upfront funding.
Dr. Debra Sullivan: Dr. Debra Sullivan: Total all revenue/saivings for year 1
Debra Sullivan, PhD: Debra Sullivan, PhD: Total all revenue/saivings for year 2
Debra Sullivan, PhD: Debra Sullivan, PhD: Total all revenue/saivings for year 3
Debra Sullivan, PhD: Debra Sullivan, PhD: Total all revenue/saivings for year 4
Debra Sullivan, PhD: Debra Sullivan, PhD: Total all revenue/saivings for year 5
Debra Sullivan, PhD: Debra Sullivan, PhD: This is the grand total of all revenue, so the sum of cells C24 through G24 should equal H20 throughH23. So either formula would be correct. Note that the cell numbers could change if you add additional revenues. Return on Investment
Dr. Debra Sullivan: For each year (column) for all 5 years and the grand total ,calculate the ROI using the following formula ROI = (Total Revenue –Total expense) /Total Expenses
A4 wk6 Budget Dev Worksheet
Expenses/Cost Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Grand Total
Start up expenses
expense 1
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.
Dr. Debra Sullivan: If you see a red tab on a cell, there are further tips. Do not enter any information in gray cells You should copy the items from week 4 Estimated Expenses to the area of this form that are the same. Be sure to enter formulas. Remember red tabs offer tips.
expense 2
expense 3
expense 4
Total start up expenses
Debra Sullivan, PhD: Debra Sullivan, PhD: This is where is total the start up expenses
Debra Sullivan, PhD: Debra Sullivan, PhD: This is the same as cell B7 representing the total start up expenses Operating Expenses
expense 1
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.
expense 2
expense 3
expense 4
expense 5
Total operating expenses
Total Expenses
Debra Sullivan, PhD: Debra Sullivan, PhD: This is total start up expenses
Dr. Debra Sullivan: Total operating expenses for year 1
Debra Sullivan, PhD: Debra Sullivan, PhD: Total operating expenses for year 2
Debra Sullivan, PhD: Debra Sullivan, PhD: Total operating expenses for year 3
Debra Sullivan, PhD: Debra Sullivan, PhD: Total operating expenses for year 4
Debra Sullivan, PhD: Debra Sullivan, PhD: Total operating expenses for year 5
Debra Sullivan, PhD: Debra Sullivan, PhD: This is the grand total of all startup and operating expenses. If you add cells B17 through G17 it should equal the sum of H7 though H15, so you can write your formula either way. Remember these cell numbers could change if you add additional expenses. Revenue/Savings
Source 1
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.
Source 2
Source 3
Source 4
Total…
