Resource Management with Core Smartsheet

Hello,

I'm wondering if anyone has created an application to track resource capacity across projects by month? Our leadership will not purchase the Resource Management module, and while the Resource Management functionality in core Smartsheet works well if you have it turned on in relevant sheets, you can't do anything with it. You can't add it to a dashboard (that I could find!), you can't print it to a PDF to share with leadership, you can't publish it so I could just send a link. It's fine for portfolio managers to get a quick look but it doesn't help me share that information with leadership to show our capacity (or lack thereof).

I've tried using sheets that have months across the columns, with one row for each project each individual is working on, then adding capacity for each person for each month they are on the project and that works, but it is very labor intensive and I have to keep adding/removing columns as the year progresses.

Just curious if someone had a more elegant design that was easy to maintain as projects are added/completed.

Thanks much!

Find more posts tagged with

Resource Management

capacity planning

executive reporting

Accepted answers

All comments

Philip Robbins

Hi @Andrea Zenner,

Do you have access to Pivot App? If so, it's possible to build something like the below where different project sheets feed a report, which you then pivot to create capacity summaries.

If you don't have pivot it's a lot trickier for the reasons you've expressed - Smartsheet doesn't like doing dates horizontally.

Andrea Zenner

@Philip Robbins We do have Pivot! I will look at your solution. I'd love (and I think our leadership would too) a dashboard like that where the data is live and I don't have to use Power BI. THANK YOU!

Philip Robbins

@Andrea Zenner that's great to hear. You'll likely need to create multiple pivots to make it work, e.g. one for each resource type, if you're looking to get views like the one I've shown. It can take a few attempts to structure the data correctly, but it works well once it is set up. Drop me an @mention if you have any questions.

Andrea Zenner

@Philip Robbins I'll definitely need multiple sheets and pivots, we have a large team. One question on data structure, I will need a sheet with each project for every week of the year, correct? So if a project will last all year, I'll need 52 rows for that one project, correct? If a project is only a few weeks, then just a row for each week it is active. Do I have that correct? In my head that's a lot of admin but maybe once I have it set up it won't be bad. We have 32 projects in flight and another 30 in backlog that can start any time. 😐️

Philip Robbins

@Andrea Zenner set up my sheet template like this. The Week Starting column just requires the Start Week Date summary field to be set and then gives up to 52 weeks (but obviously could be set up with more if needed). Note I'm not looking at individual people here - just roles and FTEs. But there are ways of connecting a sheet like this to a project schedule and roles matrix if you want to get really clever with it.

Note that with the report that feeds the pivot, if you have Control Center, you can set this up as a program report and if this sheet is part of your blueprint it will be fed into it automatically. So this can become very hands-off setup-wise if you build it smartly.

Andrea Zenner

Tim Starkey

Looks like @Philip Robbins has a good solution for you with Pivot. But, thought I would show you are solution in case it gave any ideas. We do not have the resource management add on, and do not have pivot. I am sure it could be much more elegant, but it's a 'mostly' automated solution for doing workload projections.

We call them 'backlog projections', and do a 6 month look ahead. On our dashboard, we have charts and tables for each department and the overall company, both revenue ($) based and manpower based. Below is an example of one of the charts:

We use Control Center to provision each of our projects, and use an API that connects our financial data to each project. You may have another process, but the point is we have a consistent template for each project that tracks both budgets and scheduled tasks/manpower by staff assigned. Our project sheets ("Work Plan") has two sections… the project schedule, and the budgets.

For the budget projections, our managers regularly update the start/end and % complete of a budgetary task. (Its rolled up into a report so they do not need to open each project sheet individually.)

We use sheet summary fields to calculate in each sheet the 1st and last day of the current and next 5 months. The template then has 6 date columns (M1, M2, …) and uses the dates from each budget line, and budget remaining (using both the PM's % complete and the ACTUAL budget remaining), and projects out budget spend over the next 6 months. Additionally we compare the PM's %complete against actual budget to project how much the task will be under/over budget.

Formula from M1 for example:

This is the manual part… and I figure someone smarter than me could automate it. Buts it's a small effort, since we only do updated projections monthly. We have a backlog report that pulls all these projections, then add summaries.

I copy and paste this into a helper sheet that powers the dashboard (and translates the M1, M2… into August, September…) Takes about a minute… mainly waiting for the sheets to load.

The 'manpower' projection is similar but uses hours assigned per task to the person in the 'staff assigned' column.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.