Full Sail Partners Blog
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!
