H2Etags

How to Use Tags in Excel

Did you know that you can group numbers in Excel using tags? By just listing all the categories an item should belong to, you can make it easier to group them. In this post, I’ll show you how you can use tags in Excel to efficiently summarize different categories.

Creating tags

Suppose you wanted to list all the possible streaming services you might subscribe to. You might have a list that looks something like this:

List of streaming services.

This is fine if you want to compare them or even tally them all up. But what if you wanted to look at different scenarios, such as what if you select some of these services, but not all of them? This is where tags can be really helpful. Let’s say I want to create the following categories:

  • Basic
  • Kids
  • Tier 1
  • Tier 2
  • Tier 3

Each category will have a different mix of services. Here’s how I can use tags to make that happen. I’ll create another column next to the price where I specify all the categories a service will fall under:

Streaming services grouped by tags.

In the above example, Netflix is included in every package but HBO Max is only included in Tier 3. Next, what I’m going to do is create columns for each one of these tags, such as follows:

Streaming services grouped by tags.

Without using tags, you might be tempted to put a checkmark to determine which service belongs in which category. But that’s not necessary here. Instead, I’m going to use a function to determine whether to pull in the price or not.

Using a formula to determine if a tag is found

The key to making this work is the SEARCH function. This will look within the tag values to see if there is a match. If there is, then the price will be populated within the corresponding category. To check if the ‘basic’ keyword is found within the tags related to Netflix (assume this is cell C2), this is how that formula would look:

=SEARCH(“basic”,C2,1)

This will return a value of 1, indicating that the term is found at the very start of the string. If you use the function to look for the word ‘kids’ then it would return a value of 8 as that comes after ‘basic in my example.’ Of key importance here is that there is a number. If there isn’t a number and instead there is an error, that means that the tag wasn’t found. I will adjust the formula as follows to check if there is a number:

=ISNUMBER(SEARCH(“basic”,C2,1))

This will return a value of either TRUE or FALSE. But the formula needs to go further than just identifying if the tag was found. It needs to pull in the corresponding value. To do this, I’ll need an IF statement to extract the value from column B:

=IF(ISNUMBER(SEARCH(“basic”,C2,1)),B2,0)

By freezing the formulas and copying this across the other categories, this formula will now allow me to pull in the amounts correctly based on the tags:

Summary of streaming services based on tags.

But let’s say you don’t even want to do this, you just want to quickly group the totals without these extra columns. You can also do that with the help of tags.

Summarizing the totals by category

You don’t need to create a column for each group if you don’t want to. You summarize the total in just an array formula. Simply use the formula referenced earlier and include it within a SUM function, while referencing the entire range:

=SUM(IF(ISNUMBER(SEARCH(“basic”,C2:C6,1)),B2:B6,0))

This is the same logic as before, except this time the values will be totaled together. On older versions of Excel, you may need to use CTRL+SHIFT+ENTER after entering this formula for it to correctly compute as an array. But if you’re using a newer version, you don’t need to. If you copy the formula to the other categories, you’ll be able to sum the values by without the need for additional columns:

Summary of streaming services based on tags.

If you liked this post on Using Tags in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

h2eVariances

How to Calculate Variances in Excel

In this post, I’m going to show you how you can easily calculate variances in Excel. I will also go over how to group variances and how using pivot tables, charts, and conditional formatting can help save you time in reviewing them.

For this example, I’m going to use data from the S&P 500 as stock prices frequently fluctuate. To start, I’m going to download the data from the past year. I’m going to remove everything except the closing values just to keep this example simple:

Download of the S&P 500 closing prices over the past 12 months.

Calculating the variances

The calculate the variance in these data points, what I need to do is to take the current closing price, and subtract the previous day’s closing price from it. That will tell me how much of a move there was that day. On June 7, for instance, the S&P 500 fell from 4,229.89 on June 4 (the previous trading day) to 4,226.52. If I minus the current day’s close from the previous, I get a value of -3.37.

But we can dig a lot deeper than just looking at the difference in price. Let’s also create a field to indicate whether these variances are positive or negative. To do that, I’ll create another column called ‘Direction.’ For this calculation, I will take a look at the value in column C (where my variance is) and create a simple IF formula:

=IF(C2>0,”Positive”,”Negative”)

Here’s what my sheet looks like now:

Table of variances showing positive and negative values.

Although you can determine whether it is positive or negative from the variance field, by creating another column you can quickly filter if you want to look at all the negative or positive values. Another column I’ll insert here is for the percentage change.

To do this, what I will do is take the variance amount and divide it by the previous day’s closing price. This will tell me how much the price has moved as a percentage of what its value was the day before — which is much more useful than just looking at the raw value. After inserting the column, I have the total variance, variance %, and which direction it went in:

Variances by raw amount, percentage, and positive or negative indicator.

I changed the variance % field to show percentages and I added a few decimal places since the percentages are fairly small. To add decimal places, go to the Numbers group on the Home tab and click the following button on the left:

Button to increase or decrease the number of decimal places.

The one on the left will add decimal places while the one on the right will remove them.

However, what if you don’t care about positives or negatives and are just interested in the absolute value of the changes? I’ll cover that next.

Calculating changes in absolute value

With absolute value, you remove the positive or negative indicator. And to calculate a variance this way, you just need to add a formula to the calculation in the variance field. Rather than this:

=B2-B3

You would enter this:

=ABS(B2-B3)

Now, my variances update and I no longer have a use for the Direction field since all the values will be positive:

Variance table when only calculating absolute values.

Alternatively, you could also just create another column specifically for the change in absolute value.

Now that the variances have been created, what you may want to do next is to group them.

Grouping variances

Why would you want to group variances? The big advantage in doing so is they can make it easier to analyze a large data set by showing you where the bulk of the variances are.

Rather than creating a bunch of IF statements, what I’ll do is create a table to show where the variances belong:

Table grouping the variances.

I’ve created a named range called VarianceTable for this. And now, all I need to is use a VLOOKUP formula to find which category a variance belongs in. Since I’m not using an exact match, I will set the last argument in the function to ‘TRUE’ :

=VLOOKUP(D2,VarianceTable,2,TRUE)

Now I have a category field instead of the Direction:

Table with variances grouped by category.

But this doesn’t tell me a whole lot. I could filter by the category. However, a better approach is to create a quick pivot table that shows me a summary of where the values fall:

A pivot table showing the count of the different variances groups.

And from that, I can quickly display these variances on a chart:

A chart showing variances by category.

Another way you can help identify extreme values in variances is by using conditional formatting. To apply conditional formatting, select either the variance column or the variance % column and under the Conditional Formatting button on the Home tab, you can select either Data Bars or Color Scales. I prefer using Data Bars since there are fewer colors:

Selecting data bars under the conditional formatting section.

Then, my variances are easier to visualize and to see where the highs and lows are:

When you are analyzing variances, using conditional formatting, pivot tables, and charts can help you summarize your findings.


If you liked this post on How to Calculate Variances in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

postspeeding

Excel Analysis: How Much Time Does Speeding Save?

Speeding saves time but it also can put yourself and other people in harm’s way. But how about if you only speed 5 miles over the speed limit? What about 10 miles? Below, I’ll do a sensitivity analysis in Excel to calculate just how much time you are saving by speeding over various time intervals.

Setting up the file and creating the formulas

I’m going to create a base value of 50 mph to serve as my default speed. I will also create a variable for the interval to determine the different rates I want to move by for my sensitivity analysis. Here is what it looks like thus far:

Template for calculating time savings based on different speeds.

The formulas for the actual speed are off to the right. I am just taking the previous speed (or in the case of the first value, the default speed) and incrementing it by the interval. Doing it this way will make it easy if I want to adjust the interval or base speed variables without having to manually update the other values.

Next, I need to set up my calculation to determine the time that is saved. At 55 mph, over the course of an hour, I will have traveled 5 miles more than if I was traveling 50 mph (let’s assume this is the legal, posted rate). And since 5 miles is 10% of the 50mph I would be going on an hourly basis, that equates to 6 minutes (10% of 60 minutes) of additional driving that I would do at the posted rate. That is the time saved by speeding at a rate of 55 mph. To put this into a calculation, I first need to take the difference in speed:

=C6-$C$2

C6 is the first value in the speed column and C2 is the default speed. I also need to divide this by the default speed to get the % of an hour this would represent:

=(C6-$C$2)/$C$2

The next step is to multiple all of this by 60 (number of minutes in an hour) to convert this into minutes:

=((C6-$C$2)/$C$2)*60

Now, I can copy this formula down and now I have the time savings per hour by the different speeds:

Time savings when speeding by hour.

Next, what I will do is add different time intervals. I don’t want to strictly look at just a single hour. It will be helpful to set up various different periods. To do this, I’ll create a header for the number of minutes and adjust my formula so that it references the header rather than just multiples by 60:

Time savings when speeding.

Now, what I will do is set up more periods and copy the formulas across. Here is what the time savings look like across 15, 30, 45, 60, and 120 minute periods:

Time savings when speeding across multiple intervals.

To better visualize this, I will create a line chart that shows this data:

Line chart showing time savings when speeding.

The important takeaway from all of this is that for short trips of 30 minutes or less, you aren’t saving even 10 minutes worth of time unless you are speeding excessively (70 mph vs 50 mph), which is not just dangerous but can run you the risk of getting a ticket. But over a few hours of driving, even a 5 mph bump up in speed can save you 12 minutes. It is a safer and more sustainable option to go slower and gradually accumulate time savings.

If you want a quick way to do these calculations without using a spreadsheet, simply calculate how much faster you are going than the speed limit and convert that into a percentage. Then, multiply that by the number of minutes that you are driving for. In the example of going 70 in a 50 zone, you would be 40% over the speed limit. Multiply that by 15 minutes of driving time, and the time saved would be 6 minutes. The formula looks as follows:

I’m not advocating for driving fast and the purpose of this was simply to calculate the theoretical time savings in Excel. If you have other suggestions for problems to solve in Excel, please contact me with your ideas.


If you liked this analysis on how much time speeding saves, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Optimized-H2Ereinvest

Analysis: Should You Reinvest Dividends?

You can use Excel to create models, templates, and also to do analysis. This will be the first in a new series of posts to do with Excel-related analysis and how to set up a question and answer it accurately. In this analysis, I’m going to look at how much money you can be losing by letting it sit idle. Specifically, I’ll analyze an investment that pays you a dividend every quarter and look at two scenarios — one where you reinvest dividends back into the stock and one where you don’t. How much of a difference that can make over a 30-year period could surprise you.

At the bottom of the page, I’ll leave the file available for download if you want to take a look at my work and follow along and to see just how much of a difference there is when you don’t reinvest dividends.

Scenario 1: collecting the dividend payments and not reinvesting them

The assumptions and fields

In the simplest scenario, let’s set it up that you don’t reinvest dividends back into the company. To create a template for this in Excel, we’ll need to know the price of the stock, how much you’re investing, and how much the company pays in dividends (which is usually on a quarterly basis), and the growth rate. To keep it simple, we’ll assume the dividend payments will never change and so the amount that you receive in dividends will remain constant.

This is a useful assumption to make when making this type of comparison so that you can isolate one variable, which in this case is whether you reinvest the dividend payments or not. It’s safe to assume if there is a benefit of reinvesting dividend payments, it’ll be even greater if the payouts increase over time, so it’s unnecessary to incorporate dividend growth into the model in order to do this analysis.

I’ll also set the price of the stock at $100, the quarterly dividend at $1.25, and the amount to invest at $10,000. There will be a calculated field to determine the number of shares, which will take the amount invested and divide that by the price of the stock. With a $10,000 investment, you would be able to own 100 shares of a stock that’s priced at $100. I’ll also assume that the stock will rise by 5% every year. These are what my inputs and calculations look like so far:

Inputs and calculations for dividend template.

Setting up the headers

Next, we’ll need to set up the headers for the actual model where the results will be populated. The fields I’ll include are the year, the starting portfolio value, the dividend amount, the cumulative dividend, the ending portfolio value, and the portfolio + dividend.

In the year field, I’m just going to increment the numbers 1 to 30 to show the portfolio’s progression over 30 years. You can do this a few different ways. Besides manually entering the numbers 1 to 30 in, you can enter the number 1 in first and then create a formula that just adds one to the number above, and then copy it down. Another option is to enter the values 1 and 2 in the first two rows, select those two cells, and then copy that down. Since you are selecting multiple items, Excel will know the pattern and that you want to increment by 1 each time. Otherwise, just trying to copy 1 down will give you a series of 1’s. For some examples of how this works, check out this post on how to autofill data in Excel.

For the starting portfolio value, I will just link to the initial amount invested and in subsequent periods this will be equal to the previous year’s ending portfolio value.

To calculate the dividend amount, all I need to do here is enter the number of shares, multiply them by the quarterly dividend, and then multiply that by 4, since the payments are quarterly. My formula looks as follows in the first cell:

=$C$7*$C$3*4

$C$7 is the number of shares and $C$3 is the quarterly dividend. Since I’m not reinvesting any dividends, my amount invested will remain the same and that also means that I won’t collect more dividends (since I’m assuming the dividend rate will remain unchanged). This means that every year, I’m expecting to collect $500 in dividend income as I’m taking 100 shares, multiplying them by $1.25 and then by 4 payments.

The cumulative dividend field is an easy calculation as it’s just adding the total of all the dividend payments. You can calculate the cumulative value by using the SUM formula, freezing the first cell, but not the last one. In cell D12, my formula looks as follows:

=SUM($C$12:C12)

My dividend payments are in column C. While the first cell is frozen, the second one is not and the calculation will expand as I copy this formula down.

The ending portfolio value is calculated by taking the starting portfolio value and multiplying it by the growth factor — which in this case is 5%.

The last formula is the portfolio + dividend calculation. This will tell me what the total value of my investment is after factoring the growth in share price as well as all the dividend income I’ve collected over the years. This is a simple calculation of just adding the ending portfolio value (in column E) with the cumulative dividend in column D).

With all of my formulas copied down, this is what my values look like over the 30-year period:

Model that shows what a portfolio may look like after 30 years when a dividend is not reinvested.

The dividend payments total $15,000 after 30 years and the portfolio will rise to a value of $43,219.42 by the end of the period. Combined, the value of this investment is $58,219.42 when adding the dividend income on top of all the growth the stock is expected to achieve over the years.

Now, let’s switch over to the other scenario, where you reinvest dividends to buy more shares of the company.

Scenario 2: reinvesting the dividend income

This scenario will be more complicated because now the number of shares owned will change every year if you were to reinvest the full amount of dividends you earn.

I’ll need to make some changes to the structure of the template. First, I’ll want to track the number of shares that are owned over the years as that will determine how much dividends will be collected. I’ll also need to calculate the expected stock price to determine how many additional shares I can buy with the dividend income. And I also won’t need the cumulative dividend since the payments will be reinvested back into the stock.

The stock price field will rise by 5% each year and its formula will be simple as it will just rise by the growth rate. As for the number of shares, that will start with the initial purchase of 100 shares and then in future periods it will take the dividend amount and divide it by the stock price to determine the number of additional shares that can be purchased. The dividend calculation will then take the number of shares, multiply it by the quarterly dividend and then again by 4 quarters

With those changes, here’s what the model looks like if the dividend income is reinvested:

Model that shows what a portfolio may look like after 30 years when a dividend is reinvested.

At $92,169.05, you’re making $33,949.62 more by reinvesting the dividend back into the stock. This, of course, assumes that the stock will continue to grow at a rate of 5% and that you’ll do nothing with that dividend income but let it sit in the first scenario. But the point is still the same: the cost of letting money sit idle can be significant. In the second scenario, your portfolio will be worth 58% more than it would be in the first scenario.

Now, if you were to invest the dividend income from the first scenario into other investments, then the difference would likely be smaller. However, for the purpose of this analysis, it’s clear that there’s a big advantage of reinvesting dividend income. One variable that wasn’t considered in this analysis is the discount that companies sometimes offer investors when reinvesting dividend income, which could result in more shares and greater returns over the long term under the second scenario. But again, for the sake of simplicity, that was left out but it’s an example of another reason why reinvesting dividend income can be very beneficial.

Proving out the variances

The last part of this analysis involves proving these differences out, comparing when you reinvest dividends versus when you don’t. This is an important part in order to show where the variances came from and to illustrate that the calculations are correct.

Two key areas that contribute to the differences between these two models are the loss of dividend income by not holding more shares and also the loss of portfolio value by not benefiting from the full incremental growth each year.

To do this, let’s create another table that summarizes the variances. The first field here will be the portfolio change, which will just look at the difference in portfolio values between the two models in each year.

Next, the loss of growth column will calculate how much growth is lost by not reinvesting the dividend income. This is calculated by taking the difference in starting values and multiplying that by the growth factor of 5%. Since the dividend income isn’t reinvested, the starting portfolio value will be lower in the first scenario, which means the amount of growth earned will be less than under the second scenario.

The loss of dividend income is the next source of variation because with fewer shares in the first scenario, that will mean less dividend income. To calculate this variance, we’ll need to take the difference in the number of shares and multiply that by the quarterly dividend and by 4, for the number of payments during the year.

Lastly, there is a field for the cumulative loss, which is important as it’s a running total of the losses from dividend income and growth. This should match up the total portfolio change field, and I’ve added a check column to calculate the difference and ensure everything nets out to zero.

Here’s what the variance table looks like:

Variance table summarizing the difference between the two models.

As you can see, the bulk of the losses originate from the loss of growth as the impact of compounding can significantly affect your overall returns over the long term when you don’t reinvest dividends.

To see this file in more detail, you can download it from here.


If you liked this post on whether you should reinvest dividends or not, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.