Plotting cost over time

Hi-

I am trying to either graphically or with subtotals display cost over time as aggregated preferably by month, but also want to consider by week.  

I am easily able to plot cost for items that begin or end in a calendar month, but not for items that span months.  

I am pretty sure each cost would need to be broken down into a cost/day based on its duration, but am having trouble beyond that to calculate and display over time.  

See screenshot for basic setup of what we are looking at.  

Intent is to show clients and predict internally projected billings/revenue over time for each calendar month.  

Many thanks in advance for your suggestions.  

Screen Shot 2018-06-14 at 9.56.42 AM.png

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The best way I can figure for you would to be using some additional columns. 2 date columns and a "Prorate" column (see image below). In the date columns you would enter the 1st of the month and the last of the month. In the prorate column enter:

    =IFERROR(IF(PRORATE([email protected], [Start Date]@row, [End Date]@row, [Billing Period 1 Start]@row, [Billing Period 1 End]@row) = 0, "-", PRORATE([email protected], [Start Date]@row, [End Date]@row, [Billing Period 1 Start]@row, [Billing Period 1 End]@row)), "")

    What this does is calculate the cost within that calendar month. If the cost was $0.00 it is replaced with - . (Looks a little cleaner in my opinion).

    The IFERROR portion will leave the cell blank if you don't have all 4 dates in the corresponding row (Header Rows). This allows you to drag-fill on down the column and avoid getting an "Invalid Data Type" error displayed in the cell.

    From there you would just change the column titles to reflect whatever your column names are.

    If you wanted the sheet to look cleaner you could hide the billing period start and end date columns and change the column names to the corresponding month.

    Untitled.png

    thinkspi.com

  • Hi Paul,

    I copy/pasted the following into my cost loaded construction schedule to calculate cost on a month to month basis...and then I''ll create a bell curve of the revenue spending per month for the job...but it didn't work? I just get the error "#INCORRECT ARGUMENT SET". I only changed the column label names to match mine but I get this error? Can you help?

    =IFERROR(IF(PRORATE(Budget5, [Start Date]5, [End Date]5, [Month 1 Begin]5, [Month 1 End]5 = 0, "-", PRORATE(Budget5, [Start Date]5, [End Date]5, [Month 1 Begin]5, [Month 1 End]5, ""))))


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Kurt Miller Sr.

    Hi Kurt,

    At a glance, you have some parenthesis errors.

    I've updated the formula to and changed the row numbers to @row instead, so you don't need to think about row numbers.

    Try something like this.

    =IFERROR(IF(PRORATE([email protected], [Start Date]@row, [End Date]@row, [Month 1 Begin]@row, [Month 1 End]@row) = 0, "-", PRORATE([email protected], [Start Date]@row, [End Date]@row, [Month 1 Begin]@row, [Month 1 End]@row)), "")

    Did that work?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • That did the trick!

    Thank you

    Kurt

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Kind of curious as to why I didn't get a notification for this...


    Either way, it looks like you got it working.


    Thanks @Andrée Starå for stepping in.

    thinkspi.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Kurt Miller Sr.

    Excellent!

    Happy to help!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Paul Newcome Sure thing!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.