How To Create A Flexible Hiring Plan In Excel? [Free Template]
For most startups, salaries are the biggest expense category. That’s why you need to accurately forecast your payroll with a flexible hiring plan in Excel, whilst having maximum flexibility when scaling up.
Because salaries often are the bulk of the total expenses, they need a higher level of attention. In this article we lay out a step-by-step guide on how to build your comprehensive hiring plan in excel. We also provide you a free example at the end of this article.
Whether you need a flexible recruitment plan model to integrate in your existing excel budget, or a comprehensive standalone plan you can update as you go for your HR team budget planning, read on.
Download the free Hiring Plan template
Step 1: List all the existing roles in your hiring plan
In a new sheet under Excel, start listing all your existing roles, with for each of them their respective:
- Starting date
- Annual gross salary
- Taxes and benefits (as % of gross salary): this usually depends per jurisdiction, hence the need to do it per role instead. Also, if you have freelancers/contractors, they will likely not have any (0%)
- End date (Optional): if you have contractors, enter their end date of mission (for instance, Jun-21 to Dec-21 for 6 months contractor mission)
- FTE equivalent (Optional): if you have part-time employees or contractors
Step 2: Forecast future roles
If you are running a startup, or a high-growth business, it is likely that you will not be in a position to accurately forecast all your expected hires for the next 5 years. More importantly, listing dozens, if not hundreds of expected roles will likely be daunting task, and not flexible for further changes.
Instead, in order to forecast future roles, choose a growth forecast model instead, meaning for each new role choose:
- The frequency of the new hires: will you hire, for instance, 1 developer every 3 or 6 months?
- The number of hires: will you hire 1 or 4 developer(s) every 6 months?
That way, future roles will be calculated automatically, and the growth (frequency and number of hires) will be separate for all new roles (you might expect to hire 10x as many Account Managers vs. Operations Managers)
Step 3: Factor in salary indexation and bonuses
Before diving in the actual salary and bonuses cost, let’s calculate any potential salary indexation and bonuses. An easy to do it is to, for each role:
- Indexation: set an annual indexation percentage (say, 2% increase every 12 months)
- Bonuses: set a percentage of gross salary (say, 25% of $50,000 annual gross salary) and a payment schedule (monthly, quarterly or annual)
Once done, we can now calculate, for each role and each future month, the percentage factoring in indexation and bonuses. We will apply this percentage to gross salary later on to calculate payroll. This percentage is always higher than 100%, for instance:
- Indexation: for a 5% increase in 12 months, the percentage is 100% before 12 months, 105% after 12 months, 110.25% after 24 months, etc.
- Bonuses: for a 50% bonus paid quarterly, the percentage is 112.5% every 4 months (and 0% in between), which you can then multiply by monthly gross salary as shown below
Step 4: Calculate payroll expenses of your hiring plan
For each role, you can now calculate salaries and bonuses (using, for both, salary indexation percentages). We recommend to differentiate salaries vs. bonuses, especially as the cash flow impact of the latter might be significant (especially if you pay quarterly or annual salaries) and you might want to identify the cost separately in our profit-and-loss and cash flow statement for maximum transparence.
Note: We also recommend grouping salaries and bonuses per team in your P&L.
Our standalone hiring plan and can be integrated into any Excel financial model. Save time, use our template and build error-free and flexible financial projections.