Capturing Cost Savings by the month

Have any of you created a Smartsheet that would show the savings for a project by the month? In other words, if I saved $1200 annually and the project ended in say March, then the savings would go $120 per month, Mar 2025-Feb 2026.

I have done it on Excel sheets manually, but I think there should be a way to do it with Smartsheet automatically.

Thanks so much for your help!

Freda

Find more posts tagged with

Accepted answers

Paul Newcome

Ok. So to get the chart based on "Current Year" and showing a single column for every month Jan - Dec and adding up the appropriate amounts based on start and end dates, here is what we need…

In a separate sheet, put the month labels in the Primary Column.

January

February

March

so on and so forth

The next column will be a text/number column called "Month Number", and you will manually enter the month numbers.

so on and so forth

Then you will have a text/number column formatted to show currency and using this column formula with cross sheet references:

=SUMIFS({Monthly Savings}, {End Date}, @cell >= DATE(YEAR(TODAY(), [Month Number]@row, 1), {Start Date}, @cell <= IFERROR(DATE(YEAR(TODAY()), [Month Number]@row + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1)

You are also going to need to use one of the various solutions for keeping the TODAY() function up to date. My personal preference is to insert a dedicated date type column and have a record a date automation running daily and recording the date in this dedicated column.

All comments

FTesseneer

Ok, $100 a month! Forgive my typing.

Paul Newcome

How do you want to see it, and how do you plan to use it?

FTesseneer

I want to use it as a dashboard. i would like to show all of the savings by the month instead of annually. That way the savings roll over into the next year. I have tracked it before in an Excel spreadsheet, but that was all manual. I would love to have it so that it pulls from my intake sheet and then splits it up by the month somehow.

I was thinking maybe a formula in my intake sheet and then that pulls into a dashboard?

Paul Newcome

Are you able to mock up a quick visual of how you want it displayed and how your data currently exists? There are a number of different ways to accomplish something like this depending on your needs with some being much more complex than others.

FTesseneer

This is what I am currently displaying in a report. I haven't created a dashboard for it yet.

But I would like to show something like below

This is an old graph I was working with just showing the # of improvements per month. But I would love to show the total savings per month.

This is one I was playing with to show by the department. But this is the total annual savings by department. I would like to show the total dollar value of savings by the month.

Thank you so much for your help!

I realize that I will probably have to take all the filters and grouping off of my report to be able to use it to pull from.

Freda

Paul Newcome

So are you looking for a generalized month by month like the first chart, or more like a stacked column that has the departments as columns and each month is a differnet color on each column?

FTesseneer

No, I don't really need it by department. I just would like a generalized month by month. I asked Chat GPT but it said I would have to have a row for each month. That would be a lot on this one sheet. Is there a better solution? I have added a formula to my sheet to divide the annual savings by 12 so I have the month savings amount. And I have the end date for the savings with a formula.

Paul Newcome

Ok. Everything so far we can work with.

Are you looking for a rolling 12 months, current calendar year, everythig for each motnh regardless of year, or somethign entirely different?

FTesseneer

Rolling 12 months. So, for instance, if the savings started 2/25 it will be until 1/26. My end date is in 12/06/24 format. For example i would like to show the total savings for 1/26, 2/26, and so on. Thanks so much!

Paul Newcome

My apologies. I understand it would be a monthly savings spread out between start and end dates, but I mean for the chart itself. Will the chart be rolling 12 months updating daily (to change months shown based on "today's" date), or would it be a static Jan - Dec? If static, is it only January of "current year" or all Januaries of all years, or somethign else?

FTesseneer

Oh yes, I am sorry! There may be savings that are added to that month. But I would like to keep it simple and just have the month. It would only be for the current year; however, I would like to continue using it next year if it works out.

Sometimes we have projects that may only last a month or two, but they would result in a monthly savings. So it needs to update daily.

Paul Newcome

Ok. So to get the chart based on "Current Year" and showing a single column for every month Jan - Dec and adding up the appropriate amounts based on start and end dates, here is what we need…

In a separate sheet, put the month labels in the Primary Column.

January

February

March

so on and so forth

The next column will be a text/number column called "Month Number", and you will manually enter the month numbers.

so on and so forth

Then you will have a text/number column formatted to show currency and using this column formula with cross sheet references:

FTesseneer

Thank you! I will give this a try.

Paul Newcome

Happy to help. 👍️

FTesseneer

Ok I am doing something wrong with the formula.

So the Monthly savings & End date are in my reference sheet. I am assuming the start date is the end date of the project in my reference sheet. Would that be correct?

=SUMIFS({Process Improvement Intake Sheet-2 Monthly Savings}, {PI Intake Sheet-2 Savings End Date}@cell >=DATE(YEAR(TODAY(),[[MonthNumber]@row ,1),{Process Improvement Intake Sheet-2 Start Date}@cell <=IFERROR(DATE(YEAR(TODAY()),[MonthNumber]@row +1,1),DATE(YEAR(TODAY())+1,1,1))-1)

Comes up as unparseable. Can you tell what I am

Paul Newcome

The start date would be whichever column houses the project's start date.

There are a couple of minor syntax errors that I can see right off. What does this do for you?

=SUMIFS({Process Improvement Intake Sheet-2 Monthly Savings}, {PI Intake Sheet-2 Savings End Date}, @cell >=DATE(YEAR(TODAY(), [MonthNumber]@row, 1), {Process Improvement Intake Sheet-2 Start Date}, @cell <= IFERROR(DATE(YEAR(TODAY()),[MonthNumber]@row + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1)

FTesseneer

For some reason mine is taking the brackets off MonthNumber so it is giving me an invalid data error. Any ideas?

=SUMIFS({Process Improvement Intake Sheet-2 Monthly Savings}, {PI Intake Sheet-2 Savings End Date}, @cell >= DATE(YEAR(TODAY(), [MonthNumber]@row , 1), {Process Improvement Intake Sheet-2 Start Date}, @cell <= IFERROR(DATE(YEAR(TODAY()), [MonthNumber]@row + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1))

This is what I put in, but when I press enter it is removing the column name brackets

Paul Newcome

Can you provide a screenshot of the sheet to include column headers?

FTesseneer

This is the new sheet

This is the intake sheet I am pulling from. So the start of the savings would be the end date of the project. Unless it would be better to use the month and year column.

I really appreciate you spending this extra time with me.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.