# Cash flow from a Fee Calculator - Calculate assumed Monthly Rate

✭✭✭✭✭✭
edited 08/17/22

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.

• ✭✭✭✭✭✭

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.

• ✭✭✭

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?

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭

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

• ✭✭✭✭✭✭

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!