Laboratory Test Volume and Client Activity Excel Dashboard

详细信息

Hello!
Not sure if I am in the right category or not, so I don't know how to price this out so its entered as $10, but please specify what the cost would be.
Summary: I work for a diagnostic laboratory. Today I don't have the combined data to track our laboratory client activity, provider utilization and testing volume. I have a few different reports I can download, but none of them come together. I was tasked with this project and I am really struggling trying to bring it to life. I am not an excel expert so I thought I would ask around...
In an ideal scenario, I could download the reports and plug the data into a template and a dashboard would repopulate via excel spreadsheet. I listed few items I would be interested in starting with and I would look to your expertise at the same time to help us identify growth opportunities and missed opportunities as well on client and provider utilization. I should also state that I may want an NDA signed for this purpose when the data is shared.
I could only attach 3 files so I attached only a few screenshots (Report A, B and D to start)
Report A: Volume by Test Panel by Client - This is the only main tracking by test/panel volume that we have today besides individually going into each client portal- I would have to pull a report by month for last year so we can compare previous volume by client - Interested in Total Volume by Sales Rep (month over month)
Report B: Client List- A list of all clients. - This report is unique because it shows: - CREATED DATE: Date client was entered (meaning closed won account - it doesn’t necessarily mean a test was received on this date though). - LAST ORDERED DATE: Date client last ordered a test
Report B2: Facility Type- I’d want to see where largest volume is coming from by industry channel
Report C: All Data by Client by Day- This shows detailed volume by provider by day- Data by sales rep- Can we see provider utilization- Total volume by month/week (4 bar charts) - 2 charts (EX: Month of March Bar Chart - with week 1, week 2, week 3, week 4 volume inside chart). Then show month of February and month of January
Report D: All Providers by Client- This includes a list all providers in our system. So any provider on this list, but not on the report C, would be considered an inactive provider
Other Data Points: - Would love to see total number of accounts in last 3 months - Graph showing the total number of active and inactive accounts (should add up to total # of accounts) - Percent of actives to inactive
Thank you and look forward to hearing from you and your feedback!
Thank you! Christine