Lessons

Lesson Info

Segment 25 - Excel - More Intermediate Techniques

I want to now go into a whole different mode now that we have seen how tio work off of a data list filter and sort the day the list and turn it into a pivot table to spend some time analyzing let's actually work with a spreadsheet and do some data analysis on another spreadsheet, so I'm going, tio, open up another file, and this time I want you to open up. I'm in my excel files for the course, and I want you to find revenue projections again. There's a revenue projections final in there to show you where we're going with us, but we're going to open up revenue projections. And so what I have here is a list of trade shows that I've done some of these air astrology conference is this one's a metaphysical expo? This is, ah mainstream christmas bazaar, and I'm going to go ahead and double click on the line between the a and the bay in the columns so that I could see those a little bit easier, and what I have here is going back to two thousand two, twenty, ten, twenty, eleven, twenty, twelve...

, twenty thirteen how I did at those shows, what was my grand total, and the revenue that I received from those shows, and what we are going to analyze in this unit is looking at how the how we might do in the future look at are expected return and make some decisions on whether we should do this particular show again or not and my specific intention with this unit is to show you some more intermediate techniques we've shown you how to construct a spreadsheet like this earlier in the course and so now we're going to do some actual business decision making to show you how this spread she came to be the way that it is I want to show you a way to see the actual formulas themselves and so go to the formulas ribbon and there's a button here that says show and it has kind of the function symbol and I want to show the formulas and it goes into another view of the spreadsheet and aiken see in road nine the that each of the figures at the bottom of the list of numbers is in fact a calculation of those sums another way of getting there that I like a lot the keyboard command is really handy up in your upper left hand side of your keyboard you have that kita has the accent or the tilde the little squiggle hold on the control key and press that accent and that allows you to toggle back and forth between your views that way you can see which of these numbers came in as a calculation and which one was actually typed into the cell now, when I look at this at these formulas, I can see here that this says some of the three to be eight, you know what? I am going to trace the president's now, so I'm going to click on this b three to be eight cell right here, b nine and something seems a little wrong with that. When I look at that number, when I see that twenty three thousand dollars is like, wait, I don't think I really made twenty three thousand dollars, so I come up on that formulas ribbon, and I'm going to quit trace precedence, and I can see here what cells are making up that formula and what do you see here? That's? A little incorrect. Anybody catch what's going on here? There's something wrong? What sell what's the first cell that it's adding up? Yeah, it's putting the year twenty ten in the some, and I didn't catch that until this point. When you are using dates as column headers, you're using it as a label. You're essentially using the date as text, but because it's a number excel, is trying to calculate based on it. So when I'm using a year, I am one excel to know that it's a label instead and sew up here in the formula bar I'm going to click right before the two in twenty ten so I'm clicked on selby three then I come up to the formula bar and I'm gonna type an apostrophe like you would like oven apostrophe s and when a tab off of it it now knows that this is is text and when I go back to my going toe turn off my trace precedents, I'm going to click on this button here to move to remove the arrows I can see that there's a little green spot in the corner right there and when I click on the warning here it's telling me, oh this number is stored as text, which is exactly what I want so now that twenty ten is no longer available to use as a calculation and lo and behold, that number has now dropped if I wanted, it is probably a good idea to actually go up to the formula and change that to b four because that's really what you're adding up is from four to nine but at least now if I go in right here I'll do it again in column c for twenty eleven it says twenty three thousand dollars, but if I click on that twenty eleven and add an apostrophe before the twenty eleven and tab off of it, it now drops to twenty one thousand dollars another visual is that my ear was right justified when it was a number because excel always right justifies all of your numbers now that I've turned it into text it's left justified it like it does with all of the words does that make sense? So I'll go in and I will put in my apostrophes before twenty twenty above and twenty thirteen as well and when I do that I can use those trace precedence again I'm going to highlight all of the figures in row nine and use my trace precedence I'm sorry it only does it one cell at a time so c nine trace the precedent so these are still looking at the wrong cells so I can either probably the best thing to do would be to take this formula that's correct and for those of you who are joining in and didn't see the original of cell I can take a formula and replicated across all of these columns see that little dot in the corner there of selby nine I'm clicked on the nine if I hold my cursor over that little dot it turns into a thin black plus sign and I'll drag that to the right and now it's going to replicate the formula and update it as I go so it was before two b eight and it now does copied it to c four to c eight defour two d eight d four t e eight and when I dio check my press since I can see that it's now calculating without the year so two ways to handle that potential are and then I'll click on remove arrows again so a few different techniques all wrapped up into one functional purpose so the next thing that I want to do is show you a neat little chart called a spark line and a spark line is a chart but instead of being this big chart that takes up lots of space it's a whole chart in one little cell so I want to put in another column right before column f so I'm going to right click on f again you can control click on it or you can do a two finger click on a track pad and I'm going to insert a new column actually I take that back I'm going to do it differently undue do a command z billy's now you saw how to insert a column because I have a label here for my increases and then my increases over here I'm just going toe put my spark lines right in this increase just to take up that what would otherwise be blank space so I want you to click in cell f four and we are going to make a spark line out of our sails and we'll be able to see how our sales have trended so when I'm clicked and sell f four I'm going to go to the charts tab and inside the charge tab I have a little area here that says insert spark lines and there's three different kinds there's a line chart there's a column chart and there's also a win loss chart and a win loss chart is going to be used when you have positive and negative values we're going to go with a line chart because it's going to be the easiest for us to see right now so click on line and it wants to know pops up this little window and it wants to know what data range do I want for my spark line so I'm going to drag from before to be eight I'm sorry before two e four and those are my four cells and I'll click ok are let go and where it says increase when I click ok right here I now have this tiny little church let's do it again for practice let's go toe f five and I'm going to go back to charts and backto line and again I'm going tio select the nor wax sales from twenty ten to twenty thirteen b five too eighty five and let go and click ok and then you get another little chart no, I don't have to keep doing that and keep doing it for each cell I'm going to make use of that auto fill handle again I'm going to click back on the f five and hold my cursor over the dot in the little right hand corner of it until it becomes a thin black plus sign and then dragged down and I will be able to see spark lines for all of my shows now this spark lines look a little better when they're a little bigger so let's change the height of these cells so over in the right hand I'm sorry the other right the left hand column I want you to click on the number four and dragged down to the number eight we're going to work with all five of these lines let's right click or control click on those numbers and there's an option on the little pop up menu for roe height go ahead and click on that and the height that I won should he uses point for so little we're going to make it slightly smaller than a half inch and click ok so I made this a little bit bigger and now of course my spark lines are a little easier to say and maybe I also want to hold my cursor between the f and the g and making a little longer that way as well a few other tricks with spark lines click on a four and holders shift key down and click on f eight that's another way of selecting cells and said I'm just dragging you could of course just drag on the spark lines and notice because we're on a spark line that there's a new ribbon appear at the top that was not there before and I'll click on spark lines and so now I have a couple options here I could try changing the type let's click on column and now you can see it as a bar chart a column chart, but I don't think that looks nearly as helpful someone switch it backto line I can put markers on them I can mark the highest point and the lowest point I can also marked the first or the last and I can also put markers on all of the points and that helps me actually see the data themselves and because you're all creative let's change some colors here we have a whole color color gallery and of course when I click on the triangle that appears when I put my cursor over it, I have all kinds of color schemes I'm not picky you can pick any color here that you want feel free to be creative and so now I have business little spark line graphs do you have any questions about those seems to be working how you guys doing cat? I had a big gas from use another ah ha moment the cat oh, hi, I'm a little rusty today didn't sleep much last night, but I was thinking was like, you know in real estate, they're always asking me, how's the market that I want to say, I don't have a crystal ball because all the numbers I have are from the economists and they're about six months behind, but I feel how the market is because I'm always out there and now I can be my own little economies and I can put the days on the market and whether the property was stays and different dynamics and run my own people to tables, and I'm very excited this good think right use for it fantastic and actually where I'm going to go now is into a little bit of forecasting, so now we're going to see how to take our trends and how to see where our trends go. Victor pivot tables new for you, something you've used before yeah, I think is going to be a lot of use for that tell us about your background. What career spacing u n well, I do production services for help, so yeah, we have a lot of data about customers and help for example, they was wait and things like that, I have been working to spread sheets a lot of in time. To be honest, I felt myself to be like, basic or intermediate in excel and learning these new functions, you know? Fantastic, good, thanks, alicia, sure so now we're going toe bump it up a little bit, this is going to be a little bit more challenging than the things that we've done so far. What we're going to do now is a little bit of forecasting, and so what we're going to look at is if these were our trends in up to twenty two thousand three what's going to happen in the future years now, this first step is a little bit arbitrary, this is the growth that we want to see for our company, so I'm going to click in g two under twenty fourteen, and this is how I'm going to predict that my sales are going to go and let's say, I'm going to predict that my sales are going to increase by ten percent, okay? So I'm gonna put ten percent in g two hundred twenty fourteen now want to go over column h, where we want to say, maybe we're planning to do a big marketing push that we think is going to see results in twenty fifteen, so we want to see maybe a fifteen percent increase over twenty fourteen, so we're going to do this with the calculation we always start in excel formula with and equal sign, so we're going to take whatever was in twenty fourteen, so click on g two and we're going to add to it another fifteen percent so we're going to see ten percent growth in twenty fourteen, an additional twenty additional fifteen percent in over that, which makes a twenty five percent growth compared to two thousand thirteen and in twenty sixteen maybe we know that that's going to round off a little bit, we don't want it to be quite so much so I'm going to do another equals and we're gonna compare it to h two and we're going to add maybe just another five percent growth on top of that. So this is just our predict ocean these aren't real numbers, this is what's really going to happen, but based on our business plan, this is what we have. We expect things to change our klein's that before we move on, I don't wanna miss this is comes from nora saying to get rid of the spark lies to you just click into leading the senate elite the self is simple story thank you get in or militia now what's neat about doing these as the way that I set them up to base them on each other is if I just changed on don't do this, I'm just demonstrating, but if I change my mind and next year I'm gonna have five percent revenue increase, these numbers change accordingly as well. Now before I do this next step, I have to explain something about excel and I was just going to move off to the side and show you and show you what this is your aunt toe just watch right now. You saw that every time we copied a formula, it updated the cell references. So when we moved it to the right one it updated from column b two column c and then see today, and it works that way. Also going from row for two row five to row six when you that's what's called relative cell referencing. Now what we're going to do when we do this make our formula here for our trend for twenty fourteen we don't want to have to do it over and over again for five shows and three years, we just want to take this one formula and replicated all the way down. So what we have to do is change the formula to what's called absolute self references, so first I'm going to show you how it first we're gonna build the formula, then I'm going to show you how it doesn't work, and then I'm going to show you how to make it work. And so I'm going to start this formula. I'm going to click in g four, so the twenty fourteen sales for the body, mind, spirit expelled, I'm gonna type in equal sign, and so I'm going to click. Qian e for mrs r twenty thirteen sales for the first show and so I'm going I want a ten percent increase, so I'm going to add to it ten percent of this number, so I'm going to come up here to the g to the ten percent whenever I say of in a word problem I always multiply and multiply is a shift eight to get an asterisk it's not an x it's a shift eight for multiply and then I'm going tio highlight or excuse me click on e four again so what? This formula is telling me that I'm going to take my twenty thirteen sales and I'm going to add to it ten percent of my twenty fourteen sales and it press return so this is now saying that I can expect to make five thousand dollars when I take that formula and used the auto fill handle and drag it down for all five shows and drag it across to twenty sixteen I get all kinds of whacking numbers and that's because I look over in h four when it updated it it now made it f four but so it went from e to f but f is pointing to my spark line which is a zero and then when you multiply anything times zero is zero and in the same way going down this formula took my g three, which was my ten percent I'm sorry maggie to but because I went down a sell it went down the cellar here too to a g three which is again zero so I have to change how we build this formula so I'm goingto undo those two drags and come back to my formula now what we need to do here is called an absolute cell reference and so what that means is that my e four can move down but it can't move across does that make sense? I have to absolutely look at cell e four eighty five eighty six eighty seven so I'm going to come up to my formula here and I'm going to click on e four so my cursor is flashing right inside that e four and there's a keyboard command command t that runs me through four different states so there's the just plain the for relative there's e for with dollar signs what that means is that no matter where I copy this cell it's always going to point right here but that doesn't work either because I needed to go to row five so I press command t again and now it's saying I would stay in row e no, I'm sorry I would say I'll move back and forth from d c d e f g but I'll stay in row four again not what we're looking for but the four state is this says that I'm going to start in column e on row four and it's okay to go down to five to six to seven to eight so the dollar sign and I'm sorry that they use the dollar sign it has nothing to do with money hey that's just what they use for this absolute cell reference so dollar sign me for now let's look at the that the g at the g to sell we need it to stay in road to but we needed to move to hta I so I'm going to click in the g two I'm going to do the same command t now that doesn't work because we still move it to the h to move so I pressed command t again now I have g dollar sign too so again that means that the g can change to age die but the two is not going to move up and down it's going to stay in road too no matter what and then we've do the e four again we're going to do the last number in the formula and we just need to do that same dollar sign you can by the way just type the dollar sign before the letter and before the number you don't have to do the command tea but it's nice to just toggle through them and a press return so now what happens when I copy this down? My original formula now goes to e five but the g two stays in place, and it goes to e six. But the g two stays in place, and it goes to e seven. But the g two stays in place, and when I highlight those formulas and I dragged him to the right, I get the same effect. Now, those numbers signs there simply mean that the cell is too thin. So I'm going to make my columns a little wider. And then they fit. And if I analyze column h, I can see that. What was column row four has now stays the same, but now my g two has become an h two and over here, it's become an eye, too. And down here, it's hte, too. And it has, in fact, moved tio e five. I know that's a little hard to wrap your head around, but you can see how now, under any circumstances, you're making sure that a formula points to the right cell, even when you copy it. And so this concept is called absolute cell, referencing most of the a lot of the time. You know, I kind of gave you an advanced example of this, where we have three columns and five rows of it aa lot of the time, if you just have a value somewhere, like a constant you'll do the dollar sign e dollar sign force what stays in that spot no matter what it absolutely, positively has to stay at just this one cell so that's the most common way of seeing it, so now I can see my trends, I can see approximately how I think I'm going to dio at each of these shows over the next few years, so now let's make some decisions on whether we should try and do these shows again, so I've made you a little table down in the bottom left hand corner of the spreadsheet that says, do this show again, and if we this is what we're going to do here is called a v look up, which is another one of the questions people ask me about another very popular formula, so what we're going to do here is we have if I don't if I make less than three thousand dollars on the show, I don't want to do that show again. So the answer's no, if I make three thousand dollars to five thousand dollars, let me think about doing the show. I'll see how I'm feeling at the time or if I have other considerations and maybe go and maybe not, but if I make over five thousand dollars at the show, I'm definitely going to do that show again, so you start by making this little table and you always go from lowest to highest and so you write what you're baselines are and what the answer is, what answer you want to return and when I look over let me zoom this out a little bit so you can see everything all at once so here I'm going tohave xl make the decision for me so if I'm going to do this show again so I'm going to start this formula with an equal sign like I always do and the function that we're going to do here is called a v look up and as soon as I start typing the l v stands for vertical because I'm going up and down and I'm going to look up this number over here in this area so a type v l and v look up pops up and we're going to click on it and in my pop up here I have four different arguments it has four things that it needs in order to make this decision for me. So the first thing is the look up value it wants to know what am I going to base my decision on and so let's say we get to that twenty sixteen I'm going to look at this number right here so my twenty sixteen sales so I'm going to click on that so I'm gonna look up this number in that table and then type a comma and you can see the little tool tip down here, says now table array that means where the numbers where you're going to do the analysis and that's in this little table grid over here, so I'm kind of highlight and drag from the zero to the yes. Now, both of those numbers do need to be absolute, because no matter where we copy this, I need it to go from a thirteen to be fifteen. So come up here to the formula bar and clicking the a thirteen and do that keyboard command again of command t just one time and it does the dollar sign a dollar sign thirteen so no matter where I copy my formula, it's always going to start looking it up right here, and I click on the b fifteen and again, you khun type dollar sign before the baby, and then move over and type of dollar sign before the fifteen or you can do the command t and so, no matter where a copy my formula, it will look from here to here for the answer. So it's another example of absolute values. Now click after the fifteen again and put in another comma. The third thing that excel needs to know is where you get your answer, so the table ray was saying, here's, my data the answer is in column to I wanted to return no maybe or yes so it's not concerned about the letter b it's concerned about here's the first column here's the second column so I'm going to type in the number two and if you're working the then put another comma now the range look up because I'm looking for anything greater than zero and less than three thousand I don't actually need a range here if I was telling it that I have to pick something exact off of the list, I would actually type in the word true here because it truly has to be on the list, but that's not what we're doing here, we just have anything from zero to three thousand, anything from three thousand to five thousand and anything higher than five thousand, so I'm going to take that out. I'm going to raise all the way back to the number two and I'll press return and it says yes so just to review that formula one more time, the formula function is vey look up because I'm going to look up going up and down the I four is where it's getting the information, then a comma and then the range of the values where it's doing the analysis it gets the number from here and then the two is give me the answer from the second column so, yes, I should do this show again, and is that right? Six thousand dollars greater than five thousand dollars, and I'll take my auto, fill handle and drag that down and there's my answers nor wak is in the four thousand dollars range. Maybe so maybe not. The next two shows are greater than five thousand, but america's largest christmas bazaar no it's under three thousand dollars, so it's not worth my time, and if I start changing these revenue projections, maybe I decide in twenty fourteen for some or something happens in my life, and I can't put in all the marketing that I was going to, and I don't expect to do any better the next year, one and I changed that to a zero and let's see if our answers change and in fact that wasn't really enough to even change those numbers. Maybe I decide that in I'm not going to have any revenue growth at all in twenty sixteen, so I'm just kind of like trying to move these numbers down so that these numbers here change actually some yeah, those are the same, but as my numbers change here, let's do one of these, I'll just change a number here, and I say, maybe I had a bad year here, okay? All of a sudden boom that changed to a no and so you can see that as you play with your future planning and your revenue projections, that will help you make future decisions for what shows you should d'oh now, I know that was like going up on the high dive and dr jumping in the deep end, but this is shows you demonstrates the power of excel and how you really can use it to make good business decisions was one thing that I see when I worked with artists on dh helped them with their computers, is they're doing it for the love of what they're doing, but they may not be actually selling enough, or maybe they don't put their own time. And, like most sole proprietors, don't pay themselves anything you just hope you make enough money to, you know, go pay your rent and eat, but is your business actually growing? Once you put yourself into the equation, so by making use of these tools, it doesn't have to be just for show projections. What I want you to come away with is the ability to look to the future and make decisions based on your plant growth and be able to use excel to analyze, you know, just like we did with the pivot tables, analyzer sales, what products are selling well, make more of them promote them. What are not selling even if something that you truly love maybe you make it because you love it but you don't depend on it for the life blood of your business and so that will help you not spin your wheels so that you can maximize your growth potential for your company doing any of this did you get any answers you weren't expecting on your own business that you changed drastically having seen it of on yeah it has absolutely helped me with some of the shows because there's some shows that I was just taking as a given and then I realized hey, wait I put in so much expense in to get into that show that the return actually wasn't as big a number as I thought because I wasn't putting in my materials how much did it cost me to make the jewelry for the show or to hire the health or to travel there? And then all of a sudden what looked like a fabulous show turned out to be like one of my smallest profit margins now we have a question from rob in the chat rooms I'm not absolutely certain I'm going this question right rob so if you if you were this feel free to chip chip back in but I think you've already answered it alicia jaycee asking can you use excel to forecast the growth rate based on the past five years of sales trends you know, robin, assigning arbitrary growth percentage can you actually do something very specific? Sure, there are tools for trend analysis in here, it's beyond the scope of what I can do during this course, but some of the charts, for example, actually include trendlines, and so you can take some of the charts, and you can use them to extrapolate off into the future based on past trends. Just a general question that I'm interested in. Alicia what point to do you feel personally that you sort of outgrown excel? You need to move to more of a database structure like microsoft access? They're different tools for different jobs. That access is great for tracking data like my customers, my, you know, their customer information to some extent what they've purchased, but excel is what I used for actually working with the numbers for it, that aa lot of people make the mistake of it's not really a mistake, but let me open up that other file again, going up to a file open recent, and I'm opening up those customer purchases. They go over to excel because it's really accessible and start just making sheets and sheets and sheets of data, but it does have some analysis tools, but if you go toe to toe access, it allows you to start combining different sets of information. So I have one table that has all my customers and another table that has my products and another table that has my cells, and it allows me to combine sets of data into different forms or different collection tools or different outputs that excel just doesn't allow you to dio. Now I can take this table and imported into excel. Those are imported into access. The next step with access would be separating these things out. I would have one set of information about the shows in one table. I would have one table with all my customer information, and then I would have another table that has the sales themselves and what's neat about that is instead of seeing planet bracelet planet brace the planet, brace that black, black, black, I would see the planet brace at one time with an I d number that pulls all the rest of the data from the other places. It's a whole different ball game. What is totally that's your second course?