Welcome to this Oak National Academy lesson, which is called Spreadsheet Project One, and it's taken from the unit Spreadsheets.

Thank you for joining me for this lesson and I look forward to creating a project with you today.

The outcome of this lesson is that you'll create a spreadsheet model for an event to investigate income and costs.

So we've got a number of keywords for this lesson, and they're related to income and costs and budgeting in a spreadsheet.

So some of these you may be familiar with, some of you may not.

Don't worry if you're not.

We will be picking up these terms as we go through the lesson.

So the first keyword is fixed cost.

Now, fixed cost is something that will stay the same no matter how many people attend the event which you're going to be planning.

So variable cost changes depending on how many people attend the event.

That's variable cost, then we have profit.

So profit, it's the amount of money made us subtracting all the costs from the total revenue.

And then we have loss.

So a loss occurs when the total costs are greater than the total revenue.

And then finally, we have break even.

So break even is the point at which the total revenue equals the total costs.

So there's our five keywords or phrases.

So we have fixed cost, variable cost, profit, loss, and break even.

We've got two learning cycles in this lesson.

First of all, you're going to format data using spreadsheet tools.

That's our first learning cycle.

And the later in the lesson, you're going to calculate profit using spreadsheet tools.

So let's get started now with that first learning cycle, format data using spreadsheet tools.

So here are some of the key features of a spreadsheet model.

So a spreadsheet model will contain data, so that's information entered into the spreadsheet.

So it could be things like price, quantities or dates for example.

A spreadsheet model will also have formulas.

So formulas are instructions that tell the spreadsheet how to calculate something, that could be totals or profit or averages and lots of other things.

And a spreadsheet model will also have rules.

So they are guidelines or constraints used in the model.

So that could be something like a budget or a stock limit.

So here's the scenario you are going to be planning.

So Oak National Academy are having their annual prom for year 11 levers.

The prom committee wants to create a spreadsheet model to explore the costs and income to ensure that they can generate enough income to cover the cost of the prom.

When hosting an event, there will be both fixed and variable costs.

So a fixed cost is something that will stay the same no matter how many people attend the event.

For example, venue hire will be the same whether you have 50 or 150 students attend.

So a thing like a fixed cost, you need to sort that out upfront before the event itself.

So you book a hall, you might anticipate that 150 students attend, but if only 50 attend, it's gonna cost you exactly the same.

So that's an example of a fixed cost.

A variable cost changes depending on how many people attend the event.

So catering is a really good example of a variable cost.

The more people you have attending, the more you are going to need to spend on catering.

So the catering is generally worked out as a price per head.

So that's a variable cost.

So your event is gonna have both fixed and variable costs.

So the problem committee have done some initial investigation with local suppliers and the fixed costs are now known, so it's gonna cost 1,000 pounds to buy the venue.

The DJ is gonna cost 400 pounds.

Decorations will cost 150 pounds.

There's gonna be a photo booth which costs 250 pounds, and then lighting we needed, and that's 150 pounds.

These are all fixed costs.

They won't change no matter how many people attend the prom.

So these costs are completely fixed.

They've also done some investigation into variable costs and they've worked out that catering for each student attending will cost 20 pound a head food and two pounds a head for drink.

And these are variable costs 'cause the amount you spend will depend on the number of students who attend the prom.

So time for a true or false question, a fixed cost changes depending on how many people attend the event.

So is that true or false? Well done, it's false and it's false because a fixed cost is something that will stay the same no matter how many people attend the event.

The prom committee have surveyed students about how much they'd be willing to pay for a ticket.

The initial response that students would expect to pay around about 20 to 30 pounds for a ticket.

Some students will be eligible for a discounted ticket rate and staff will also pay a discounted rate as they will be supervising the event.

For initial planning, the prom committee have decided on these ticket prices.

So they've said a standard ticket will cost 25 pounds.

A discounted ticket will cost 15, and a staff ticket will cost 10 pounds.

So in a spreadsheet, you can organise and format data to make it clear what is in each cell.

So Sam points out, each cell in a spreadsheet can have a data format, for example, which is like a label.

So it could be something like currency and Andeep adds, "Each cell in a spreadsheet can be formatted so that it's clear what's in each cell and it looks neater too as well." So there's a couple of ways you can do formatting.

You can format the data, you can also format the appearance.

There are lots of different data formats you can choose.

Here are some of the common formats that you might want to use.

So plain text is one you'll definitely be using in this example.

So plain text could be data format for letters or words.

So we've got an example there of trainers, just the word trainers, that's in plain text.

Number is a data format for numbers.

So there's an example, number 10.

5.

Percent is a data format for percentages.

So a number followed by a percentage.

So 50% here in this example currency is a data format for money.

So this is in pounds sterling £12.

50, that's in a currency format.

And then we have a date format, so a date format for dates for the date month and year.

So this one is 25th of the 12th, 2024.

So it's in day, month and year order.

Some date formats will be in other orders depending on how you want the date presented.

So it could be that you just want the month and the year for example, or just the day and the month.

But here we've got the day, the month and the year, and time is a data format for times with hours, minutes and seconds.

So a question about data format.

So what would be the most appropriate data format for the ticket price of the prom? Would it be A, a number, B percent, C currency, or D, plain text? Yep, that's correct.

Currency would be the most appropriate data format for the ticket price of the prom because the ticket price will be in money and money is represented by currency.

So moving on to creating your spreadsheet.

So sometimes text and spreadsheet cell can be too big for a cell.

So this example cells G2, H2 and I2 have been merged together into one cell.

The text formatting will apply across all the cells and that will be referred to as a range, so in this case, G2 to I2.

And that's using the merge cells tool, which you can see on the toolbar of your spreadsheet application.

So sometimes, depending on which spreadsheet application you're using, the merge cell may only be referred to as the first cell.

So in this example it might just be G2 instead of the range G2 to I2.

So just look out for that depending on which spreadsheet tool you are using.

You can also add borders to cells to make the cells stand out or show the table more clearly.

Once again, there's a menu for borders, so it's kind of like, it looks like a little window with a cross in the middle of it.

And you've got various different border styles you can apply to your cells.

So the two little icons on the right, you've got the pen icon, which sets the thickness of the border, and then the icon with the solid line and then the various different grades of dotted lines that sets the appearance of the border.

So you could have a thick dotted line or a thinner solid line or any combination.

So look out for that.

And again, that might look slightly different depending on which spreadsheet application you're using.

Okay, so now we have the formatting applied to this example.

So the title now has a thick black outer border, and as I said previously, you can change the thickness and colour of these borders here as well.

So task A for this lesson, the prom committee has started adding data to the prom planning spreadsheet.

However, they need some help.

They need some help to add formatting to improve the presentation of the spreadsheet.

So here's your instructions.

So one, open the spreadsheet prom-planning.

xls.

So I'll just say that again.

Prom-planning.

xls, apply the currency format to any appropriate values in the spreadsheet.

So third, merge the title across the cell range A1 to H1 and fourth, add any other formatting of your choice.

So this is what your spreadsheet could now look like.

So you could see we've got some formatting applied to cells.

So the title cells have got a black background with a white bold text, so they really stand out.

You've got the totals in red at the bottom of each of the smaller tables.

And then you've got profit and loss in bold as well, and you've got some merged cells at the top for the top line.

So the title Oak Academy Prom Planner is merged across all those cells at the top.

That's what your spreadsheet may look like now with formatting applied.

We can now move onto the second learning cycle in this lesson.

So that is to calculate profit using spreadsheet tools.

So let's get started with that.

The prom committee have been informed that the prom must make profit or at least break even.

The one thing it must not do is make a loss.

Profit is the amount of money made after subtracting the total expenditure, so that's the costs, away from the total revenue, which is the income.

It's calculated by the formula profit equals revenue minus expenditure.

Break even is the point at which the total revenue equals the total costs, meaning you neither make a profit nor a loss.

So you spend exactly the same amount as you get in.

So that's the break even point.

Total expenditure, revenue and profit can be calculated in a spreadsheet using formulas.

Here is a reminder of some common operators used in spreadsheet formulas.

So the operator for add has the mathematical symbol of a plus sign, and that's the same as the spreadsheet.

And that's the same as the spreadsheet operator, which is also an add sign.

Then we have subtract, which is kind of like a hyphen or a minus sign.

So that again is the same as the spreadsheet operator.

So that minus sign there.

So multiply is slightly different.

So multiply, the mathematical symbol you may use is kind of like an X or a times sign, here it called.

So the spreadsheet operator for multiply is an asterisk.

So depending on your keyboard, that's usually shift and eight.

And then divide, which is represented by the line with the dot either side as a mathematical symbol is a forward slash in a spreadsheet operator.

So the main ones that are different are multiply and divide.

So it's the asterisk for multiply and the forward slash for divide.

Okay, time for a true or false question, profit is the same as total revenue.

Is that true or false? Well done, that's false.

And the reason why it's false is profit is the amount of money made after subtracting the total expenditure or the costs from the total revenue or the income.

So profit is not the same as total revenue, it's a completely different thing.

Right, moving on to formulas.

Formulas in spreadsheets always start with the equals sign.

This tells the spreadsheet that you want it to perform a calculation and that you're not just entering text or numbers in.

So when writing a formula in a spreadsheet, it's best to use cell references instead of typing values indirectly.

This way, if the values in the cells change, the formula will automatically update the answer.

So it makes things happen automatically without you having to update lots of different cells.

We'll look at some examples of that in a minute.

A function in a spreadsheet is a ready made instruction that performs a specific mathematical job.

So functions are used within formulas.

They take time by doing calculations instantly, reducing errors and making more complex tasks easier.

You should use preset functions wherever you can.

It just makes life a lot easier.

So here's a couple of examples of functions that will be useful.

So sum will add up all the numbers in a range with just one command and average will work out the mean of the numbers in a range.

Again, just with one command.

So you're not having to go through every single number in that range to find either the sum or the average.

The function does all the hard work for you.

So what formula or function would you use in cell B8 to calculate the total fixed costs? There's two ways you could do this.

You could use a formula, which would be equals B3 plus B4 plus B5 plus B6 plus B7.

Or you could use a function which would be equals sum, and then in brackets B3, colon, B7, close brackets.

So there's probably good reasons here why you'd want to use a sum.

It's a little bit more efficient than typing out all the different cell references.

And if you then changed it so that you added an extra line in, it'll be easier using the sum 'cause depending on where you added that line, it might do it automatically for you.

So sum is probably the preferable way to go with that one.

Okay, let's move on to task B.

So go to the prom planning spreadsheet you used for task A and add formulas or functions to calculate the following.

So A, the total fixed costs.

B, the total variable costs, C, the total costs, so that's fixed costs plus variable costs.

D, the total income from ticket sales and E, the profit or loss for your event.

And your second part of the task is what do the calculations tell the prom committee about the event? Are they currently making a profit or a loss? And the third part of the task is, what actions could be taken to increase income for the prom? So there's your three tasks.

So here's what your spreadsheet should now look like.

So your total fixed costs should be 2,150, and your total variable costs would be 4,620.

So that's based on the ticket sales we've got there at the moment.

So 150 standard tickets sold, 40 discounted tickets, 20 staff tickets.

So your total costs are 6,770 pounds.

And then in the income part, so from standard tickets you're getting 3000 pounds.

The income from discounted tickets is 600 pounds, and from staff tickets it's 200 pounds.

Then in addition, you expect to raise from fundraising 100 pound from raffle ticket sales, 800 pounds from a fun run and 250 pounds from a quiz night.

So overall, if that was the model you went with, you would make an overall loss of 1,820 pounds.

So currently with those figures, the prom is operating at a loss.

So here's a summary of what I just went through on the previous slide.

So the prom is currently making a loss of 1,820 pounds.

This means that prom can't run as the committee has been told that the event needs to be at least break even.

So we've got a bit of a problem.

So what actions could be taken to increase income from the prom to solve the problem? So the prom committee could charge more per ticket to increase income.

They could see if they could raise more money through fundraising or reduce expenditure by reducing some costs.

There's a couple of ways you could increase the income.

Here's the summary of this lesson.

So you've created a spreadsheet model that can be used to model a real world event.

A spreadsheet can be used to calculate if an event will make a profit or loss or break even, and a spreadsheet model can help the organisers of an event to decide what they should charge or whether they need to reduce costs.

Thank you for joining me for this Oak National Academy lesson today.

I hope you've enjoyed learning how to use a spreadsheet model and I hope to see you again very soon.