Forum Discussion

MikeTamoush's avatar
MikeTamoush
Qrew Commander
3 years ago

Any new ways to deal with costs and revenue tables?

I have a table of costs, and a table of revenue. At this point I have a summary table of months, with a 'key' in both revenue and costs tables, to look at everything on a monthly basis. With the i...
Relationships
reports and charts
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
Are these tables related?  Or what do they have in common?

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
MikeTamoush's avatar
MikeTamoush
Qrew Commander
3 years ago
Projects. I can relate them all to a project summary table and pull costs and revenue per project there. I thought about using that table, but what I can't figure out is - I will have multiple dates. The date the revenue is recorded and the date the cost is recorded. This may be in different months. So if I use the project summary table, I don't think I can look at Gross margin per month?

It feels to me like I will still need to utilize a monthly summary table to look at costs/revenue per month, and a project summary table to view per project.

In the same way, I feel like I would also need a yearly summary table to view per year.

But I also keep thinking that all these new tools were supposed to help me avoid these summary tables...

------------------------------
Mike Tamoush
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    But the fundamental problem is that if you have a mismatch on timing between revenue and cost recognition then you can't really know what the margin is in a particular month because for example a month maybe all cost and no revenue. And opposite for the next month. So really arguably the margin really only makes sense on a project to date basis.  Even if you go annually then you can still have the problem that there is a stub of a cost or a revenue recognition mismatch during that.  Of course the same problem can happen even on a project today basis if you have not got around to billing Revenue yet.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • MikeTamoush's avatar
      MikeTamoush
      Qrew Commander
      3 years ago
      Try telling that to my mgmt! :) LOL

      I don't disagree, but alas I have been tasked with looking at costs and revenue per month, even though as you said, if a project costs hit one month and the revenue the next, its a bit non nonsensical. But if we have 30 projects in a month they can at least see how the costs and revenue are hitting as a total for all projects.

      Do you see any other way besides the monthly summary table? I think I still need it, but at least the new dashboards have global filters so I can avoid the user focus trick for that.

      Let me know if you think of a new trick with summary formulas or something sneaky!

      ------------------------------
      Mike Tamoush
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        3 years ago

        Ok, "no problem"

        we will make a new fully automated table which merges the costs and the revenue records into one table and then use the relatively new feature of summary reports to do do formulas like Gross Margin $ and %.

        make a magic button or a nightly process to copy all the records of both tables into a consolidated table.  Ie purge the table and repopulate it. 


        The target fields will include related project so you can pull down project fields , date, and then separate fields for cost and revenues.

        This will allow you to make any kind of crazy summaries by any time period you want, and the new feature of summary formulas can be used to do math between the cost and revenue buckets.  This way you can also have totals based on just months or quarters for example so merging projects.  You can also do charts for trends over time bucketed as you like. Charts now also support summary formulas.



        then now that you have a single table to report from..



        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------

    • MikeTamoush's avatar
      MikeTamoush
      Qrew Commander
      3 years ago
      Insult to injury - if I roll up to a monthly summary table, I don't think I can then use the new dashboard global filters....except for the month. Any other filter is inherent to cost/revenue, which just got rolled up.....

      At least with the user focus table I can use the filters in the summary fields to look at the current user selections.....hmmm.

      I'm thinking my use case has not changed at all with the new features.

      ------------------------------
      Mike Tamoush
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        3 years ago
        Did you see my post above ?

        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------