Smartsheet PMO Template Setup - Part 3 - Customize Project Level

In this 3-part Smartsheet tutorial series, I will teach you step-by-step how to set up the Project Management Office (PMO) template set. The PMO Template set is Smartsheet created template that provides a streamlined way for organizations to approve, track, and start new projects while aggregating key metrics across all projects.
This Series:
Setting up the Smartsheet PMO Template - Part 1 - Setup & Customize
Part 1 - • Smartsheet PMO Templat...
Setting up the Smartsheet PMO Template - Part 2 - Customize Project Intake and Portfolio Level
• Smartsheet PMO Templat...
Congrats! You made it to Part 3 of the Smartsheet PMO Template set 3-part series. In this video, I will walk you through the final step to setting up the Smartsheet PMO Template set. I will demonstrate how to modify the project plan sheet, set up baselines to track project status, set up a logic-driven schedule with durations and predecessor tasks, assign tasks to individual project team members and create filters for easier data analysis. We will also update the automation to send alerts to the project team members when specific criteria are met. Additionally, we will review the sheet summary, discuss the Overdue Report and look at the Milestones Report. And lastly, I will show you how to quickly review the project schedule health and lastly, we will take a look at the Project Specific Dashboard.
The PMO template is recommended for the Intermediate Smartsheet experience level and is at a medium in complexity level. Please check out a few of my other videos if you are new to Smartsheet or need a little brushing up before setting up this template. Here are a few recommendations or videos that I referred to in this series that might be helpful.
Creating Smartsheet Forms • Creating Smartsheet Forms
Mastering Your Project Schedule - Predecessors, Setting a Baseline, & Critical Path - • Mastering Your Project...
Managing (Human) Resource Allocation in Smartsheet in Less than 15 Minutes! • Managing (Human) Resou...
Email me! [e-mail]
***************************************
Learn more about Effective Flow Connections: www.effectivef...
Want to book a 1:1 coaching session with Kandis? You can do so via this link! www.effectivef.......
Kandis Porter is the Founder and Managing Director of Effective Flow Connections (EFC), a management consulting company that helps transform organizations through project management, process improvement, change management, facilitation of key meetings, and training. Kandis also works one-on-one with clients, focusing on Leadership development and growth. EFC enables organizations to do what they do, even better! Kandis Porter has more than 17 years of experience managing people and large projects in both the government and private sectors. She has supported clients in various industries, including the Department of Defense, Federal Aviation Administration, utilities, medical device, non-profit, and learning & development. Kandis has a Master of Science Degree in Project Management (MSPM) from The George Washington University and Master of Business Administration (MBA) in Human Resources. In addition, she is a certified Project Management Professional (PMP), PMP Exam Prep Course Instructor through Project Management Institute, Prosci Change Manager, and Six Sigma Green Belt. She is also certified as a Myers Briggs Type Indicator (MBTI) Practitioner, Thomas-Kilmann Instrument (TKI) Practitioner, and is a DiSC Workshop facilitator.
Connect with me on social -
LinkedIn: / effective-flow-connect...
Facebook: / effectiveflowconnections
Subscribe to my KZbin channel: Kandis Porter - KZbin

[@dwellsemail]

This video series literally saved our team $40k. We were contemplating adding SS Control Center to automate the Portfolio Rollup, but this was exactly what we needed. Thank you so much. P.S. You likely already know this trick, but we added a formula to the Status column in our Project Plan to update automatically based on %Complete. IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row = 1, "Complete", "In Progress"))

[@PMPexpert]

Doug - THANK YOU! This absolutely made my day. I’m so happy to hear you and your team benefited from setting up the PMO template set. I definitely think there is a need to share a video on Smartsheet formulas, as you can do so much with them! Be on the lookout for a new release next week, focused on Smartsheet formulas and using ChatGPT. Best of luck!

[@PMPexpert]

Would love to hear an update on how this is going for you, @dwellsemail. Keep us updated on lessons learned!

[@marisolwhalen9872]

Love It!!! I tried to follow the step by step instructions and was stuck beyond belief, thank you for doing this, loved the cadence, explanations, and the professionalism!

[@PMPexpert]

Thank you for the positive feedback! Wishing you the best on your projects and utilizing the PMO setup. 🙌🏻

[@colinjamieson6065]

Hi Kandis, really enjoyed this series. Please do a video on Earned Value Management and how (if possible) we can do a Portfolio level dashboard/report on the earned value across ALL projects. Also is there a way to get a roll-up view across all projects on critical paths instead of going into each project to report.

[@PMPexpert]

Thanks for the suggestion, Colin! I agree a EVM video would be helpful for many. In the meantime, here are some thoughts on setting it up in Smartsheet...WARNING...this will be a lot of information! I hope it is helpful. Yes, you can use Smartsheet for Earned Value Management (EVM), though it requires some customization and setup to fully implement the key EVM metrics and calculations. You can also extend these capabilities to a portfolio level to manage and monitor multiple projects at once. First, you'll need to set up your project in Smartsheet by outlining all the tasks and subtasks in a project sheet. Ensure that each task has start and finish dates, assigned resources, and estimated costs. Use dependencies to define relationships between tasks, which is crucial for tracking project progress and understanding the critical path. To track EVM, start by entering the planned cost for each task in a column labeled "Planned Value (PV)." This represents the budgeted amount of work scheduled to be completed by a certain date. You can also set a baseline schedule in Smartsheet to compare actual progress. While Smartsheet doesn’t have a native baseline function, you can manually create a column for baseline dates and costs. Next, create a column to track the actual cost incurred for each task, labeled "Actual Cost (AC)." Update this regularly to reflect the current spending. Add another column for "Earned Value (EV)," which is the value of work actually performed. This can be calculated based on the percentage of completion multiplied by the Planned Value (PV). To calculate key EVM metrics like Cost Variance (CV) and Schedule Variance (SV), use the following formulas: CV = EV - AC and SV = EV - PV. Similarly, for the Cost Performance Index (CPI) and Schedule Performance Index (SPI), use CPI = EV / AC and SPI = EV / PV. You can set up formulas in Smartsheet to automatically calculate these variances and indices. Use Smartsheet’s dashboard feature to create visual representations of your EVM metrics, such as CV, SV, CPI, and SPI. Dashboards can include charts, graphs, and key performance indicators (KPIs) to monitor your project’s health in real-time. Additionally, you can generate reports to summarize EVM data across projects or for specific time periods, helping you track trends and identify potential issues early. If you manage multiple projects and want to apply EVM at the portfolio level, you can consolidate data from individual projects into a central portfolio sheet. Use cross-sheet formulas or cell linking to pull data from each project's EVM metrics into this portfolio sheet. In the portfolio sheet, create summary columns to aggregate metrics like total Planned Value (PV), total Actual Cost (AC), and total Earned Value (EV) across all projects. You can then calculate portfolio-level EVM metrics such as portfolio Cost Variance (CV), Schedule Variance (SV), Cost Performance Index (CPI), and Schedule Performance Index (SPI). Finally, set up portfolio dashboards to provide a high-level view of your entire portfolio’s EVM performance. Use Smartsheet’s dashboard feature to visualize aggregated metrics and monitor portfolio health. Automations can be set up to notify stakeholders when portfolio-level EVM metrics fall outside acceptable ranges, ensuring timely intervention. While Smartsheet doesn’t have a built-in EVM module, you can effectively manage Earned Value Management at both the project and portfolio levels by customizing your sheets with additional columns, formulas, and cross-sheet references.

[@colinjamieson6065]

@PMPexpert Thanks Kandis I've already created the EVM spreadsheet in Excel - I'll look at building it into a SS sheet. and follow the advice from there. What about a portfolio level dashboard that somehow indicates all the critical paths?

[@eclecticshenanigans]

This series has been a game breaker for me. So good. thank you for your diligent and through training style.

[@PMPexpert]

Awesome to hear! Thanks for the feedback and best of luck. 😃

[@dankirk6096]

Thank you for this 3 part walk through. I learned a few new things about Smartsheet and Project Management.

[@PMPexpert]

Great, thanks for watching Dan! 🙌🏻

[@user-tk1gd3ck4e]

Kandis, Thank you so much for this set of videos on the PMO. I had been trying to set this up for a year and struggled. You explained it perfectly. I appreciate this so much. It is much better than the Smartsheet offered training. Bless you.

[@PMPexpert]

Thank you for the feedback and congrats on getting the PMO setup! 🎉

[@madelinehunter1843]

A few good take aways for me is the instructions on building and customizing the schedule,. I love you the way you explain about setting up logic, and the importance of sitting down with the team and do a WBS , All to say I have learn so much from this video as well

[@PMPexpert]

Glad it was helpful! Thank you

[@xianbrewso]

Thank you Kandis for this wonderful series about the PMO, very helpful, I will definitely use this template set! Looking forward to browse through your videos on PM!

[@PMPexpert]

You are so welcome, Christian! Thanks for watching.

[@jagathperera6476]

Really good instruction and easy to follow up to smooth learning,

[@PMPexpert]

Thanks for watching!

[@WasabiDoobie]

This is a great series, thank you! The content/outline is great, but you have outstanding tone and pace 👍🏼! I have a customized set of this that I use on all my engagements as a consultant/contractor, but you had several insights and tips that were outstanding and I updated my master template set accordingly. 🍻 ✌

[@PMPexpert]

Awesome, thanks for the feedback! Best of luck managing your projects. 🙌🏻

[@hfdsthjjbgt3655]

Hi Kandis, your videos are amazing! Like others, I struggled to get started with Smartsheets until I found your videos. Question, I put in a lot of effort into building that first project, and all my projects will have almost the same tasks, etc with minor variations. How can I copy the entire project to use on another project to save me time and still roll up to portfolio correctly? Thank win advance!!!

[@irinademberg9561]

Thank you so very much! all 3 videos have been amazing and so very helpful!

[@PMPexpert]

You are so welcome!

[@siempreonlinetv5484]

Excellent video. Thank you so much for creating this video. It was very helpful.

[@PMPexpert]

Thanks for watching, Siempre!

[@1kempinga]

Great videos Kandis! Amazing content. Though working through it.... as I am coordinating projects (construction) with fixed delivery dates - and using the PMO set up. Now, the one driver that changes the tasks (+700) is the "practical completion date" of the building. I just want to input the delivery date and that all tasks dates changes - in the current PMO set up the driver is the "start date" of the project, and I need to base timelines on the "end date" of the project. Can you give me some guidance please/ I sincerely appreciate your feedback

[@PMPexpert]

Thanks for watching! Unfortunately, Smartsheet does not (currently) have an explicit "schedule from finish" feature like Microsoft Project, but I'm hoping it will at some point. Here is a workaround: Work backward from your fixed end date using the Start-to-Finish (SF) dependency type in Smartsheet: Row 3: Practical Completion: This is your fixed end date on 5/30/2025, and takes 1 day to complete. No predecessor is needed for this task. Row 2: Final Inspection: This task has a duration of 1 day and should finish when "Practical Completion" starts. In the Predecessor column, enter "3SF" (Practical Completion is in row 3). This will set Final Inspection to end on 5/29/2025. Row 1: Construction Wrap-Up: This task has a duration of 8 days and should finish on 5/28/2025, the day before "Final Inspection" starts. In the Predecessor column, enter "2SF" (Final Inspection is in row 2). This will set the dates for Construction Wrap-Up to start on 5/19/2025 and end on 5/28/2025, assuming you are on a Monday - Friday workweek for the project plan. Best of luck - let me know how it works for you!

[@PaulinaGuerrero-so2to]

This is so very very useful. The only part in series that confused me was using the Target Start.End. If thats driving the Project Health, it feels like any small slip in a single early delivery could show the project as At Risk, which in my world at least isn't true (if you can make up time elsewhere). Even if you reset baseline, the health wouldnt reflect, is that true? I wonder if you have any suggestions?

[@PMPexpert]

Hi Paulina - I agree that Project Health is best customized in the PMO template set, to align with your company’s definition. If you haven’t discovered the Smartsheet Formula Handbook Template Set (link below to a video), this could be helpful to give you ideas on how to modify! I definitely have clients that want the formulas to be very specific to what the PMO has provided as guidance. Cheers to successful projects! kzbin.info/www/bejne/nJfZaaJ5qNprjMU

[@babajidekehinde6900]

Thank you!

[@PMPexpert]

Thanks for watching!

[@johngraham6506]

Thank you again for helping me get through the full process. Can you recommend a video for me to learn one more thing. I have the PMO going, however, I have multiple customers and some customers have a single project, other customers have multiple projects. So, I need a full view of everything, but I also need a view of the multiple projects for a single customer at the customer level. Thoughts? Ideas? Suggestions are greatly appreciated.

[@PMPexpert]

Hi John! We are actually working with a client on a custom build for a similar situation. They want their company’s portfolio built out (via the PMO template set), and they also want a customer portfolio built out. We are approaching by creating additional reports that feed into a customer dashboard and filter out any unnecessary information (i.e., other customers). The great thing about Smartsheet is that you can build multiple reports/dashboards off the same sheet, for different purposes. I recommend creating a new folder for each client and storing additional reports/dashboards there, to remain organized!

[@user-ei4ur9sz4g]

Kandis -- Your PMO Template series tutorials have been extremely informative and helpful - thank you! You provide some info. I haven’t been able to get from the Smartsheet-provided training. I am struggling with how Schedule Health is calculated in the Project Plan that comes with the PMO template set -- it seems misleading and not an accurate reflection of health because: 1. After setting a Baseline, the Schedule Health is still calculated using Target End Date (which is likely a different date than what is in my approved Baseline). 2. Schedule Health is also using the Actual End Date (“End Date”) but we could be past that date and not yet determined the actual end date yet - so the health could be misleading - showing as “green” until I enter the actual date. (In other Project Plan templates - not part of PMO set - the Schedule Health would show “red” if the Baseline End date passed and is not marked as 100% complete. Am I understanding this correctly? If so, have you or others had issues with this and have suggestions for how I can still use this template (within PMO set) but accurately track and communicate Schedule Health? Thank you in advance for any additional insight and tips you can share.

[@PMPexpert]

Hello! Thanks for watching and for your question. I'm a big advocate for making the Smartsheet templates work for you. You can actually customize the formulas in Smartsheet to tailor how the Schedule Health is calculated. The current template simply has this: [Schedule Delta (%)] value > 0.1: Red [Schedule Delta (%)] value > 0: Yellow Otherwise: Green Have you thought about what you want the project health to reflect? Once you do, I'm sure this community can help you write a formula! Just let us know. For a recent project template we created, we wanted the schedule health to reflect these conditions and colors: "Complete" status or 100% complete: Green "On Hold" status: Gray End date within the next 5 days and less than 100% complete: Yellow End date has passed and less than 100% complete: Red To make this happen, we used this formula in the template: =IF(OR(Status@row = "Complete", [% Complete]@row = 100), "Green", IF(Status@row = "On Hold", "Gray", IF(AND([End Date]@row >= TODAY(), [End Date]@row

[@jagas226]

Hi Kandis, this is really awesome and very interesting thanks a lot for your time and explanation. I have small query about the reporting, i have setup and creating dashboard reporting now but how i show all the reports dashboard? can you please give some inputs.

[@azksp]

Hi, Awesome series. I was finally able to get into Smart Sheets and understand it. However, I am just stuck at the start date and end date that is in the project intake form and why it is not updating automatically inside the project plan. Any insights are appreciated.

[@mbjlukas]

Hello - great job -- so you keep the target dates as the Org Dates and you keep the baseline and you allow the baseline to be updated if needed and agreed with Stakeholders

[@laurencsc8393]

Thank you so much for these videos!! So helpful! Do you have any best practices for sharing to end users? I'm hesitant to share the whole workspace with everyone who only needs to see one specific project in one of the project folders but of course want to avoid sharing individual sheets. Thank you again!!

[@PMPexpert]

Glad the videos are helpful! One idea for sharing is to create and publish a dashboard that has links to all the different project sheets. You can then give people access to the individual sheets (as needed), but there will be one dashboard to help keep the information organized.

[@laurencsc8393]

@@PMPexpertThank you so much! That's a great idea!

[@kashmoney1]

Great video - 2 questions please : what's the best to include multiple project details, I have over 50 projects so which sheet should I use to track it's progress and status, dates, etc. so each project has its own tracking. # 2 is that the linking of those 5 yellow cells in the new project intake sheet - is there any way we can automate so we don't have to keep linking those yellow rows to new project folder? unless Im missed a step somewhere.

[@user-rz9im6uw2j]

the three set of videos is really really helpfiul. However i have one query, The moment i change the start and end date of the project, it is not visible in gantt chart of Portfolio reporting... any reason or am i missing something? Please reply

[@user-iu6qz3jw3q]

Thank you Kandis. ThIs tutorial has been very helpful! I have a one question. My project Data is feeding into my Project Tracking and Rollup and I have marked all tasks 100% complete but how do I get the PI Sheet to show that the project is complete? It still says "In Progress".

[@PMPexpert]

Hello! Please try to change the 'Status' column to 'Complete' in Row 1 of the completed project plan. This is the cell that is linked in the Project Metadata / Intake. I just tested it and confirmed it works for me.

[@KatherineDavidson-gu2xh]

Hello! When I create a new project the Dashboard Link does not show up. I do not know if I deleted it somehow in the template and do not know how to undo it.

[@kulrajsingh9415]

Hi Kandis, amazing tutorial, Loving it so far. I am not understanding the Target start date and end date from the intake form. Where are they getting populated in the project plan?

[@PMPexpert]

Hello Kulraj - The Target Start Date and Target End Dates should be manually entered when you set up the Project Plan Sheet in the template set (see 'C' below). They are used to calculate the project health of the schedule. Here is a link to all instructions to the PMO template set, in case they are helpful: app.smartsheet.com/b/publish?EQBCT=8c1ace8ce2d14472a986005d454626d8. Thank you! Modify the Project Plan Sheet Use the Project Plan sheet to track and maintain critical data about a project. Project sheets in Smartsheet have dependencies enabled and includes columns such as Duration, Predecessors, and % Complete. The project plan will automate your project timeline from the start date and will provide an estimated end date based on project tasks. It’s best to enable dependencies for projects with deadlines to ensure every milestone in your project is tracked, on time, and ultimately meets your deadline. A. In the Task Name column, name the project phases in the parent (gray) rows. B. Use the indented grandchild (white) rows in your hierarchy, to enter tasks in the Task Name column. C. Starting on row 3, enter dates in the Start Date and End Date, Target Start Date, and Target End Date columns. This template uses the difference between the Actual End date and the Target End date along with Duration to determine the health of the schedule. At the beginning of your project they should be the same for each row. D. The other data on your ancestor and parent rows, including Start Date, End Date, % Complete, and Duration are auto calculated by the tasks below the parent row. E. Attach documents or links to webpages to the row in the Attachment (paperclip icon) column to keep all of your project information and resources in one place. F. Track the progress of your project by viewing the interactive Gantt timeline to see which tasks are complete (green), which are late (red), which are in progress (blue), and which are on hold (orange). Conditional formatting rules apply formatting automatically to rows or cells based on the values they contain.

[@tylerjewell7561]

Kandis - great video. For customizing the Project Contact List on the Dashboard, the metric widget is pulling data from the Project Plan. It is unclear to me how it is deciding which field to pull each position (PM, Port Mgr, etc) from. Normally you can see which specific data set it's pulling from, but I can't see it. Are you able to shed light on this?

[@PMPexpert]

Hey Tyler! It looks like the dashboard is linked to the ‘assigned to’ column for rows 3, 4, and 5. Row 3: PM, Row 4: Portfolio Manager, Row 5: VP of Projects. I’m not sure why it is setup this way, but you can change the ‘data source’ in the dashboard widget. One thing that you could do is creat a separate sheet with key project roles and link to that instead. I hope this is helpful!

[@tylerjewell7561]

@@PMPexpert Thanks! Yes, I agree. Or create another parent row in the project sheet as a "establish project team" task or something.

[@krismoodley6418]

Thank you. Your presentation is fantastic. I am an educator but you can teach me a thing or two

[@PMPexpert]

Thank you, Kris! I’m flattered. Wishing you the best in educating our future leaders. Cheers!

[@Nadege-pe7di]

Hello Kandis, great video! How to copy a project and plan and the project dashboard as template for other project ? Which hyperlink we need to copy where ?

[@PMPexpert]

Within the Browse Menu of Smartsheet, you want to right-click on the Project Template (Copy for New Projects) folder and select Save as New… Then you will name the folder with your project name and save it within the same workspace (you shouldn’t need to edit this) and click save. The individual project reports, sheets and dashboard will all be established for your new project. You want to create/copy the entire Project template folder not individual sheets.

[@grarace7228]

Hi there, where I have milestones added it shows up the schedule health as green when in effect it is red (I think due to the 0 days element). Is there a way to revert this back to the correct colour?

[@Heather-Roberts-hq3hx]

This series was so helpful! Thank you! Kandis, I made all the changes to the Project Plan for my first project and have 6 more to do. Can I copy my project instead of the one that came with the template? I wanted to be able to avoid having to make the same types of changes over and over again, but I don't want to mess anything up with how it all works together in the dashboard and stats.

[@lyssie4343]

I am wondering the same thing!! For the original one that is titled copy, I was wondering if I can make the changes on the project plan on the one so that when my team (or I) copies the project plan it is set up the way we need. I hope Kandis answers us! These tutorials are amazing, I’m switching my team from M365 Project to Smartsheet and needed these to make it happen

[@Nadege-pe7di]

Interrested to get the answer too ! @PMPexpert

[@PMPexpert]

Absolutely, feel free to customize and generate a new Project Template (Copy for New Project), or modify the existing Project Template folder to reflect changes across all upcoming projects. It's essential to double-check your links within the sheets and reports to ensure seamless functionality, avoiding additional workload during future project setups. Also, when creating a new folder, I recommend giving it a distinct title like 'Project Template (Copy for New Project)' to prevent confusion with the master copy folder and specific project folders.

[@KalyaniManchiraju]

Is there a way we can track Project risks, issues, changes, action items and decisions and add them to the project dashboard and eventually portfolio dashboard?

[@PMPexpert]

Yes, you can setup a RAID log folder in your project workspace and track each of these items in a different 'sheet'. You can then have them all feed into one row report, that you can add to the dashboard, sorted by item. I hope this helps!

[@alliedavis3558]

Could you please share the formula you used for the schedule health column?

[@PMPexpert]

=IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green")) This formula uses the ‘Schedule Delta’ column to determine the symbol color to display in the ‘Schedule Health’ column. So let me 1st explain that column, if the ‘Target Start Date’ is later than the (planned) ‘Start Date’ the ‘Schedule Delta’ column display a % change. For the formula, if there is a % change over .1 (or 10%) the ‘Schedule Health’ column with display Red, with any % change greater than 0 and not already displaying red the ‘Schedule Health’ column with display Yellow, and for anything else the ‘Schedule Health’ will display green.

[@KalyaniManchiraju]

If the same format is followed for all the projects, Is it beneficial to change the Project Template folder instead of the individual project folder? That way when we copy the Project Template folder for every new project, we do not have to repeat the changes

[@PMPexpert]

Hi Kalyani! Yes, you could certainly build a 'RAID" log into your project template folder, and update the Dashboard to include highlights from the RAID log, so it can be copied with a new project! This might be a helpful video to give you some ideas: kzbin.info/www/bejne/ZqureWCrnqmWgK8. I have a separate video on the value of a RAID log, as that may be helpful too. I look forward to hearing how it goes!

[@KalM18]

@@PMPexpert thank you.appreciate it

[@traceyeppes1697]

Hello! I love your tutorial and it is helping me immensely! As I was following you to set up the Project Plan, I think I overdid some of the deletion with the Schedule Delta columns and now I have weird percentages where it should be zero. Is there a way to fix my mistake or will I have to scrap this Project Plan and start over? Any helpers out there? Thanks!

[@traceyeppes1697]

Nevermind- found my mistake!!! UGH LOL

[@PMPexpert]

I'm so glad you discovered the challenge. Was something off with the indentation of the parent / child tasks? Or did it need you to save it before the changes took place? This is actually a super common question that I receive and I'm curious how you solved it! I hope your projects are going great. :)

[@PMPexpert]

Glad it was helpful!