This lesson is exclusive to members

Microsoft Excel 2016 Bootcamp - Zero to Hero Training

Profit & loss spreadsheet

Daniel Walter Scott || VIDEO: 23 of 24

Download Exercise Files Download Completed Files

You need to be a member to view comments.

Join today. Cancel any time.

Sign Up

Hi everyone, welcome to this tutorial. We're going to look at making our profit/loss spreadsheet a nice, simple one. And it's going to do cool things, like down here, I've got a 'Sales' tab and allows me to do things like data validation which sounds really scary but it just means, if I type in 'Dan' here it says, don't put Dan', put in a date please. And when we get across to here, when I'm putting in the items that I've sold I can create a little drop down list and it's pulling this list from this list over here so it keeps everything consistent.

Same for the 'Cost of Sales'. A very similar format, there's a drop down for all my different costs, and when we've finished all of that, we create a dashboard to show it all using pivot tables, showing all my 'Sales', my 'Cost of Sales', 'Net Profit'. And also, a pretty looking graph. So let's now, go get started.

The first thing we're going to do is create our 'Sales' page. We're going to call this one 'Sales'. We're going to make it look pretty for no reasons other than to satisfy my designer urge. I'm going to make it a 'blue' background, I'm going to have 'white' text, make it 'Bold'. I'll make this a bit higher. And in this cell here, I'm going to tab across a little bit. And I'm probably going to get it to center inside this cell.

So this is my 'Sales'. Inside this sales document, there's going to be the date the sales were made. And there's going to be the price of the sale. And there's going to be the item that was sold. Now, what we want to do is, couple of things-- I'm going to stretch this out a little bit. You can do it all at one go by selecting all three of these columns, and dragging any one of them. And they all get a bit bigger. So, what we want to do is look at something called data validation, because, say we're going to create this Excel spreadsheet for somebody else to start working on and we want to make sure they don't go and wreck it. So, we're going to give them some boundaries. And that's called data validation.

So what we're going to do is, in this column here I'd like to say, all the things in this column have to be a date. And we do that by going up to 'Data' along the top here, and you're looking for this one that says 'Data Validation'. So, click the drop down, click 'Data Validation', and it will default to here. What we're going to say is, at the moment it says, you can enter any value here, no problem. What we want to say, actually it needs to be a date. You can see, it could be a 'Time', we'll look at 'List' in a second, it needs to be 'Decimal' point, or 'Whole number'.

You can give it a specific value that it has to hit. I want it to be definitely 'date'. And I'm going to say, I just want it to be 'greater than', and it could be any date as long as it's greater than 01/01/2000 Just a random date, as long as it's above that. And it's a 'Date' format. Click 'OK'. So what happens is, if I put in a date here '01/01/2017' Great, it works, no problem. But if I put in, say, my name, it comes up with what's called a 'stop', which just means you are not allowed to. You can retry but you can't go any further, it doesn't allow you to do it. So there are times where a 'stop' isn't as important.

If I select all these guys again, and go to my 'Data Validation', the other options in here are, instead of this one here which goes to 'Stop', there's these other two. 'Warning' is like a 'Stop' but you can override it. And the last one here, which says 'Information' it's more just a general kind of frequently asked questions kind of thing.

So let's quickly look at 'Warning'. Let's type the error message, so, let's say 'Date invalid'. 'Please enter date into this field.' I spelled field right. So that's going to be the pop up text that appears. Let's see what this one does. If I put in 'Dan' now, it's wrong, but unlike 'Stop', where there were no other options this option, you can override by clicking 'Yes', or you can hit 'Stop' by hitting 'No'. You can see here, my 'Date Invalid'. ‘Please enter a date’, that's the pop up that comes to the person that's using it.

So the last option in here - I'm going to hit 'Cancel' on this one. - is I'm going to select all my cells and I'm going to say, the last one, 'Data Validation' we just had a little look at it. Here you are. And the message in this one's going to be 'Information'. Same sort of thing, but you'll look at the difference when I pop in 'Dan'. It's more like a helpful reminder, "Did you know?' rather than a warning. It's up to you how you work it, I'm going to put mine back to 'Stop' because I need it to be a 'Date' because I'm going to use a report at the end of this tutorial that's going to show the dates, and what money we've earned for different dates of the month.

So with it all selected, back to 'Data Validation', and I'm going to say it will not go forward unless the dates are put in there properly. Next one, easy one, under 'Price', we're going to select all of our cells here. And we're going to say, under 'Home' we're going to say, I would like you to be 'Accounting'. Actually I need mine to default to £s, so I need to change mine to $ sign. This first one, I'm entering off my bank statement and it was on this date, it will cost this much, and the item.

So now, next thing we want to do is we want to create a drop down menu. Why? Because, let's say we sell 200 different things or 10 different things. We don't want people to be just wily nily adding the name of it because when it comes to creating graphs or tracking data we need to have consistent naming. So what we're going to do is create a nice little drop down list so that people can just choose from it. Then it's going to be exactly the way we want to display it.

So, to create a drop down list is pretty easy. You create a list somewhere, could be on an extra workbook sorry, extra worksheet. So we've created an extra sheet here but what we'll do is, we'll just create it to the right here. We'll call this one our 'Product List'. And we sell courses that are Photoshop courses, we sell InDesign courses, we also sell Illustrator courses. And of course, Excel courses. And Word courses, and all the courses.

So that's going to be my list here. So I want people to be able to pick from this drop down here. So what we're going to do is, in this list here, I'm going to select them all. And we go back to 'Data Validation' like we did for our date. So we say, you have to pick 'Data Validation' here. You have to pick from this list. We just used 'Data' a second ago. So, pick from this list, I would like this 'Source' to be, so click in here. I'm going to say this source, please. This is where we're going to pick our list from. Click 'OK'. And you can see, this little drop down menu appears. I'm in here, and this is for my Photoshop course.

So let's go through and add a little bit of fake data. This is going to be a different month. And the same year. And we've got another one, that was-- Another statement for $500. And this one here is going to be InDesign course. So I'm going to go through and add a bit of data. Tayla would fast forward this little bit, while I go and do this; boring.

So I've entered all my details from my bank statement. We've only got a few details here, yours will be a lot longer. Next thing I want to do is I'd like to create another sheet, or another document because this is my 'Sales' I'd like to keep the "Cost of Sales' in another place. And that could be just below here, 'Cost of Sales' just underneath. Or we're going to keep them on a different worksheet. I would like you to go through and see if you could do this on your own because this is exactly the same, but instead of it being costs in terms of sales coming through it's going to be the cost of things we spent money on.

So it might be things like printing materials, the venue, the trainer cost. These are the costs involved. So you're going to go through your statement and put that into your 'Cost of Sales' spreadsheet. Now I'd like to see if you can do it, it's exactly the same. I don't want you to copy and paste I want to see if you can build a list of things to pick from. It might be telephone, might be rent, power, water. And I'd like you to create a drop down for people to pick from. I know you're not going to do that, are you? I would need that, because this thing here is exactly the same thing. I want to just go change the list in the drop down, so let's do that together.

So I'm going to right click this guy here, and go to 'Copy or Move'. "Move or Copy'. And I'm going to 'Create a copy'. I'm going to move it to the end. This sheet goes afterwards. Double click this one, I'm going to call this one 'Cost of Sales', not much difference. This one here is just the 'Cost of Sales'. The only thing I need to do in here, is over this side, I'm going to have to go through and change my list. So this might be the cost of the venue, 'Venue'. This might be the cost for, what else? The kind of trainer cost that we have, Trainer'. We have the cost for materials. You might have lots of other ones. So telephone, all those different ones.

And in here, we're going to clear these out but leave the data validation, so in here, the list changes. So this one here is going to be a cost. Let's say my venue cost me $150 for some reason. That was my venue. And then there was the trainer cost, which was 1 million, because trainers are awesome. So we go to this drop down. Change it to 'Trainer'. This one here, we're going to go to-- this was some printing costs, cost us $20. And the telephone cost us $30 for the month. And that's-- that field there we don't need. So that is my cost that come into the business.

Down the bottom here, I'm going to rename this one here 'Sales'. I've imported my 'Sales', the 'Cost of Sale'. And now I would like to build a little dashboard to show me my profit and loss. So we're going to create a new blank sheet down here by hitting this '+' button. This one here is going to be called my 'Dashboard'. And what I might do is, just click, hold and drag that tab to the front. So it's the first thing that opens.

To create our beautiful dashboard, we're going in to add a total. And I'm going to fancy it up a little bit. For no reason, remember. Actually I'll do it for this whole column so it looks nice. And I'll get him to 'center', and I'll get the 'font' to be a bit bigger. And I'll 'indent' within. Nice and finished. Next thing I want to do is, I want to show a table showing the total of all the sales. At the moment, they're all separated in here. I want to group them all together. We're going to use our beautiful pivot tables that we did earlier.

So, click the cell I want it to go into. I'm going to go to 'Insert', 'Pivot Table'. Now I can't see it here. All you need to do is-- I want to go to 'Sales', and grab all of this. Then click 'OK', and it jumps back to here. Back to our 'Dashboard'. And I want to show my items in the rows, and my price, and the values. I'll do the same for showing the cost of those sales. So I'm going to put it where I want it to go, right there. And I'm going to go to 'Insert', 'Pivot Table', and I'm going to go to 'Cost of Sales'. Grab these guys. Click 'OK'. Same thing, I want the items to go in the rows. And I want the price to go into values. Yes, that is my 'Sales', and 'Cost of Sales'.

We're going to have to play with the labelling a bit. You can see here, I'm going to stretch this cell. I'm going to call this one 'Sales' instead of 'Row Labels'. So this is going to be 'Sales'. Some of the prices are probably not what I want, I want-- let me do 'NZ$'. I'll do the same for here, 'NZ$'. I'm going to select this whole column, and say actually I'd like you to be 'Accounting'. And I'd like it to be 'New Zealand $'. Great!

This one here, I want to be 'Cost of Sales'. And I would like to start doing some totals. At the moment, 'Grand Total' seems a bit-- These are just 'Totals', not 'Grand Totals'. What I'd like to do is, look at what my 'Net Profit' is, expenses, of my sales. So we're going to go into 'Net Profit'. And what we'll do is, I would like it to-- I want to create a formula. So '=' first, and I'd like this, '-' the cost. 'Return'. And my 'Net Profit' is currently -$28,000. That's because my trainer cost was so high. So let me just style this a little bit. Just so we've got some easy way to say it.

Now, let's look at updating this because this is quite important, remember. There's two things. If I go to my 'Cost of Sale'. And let's say I've mis-entered this as 3000 because it's $30,000. Let's say it's just $300. And if I go back to my dashboard, nothing changes. So, remember, you've got to go and refresh that data. We did that in our pivot tables. But there's something else that's going to happen as well. So, let's say the cost-- I'm clicking in my 'Cost of Sales' here, and I'm going to 'Analyze', and there's my 'Refresh' button. Look what's going to happen. It does refresh. Get the right number in it, 300, but it's kind of re-sized the columns as well, so I'm going to 'undo' that.

And what we can do in here, we can go to 'Options' along the top here. Click on that. And where it says 'Layout & Format' we want to go down to where it says 'Autofit Column widths on update'. Because it's going to automatically just have the columns just wide enough to show things. I don't want you to do that, I want you to just leave it as I had. Let's click 'OK. Now if we hit 'Refresh' it's adjusted, and our 'Total' is adjusted and we had a 'Net Profit' of $868. In the money. Again, for no reason, 'Center', 'Bold’, 'White'. I'm going to—

What you can do though is-- Refreshing it is a bit of a pain. There's something you can do, click inside your table. And go up to 'Analyze'. Go to 'Options'. And along here where it says 'Data', there's an option which says 'Refresh data when you open a file'. So at least when you open it, it goes and checks again. Do the same for this one here, so you need to do it for both of them. I'd like to go to 'Data', and say 'Refresh this data when you open it'. Awesome! I didn't do it to this one here. I need to also go into 'Options' and say, whenever I refresh this one here 'Autofit columns on update'. So I did it for this bottom one, or maybe did the top one.

Next thing I want to do, maybe one of the last things is just-- my dashboard looks kind of cool. So I've got my 'Profits', 'Losses', and 'Totals'. I want to show a nice little graph in here. So what I'm going to do is I'm going to click in this top one here because I want the graph or charts to be from my 'Sales' I don't want to show my cost, you could do the cost one as well. And up here, where it says 'Analyze' there's one along here that says 'Pivot Chart'. It's going to give you a chart based on the pivot table. It's given me default columns which is perfect for what I want. And this is just going to give me a nice-- oops, 'undo'. And we're going to grab the outside white bit. And this is going to give me my 'Sales' along the top here. So this is going to be 'Sales'. 'May 2017 Sales'. Nice! You can do the same thing for the 'Cost', just to see what they're like. Maybe make a different kind of chart.

Last thing we might do - I'm going to close down the 'Charts'. - is I don't like these little lines everywhere so this is going to be my-- my dashboard is kind of my presentation sheet. So what I'm going to do is I'm going to go along to 'Page Layout'. And along here, where it says 'Guidelines', 'View', we're going to turn this off, so we got a nice prettier table.

So that my friends was a long video. We made some drop down menus, we did some data validation, and we made some profit and loss charts using pivot tables and we made a chart at the end there. How good is that? All right, that is going to be the end of this tutorial series. There is a more advanced tutorial series if you want to go do that one as well. But that's going to be us for now. Bye for now. And I will hopefully see you in another course. There's a bunch of other courses. There's Word, and PowerPoint, and Access. Plus a lot of Adobe products like Photoshop, Illustrator, InDesign. So go and check those out as well.