Hi, my name is Mr. Hall and thank you for joining me for this Oak Academy lesson, which is called Spreadsheets Project II and is the final lesson from the unit Spreadsheets.

I hope you enjoy creating and adapting your spreadsheet model today with me.

The outcome of this lesson is that you will use a spreadsheet to model different scenarios for an event and use the model to make recommendations.

We've got two keywords in this lesson so our first keyword is scenario.

So a scenario is where different sets of input values are used within a spreadsheet to explore various potential outcomes.

So that's scenario.

And then we've got COUNTIF.

So, COUNTIF is a function used to count how many cells meet a set criterion.

So those are two keywords for today, scenario and COUNTIF.

We'll be working through two learning cycles in this lesson.

So first of all, you're going to model different scenarios in a spreadsheet.

And then later in the lesson we will look at how to present information from a spreadsheet model.

So let's move onto that first learning cycle to model different scenarios in a spreadsheet.

So, we've got a problem.

Sofia's identified that the spreadsheet model shows we are going to make a loss on the prom.

So the model is showing that the income from the prom is not going to cover the costs.

And Jacob says, "That means we won't be able to have the prom, Sofia.

What can we do?" So, yeah, we've got a problem.

The prom needs to operate at least at break-even or make a profit to go ahead and at the moment it's showing that it's going to make a loss and we need to come up with a solution.

So, one thing we can do is we can use a spreadsheet to model for what-if analysis.

So, this is looking at variables like price or sales and changing them to see how the outcome changes.

So you could increase price or you could look for more sales, various different things you can change.

So this can really help you support decision making.

You can also use a spreadsheet model for something called scenario planning where you can test the best-case, worst-case, and average scenarios.

So that could be something like, if we sell the most tickets we expect to, this will be the outcome.

If we sell the least tickets we expect to, this will be the outcome.

And if we're somewhere in the middle, you can work out the outcome from that as well.

So, spreadsheet's really useful to model different scenarios and we'll have a look at that in this lesson.

A scenario is a version of a situation based on changing certain values.

So let's have a look at some of the values we could change.

So Sofia's identified, "We could see what would happen if we raised the ticket price for the prom." And Jacob has said, "We could also see what would happen if we raised more money with fundraising." So fill in the gaps to complete these sentences.

So we've got two gaps.

A blank is a version of a situation based on changing certain values.

And scenario planning can support blank making.

So can you fill in those two gaps? A scenario is a version of a situation based on changing certain values.

And scenario planning can support decision making.

So those are the two words we were looking for, scenario and decision.

We can also use data validation in spreadsheets.

So data validation is a way to prevent user error by limiting the data a user can input.

There are different types of data validation rules including these three.

So you could choose a list.

So the user chooses from a set of options like specific text.

So typically you see that in a dropdown menu where you can only select what's in that menu to fill in the cells.

You could use number range data validations.

This only allows values between two numbers to be input.

So it could for example, between five and 10.

So it'd be greater than five, less than 10.

Or you could use a custom formula.

So this is a little bit more advanced, like must be a number or must be a date in the future.

So there's various different ways you can prevent user error by limiting what a user can input.

You can also set up error messages if someone tries to put in any input, which doesn't meet the data validation as well to kinda help them and explain why their input's not being accepted.

So, data validation is a really useful tool.

So, one way of using data validation is creating a dropdown list, which I mentioned on the previous slide.

So, in the blue box you've got what's called a dropdown arrow and you've probably seen that in various different applications or websites.

They're quite commonly used as a way of selecting something from a list.

So this is a dropdown list and there's our dropdown arrow.

So once you click on the arrow, the list is opened up.

And from this list we've got three options.

We can choose 20, 25 pounds, or 30 pounds for the sell ticket price.

So that's the only thing with this dropdown list that can be put into that cell.

We can't put any other costs or any other data, so you can't put any words or any other numbers in.

So it must be either 20, 25, or 30 pounds as the only things that can go in that cell.

So first of all, we've got 25 pounds selected there.

So the middle of the three in the range was selected.

And the main benefit of a dropdown list is that you see fewer errors because the user can't make any typing mistakes.

So they can't accidentally type in two pounds 50 or 250 pounds 'cause literally you're just selecting from a list.

So it's a really good way of making spreadsheets a little bit more accurate and a little bit more reliable.

So here's how you can set up the data validation in a spreadsheet.

So, first of all, select cells you want to format.

So in this example we've got cells B2 through to B9.

So these are scores, looks like they're probably scores in a test or something.

So these scores are selected there.

So that's the range we want.

So cells B2 through to B9 are selected.

Next we click on data from the data from the menu at the top of the spreadsheet.

That's data.

And from there we choose data validation.

Now, depending on the spreadsheet application that you're using, it may look slightly different to this, but the general principles are similar.

Just to recap, select the cells you want to format, then click on the data menu, and then choose data validation from the list.

So that's data validation.

So once you've selected data validation, you need to just make sure that you've selected the right range.

So here we've got L4 data validation example.

So that's the tab that you're in and then the range is included there.

So it's cells B2 through to B9, which is the range we wanted.

So, that is correct.

So we've got the correct range here.

Okay, time for a question.

What is the main purpose of using data validation dropdown list in Excel? Is it A, to automatically sort a list of items? Is it B, to highlight incorrect values in a worksheet? Is it C, to allow users to select from a list of valid options? Or is it D, to convert text into numbers for calculations? So what is the main purpose of using a data validation dropdown list in Excel? A, B, C, or D? Well done, it's C.

So, the main purpose of using data validation in Excel is to allow users to select from a list of valid options.

Now, open your prom-planning spreadsheet.

If you don't have a version of prom-planning spreadsheet, you can download a version from the additional resources in this lesson.

So once you've opened the prom-planning spreadsheet or downloaded it and opened it, add a dropdown list validation to the ticket price for the sales that contain the standard, discounted, and staffed ticket prices.

The cell should only allow whole numbers between 10 and 35.

So from 10, 10, 11, 12, and so on up to the number of 35.

Once you've done that, use the spreadsheet model to explore these scenarios.

So the first scenario is what will be the profit loss if the ticket prices are raised to 30 pounds? B, if you raise ticket prices to 30 pounds, it's predicted that the standard ticket sales will reduce to 130 pounds.

What's the impact on the profit or loss? And C, the PTA have said they can advertise the quiz night and fun run.

What happens to the profit loss if 100 tickets are sold for the fun run and 100 tickets are sold for the quiz night? So there's three different scenarios that I want you to model in the spreadsheet.

And the fourth thing to model is that the school council are concerned that raising ticket prices to 30 pounds could be too expensive for students.

They think you should raise the ticket prices for staff instead.

So A, leave the fundraising figures and change the staff ticket price to 30 pounds.

And B, what's the smallest amount you can change for a standard ticket and still break-even? So, here are the answers,.

The profit loss if ticket prices are raised to 30 pounds, it will still be a loss this time of 120 pounds.

If you raise the ticket prices to 30 pounds it is predicted that standard ticket sales will reduce to 130.

So what's the impact of that and the profit loss? So you're gonna sell fewer tickets so as you'd expect the loss will increase.

So it will increase to a loss of 280 pounds.

And then C, the PTA said they can advertise the quiz night and fun run.

What happens to the profit loss if 100 tickets are sold for the fun run and 100 tickets are sold for the quiz night? So that's gonna generate some extra income and if that happens, the prom will make a profit of 170 pounds, so it will become viable.

So for question four, the school council are concerned that raising ticket prices to 30 pounds could be too expensive for students.

They think you should raise the ticket price for staff instead.

So the two things you're asked to do there is leave the fundraising figures and change the staff ticket price to 30 pounds.

And then what's the smallest amount you can change for a standard ticket and still break-even? And the smallest you could change it to was 26 pounds.

So if you have a staff ticket at 30 pounds and a standard ticket at 26 pounds, you will break-even.

Let's move on to the second learning cycle, that is to present information from a spreadsheet model.

So let's have a recap.

What is a chart? And Lucas has identified that a chart is a visual way to show data.

So we've got an example of a chart there on the left-hand side of the screen and here are some of the reasons why you would use a chart.

So instead of looking at numbers in a table, a chart helps you to see patterns, compare values, and spot trends.

Charts turn data into shapes and colours that are easier to understand.

And this is why charts are referred to as data visual.

It's a way of visualising data to make it easier to understand and interpret.

So this example that you can see here is a bar chart.

So bar charts are most useful when comparing values like the number of sales per product.

So in this case we've got a bar chart with gaming, sports, reading, watching videos, friends across the bottom, and then a numeric scale on the side.

So, the numeric scale going from 10, 20 to 30.

There are also pie charts and this is an example of a pie chart.

So pie charts are used to show how data is split into parts of a whole, like a percentage or total points scored.

Line charts are best used to show changes or trends over time like sales each year.

So in this case we've got changes of number of viewers over a period of minutes so that you can see the line is gradually increasing, showing that viewers were gradually increasing over that time.

It doesn't increase every single interval, but the general trend is that the number is increasing and the line helps us identify that.

The prom committee wants to show the breakdown of costs for the prom.

Which type of chart would be most suitable to use? Would it be A, a bar chart, B, a pie chart, or C, a line chart? Well done, a pie chart would be most suitable if you wanted to show the breakdown of costs 'cause we're looking at proportions of a whole.

Next question, the prom committee wants to show how many tickets have been sold each month from January to May.

Which chart would be the most suitable to you? So would that be a bar chart, a pie chart, or a line chart? Well done, a line chart is the best one for that.

So a line chart would show how ticket sales have changed over time.

You could show the same information in a bar chart but it would be less easy to identify the trend.

So, a line chart is the best option for that one.

So let's have a look at a function.

We've got the COUNTIF function, so that can be used to count how many cells meet a set criterion.

So we've got a couple of examples here.

So, =COUNTIF, and then in brackets B2 to B15, comma and then in speech marks is less than 10, and then close the bracket.

So this function returns how many cells in the range B2 to B15 contain a value less than 10.

So that's the first example of a COUNTIF function.

And then the second one is =COUNTIF and then we've got in brackets B2 to B15, and then the word "Blue" in quotation marks.

So this function returns how many cells in the range B2 to B15 contain the word blue.

Now, when you are using any function including the COUNTIF function, you've gotta be really careful to use accurate syntax.

So that's the symbols and the letters and the words and the numbers that you're using in this function.

So just bear in mind, you must use brackets.

There's no spaces between the function and the bracket.

You must use a colon to show the range of the cells.

So between B2 and B15, you need that colon to show its range.

Then you need a comma before you have the value.

So the top one is the value is less than 10 the bottom one the value is blue, and the value must be inside quotation marks.

And then you must close the bracket as well.

So, all of that syntax is really important.

And you also need the equal sign at the beginning and the COUNTIF in capitals.

All of that is really important.

You can now watch the video which demonstrates how to use the COUNTIF function.

(bright music) <v Presenter>In this video we'll learn how to use</v> the COUNTIF function.

The COUNTIF function is perfect for answering questions like how many products sold more than 150 units, like we're gonna look at in this example.

(bright music) So we're gonna count how many products are sold more than 150 units using the COUNTIF function.

And the data we'll be using is in the Units Sold column, column C.

So from C2 to C31.

We want to display the result in the cell J4.

So go ahead and click and select cell J4.

(bright music) To use the COUNTIF function, start by typing an equal sign.

This tells the software that you're entering a calculation.

Then type COUNTIF.

Open brackets.

Now click and drag to highlight cells C2 to C31.

(bright music) This is telling the function where to look.

Now, we need to type in a condition, (bright music) So, we want to type a comma.

So we've selected the range.

Now we need to put in the condition, the criteria for our search.

In this case, we wanna search for values that are greater than 150.

So we type double quotes great than which is shift and full stop on your keyboard and then double, and then 150 double quotes again, and close your bracket.

So let's recap this.

The completed formula now reads =COUNTIF.

(bright music) And then in brackets is our range, C2 to C31.

That's the range, but this time we're adding a criteria to that.

So we add a comma to separate the range and the criteria.

The criteria in this case we put in double quotes.

Then we type the greater than symbol followed by the value.

So we're only counting the number of cells that contain a value greater than 150.

(bright music) Once you're confident that your formula is correct, you press Enter.

We can see that the number of products sold more than 150 units equals 11.

So it's counted the number of cells that has a value greater than 150, does not add or count the values within those cells.

It's just counting the cells and how many cells have a value greater than 150.

So please make sure that you understand that.

This is a really quick and easy way to count data that meets a specific condition.

So using COUNTIF you can answer questions like how many items sold more than 150 or how many scores were above a certain number, how many students scored more or greater than 80% in their test and so on.

It saves time and it makes analysing your data so much easier.

So now it's your turn.

Use the COUNTIF function in cell J4 to count the number of products with sales greater than 150 units.

Good luck.

(bright music) <v ->To replicate a formula in a spreadsheet,</v> follow these steps.

So I'll talk through the steps first and then I will talk through the animation as it runs.

So first of all, once you've entered the formula, move your mouse to the bottom right corner of the cell until you see a small black cross.

This is the fill handle.

Click and drag the fill handle down to fill a column or sideways to fill a row, and then release the mouse and the spreadsheet automatically copies the formula to the new cells.

So I'll talk through that as it happens.

So, there's a formula going in there.

So click on the cell and the fill handle.

Drag down, release the mouse, and there we have it copied and the column.

So I'll show you that one more time.

So there's the formula going in.

Click in the cell where the formula is.

Grab the fill handle there in the corner, pull it down, and it's copied it down to C3, C4, C5, and C6.

So that's how you replicate a formula in a spreadsheet.

When you use the full handle to replicate a formula, the cell reference changes automatically and this is because formulas use relative references by default.

So a relative formula changes when moved or copied to another cell.

So in the previous example, the formula was in C2 and when it was dragged down it changed to C3, C4, C5, et cetera.

So this is useful when you wanna do the same calculation across rows or columns.

Relative formulas automatically update based on their position.

Sometimes though you want a cell to say fixed in a formula, like the ticket price.

So you don't want that to change.

And Andeep identifies that you'll need an absolute cell reference for this.

So let's have a look at how you can grab an absolute cell reference.

So an absolute reference is a way of keeping a cell reference locked even when it's replicated.

You mark a reference as an absolute reference by adding the dollar signs.

So here's an example of an absolute reference.

So it's =B2 multiplied by $B$6.

So this formula will always use the value in B6 even if the formula is replicated using the fill handle.

So, the first dollar sign is locking B, which is the column, and the second one is locking six, which is the row.

So the column and the row are both fixed.

So, $B$6.

That formula no matter how many times you replicate it in other cells will always reference B6 because that's an absolute reference.

So there's confirmation of what we've just been through.

So the dollar sign in front of the letter locks the column and the dollar sign in front of the number locks the row.

And Lucas says, "Using both dollar signs locks both the row and the column.

And this is a full absolute cell reference." So absolute cell references are really, really useful.

Here's your task.

Open the spreadsheet, prom-ticket-sales.

xls, that's prom-ticket-sales.

xls.

So two, the number of tickets sold cells are currently empty.

Use the COUNTIF functions to count how many of each ticket type have currently been sold.

The tickets sold are contained in the tickets_sales sheet.

So third, create a pie chart to show the breakdown of costs for the prom.

Four, the school council want to analyse how many ticket sales were sold each month.

Use a COUNTIF function to show how many tickets were sold each month in the table provided on the ticket_sales sheet.

And five, create a line chart to show how many tickets were sold each month.

So this is what your COUNTIF function should look like.

So it should be =COUNTIF.

And then inside brackets, ticket_sales!$C:$C, and then in quotation marks "standard," and closed bracket.

So there's your COUNTIF function.

The prom costs are best represented in a pie chart.

So we've got the proportion of the overall costs here represented in a pie chart.

And you see most of the costs are coming from venue hire and then the various other different percentages of the other costs.

And then for ticket sales versus month, a line chart is useful.

So first of all, you'll need the COUNTIF function to grab the data.

So the COUNTIF function is =COUNTIF, and then brackets $B$2:$B$180, so 180,E5, so there's our COUNTIF function.

And then that's best displayed in a line chart.

So we've got a line chart there showing the trend of ticket sales.

So you can see there was an initial peak in January, then it fell away in February and March and April, and then began rising it again in May.

So you can see the overall trend of ticket sales represented in that line chart.

Let's have a look at a summary of this lesson.

A spreadsheet module can be used for what-if analysis variables like price or sales can be changed to see how outcomes change.

Different types of charts are suitable for different purposes, such as bar charts for comparisons, pie charts for showing parts of a whole, and line charts for changes over time.

And the COUNTIF function can be used to count how many cells meet a set criterion.

This is helpful when generating totals for graphics.

That's the end of this lesson.

Thank you so much for joining me for this lesson and for this unit.

I do hope to see you again soon.