Client Invoicing

There are many ways to implement what you're trying to do in Smartsheet with the best approach dependent on how many employees, clients, projects, etc. you need to track. Four years ago I started with a unique Smartsheet timesheet for each employee that had a list of all active projects, the different roles that could be performed (each role has a different burdened labor rate and billing rate) and the hours worked on each combination of role for each day of the week. It was very fast and easy for employees to fill in their time but the individual timesheets were time consuming for me to process and archive each week. As our company grew we sometimes have over 50 active projects and that process became too burdensome for me. A couple of years ago we wrote an internal browser-based application to do the timesheet part and now the weekly data comes to Smartsheet via the API that I process each Monday (originally I had to copy/paste the weekly data from our internal app into Smartsheet but we semi-automated that a year ago). Here are some thoughts you might find helpful:

You can link (or use the new cross-sheet reference capability) all the pertinent data from each employee's timesheet into a single master timesheet where you can gather the hours by role for each project for each week and put them into a unique Smartsheet for each project that can be used for invoicing purposes. This would involve a lot of formulas using SUMIFS() functions but if you're familiar with Excel it won't be too difficult.

Employees can use Smartsheet webforms to cause data to be entered on a master timesheet that gets processed weekly.

Employee timesheets could be created in Excel using dropdown lists to select projects and roles and then enter their hours. Individual employee timesheets could then be combined in a master Excel timesheet that could then be processed in Excel or copied and pasted into Smartsheet for processing financials.

There are many other issues involved such as validating the timesheet entries, tracking project cost based on burdened rates for the accounting year involved, generating invoices based on billing rates for the accounting year involved, making sure that total invoices to date don't exceed contract totals, etc.

I make use of the Smartsheet report generation capabilities to pull the data out of other Smartsheets to generate the monthly invoices for projects that are based on calendar month hours used.

Good luck as you move forward.

First, let me deal with linking and using cross-sheet references to get all your employee timesheet data into a master timesheet. 

Linking: like Excel, Smartsheet has the ability to link in data from other Smarthseets. One operation can link in from one to 500 cells of data with a maximum of 5,000 incoming links into a sheet. All you do is select the cells on the destination sheet that you want to link in and right click and select "link from cells from another sheet". You would have to do that with each employee's timesheet to bring all the data into your master timesheet for analysis. I don't know how many rows of data you would have to link in since that depends on the maximum number of charges an employee can make in a given week. In my case I linked in the project name, the role they charged to, the total hours worked (they had a cell for entering hours daily for the week) and a comment for each individual charge. I reset the employee timesheets each week after processing the data on the master timesheet and storing the results in a separate Smartsheet for each project. By resetting their timesheet I mean opening up each one and basically clearing out the week's entries. Unfortunately with 80 employees this approach will take a lot of your time to open and clear each timesheet weekly. It was doable for me because we only had 12 employees at the time. The timesheets were linked in vertically so if you can live with 20 rows of linked-in data, for example, you could only link in three cells per row. With 80 employees I don't think you will be happy with this approach because of the sheer number of Smartsheets to manage and reset each timekeeping period.

Cross-Sheet References: similar to Excel, Smartsheet now has the ability to use external references in functions to access data from other smartsheets. It's a lot like linking them in but it uses formulas like INDEX() or VLOOKUP() to pull data from the employee timesheets or any other functions to analyze data directly on other sheets. Using this approach up to 25,000 cells can be referenced from other sheets. After the data is in the master timesheet it can be processed the same as if the links brought in the data.

Somehow in your process all the timesheet entries need to be validated and any issues corrected before the weekly (or monthly?) processing. I found this necessary because people would forget to enter some of their time (<8 hours per day or <40 hours per week entered), they would charge to the wrong role (we have designers, developers, QA people, project managers, etc.) and occasionally to the wrong project completely which would mess up the financial tracking.

Initially I kept the list of active projects in a dropdown list for the project name column but that was really hard with a large number of active projects since I would have to update all timesheet project name dropdown lists (80 of them in your case) when a new project opened or one closed. I used a dropdown list since all project names on each timesheet had to be spelled exactly the same for processing with SUMIFS functions. I believe there are third party add-ons that allow dropdown lists to be updated from a central source which would be critical in this case but would cost extra.

I haven't used Smartsheet webforms in a long time but I think it might work better than having a separate timesheet for each employee. Employees would enter their time using webforms and then the data automatically shows up on a master timesheet where it could be processed. If you can use dropdown lists on webforms for project names that would solve the problem of getting project names in a dropdown list for everyone's webform. I was starting down that path when we decided to write our own browser-based application for gathering timekeeping data along with many other project management features needed in our business.

I hope this helps!

It is really impressive how far you have gotten so quickly! It sounds like it will turn out to be a very capable application. Here are a few comments triggered by you post.

I would definitely start archiving everything, probably on a monthly basis. I don't know if you keep track of things on a calendar month or timekeeping month (timekeeping months always include a whole number of 7-day weeks in our case). Projects where we charge for actual hours worked are tracked and invoiced on a calendar month basis while our fixed-bid projects are typically tracked on the timekeeping month basis with invoices based on milestones achieved. Most calendar months start and end in the middle of a timekeeping month so that has to be managed somehow. I archive the weekly time charges in an Excel database that is somewhat compressed to keep it within reasonable size. That's a more complex subject than I have time for right now. 

I do the weekly timekeeping validation myself since we are still small enough and I know what type of work each employee typically does though some do charge to three or more types of work. For financial tracking we determine a burdened labor rate for each type of work we do that changes each year as costs change. This takes the employee salaries and benefits into account and then adds various other costs including facilities, utilities, equipment, software and the cost of overhead people that don't charge to projects. Our billing rates are then set at a certain percentage above that to provide a profit margin.

My application tracks projects costs but does not include payroll processing. That's handled by a robust payroll system from a third party.

I have some more comments but I'm out of time right now.