Project Cashflow

James_Salmon

Hi there,

I would like to be able to create a month by month cashflow report from a Smartsheet Project (or a number of projects) based on the schedule and Budget Amount entered into each row.

ie. Take a standard Project in Smartsheet, which has rows for each task (along with STart and Finish times) and has additional columns for Actuals and Forecast Budgets, and create a report that shows the expenditure for each month based on Start/FInish and the Actual/Forecast Budget columns. Is this possible? It's really easy to do in Microsoft Project, and I am hoping can be done similarly in Smartsheet - ideas welcome!!

 

Thanks,

 

James

 

 

Shaine Greenwood

Hi James,

Currently, reports can't perform calculations on their own—you'd need to do this by creating formulas on the sheets themselves and thing bringing the rows into a report.

More information on formulas and functions in Smartsheet can be found in our help center: https://help.smartsheet.com/functions

If needed, details on reporting can be found in this help article: https://help.smartsheet.com/articles/522214-creating-reports

 

James_Salmon

Hi Shaine,

 

I'm happy to do it a different way, but what I am trying to avoid is having to do a manual calculation every time a person wants to add additional tasks, sub tasks etc to the project sheet. ie, much like MS Project does - take all tasks, add their budgets (sum budgets for each 'section') and present that over a month to month timeline, allowing us to quickly identify spend in key areas of the project on a month by month basis (comparing actual to budget). Frankly, I'm having a hard time knowing where to start with creating that in a way which does not require manual intervention each time we do it and for each Project we do.

Any suggestions?

 

Thanks,

 

James

 

 

Shaine Greenwood

Hi James,

You could create parent rows for each month with hierarchy (details in on hierarchy this article) and place a =SUM(CHILDREN()) function in the parent row. This will sum all children, so you'll only need to make sure that rows are correctly organized under their parent. (check out the functions list for more on both SUM and CHILDREN functions: https://help.smartsheet.com/functions)

If you need to calculate totals month-to-month you'll need to create a formula of some kind and the data will need to exist on one sheet for now. Later this year, we're releasing a feature that will allow you to create formulas that reference data across multiple sheets.

James_Salmon

Hi Shaine,

That doesn't really do what I am trying to achieve. I am trying to get to the point where I can essentially pivot the budget data and schedule into a month by month expenditure sheet. ie. because I have the tasks with end date and budget and actuals, I should be able to show a report for any given period what the actuals vs budgets totals were for that period. It seems like a simple thing to do, but I cannot see how I can get Smartsheet to do it.

 

Idea's?

 

Thanks,

 

James

 

 

Warren Labuschagne

Hi James, did you ever come right with a solution on how to create a cash flow projection showing projected monthly expenses taken from a schedule. 

Rgds Warren