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.
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!
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.
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.
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.
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:
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()
