Forecasting Scopes of Work

Nathan_Coutts

Hello all!

I am looking to do some forecasting!

I have Data from a third party application that I bring into smartsheet as its own data. It has a Start Date Column, and End Date Column, a Duration Column, and a scope of work column.

The Scope of work is consistent from Row to row, depending on what the scope entails.

I am looking to create a sheet that will break down the number of hours assigned to a scope of work, by week so that I can forecast upcoming work... I mean, I've created the sheet, its just not working.

My Layout is the following:

Rows : week of the year (Week 1, Week 2, ...), with a start date and end date column, and then a column for each scope of work ( demolition, prep, install, etc.).

The issue that I am not able to wrap my head around is that my start dates and end dates in the Raw data aren't always at the beginning and end of the week, and the duration carries over across weeks at a time.

I have tried doing a Countifs Formula, but it will only count the number of cells that meet that criteria, and not the duration total of that sell. The Sumif formula gets me closer, but when the duration carries over multiple weeks, it is not giving an accurate representation as to what is happening in that week.

Any direction or ideas would be greatly appreciated!

Thanks,

Nathan

Paul Newcome

Are you able to provide a few screenshots for reference?

Nathan_Coutts

Hi Paul,

Of course! see below

The Input

Employee list : This is a list of employees, along with the scope of work that they typically perform.

Job List: This is a list of all the jobs that we have in our third Party app, that has been imported. they contain a start date, an end date, and a duration column, as you can see. they also have a column for the people are assigned. ( if there are any 'Johns' that are overlapping, that's just a lack of me paying attention when putting the names in. typically there would only be one person assigned to a task per day)

The output:

Looking to forecast per week. Trying to show the percentage of the capacity that we have scheduled in each scope of work.

Capacity is pulled from a list of employees in the employee sheet, it is expressed in manhours. As the list grows or shrinks the number of hours grows or shrinks from week to week, the numbers would adjust based off of the start and end date of the employee on that page.

All the columns to the right of 'Capacity' where meant to be expressed in percentages, based off of the total capacity that the total amount of employees that are assigned to that scope. For instance, if I have 5 guys that are assigned to Demo on the employee page, then I know that there are 50 Man hours a day that I can allocate to demolition.

I would like to try and get an accurate representation of the % capacity. sometime I have 2 employees assigned to a Job, Sometimes 3, so would like to try and get that reflected properly in the correct week.

Thanks in advance for any suggestions! Open to ideas too if there are any thoughts on a better way of doing this too.

Nathan_Coutts

Racking my brain on this still. Would there be a way of looking at it with the start date of the Week in the output sheet, and the end date of the individual job on the job list, and then a sumif function within that..

Nathan_Coutts

Found a work around to this, which is to analyze each day separately within the week... turns into a long formula though.

=(IF([End Date]@row - [Start Date]@row + 1 = 5, COUNTIFS({August 29 Jobs Range 1}, "Demolition/FloorPrep", {August 29 Jobs Range 4}, >=([End Date]@row), {August 29 Jobs Range 3}, <=([Start Date]@row + 4)) + COUNTIFS({August 29 Jobs Range 1}, "Demolition/Floor Prep", {August 29 Jobs Range 4}, >=([End Date]@row - 1), {August 29 Jobs Range 3}, <=([Start Date]@row + 3)) + COUNTIFS({August 29 Jobs Range 1}, "Demolition/Floor Prep", {August 29 Jobs Range 4}, >=([End Date]@row - 2), {August 29 Jobs Range 3}, <=([Start Date]@row + 2)) + COUNTIFS({August 29 Jobs Range 1}, "Demolition/Floor Prep", {August 29 Jobs Range 4}, >=([End Date]@row - 3), {August 29 Jobs Range 3}, <=([Start Date]@row + 1)) + COUNTIFS({August 29 Jobs Range 1}, "Demolition/Floor Prep", {August 29 Jobs Range 4}, >=([End Date]@row - 4), {August 29 Jobs Range 3}, <=[Start Date]@row), COUNTIFS({August 29 Jobs Range 1}, "Demolition/Floor Prep", {August 29 Jobs Range 4}, >=([End Date]@row), {August 29 Jobs Range 3}, <=([Start Date]@row + 4)) + COUNTIFS({August 29 Jobs Range 1}, "Demolition/Floor Prep", {August 29 Jobs Range 4}, >=([End Date]@row - 1), {August 29 Jobs Range 3}, <=([Start Date]@row + 3)) + COUNTIFS({August 29 Jobs Range 1}, "Demolition/Floor Prep", {August 29 Jobs Range 4}, >=([End Date]@row - 2), {August 29 Jobs Range 3}, <=([Start Date]@row + 2)) + COUNTIFS({August 29 Jobs Range 1}, "Demolition/Floor Prep", {August 29 Jobs Range 4}, >=([End Date]@row - 3), {August 29 Jobs Range 3}, <=([Start Date]@row + 1)))) * 20 / Capacity@row

This now allows me to determine that if a job on my list contains the scope of work to count it in the proper week of the year row. I assume that a standard workday is 2 guys and 10 hours, thus giving me a rough estimate into how many working hours a task will take. Go through the list, and analyze each row, and it looks like all the numbers are going where they should.

Let me know if anyone sees a way to make that formula any smaller.

Thanks,