Plotting cost over time

Mike Liggett · 06/14/18

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

Paul Newcome · 06/14/18

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.

Kurt Miller Sr. · 04/16/20

Hi Paul,

我下面的复制/粘贴到成本加载有限公司nstruction 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å · 04/16/20

@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 bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Kurt Miller Sr. · 04/16/20

That did the trick!

Thank you

Kurt

Paul Newcome · 04/16/20

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.

Andrée Starå · 04/16/20

@Kurt Miller Sr.

Excellent!

Happy to help!

Andrée Starå · 04/16/20

@Paul NewcomeSure thing!

Plotting cost over time

Comments

Help Article Resources

Categories