Hello, my name is Mrs. Holbrough and welcome to Computing.

I'm so pleased you've decided to join me for the lesson today.

In today's lesson, we'll be starting the execution stage of a project.

You'll be developing a spreadsheet for the Delicious Desserts project by using formulas and functions.

Welcome to today's lesson from the unit, Using IT in Project Management.

This lesson is called Carrying Out a Project, Part One, and by the end of today's lesson, you'll be able to edit the structure of a provided spreadsheet workbook and apply functions and formulas to each sheet.

Shall we make a start? We will be exploring these key words throughout today's lesson.

Let's take a look at them now.

Workbook, workbook.

A file in a spreadsheet package that is made up of multiple worksheets.

Worksheet, worksheet, a single sheet within a workbook made up of cells.

Formula, formula, simple calculations within a spreadsheet.

Function, function, advanced calculations within a spreadsheet.

Look out for these keywords throughout today's lesson.

Today's lesson is broken down into two parts.

We'll start the execution stage of a project and then we'll move on to use formulas and functions in a spreadsheet.

Let's start the execution stage of a project.

Let's have a look at the preparation for the integrated system.

What information do you have that will support the development of the integrated system? Maybe pause the video whilst you have a think.

Your smart goals should have helped you to identify what you need to include in your workbook.

These include stock control, running costs, dessert costs, daily, weekly, monthly and annual profit and loss.

What can you use these for? We can use these for sheets in our workbook so you can see that they've all been named along the bottom.

So we have front page, stock and dessert costs in this example.

Using a single spreadsheet workbook with different sheets can help organise all of the data in one place.

It's important to give the sheets in your workbook names that relate to the purpose of each sheet so people understand where to find the information.

The template you will be given already has these sheets in it.

So you can see we have front page stock, dessert costs, running costs, sales and annual profit and loss.

What do you think will be found on the sale sheet? As a hint, have all the headings that were identified in the SMART goals been used? Pause your video and have a think.

Okay, let's have a look at this sales sheet in more detail.

It contains daily, weekly, and monthly sales.

It will be completed on a daily basis.

Data from previous days will be stored in this sheet.

Data from previous weeks will be stored on this sheet.

Data from previous months will be transferred to the annual sale sheet and stored.

Let's now look at the costs breakdown data.

Sometimes the data you have been given can cause complications when using formulas or functions in the workbook.

What is the issue with the data provided on the cost breakdown worksheet? Have a look carefully at the data provided on the cost breakdown worksheet and think about what issues this might cause.

You can pause the video if you'd like.

In order to have accurate pricing, the values used to measure the costs must be the same.

So if we look back at the previous example, you can see we have some things that are measured in kilogrammes, some things that are measured in grammes, some things that are measured in litres and things like that.

The workbook template converts the measurements in grammes and millimetres to kilogrammes and litres to save you time.

The first thing you need to do before applying formulas and functions is to apply formatting to the workbook to create a house style.

A house style is a bit like a theme or design that's used across a business organisation.

Think about the font choices, font size, text and background colours that you're going to use.

By creating a house style, you can ensure consistency across the project and reinforce the brand image.

Let's have a look at some formatting tools in Sheets.

So here I've got my menu bar.

If we zoom in on this section, we have the number format for individual cells.

Remember, where costs are calculated, the cells must be formatted as currency and we can apply that using the pound sign on this menu bar.

Let's have a look at some more tools.

This time we are zooming in on the font and the text colour and size.

Font formatting, the default setting is Arial size 10.

Highlight the sheet areas and change the format to your house style.

These are now the cell formatting tools.

These tools alter the cells' colour and grid lines and let you choose whether the number of cells can be merged together or not.

So a merge cell will join multiple cells together.

These tools are the alignment tools.

They adjust the position of the text within cells, whether the cells should have wrapped text and the direction of the text.

Wrapped text means that the text will go down to more than one line if needed.

Time to check your understanding.

Which of these icons affects the colour of a cell? Is it A, B, or C? Pause the video whilst you have a think.

Did you spot it? Well done, both A and C will affect the colour of a cell.

A will affect the fill colour and C will affect the border colour.

Time to check your understanding with a question.

Why would a business choose to use a house style across all of its documents? Is it A, to ensure consistency, B, to look similar to competitors or C, to reinforce the brand image, pause the video whilst you think carefully about your answer.

A business would choose a house style across its documents to ensure consistency and to reinforce the brand image.

It's useful for the client to be able to easily access the sheet that they need to work on or view.

On the front page, use the merge cells to create a link to each of the other sheets.

How do we insert a hyperlink? So we type in the name of the sheet we want to link to.

So the first one here is stock.

With the cell selected, we then go to the insert menu and select link.

So from the insert menu, choose link.

We then click on sheets and name ranges and we choose the sheet we want to link to.

So in this case, we're linking to stock.

Okay, we're moving on to our first task of today's lesson.

Task A, I'd like you to open the provided spreadsheet, Delicious Desserts.

XLS.

I'd then like you to apply a house style to the workbook template.

So you can do this by changing the font styles, changing the colours, and changing any formats that you need to.

And then for part three, I'd like you to create hyperlinks to the following sheets on the front page.

So we need a hyperlink for stock, dessert costs, running costs, sales and annual profit and loss.

Pause the video whilst you complete the tasks.

Okay, so we've started the execution stage of our project now.

Let's now move on to use formulas and functions in a spreadsheet.

Now that you have formatted the worksheets, you'll start to use formulas and functions to complete calculations.

Functions like SUM and IF can help you do calculations quickly and accurately.

You will need to refer back to the information in the project brief.

So here's a reminder about some basic ingredients that must be available daily.

So for example, we need 16 kilogrammes of plain flour, 2.

27 litres of milk, et cetera.

There's also some additional ingredients, so things like ice cream, one litre, apricot topping, one kilogramme.

You also need the information about the recipes.

So here are the recipes for the waffles, the basic cookie dough recipe and the cheesecake recipe.

There are some other costs that you also need to be aware of.

Staff wages have an annual cost of 63,000, that's per year.

Utility bills are 32,790 and rent is 12,000 pounds a year.

The most popular desserts are strawberry cheesecake, chocolate cheesecake, chocolate fudge waffle, strawberry waffle, and triple chocolate chip cookie dough.

Here is the stock sheet.

So we've got one item in here at the moment, which is plain flour.

So the cost per kilogramme or litre is 62 pence.

The minimum stock we need is two, and then we've got a column for purchase more.

We've got a column for current stock.

So at the moment that's five.

We've got a column for daily use and we've got a column for remaining stock.

So we need to find the remaining stock using a formula.

To do this, you need to subtract the daily use from the current stock.

The sales highlighted in blue are the ones where formulas, functions and conditional formatting will need to be applied on this sheet.

Which cell will you type the formula into? Maybe pause the video whilst you have a think.

Once we've done a formula and we've added it correctly, we can use the drag handle to replicate a formula across multiple ingredients.

So once you have entered a formula, it's an advantage to use the drag handle of a cell to replicate or copy the formula down a column.

So the drag handle is in the bottom right hand corner of the cell, hover over the drag handle, then click and hold the cursor when it changes to the plus shape and drag down as far as you need in the column.

It's not possible to do this for every function or formula you include.

We also need to tell the client whether or not they need to purchase more.

To do this, you need to compare the remaining stock with the minimum stock and see whether the value of remaining stock is less than or equal to the minimum stock.

You will use an if function.

So we have equals if, open brackets, G2, less than or equal to C2.

Comma, yes, in speech marks, no in speech marks, and then close our brackets.

So this is basically saying if G2 is less than or equal to C2, we're going to write yes in the cell.

If that's not true, then we're going to write no in the cell.

Okay, time to check your understanding.

What does the following function do? Is it A, this is a simple multiplication function.

B, this adds a range of values to give a total or C, this function compares the values in cells D5 and E5.

Pause the video whilst you think about your answer.

That's right, the correct answer is C.

This is an if function and it's comparing the values in D5 and E5.

Let's have a look at the if function in more detail.

This function compares two values using logical expressions.

Logical expressions include equal to, less than, greater than, less than or equal to, greater than or equal to.

So in this example, we have G2, less than equal to C2 as our logical expression.

It returns a value based on whether the expression is true or false.

You can also add conditional formatting to highlight when more stock needs to be purchased.

So yes is the return value of true and no is the return value of false.

Let's now have a look at the running cost sheet.

As a hint, you need to complete the running cost sheet before the dessert cost sheet because you need to use some data from this sheet in the calculation on the dessert cost sheet later on.

In this sheet, we need to use absolute cell referencing.

Here, we need to divide B3 by C1.

So C1 contains the number of days and the formula that goes in here is equal B3 divided by C1.

Before using the drag handle, you must make cell C1 absolute.

This means that each value in the column will be divided by C1 when you drag the handle.

You can do this by adding dollar symbols like this.

So dollar C dollar one, that makes cell C one an absolute cell reference.

Let's now have a look at the dessert cost sheet.

So we've got the running cost table, which is on the right hand side.

This has been included on the dessert cost sheet because you need to calculate how much to add to the sale price of each dessert to cover the running costs.

So we're going to link to cell C7 on the running cost sheet.

So you can see we do this by typing in equals and then we navigate across to the sale sheet and select cell C7.

Note that the amount has been converted to a decimal of a kilogramme or litre.

This is so that all of the ingredients are in the same unit of measurement.

These cells require formulas or a function.

Let's have a look at this recipe example.

As a note, do not copy the cost formula down on this sheet.

You need to enter every link manually.

Some have been completed for you.

Here's the sale sheet.

So you can see we have our day one, we have our opening stock, we have the available portion size, and then we have room for the numbers sold and online orders.

At the moment, the numbers sold and online orders are all set to zero.

Examples of most of the functions and formulas needed for the daily sales table have been demonstrated in this lesson already.

Here's the weekly sales sheet.

So here we have an if and an and function combined.

We have equals if open brackets and and then we have another set of open brackets and we have an absolute cell reference for C16.

And we're checking the logical expression there is equal to the absolute cell reference of B1 and then we're doing a comma.

So this is our and bit here, which is another absolute cell reference for A16 is equal to an absolute cell reference for A1.

And then we're closing our brackets.

And then our value of true is F3 and our value of false is C17.

This function is testing two logical expressions at the same time.

It is asking if the week numbers and the day numbers are the same in two different areas of the sheet.

So it's checking that week one at the top compared to the week one at the bottom of the sheet.

And that day one at the top is equal to day one at the bottom of the sheet.