Kickoff of Budget Season

The chill in the air, the smell of brats and burgers wafting through crowded parking lots, the excitement on the painted faces of rowdy fans… it can only mean one thing:  It’s time to kickoff Budget Season.

The quarterback sneak, the triple option, the end around, the hail mary and the punt – these are all moves managers make throughout the budget approval process with varying degrees of success.

What tools do you use for budgeting?  Do you rely solely on Excel spreadsheets or do you use a more sophisticated budgeting software such as Microsoft’s Management Reporter or do you use a hybrid approach such as Vena Software? The advantages of going beyond Excel include the ability to manage workflow throughout the process, to make your individual budgets more consistent and structurally secure and to roll them up easier for automatic visibility to the big picture.  Most companies start out using only Excel spreadsheets and eventually evolve to a more structured format.

Regardless of the software you use, your budget should typically include the following components:

(1)  A schedule of dates and deadlines that clearly spells out who is responsible for what and by when.

(2) Payroll by employee including open positions and additional headcounts – this schedule should roll forward into the budget to calculate labor costs and benefits and may also be used to allocate expenses between departments.

(3) Fixed Assets and Capital Expenditures – this schedule should roll forward to the P&L for depreciation and to the Balance Sheet/Cash Flow Statement for capital expenditures.

(4) Comparisons to current year and long term plan.

(5)  The aforementioned “Cash Flow Statement” should be part of your budget.

I have to get on a plane now.  What other components should be built into a good budget?

Eliminating Inefficiencies in Excel Spreadsheets

excel_university_logo_100I attended an IMA meeting this evening and the guest speaker was Jeff Lenning, President of Click Consulting and author of “Excel University: Microsoft Excel Training for CPA’s and Accounting Professionals”.

The theme of the presentation was basically “eliminating inefficiencies in recurring use spreadsheets”.  I build a lot of large spreadsheets with vlookups, pivot tables, charts, if statements, conditional formatting and other relatively advanced functions.  To be honest, I did not expect to learn much, but as is often the case, the more you know, the more you realize how much you don’t know.  I quickly learned several relatively simple tricks that I can apply more consistently throughout my recurring use spreadsheets to save a lot of time for myself and my clients.  I’ll summarize some of these below:

  • Stop using SUM where SUBTOTAL is more efficient.  The SUBTOTAL function excludes other subtotals within the range, so it eliminates the need to revise the ‘grand total’ formula when new rows or subtotals are inserted into the range.
  • Add a “skinny row” before formula rows.  A “skinny row” is a blank row with a relatively small row height.  Adding a skinny row eliminates the need to adjust the formula row or the formatting when inserting a new row at the bottom of the range.
  • Hide rows and columns using Data-Outline-Group feature rather than using the hide function.
  • Use the FILTER function in conjunction with the SUBTOTAL function to create methods for other users to sort and subtotal data that are a little simpler to use than pivot tables.  I like pivot tables, but they aren’t for everyone so this is a good alternative.
  • Use a “Start Here” worksheet for input areas and highlight input cells with the “input” cell style.
  • Use an “Error Check” worksheet to ensure integrity throughout multiple worksheets.
  • Use hyperlinks to ease navigation between source data and other worksheets.
  • Use keyboard functions (rather than right click or ribbon icons) for common features such as “F2” to edit a cell, “F4” to toggle through cell references, Arrow+Shift/CTRL to scoot around your worksheet more efficiently than with your mouse, and double-clicking the lower right corner of a formula cell to push the formula down as far as there are adjacent cells.

I have used many of the tips above, but the key is to use them more consistently.  If you have any additional Excel tips or tricks you like, please share them in the comment section below.