Cash flow from a Fee Calculator - Calculate assumed Monthly Rate

Richard Heath
Richard Heath ✭✭✭✭✭✭
edited 08/17/22 inFormulas and Functions

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 theAllocationreflects 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.

image.png


· Share on Facebook Share on Twitter

Best Answer

  • Richard Heath
    Richard Heath ✭✭✭✭✭✭
    Answer ✓

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

    I utilised thiswebsiteto 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.


    image.png
    image.png


    · Share on Facebook Share on Twitter

Answers

  • Intern98
    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?

    · Share on Facebook Share on Twitter
  • Richard Heath
    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 talliesResource 1and 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.

    image.png

    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.

    · Share on Facebook Share on Twitter
  • James Keuning
    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?

    · Share on Facebook Share on Twitter
  • Richard Heath
    Richard Heath ✭✭✭✭✭✭
    Answer ✓

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

    I utilised thiswebsiteto 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.


    image.png
    image.png


    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
Thank you, as always I was trying to make it too hard!!!!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106971,"type":"question","name":"Using SUMIFS Formula","excerpt":"Needing help writing a SUMIFS formula! I have tried a few ways, but just get errors. =SUMIFS({budget}, {Type}, \"AMR\", {leadership}, \"Truttmann\")) I would like to have the sum of the budget if the type is AMR and the leadership is Truttmann. I have multiple types and leaderships that I would be using this for. Thanks!","snippet":"Needing help writing a SUMIFS formula! I have tried a few ways, but just get errors. =SUMIFS({budget}, {Type}, \"AMR\", {leadership}, \"Truttmann\")) I would like to have the sum of…","categoryID":322,"dateInserted":"2023-06-27T18:45:22+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T20:20:13+00:00","insertUserID":162642,"insertUser":{"userID":162642,"name":"jtomeaCH","title":"ISG Support Manager","url":"https:\/\/community.smartsheet.com\/profile\/jtomeaCH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T20:31:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162642,"lastUser":{"userID":162642,"name":"jtomeaCH","title":"ISG Support Manager","url":"https:\/\/community.smartsheet.com\/profile\/jtomeaCH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T20:31:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":25,"score":null,"hot":3375791135,"url":"https:\/\/community.smartsheet.com\/discussion\/106971\/using-sumifs-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106971\/using-sumifs-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106971,"commentID":382695,"name":"Re: Using SUMIFS Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382695#Comment_382695","dateInserted":"2023-06-27T20:20:13+00:00","insertUserID":162642,"insertUser":{"userID":162642,"name":"jtomeaCH","title":"ISG Support Manager","url":"https:\/\/community.smartsheet.com\/profile\/jtomeaCH","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T20:31:55+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-27T20:19:39+00:00","dateAnswered":"2023-06-27T19:59:16+00:00","acceptedAnswers":[{"commentID":382688,"body":"

Double check your cross sheet references. Make sure they are all single columns by clicking on the appropriate column header.<\/p>


<\/p>

When doing this, give the sheet a little time to load before selecting the column. Sometimes moving too fast allows you to select a column header before the sheet fully loads in the creator window. Then when the sheet does finally completely load in, the selection automatically reverts to the home cell (top right corner). This happens to me quite a bit when I am in a hurry.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106940,"type":"question","name":"IF cell CONTAINS one\/two\/three different values, return corresponding values","excerpt":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\", \"Termination\", \"New\" Currently the formula works for single values with the following formula =IF([Type of Project]@row = \"Consolidation\", \"🝢\",…","snippet":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\",…","categoryID":322,"dateInserted":"2023-06-27T09:24:42+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T12:11:45+00:00","insertUserID":143463,"insertUser":{"userID":143463,"name":"Sam Swain","url":"https:\/\/community.smartsheet.com\/profile\/Sam%20Swain","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T20:08:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T12:11:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":53,"score":null,"hot":3375727587,"url":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106940,"commentID":382505,"name":"Re: IF cell CONTAINS one\/two\/three different values, return corresponding values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382505#Comment_382505","dateInserted":"2023-06-27T12:11:45+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T12:11:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/Q6RJIERQGF1Y\/screenshot-2023-06-27-at-10-22-29.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-06-27 at 10.22.29.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-27T10:17:00+00:00","dateAnswered":"2023-06-27T10:12:21+00:00","acceptedAnswers":[{"commentID":382490,"body":"

Hi @Sam Swain<\/a>,<\/p>

You should be able to use this formula to accomplish this:<\/p>

=IF(HAS([Type of Project]@row, \"Consolidation\"), \"🝢\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Reduction\"), \"︾\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"New\"), \"○\", \"//www.santa-greenland.com/community/discussion/comment/\") + IF(HAS([Type of Project]@row, \"Termination\"), \"⨷\", \"//www.santa-greenland.com/community/discussion/comment/\")<\/p>

Example output:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps - if there are any issues etc. then just post! ☺️<\/span><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • 使用Countif to count Often and Very Often,

    however I can't get it to work the Often is also counting the Very Often: =COUNTIF([Tools & Resources (SE-2)]8:[Tools & Resources (SE-2)]12, HAS("Often", @cell), NOT(HAS("Very", @cell)))

  • Using SUMIFS Formula

    Needing help writing a SUMIFS formula! I have tried a few ways, but just get errors. =SUMIFS({budget}, {Type}, "AMR", {leadership}, "Truttmann")) I would like to have the sum of the budget if the type is AMR and the leadership is Truttmann. I have multiple types and leaderships that I would be using this for. Thanks!

  • IF cell CONTAINS one/two/three different values, return corresponding values

    Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria "Consolidation", "Reduction", "Termination", "New" Currently the formula works for single values with the following formula =IF([Type of Project]@row = "Consolidation", "",…