Gannt Chart With Dynamic Income/Expenses Per Task



I am a contractor and I have all of my draw payments tied to tasks (ex. beginning of drywall) and all my subcontractor payments also tied to tasks (ex. beginning of framing). I'm trying to use my project Gannt to also track my cash flow. I've done this in Excel; however, it was VERY complicated and kept breaking. My thoughts:

-Build my project schedule in a Gannt chart

-Input all of my income and expenses and tie them dynamically to the corresponding task (I had to do this in a separate table above or below the Gannt which mad it more difficult)

-At the bottom, have a calculation showing me the total cash balance for that week, based on (income that week) - (expenses that week) + (the cash balance the previous week).

Here's the part I can't figure out. How do I associate the transaction with the task dates? Ex. if drywall is late by three weeks, I move the schedule forward three weeks and the income from that ALSO moves forward three weeks.

Any thoughts?


  • Erin Greunke
    Erin Greunke Moderator
    edited 10/16/20

    Hi @Aaron Skoczen !

    Do you have Sheet Summary enabled on your sheet? That could be a good place to keep the table of task types and their associated costs that could be referred to for a column adding up that associated task factoring in hours. Weekly income and expenses could also be fields tallied there, as well.

    You'll notice on some of the templates we have in the Solution Center that we add tables to the top of the sheet when Sheet Summary might not be used. We use Hierarchy to hide that section so that it's not in the way or viewable when we don't want it to be.

    You could also use Hierarchy to calculate your total expenses and income for weeks -- depending on how you have your Gantt chart set-up. So, if you move a task to a different phase/week (depending on how you set up your sections), you use the =Sum(Children()) functions to capture income and expenses for each of those sections.

    A final note, we have a Construction and Facilities Management template set in you might want to check out for some ideas.

    Hope this gave you some ideas!

    Erin Greunke
    Sr. Program Manager, Smartsheet Beta Programming

  • Aaron Skoczen

    Hi Erin,

    Thanks for the response; however, I need some clarification on your comments please:

    -Could you point me to which templates in the Solution Center has the tables on the top that you're referring too.

    -Is there a tutorial to describe the "Hierarchy to calculate your total expenses and income for weeks...", or a template? How do I create this column to represent each week?