Neat List – Phone Call Memo

A neat list that is called Phone Call Memo is added when you activate the Group Calendar in SharePoint Online. It is a great way to track incoming calls and route them to the appropriate employee for follow up. You can set the Workflow to ensure that other employees in the company are also alerted about the call. The Phone Call Memo list also comes with four standard views:

  • My Unresolved Memos
  • My Memos
  • Memos from Me
  • All Unresolved Memos

All items list view:

SharePoint Task Plan

The post Job/Prospect Setup Alternatives discusses the option of setting up a project task plan. SharePoint has great project management tools to assist team members in undertaking projects and communicating to customers of its progress.  So let’s look further at how this could work for the Jim’s Family Store Irrigation Plan project. We will assume that QuickBooks Sample Larry’s Landscape & Garden Supply company took advantage of SharePoint’s great functionality and put together both the proposal as discussed in Sales Proposals in SharePoint Online and a task plan as discussed in Task List Templates in Service Delivery. Larry’s has decided that for project management and customer relations reasons they want to manage and report against the plan.(Note: I could have set up the task plan as described in this post and submitted it as part of the proposal.)

Here is what they did to set it up:

  1. They added the Irrigation Design Tasks template to the Service Delivery subsite by choosing the template under Service Delivery subsite>Site Contents>Your Apps as shown below.

2016-02-29 (1)

  1. They set up a “Load Estimates” list as depicted below and copied and pasted the Project Rate, Estimated Work, and Estimated $ from the Excel spreadsheet in the Proposal Library to the list.

  1. This resulted in all tasks, hours, project rate, and estimated amounts being loaded into the task plan. Larry’s project manager then went in and edited the task plan for Task Start and End dates and assigned employees to the tasks as shown below. You may notice that I didn’t include Estimate $ and Project Rate in the view below since these amounts are fixed for this particular project and we can see them under other views of the task plan. I did include Estimated Work since this helpful in assigning employees to the project.

EditTask

We now have the completed task plan as depicted below. You will notice that the total hours and project start and end dates are at the top of the columns.

The standard Task Plan app in SharePoint also includes the following views:

  • Calendar View. Depicting the dates of the tasks.

  • Gantt View showing tasks and their relationships.

Additionally, list views are added by SharePoint for:

  • Completed Tasks
  • Upcoming Tasks
  • Late Tasks
  • My Tasks

The diamonds in the Gantt chart indicate one day tasks.  Project deliverables can be shown on the chart by giving them one day start and end dates.

Dashboard & View Ideas – Taking the Project List One Step Forward

The post The Case for a Project List discusses the advantageous of setting up Jobs/Projects in SharePoint. Reporting being one of the major advantages. Let’s look at an example.

On the Project List web page, I developed a Monthly Estimate view based on the listing. The screen shot is below:

I grouped the Customers and Projects by Service Type and then computed the Monthly Hours and Monthly Revenue. The computation was easy to do since I knew the Frequency, Estimated Work, and Project Rate. Monthly Hours and Monthly Revenue are what SharePoint calls “computed columns”. They do not require programming but they require an understanding of SharePoint formulas. Anyone on your staff that understands Excel formulas can set them up. You may be thinking this view is helpful but where are the totals? You cannot total on computed fields without doing some custom programming. Since you’re a small business and don’t have either that type of staff or money, I don’t recommend doing it.

There is a much better solution and by the way a picture is worth a thousand words. Connect & Export this view to Excel:

Then develop several pivot charts and place them onto the web page to view the data. Each time the Project Lists changes the pivot charts are automatically updated with your changes. BTW pivot charts are extremely easy to do with Excel 2013 or 2016.

Below are couple of possible pivot charts and tables.

The Case for a Project List

In my post Job/Project setup alternatives, I discuss  a couple of alternatives once you get a project and want to monitor its progress and results.  To recap:

  • Set up a project team site if the project is either large and/or complicated.
  • Set up a separate task plan to manage the project.  This is a subset of the project team site.

In the post We Got The Job! Now What?, I discuss maybe not even setting up a project in SharePoint and just setup the job in QuickBooks.  I then list reasons why you may want to set up projects in SharePoint.  The last reason being reporting across jobs/projects. This reason is why you should seriously consider setting up projects up in SharePoint.

So to illustrate another project alternative, I took the customers and jobs for QuickBooks’ Sample Larry’s Landscaping and Garden Supply company and set them up in Project List. Here is screen shot of the list:

Projects List

I added Frequency, Estimated Work, Rate, and Estimated $ as fields not found in QuickBooks.  The frequency and estimated work is need for  customer & employee scheduling.  Since I already know the rate, frequency, estimated work, I can make a projection of total estimated customer revenue.  I added Service Type since I didn’t like the way QuickBooks sets up a separate description for the service being provided.  Service Type also let’s me report across all projects. Noticed that view we are looking at is only Active Jobs.  I also have views for Not Active Jobs and All Jobs.

I could have added similar information as is found in QuickBooks like Project Address, Project Contacts, etc. if I so chose. Based on this Project List, I can easily copy and paste the job information into QuickBooks as outlined in the import/export post.

I also decided to place this list on the main firm website since it will probably be accessed in various subsites.  Not only Service & Delivery for scheduling but also Marketing & Sales for customer relationship management.

Another advantage about having project information on SharePoint is that I can easily customize my list views to meet a particular business need.  The example I use here could not have been done in QuickBooks. It just doesn’t have that flexibility.   In fact, I had to run several custom reports and export them to Excel in order to put the data together the way I wanted it.

You may also want to either attach a copy of the Service Agreement to the project row or hyperlink the service agreement to the Service Agreement Library so it can be viewed directly from the list.

SharePoint Lookup Column Links

In SharePoint, the standard default is to present Lookup columns in a view with a link to  their originating list.  In the screen shot of the Prospect list from the Marketing & Sales subsite, the Lookup column Prospect comes from the Customer and Prospect list on the main site.

LookupColumn

The Prospect Name is in blue and if you click on the link you are taken to the main site and provided the opportunity to edit the related data.

It may not be desirable to provide the user this linked capability.  For example it is very easy to click on the link by accident what you are editing the row that contains the Lookup Column. Consequently, to provide the prospect name  without the link, you need to check the Lookup field to the “Add a column” listing even though it is the column you are looking up.  You then show the Add a column field choice in your view.  This field does not have a link back to the original list.

LookupName

What data goes in either SharePoint or QuickBooks?

 

There are several factors to consider in determining the company data that goes either in QuickBooks, SharePoint, or both.

  • QuickBooks Access.  Determine which employees really need access to your accounting data.  If they are neither undertaking financial management nor accounting  tasks, you probably don’t want them in your accounting system for security, accessibility, and cost purposes. So place the data they need in SharePoint.
  • # of Users.  If you have more than 30 employees that need access to QuickBooks, even the enterprise version isn’t going to accommodate your needs. Those that generate accounting transactions should stay in QuickBooks and meet the business needs of your other users through SharePoint.
  • Application Performance. If you have an in-house version of QuickBooks, you need to worry about performance.  Too many users slow the application down and users may not be able to efficiently complete their tasks.  SharePoint is a cloud application that runs on truly enterprise level technology (server  hardware farms, SQL Server, Windows Server, etc.) you can get non accounting tasks completed much faster. So use QuickBooks for accounting and SharePoint for tasks discussed through out this blog such as customer relationship management, scheduling, project management, document management, etc.
  • Cost.  QuickBooks user licenses can be fairly expensive. So do a cost comparison on SharePoint vs QuickBooks user costs for tasks that can be undertaken in either one.  Don’t forget to include the savings that may be generated by Office 365 for both SharePoint and Office together.
  • Accessibility.  If your employees need offsite accessibility and mobile device accessibility, SharePoint may be the best way to meet your non accounting needs due to its capabilities in these areas.

QuickBooks Doc Center or SharePoint?

QuickBooks’ Doc Center is where you can store a document, give it a name, title, description, comments, attach keywords so you can look up related documents, and attach it to a transaction or item. So do you keep your documents in QuickBooks, SharePoint or both?

Since the accounting for your business occurs in QuickBooks, then you should keep those documents, with two exceptions, just in QuickBooks. I see no need for Vendor invoices, checks, credit memos, etc. residing in two places. SharePoint really does not provide any advantages over QuickBooks in this area.

An exception may be in areas where you are generating financial or accounting documents outside of QuickBooks. The post Sales Proposals in SharePoint Online is an example. We can use Microsoft Office’s capabilities to produce templates for uniformity and efficiency, tie them into sales and marketing, and make them easily assessable to everyone in the business so they can use them as references for multiple tasks. Also let’s face it, the job estimation in QuickBooks is rather primitive. Another example may be customer and job billing. QuickBooks’ billing templates are limited and many small businesses use Word or Excel to produce more complete and attractive billing statements.

A second exception would be if you need non accounting employees to access QuickBooks documents. Why have to pay for licenses and maintain them as users within QuickBooks for this one purpose?  In this case you may want to keep them in both places.

Non accounting and financial documents are more effectively handled in SharePoint and I cannot see any value of keeping these in QuickBooks’ Doc Center.