Forecast employee payroll and bonuses: Free payroll budgeting tool

Hand point to the screen with histogram
istock_jxfzsy_aw
James Power
Columnist
Share this content

Ever tried to forecast changes in payroll quickly and effortlessly for an entire organisation?

How do you easily calculate the actual P&L effect of changes relating to leavers and new starters? What about accruing bonuses that pertain to a particular accounting period (eg sales commissions) but are then paid at a later stage in the year? What is the true cost to an employer for an entire team of people all on different salary scales and bonuses? And have you remembered to factor in the new Apprenticeship Levy and Employer NI Rebate?

Free of charge and exclusively for AccountingWEB readers, the Payroll Budgeter Tool (link to external download) brought to you by FD4Cast is one of the most complete and simple to use payroll budgeting tools around. Designed for start-ups, accountants, financial modellers and finance directors, it aims to take the headache out of trying to correctly forecast what tends to be the biggest cost for any organisation.

As a ‘stand-alone module’, this robust tool can easily be linked to or incorporated into more comprehensive forecasting custom-built models that users may want construct themselves, but saving the user time and giving them the reassurance that the calculations are correct.

With a forecast horizon out to five years, the free payroll budgeter offers the unparalleled flexibility and familiarity of Excel-based forecasting tools, with calculations that reference annual tables updated by the user which contain HMRC rates for PAYE, NI, pension contributions and the Apprenticeship Levy.

Driven by macros, users are able to dynamically expand the number of employees in the tool to match the business being modelled with just a simple click of a button. To save time incurred in populating gross salary amounts for each month per employee, users can simply enter an annual salary amount for each employee and then click a button which will apportion the correct amount in the relevant monthly columns. Clicking this button will also implement the percentage salary increase ‘flex’ functionality, where users can also specify the month in which the increase should be made.

Ad-hoc monthly bonus payments can be entered per employee, with separate accrual and payment months, thus enabling bonus forecasting which shows the P&L effect for the accrual month, as well as the cash flow effect for the month in which the bonuses are paid.

To save time, and if you have several employees at the same pay grade and bonus entitlement, each ‘employee’ row can be multiplied by a headcount, meaning users need only the employee type once and set the applicable headcount to that employee row.

The tool also enables the separate reporting of both ‘Direct Labour’ and ‘Admin’ employees. The Employer NI allowance is selectable to be used against either of direct labour or admin for each separate financial year.

By default pension contribution rates are set to the workplace pension minimum employee and employer rates. These may be easily overridden per employee with any rate from 0% upwards.

For benefits in kind, there is a section where users may manually enter summary figures for the full payroll. These will then feed into the overall monthly and annual reporting figures.

As well as giving monthly figures, the tool contains financial year summary totals so that users can easily track how costs evolve from year to year, with an unprotected area at the bottom of the worksheet where users may create their own bespoke calculations, should they wish to do so.

Perhaps most crucially for those users wishing to use the tool as a ‘stand-alone module’ when creating their own bespoke financial forecasts, there are summary tables at the bottom of the payroll entry sheet which show the required profit and loss and balance sheet effect, as well as the cash flow movements for the input assumptions. Users can simply link these summary tables to other financial models, thus saving considerable time and effort.

The tool is non-promotional and use of it is exclusive to AccountingWEB readers. As such it is completely free to use with nothing required in return - not even an email address or registration.

Simply download the tool and instructions from here (editor’s note: links to external site). MS Excel 2007 or a later version of MS Excel is required. The tool will only work on a Windows platform as macros are required to operate the tool.

The author is also open to suggestions around the design of further ‘freebies’ exclusive for AccountingWEB readers - simply post your ideas below or PM the author.

About James Power

Replies

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.