Creating a project schedule based on task lead time AND budget?

[matt_stewart1]

I am trying to find a better way of building out our schedule across the entire portfolio.

Current setup:

• Projects table
• Task Table
• Financials Table

Currently the schedule only assumes the task lead times to do a workback for planning along with a workforward schedule that adjusts forecasted delivery based on actual stage gate progress.

Separately we manage finances and track costs and approvals, along with payments, however all of these are linked to the projects. When we hit our budget for the month in our portfolio spend, we end up delaying tasks and pausing development.

So what i would like to do is have the workback schedule anticipate the weekly budget and increase LT for the workback so that we can ensure we are starting projects early enough to schedule in their payments.

Is there a way to structure this with automations and formulas to avoid having to do this manually for 1000's of projects?

[Sburvit]

Hello,

I see it this way:

First, integrate budget constraints into scheduling. In your Financials Table, track each project's allocated weekly budget and spending. Then, use a rollup field in the Projects Table to calculate the cumulative spend for all associated tasks. Add a formula to determine if the budget limit has been reached for a given period.

In the Task Table, introduce a formula field to dynamically adjust the lead time based on the available budget.

Set up automation to adjust schedules in real time. The trigger is when a payment is recorded or a task is marked complete in the Financials Table. The action is an update the lead times or forecast delivery dates in the Task Table based on budget availability. Another automation can notify stakeholders when tasks are delayed due to budget constraints.

Create a formula in the Projects Table to estimate when a paused task can resume based on expected budget availability. Use the total forecasted spend and projected budget replenishments to calculate potential start dates.

You can use Airtable’s gantt or timeline view to visualize the updated schedules. Highlight delayed tasks and their dependencies.

Combining these steps should help you to complete your tasks.