I would like to hire an Excel Expert
This project was awarded to KavTan for $250 AUD.Get free quotes for a project like this
Project Budget$30 - $250 AUD
Hi i'm a Physiotherapist and i run an allied health clinic with 10 therapists and 3 admin staff. We currently use a good clinic management software that produces good reports but i need to give meaning to the numbers this report produces in order to see trends and make decisions.
I would like to produce monthly reports that can be displayed in table form and in a graph form to track progress for each therapist, to compare performance between each therapist and to view the performance of the clinic overall.
The KPI's that i would like to track for each therapist are:
1) Total Clients (shows the number of patients the therapist has seen in the month)
2) Total Sales
3) Total Inventory sales
4) Monthly wage
5) Profit Generated (Total sales - monthly wage)
6) Average Client Spend (Shows on average how much each client is spending with them $)
7) Forward Booking Average (shows how many sessions they're booking a patient in after they finish their session)
8) Client Visit Average (shows how many time a client sees them)
9) New Clients (shows number of new clients for the month)
10) Cancellations (shows number of cancellations for the month)
11) DNA (shows the number of patients that simply DID NOT ATTEND for the month)
12) Utilisation Rate (this is a % value which shows how busy the therapist is: the software produces this result)
When viewing this data in a graph format i would like to achieve 2 views:
1) GOAL BASED VIEW that can compare how the therapist performed against his own goal via a column graph.
- Ability to compare how the therapist performed in comparison with the goal we have set together for the month E.g In January the therapist's goal for "total sales" for the month was $2000 (i would like this column to show in blue). When the "Total sales" is calculated i would like this to show as a column right next to the goal column, that either shows up in green if they meet or exceed the goal, or in red if they fall behind.
- I would like the graph above to show for all therapist so i can compare between therapists
- Are we able to have one page that has all the 12 KPI graphs showing?
2) LINE GRAPH of a Month to month view for the year for each individual therapist to see how they're progressing for each KPI
CLINIC KPI'S for the month
1) Total Clients
2) Number of new clients
3) Total Revenue
4) Total Practice Payments
5) Expenses breakdown (therapist wages, admin wages, stationary, products, etc - would want to be able to modify this list) I would also be interested to know what %of the gross income each expense takes. I would also like to add up all the expenses for the year
6) Performance Profit (Total Revenue - Expenses). I would also be interested to know what % of the gross income this profit represents takes. I would also like to add up all the profits of each month for the year.
7) Real Profit (Total Practice Payments - Expenses) I would also be interested to know what % of the gross income this profit represents takes
8) Ageing debts (shows total owing for the clinic to date)
9) Total Cancellations
10) Total DNA
11) How many marketing things done social media posts/events attended/blogs/dr visits (This will be like a tally, can we also write notes here to write down what we've done)
12) Total social media likes
13) Total Social media Shares
I would like to achieve the 2 views as mentioned above (that is the GOAL BASED VIEW and the month-month view for the year) for the clinic KPI's
QUESTION: Could this data be aggregated to compare year to year? This isn't very important. If not i guess i can initiate a new file for each year to keep the size of the file small, and for backup purposes?
Can we use this file on google docs?
Will we be able to print these statistics?
Browse Related Skills
Other things people do on Freelancer
Looking to make some money?
- Set your budget and the timeframe
- Outline your proposal
- Get paid for your work
Hire Freelancers who also bid on this project
Looking for work?
Work on projects like this and make money from home!Sign Up Now
- The New York Times
- Wall Street Journal
- Times Online