Headcount Forecasts in Excel For a software company, personnel-related expenses are usually the largest operating expense by far on the P&L (Profit and Loss Statement). As a large expense, you’ll want to forecast this as accurately as possible. I’ll walk you through how I create detailed headcount forecasts. You can download the Excel model I used in this example at the bottom of this post. When forecasting operating expenses, I follow the 80/20 rule on where I spend my forecast energy. If it is a large expense item, I’ll spend 80% of my time on those expenses or expense categories. When setting up my model, I’ll take the time to build forecast automation and detailed calculations into my spreadsheet model.
Headcount Forecasting and Planning I forecast headcount by position and name. We are still of the size where this makes sense and it is not tedious to maintain. However, if you have large labor pools, say Pilots or Mechanics in my former life or several hundred sales representatives, you’ll obviously not want to forecast at the named person level. You would be in tears trying to track this. Rather, you will forecast this labor in aggregate using average wage rates, headcount levels, productivity, and so on. And you’ll want to include an open headcount assumption so that you don’t overstate your forecasted labor expenses.
Headcount Forecast Model Setup I use one tab in my spreadsheet where I forecast all named positions by department. This tab is my labor “HQ” and all wage and medical changes in this tab flow out through the entire forecast model.
I group each department together on rows for ease of use. On the left side of the tab, I have my inputs. On the right side, my outputs (wages & taxes, benefits, FTE’s physicals). Headcount Model Inputs My columns on the left include Department, Roster (Name), Title, Full/Part-time, Wage Rate, Hours, Tax Rate, Benefits Rate, Start Date, and End Date. I’ll explain these in detail below.
Department – the employee’s department Roster – obviously, the name of your staff Title – again, just their title FT or PT – this field is in calculating the correctly monthly wage expense Hours – number of hours worked per month for part-time employees Payroll Taxes – current employer’s wage tax rate Medical Rate – I forecast medical expenses by head. Often, you see medical expense as a percent of wages which is not really accurate.
It usually doesn’t matter if that staff is earning 200K or 20K, your medical premiums are set under a fully-insured plan. If self-insured, I take total medical expenses and the number of participants to determine an average rate per head. Start Date – start date of the position. End Date – end date of the position Required Fields The only required fields are Wage Rate, PT/FT, Hours, Taxes, Medical Rate, and Start and End Date. I’ve built date logic into my model so that if an employee starts on the 17th of the month, for example, the model calculates the wages for the remaining days in the month. Same goes for the end date. Also, aggregate merit and wage inflation increases can be easily forecasted on its own row.
Headcount Model Output With my inputs complete, my headcount model will forecast my monthly wages, physical headcount, FTE’s (full-time equivalent), and medical expenses. FTE’s are determined by how many days in the month the employee worked. At the very bottom of the model, I have a total row and use the SUMIF Excel function to sum these outputs by department. Excel Formulas Used SUMIF, IF, AND, EOMONTH Conclusion Headcount expenses often represent the largest expense on a software company’s P&L.
It’s important to spend the time to develop a detailed headcount forecast model so that not only your wage forecast is accurate but also your cash flow forecast. In any software company, there are always new hires, terminations, and transfers so make your forecast life easier with an automated Excel model. I can literally update wage expenses in ten minutes or less and be accurate to the day (assuming our hiring managers are not too optimistic!).
Please let me know if you have any questions or comments. I would love to hear feedback? How do you forecast headcount or personnel-related expenses? Comment below.
UPDATE (Dec-16) – I added flexibility to the model so that you can forecast the same position multiple times on the same row. Instead of a row for each hire, you can enter the number of positions for that role. Much easier to scale. This headcount forecast template is incorporated into my.
Download below. Please enter your email address (no spam) below to download the model. I’ll keep you updated on future models and posts. 'Most comprehensive and accessible SaaS metrics explanation I've found.' 'Great materials, excellent models' 'Great content, which is highly applicable!' 'Informative - adds value to my life THANK YOU' 'Great resources that save a lot of time and brain damage to replicate.' 'Very informative' 'Excellent look at SaaS planning spreadsheets' 'A usable sales conversion model was a big bonus.
It saves a lot of us from reinventing the wheel.' 'Well written, interesting topics. My CFO will love this:)' 'Clearly itemized & explained' 'So specific, clear delivery and incredible time saver' 'Straightforward and informed language on metrics essential to tracking success in a SaaS business.' 'Practical and detailed info on calculating SaaS metrics' 'Good content, real life applicability' When the NPS Survey appears, please take a moment to rate TheSaaSCFO.com and provide a comment.
Our Workforce Planning template will allow you to have all of yoru workforce metrics in one area. You can track your workforce planning inititatives by geographic distribution, business units, and job levels.
Headcount Planning Template Excel
It is very easy to update, maintain and share. The data center tab houses all of the data that all of the metrics are dependent on and all you have to do is enter the data, the charts are dependent on the data you enter on the data center tab as well as the geographic distribution and job level.
With our Excel Templates for HR, the templates do all of the math for you, easy, no formulas or charts to figure out.
Filed under -, Now free to download is our new Excel forecasting template, which will assist your call centre’s capacity planning, demand and call volume forecasting processes. This tool needs you to have 24 previous months of data available.
You add these to the spreadsheet and it will calculate the next 12 months’ predicted values for you. You can then used these reports for capacity planning purposes. The forecasting template has been designed to work with call volumes, but could easily be adapted for other types of monthly forecast. Our forecaster works using the Holt-Winters, or triple exponential smoothing, method, which assumes that there are three components to any forecast:. A seasonal adjustment – based on previous data for the same months, e.g.
You might expect customer calls to go down in December or rise in July. A trend – whether your values are generally growing or shrinking over time. A level – a baseline for your values once the other components have been removed. To get the calculator to work you must enable macros in Excel and have both Analysis Toolpak and Solver installed in Excel as Add-ins.
If you prefer to print out the instructions you can also which explains a little about how the calculations are performed and has the instructions for installing the Add-ins and using the tool. We have also created a how-to guide on YouTube.
You are free to use the forecaster in all of your projects, provided that you do not resell or distribute the tool on the web. If you spot any bugs in the spreadsheet or if you have any feedback please send us an email or leave details in the comments below. Using the Forecaster Open the spreadsheet and enable macros (or Enable Content) if you are prompted to do so.
You will need to have at least 24 months of data in order to produce the next 12 predicted months. You can probably get this data from the call volume reports on the Call centre system. When you open up the tool you will see that it has some dummy data in it, and you should be able to see a chart, with the data that feeds it to the left-hand side, starting with the oldest month in cell A3 and the oldest value in cell B3. You can add as many values as you wish in columns A and B. Please don’t leave any gaps in the data.
You can replace the values by simply overwriting them or copying and pasting data from another spreadsheet, as long as it is the same format of date in the first column (dd/mm/yyyy) and value in the second column. It’s best to Copy and Paste Special (Paste as Values) to be absolutely sure that you don’t inadvertently copy over any formulas from elsewhere.
If you select the current data and press the delete key, you may get the following message This is fine, click OK and the chart will refresh and show just one point in the centre. When you enter your new data, the chart will redraw with the data you’ve entered and everything should work. Click the blue “Get Forecast” button and Excel will start to use Solver to find the most likely next points for your data.
You will need to be patient at this point. It can take around 20-30s for Solver to settle on a most probable solution and you will see numbers changing at the bottom left of your screen as different values are tried out. After this time, you will be taken to the Forecast and you will see a new chart with the next 12 predicted values showing in red.
The next 12 predicted months and forecast values are shown to the left of the chart. Your original data also exists in this sheet but the rows have been hidden, leaving only your forecast visible. If you’d like to forecast even further in to the future then you could take these values and transfer them back in to the data worksheet at the end of your current data, then run the forecast again to get another 12 months. In this way you could try to forecast a few years. The forecasting spreadsheet just works out the monthly predicted call volumes.
If you want to use it for scheduling you will need to break down the outputs into a daily and then hourly forecast. You can then plug the numbers into an to get the numbers of Agents or FTE required.
Terms and Conditions Use of the Forecasting Spreadsheet is subject to our standard. This tool uses a feature in Excel called Solver, which will run through many options for the future values until an optimal solution is found that fits well for a number of the original values. Sometimes if the original data is not regular or well defined then it can take longer to reach a solution as there can still be large differences in the new values that Solver has found – and there are constraints on how many gos it has so you’re not sat waiting forever! When you get that message you can either choose to continue and solver will carry on trying to find a good best fit, or you can show the trial solution and see if you’re fairly happy with what it has come up with. You could then run it again and see how different the forecast is, which will give you an idea of how much it’s struggling to make sense of your original numbers.
This business plan headcount and staff costs calculator will help you to calculate staff related costs for use in the and your business plan. Headcount and staff costs form part of operating expenses, and can be a major expense to consider when preparing a financial projection. Staff costs relate to the costs of having employees, they include the basic payroll expenses such as wages and salaries, but also benefits, perks and employer taxes.
Headcount Tracking Template
The Excel headcount and staff cost calculator, available for download below, allows for up to four departments, a markup percentage, and annual inflation. Headcount and Staff Costs Calculator Preview The calculator is used by entering details of each employee such as job title, basic annual salary cost, whether part time or full time, and start month. The start month can range from 1 to 60 months and the calculator will calculate the relevant cost for a particular year. Additional on-costs such as benefits, perks, taxes are allowed for by applying a mark up percentage to the basic salary costs. The second page of the calculator provides a summary of total staff costs and headcount by department. Headcount and Staff Costs Calculator Download The staffing projection template is available for download in Excel format by following the link below. Notes and major health warnings Users use this headcount and staff costs calculator template at their own risk.
Workforce Planning Spreadsheet Template
We make no warranty or representation as to its accuracy and we are covered by the terms of our legal disclaimer, which you are deemed to have read. This is an example of a staffing calculator template that you might use. It is purely illustrative. This is not intended to reflect general standards or targets for any particular company or sector. If you do spot a mistake in the staff costs calculator, please let us know and we will try to fix it.
Workforce planning – what's in it for me? Your business plan should give you a clear idea of what you want to achieve. Common reasons for workforce planning include:.
struggling with the workload generated by your business. regulatory changes, market changes or new technology that could force you to look at how you're running your business. poor staff performance. achieving business growth.
Look at your current staffing situation Good HR records should help you gather this information – but if you don't have this information at hand – you can conduct staff surveys or have conversations with your staff. Questions you should ask to determine your current staffing situation:. What are your employees characteristics – for example, age, salary, who they report to.
What skills do you have in your business?. What's the rate of workforce growth or decline – over time?. What's your staff turnover rate?. How would you rate your workers' satisfaction in their roles? Hiring new staff Think about the design of your organisation structure – inefficiencies can be commonly tracked back to poor organisational design. Effective organisational design creates an environment where people can work and communicate efficiently. Before you hire new staff, consider the following: Critical skills. If you couldn't find someone to fill the job, would it hurt your business significantly?.
Does one of your employees already possess the skills – but isn't using them in their role? Consider your options Consider up-skilling existing workers and taking on new entrants to the workforce and training them. When considering your options for employment, be sure to look at:.
– offering incentives for employing people aged 50 or older. – providing targeted support services for people looking for work and for employers looking for workers Consider costs This doesn't just mean wages – you'll also need to think about:. turnover. loss of training. inexperience. long-term vacancies if there's a high turnover of staff due to badly thought-out jobs.
Resources. Will this change in three to five years – and if so – how are you planning for it?. Will the role be full-time, part-time or casual?. Having flexible working arrangements, such as part-time hours, working from home or job-sharing options may open up your job to more applicants. Have you considered a broader range of potential employees such as older/younger workers, or working parents?
Determine your future staffing needs Having identified your business needs and profiled your current workforce, you can now get your workforce to meet your business goals. Create a HR manual Tailor a policy and procedure document that will ensure your staff will fit your workforce plan.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |