Reversing signage values in Excel is a common but tricky task, particularly when dealing with complex data dimensions like cost centers. Manual signage reversal can quickly lead to errors, impacting the accuracy of your Excel reports. Our video explores various Excel signage reversal methods, including advanced techniques and tools to enhance your workflow and minimize mistakes.

In this video, Jon Lee explore various methods to address these challenges:

Lookup Tables and VLOOKUP: For those looking for a more advanced approach, we demonstrate how to use lookup tables with VLOOKUP to manage signage reversal with greater accuracy.

Manual Changes: While this approach is straightforward, it’s highly error-prone and not ideal for complex datasets.

Multiplier Method: A more efficient solution, using a multiplier to reverse signage, simplifies the process significantly.

While Excel is a powerful tool, its manual manipulation limitations can become apparent with complex datasets. We also highlight the advantages of transitioning to functional databases like IBM Planning Analytics/TM1, which can automate processes and ensure consistency across your reports.

Ready to take your Excel skills to the next level? Our video provides a step-by-step guide to mastering Excel signage reversal methods, improving efficiency during month-end procedures, and ultimately enhancing your quality of life.

(disclaimer: this transcript has been automatically transcribed so it may contain errors)

Ever found yourself in a place where you have to reverse the signage of your values in excel? Because how the data is coming and how you need to report and consolidate them is different. A good example is you’ve got one line of expense using a negative sign and then you have got another line of expense from another system that comes through as positive. Now you have to report on both of them.

Well, what do you do well this video we’re going to look at how we can do that. We’re going to look at a few different ways we can handle changing the sign of your value for reporting and planning purposes. And the name of the game is to upgrade your excel skills for month end to improve your quality of life. Let’s check it out in our lab. So today in this video, we’re going to go through multiple different methods of doing this in Excel and we’ll show you which ways, how to do them, what’s better and what suit for what situations.

And then at the end of all this, we’ll try to do one level better, getting to the edge of what Excel can do well to the next level because after all, month end is a very important process, but it shouldn’t be so stressful. So hopefully these tips will help you improve your quality of life for you or maybe your team as well. All right, so let’s have a look at our example in the lab.

So now, basic example one here I’ve got five journal entries coming through as retail wholesale returns, discounts, cogs and distributed commissions. And in my P&L I’m showing them as retail wholesale adds up to gross sales. And then I’ve got return discounts showing as negative and they sum up to net sales. And of course net sales together with cogs give us the gross margin. What is the problem here?

The problem is our source data is giving us the returns and discounts in a positive number. But what we need in the P&L is a negative number. So what do we have to do? Well, the simplest way to do this is to change the numbers manually using keypad, and that’s not good. Don’t do that because so much manual intervention is prone to error, which means you are really making your own life more difficult.

Alright? So particularly if you’ve got a lot of different numbers, because in my example I’ve got six numbers. In your real world, you probably have hundreds or thousands and you definitely don’t want to do them by hand. So what we can do here is we can have a multiplier. Let’s call it negative one. So the first method, the simplest method is change that back is to copy the multiplier.

Hit Ctrl C or right click copy. Once that’s in your clipboard, you go to your target and then right click paste special and then go to multiply. So what happened is here you’re pasting the multiplied answer into this destination cells and that give me a negative 200 and 256. Well, can we do better? Yes, we can. Let’s have a look in the next basic example. I don’t want to use that multiplier manually.

I want to have a global lookup because we want to improve the quality of life and more auditability is a good thing. So what we want to do here is I’m just going to put a signage as a column here and then as a final result, I can multiply signage by the actual source number coming from the journals, copy and paste those formulas or drag the handle down.

Hit F9. Okay. It’s given me exactly what we need to show. The correct value is now appeared. Well, can we do better? Yes, we can. All right. So in our next example, it’s slightly closer to the real world where we’ve got more data and more granularity than what we can handle simply with manual ones and negative ones. Here I’ve got January and February. But in your world it could also include cost center, department, region, location, currency, etc.

So how does this example work is I want to use a lookup. So here I’ve got a lookup table to be populated. Just like last time. I’m going to put ones and negative one waiting or signage multiplier in a lookup table. And in my final result, I’m going to use a VLOOKUP. VLOOKUP’s are very simple to use, but of course, let me talk you through how to do that end to end.

So VLOOKUP, I need to look up a value. So in this case I need to look up retail. Click on retail, hit comma, super important hit comma. And then we got to select a table array, which is the lookup table, right? So I select that and it’s important. Key here is I want to hit F4 on the keyboard to make an absolute reference. See the dollar signs coming out.

So that means as we drag or copy the formula down, we’ll keep this absolute reference of A18 to B23. Okay. And then the next one, comma, we’re going to tell it what column to look up. So the column index is the second column in this table. So I’m going to go 2. And then finally, false. Okay, so now that nose is one. And if we hit F9 it intelligently matched the lookup for us and the VLOOKUP.

So all the return discounts are minus one. But that’s not the end yet because this is just the weighting. That’s not the actual value. So now we multiply the weighting by a source number and I’ll drag the handle down or you can go Ctrl C. Ctrl V. Okay, F9. Voila. Now I’ve got the correct value. 200, 256. Yes. Excellent. So in this example, I’ve got a lot more data, but data isn’t the problem.

Hopefully with the VLOOKUP method that I just showed you before, this can really help with larger volume of data. But now data is not the problem because the problem is on the reporting side here. My superior have asked me to show net sales adding up a positive value of returns and discounts to the gross sales value to get my final answer. But what I do know is net sales is actually gross sales minus return minus discounts.

So in order to do that in my excel, I will have to manually formulate it as to gross sales minus return minus discounts to get my end result. Well, is that a problem? That depends why Excel is great at what it does because it gives us the flexibility to do exactly these type of edge cases to get the outcome that you need. So net sales can be anything that you want as long as you, your Excel skills up there and you’ve got discipline to keep it updated.

What’s the problem? Well, the problem is when you have got lots of different dimensionality, like cost centers. So for example, I’ve got cost center one, cost center two, cost center three. How do I update all these reports to reflect the new reporting methodology or new reporting requirement? Well, you have guessed it. We need to go to each of the reports and change them manually. I can go. Well, this now equals to that, minus that, minus that.

What’s the problem? Well, it’s prone to errors, isn’t it? And you have to cut and paste and hoping that your mouse and keyboard skills is right up there because this would be quite prone to errors. Minus this, minus this, minus this. Yay. Oh, made an error. Luckily Excel fixed it for me and off I go into all my reports and cost centers. Who would have thought after years of training and aspirating to be a financial specialist, I spent hours and hours manipulating reports to make sure the formulas are right.

And copy and pasting a lot of reports on a regular basis to meet different edge cases and reporting requirements. So based on what we’ve shown here, can we improve the quality of life for month end further, because how often do you get these reporting requirements from other people in the business or depending on what you need, if you’re the only person who’s doing these reports? Well, there are tools out there called functional databases that can help you with this.

So for example, here I’m in a functional database and I’m showing a sample P&L. And this is more akin to a real life situation. When we have a P&L, we’ve got a lot more accounts, we’ve got a lot more dimensionality. So it’s not just three months and three cost centers, we’ve got different versions, we’ve got budget, we’ve got year, we’ve got period, we’ve got currency, we’ve got departments, we’ve got region, we’ve got accounts, cost centers, you name it.

Right. So how does a tool like this help with signage? Well, I’m glad that you asked. So in the functional database, like planning, analytics, it stores the metadata within your chart of accounts. So you set it up once and it’s set up everywhere for everyone. So we have a look at the screen here. We’ve got a very similar situation. We’ve got discounts, returns and adjustments. And they have a negative weighting that adds up to net sales.

And so when net sales compute, it will add up gross sales, returns and discounts. So let’s see that in action when I hit,Ok, if you have a look at my net sales net sales is essentially gross sales minus returns, minus discounts. So what we’ve learned here is in Excel there are ways you can manipulate your source data to make it the correct signage for what you need. You can also use Excel to manually manipulate your reports.

So if you have a P&L, you can manipulate your P&L. If you’ve got 16 different P&Ls or reports that you need to manipulate, you can manipulate all 16 of those P&Ls to make the formula just right to handle your signage issues. Or you can have a solution like a functional database such as TM1 planning analytics. And it stores your definition once and then it applies everywhere for everyone every time.

So that’s an example of how we can improve quality of life with a few tips and tricks and levelling up your Excel skills. And also, maybe if you’re on the edge of Excel, you can, you may want to consider one step further to move your excel world into a functional database to improve your quality of life during month end. Because month end, although it’s important, it shouldn’t be a struggle.

If we can help you improve your month end just by a little bit, this video was worth it. Did any part of this video help you at all with your excel or your month end? If it did, leave us a comment or you think of any other challenges that we can help you potentially with month end because excel challenges everywhere happens to everybody. Let me know in the comments and we’ll try to solve them one issue at a time.

See you next time.