Recording a SharePoint Project Bill in QuickBooks

After sending out the bill discussed in Preparing a Customer Bill from SharePoint, we need to record the receivable in QuickBooks. Below I’ve used a one-line entry in a QuickBooks Invoice to record the bill. Payments received from Jim’s Family Store are applied against the invoice in QuickBooks. An alternative could be to enter the amounts by item that correspond to each task. See my post Exporting Task Lists to QuickBooks on how this is accomplished.

Preparing a Customer Bill from SharePoint

The post “Project Task Plan Billing Using SharePoint” discusses how a project/job manager can set the project’s monthly billing based the hours charged to the project. The Excel spreadsheet referenced in that post, has all the data to be included in the monthly billing. Based on that data, we can easily produce a spreadsheet similar to the one below to send to the customer. This example is for the billing period April 1, 2016 to April 30,2016. The previous billed amounts were for the month of March.

We can then attach the spreadsheet to a word cover letter or even embed the spreadsheet in the Word document. A simple example is presented below. You can use Excel and Word’s rich features to develop a bill to meet your specific needs. This cannot be done in QuickBooks.

Additionally, you can establish a SharePoint folder in Service Delivery subsite’s document library to keep Jim’s bill and other relevant project documents.

Project Task Plan Billing Using SharePoint

We setup a task plan for the Jim’s Family Store Irrigation Plan, discussed how we could post time against the project and view those timesheet charges. So let’s stay on the path on not yet posting to QuickBooks and discuss how we use SharePoint for billing the project. Below is a SharePoint Datasheet View of the project timesheet list. It contains the following columns:

  • Task:Title. These are tasks that so far received timesheet charges.
  • Timesheet Year Month. This column later allows summing the hours by month.
  • Timesheet Date. The dates the hours are charged against the task. In this illustration, I want to make the billing decisions by day and this is why task 2.0 “Determine irrigation requirements” is displayed on three rows of the view. I could have gotten more finite and also summarized by employee.
  • Timesheet Hours. The total hours charged on that date.
  • Non – Billable Work. These are hours that are not to be billed to Jim but incurred on the project (e.g. training time).
  • To Be Billed Work. Hours that are to be billed in March.
  • Not Yet Billed. Hours to be billed at a later date.
  • Project Rate. The rate associated with the task. This is for informational purposes.

The project/job manager completes the Non-Billable and To Be Billed columns of the datasheet to compute the actual billing. The Not Yet Billed column is a computed columns. This datasheet view is also exported and linked to Excel. The two pivot tables shown below the view are based on the export and show Actual Work to Budget and Actual Billed Amount to Budget. They are embedded in the billing view web page so the budget impact of the billing is available to the project manager as they enter the hours.

If the decision was not to develop the billing by date but rather by month, the Non-Billable, To Be Billed and Not Yet Billed columns would have been added to the Excel spreadsheet and not contained in the datasheet view. The pivot tables would still present the same data the project manager entered the hours.

Viewing Timesheet Charges

The post SharePoint Task Plan – Tracking Time presents a SharePoint List based timesheet for charging time against a project task plan. Adopting this type of list based timesheet raises the question: How does a project or job manager view all the time tracked to project? The screen shot below presents a view of the timesheet list displaying time charges by year/month and then task and employee. Summary totals are also presented. This view provides an easy way of seeing all time charged to the project. Alternative views would be by task, year/month, and employee or by employee, task, year/month etc. Because it is easy for users to develop their own personal views in SharePoint, a project or job manager can develop a view to meet their particular needs.

The number in parenthesis at the end of the task, indicates the number of entries for that task.  Heading columns, e.g. Task Title, Timesheet Date, Hours, Comments, Staff Member, etc. are not depicted in the screen shot.  They appear at the top of the view.

SharePoint Task Plan – Tracking Time

The post SharePoint Task Plan discusses using SharePoint’s great project team features to develop a sophisticated project task plan. The project task plan includes tasks, subtasks, budgeted hours, budgeted $, assigned staff, task dates, a related Calendar, and Gantt Chart for planning and executing the project. This is all great for planning and proposing the project, but how can you track actual results when your project time and expenses are being captured in either QuickBooks or a time tracking application? This post discusses the options to capture actual time charges against the project. These options are:

  1. QuickBooks. – You can enter the time in QuickBooks by first exporting the tasks as Service Items as discussed in the Exporting Task Lists to QuickBooks post and then use QuickBooks’ time sheet to capture the billable and not billable time as depicted below:

    You can then run either a QuickBooks’ “Job by Time Summary” or a “Job by Time Detail” report and post the actual hours back to the SharePoint Task Plan or to an Excel spreadsheet (Note: I discuss an idea for the former in a separate post). However, some organizations do not use the QuickBooks timesheet either because of # license and permission issues or because in QuickBooks the decision on whether hours are billable or not billable must be made during time entry. This decision usually resides with the project or job supervisor and often occurs when the project bill(s) is being prepared. QuickBooks doesn’t easily handle either writing off hours or deferring hours to a later bill.

  2. Third party SharePoint Timesheet software. There are a few applications available for purchase. However, most of these are geared towards larger businesses, require some modification, and are expensive to license. Also without some custom development they do not get the hours over to QuickBooks for billing.
  3. Third party non SharePoint Timesheet software. There are many third party timesheet alternatives that integrate with QuickBooks. The issue is that they do not integrate with SharePoint and like option 1 above, require posting the hours back to SharePoint or like 2 above require customization to post back.
  4. Custom Develop a SharePoint Timesheet. I would not recommend this alternative for small businesses because of the development and maintenance cost associated with it.
  5. Use a SharePoint List for the Timesheet. This would provide a basic timesheet using SharePoint’s out of the box functionality. It would require some type of posting back to QuickBooks. Thuan’s post on The Soldier of Fortune blog titled “Building Timesheet Management Solution in Office 365 Without Code” goes through such a solution. His solution has the user picking from a list of all projects and then picking a task from all tasks. This could work for organizations that charge at the project level but may be problematic in organizations with many projects and tasks since there are no edit controls. If billing is undertaken in QuickBooks, then a manual posting back is required.
    Below is screen shot of a timesheet alternative based on Thuan’s post. The timesheet is a SharePoint list based on Sample Jim’s Family Store Irrigation Plan SharePoint task plan. Because the list is based on the SharePoint task plan specific to that project, the tasks that can be charged are only the ones specific to the project. The drawback to this approach is that a user would need to go to each project that has a task plan to charge hours to a specific task.