Hi, my name's Mr. Hall, and welcome to this Oak National Academy lesson, which is called Improving a spreadsheet model, and it's taken from the unit Spreadsheets.

I'm really pleased you've joined me for this lesson, and I look forward to learning with you today.

Let's get started.

The outcome of this lesson is I can use a range of tools to improve a spreadsheet model.

We've got two keywords in this lesson.

We've got model, and a model is a set of data, formulas, and calculations used to test different situations.

And then we've got macro.

A macro is a recorded set of steps in a spreadsheet.

So the two keywords are model and macro.

There's two learning cycles in this lesson.

So the first learning cycle is that you will use a spreadsheet to model scenarios, and then we'll move on to using a macro to automate tasks.

So now we can move on to the first learning cycle: use a spreadsheet to model scenarios.

So first, we'll start by thinking about a question, which is what is a spreadsheet model? And there's a couple of suggestions here from Sofia and Andeep.

So a spreadsheet model, according to Sofia, is used to represent a real-world situation.

And Andeep adds it uses data to help you understand a real-life scenario and to make plans.

Here are some key features of a spreadsheet model.

So it will feature data, so that's information entered into the spreadsheet, such as price, quantities, dates.

It will include formulas.

So formulas are instructions that tell the spreadsheet how to calculate some things.

So it could be totals, profits, averages, amongst other things.

And then it will have rules.

So rules are guidelines or constraints you used within the model.

So that could be things like budgets or stock limits.

So that's some key features of what you might see in a spreadsheet model.

So this is a spreadsheet model for a Rockstar Challenge live show event.

And this model tracks seating prices and discounts, seats sold and income, and seat allocations.

If you open the spreadsheet file, "RSC live show event", you'll see a version of the spreadsheet in a spreadsheet package.

So if the data in the spreadsheet model is changed, the spreadsheet updates automatically.

And Sofia and Andeep explained a little bit more about that.

So Sofia says, "If a student seat is sold, the total seats sold value will change." And Andeep says, "The income from selling a student seat will correctly be added to the total income as well." So as things change, you'll see the number of seats sold change and the income change.

So it's giving you really, really useful information about that live event.

So time for a question.

So what is one benefit of using a spreadsheet model? Is it A, you don't have to save your work, B, it helps you see the impact of changes quickly, or C, it automatically sends emails.

So which is one benefit of using a spreadsheet model? A, B, or C? That's right, the correct answer is B.

It helps you see the impact of changes quickly.

An example of that is if the price or discounts change, the spreadsheet model will adjust the other data on the sheet.

And it does this by using formulas.

So formulas are used to instantly update numbers and see changes.

This means the spreadsheet model is flexible, so it makes it easy to test different ideas.

You can change the values or you can change the formula.

But once you change one or the other, everything else will change accordingly.

Time for a true or false question.

Spreadsheet models only show past data and cannot be changed.

Is that true or false? That's right, it's false.

And can you explain why? And it's false because models are useful because you can change the data to test different situations.

So it's the exact opposite.

You definitely can change data in a spreadsheet model so that you can change scenarios.

Sometimes, you need to reset the data in a model.

So it can happen when you want to test different situations quickly.

In this seating plan, it would take a lot of effort to manually reset all the cells.

So we've got a number of seats that have been sold there, you can see on the diagram.

And if you had to reset those all back to where they started, you'd have to do it with every single individual cell that's got a letter in it.

And there's a lot of them.

Also, you might clear data from the wrong cell ranges, or you might have to retype data.

So it's a really inefficient way of making changes.

To help you, you can use something called a macro.

So a macro can be used to help you improve the model and save time.

So in this model, when the Reset button is clicked, a script is run, which resets the values in the seating plan.

So instead of going through all those different cells one by one, it's just one click of a button.

Now it's time for task A.

So open the spreadsheet file, RSC live show event, and use the sheet to calculate the answers.

Reset the sheet in between questions.

So here's your questions.

Number one, sell all the seats in first class to adults.

What's the total income? Question two, what's the total income if half of the premier seats are sold to students and the rest of the premier seats are sold to over-60s? And here's the answers you should have.

So if you sell all the seats in first class to adults, the total income will be 420 pounds.

And then to answer the second question, you'll need to reset your data using the reset macro.

And the answer for the second question, what's the total income if half the premier seats are sold to students and the rest of the premier seats are sold to over-60s, the total income there would be 200 pounds.

Next question, number three, a group booking has 10 adults and four students.

They want to sit in the same tier of seating and have a maximum budget of 220 pounds.

So A, which tier or tiers are they able to book, and what would the total cost be? So the answers to that one, they can book in either standard for a total cost of 130 pounds or they could book in first class for a total of 195 pounds.

They can't sit in premier as the total cost will be 260 pounds, and that's above their budget of 220 pounds.

Next question, number four, a family wants to sit together in premier seats.

There are two adults, three students, and one person over 60.

They have a budget of 80 pounds.

Can the family afford to sit in premier? If they cannot sit in premier, can they afford to sit in first? Let's have a look at the answers.

So if they're in premier, they will be paying 40 pounds for the adults, 45 pounds for the students, and five pounds for the over-60s.

So that will be a total of 90 pounds.

So their budget is 80 pounds, so that's above their budget.

However, if they sit in first, it'll be 30 pounds for the adults, 33.

75 for the students, 3.

75 for the over-60s, making a total of 67.

50, so they can afford to sit in the first seats.

Let's move on to the second learning cycle.

And this time you're gonna use a macro to automate tasks.

So a macro is a set of steps that the spreadsheet records and can repeat for you.

And they're commonly used for tasks, such as deleting test data, resetting a form, or formatting cells.

Let's find out a little bit more about them.

So Lucas says, "Macros can save time by quickly performing repetitive tasks in a spreadsheet." Sam says, "They can also help you avoid mistakes in your work." And finally, Andeep adds, "Macros can automate boring or repeated steps so you don't have to do them yourself." So back to Rockstar Challenge.

Rockstar Challenge is running a competition.

One person will be picked at random from a list of 10 finalists to win a free ticket to the live show.

Open the spreadsheet, RSC competition.

You are gonna create a macro that will select the winner.

So here are the steps you need to follow to create a macro.

So first of all, click on the Extensions menu, then click on Macro, and then choose Record macro.

So three steps, click on the Extensions menu, click on Macro, and then choose Record macro.

The spreadsheet software's record macro feature lets you save a set of steps so that you can repeat them later with just one click.

It watches what actions you do, such as formatting, typing, or clearing cells.

And as you do them, it records them.

The spreadsheet saves these actions as a macro.

You can then play back the macro to do those steps again automatically.

So instead of having to go through all the steps you did when you recorded the macro, it's just one click.

In the Rockstar Challenge Competition sheet, there are two tabs.

The tab labelled Names contains the names of the 10 entrants.

They need to be randomised.

When the macro is recording, in the Names tab, select the range.

So select the whole range of names from cell A1 through to A10, then click on Data, and then choose Randomise range from the data menu.

And it's right down there at the bottom.

Randomise range will use an algorithm to randomise the order of the cells in the range.

So here you can see them randomised, so before and after.

So now they're presented in a randomised order.

Once the randomise range action has executed, make sure you go back to the first tab.

This is so that when the user uses your macro, they're returned to the starting sheet after the randomise range action is finished.

So it just finishes off the macro.

So they go click back in the template tab.

Once you've done that, select Save, and name your macro.

Now the name of your macro is important.

You want to name it something that's recognisable so that when you want to call that macro, you can remember what it is.

So this one we've called random_range, and you'll see that we've used an underscore and not a space.

So you can't use spaces in the name of a macro.

So use something like an underscore to indicate where there should be a space.

So once again, this one's named random_range, and you'll see in a second why it's useful to choose a name which you can remember easily and is meaningful, as well.

So once you've done that, click Save.

And there you go.

So time for a question.

What does the record macro feature do? Does it A, record audio notes for your spreadsheet, B, it locks your spreadsheet from editing, C, it saves a series of actions to be repeated automatically, or D, it shares your sheet with others.

Well done.

The correct answer is C.

So the record macro feature saves a series of actions to be repeated automatically.

So the next step is to create a button, which the user can press to randomise the name.

So you can see there there's a Roll button next to the lucky winner there.

So that's what we're gonna have a look at doing now.

Here's how you create a button.

Click on the Insert menu and choose Drawing, which is down there at the bottom of the menu.

And then use shapes and texts to create the button.

So we've got the shape of a button there and you can add text once you've chosen the shape.

So there's our button with a roll written on it.

So we've got the image of the button and the word roll written on the image.

And now what we need to do is link the macro to the button.

And here's how you do that.

First of all, you select the button, so you can see it's got the handles on it to show you've selected it.

Then you press the three-dot menu.

So it could be quite difficult to see that, but there's just a really small three-dot menu in the top right hand corner once you've selected it, just inside the handles.

And then select Assign a script.

And this is where the naming becomes important.

So the script you want to assign is random_range.

So if you've given it a really long name, which you can't remember or something unrelated to what the macro actually does, you may face some problems this time.

But because this one's been named sensibly, we've named it random_range, we know exactly which script we want to run.

So type the name of the macro that you created and press OK.

So there's random range in there, and this will assign your macro to the button.

When the user presses the button, the macro that you recorded will run.

This will reorder the name list and produce a new name.

If we click on Roll, it will change to a new randomised name.

So fill in the blanks in this sentence.

A blank can be used to automate many different tasks in a blank.

After recording a macro, you can assign it to a blank, so users can run it with one click.

And the words you've got to fill in the gaps are spreadsheet, button and a macro.

Let's fill in those blanks.

So a macro can be used to automate many different tasks in a spreadsheet.

After recording a macro, you can assign it to a button, so users can run it with one click.

Now move on to task B.

So open the spreadsheet, RSC competition, and you've got three tasks.

So first, record a macro that will randomise the range of names.

Second, create a button and assign your macro to the button.

And then third, test the button.

It should randomise the name list and then return to the first tab.

So this is what your spreadsheet should look like.

So you should have the Roll button, next to the randomised name.

And each time you click on the Roll button, you should get a new randomised name.

Now I'll take you through the process of recording and assigning a macro in a spreadsheet package.

So I'm doing this in Google Sheets.

It looks similar in whichever spreadsheet package you are using.

First of all, what we need to do is start recording.

So to do that, I'm gonna click on Extensions, Macros, and Record macro.

So now the macro is recording, so anything I do now is gonna be recorded.

So first thing I want to do is go into the Names tab.

Now the names are already selected in my example, but I need to deselect and just select them again so that it's recorded in the macro, otherwise they wouldn't be selected.

And then I'm gonna click on Data and Randomise range.

And you'll see they will change order once I click on this.

So there I've randomised the range.

So that's pretty much everything we need to do to record the macro, except one thing, we need to go back into the template so that it doesn't keep switching back to the other tab.

So I'm gonna save that macro now.

And this is where we need to give it a sensible name.

So I'm gonna call it random range.

So that is exactly what we're doing and I can remember that.

Click on Save.

So that's our macro recorded.

Now the next thing we need to do is add a button and then assign the macro to the button.

So to add the button, I'm going to click on Insert, Drawing.

And under the Shapes, we've got the button shape there.

So there's our button.

I'm gonna fill it yellow like you saw in the slides and add some text.

So just to the middle, just gonna make that box, whoops, make that box smaller and pop it into that, into the middle.

There we go.

Click the word roll.

Just move that over.

Okay, so there's our button with the word roll on it.

Now I can save that, and it's gonna insert it into the spreadsheet a little bit big at the moment.

So I'm just gonna, whoops, just gonna make it smaller.

Drag it next to where we want the name to appear.

So at the moment, that's not gonna do anything 'cause I've not assigned the macro.

So if I click on it, all it'll do is bring it back into the drawing itself.

So what we need to do is assign the macro to it.

So there's the three small dots there, which we click on, and we're going to assign a script.

And this is where your naming is important.

So the script we want to assign is random_range.

Okay, and now let's test it.

When we run the script, there we go, the name changes.

So I just run that again.

So it tells me it's running and it changes the name, so it's randomising the name.

It'll do that every time I click on the button.

So that's how you record a macro, add a button, and assign the macro to the button.

Fourth part of your task, a teacher has a markbook stored on a spreadsheet.

What tasks could they use macros for? Here's some examples.

A teacher could use a macro to clear all the test scores at the end of term without deleting each one by hand.

They could also make a macro that highlights the top scores in bold or a different colour.

A teacher could use a macro to quickly add a new row for each student on a sheet.

Another idea is making a macro that hides all the columns except for one for the current subject, so it's easier to focus.

So those are just three ways a teacher could use a macro in a markbook.

So that's the end of today's lesson.

So let's have a look at the summary.

A spreadsheet model is used to represent a real-world situation.

It uses data to help you understand a real-life scenario and to make plans.

A macro is a recorded set of actions that can be repeated automatically in a spreadsheet.

Macros can be recorded using built-in tools and assigned to buttons for easy access.

Using macros can make spreadsheet models more efficient and easier to use.

Thank you for joining me for this lesson today.

I hope you enjoyed learning with me, and I hope to see you again soon.