How to use concatenation to clarify assumptions

One of the most basic and useful features in Excel is the ability to concatenate values with text. CONCATENATE is just a fancy word for "join." In this video we'll look at a simple way to use concatenation with the TEXT formula to highlight assumptions in a break-even model.

This model calculates the break-even point for a single product based on assumptions about price, variable costs, and fixed costs. In addition, it shows operating income at different sales volumes.

If we change an assumption, the model recalculates.

In the sales volume table, it would be nice to show the values used for selling price, variable cost per unit, and unit contribution margin so that the current assumptions are always clear.

We could hard-code these values directly into the labels, but then the values won't change when the assumptions are updated.

A better way to expose values in the table is to add them directly to the labels using concatenation.

We can do this using the ampersand symbol. First, add an equal sign to turn the label into a formula. Then, wrap the existing text in double quotes. Next, add an ampersand character and point to the cell with the correct value. The ampersand joins the two values.

We also need to add a "space" character inside the quotes to separate the text from the cell value. And the @ symbol will make things more readable.

Now, if we change the selling price, the label is updated to match.

This works pretty well, but we can improve this approach by using the TEXT function to display values in currency format.

The TEXT function converts numbers to formatted text. You can use the TEXT function to format currency, dates, percentages and any other format that Excel understands.

The TEXT function takes two parameters: the number to format, or value, and the number format to use.

In this case, we'll use a Standard currency format with commas, and without decimals.

Now we have a formatted value in our label. I'll fast forward here to update the other labels. Now when assumptions change, the labels in the table are updated to match.

You can use this same approach to highlight assumptions in any model.