The Sweet Spot – Calculating the Optimal Number of Days to Work

Sometimes you can calculate the most efficient number of days to work in a month using the trendline feature (regression analysis) in MS-Excel. Practice revenue increases with the number of days worked but it will eventually hit a plateau. For non fee-for-service practices the number of patient encounters also follows this model. There is a certain point, beyond which, revenue or patient visits are just diluted out and there are diminishing returns for working a greater number of days with no benefit to the patient. Good practice management using this feature minimizes office waits and maximizes practice efficiency.

Create a spreadsheet with days worked per month in the first column and revenue (or patient visits) in the second. Label the first column days and the second revenue/visits

Highlight the block of data and click INSERT>>CHART

Choose XY (Scatter) plot then hit finish. The plot should look like this:



Click on the data points (they should highlight yellow) the right-click on the mouse and choose ADD TRENDLINE

In the trendline box
Under the TYPE tab; choose polynominal (choose Order: 3)
Under the OPTIONS tab; turn on all three checkboxes at the bottom
Set intercept = 0 (no days = no revenue)
Display equation on chart: YES
Display R-squared value on chart: YES
Forecast: forward 1 unit



The graph should look like the picture below. If you need to change the trendline click on it once then choose format trendline



You’ll notice that at 30 days the revenue/patient visits plateau (ignore the actual numbers as they are all randomly generated in this example)

R2 = 0.7439
The R2 (R-squared value) quantifies how well the revenue data correlates with the days worked value. Anything over 0.6 seems to be useful and over 0.8 is fantastic.

y = -1.1113x3 + 40.452x2 + 445.61x
This is the equation that creates the trendline. You can use it to calculate a revenue (Y) for any days worked (X). To do this put the number of days you want to work in cell A1. In cell B1 convert the equation above as follows (the “^” symbol is above the 6):

=(-1.1113)*A1^3+(40.452)*A1^2+(445.61)*A1

You’ll notice that after 30 days then projected revenue diminishes. This is artifact because a 3rd order polynominal was used. It represents the plateau. If a 2nd order had been selected the projected revenue would continue to increase and the plateau would be less obvious. Finally, if you have multiple offices create an equation for each one and balance the days to maximize revenue or patient visits. The file can be found here to work with the data.