caffeineated
New Member
- Joined
- Jan 23, 2016
- Messages
- 1
I like to think I'm pretty good with Excel, but I'm having trouble "thinking outside the box" to improve a workbook I created and have been tweaking over the last several months.
My wife works in a job where she is considered an independent contractor. She earns her income based on clients she sees, but the administration part is handled for her and the agreement in place is that she gets paid when the company collects on the billings (insurance, so it can range from a couple of weeks to a couple of months depending on the plan). Being the numbers person in our house, I wanted to track billable clients seen to estimate expected income and actual checks received each month.
tl;dr version: I have a cumbersome workbook to track billable hours/clients seen at different billable rates. Is there an easier way (minimum required monthly maintenance) to compile daily data, billed and lost potential income, and various analytical values?
Long version:
It started simple enough (ha) with a DailyDetail tab where each worked day gets a row and various columns to track how many clients were scheduled, whether they were a new or returning client (different billing rates), and how many were actually seen (vs no-shows or cancellations) to estimate the earned as well as lost potential income on a wasted session/hour.
As things progressed I wanted more and more analytical tools, so I've ended up with a tab breaking down scheduled, billable, and no-shows by calendar week; a tab calculating a handful of analytical percentages by calendar month; a tab summarizing the daily detail into monthly/annual totals; and most recently a tab calculating (recalculating?) actual billings by allocating the clients seen to one of a dozen or so rate tiers (allowing me to blend the different rates into an overall average for more accurate estimation of outstanding billings).
It's all still totally usable, it just feels like it's getting clunky, and it requires a fair amount of maintenance each month - set up 30-ish new rows in the daily detail, update links in the other tabs to accommodate that detail, etc. and I can't help but think there has to be a way to capture all the data and get the analysis tools I'm looking for in an easier way.
I feel like the answer is probably the use of VB/macros, but those are areas where I lack experience. I can make excel work to get the information I need, but I'm not necessarily versed in getting it to work as efficiently as it can.
I know that's a lot of background for what is not a specific "how do I do this" question, but hopefully there's enough here to get a couple of ideas and pointers about what I should look into.
My wife works in a job where she is considered an independent contractor. She earns her income based on clients she sees, but the administration part is handled for her and the agreement in place is that she gets paid when the company collects on the billings (insurance, so it can range from a couple of weeks to a couple of months depending on the plan). Being the numbers person in our house, I wanted to track billable clients seen to estimate expected income and actual checks received each month.
tl;dr version: I have a cumbersome workbook to track billable hours/clients seen at different billable rates. Is there an easier way (minimum required monthly maintenance) to compile daily data, billed and lost potential income, and various analytical values?
Long version:
It started simple enough (ha) with a DailyDetail tab where each worked day gets a row and various columns to track how many clients were scheduled, whether they were a new or returning client (different billing rates), and how many were actually seen (vs no-shows or cancellations) to estimate the earned as well as lost potential income on a wasted session/hour.
As things progressed I wanted more and more analytical tools, so I've ended up with a tab breaking down scheduled, billable, and no-shows by calendar week; a tab calculating a handful of analytical percentages by calendar month; a tab summarizing the daily detail into monthly/annual totals; and most recently a tab calculating (recalculating?) actual billings by allocating the clients seen to one of a dozen or so rate tiers (allowing me to blend the different rates into an overall average for more accurate estimation of outstanding billings).
It's all still totally usable, it just feels like it's getting clunky, and it requires a fair amount of maintenance each month - set up 30-ish new rows in the daily detail, update links in the other tabs to accommodate that detail, etc. and I can't help but think there has to be a way to capture all the data and get the analysis tools I'm looking for in an easier way.
I feel like the answer is probably the use of VB/macros, but those are areas where I lack experience. I can make excel work to get the information I need, but I'm not necessarily versed in getting it to work as efficiently as it can.
I know that's a lot of background for what is not a specific "how do I do this" question, but hopefully there's enough here to get a couple of ideas and pointers about what I should look into.
