Cash flow from a Fee Calculator - Calculate assumed Monthly Rate

Richard Heath

I've built out a Fee Calculator that assists our team in articulating the %allocation of a team member against a Phase of a project for upcoming projects. You'll see from the screenshot below, that the example Project runs from 4 April 2022 through to 25 November 2022 and that the Allocation reflects time against a Full-Time Employee.

I've been asked to see if I can extract forecasted monthly claims based on our Fee. For Example, I would expect to bill:

• April 2022 - (Resource 1 - $10,875) + (Resource 2 -$4,350)
• May 2022 - $4,350 + $8,700 (estimated as the Phase (2 - Design in this instance) of the Project runs over two months.)
• June 2022 - Mostly Design Phase, and a few days of Procurement.
• July 2022 - A little Procurement, but mostly Delivery Phase
• August 2022 through November - All Delivery Phase

Does anyone have a formula that can determine that a value is assigned to a particular month? I think it might be too complex for Smartsheet.

Richard Heath

I've resolved this issue, but in Excel, which is fine by me.

I utilised this website to assist with the formulas - it uses some complex MAX and EOMONTH arguments, which can't be duplicated in Smartsheet. I thought someone else here may need something like this one day.

I dump the start and end dates with the value of the scope of work - Column H through to XX then allocates the value of work undertaken in any given month. I then graph the data to give me a Cashflow Graph for the entire project and per PM. The values allocated don't always match the value in Column B every time, but its close enough to live with for our purposes.

Intern98

Hi, nice smartsheet you have built.

I am not sure what kind of help is needed in this case, would you elaborate more on this?

Richard Heath

I'm looking to have the sheet (or another sheet) provide an overall tally for each Month that we have assigned some costs against the resources - the example below tallies Resource 1 and returns the monthly fee. (Column 2 is simply the NETWORKS days for the given month). Ideally, we can get the formulas to look at all resources and combine.

I still don't believe that Excel or Smartsheet has the functionality to dive this deeply into analysing costs. But happy to be proven wrong.

James Keuning

Maybe I am missing what you are trying to do... could you use COLLECT to grab the numbers for the month, and then SUM?

Richard Heath

I've resolved this issue, but in Excel, which is fine by me.

I utilised this website to assist with the formulas - it uses some complex MAX and EOMONTH arguments, which can't be duplicated in Smartsheet. I thought someone else here may need something like this one day.

I dump the start and end dates with the value of the scope of work - Column H through to XX then allocates the value of work undertaken in any given month. I then graph the data to give me a Cashflow Graph for the entire project and per PM. The values allocated don't always match the value in Column B every time, but its close enough to live with for our purposes.