Client Invoicing

gdi2k

Hi, I just signed up to the trial of Smartsheet today, so I'm very new to all of this. 

I'm wondering if we can use this for client invoicing. 

As a start, I have set up a sheet that lists employees, which includes a "Client" column to whom their time should be billed. 

For each employee, I have set up a separate time sheet - employees would be able to input their billable hours at the end of each day via a form. Each row represents a shift, with start time, end time and billable hours. 

At the end of the month, I somehow need to generate client invoices. I am unsure how to pull this together. What's the recommended approach? Should I create a fresh sheet for each client invoice (that I can then merge into invoices with Smartsheet Merge)? Or is there some reporting function that can automate this? 

I'm nifty with Excel, but my understanding of smartsheets reporting tools is very poor. 

Jim Hook

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.

gdi2k

Jim, thank you for taking the time to respond to my questions, and it's great to hear that all these things are possible. I'm excited. 

I have set some tests up, using, as mentioned, a lot of sumifs and the like. There are limitations, but I think I'll be able to work around them. 

You mentioned "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" - I haven't been able to figure this out. I definitely want to use one timesheet per employee (primarily for accountability / security / sabotage concerns - anyone with a link could submit garbage to the master time sheet). But I am unsure of how to actually generate new rows in the master time sheet as new rows are added to the individual employee timesheets without using APIs or third party add-ons. 

Could you provide some hints on that? 

For reference, we currently have around 80 employees working with around 20 different clients. 

Many thanks

Jim Hook

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!

Jim Hook

I had some time today so I took a look at how I would handle your hours tracking project for invoices using web forms along with a master timesheet. I really think having a unique Smartsheet timesheet for each employee will be extremely messy for you, the administrator, to handle. Here are some thoughts on the master timesheet and the web form.

Do all the calculations and summaries for each project and person at the top of the sheet and off to the right of the data brought in from the web forms. This allows you to sum all the values in entire columns regardless of how many entries (rows) have been submitted since you would always have new entries entered at the bottom of the sheet.

You can keep one dropdown list of permissible (active) project names/numbers to charge to on the master sheet in the column where the web forms place the project name. Also, do the same for roles, if you have them, and the employee name.

Each employee could have their own web form for themselves or you can have one that you share with everyone. They can be setup so that when they submit a time charge the form automatically reopens for the next charge. The problem with doing time charges this way is that there is no way on a web form to total the hours per day, per week or per project on the form so they can't tell when they have accounted for all their time during a week. I see two ways to work around this issue. First, your master timesheet could have a summary area for each employee for the timekeeping period and that could be linked out to a unique Smartsheet summary sheet for the employee. Each employee would only have access to their particular summary sheet. It would show their charges by project, by day and the totals for the timekeeping period. Correcting mistakes would be pretty difficult though. They would need to email the administrator with their correction and have you do it.

You could also display each employee's timesheet entries along with their daily and weekly totals using a unique Smartsheet report for each employee that picks up their charges and the summary information from the master timesheet. Since editing a cell in the report edits the source data on the master timesheet that would make corrections much easier. I would suggest this approach but whatever way you go will take quite a bit of work to setup the master timesheet and all the reports/summary sheets and a new report would have to be created every time a new employee hires in.

All these considerations, and many more related to making project assignments to people, are why we ended up creating our own internal timekeeping entry application that transfers the data to Smartsheet where the project financial analysis and invoicing is handled.

gdi2k

Jim, thanks so much for going into such amazing detail on this. Now I'm really excited - there are plenty of aha moments here! I've cleared my weekend and am going to get stuck into building a prototype based on your guidance by Monday. 

Yesterday I had some time to better understand what "Reports" is all about, and I can definitely see that being useful (despite lack of calculations / formulas in reports) for things like generating raw time sheets for clients as back-up for their invoices (which will contained summary of hours). 

I'll post back to let you know how it goes (and probably ask for help when I hit stumbling blocks). Thanks again for taking the time! 

gdi2k

So I spent some of the weekend working on this and made some workable progress I think. I am going to try and combine employee tracking, payroll and client invoicing. After some experimentation, I have come up with this basic set up: 

Employee Master List: Employee details are all stored here, such as salaries, billable rates and client assignments. 

Master Timesheet: All employee hours go in here - they are submitted by web form, and there's an approval process to make sure the data is sane (especially as we have 17 different flavors of hours we have to apply by labor law). Each employee has their own link to submit hours, which auto-fills employee ID so they can't submit hours for each other etc. 

Client Timesheet Report: This outputs the raw time sheet rows for employees billed to a specific client for the billing month, and will be used in support of client invoices. Running these will be manual each month (change date range, select client, export, repeat). Would be nice if this were automatable, but I don't think that's currently possible. 

Client Invoice Sheet: This sumifs agent hours that are assigned to a specific client in a given billing month, and will be used to merge into invoices in Google Docs. This will be a struggle because some clients have just 1 hourly rate, and some have up to 5. I'm not sure how to deal with this yet - there may have to have blank table rows on the invoice to accommodate the variation. 

Payroll Sheet: This sumifs all employee hours and calculates taxes and statutory government contributions for each employee on each row for a given pay period. Will be used to merge into payslips sheets and summary reports for bank transfer uploads, taxes etc. 

For now I have the Master Employee List and Master Timesheet up and running, as well as the Client Timesheet Report. I have asked a handful of users to start inputting their hours each day so I can build some real world data and then start developing the more complex sheets with a lot of sumifs. Will let you know how it goes! 

It's a pity that there isn't a way to calculate times (between login / logout etc.) without performing formulaic acrobatics. I would also like it if the graphical symbols were replicated on the web forms - they are just displayed as textual dropdowns. 

With our current number of employees we anticipate hitting the 5000 row limit on the Master Timesheet within 3 months. What's the best way to archive the data? I may even do this monthly after all the exports are complete. 

 

Jim Hook

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.

Jim Hook

Just a couple quick additional comments. I like your overall architecture keeping things like the employee master list and timesheets separate. That makes for easier support. You can come close to automating the client reports by generating a separate report for each client that scans the master timesheet and picks up summary data, or as much detail as you need. It might require that your master timesheet has a column that identifies entries for the current time period that each report will use to select which records to include in the report, the current month for example. That same column can then be used to filter for just the current month entries that can be copied and pasted into an archive google sheet or Excel before they get deleted. The archive file would then have everything prior to a new month to track entire projects to date.

I looked into trying to find a way to automatically track time people worked on a particular project but couldn't find one the did what we wanted. That has downfalls when people forget to log in or log out and you start getting bogus entries. We have people just do it manually with 0.25 hour resolution. You may also need some way for individuals to view how much time they have charged each day and each week as part of the process, possibly another report for each employee that scans the master timesheet and shows them all their records saved via the web forms. If your master timesheet includes one summary row per employee that could include daily/week/month totals that could become part of that report.