Finance Archives - Excel Campus

Archive

Category Archives for "Finance"
17

Stacked Column Bar Chart Alternatives – Find the Missing Trends

Stacked bar or column charts are used a lot by the media and corporate world.  I believe they are popular because they display a lot of information in one chart, and are relatively easy to create in Excel.  However, I’m not a big fan.  I’m going to explain why I consider them to be “bad charts”, and present some alternatives.

Stacked Column Chart - BAD CHART

Bad Charts

I consider the stacked column chart above to be a “bad chart” because it doesn’t do a good job of displaying the trends in the data.  There are a total of six data series displayed here; the five regions plus the total.  But we are really only able to see trends in two of the six series.  We can see trends in the North America and Total (height of entire column) series because the baseline for these series is flat.  The baseline is basically the x-axis at $0.  This is very important when creating a chart that is displaying a trend.  Our eye is able to distinguish the baseline as the starting point for each column, and then compare the top point of each column moving from left to right.  This works great for the North America and Total series, but it is very difficult to see the trends for the other four regions.

Stacked Column Chart - Baseline Example

Uneven Baseline

Stacked Column Chart - Uneven Baseline Example

The image above shows the difficulty in quickly seeing the trend in sales for Asia because the baseline is uneven.  I emphasize “quickly” because we want our chart to clearly and easily communicate a story or trend.  It would probably be easier to just look at the actual numbers to see a trend versus trying to determine if the bars are increasing or decreasing in size over time.  And these bars for Asia get hidden when you consider all the other regions that are also stacked above and below it.  It’s an absolute mess! 🙂

Here is a line chart that shows the actual trend for Asia.  It’s much easier to quickly see that sales are declining over the last three quarters of the year.  This would probably draw attention to the performance of the region, and spark some action to be taken to correct the problem.

Asia Line Chart

When to Use Stacked Charts

Stacked charts can work if there are drastic changes in your data over time, and you want to only display the trend of one or possibly two series.  It’s best to highlight these series in a color that stands out, and add some text to describe the trend.  You also want to move the series to the bottom of the chart so it sits on the baseline, making it easy for the reader to see the trend.

Acceptable Stacked Column Chart

Alternative 2 below also makes use of the stacked chart by giving it a dynamic baseline to quickly analyze trends.

Solutions

Now that we have an understanding of when NOT to use stacked charts, we can start thinking about alternative methods that clearly and quickly tell the story.  The use of these will depend on what trends you find in your data, and how you want to convey the message.

Alternative #1: Panel Charts

Panel charts are a group of small charts organized together in a panel.  This is a good way to break out each region into its own chart.

Panel Chart - Alternative 1

Now we can start to see trends within each region.  These trends are not possible to see in the stacked chart because of the uneven baselines, and scale of the chart.  Sales in North America and Europe are much greater than Asia and Africa, so the trends get lost.  There are some important trends getting lost if you don’t look at them closely.  You can highlight those trends in a panel chart as I did above.  We can see that sales in Asia are declining, while sales in Africa have almost doubled over the year.  The dollars are small compared to total sales, but there might be great opportunities being missed if this is only analyzed in a stacked chart.

If you really want to impress your boss, you could add zoom buttons to each panel using the Zoom on Charts Macro (available for free download).  This is a great feature to add to an interactive dashboard.

Zoom on Panel Charts

Alternative #2: Dynamic Baseline Stacked Column or Bar Chart

You might like this solution if you want to keep the stacked chart, or don’t want to freak out your audience with something drastically different than they are used to seeing.  I call this one the dynamic baseline stack chart because it allows you to quickly choose the series to display at the bottom using a drop-down menu.  It is pretty easy to implement.  NO macros or VBA are required.  Just two different formulas and a drop-down form control.  The workbook that contains the form is available for free download below.  Instructions on how to create this chart are included in the file.

Dynamic Stacked Chart - Alternative 2 GIF

When the West region is at the top of the column stack it is difficult to see any trends.  But when the West is moved to the baseline (bottom) series, we see that there is a steady decline in the last half of the year.

There are also some correlating trends between South and West in the second half of the year.  This is easier to see when we move East to the top of the stack.  This might be another way to present your data depending on the message you want to convey to the readers.

Dynamic Baseline - West Example

Alternative #3: ???

I’d love to hear about some alternatives from you.  Make it as simple or complex as you like, there’s no right or wrong answer.  Just remember that the goal is to clearly and quickly tell the story to the reader.

Thanks!

Download

Dynamic Baseline Stacked Column Chart.xls (130.6 KB)

Additional Resources

If you’re looking to learn more about charting, I highly recommend the dashboard course from My Online Training Hub.  I have taken this course and it really helped me improve my charting skills.  Checkout my video review of the course to learn more about it.

79

Excel Formula to Calculate Commissions with Tiered Rate Structure

In this post I will explain how to calculate a dollar or percentage commission payout in one cell using the SUMPRODUCT function.  Calculating commissions on a tiered rate structure can be difficult because you are trying to determine the cumulative payout based on different rates at each tier, and the achievement amount might fall in between one of the tier ranges.

If your commission plan tiers are not cumulative, then you might want to checkout my article on how to calculate commissions with VLOOKUP.  This is a simpler calculation then the one presented below.

Tiered Rate Table

The following is an example of a tiered rate table for sales commissions.  The first column contains the tiered ranges of Quota Attainment and the second column contains the Payout % for each tier.  If the sales person (rep) achieves sales that are at the top end of each tier, then they will receive the full payout amount in the Total Payout column.  For example, if the rep sells 40% of their quota then they will receive 20% of their commission.  If they sell 60% of quota, they will receive 35%, and so on down the table.

Tiered Rate Structure Table - Basic

The difficult part is when the attainment amount falls in between the ranges.  What if the rep sells 50% of his/her quota?  The rep would receive 20% payout for the first 40% of quota, and an additional 7.5% payout for the last 10% of quota.  The last 10% of quota attainment is calculated by finding the payout rate at each tier.  So the total payout on 50% of quota would be 27.5%.

Typically we would have to calculate the payout at every tier and then sum the payout amounts to get the total amount.  Or we could use some complicated IF statement to determine the payout all in one formula.  But there is an easier way…

The Solution

You can download the sample workbook below to follow along.

The SUMPRODUCT function can be used to calculate the entire payout.  First we have to calculate the differential payout rate for each tier.  The differential rate is the difference between the payout rates at each tier.

The payout rate at each tier is the total percentage of payout in the tier, divided by the total percent of attainment possible in the tier.  This is basically the amount paid for each percentage increase in attainment in each tier.  In the image below the Payout Rate for the 0%-40% range is 0.50.  This means that for every 1% attained, the payout will be 0.50 of the 20% total payout.

Payout Rate =([tier attainment max] – [tier attainment min]) / ([payout % this tier] – [payout % previous tier])

Tiered Rate Structure Table - Payout Rate Formula

Rate Curve

The payout rate is also known as the rate curve.  The rate curve is displayed below, and you can think of the payout rate as the slope of the curve at each tier.

Tiered Rate Structure Table - Rate Curve

Differential Rate

The differential rate is calculated by taking the difference between the payout rate in the current tier and the payout rate in the previous tier.

Differential Rate = [payout rate Current tier] – [payout rate Previous tier]

Tiered Rate Structure Table - Differential Rate Formula

This is used in the cumulative calculation of the payout percentage.  As the attainment moves up into multiple tiers, the amount of attainment left in each tier is multiplied by the differential rate.  The sum of all these is the total payout.

SUMPRODUCT Explained

The SUMPRODUCT formula for Total Payout is:

=SUMPRODUCT( (Attainment > [Tier Min]) * (Attainment –  [Tier Min]) * [Differential Rate] )

Variables in brackets [] refer to entire column in rate table.

Tiered Rate Structure Table - SUMPRODUCT Formula

The following splits the SUMPRODUCT formula into multiple columns and rows for a clearer visual of how the formula is calculating the total payout.

  1. Attain > Tier Min: Returns a “1” if the attainment is greater than the attainment tier minimum.  If attainment is 90%, then the condition is true for the first 4 rows and a value of 1 is returned.
  2. Attain – Tier Min: Finds the difference between the total attainment and attainment tier minimum. This is necessary because we are multiplying it by the differential rate.  So in the first row we are taking the entire attainment of 90% and multiplying it by the diff rate of 50%.  Each subsequent row is taking the leftover attainment for its tier range, and multiplying it by the payout rate that is leftover for its tier (the diff rate).
  3. Diff Rate: The differential rate for each tier.
  4. Product: Column 1 * Column 2 * Column 3
  5. Sumproduct: Sum of the Product column.  The total payout on 90% attainment is 87.5%

Tiered Rate Structure Table - SUMPRODUCT Explained

SUMPRODUCT Visualization

The following is a visual example of the Product column plotted on the rate curve.  Sometimes it is easier to understand when you see it visually.  The SUMPRODUCT formula finds the total payout in each tier based on the remaining balance of attainment multiplied by the payout rate in that tier.  This is basically a continuation of the rate curve at each tier to the total attainment of 90%.  In the chart below you can see that the dark grey lines follow the rate curve at each tier and then continue on the same curve to the 90% attainment (green) line.

Tiered Rate Structure Table - Product Point Rate Curve

  1. For the first tier it is (90%-0%) * (50%-0%) = 45%.  50% is the payout rate for tier 1, and 90% is the total attainment.
  2. The second tier is (90%-40%) * (75%-50%)  = 12.5%.  The 90%-40% is the remaining balance of attainment for tier 2, which is 50%.  45% (tier 1) + 12.5% (tier 2) = 57.5%
  3. Tier 3 is (90%-60%) * (200%-75%) = 37.5%.  The sum of the first three tiers is 95% (45%+12.5%+37.5%), which is higher than the actual payout of 87.5%.
  4. Tier 4 is (90%-80%) * (125%-200%) = -7.5%.  The negative product is a result of the payout rate being less in tier 4 than tier 3.  Or, the slope of the rate curve is flatter in tier 4 than tier 3.  That brings the total payout back down to 87.5%.

Negative Differential

This negative differential rate in tier 4 is important to note.  Not only does it make for a confusing calculation, it also tells you that the rate of compensation is not as great in tier 4.  For each additional percentage point of attainment, the sales rep is compensated at a lower rate than tier 3.  This might mean that there is more emphasis for the rep to attain sales on their quota in tier 3.  And the monetary motivation is not as great for attainment in tier 4.

Negative differential can also mean that the rate curve is poorly designed.  If the goal is to achieve 100% attainment of quota, then it is probably best to increase the payout rate (rate curve slope) in each tier up to 100%.  This design would give the rep more motivation (higher payout rate) as he/she gets closer to achieving their goal of 100% attainment.

Apply Formula to a List

Now that we are able to calculate the payout in one cell using one SUMPRODUCT formula, we can apply the formula to a whole list of employees in a table.  See the Total Payout % column  on Sales Table tab of the example file.  This is the major benefit of this formula.  The entire calculation can be handled in one cell and it is easily transferable to other models.  There are no hard-coded variables in your formula, or ugly IF statements.

Download

Tiered Commission Rates using SUMPRODUCT.xls (101.4 KB)

Here is a file that uses whole number (units or dollars) for the tiers and payouts, instead of percentages.

Commission Plan SUMPRODUCT Units Dollars.xlsx (10.6 KB)

Here is an alternate solution submitted by Matthew Burgos using VLOOKUP instead of SUMPRODUCT.  He explains the formula in detail in the comment below.

Tiered Commission Rates Using VLOOKUP.xlsx (12.1 KB)

Still Confused? 🙂

I was too the first time I learned this technique.  It’s definitely complex.  I’d recommend reviewing it a few times with sample workbook and then try to implement it in your own model.

I have another article on how to calculate commissions with VLOOKUP that is an easier calculation for a simple commission plan.

Please leave a comment below with any questions/comments about this technique.