Experiential Exercise The objective of this exercise is to use workforce 60 percent of the head count. Why is data to develop a supply model for the physician this important to know? Do you expect workforce in the United States. 2 This exercise male or female physicians to work uses a simple Microsoft Excel model. Double- more hours? Which other factors might clicking on cells within the Excel spreadsheet affect the FTE/head count ratio? will reveal the formulas used to fill in those cells. Students may access the live spreadsheet model Step 2: Retirees and Leavers at ache.org/books/HRHealthcare4. 4. In cells E12 through E20, enter the age distribution of the workforce Step 1: Current Workforce shown in Exhibit 13.8. 1. Open the template Excel model. 5. The model will automatically generate 2. Note the head count and FTE of the an age distribution in cells L 12 current physician workforce in cells E6 through L41. and E7. What are some reasons that 6. Enter a typical retirement age of 68 the FTE is lower than the head count? into cell H13. 3. The FTE/head count ratio is 0.6, 7. The model will automatically generate which means that the effective number an annual retirement estimate in cells of physicians delivering care is about O12 through O29. attrition and residency supply, populate the residency training pipeline in cells D46 through D63 with the number of residents completing training in each year. How many residents complete training in 2020? Step 3: Training Pipeline 8. After medical school, physicians enter a Step 4: FTE/Head Count Participation residency training program. Once these 9. Cells D69 through E81 contain data individuals complete residency training, on physician head counts and FTEs they become part of the physician between 2001 and 2013. workforce. The table in Exhibit 13.9 10. Cells F69 through F81 show the shows the number of residents entering physician FTE/head count ratio for training in each year in the model. the years 2001 through 2013. EXHIBIT 13.9 11. Cclls F82 through F99 show the Number of cstimated physician FTE/head count ratio for the years 2014 through 2031. (In this simple model, the FTE/ head count ratio for these years is calculated using the Excel TREND function.) Step 5: Forecast 12. The model uses Excel's SUM function This modeling exercise makes the fol- to calculate the annual head count lowing assumptions about the residency forecast for physicians in cells D115 training pipeline: through D132. The model calculates - Residents have a 10 percent attrition each year's estimated physician rate - that is, 10 percent of residents head count by subtracting retirements who begin residency programs do and adding the number of new residents entering the workforce to If you do not get the same display, unhide the previous year's estimated physician the "Possible Solution" workshcet to see head count. 13. The model calculates annual physician how this forccast can be created. 3 FTE forecasts using the FTE/head Step 6: Evaluation count ratio. Cells E115 through E132 15. Does this forecast secm reasonable to show the FTE forecasts. you? How might you improve it? 14. The preceding steps produce a line 16. What happens to the head count and graph similar to the one in Exhibit FTE forecasts if all physicians retire at 13.10. the age of 65 ? EXHIBIT 13.10 Physician Forecast Notes 1. Exhibit 13.2 is a simplified workforce supply model that docs not include all factors. One such factor, the in-migration of forcign-trained health professionals to fill supply gaps, can be significant in some professions and can accelerate in response to shortages or decelerate in response to downturns in the economy. For example, the number of forcign-trained registered nurses who entered the US workforce increased prior to the 2008 recession. With the subsequent increase in US nursing school graduates, the percentage of nurses that are forcign trained is declining (Budden et al. 2013). The authors of this chapter are indebted to Katie Gaul, research associate at the Cecil G. Sheps Center for Health Services Research at the University of North Carolina, for her assistance in designing the exhibits. Head Count/FTE Forecast Physlcian Forecast Experiential Exercise The objective of this exercise is to use workforce 60 percent of the head count. Why is data to develop a supply model for the physician this important to know? Do you expect workforce in the United States. 2 This exercise male or female physicians to work uses a simple Microsoft Excel model. Double- more hours? Which other factors might clicking on cells within the Excel spreadsheet affect the FTE/head count ratio? will reveal the formulas used to fill in those cells. Students may access the live spreadsheet model Step 2: Retirees and Leavers at ache.org/books/HRHealthcare4. 4. In cells E12 through E20, enter the age distribution of the workforce Step 1: Current Workforce shown in Exhibit 13.8. 1. Open the template Excel model. 5. The model will automatically generate 2. Note the head count and FTE of the an age distribution in cells L 12 current physician workforce in cells E6 through L41. and E7. What are some reasons that 6. Enter a typical retirement age of 68 the FTE is lower than the head count? into cell H13. 3. The FTE/head count ratio is 0.6, 7. The model will automatically generate which means that the effective number an annual retirement estimate in cells of physicians delivering care is about O12 through O29. attrition and residency supply, populate the residency training pipeline in cells D46 through D63 with the number of residents completing training in each year. How many residents complete training in 2020? Step 3: Training Pipeline 8. After medical school, physicians enter a Step 4: FTE/Head Count Participation residency training program. Once these 9. Cells D69 through E81 contain data individuals complete residency training, on physician head counts and FTEs they become part of the physician between 2001 and 2013. workforce. The table in Exhibit 13.9 10. Cells F69 through F81 show the shows the number of residents entering physician FTE/head count ratio for training in each year in the model. the years 2001 through 2013. EXHIBIT 13.9 11. Cclls F82 through F99 show the Number of cstimated physician FTE/head count ratio for the years 2014 through 2031. (In this simple model, the FTE/ head count ratio for these years is calculated using the Excel TREND function.) Step 5: Forecast 12. The model uses Excel's SUM function This modeling exercise makes the fol- to calculate the annual head count lowing assumptions about the residency forecast for physicians in cells D115 training pipeline: through D132. The model calculates - Residents have a 10 percent attrition each year's estimated physician rate - that is, 10 percent of residents head count by subtracting retirements who begin residency programs do and adding the number of new residents entering the workforce to If you do not get the same display, unhide the previous year's estimated physician the "Possible Solution" workshcet to see head count. 13. The model calculates annual physician how this forccast can be created. 3 FTE forecasts using the FTE/head Step 6: Evaluation count ratio. Cells E115 through E132 15. Does this forecast secm reasonable to show the FTE forecasts. you? How might you improve it? 14. The preceding steps produce a line 16. What happens to the head count and graph similar to the one in Exhibit FTE forecasts if all physicians retire at 13.10. the age of 65 ? EXHIBIT 13.10 Physician Forecast Notes 1. Exhibit 13.2 is a simplified workforce supply model that docs not include all factors. One such factor, the in-migration of forcign-trained health professionals to fill supply gaps, can be significant in some professions and can accelerate in response to shortages or decelerate in response to downturns in the economy. For example, the number of forcign-trained registered nurses who entered the US workforce increased prior to the 2008 recession. With the subsequent increase in US nursing school graduates, the percentage of nurses that are forcign trained is declining (Budden et al. 2013). The authors of this chapter are indebted to Katie Gaul, research associate at the Cecil G. Sheps Center for Health Services Research at the University of North Carolina, for her assistance in designing the exhibits. Head Count/FTE Forecast Physlcian Forecast