# Discussions

View Only

## Sales Goals Tables

• #### 1.  Sales Goals Tables

Posted 11-27-2019 13:19
I am trying to track future sales revenue forecasting for reporting and planning purposes.

I have a table for Team Members (basically an Employees table)
I have a table for Teams (Employees are associated with a team and generate revenue as a team)
I have a table for Quarterly Sales Goals

One team has many team members
One team has many quarterly sales goals

On the quarterly sales goals I have two fields, Date and currency.  The currency is of course the revenue.  I want the date to correspond with a quarter/year not a specific date.  How do i do that?  I can foresee users entering data inadvertently in the same quarter if they can pick any date.

For example I want to be able to see the following for next year:

Team Name       Date                      Revenue
Team A              Quarter 1 2020    3 Million
Team A              Quarter 2 2020    2 Million
Team A              Quarter 3 2020     4 Million
Team A              Quarter 4 2020     3 Million
Team B              Quarter 1 2020      6 Million
Team B               Quarter 2 2020     3 Million
etc.....

I do not want the various teams to see each others goals but I want to be able to see them roll up together in a year.

Separately we have an Invocies table and ultimately I will pull reporting comparing the amount invoiced against the goals etc.

------------------------------
Ivan Weiss
------------------------------

• #### 2.  RE: Sales Goals Tables

Posted 12-03-2019 15:55
Hi Ivan, have you considered having the user just pick the quarter number from a dropdown, type in the year, and then use formulas to generate the quarter name, and the on which that quarter begins? That would make sure you have valid data and it would make it easier for folks to add new sales goals. So you could use these fields:

Quarter Number - Text Multiple Choice
Year - Numeric
Quarter Name - Formula Text
Quarter Start Date - Formula Date

For the Quarter Name, you can use this formula:
`[Quarter Number] & " " & [Year]`

and for the Quarter Start Date, you can use this formula:
```ToDate(
If([Quarter Number] = "Quarter 1", "January 1,",
[Quarter Number] = "Quarter 2", "April 1,",
[Quarter Number] = "Quarter 3", "July 1,",
[Quarter Number] = "Quarter 4", "October 1,") &
[Year])```

------------------------------
Brian Cafferelli
------------------------------