Hello, my name is Mrs. Holborow 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 organize 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 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 kilograms, some things that are measured in grams, some things that are measured in liters and things like that.

The workbook template converts the measurements in grams and millimeters to kilograms and liters 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 organization.

Think about the font choices, font size, text and background colors that you are 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're zooming in on the font and the text color and size.

Font formatting.

The default setting is aerial 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 color and grid lines and let you choose whether the number of cells can be merged together or not.

So a merged cell will join multiple cells together.

These tools are the alignment tools.

They adjust the position of the text within cells, whether the cell 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 color of a cell? Is it A, B, or C? Pause the video whilst, do you have a think? Did you spot it? Well-done.

Both A and C will affect the color of a cell.

A will affect the fill color, and C will affect the border color.

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 merged 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 sale 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, deliciousdesserts.

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 colors, 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'll 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 kilograms of plain flour, 2.

27 liters of milk, et cetera.

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

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 kilogram or liter 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 cells 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'll use an IF function.

So we have =if(G2<=C2,"Yes","No").

So this is basically saying if G two is less than or equal to C two, 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 D five and E five.

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 D five and E five.

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 G two less than equal to C two 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 B three by C one.

So C one contains the number of days and the formula that goes in here is equal B three divided by C one.

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

This means that each value in the column will be divided by C one 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 C seven 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 C seven.

Note that the amount has been converted to a decimal of a kilogram or liter.

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 bracket AND, and then we have another set of open bracket and we have an absolute cell reference for C 16.

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

So this is our and bit here, which is another absolute cell reference for A 16 is equal to an absolute cell reference for A one.

And then we're closing our brackets.

And then our value of true is F three and our value of false is C 17.

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.