Automatically Generate Invoices from Time Sheet (somewhat complex circumstances)

So, I've been a long time lurker here, and the forum has helped me setup my firm financials. It's been a great help.

I'm now looking to automate invoicing from a timesheet. Up to now, I've been using manual entry, which is prone to errors and take a very long time. That said, I'm wondering if I'm wanting to do something too complex.

The gist sounds simple but has proven very hard for me to figure out:

1) There is a monthly master time sheet with numerous timekeepers, numerous clients, a description, and amount of time spent;

2) There is a sheet that contains the clients and the rates each client pays for each timekeeper;

3) There is an invoice summary template; and

4) There is an invoice template.

I want to automatically produce invoices for each client that automatically populates the fields in the templates and calculates the amount owed by each client.

I've attached a file that shows all of this.

I'd appreciate any help at all. If it is a complex project, I'd be happy to pay for a result.

Thanks,

Will

Thanks for taking the time, jmac. I really appreciate the help.

As to your first question, I had not thought of that issue. I think my solution will be to organize time entries by matter. In other words, Client 1 may have multiple matters. Each matter will have a unique number (so, Client 1 is assigned the random number 1501, and matter 1 will be 1501a, matter 2 will be 1501b, and so on). For now, as I just started the business, there is really only one matter per client. And to be honest, other than five or so clients, that will be pretty constant. The rest will probably be one-off clients. I've attached an updated spreadsheet with a Matter column on the timesheet and corresponding changes on the Clients&Rates sheet.

As to your second question, I imagine the total timesheet entries will never exceed 500 (that's assuming 5 timekeepers, each inputting no more than 5 per workday).

To answer your third question, there will be a new workbook each month (hopefully, based off whatever we come up with here).

Another thought - this change means there will need to be invoices for each Matter, not client.

Also, I'd love the output just to be the invoices that have amounts associated with them, ie: if a matter was not billed, I don't want just a blank output invoice.

Again, thank you so much! I really, really appreciate you.

Originally Posted by jmac1947

Hi Wad06, and welcome to the spotlight of being a poster, I hope you enjoy the experience

just thinking about an approach to your issue, does the monthly timesheet have a client code or only a client name, and if only a name how do you deal with duplicate names (and there are sure to be duplicates eventually even if not in your sample data)

second question that might influence an approach. what volume of timesheet entries do you need to process and what number of clients typically per month

Jmac1947

No problems, feels like an interesting challenge so glad to help.

If you have just started the firm then you are probably in a good place that would allow you to make some small changes in procedures that could significantly ease your admin load in the most critical business process you have... getting paid for your work

The old business analyst in me says we should spend some time getting the "landscape" defined before we start any automated solution so I will send you a PM with some more questions etc rather than clog up the forum at this point

jmac