The Best Way to Use Excel for Workforce Planning

Spreadsheets. The Swiss Army knife of the software world that has gracefully aged with the rise of digital data management. Especially for smaller organizations, spreadsheet tools like Microsoft Excel provide a handy, straightforward platform for numerous tasks including workforce planning. When diving into Excel for planning purposes, a few quintessential techniques come to the fore:

• Leveraging pivot tables allows for summarizing massive data sets, enabling quick insights.
• Data validation and dropdown lists can help streamline input processes, reducing chances of manual errors.
• Conditional formatting visually highlights specific trends or outliers in your data, making discrepancies or peaks immediately recognizable.
• And, not to forget, Excel’s formula arsenal, from simple arithmetic to complex lookups, which forms the backbone of any planning exercise.

But as an organization grows in size and complexity, the question arises: is spreadsheet planning still the most efficient choice?

Let’s dive into the world of spreadsheet planning, understand its nuances, and explore why, at a certain point, it may not be up to the job.

Spreadsheet planning: The foundations

Monthly staff plan in Excel

Excel offers a myriad of functionalities for monthly planning. A simple monthly staff plan might involve:

1. Setting up a table: With weeks of the month as rows and days of the week as columns.
2. Inputting demand data: Using previous months’ data to forecast the upcoming month's demand.
3. Assigning staff: Based on the forecasted demand, allocate staff accordingly.

Example:

Imagine a small call center. January has historically been the busiest month with 60,000 calls and by trial-and-error, you’ve determined that 16.2 staff are required for that volume. Applying a simple formula, you calculate that in February, with 52,000 forecast calls, you will need 14.7 staff, and so forth. Input these values into your spreadsheet, and you'll have a rudimentary monthly staffing plan.

A straightforward way to calculate under/over staffing is to take the volume input, multiply it by the average handle time (AHT) input to get the total workload. Then input the shrinkage to gross up the staffing. This gives you the total required. In the row below this, put the planned staff. The difference between these two rows gives you the over/under for the week or month.

Here is what a monthly view would look like:

And a weekly view:

Day of the week staff planning in Excel

Expanding on the monthly plan, day-of-the-week planning provides a more granular approach:

1. Highlight peak days: From your monthly plan, identify which days consistently see the highest demand.
2. Allocate main and backup staff: Main staff handle the bulk of the work while backup staff manage overflow and breaks.

Example:

Using the same methodology as the monthly plan, populate the volume, AHT, and shrinkage for each day of the week. Then, you can look at your over/under as you compare that requirement to scheduled staff. The day-of-week volumes can tie directly into the monthly (or weekly if you have it) staffing plan to make sure the spreadsheets stay connected.

Consider allocating additional staff to days where there may be spikes in volume or drops in staffing due to absenteeism or other causes. For example, if you require 20 FTE on a Monday and want a 10% buffer, you would staff 22 to help absorb the unexpected.

Intraday staff planning in Excel

For detailed day-to-day operations, intraday planning is pivotal:

1. Break the day into intervals: Usually, hours or half-hours.
2. Monitor real-time demand: This can be call volumes, client interactions, or any metric relevant to your organization.
3. Allocate staff based on demand fluctuations: Increase staff during peak hours, and decrease during off-peak times.

This is what an interval plan may look like. Note we have the 10% contingent staff during the peak intervals to be able to absorb an added spike.

Example:

From 9-11 a.m., there's a surge in calls. For our hypothetical call center, 25 staff are allocated for these intervals. Post-lunch, from 2-4 p.m., only 15 staff are needed due to decreased call volume. Looking at required staffing and scheduled staffing at every interval allows you to identify where there is excess staffing and understaffing. You can use this information to adjust schedules or schedule offline activities to smooth out the expected service levels.

Free eBook

The Ultimate Guide to Forecasting with Excel

Are you looking to create and optimize your call center workload forecast in Excel?

The guide shows proven ways to collect and analyze historical data, predict future workload, apply business intelligence and report on accuracy.

Download eBook

The advantages and shortcomings of spreadsheet planning

Advantages:

1. Flexibility: Spreadsheets allow for customizable planning tailored to specific needs.
2. Accessibility: Easily shareable and editable across teams.
3. No additional costs: Especially for smaller organizations, spreadsheets avoid the need for extra investment.

Shortcomings:

1. Error-prone: A simple formula error can throw off entire projections and can be hard to detect.
2. Time-consuming: Manual data input and adjustments can be tedious.
3. Limited scalability: As the organization grows, maintaining a spreadsheet system becomes cumbersome.
4. Lack of automation: Real-time adjustments, which are crucial for large organizations, are hard to achieve with traditional spreadsheets.

>> Have you already outgrown Excel? Check out this post which reveals 8 signs that it’s time to wave goodbye to spreadsheets.

Moving beyond spreadsheets: The case for workforce management tools

For a startup or a small business, a well-maintained spreadsheet might suffice. But as the scale of operations grows, the limitations of spreadsheets become glaringly evident.

Advantages of Workforce Management Tools:

1. Automation: Automated demand forecasting and staff allocation save hours of manual effort and adjustments.
2. Integration: Seamless integration with other business tools for real-time data access.
3. Enhanced accuracy: Advanced algorithms reduce the chance of human error, ensuring more accurate projections and more efficient schedules.
4. Scalability: Designed to handle the complexities of large organizations, these tools grow with your business.

Conclusion

While spreadsheets like Excel are powerful tools with their formulae, charts, and pivot tables, they aren’t infallible. As the adage goes, “To err is human.” And with spreadsheets, the room for error can be substantial.

For smaller setups, spreadsheet planning might be a cost-effective choice. However, as your organization expands and the intricacies of demand, staffing, and real-time adjustments amplify, considering a dedicated workforce management tool becomes not just a luxury, but a necessity.

Remember, the right tool doesn't only save time but can significantly impact the bottom line by ensuring operational efficiency, customer satisfaction, agent morale, and reduced overhead costs. When the stakes are high, it's prudent to arm oneself with the best technology available.

👉 Whether you’ve decided to leave your spreadsheet behind or are looking to make the switch to a new solution that fits you better, make sure to download the injixo WFM Software Must-Haves Checklist.