Calculating costs for a period broken down by sprints

I’m working out cash flow planning for a project. For each phase of a project I have a start date and and end date, which covers several weeks.

What I’m trying to do is both plan teams workflow (which is fine with start and end dates) and calculate the costs.

For example say I have Team A of 4 people at £100/day working from 4 Jan to 12 Feb on one project then 15 Feb to 2 April on a new project. The first project brings in a sale of £500 on 2 March.

Meanwhile Team B of 5 people at £80/day starts on 26 January through to 5 March and again 8 March to 2 April, with a sale of £1,000 on 12 March.

So as I said in addition to using the Gantt chart for planning, which works really well in Coda, I want to include these costs. I tried creating a separate table with week numbers (eg week 1 is 4 January, week 2 is 11 Jan etc) to get weekly costs but I can only get the lookup to search whether the phase start date matches the table, not to get all the relevant weeks and associated costs for each phase as well as a weekly cost for all my teams.

The relevant columns I have in my main table are for start date, end date, team involved (A/B). I assume I’ll need a column for income (eg at the end of a phase of sales activity), but what else and what formula should I use to get a cash flow forecast, ideally one where I can insert things such as starting balance, loans, fixed costs such as rent and so on.

Hey @Jonathan_Richardson I think for things this complicated it is very helpful to share an example an d show where you need some guidance.

1 Like

Sure. The original data is a bit sensitive so I’ll make a new doc and share

Hi @Johg_Ananda thanks for your help, I’ve created a dummy document here if that makes it clearer…

Its comment only I can’t see any of the formulas.

hi @Johg_Ananda sorry for that, I’ve updated to Edit

OK I think the next step is for you to create the table for the output you want to realize. What does a cash flow forecast mean in this use case?

Hi again @Johg_Ananda and thanks for persisting. I’ve reviewed the tables and updated columns and created formulas as appropriate.

The table Cashflow table and sprints is what I am looking as an output, where each week I will be able to get a net balance on my account, accounting for the various costs and incomes that week.

The Sprint outgoings and Sprint income columns have dummy figures but these should come from the Project table of the various team income and costs (Income less Team cost that week and Payments).

I’m very much aware that this is getting complicated so understand if it’s a bit much to ask.

OK its hard to track your document. Can you make one page called ‘tables’ and then create subpages for each original table, listing alphabetically? That would get things more organized… I can’t dive into your doc when its a mess. Thanks!

Ok I’ve reorganised it so that Project table is the main page and the other tables are on sub-pages named after them.

All other tables that were in the doc are now hidden so it should be easier to read. Thanks

OK great that’s better! Now what columns are we trying to get working?

Hi @Johg_Ananda just so you know I’ve asked Coda support for help as of yesterday so don’t want you duplicating effort if you don’t want to.

I have tidied up the tables and replaced the dummy figures in Cashflow with the actual figures I am expecting to see.

So in the Cashflow table I have manually input the Weekly outgoings and Weekly income in the columns with MANUAL in them. What I want though is a way to calculate this automatically. I’ve highlighted the values that need to go from the Project Table into the Cashflow table in the AUTOMATIC Weekly outings and income columns.

OK so we need to represent that in the formula language. What did you do ? What was the math and steps, which columns did you use, to get the values/

Dear @Jonathan_Richardson,

To my opinion the steps in below post are to my opinion the direction you have to go for:

Thanks @Jean_Pierre_Traets I have managed to adapt the formula to get the dates in between my start and end dates. I had thought that a Lookup or Table filter would then be able to find whether my week start date is contained within those values but it doesn’t seem to like it.

Eg

[Project table].Filter([Project table].[Start Date].Contains(thisRow.[Week Start Date])).Income

Okay I have solved it! It’s late but I will update tomorrow. Basically it involved Jean_Pierre’s solution of getting the date range for the project dates AND the dates in each sprint week.

This is what I needed to do.

In the Project planning table where I worked out how much a project phase would last, I had columns for Start date, Duration and End date. I created a column called Dates that got all the dates between the start and end:

Sequence(thisRow.[Start Date],thisRow.[End Date]).FormulaMap(CurrentValue.DateTimeTruncate("day")).Unique()

Then in the Cashflow table I created a new column to get all the dates that would occur in a week, ie not just the week start date but the next 6 days

Sequence(thisRow.[Week Start Date],thisRow.[Week Start Date]+6).FormulaMap(CurrentValue.DateTimeTruncate("day")).Unique()

From then I could run 2 formulae to gather the income and outgoings (Staff costs + other costs)

Income:

[Project table].Filter([Dates included].Contains(thisRow.[Dates included this week])).Income.Sum()

Outgoings:

([Project table].Filter([Dates included].Contains(thisRow.[Dates included this week])).[Team cost per week].Sum())+[Project table].Filter([Dates included].Contains(thisRow.[Dates included this week])).[Other costs and payments].Sum()

Which got it working. I’ve now shared as a a template https://coda.io/@jonathan-richardson/project-planning-and-cashflow

OK @Jonathan_Richardsonit sounds like you’re getting some momentum! Great job :slight_smile:

1 Like