Week 1
Assignment 1: W1A1 HealthWays Budget
In Assignment 1: W1A1 HealthWays Bu

Week 1
Assignment 1: W1A1 HealthWays Budget
In Assignment 1: W1A1 HealthWays Budget, you will analyze data pertaining to a fictitious healthcare organization, HealthWays Clinic Inc. You will perform various calculations and analyze the results.
The following material will guide you through completion of the assignment. To proceed, you will need:
• This section of the Healthcare Budget Request Guide
• The Excel file “Excel Assignment Workbook.xlsx”, located in the Learning Resources
Assignment 1: Step-by-Step Guide
1) If you have not done so already, download and open the file “Excel Assignment Workbook” located in the Learning Resources.
Note: Be sure to save the file to the location you typically save documents and resave after each step to avoid losing work.
2) Navigate to the worksheet tab labeled “W1A1HealthWaysBudget”
3) Develop formulas or functions that calculate totals for the budgeted and actual encounters for June 2018, May 2018, and 2018 Year-to-Date (YTD) in the appropriate shaded cells of row 12.
4) Develop formulas or functions that calculate the variance between June 2018 budgeted and actual amounts in the appropriate shaded cells in Column D.
a. DO NOT USE the Variance function in Excel as it is a statistical variance and different from our budget variance.
b. A budget variance is simply the difference between the budget of units, expense or revenue, and the actual amount. To calculate a budget variance, you are communicating if the variance is favorable or unfavorable. In Excel, a number in parenthesis represents a negative number. So, if you see a number in parenthesis in the budget variance column, it represents an unfavorable variance, whereas a number that is not in parenthesis, represents a favorable variance. If you look in the Penner textbook on page 127 there is a sample variance report. Using that sample, we will look at units, revenue, and expense and how the variance is calculated.
c. Units and revenues are calculated as “actual minus budget”, and expenses are calculated “budget minus actual.” Examples:
i. “Total encounters” is a unit measure. The budget planned 980 encounters and the actual number of encounters was 997. The formula will be actual minus budget and yields a positive number. This is a favorable budget variance.
ii. Under “Expense” set the formula to begin with the budgeted amount and subtract the actual amount. For example, the budget for “cost per patient” is $128 but the actual amount was $134 yielding a negative $17; this is an unfavorable variable.
5) Develop formulas or functions that calculate totals for personnel expenses and non-personnel expenses in the appropriate shaded cells in rows 17 and 24.
6) Develop formulas or functions that calculate totals for health center expenses and their variances in the shaded cells in B25 through G25.
7) On the W1A1Healthways Budget Worksheet, answer the following question related to the results of your calculations:
a. What interpretations can you make based on the data? What is happening in regard to such measurables for:
i. The full-time equivalents (FTE) for HealthWays employees
ii. The number of encounters, both new and established
iii. Non-personnel expenses
iv. Total expenses
b. If these trends continue, what could it mean for HealthWays? What strategies might they employ to address any issues your analysis suggests?