Why this tool: projects on a shared-services model are hard to track
In a shared-services model the same people work on several projects and clients at once, are paid in different currencies and are billed at different rates. Their cost sits in one pool, so it is hard to see what each project really costs, whether it makes money, and where paid time is lost.
This dashboard splits each person's monthly cost across their projects by effort, sets it against what each client is billed, and shows the margin for each project, location and the whole company, every month and year to date.
Monthly actuals
Grey numbers come from the plan. Type a value only when this month was different: a salary change, a leave month, hours the client did not accept. Highlighted cells are overrides.
Resources
Project teams and client rates
Pick a project to see who works on it. Add people from the list (it asks for their effort and client rate), or take them off with the trash icon. Effort is a % of the month or hrs as needed (enter actual hours each month under Monthly actuals). A rate per month is the price of a full-time person.
Projects
Enter each project's monthly invoice in the currency you invoice in. It is converted to the reporting currency, totalled, and used as the project's revenue; margin = invoice − cost of the people on it. Billable hours × rate (the timesheet value) decides each person's share of the invoice, and the dashboard shows whether the timesheets add up to the invoice. Non-billable effort shows as cost leakage. Leave the invoice blank for projects billed by hourly rates.
Year and reporting
Currencies and exchange rates
New currencies get today's market rate automatically (from open.er-api.com; only the currency code is sent). Rates you type are kept until you press ↻ or Update all, so you can match the fixed rates your finance team uses.
Locations
The default cost currency is used for new people in that location. Each person can still be set to a different currency.
Data
Import from Excel or CSV
The Excel template has three sheets: Resources, Projects and Allocations, filled with your current data so you can edit and import it back. With CSV, use one file per sheet (you can pick several at once). Rows are matched by name: existing people and projects are updated, new ones are added. Blank cells leave the current value unchanged.
Backup
Everything is saved in this browser only. Nothing is sent to a server. Download a backup to move it to another computer or share it.
How the numbers are worked out
- Cost
- Monthly internal cost in local currency ÷ exchange rate. A resource only costs money in months between their start and end month.
- Effort
- Either a % of the month (50% = half of the billable hours per month) or hours as needed: an estimate in the plan, replaced by the actual hours you enter each month.
- Billable %
- How much of the effort the client pays for. A person can work 100% on a project and be 50% billable.
- Org internal cost per hour
- Monthly internal cost ÷ billable hours per month.
- Client cost per hour
- Set per person on the Resources table. A project can override it with its own rate in the Allocation table.
- Fixed-price projects
- One price for a project with its own start and end date. The price is spread over the working days (Mon–Fri) between those dates, so a month gets its share by the project days that fall in it, and the team's effort on the project only counts inside the dates. Monthly and hourly projects keep the 22-day / monthly-hours basis.
- Monthly invoice projects
- Revenue is the monthly invoice (in its own currency, converted to the reporting currency) for months between Invoice from and Invoice to. Each person's share of the invoice follows their timesheet value that month: billed hours (effort × billable %, or the hours entered under Monthly actuals) × their rate. The dashboard compares the total timesheet value with the invoice. Non-billable effort is still a cost and counts as leakage.
- Revenue
- For hourly projects: billed hours × client cost per hour. Billed hours default to effort hours × billable %; type the real number under Monthly actuals if it differs. A monthly rate is turned into an hourly rate by dividing by billable hours per month.
- Project cost and margin
- Project cost = resource cost per hour × effort hours. Margin = revenue − cost. Margin % = margin ÷ revenue.
- Part months
- Start, end and invoice dates are exact days. In a month someone joins or leaves, their cost, capacity and planned effort follow the working days (Mon–Fri) they are on the team; an invoice that starts or ends mid-month is reduced the same way. Hours typed under Monthly actuals are used as they are.
- Idle capacity (bench)
- Hours of a person's month not spent on any project. Paid for, earns nothing. For as-needed people this is whatever their actual hours leave over.
- Non-billable effort
- The cost of the planned non-billable share, e.g. the other half of a 50% billable person.
- Unbilled work
- Billable hours that were worked but not billed (fewer billed hours entered than effort × billable %). Its cost is leakage; the lost revenue is shown too.
- Below-cost rate
- The client rate per hour is lower than the person's cost per hour, so every billed hour loses money. The leakage is the difference × billed hours.
- Company margin
- All revenue − all cost, including idle capacity. This is lower than the sum of project margins when people sit on the bench.