Forum Discussion

IvanWeiss's avatar
IvanWeiss
Qrew Captain
5 years ago

Sales Goals Tables

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
------------------------------
Relationships
reports and charts

1 Reply

Sort By
  • BrianCafferelli's avatar
    BrianCafferelli
    Qrew Captain
    5 years ago
    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
    ------------------------------