Discussions

Expand all | Collapse all

Margins on a sales report Totaling

  • 1.  Margins on a sales report Totaling

    Posted 11 days ago
    I have a table report that shows each of my projects with several columns:

    Project Name | Total Sell | Total Cost | Total Profit | Margin

    Each project might have several rows (due to the original contract + change orders)
    I have the table to sort and group by project name and it subtotals each project so I can get the total current sell price, total cost, and total profit aggregated for the project.  

    This does not work for Margin.  By default it was summing the margin column, of course wrong.  Let's say I have 30 rows on this project and they are all at a 20% margin for easy math.  The total would result in 60% margin because they are adding, incorrect.

    I tried switching it to average but that is also incorrect.  Let's say the revenue is disproportionate and the margins are not even on all of the rows.  It would take the total margin and divide by 3 (in the prior example).  That is not correct as if one row is 3,000,000 at 20% and the next two rows are much smaller values but at lets say 30% the average is not the (3) totaled and divided by 3.  It is the total profit / total sell.

    Is there a way to get the total margin the table self generates to run the formula of the total profit / the total sell?  That is the only way to get an accurate value in there.

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


  • 2.  RE: Margins on a sales report Totaling

    Posted 11 days ago
    There is not currently a way to do math between totals on a summary report or on totals on a table report.  You would need to get those totals accumulated up to a parent record and then do the math there.  The issue of course is that you need to plan out what totals you need to accumulate and set up the Parent table records. 

    For example if you just wanted monthly totals with Margins, you would need to load up a table of months where the Key field is the first of each month and then make a relationship down to the detail tables and roll up the total sales and total costs up to that record, then do proper margin calculations up there.

    mnark

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Margins on a sales report Totaling

    Posted 11 days ago
    I keep my changes in a Change Log table and put a table report in the Project form to show the margins on change orders. I then show the original margin , current projected margin (w/changes) and the actual margin. 1 line per project and in a table not a summary report with the average at the bottom.  I made sure to put the financial details on one of the first few tabs to keep it simple to find for management when they want to see the details.
    This issue was a headache for me as well.

    ------------------------------
    Jason Johnson
    ------------------------------



  • 4.  RE: Margins on a sales report Totaling

    Posted 11 days ago
    but that does not solve the issue of getting a mathematically correct margin % as a total.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Margins on a sales report Totaling

    Posted 11 days ago
    I would never total my margins. Right now my margin total is 572% but my margin average is 27% which is correct.  I now know that our department is hitting the target but am also highlighting any under performing projects whose margin is not meeting goals.

    ------------------------------
    Jason Johnson
    ------------------------------



  • 6.  RE: Margins on a sales report Totaling

    Posted 11 days ago
    You need an overall project table related one to many to a project finance table. Then you can summarize the financials in your project table and get the overall margin using a formula field to divide the summary of profit into the summary of sell.

    Take a look at this thread to learn how to set it up if you aren't familiar with relationships and summary fields:
    Relationships and Summary Fields

    ------------------------------
    Adam Keever
    ------------------------------



  • 7.  RE: Margins on a sales report Totaling

    Posted 11 days ago
    If you check the math you will find that your average margin % is incorrect.

    If you have job with a value of $ 1 million and the margin is 20% GP, and you have another job where the margin is 80% and the value of the job is $1,000, then the average margin will be 50%.  But in fact the correct Weighted average margin for both jobs combined will be about 20.1%.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 8.  RE: Margins on a sales report Totaling

    Posted 10 days ago
    Edited by Adam Keever 10 days ago
    Jason, can you show us what this looks like so we can get a better idea of how you are managing it? I think I follow what you are doing, but seeing would be helpful.

    ------------------------------
    Adam Keever
    ------------------------------