Dashboarding projected annual revenue

Brian Erdelyi

I am trying to dashboard my annual projected revenue. This will factor on the amount of the job and the expected completion date of the job. I know the scheduled job date and the expedted amountof time it takes to complete a job so it seems like this is doable but I cant seem to figure it out.

I have a sheet where I make clculations and store all of my dashboard data. Its called "dashboard data sheet". I use this to drive my dashboard wiidgets

On my "finance" sheet I have a $ column called "job amount", a date column called "job scheduled", and a drop down collumn called "job type".

I am trying to create a formula on my "dashboard data sheet" that will add 7 days to "job scheduled" if it is "job type" is "training", add 15 days if it is "job type" is "assessment", and add 30 days if "job type" is "development". Then, using that new date it should pull the amount from "job amount" and bucket it by year (i.e.,2019, 2020,2021, etc). I am trying to do this in one formula but i cant seem to figure this out. I have been trying for days! Any help is much appreciated.

Eric M Oliveira

Hi Brian,

Andrew is correct you may want to put the IF formula that performs the 7, 15, or 30 calculation on the source sheet in a new column that references the individual rows at the row-level utilizing the @row function. You may also want to do the same with the Date column.

Once you create these columns you can sum these values based on Year on your "dashboard data sheet".

Note: If you also desired to create a Date column on the source sheet depicting the new date you can achieve this utilizing this below formula provided by Andrew. In the above formula, he provided there was a typo that I had corrected below.

=[Job Scheduled]@row + [Job Duration]

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support

Andrew Stewart

Hi Brian,

I find it is usually easier to build things up bit by bit rather than try to everything in one formula.

Start by adding a text/number column called Job Duration. Set the formula to

=if([Job Type]@row="Training",7,if([Job Type]@row="Assessment",15,if([Job Type]@row="development",30,"Invalid Job Type!")))

and check the results are OK.

Create a date column, Job Expected Finish (for instance). Set the formula to:

=[Job Scheduled]@rachel1073@row + [Job Duration]@row

(or if you want to take non-working days into account you could use =WORKDAY([Job Scheduled]@row,[Job Duration]@row, holidays, and reduce the lag time in the first formula).

Then to find the total amount for a given year, have a cell with the formula

=SUMIFS([job amount]:[job amount], [Job Expected Finish ]:[Job Expected Finish ], YEAR(@cell) = 2019)

which adds all numeric entries in the job amount column where the job scheduled year is 2019.

Etc for other years.

If you don't want to see the extra columns, you can hide them.

If you want to make it less maintainable, you can skip the job duration column and change the formula of the Job Expected Finish column to:

=[Job Scheduled]@row + if([Job Type]@row="Training",7,if([Job Type]@row="Assessment",15,if([Job Type]@row="development",30,"Invalid Job Type!")))

but it makes it a lot harder to track down typos!

Once this is working, you can move to your dashboard sheet which presumably has a Year columns and recreate the formula with a reference to each of the two columns on the other sheet instead, eg

=SUMIFS({reference to job amount column}, {Reference to Job Expected Finish}, YEAR(@cell) = [Year]@row)

Regards,

Andrew

Eric M Oliveira

Hi Brian,

Andrew is correct you may want to put the IF formula that performs the 7, 15, or 30 calculation on the source sheet in a new column that references the individual rows at the row-level utilizing the @row function. You may also want to do the same with the Date column.

Once you create these columns you can sum these values based on Year on your "dashboard data sheet".

Note: If you also desired to create a Date column on the source sheet depicting the new date you can achieve this utilizing this below formula provided by Andrew. In the above formula, he provided there was a typo that I had corrected below.

=[Job Scheduled]@row + [Job Duration]

Have a wonderful day. Thank you for contacting Smartsheet Support.

Cheers,

Eric

Smartsheet Technical Support

Andrew Stewart

@Eric M Oliveira

Thanks for the correction. I've been experiencing issues typing in @row, if I am not careful one of the selected list of options for an @mention is selected, and if I backspace to remove it it appears to have gone, but when I click on Post Comment, it seems to come back....

Perhaps I should be composing responses in Notepad and then pasting them in....

Andrée Starå

@Andrew Stewart I'd recommend adding a comment in the threads below and hopefully the Community Team can improve/fix it.

https://community.smartsheet.com/discussion/64740/faq-how-do-i-notify-someone-else-in-one-of-my-comments

https://community.smartsheet.com/discussion/64751/faq-how-do-i-use-rich-text-when-i-m-posting-a-question-discussion-or-comment

Have a fantastic weekend!

Best,

Andrée