April 20, 2017 at 11:13 pm
I got this idea from something they are doing in Seattle, but basically instead of waiting until CSMR time to run your integrity hours report, I run it every Monday morning (which takes some time for larger regions…)
I created THIS spreadsheet to track the data.April 20, 2017 at 11:42 pm
Ahem. I did not mean to submit that yet.
I created THIS spreadsheet to track the data. There is a template tab that you can copy for your own use.
To set up:
- Fill in all of your sites in Column A. I just did it in the order they appear on the intranet under My Locations
- In the formula in Column B, replace SPREADSHEET KEY with the key from your site’s CSMR. Everything from d/ to /edit (it should be 44 characters long).
- Run the integrity hours report for that site, using all locations that show up on the CSMR
- Paste the data at the bottom into this sheet, highlight Saving Doctors, Integrity Hours, and Innovation Hours (it will hide the innovation, I just find it easier to copy them all).
- Any site that has < 10 hours left MTD will be flagged with colors based on how close to the budget (or over!) they are.
- Do the same for the OT report for each site.
- Important: The following month you will need to change the formula in column B. Since it auto-pulls from your CSMR directly, you will need to change the end part of the formula, which is boldfaced and underlined here: <span class=” default-formula-text-color” dir=”auto”>=</span><span class=” default-formula-text-color” dir=”auto”>ImportRange</span><span class=” default-formula-text-color” dir=”auto”>(</span><span class=” string ” dir=”auto”>”Spreadsheet Key”</span><span class=” default-formula-text-color” dir=”auto”>,</span><span class=” string ” dir=”auto”>”csmr data!<span style=”text-decoration: underline;”>f41</span>”</span><span class=” default-formula-text-color” dir=”auto”>). </span>
- <span class=” default-formula-text-color” dir=”auto”>Go to your CSMR, and look at the month you are <span style=”text-decoration: underline;”>currently</span> in, for September 2017, it would be cell K41.</span>
- On the tracker spreadsheet, highlight Column B, then hit Ctrl H (on windows) or ⌘ + f (on evil Mac)
- Search for the current value. In the September example it would be Find J41 and replace with K41
- Make sure you click “also search within formulas”
Feel free to reach out if you have questions or can’t get it to work!
-MichaelJaclyn LaborRegional ManagerApril 21, 2017 at 7:39 pm
You are the master of amazing spreadsheets- I always appreciate the tools that you share! This is a fantastic resource to track the hours on an ongoing basis. I find that monitoring hours each Monday is a good way to ensure that we are budgeting appropriately for that week and the remainder of the month.
Do you just use this spreadsheet for your own reference or share it in a view only format for your entire leadership team? I think that for certain initiatives, sharing spreadsheets like this can help to motivate other site leaders in the region to meet the goals set for the program.Erin Winebarger MillerRegional ManagerApril 24, 2017 at 9:02 pm
Thank you for sharing your spreadsheets with us!! They are always so helpful!April 24, 2017 at 11:36 pm
I do send it out to my sites and have them comment if they have < 10 hours left MTD. I didn’t send it out today because the pay period ends tomorrow, so I’d just have to do it all over again.
Implementations are still killer, but the rest of my region looks to be a lot better this month, so we will see!
You must be logged in to reply to this topic.