Using Excel to manage employee time
Companies track employee time to feed payroll and billingsystems. The data can also be used to track margins and profitability. Largecorporations typically use enterprise solutions from companies like SAP andOracle. For smaller, growing businesses,Excel can play a useful role bridging the gap between industry-specific apps, accountingand payroll solutions.
Small, labour intensive businesses often price jobs on acost-plus basis where the selected margin covers overheads and profits. They createinvoices for clients based on a combination of deliverables, consumables andemployee time. They pay their staff salaries through a separate payrollprocess. In a company with three or four fee earners, the business owner knowshow well they are doing and the administration involved in invoicing isn’t thathigh.
As the company grows, several things happen. Revenue becomessmoother, the founder spends less time with each individual member of staff,fee earners may stop doubling up in sales roles, financial admin increases. Toensure the growth is profitable, the company needs to formally identify when employeesare delivering a service and how much they’re earning. Excel spreadsheets can bringdata together from different attendance apps, CRM packages and accountancysoftware to inform future pricing decisions.
If the company has decided on a method for pricing clientwork, Excel can be used to build project proposals based on estimates of thetime required. The project pipeline can be used to identify any headcountbottlenecks and, once the work has been completed, the actual time taken can betied back to the budget, allowing the pricing strategy to evolve.
Our Excel experts have also built scheduling systems foremployees working in the field. If there are complex rules around employee placementsthen a bespoke Excel spreadsheet can reduce the time required to allocate humanresources and reduce the scope for error. We are not generally asked to trackabsences in Excel but they are occasionally a necessary pre-requisite toemployee scheduling.
What follows are some examples of how we’ve used Excel in time management. A summary of those examples is listed in the table below:
Can Excel be used to calculate payments for off-payroll contractors?
Excel is a very good place to manage complex pay arrangements. A lot of companies provide a venue and referrals to self-employed fee earners; be they doctors or delivery drivers. These contractors often rely on the company’s timekeeping systems to calculate their earnings, but the actual payments made will be subject to deductions. An Excel expert can build a bridging spreadsheet to notify contractors how much to invoice and the accounts team can approve and pay contractors on that basis.
Excel4Business has worked with a private hospital providing theatre space to surgeons. Appointments were being scheduled in the appropriate industry software. Our Excel consultants could automate the creation of each surgeon’s monthly record which could be combined with their billing rates to calculate an amount due. Adjustments could be made to the total based on consumables used or other individual circumstances. The surgeons could be automatically notified of these intended payments and instructed to invoice.
Our gurus have also worked with a delivery firm providing vans and routes to drivers. Delivery records were being made in the appropriate software, and the drivers pay would depend on the route worked and whether targets were hit. Adjustments could be made based on fuel use, insurance payments and any vehicle damage. The drivers would be told how much to invoice and, in this case, the performance data could be used to determine profitable routes.
Both these examples are very similar because the contractors aren’t earning a flat fee, they aren’t working fixed hours and their payments are subject to a combination of everyday and exceptional adjustments. Excel’s flexibility becomes an asset especially for growing businesses who may wish to change those methods as their business environment changes.
Some of the same arguments can apply to on-payroll sales staff. Excel can play a role when sales commission agreements become particularly complex such as if commissions are shared between employees, payments are staggered based on client retention, and rates due depend on a variety of different targets. In those cases, a well-designed Excel spreadsheet can provide employees with transparency over moneys received.
Can Excel be used to calculate the margin earned on employee time?
As a calculator that can tabulate data, Excel is a good place for SMEs to compute and monitor company margins. It’s particularly helpful when the main user of the spreadsheet will be the person making decisions based on the calculated figures. That’s because the spreadsheet can be designed around the metrics that interest the user. For large corporations, it makes more sense to combine data sources in the cloud and track the outputs in Power BI.
An organization wide margin on inputs can be calculated simply by dividing the cost of sales into the total revenue. That information is available in standard accounting packages. The data is not always so accessible at a project or individual employee level.
Our Excel consultants have worked with a commercial landscaping firm to provide granularity to their margin calculations. As employees work on-site, they clock in and out on their smartphones. The smartphone app only lets users sign in when in the correct location and the collected data can be exported in an Excel friendly text format. Macros can combine this raw data with employee identities to calculate the cost of the time worked. The app can report on travel time, allowing the business to build a picture of each employee’s overall productivity. The raw data can also tie worked time to individual projects, allowing the business to calculate the profitability of its fixed price work.
Typically, Excel4Business is approached by growing companies taking their first steps into the world of business intelligence and consulting. A business starts to buy in tools that harvest large amounts of data as a by-product of their use. In the example above, the geo-clocking app was providing value simply because it showed whether employees were on-site when they were supposed to be. As the tools weren’t purchased with an eye on using the data more widely, the data often needs significant manipulation before it can be used in financial decisions. A well-designed Excel spreadsheet can automate the data processing required to calculate profitability from the underlying data.
Can Excel be used to create client proposals that protect business margins?
You can build Excel quote templates to build proposals line-by-line. When clients purchase a physical product, they will be happy with a proposal that contains the requested items. When clients are hiring a professional services firm to work on a specific project, they want to know how the product is being delivered; who they’re buying, for how long, and at what rate. The professional services firm needs to compile a separate internal budget to ensure margins are protected.
This means preparing two versions of a proposal. The first is the internal budget from which the desired project price can be calculated. This may include travel costs, disbursements and administrative time. The project proposal itself would be restricted to items which the client is prepared to pay for, at a rate they’re prepared to pay. A well-designed Excel template can minimise any duplication of effort in this process whilst allowing for any unique circumstances in the process.
Excel4Business had to build a very bespoke tool for a firm in marketing and communications. The customer had service-level agreements (SLAs) in place with clients stating the agreed rates for each role on a project. Project components were being legitimately outsourced for fixed fees, but the SLAs required time allocations to be made. A further complication was that senior staff could be substituted into more junior roles which would then be billed at a lower rate.
The result was that the internal budget needed tweaking before it could be written up as a full proposal. As well as ensuring proposals conformed to each individual client’s SLA and role definitions, our Excel consultants used code to avoid any duplication of effort in preparing the client-facing budget. Throughout the entire process, estimators would be able to see project margins and management would be able to approve proposals in the knowledge the business’ margins were protected.
Can Excel provide clients with a breakdown of hours worked?
Your clients may request for supplementary information on hours worked to be included with invoices. A one-off table of dates, rates and descriptions can be manually prepared in Excel. If this is a regular requirement, then the optimal solution would be an integrated timesheet and billing process, from which the data would be available as a downloadable report. If the wider business context means that isn’t practical, then Excel can be plugged into whatever data sources are available to automate the process of providing clients with a breakdown of hours worked.
Our Excel consultants come across this most frequently when a small business wins an unusually large, long-term contract with a strategically important client. Large corporations may request invoices get submitted through their own portals. These portals require different people to sign-off on invoices and final approval often comes from someone a few steps removed from the work being performed. Final approval can become contingent on supplying a breakdown of work performed.
Assuming the hours worked are recorded in some sort of database or spreadsheet, the exported data can be queried for relevant work in each billing period. In Excel, our experts would tie the hours worked to applicable rates based on the tasks performed or a table of employee rates. Manual adjustments may be required at this stage. If the customer has dictated the output format then macros can be used to arrange the data as appropriate.
As well as hours worked by your employees, customers may request a breakdown of hours for which they received a service. An example of how we helped a tutoring company can be found here.
Can Excel be used to create employee schedules?
Excel lets you create grids containing any data you like so, at a superficial level, yes it can. However, creating staff rotas is a very common business problem and off-the-shelf apps contain a number of handy features such as helping employees swap shifts. Then, for office-based staff, Outlook is a more natural scheduling tool than Excel because of it’s native diary functionality. If you have a more unique scheduling problem then Excel may be the only option.
We would recommend using Excel only when the task of scheduling staff is a logic puzzle where the design of the spreadsheet itself can help the user find a solution. Other considerations would be; how interchangeable are different members of staff? How many members of staff are you trying to place? Do you need to consider the personal circumstances of staff at all? Will putting the data in Excel help with other processes?
At Excel4Business, we were approached by a business who has a contract to supply wardens to various buildings over a large area. There are different numbers of wardens, shift patterns and roles required at different sites. The wardens are hired as freelancers who complete a monthly questionnaire stating where they’re happy to work, and when. The business was collecting information from the wardens through Microsoft Forms which meant their responses were available in an Excel-friendly format.
The solution our experts built let a scheduler filter down the responses to show who was available on a site-by-site basis for each day of the month, as well as the roles each freelancer was qualified to fill. Simple colour coding would reveal any sites that needed more staff, and some Visual Basic was used to minimise the time spent navigating between a summary and the individual sites. Notifications of shifts could be sent out automatically by e-mail, as could notifications of any changes once the month had begun.
The benefit of using Excel as scheduling software is that you can bolt on other functionality and automate other business processes. In this case the software could also be used to calculate payments due to the freelancers hired, building in any required adjustments for shifts performed at short notice.
Can Excel be used to analyse future headcount and human resourcing requirements?
In industries with long sales cycles, it’s not always possible to match headcount to client demand. Excel can bring together employee data and the sales pipeline to model different scenarios and help business owners make informed decisions on hiring. A simple model would forecast based on the probability of leads converting and the projected duration of the project. A stochastic model would sample scenarios in which different combinations of projects convert and might allow for start dates to vary. Our consultants can advise on whether your business’ circumstances require the more sophisticated approach.
Excel4Business has built a headcount calculator for an oil and gas company. They would tender to participate in large drilling projects. This meant they had a relatively small sales pipeline of projects with start dates that were subject to change. These were maintained in Excel as part of a wider revenue forecast sheet. They would map out separately how many people would be required, and when, in each project.
In this case, we built a simple headcount model. The gap between essentially securing a contract and employees being deployed was sufficiently long that the business could react to sales surprises. They also had a flexible workforce as they could call on additional contractors when required.
In this example the client was already storing the sales pipeline in Excel. If you use a CRM like Salesforce then those systems can handle basic headcount modelling and planning which will be sufficient most the time. However, if you need something more bespoke, then our experienced consultants will ensure you get an appropriate model and that you understand how to interpret its outputs. If your pipeline is available for download then our team focus on the model building and not how users input data.