#### Archive

*Category Archives for "Modeling"*

58 ## Drop-down List Arrow Always Visible for Data Validation

**Bottom Line:** The drop-down arrow (icon) for a data validation list disappears when another cell is selected. This technique will make the drop-down arrow permanently visible on the worksheet, even if the user selects a different cell.

**Skill Level:** Intermediate

88 ## How to Create Dependent Cascading Drop-down Lists

**Bottom line: **Learn how to create cascading or dependent drop-down lists (also known as cascading validation) in Excel. This technique does NOT require named ranges. If you don’t mind using named ranges then there are a few links at the bottom of the page with solutions that will be easier to implement.

**Skill level: **Intermediate

**Functions used: **OFFSET, MATCH, COUNTA, COUNTIF, INDIRECT Continue reading

11 ## SUMIFS, COUNTIFS, AVERAGEIFS Filter & Analyzer Add-in

This add-in will help you filter and analyze results for long formulas using SumIfs, CountIfs, or AverageIfs functions, and it’s FREE!

88 ## How to Structure Source Data for a Pivot Table & Unpivot

This post will show you the proper way to setup or organize your source data for a pivot table.

20 ## Quarter Sum Formulas Add-in

##### This post provides a solution for quickly creating formulas to sum monthly data into quarters and full year totals using a free add-in.

### The Problem

### The Solution

###

### And it Gets FASTER!

### Benefits

### Download

### Related Help Articles

### Instruction Guide

#### Report Structure Requirements

#### Creating the Formulas

### What do you think?

When using the SUM function to sum monthly data into quarterly totals, you can NOT create the formula for the 1st quarter and then copy/drag the formula to the right.

The cell references will only move one column to the right instead of three columns. If we copy the formula above for Q1 to the right, the resulting formula will give us the total for Feb-Apr. The correct formula should be a total for Apr-Jun.

The Quarter Formulas Add-in creates the correct formulas for the quarters and full year in just a few clicks. And it’s **FREE!**

You can add the Quarter Formulas button to the Quick Access Toolbar to launch it with a** keyboard shortcut**. Then you will be creating all the formulas in **under 2 seconds!**

These articles will explain how to setup the Quick Access Toolbar (QAT) and use the QAT for keyboard shortcuts.

- Four quarter and full year SUM formulas created in 2 seconds.
- No typing or complex formulas needed.
- Creates easy-to-read SUM formulas.
- Automatically determines 1st month cell when Q1 cell is selected.

Quarter_Sum_Formulas_Add-in.zip (758.5 KB)

The add-in file and installation guide are included in the zip file. The add-in is compatible with Excel 2007, 2010, 2013. Please let me know if you are interested in a version compatible with Excel 2003.

Subscribe to our email newsletter below to stay updated with changes.

- How to Install an Add-in
- How to Setup the Quick Access Toolbar (QAT)
- How to Use the QAT for Keyboard Shortcuts

This add-in will save you lots of time when creating quarter and full year formulas, and it is **very easy to use**.

See the guide on how to install an add-in (included with download). After the add-in is installed, you will see the Formula Tools menu on the Add-ins tab of the ribbon.

The Formula Tools button is a drop-down menu that includes a button for the Quarter Formulas function. More functions will be added to this menu in the future. See below for instructions on how to setup a keyboard shortcut to launch the function.

The Quarter Formulas add-in requires **all the month data be in 12 consecutive columns**. The four quarter and year total formulas will be created in five consecutive columns. You will choose the starting cell (Q1) that the formulas will be created in. The created formulas do NOT have to be next to the monthly data.

- Click the Quarter Formulas Button.
- Select the 1st Quarter cell where you want the formulas to be created and click OK.
*– Formulas for Q2-Q4 and Full Year sum will be created to the right of this cell.*

– The selection defaults to the active (selected) cell in the worksheet. Selecting the Q1 cell prior to clicking the Quarter Formulas button will**save you a step**. - Select the cell that contains month 1 data and click OK.
*– This is the 1st month in the 1st quarter (January if you are on a calendar year).*

– The selection defaults to 12 cells to the left of the Q1 cell selected in the previous step. If the quarterly total cells are directly to the right of the monthly data then you will not have to select the month 1 cell. This will**save you another step**. - The SUM formulas will be created in the Q1 cell and the four cells to the right of it.
*– Copy the formulas down to fill all the rows in your report.*

Please leave a comment with any questions or suggestions.

18 ## Stacked Column Bar Chart Alternatives – Find the Missing Trends

### Bad Charts

### Uneven Baseline

### When to Use Stacked Charts

### Solutions

### Alternative #1: Panel Charts

### Alternative #2: Dynamic Baseline Stacked Column or Bar Chart

### Alternative #3: ???

### Download

### Additional Resources

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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!

Dynamic Baseline Stacked Column Chart.xls (130.6 KB)

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.

81 ## Excel Formula to Calculate Commissions with Tiered Rate Structure

### Tiered Rate Table

### The Solution

### Rate Curve

### Differential Rate

### SUMPRODUCT Explained

### SUMPRODUCT Visualization

### Negative Differential

### Apply Formula to a List

### Download

### Still Confused? 🙂

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.

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.

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…

*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])

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.

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]

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.

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.*

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

**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.**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).**Diff Rate:**The differential rate for each tier.**Product:**Column 1 * Column 2 * Column 3**Sumproduct:**Sum of the Product column. The total payout on 90% attainment is 87.5%

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.**

- 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.
- 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%
- 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%**. - 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%.

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.

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.

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)

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.