Skip to main content
Question

Creating Sales Goals Comparison to Actual Sales (Invoices Submitted)

  • January 4, 2020
  • 0 replies
  • 54 views
Forum|alt.badge.img+11
I have a project management application that has a CRM component built into it.  My sales team enters their annual sales goal on a table called Sales Goals

The sales team enters their data on the table and I can view it as follows.  Please note that we have two types of sales we track (design revenue and build revenue).  We are a design and construction firm.

Now I have another table called Invoices.  I want to build a meter chart on the home page of each user showing their percentage towards goal. I also want to be able to view the bottom report with a comparison to show what has currently been invoiced and a percentage towards goal.  Again per quarter and annual separating the two types.

I am struggling with how to relate this data....  My first instinct was I need to summarize the data out of the Invoices table.  But I need that sum sorted by type and sales person.  I would need to pull each out quarterly and could sum it up for the annual via formula.

My other thought was if I need a snapshot table of some sort, but honestly I am not sure if I would need that as the data is always live.  It is not like it is a cumulative number like a pipeline tracker or something like that.

Any advice on how to structure something like this?  I have to imagine someone has built this type of functionality into a CRM tool.

Thanks!



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

QuickBaseJunkie
Forum|alt.badge.img+15
Hi Ivan!

Without having access to the data myself, this is my initial thinking...

Relate Invoices to these Sales goals (or this could be done with a sync table if you don't want to relate it directly). I assume the salesperson is already on (or could be added to the invoice). The Sales goal would be the parent with Invoices as the child. I assume there is only one salesperson per invoice.

You can then create your summary fields by quarter, type, and annual... I assume the information you'd need to filter these summaries is already included on the invoice (ie quarter & type). Separating it by salesperson will happen automatically as part of the relationship.

Then it's just a matter of adding formula fields to the Sales goal table to give you the %.

In my head, I believe this will do the trick for you. Let me know if it helps 😀👍

------------------------------
Sharon Faust (QuickBaseJunkie.com)
Founder, Quick Base Junkie
https://quickbasejunkie.com
------------------------------
One - Sale Goals to Many- - Invoices is the relationship and make the Sale Person the proxy lookup field in invoices. Change the default record picker in Sales Goals to Team Member as the only field showing so that way people are only selecting names. I would suggest having a formula in the Invoices table to assign  the quarter based on invoice date if you don't want people to pick the quarter themselves. For example:
If(Month([Invoice Date])>0 and Month([Invoice Date])<4,1,
If(Month([Invoice Date])>3 and Month([Invoice Date])<7,2,
If(Month([Invoice Date])>6 and Month([Invoice Date])<10,3,
4)))
Now with the relationship setup you can create the summary fields. Yes you could use the "is during" Criteria when making the summary instead. Make sure when you design this that you are thinking of what it will look like at the end of the year going into the next year with new sales goals.  Don't forget the field to select in Invoices if the project is design or build.


------------------------------
jason johnson
------------------------------
    Terms & ConditionsAccessibility statement