{"id":6200,"date":"2023-02-21T12:00:15","date_gmt":"2023-02-21T11:00:15","guid":{"rendered":"http:\/\/www.peopleplan.eu\/?p=6200"},"modified":"2023-03-07T16:00:20","modified_gmt":"2023-03-07T15:00:20","slug":"free-template-how-does-workforce-planning-work-easily-in-excel","status":"publish","type":"post","link":"https:\/\/www.peopleplan.eu\/en\/free-template-how-does-workforce-planning-work-easily-in-excel\/","title":{"rendered":"Free Template: How Does Workforce Planning Work in Excel?"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t

What is workforce planning needed for?<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

\"\"Workforce planning is usually part of the financial planning of a company. Personnel costs are often the largest item and should therefore be planned with as much accuracy as possible. The planned personnel costs must be coordinated in the planning process with the available budgets, planned key production figures and\/or sales.<\/p>

The planning process is often iterative, i.e., on the one hand, planning is done bottom-up based on people and positions, and on the other hand, a budget is allocated top-down. Therefore, starting from an initial planning of personnel costs, adjustments are necessary (e.g., reducing planned bonus payments). In doing so, the legal possibilities must be taken into account – if possible, the planned personnel costs should not fall below the costs that are contractually bound, e.g., by labor and collective bargaining agreements.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

\n\t\t\t\t
\n\t\t\t

What costs are planned as part of workforce planning?<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t

Within the framework of workforce planning, the various components of personnel costs can be planned. All employee groups, e.g. permanent employees, trainees, temporary employees, interns, etc., must be taken into account. The cost elements include:<\/p>

  • Basic remuneration<\/li>
  • Allowances and special payments<\/li>
  • Employer contributions to social insurance (health insurance, nursing care insurance, unemployment insurance, pension insurance and contributions to the employers’ liability insurance association)<\/li>
  • Voluntary social benefits (e.g. company pension scheme)<\/li>
  • Other costs such as further training costs, travel expenses, allowances for meals, etc., as well as<\/li>
  • Imputed costs.<\/li><\/ul>

    The latter include, for example, unpaid entrepreneurial wages. In addition, further discounts or surcharges can be taken into account on the basis of the above-mentioned costs. For example, it is conceivable to make a lump-sum deduction from the total calculated personnel costs as a result of long-term illnesses and short-term absences such as parental leave, for which no substitute is usually hired up to a certain duration.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

    \n\t\t\t\t
    \n\t\t\t

    What data is included in workforce planning?<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
    \n\t\t\t\t
    \n\t\t\t\t\t\t\t

    The planning of personnel costs requires a planning of the headcount. The planned headcount can be derived from the company’s staffing plan, for example. Before starting the actual workforce planning, the following questions should therefore be clarified:<\/p>

    • Will planned-positions be added?<\/li>
    • Can all positions that become vacant be reoccupied or will positions have to be reduced?<\/li>
    • Will positions be moved within the company?<\/li><\/ul>

      The planned costs are then usually made up of the planned costs of the persons in the positions and a calculation of the new persons to be hired in positions that can be filled. For the persons already hired, actual costs from previous years can be extrapolated for the planned year, or planned personnel costs can be calculated on the basis of existing data on classification and working hours (and any future changes to these that have already been contractually agreed) as well as the legal framework conditions (e.g., allowances for overtime, shift work or weekend work, mandatory step increases, capital-forming benefits, vacation pay, special annual payments and other miscellaneous payments). Various extrapolation methods are also conceivable for the staffable positions. On the one hand, average costs of comparable employees in previous years can be used for the calculation. On the other hand, it is conceivable to calculate the expected costs from the job profile or the job evaluation.<\/p>

      The concrete calculation of the planned values is based on further general planning assumptions, e.g:<\/p>

      • Assumed increase in pay scales,<\/li>
      • Assumptions regarding developments in social security (e.g., contribution assessment thresholds),<\/li>
      • Empty months in the case of backfilling of positions, and<\/li>
      • Average sick days or other unpaid absences.<\/li><\/ul>

        If the company has a workforce management system, data from this system can be taken into account in personnel cost planning. Particularly in companies with extensive shift systems, the associated costs must be taken into account.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

        \n\t\t\t\t
        \n\t\t\t

        How can Excel support workforce planning?<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
        \n\t\t\t\t
        \n\t\t\t\t\t\t\t

        Excel offers extensive possibilities for workforce planning. Depending on the basic remuneration of the persons, for example, the employer’s contributions to social security, imputed costs as well as lump-sum supplements and special payments can be calculated. Depending on the interest of the companies and the available data, the calculations can be evaluated \/ built up with regard to different factors, for example<\/p>

        • based on calendar months<\/li>
        • on a cost center or cost unit basis<\/li>
        • with regard to different employee groups.<\/li><\/ul>

          In addition, different scenarios can be stored, e.g. for assumed tariff increases. By using macros, extensive individual requirements can be taken into account and, for example, the final presentation of the results can be prepared automatically. Furthermore, personnel cost planning in Excel allows the integration of several plausibility checks within the calculations. All results can be visualized with the familiar Excel charts. A sample file for personnel cost planning in Excel can be downloaded “here”.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t

          \n\t\t\t\t
          \n\t\t\t

          When does an integrated Workforce Planning Solution make sense?\u200b<\/h3>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
          \n\t\t\t\t
          \n\t\t\t\t\t\t\t

          Even though Excel offers a variety of possibilities, especially through the use of macros, alternatives should be considered from a certain complexity (more than 200 employees, a variety of collective bargaining regulations, etc.). Especially if the company already uses ERP software – such as SAP HCM – in the HR area.<\/p>\n

          These ERP solutions already contain a large amount of the master data required for workforce planning in detail, especially if the company runs its own payroll accounting. workforce planning docked to the ERP solution can then build on this data. This means, for example, that future step changes as well as individual employer contributions to social insurance (e.g., with regard to statutory health insurance) can be taken into account directly in the planning.<\/p>\n

          Another advantage of workforce planning integrated in ERP is the improved possibilities of plan-actual analysis. For each month, the planned and actual costs can be compared in relation to the person, individual G\/L accounts or specific cost centers, and thus causes for variances can be quickly determined. The results of these analyses can be used to make adjustments to the plan or to improve future plans and their premises.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t

          \n\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t
          \n\t\t\t\t\t
          \n\t\t\t
          <\/div>\n\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t\t
          \n\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t
          \n\t\t\t\t\t\t\t

          Download free Workforce Planning Excel template<\/span><\/strong><\/h2>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t
          \n\t\t\t\t
          \n\t\t\t