Creating a sales forecast and dashboard

[dparibello (David Paribello)]

I need to build a dashboard that shows forecasted vs actual vs projected sales. The dashboard should pull data from a table that is created by a combination of manually entered data and automations.

I need to build this out for the next 5 years broken down by quarters. The data needs to be updated according to changing market dynamics which ultimately change the actual and projected close date(s). That is, if the projected close date changes then it should automatically be reflected in the (new) corresponding quarter.

Here is the data I need to use to build the dashboard:

-Forecasted number of units sold (provided by leadership, manually entered)
-Actual units sold (based on the close date of a sale and is then automatically updated)
-Projected units sold (based on a future date the sales rep chooses and is then automatically updated)

It seems as though it should be a simple task, but it’s proving to be rather difficult. Any help is greatly appreciated.

[CharlotteK (Charlotte Kirkham)]

Hey David!

Sorry to hear you’re running into difficulty setting this up! It would be super helpful to see a screenshot of what you have so far or to learn a little more about what you’ve tried that hasn’t worked out - just so we don’t make any suggestions that won’t work for what you need.

To start, it sounds like you’ll want to have two Formula Columns in a board that calculate the actual units sold and projected units sold, and then add these (along with the Numbers Column you use for the forecasted number of units sold) as Number Widgets or Chart Widgets into your Dashboard. Then, you can apply a date filter to the widget to reflect just one quarter or one year. Then, duplicate these widgets as many quarters or years you want to see and then filter them to show just that quarter or year again.

Then, if you are doing 3 widgets for each of the 5 years, you’ll have a total of 15 widgets in the Dashboard, each filtered to show the relevant data.

Is this similar to what you were thinking? Let us know if not!

[dparibello (David Paribello)]

Here are some snapshots of what’s being done:

This is what it needs to look like. Unfortunately, the way it’s been done isn’t working and it doesn’t update if you go back and edit the initial data point(s) - it doesn’t update because the accounts aren’t connecting or reconnecting properly.

[ZakiImtiaz (Zaki | readylogic.co)]

Hi @dparibello have you tried using mirror columns from the boards and then using those columns to reflect that in the dashboard?
Apparently the way you’ve set it up is correct. The chart gets updated dynamically if the data points change.

Can you please elaborate a bit by making a screen recording or something?

[dparibello (David Paribello)]

I ended up abandoning this initial approach and went a different direction with a 3rd party app dashboard. This way is much easier to implement.

I built out my forecast via ‘dummy’ deals in the same board then made a simple status column along with a few automations that identify each deal as forecast, actual, projected. Based on a date column, I’m running a formula to determine the sales year and quarter. Next I cast those results to a text column and then am able to build this dashboard.

I’m still trying to work though one minor kink with General Caster now - it was casting my formula column to a text column and then it just stopped. My formulas are correct - I’ve test them independently and I’ve also made sure to create the formula in General Caster using the corrected syntax. It worked for a simple “0, 1” test and no longer works with my full fledged formula. I contacted their support and they indicated haven’t gone over the budgeted automations for my plan. It indicates the automation was triggered and ran successfully, but the results don’t get cast to the column. I’ve tried contacting them multiple times for help and they’ve stopped responding.

Have any of you used this app before? Any tips?

Here’s the integration…

Here’s the formula I’m trying to cast:

IF(
FORMAT_DATE({item’s Projected Close Date}, “YYYY”) > “1999”,
IF(
{item’s group} = “Closed Won”,
CONCATENATE(“Q”, FORMAT_DATE({item’s *Actual Close Date}, “Q”), “-”, FORMAT_DATE({item’s *Actual Close Date}, “YYYY”)),
CONCATENATE(“Q”, FORMAT_DATE({item’s Projected Close Date}, “Q”), “-”, FORMAT_DATE({item’s Projected Close Date}, “YYYY”))
),
“Undated”
)

Again, the above formula provides me with the expected results/outcomes for all my deals when I run it as a simple formula column so I’m stumped on why it’s not running in the integration.