I don’t know of a single Professional Service firm without a formal contract management process. Firms spend significant dollars purchasing contract templates and some even retain legal counsel for review. Everyone knows that the contract is an important document in any project undertaking.

Nevertheless, those same firms often begin work without getting the document signed!

Does the importance of the document cease once work has begun? Of course not. But what happens is that the production machine must and does begin before the administration machine can catch up. The contract seems to be subjugated to more pressing needs and the team begins operating on ‘Good Faith’.

External stakeholders have a vested interest in the firm’s contract habits. Professional Liability Insurance applications have at least a few questions relating to whether or not a contract is required, how much work is performed without a contract, and so on. Even the bank wants to know about the status of firm’s contracts and the processes employed. Banks analyze aging or large Accounts Receivable (AR) and next ask about the project’s contracts status. Both want to assess the risk inherent in providing their products to the firm.

It’s All About the Risk

Business is risky. There is no way to eliminate it, only to reduce. Written contracts certainly mitigate risks. Signed ones reduce it further.

Having a signed contract doesn’t guarantee against litigation. There are always competing interpretations of the language after the fact. Better contracts decrease collection time and reduce disputes.

Not having a signed contract does not mean that the firm will not be paid. As long as it is clear that money was to be exchanged for services, there will be some value exchanged. However, the firm might have to perform more work than intended for the stated amount. Additionally, it might mean considerable expense or time attempting to get those dollars.

The truth is, all Professional Services firms have a tendency to do some work without a contract. How much, how often, and with whom is important information that should be assembled and available for review by stakeholders to stay informed about the risks of the firm.

Leveraging the Deltek Vision Contract Management Feature

The Contract Management feature included in Deltek Vision’s Finance Core is one such tool that can be used to assist in accumulating information on this risk factor in the firm. The tool and system are designed specifically with project-based firms in mind. Let’s take a look at how this feature can help:

Track It

Deltek Vision Contract Management allows users to track multiple contract documents, their origination and approval dates, status, and fees requested per document. Because the information is entered on each project, it makes a variety of reports and workflows available allowing for efficient and integrated contract tracking. Optionally, information can be extended neatly into the Project’s Work Breakdown Structure which will allow users to easily see which documents created and amended the fee on each level.

Identify It

By using the Contract Management area of the Project Info Center, users can add the information to reports already in use. Both the Project Summary and Office Earnings allow the Contract Management fields to be selected as columns to appear along-side the existing data on firm reports. Contract statistical data can also be used as a filter on other reports.

Manage It

Integrating project contract information into Deltek Vision allows users to employ the powerful workflow engine in the software. Vision Workflows enables Contract Management users to automate the reporting and informing process around Contracts.

Consider these workflows:

• Notify Project Manager and/or Principal when a contract has been marked as Approved
• Remind a user of special handling when a contract exceeding $XXX,XXX has been created
• Update a column on the Project record when a contract record has been inserted

Use It

Deltek Vision Contract Management users can leverage the information about contract status in other organizational processes. The Contract Management fields are available as filters on any project-based report. This allows the contract status to become a part of the firm’s management process.

Consider using Contract Management data and filters in these activities:

• Client Management – Create a Project List report for active projects and sorted by Client. Include Contract creation date and status as columns to get the most from the client check-in call.
• Collections Management – Create a scheduled report for outstanding AR where contract documents are in ‘Pending’ status. Create an AR alert for projects with certain contract status’.
• Employee Management – Use the Optional Sales Credit feature to measure employees’ participation on specific contract documents. Use in reviews or statistical reporting.

Know the Risk and Accept It

None of us live in a perfect world where a single project meeting hasn’t been had until the contract is signed. However, our focus should turn to understanding and measuring the risk that will be taken on. Firm management should be well informed of the status of the firm’s contracts and have the necessary information on hand should they need to intervene. To support this, contract tracking and management should be integrated with the projects to which they relate. Your Vision software is already well equipped to assist you in the mechanics.

To get started today you can:

• Review our technical webinar on the Contract Management feature
• Enter a test contract or two to understand how the feature works

• Identify how your current process can improve with automation, integration and reporting
• Backfill existing contract data
• Contact a Full Sail Partners consultant for more advanced training to customize menus, write workflows and complex reports.

As a virtual employee I often have a computer within arm’s reach. The need to access Deltek Vision on a mobile device has largely been non-existent for my work situation. However, recently I attended back-to-back conferences and I quickly found myself behind on my timesheets and desperately needing to submit an expense report. So one afternoon, as traffic died down while manning a tradeshow booth, I decided to install the Deltek Touch Time and Expense app on my phone.

The following blog outlines my thoughts and experiences using the app!

Deltek Touch Time and Expense:  What’s All the Buzz About?

This app is really easy to use! No seriously, it is. The clean smooth detailed design of the user experience shines through immediately. I quickly found myself navigating the menus and feeling a sense of insight regarding where I stood on past, present, and current timesheets and expense reports.

Expense reports have never been less of a hassle. I typically experience a lot of anxiety when it comes to doing expense reports. The idea of managing multiple, high value, expenses can be stressful to say the least. For me, Deltek’s Touch Time and Expense app took the stress out. I was able to quickly take a photo of my receipts with my smart phone, attach them to my expense report in Touch T&E, and voila – expense complete!

After long stints out of the office timesheets are no longer something I dread. Nothing sucks worse than being out of the office for days at a time. Oh wait – you know what sucks more? Trying to figure out how to bill all that time that you spent out of the office!

Managing my time on-site at these conferences with Deltek Touch Time and Expense could not have been easier. During down times I found myself quickly plugging my time in to Vision from my mobile device. No more sticky notes or emails to myself with logs of my time. This process was so easy that I now find myself using the application to log my day-to- day time after work while watching TV with my wife.

Like any application on a mobile device, Wi-Fi helps! The conference I was at had so many people using their phones that the 4G networks were basically useless. I quickly found a Wi-Fi hotspot and connected up and noticed a considerable increase in performance. Stay aware of the environment you are in and the technology you have available and make the most out of it!

This Deltek Touch Time and Expense App is Pretty Cool!

Overall I was extremely impressed with this application. The ability to instantly access my timesheets and expense reports on a mobile device was a life saver!  I’d encourage you to try the app out for yourself and let me know what you think.

Ready to start using the app? Watch this Deltek Touch Time and Expense Application tutorial and start doing more with your Vision system from a mobile device:

Are you ready? Year-end is upon us. As part of a yearly tradition, we are providing you with resources and reminders to assist your firm with year-end processing. Check out the information below, and let us know if you have any questions.

Don't Forget Your Deltek Vision Year-End Processes

As we come into the holiday season many of us in accounting face the coming season, not only with the joy of family and friends coming together, but with a sense of impending dread – YEAR-END and TAX SEASON are looming – ugh - have another drink.

W-2s - 1099s - journal entries - checking and double checking each number!  Auditors - shareholder meetings - endless explanations’ of what the results mean (even though you have been saying for the past 6 months) - Oh the joy of the season!

As we go through the year-end process there are mistakes we (and others) make that are easy to recover from like entering the 15 page depreciation journal entry “backwards” at midnight sitting in your semi dark office chugging your 15th pot of coffee.  Some are more difficult – like removing all the lower levels of your work break-down structure (did that once – glad our backups worked). So outside of deleting the database, what are some of the items in Vision year end processing that are hard to recover from?

>> Learn more

Is Your Deltek Vision System Year-End Process Stress Free?

Year-end is always a stressful time of year, but it doesn’t have to be.  As with everything else in the world of Accounting, planning and preparation is the key to a successful and stress-free year end. Check out this article talk provides a few tips to make your year-end close just a little easier.

>> Prepare more

Webinar: Stress Free Year-End Process - Yes You Can!

Join our on-demand webinar to see how you can make your year-end stress free.  Our panel will provide insight into best practices, tips to close your books with ease, and focus on those “gotchas” on year-end processes. See what our panel says about the following year-end items:

1. How to make your process efficient
2. Avoid disruption to production
3. Communication topics to provide your staff and management
4. Saving money by evading closing obstacles

>> View more

Deltek’s Year End Resources

Deltek Customer Care is working to assist you with year-end activities. You can now take advantage of year-end resources:

• Year-End Forums
• Phone Menu Guidance
• Year-End Info Center
• Quick Chat Guidance
• Year-End Guides & Videos

>> Know more

We hope these resources serve as good reminder. Best wishes from the Full Sail Partners' staff! Wishing you a prosperous 2014.

It’s Time to Upgrade! Regulatory and Year-end Updates Available ONLY on Vision 7.2 or 7.3

Working in the most recent release of your Deltek solution ensures your firm is benefiting from all the features and enhancements engineered into the latest versions of Vision. Your support and maintenance plan entitles your firm to download the latest version of your product here if you're not up-to-date!

Important Note Regarding Year-End Regulatory Updates in 2014

Year-end updates in 2014 will be supported only on Vision 7.2 and Vision 7.3. Regulatory updates for releases prior to Vision 7.2 will not be delivered.

Check out the Deltek Support Assurance Product Lifecycle on the Deltek Customer Care Connect website for the latest information on your product's status and definitions of the support delivered in the Active, Maintenance, and Sustaining phases.

Is Your Firm Prepared for Deltek Vision 7.3?

Because of Microsoft’s de-support of Windows XP in April 2014, Deltek can no longer support the Windows XP client operating system beginning with Vision 7.3 and extending to future releases. 

The following client operating systems will be supported for Vision 7.3:

• Windows 8 or 8.1
• Windows 7
• Windows Vista

Additionally, to use this new version you must ensure that .NET Framework 4.5 or 4.5.1 is installed. For more information on the .NET Framework and to determine how best to deploy the 4.5 or 4.5.1 release within your organization, please refer to the Microsoft web site at http://www.microsoft.com/net.

It’s time to break away from the chains of your desk, and get mobile with Deltek Touch Time & Expense, and Touch CRM 1.3. With these apps, both available in iTunes and in the Play Store, it is now easier than ever to research (and update) contact records, as well as capture expenses and track time on the go. Take advantage of the following features, and provide your staff with the most powerful remote tools and utilities to do their jobs to the fullest:

Deltek Touch Time & Expense 1.3:

• New Name, New Features, Same Reliability | In this release, expenses were added to the Touch Time application, and the application name has officially changed to ‘Touch Time and Expense’. No more waiting to manage timesheets and create expense reports. Avoid forgetting the details or losing receipts by entering as you go. Wherever your mobile device is, Touch Time & Expense follows.
  
  
• A Picture is Worth a Thousand Words | Touch Time and Expense allows you to create a new report, and then associate expense with that report. Take pictures of receipts, and upload them directly!
  
  
• Numerous User Experience and User Interface Upgrades | A number of improvements have been made to the application to improve your experience. Upgrades include:
  
  
  • A sliding menu to replace all tabs and provide quick access to Help and Log Out.
     
  • A menu button to allow you to perform specific tasks for timesheets and expense reports.
    
    
• Math is Hard, Let Your Apps Figure it Out | Touch Time & Expense allows you to select or update default transaction currency and exchange rate for an expense report. This screen, however, only displays if Multicurrency is enabled in Vision core.

Deltek Touch CRM 1.3:

• Touch Knows How to Push Your Buttons | Touch CRM now has buttons, with icons, to replace labels on certain screens. Examples of new buttons include:
  
  
  • ‘List Button’ - Tap this button to display options specific for the contacts, clients, and opportunities screens.
     
  • ‘Pen Button’ – Tapping this button allows you to edit contact, client, and opportunity information.
    
    
• No Mobile Limitations | Touch CRM allows you to search, edit, and add clients on the go. In addition, add or update Opportunities with the touch of a button.
   
• Syncing Made Easy | Touch CRM automatically syncs up with your Deltek Vision system, and allows you to create activities and calendar events on the desktop, and access them on your mobile device.
   
• Full Visibility | See user defined fields in contacts, clients, and opportunities. Additionally, Touch CRM 1.3 gives you the ability to view activities!
   
• It’s All One Touch Away | Make a phone call, send an email, or map an address with the touch of a button.
  
  
• And more | Interested in learning more about the features and functionality included in Touch 1.3? Reach out to us today for a demo, and start operating better!

Are you currently using the Touch applications? If so, respond below and let us know what your experience has been like so far.

Myth or Reality: Higher utilization yields higher revenues? Well, both actually.  But before I explain, I’m going to start with this quote from Zig Zigler,

“Success is the maximum utilization of the ability that you have.”

This is the perfect quote to start our discussion regarding utilization, because it specifies that success (i.e. defined by higher revenues for most companies) comes from utilization of ability and NOT from a higher utilization of time.  

The time utilization myth

Too many professional services firms looking to improve revenue and profit numbers are chasing this goal a bit myopically.  Their logic sounds reasonable but comes up against a “theory vs. reality” wall:  more money comes from higher utilization of resources, i.e. their consultants.  So they put into place incentive programs that encourage their consultants to work more.  Consequently, these consultants put in high numbers per week leading, too often, to burnout and turnover coupled with the often very ugly effect of lowering customer satisfaction, because the consultant has lower motivation for solving and higher interest in billing.  

The balance with reality

Luckily, these days, more and more professional services firms are wise to this “higher utilization brings higher dollars” fallacy.  They understand that chasing the utilization dollar by itself is a delusion.  Here are the steps of the real logic:

1. Greater financial rewards for companies come from happy, prepared, challenged and respected employees, because
2. These employees do what’s best for their customer, while still balancing the number of hours it took to solve their customer’s problem, and
3. Then, customers who have had previous problems solved, use your company’s consultants again thereby generating even more revenue.   

This brings us back to our initial quote –business success comes from utilizing employees’ abilities and not just their time. 

But how?  How does a company go from making employees focus on utilization to their being self-motivated to be happy and work more, successfully?  Here are three suggestions.

1. Know, really know, where your consultants strengths are.  Why is that important to utilization, you ask?  For two reasons:  1) it’s not the number of consultants you use; it’s the right consultants, and 2) happy employees who are on project they are good at work harder simply because they are happy.  A circular logic that is, indeed, true.  If you know what your consultants are good at and organize projects accordingly, your happy employees will continuously work a) harder because they enjoy it and b) smarter because they are good at it. 
2. Get rid of “high utilization” numbers as the sole focus of employee incentive plans.  Sure, it’s okay to keep a facet of utilization in the verbiage of their incentive programs, but balance that with another positively motivating focus like customer satisfaction, so that employees get the message that they need to work hard to solve customer issues and not just bill high numbers.
3. Better utilization comes from better processes. Take a close look at the work your consultants are doing.  Are they overwhelmed with administrative and/or manual processes (e.g. filling out project planning forms, expense reimbursement processes, or dealing with emails/directives which constantly impact work week schedules)?  Now, take a close look at how many of these could be automated or even switched to a different employee whose job is NOT consulting with customers.

Utilization is not just about improving your professional services firm’s revenue and profit numbers; it’s really about

• Automating processes to make not only your consultants but also your entire company more efficient
• Shifting administrative work to non-utilization based employees
• Balancing utilization incentives with more employee and/or customer focused incentives
• Focusing on your consultants’ strengths

But you don’t have to do this alone.  Reach out to us to take a look at your utilization and see, first hand, how making these small changes will yield big results. 

The newest version of Deltek Vision (7.3) has been released, and along with it comes a new slew of powerful features and functionality. As always, operating on the newest version of Vision gives your firm the ability to manage better, achieve more, and improve your ability to better track your projects and efforts. Let’s take an overview glance of the new benefits.

Vision 7.3 Product Enhancement Synopsis

• Manage company paid credit cards | 7.3’s new credit card functionality streamlines cost accounting. Features include the ability to setup credit cards, import credit card charges, and reconcile credit cards.
• Expanded internationalization and localization features | New globalization functionality included in 7.3 gives your firm the tools needed to expand into new markets, and win more work!
• Improved compensation break out | Gain additional insights into fee structures with detailed compensation breakouts.
• Dashpart enhancements | Users can now choose which columns to include in info center dashparts; including user defined info center dashparts. Additionally, the dashpart can be populated with work breakdown structure level two or three record data.  Lastly, users can create an Invoice Review dashpart; providing a quick summary of outstanding receivables for chosen projects.
• Core CRM | Users can now edit activities directly in the grid. Many new text editor features have been improved; including highlighting misspelled words and the ability to add words to the spelling dictionary.  Enhancements to the Opportunity Forecast Report; including revenue allocation for fiscal years.
• And more | Interested in learning more about the features and functionality included in 7.3? Reach out to us today for a demo, and start operating better!

Is Your Firm Prepared for Deltek Vision 7.3?

Because of Microsoft’s de-support of Windows XP in April 2014, Deltek can no longer support the Windows XP client operating system beginning with Vision 7.3 and extending to future releases.

The following client operating systems will be supported for Vision 7.3:

• Windows 8 or 8.1
• Windows 7
• Windows Vista

Additionally, to use this new version you must ensure that .NET Framework 4.5 or 4.5.1 is installed. For more information on the .NET Framework and to determine how best to deploy the 4.5 or 4.5.1 release within your organization, please refer to the Microsoft web site at http://www.microsoft.com/net.

Payroll, the reason we drag ourselves out of bed and into the office.  Payday!!  Everyone’s favorite day – it is especially nice when it is on a Friday.  As happy as payday makes us, if you are the one stuck processing your payroll software can be a total PITA.

Utilize In-House Payroll Software vs. Out Sourced Payroll

So why process payroll in-house?  Why not just hire a service?  In many cases a service is exactly what you would use.  However, payroll services have their own set of challenges:

• Rigid deadlines that may cause you to process payroll days before your pay date (in some cases before the pay period ends)
• Inability to add non-payroll items (like expense reimbursement) to the payroll
• Can be a bit more “pricey”

When looking at a way to process payroll in-house, a system that integrates with your existing ERP system, such as Deltek’s Vision, is priceless.  You can have a one stop shop for employee set up, employees can enter their time and it is automatically available in payroll when posted.   Since everyone is already used to the system, the basic information is already there.

Some of the other benefits of having payroll in-house and integrated with your ERP are:

• Employees will have access to see their vacation and sick time remaining
• Access to see and print copies of their paychecks (Meaning you don’t have to provide their last 5 paychecks)
• Accounting can control deadlines
• Can sometimes be less expensive

What to Look for in Payroll Software

If you determine that in-house is the way to go, it’s important to make sure your payroll software is flexible and provides the features that matter.  So what type of flexibility should you look for in your payroll software? A couple features include the ability to add codes when you need it or to track your company contribution to the employee benefits.

An example of an additional code is the ability to add an after tax code to reimburse your employees for expenses.  This allows you to provide your employees one check for payroll and expenses.

An example of a contribution code would be if you offer a 401K plan, the company match is your company contribution.  If you offer health care, the portion covered by the company is the company contribution.  Tracking this information on each payroll can simplify your accounting process (getting the costs into the right “bucket”) but can also allow you to easily create a total compensation report at the end of the year showing the employee the total value of their compensation package including benefits (not every service offers this either).

Either way you go, an in-house payroll software or outside payroll service, look for the process that will provide you the most flexibility and integration with your current ERP to make your life easier!

Are you ready? Year-end is upon us. We thought we would highlight some resources to assist firms with year-end processing.

Don't Forget Your Deltek Vision Year End Processes

As we come into the holiday season many of us in accounting face the coming season, not only with the joy of family and friends coming together, but with a sense of impending dread – YEAR-END and TAX SEASON are looming – ugh - have another drink.

W-2s - 1099s - journal entries - checking and double checking each number!  Auditors - shareholder meetings - endless explanations’ of what the results mean (even though you have been saying for the past 6 months) - Oh the joy of the season!

As we go through the year-end process there are mistakes we (and others) make that are easy to recover from like entering the 15 page depreciation journal entry “backwards” at midnight sitting in your semi dark office chugging your 15th pot of coffee.  Some are more difficult – like removing all the lower levels of your work break-down structure (did that once – glad our backups worked). So outside of deleting the database, what are some of the items in Vision year end processing that are hard to recover from?

>> Learn more

Is Your Deltek Vision System Year End Process Stress Free?

Year-end is always a stressful time of year, but it doesn’t have to be.  As with everything else in the world of Accounting, planning and preparation is the key to a successful and stress-free year end. Check out this article talk provides a few tips to make your year-end close just a little easier.

>> Prepare more

Webinar: Stress Free Year-End Process - Yes You Can!

Join our on-demand webinar to see how you can make your year-end stress free.  Our panel will provide insight into best practices, tips to close your books with ease, and focus on those “gotchas” on year-end processes. See what our panel says about the following year-end items:

1. How to make your process efficient
2. Avoid disruption to production
3. Communication topics to provide your staff and management
4. Saving money by evading closing obstacles

>> View more

Deltek’s Year End Resources

Deltek Customer Care is working to assist you with year-end activities. You can now take advantage of year-end resources:

• Year-End Forums

• Phone Menu Guidance

• Year-End Info Center

• Quick Chat Guidance

• Year-End Guides & Videos

>> Know more

When it comes to proving the value of marketing efforts, often professional services marketers have to prove their worth to the financial department through a language that they understand – Marketing Metrics! This often means a series of pre-determined metrics for measuring marketing ROI (return on investment). 

Marketers are often challenged with measuring marketing ROI. Many times it’s because we are don’t have access to the right type of data or in some cases it’s because we don’t know what to measure. This is where having a good relationship with finance can help you be a better marketer. To better develop the relationship and expectations between marketing and finance, we suggest fostering a relationship of understanding and sharing. 

Having the financial department on your side is one of the greatest feats any marketer can accomplish – If finance buys in, you can be assured that it is only a matter of time until everyone else falls in place! 

No matter how copasetic our relationship is with our financial department, we have to be ready to report on marketing ROI at a moment’s notice so here are some steps to take to gain a better relationship. 

Talking the Talk 

If you are looking at building a better relationship with accounting, in my experience the first bridge to cross is to put yourself in their shoes. When you think about what functions accounting is responsible for, you can easily understand their hesitancy to buy in to the marketing plan without cold hard data to evaluate. Instead of running from this hurdle, attack it straight on! Schedule a kick-off meeting with finance to address the plan, and allow them to voice any concerns. 

The goal during the kick-off session is to ease accounting’s anxiety.  Allow the finance department a chance to express their suggestions and concerns. Continue to reassure the finance team that through the marketing metrics established by your firm, you will be consistently measuring marketing ROI throughout the year to ensure that the marketing team’s plans and efforts stay on track.

Walking the Walk 

The quickest way to gain buy in is to lead by example. You know your job better than finance knows your job. Identify areas that your marketing efforts affect that might not be easily identified.  One way a Marketer can begin to do this on their own is to think about the data that you need to do their job better. Come to the kick off meeting ready to show your finance team that you understand their concerns, by identifying previously overlooked metrics for measuring marketing ROI. This will demonstrate to finance that you are looking at metrics that can help impact the growth of the company and further prove your value to the firm. 

If you are interested in learning more, review this blog article that discusses evaluating your business growth plan with metrics. This introduction can be applied to developing marketing metrics that help identify how your efforts are helping the firm grow. 

Here are important questions marketers can ask accounting to start the conversation on how the firm can start measuring marketing ROI:

1. Retaining & Gaining Clients: I’m looking to understand our total customer growth. Do we have a way to determine by percentage and revenue the amount of our work we’ve received is new vs. existing clients throughout the year?
2. Pursuing the Right Client: I’m been looking at how we can be more strategic in our pursuit of clients. Would it be beneficial to advise you when I see we are pursuing more work with clients that we are having AR issues with?
3. Forecasting and Backlog: Can you help me understand what our break-even is and do we have a way to see what our current backlog is? I’d like to help make sure we have enough business coming in the pipeline for each market or division.
4. Effectiveness: Can you help me better understand how I affect the bottom line? Developing metrics that help you understand the financials behind your results can help you fine tune your approach. 

Often times your finance team is not questioning the value of the marketing team -- they are however questioning the tactics (and results!) being used. Often times as marketers we can get lost down in the weeds and lose sight of the overall firm goals. By proving efforts through metrics and marketing ROI, we start speaking a language that our financial team can understand. 

As professional services marketers, start showing your finance team that you care by measuring marketing ROI, and building better relationships between marketing and finance to demonstrate the value of promotional efforts.

For more information, view the below webinar: 

We all love this this time of year when EVERYONE (in management at least) is coming at you to peer into the crystal ball to see what the upcoming year will bring -- everyone else is simply wondering if they will get a holiday bonus. With all of the tasks ahead for year-end, you may feel as though you will be lucky enough to make it to the New Year, let alone forecast what is going to happen!

Budget, budget, toil and fudge it (not as well-known as bubble, bubble, toil and trouble, but more appropriate). 

Most often you are expected to reach out to everyone in the firm to pull together a web of disparate “visions” for the various divisions of the firm, get buy in from everyone, set pay rates and bill rates, set annual goals for every person in the firm and publish your findings so that everyone can have proper “visibility”.  Just when you have it all together and about ready to go, someone transfers or quits or gets hired and everyone wants to you to “simply update the budget”. Fun times – just before the holidays. Unfortunately, if you are attempting to manage the undertaking of forecasting in Excel, you are causing yourself unneeded stress in an already stressful situation!

What are some of the hazards to be aware of when forecasting in Excel?

Multiple Spreadsheets – Typically when creating a budget in Excel you end up with multiple workbooks, at a minimum one for every department that will then roll up into a firm-wide budget.  And in each of these workbooks you can have multiple spreadsheets to total up the workbook (maybe a spreadsheet for each service type or for each employee).  As you move these workbooks, you run the risk of breaking links within each individual spreadsheet.

Changing Dynamics – When you create your budget workbooks, you usually create them to “map” the way the firm currently does business.  For example, at an AEC firm, the Survey Department staff only works for the survey department and the Water Recourses staff only works for the Water Resources department.  The issue is that the department heads might have hatched a plan to share resources to maximize efficiency. But if you have all your workbooks mapped out and they come back with their scheme you will need to “revamp” all your work.

Oops, I Forgot – When you created your workbooks you simply forgot a chart of account numbers or forgot that management wanted to add an employee in the corporate office.  Once you have everything mapped, if you have to add something, you run the risk of breaking links (bad) or creating incongruent links (worse).  An incongruent link would be adding a row in the Survey Department called “Survey Supplies” but forgetting to map to the row in the summarized data called “Interest Expense” because the Survey Supplies is only in the survey department and Interest expense is only in the corporate department.

It Doesn’t Add Up – So you have everything set-up and summarized and you have an acceptable profit.  However, when you pay closer attention, you realize that in January, the sum of your departments properly add up.  Somewhere, something is broken.

What can you do to avoid or mitigate these hazards?

Meet Early – Before you even get started down this long road of forecasting in excel, try to meet with the department heads to get an idea of changes they may have planned or discussed.  If you have suggestions for them to be more efficient (for example work share), get it out there for discussion and resolution before you get too far down the usual primrose path you find yourself following year after year.

Share Information – First figure out if there is truly a place where you can put your workbooks, create the links, and share with the appropriate staff.  This could simply be a shared drive in your network.  If you need to limit access for the workbooks (for example the San Francisco Engineering department head can only see the San Francisco workbook), this can be achieved via the “password protection” in Excel (found in File/Protect Workbook/Encrypt with Password).  Be sure to make a list of passwords for each workbook because if you lose them, you have completely lost your ability to use the workbook.

If you can’t create a shared drive, use your personal drive to save your workbooks until complete. Then you’ll want to only print final data. 

Having links break when you move your workbook is frustrating.  If you do have to move workbooks or update tab names, you can use the Update Links in Excel (Data/Edit Links/Update source) to get the correct mapping.

Be Consistent in Your Workbooks – Use the exact same chart of accounts and structure for each workbook – even if the information doesn’t apply to your department.  If you are consistent in your account listing and your workbook structure you do not have to worry about incongruent data.  If you realize you have to add an account number, make sure to add the account in the same row in every workbook and you can update your summary easily.  If you have utilization in your engineering department, have it in Corporate –even though it doesn’t apply – makes it easier to simply copy and paste formulas throughout the entire summary workbook.

Create Summary Rows/Columns – In your summary workbook, when you have subtotals in rows or your grand total column, use the column/row formulas to calculate these; DO NOT use the sum from the individual workbooks.  However, after your Total Column, add a column that adds the total columns from the individual workbooks to compare your results.  This will allow you to see if there is a mistake in any of the individual cells.  Also, at the bottom of your summary workbook, add a section that shows the profit from each department.  This should add to the total in your summary. If it doesn’t, then you may have to do some research into what is causing the issue.

We hope that the above tips help you avoid some of the most common hazards encountered when accounting and forecasting in Excel. With year-end approaching quickly, best of luck getting your accounting and forecasting in order, so that your firm can ‘excel’ in 2014!

If you are interested in taking your accounting and forecasting functions to the next level, check out Deltek Vision, the industry leading Enterprise Resource Planning (ERP) software for A/E and Professional Services Firms!