BSBTEC302

University :
Other Exam Question Bank is not sponsored or endorsed by this college or university.

Student workbook

BSBTEC302

Design and produce spreadsheets

TAFE NSW would like to pay our respect and acknowledge Aboriginal and Torres Strait Islander Peoples as the Traditional Custodians of the Land, Rivers and Sea. We acknowledge and pay our respect to Elders, past, present and emerging of all Nations.

Version:20220112

Date created:14 December 2021

Date modified: SAVEDATE @ "dd MMMM yyyy" * MERGEFORMAT 30 May 2023

For queries contact: Technical and Business Services SkillsPoint

TAFE NSW 2021RTO Provider Number 90003 | CRICOS Provider Code: 00591E

This resource can be found in the TAFE NSW Learning Bank.

The content in this document is copyright TAFE NSW 2021 and should not be reproduced without the permission of TAFE NSW. Information contained in this document is correct at the time of printing: DATE @ "dd MMMM yyyy" * MERGEFORMAT 11 July 2024. For current information please refer to our website or your teacher as appropriate.

Contents

TOC o "1-2" h z u Getting started PAGEREF _Toc92826371 h 4What will I learn by completing this workbook? PAGEREF _Toc92826372 h 5Icon legends PAGEREF _Toc92826373 h 6Introduction PAGEREF _Toc92826374 h 7Course overview PAGEREF _Toc92826375 h 8Topic 1: Select and prepare resources PAGEREF _Toc92826376 h 9Overview PAGEREF _Toc92826377 h 10Identifying task purpose and audience PAGEREF _Toc92826378 h 10Identifying task requirements PAGEREF _Toc92826379 h 11Selecting application to create spreadsheet PAGEREF _Toc92826380 h 12Topic 2: Planning your spreadsheet PAGEREF _Toc92826381 h 19Overview PAGEREF _Toc92826382 h 20Planning and designing spreadsheet PAGEREF _Toc92826383 h 20Considering layout and readability PAGEREF _Toc92826384 h 21Using available function and style guides for consistency PAGEREF _Toc92826385 h 22Topic 3: Creating spreadsheet PAGEREF _Toc92826386 h 27Overview PAGEREF _Toc92826387 h 28Creating a new spreadsheet PAGEREF _Toc92826388 h 28Entering data into spreadsheet and checking for consistency PAGEREF _Toc92826389 h 30Consulting stakeholder to confirm formulas PAGEREF _Toc92826390 h 33Help function PAGEREF _Toc92826391 h 36Topic 4: Produce charts PAGEREF _Toc92826392 h 39Overview PAGEREF _Toc92826393 h 40Chart design for numerical data to meet organisational requirements PAGEREF _Toc92826394 h 40What is a chart? PAGEREF _Toc92826395 h 41Create charts PAGEREF _Toc92826396 h 45Modify chart style and layout PAGEREF _Toc92826397 h 47Topic 5: Finalise and present spreadsheets PAGEREF _Toc92826398 h 51Overview PAGEREF _Toc92826399 h 52Prepare and review spreadsheet according to task requirements PAGEREF _Toc92826400 h 52Deliver document to required stakeholders PAGEREF _Toc92826401 h 53Sustainability requirements PAGEREF _Toc92826402 h 55Store spreadsheets PAGEREF _Toc92826403 h 57Answers and feedback for practice activities PAGEREF _Toc92826404 h 60Image attributions PAGEREF _Toc92826405 h 67

Getting started

What will I learn by completing this workbook?

This workbook covers the unit of competency BSBTEC302 Design and produce spreadsheets.

Successfully completing this unit will give you the skills and knowledge to design and produce spreadsheets, create charts, add data in spreadsheets.

In this workbook, you will learn how to:

use an appropriate application to produce a spreadsheet

design and plan spreadsheets to suit purpose and audience

review and edit the final spreadsheet and deliver it to required stakeholders.

Each topic includes opportunities to check your progress and understanding as well as activities that will prepare you to complete the formal assessments.

There are five topics to complete within this workbook. They are:

Select and prepare resources.

Plan spreadsheet design.

Create a spreadsheet.

Produce charts.

Finalise and present spreadsheets.

Alright, lets get started!

Icon legendsTable SEQ Table * ARABIC1 Icon legends

Icon Description

Practice activity

Learning activities help you to gain a clear understanding of the content in this resource. It is important for you to complete these activities, as they will enhance your learning. The activities will prepare you for assessments.

Resources (required and suggested)

Additional resources throughout this workbook, such as chapters from textbooks, online articles, videos, diagrams, and infographics. These are supplementary resources, which will enhance your learning experience and may help you complete the unit.

Video

Videos will give you a deeper insight into the content covered in this workbook. If you are working from a printed version, you will need to look these up using the URL (link to the video online) provided.

Self-check

An activity that allows you to check your learning progress. The self-check activity gives you the opportunity to identify areas of learning where you could improve. If you identify these, you could review the relevant content or activities.

Introduction

Figure SEQ Figure * ARABIC1 Presentation on a laptop by Fauxels under Pexels licence.

This unit describes the skills and knowledge required to develop spreadsheets through the use of spreadsheet applications.

The unit applies to individuals employed in a range of environments who tend to be personally responsible for designing and working with spreadsheets under minimal supervision. These individuals are generally required to have intermediate knowledge and understanding of a number of spreadsheet applications.No licensing, legislative or certification requirements apply to this unit at the time of publication.

Virtual organisation policies Gelos Enterprises

You can access the virtual organisation Gelos Enterprises to gain insight into their organisational operational plans, policies and procedures and workplace documentation including, budgets, physical and human resource procurement documentation, employee induction and performance monitoring procedures.

Gelos Enterprises is an online simulated workplace and it is one of the world's leading business operations. Visit Gelos Enterprises online and log in with your TAFE username and password. (Long URL: https://share.tafensw.edu.au/share/items/d0b458dc-3922-409d-b1fe-9a2f785f4a38/0/GelosEnterprises.zip/index.html)

Course overviewThis course covers principles and practices involved in designing and creating spreadsheets. It gives you a clear understanding of how to use software applications to plan, design and present the spreadsheet. It also explains you about the advantage of good ergonomics. The course guides you around different functions, formulas, style guides and all the required steps one must follow to create a professional spreadsheet.

Topic SEQ Topic * ARABIC1: Select and prepare resources

OverviewAccording to Small Business chronicles, organisations use spreadsheets to do tasks such as calculating data, analysing data, creating charts, and using the data to inform future planning and decision making.

In this topic you will learn about how to:

identify the task purpose and intended audience

identify task requirements according to data entry, storage, output, timeline and presentation format

select the most appropriate application to produce a spreadsheet according to organisational policies

set up your equipment according to ergonomics policies and procedures.

The activities throughout this resource will assist you in your learning. These activities do not form a part of your final assessment; however they will contribute to your understanding of the topic area. Answers and feedback for the practice activities are provided at the end of this document.

Identifying task purpose and audienceThe task purpose of the spreadsheet must be clear. It is also necessary to keep in mind what it will achieve. The following options need to be considered:

inform on recent sales activity

respond to a recent client sale with billing information

educate the reader

record what hours an employee worked

produce a chart for a quick visual analysis of information

report details about a company to its shareholders.

Audience

Who is going to read the spreadsheet or document? Think about all of the people that will read the document and consider:

prior knowledge level are they already familiar with the content

needs and goals how is the document going to meet their need for information or results

level of education consider language, literacy, numeracy and digital literary ability.

Information

How will the information in the spreadsheet be presented? If you will be handing out the spreadsheet, it is essential to make sure it will look good when printed. If you print it in colour, ensure the colours are consistent with the colour theme of the organisation's branding. If you print it in black and white (monochrome), use shading appropriately so that the spreadsheet is easy to read.

Identifying task requirements

When you produce a spreadsheet in a business environment, you need to ensure that it looks professional, functions as per task requirements and accurately represents the organisation you are working for. In your spreadsheet design, you may need to consider the organisation requirements. Many organisations use a style guide for specific information relating to spreadsheet production and layout. This ensures that spreadsheet presentation is consistent with the presentation of other spreadsheets created by the organisation.

Collaborate with others about spreadsheet design

If you are unsure of anything about the spreadsheet you are creating, consult with the content author (the person giving you the information) or your supervisor.

Questions you might ask could be:

What is the aim of the spreadsheet?

What message are you trying to convey?

Can you describe who the audience is?

Is there a specific layout you would like for the spreadsheet?

In addition to organisational requirements, do you have any other design requirements?

When do you require the completed spreadsheet?

Where would you like me to save the file?

Selecting application to create spreadsheetThere are several applications you can use to create spreadsheets. Some of the most popular are

Microsoft Excel (available through your TAFE studies as part of the Office 365 suite of applications)

Google Sheets - It is a spreadsheet program which is offered by Google. The service includes Google docs, Google slides, Google drawings, Google Forms, Google Sites and Google keep. (Long URL: https://www.google.com/sheets/about/)

Numbers - It is a spreadsheet application developed by Apple Inc. It helps to create spreadsheets from scratch allowing you to import or export data, sort it by column or row, using formulas and more. It can be used for apple products only. (Long URL: https://www.apple.com/au/numbers/)

Open Office - It is a spreadsheet offered by software package of OpenOffice. It offers a wide range of file formats. (Long URL: https://www.openoffice.org/)

Zoho Sheets It is an online spreadsheet application that allows you to create, design, edit, share and collaborate spreadsheets.

(Long URL: https://www.zoho.com/sheet/).

Microsoft Excel ('Excel') is the leading industry software for creating spreadsheets for business. This unit assumes the use of Excel to create spreadsheets.

As a TAFE NSW student, you can access Excel. Go to Microsoft Office and Log-in using your TAFE NSW email address and password. (Long URL: https://office.com/)

Microsoft Excel is available as:

Desktop application - Your computer has the Microsoft Excel program installed. You access it via your computer's Start menu or desktop shortcut. If you don't already have Microsoft Excel on your computer, the TAFE NSW Quick Reference Guide (.pdf) sets out the steps to install Microsoft Office 365 Applications.

Excel on the web - You access Microsoft Excel via your internet browser. You can access it from any device. As a TAFE NSW student, you can access Excel on the web via Microsoft Office and Log-in using your TAFE NSW email address and password.

While Excel for the web and desktop applications appear similar, you should be aware that there may be some differences between using a workbook in the browser and in Excel.

Ergonomics

Ergonomics is the study of the interaction between humans and the working environment. It aims to promote the safety of workers by making a comfortable and productive workplace.

Ergonomics includes making simple changes to work methods, equipment, and layout that can help to prevent injuries. This may be as simple as providing fully adjustable office chairs and extend to adjusting lighting or providing standing desks and exercise classes.

Good ergonomics can lead to:

increases in your work efficiency

decreases in errors due to fatigue

reduction of health and injury problems

increase in work satisfaction.

Ergonomic office set-up

Before you begin working on spreadsheets, it is essential to check your workstation is set up ergonomically. You may spend many hours sitting, gazing at monitors, and keying in information, so placing all equipment correctly will reduce the chances of injury.

Setting up your furniture

Your chair is perhaps the single most crucial component of a healthy working environment. It's what you should adjust first before adjusting your monitor or keyboard. You should be able to sit comfortably in your chair, using as much of the chair back as possible for support.

Use the following checklist to review your chair set-up:

adjust the chair height so that your elbows are bent at 90, forearms should be parallel with the floor, wrists straight, and shoulders relaxed

adjust the seat tilt so that you are comfortable working on the keyboard and your knees are bent at a comfortable angle and greater than 90 flexion

adjust the backrest so that it supports the lower back when you are sitting upright

your elbows should be in line with or slightly higher than the level of your keyboard

use a footrest if you cannot easily reach the floor while maintaining the correct arm and hand position

the seat should support you through your legs with 3 4 fingers between the end of your seat and knee

sit back in your chair and adjust the lumbar support to the small of your back.

check the chair can get close to your desk, so you are working with your elbows by your side

adjust your seat back to support you in an upright position where your ears, shoulder, and hips are all in line.

Setting up your equipment

Next, you need to position your keyboard, mouse, and monitor so that you don't place your body under stress.

Use the following checklist to review your equipment set-up:

when you use a single monitor, the screen should be between 40 90 cm from you or about an arm's length away

when you use two monitors, they should be at the same height, next to each other, where your eyes can move to view both screens

place the keyboard as close to the front edge of your desk as you can so it's comfortable

your mouse should fit your hand and not be too large

when using your mouse, keep your wrist is in a neutral position and avoid bending your wrists towards your thumbs or smallest finger

place the mouse mat directly next to the end of the keyboard on your preferred side

where possible, try to avoid holding on to the mouse when not in use

if using a laptop, use an external keyboard, mouse, and monitor stand where possible.

Work periods and breaks

Once you have set up your computer workstation correctly, you also have to create good work habits. No matter how perfect the environment, prolonged, static postures will take a toll on your body and inhibit circulation. Work breaks are not a luxury; they are a work health and safety requirement.

Short regular breaks help you stay alert, decrease boredom and increase productivity. Work breaks don't mean you have to stop working; it can mean a change of task or introducing movement.

Figure SEQ Figure * ARABIC 2 Developer working in the office by Tim Van der Kuip under Unsplash licence.

Some of the everyday strategies for integrating more movement into your daily tasks and routines are:

take regular postural/stretching breaks to reduce intense periods of repetitive movement

refocus their eyes away from the screen; for example, every 20 minutes, focus on an object 20 metres away for 20 seconds

drink plenty of water to keep well hydrated

take time away from your desk during your lunch break

where possible, mix computer-related tasks with non-computer related tasks

take the opportunity to stand up during phone calls

walk to your colleagues' desks instead of phoning or emailing

step outside for fresh air regularly

have standing or walking meetings

ensure you get up and move around every one or two hours.

Video

The video, Get to know Excel (Microsoft Office, 1.33 min) for the web introduces the features of Excel for the web.

(Long URL: https://share.tafensw.edu.au/share/items/a7f6f6cf-30ce-4ed3-a848-5970523358c6/0/?attachment.uuid=d5b6b8d7-7e63-427c-81c6-b4e009c19a82)

Practice activity

Activity SEQ Activity * ARABIC 1: Reflective exercise

Read the questions carefully. Your answer should be a minimum of 50 words but no longer than 100 words.

Outline the points to consider before creating a spreadsheet based on the intended audience.

What are the points to be considered before handing out the information in the spreadsheet?

Mention some of the popular applications to produce a spreadsheet.

Check your knowledge

Activity SEQ Activity * ARABIC 2: Select and prepare resources

Read the following questions and select the correct answers.

An organisation's style guide will generally include:

Review Gelos Style Guide and then select the correct answer from the following question.

(Long URL: https://share.tafensw.edu.au/share/items/5f1cec7b-1d03-446a-85b7-edb42692c34e/0/?attachment.uuid=7f4fb7d3-3c6e-4e89-ad1b-2443d7e7b2fe)

Table SEQ Table * ARABIC 2 - Multiple choice

# Choices Answer/s

Font, formatting and chart styles

B. Font, formatting and skype meetings

C. Email reminders, font, and lunch break times

To reduce injuries caused by intense periods of repetitive movement, it is recommended that Gelos Enterprises staff members:

Table SEQ Table * ARABIC 3 - Multiple choice

# Choices Answer/s

Take regular postural/stretching breaks

Take a break every twenty minutes for ten minutes at a time

Not to take stretch breaks in work time

Self-check

How did you go?

You have completed the topic on select and prepare resources. Check the boxes for the tasks you feel confident you can complete.

I can identify task purpose and audience for creating spreadsheet.

I can identify task requirements according to data entry, storage, output, timeline and presentation format.

I can select the most appropriate application to produce spreadsheet, according to available resources and organisational policies and procedures.

Topic SEQ Topic * ARABIC2: Planning your spreadsheet

OverviewPlanning the layout and functionality of your spreadsheet prior to creating it will save you hours of time. Considering the purpose and the audience of your spreadsheet will ensure that the data entered will meet task requirements.

In this lesson, you will develop important skills and knowledge in planning your spreadsheet. You will learn how to:

plan and design a spreadsheet to suit the purpose, audience and information requirements of a task

consider the layout and readability of your spreadsheet

use available functions and style guides to create spreadsheets.

The activities throughout this resource will assist you in your learning. These activities do not form a part of your final assessment; however, they will contribute to your understanding of the topic area. Answers and feedback for the practice activities are provided at the end of this document.

Planning and designing spreadsheetThe first step to producing professional spreadsheets is planning the spreadsheet design. When planning the design of your spreadsheet, it is crucial to make sure that the design of the spreadsheet suits the purpose, audience, information, and requirements of the task.

Figure SEQ Figure * ARABIC 3 - Planning Presentation by Ono Kosuki under Pexels licenceVideo

The video presented by industry experts, Planning and designing spreadsheets (Vimeo, 1:34 min), offers tips on planning and designing spreadsheets in the workplace.

(Long URL: https://player.vimeo.com/video/594475538?h=bde78a58c8)

Considering layout and readability

Readability

When designing your spreadsheets, it is vital to make them easy to read and follow.

Video

The video Do's & Don'ts of Excel Spreadsheet Design (YouTube, 5:27 mins) with Examples outlines ways to design and structure your Excel workbook to improve readability.

(Long URL: https://www.youtube.com/watch?v=7LjXpCcibks)

Layout

You should present the information in a spreadsheet in a straightforward, easy to read and understand format. Table 4 includes layout features to consider when designing spreadsheets.

Table SEQ Table * ARABIC 4 - Feature and purpose

Feature Purpose

Page Set-up Adjust the print borders. Vertical and horizontal alignment can place the spreadsheet in the middle of the page, enhancing the appearance of data.

Borders and shading Enhances the spreadsheet's appearance and makes the data look more presentable on screen and a printout.

Headers and footers Allows the spreadsheet to be identified by including dates, page numbers, file name, and file path.

Column width and height Adjust the row width and column heights to show all data while minimising the need to scroll.

Appearance

It is important to be consistent in the appearance of all spreadsheets:

the style and point size should be consistent throughout the spreadsheet

ensure you follow organisational policy and procedures for content and format

documents are easier to read without underlining, and all capital letters

use font style and point size with care - they should enhance the appearance of your spreadsheet for ease of reading and not confuse your reader.

Using available function and style guides for consistencyFunctions

A function is a pre-written or in-built formula. All functions will:

Begin with an equal sign (=).

Function name for example, SUM.

Include an argument/s that is/are required for the function.

An argument is included in brackets after the function name. It may include:

individual cells

cell ranges - the range has a colon (:) between the first and last cell. For example, in the function: =SUM(A5:A10), the range starts with A5 and ends with A10

multiple arguments these are separated by a comma (,). For example, in the function: =SUM(A5:A10,B1:B2).

Video

The video Excel: Functions (YouTube, 5:15 min) outlines creating functions and arguments.

(Long URL: https://www.youtube.com/embed/-9d4m79twdA)

Table SEQ Table * ARABIC 5 Function description

Function Name Example function Description

Sum =SUM(A5:A10) Adds the amounts in cells that are specified in a set range.

Average =AVERAGE(A5:A10) Adds the values in the range, divides the total by the number of values.

Minimum =MIN(A5:A10 Inserts the minimum or lowest value in the stipulated range.

Maximum =MAX(A5:A10) Inserts the maximum or largest value in the stipulated range.

Count =COUNT(A5:A10) This function allows you to get the number of entries that is in a range of numbers.

Style guides for consistency

A style guide is a set of editing and formatting standards for staff in a specified organisation to use. Style guides help individuals and departments to prepare company documents consistent with the company's corporate style. The style guide explains what style to apply to company documents and advice for writing and producing documents.

Many organisations use a style guide to ensure consistency of style and image in their spreadsheets. These provide guidelines for staff to follow and set quality standards expected of staff in relation to their work. Review the Gelos Enterprises Style Guide to view examples of organisational requirements.

(Long URL: https://share.tafensw.edu.au/share/items/5f1cec7b-1d03-446a-85b7-edb42692c34e/0/?attachment.uuid=7f4fb7d3-3c6e-4e89-ad1b-2443d7e7b2fe)

Before you prepare any spreadsheets, check your organisation's style guide for guidelines on standard spreadsheet formats. This may include preferred default settings such as font, formatting, and heading requirements for spreadsheets and charts. They will usually provide:

information on where files should be saved

the file naming convention

policies for backing up documents.

Practice activity

Activity SEQ Activity * ARABIC 3: Short answer question

Read the question carefully. Your answer should be a minimum of 50 words but no longer than 120 words.

Describe the features to consider while designing a spreadsheet.

How do you stay consistent in the appearance of spreadsheets?

What is a function in a spreadsheet? Give a few examples.

Check your knowledge

Activity SEQ Activity * ARABIC 4: Planning spreadsheet

Read the following questions then write true or false in the space provided.

Table SEQ Table * ARABIC 6 - True and false

Questions True or False

Purpose, audience, and presentation are three main aspects to consider while planning your spreadsheet. Page setup enhances the spreadsheet's appearance and makes the data look more presentable on screen and a printout. Count function allows you to get the number of entries that is in a range of numbers. Self-check

How did you go?

You have completed the topic on planning your spreadsheet. Check the boxes for the tasks you feel confident you can complete.

I can design spreadsheet design to suit purpose, audience and information requirements of task.

I can confirm, spreadsheet is designed to enhance readability and appearance, and is in accordance with organisational and task requirements.

I can use available application functions and confirm consistency of design and layout, adhering to organisational and task requirements.

Topic SEQ Topic * ARABIC 3: Creating spreadsheets

OverviewNow that you have a plan in place around the design of your spreadsheet, it's time to create the spreadsheet. In this lesson, you will develop important skills and knowledge to create spreadsheets.

In this topic you will learn how to:

create spreadsheet and enter data into spreadsheet

check data for consistency in design and layout

format data in spreadsheet using applicable function

consult with stakeholders and confirm formulae to be used

use required help function and action issues as required.

The activities throughout this resource will assist you in your learning. These activities do not form a part of your final assessment however they will contribute to your understanding of the topic area. Answers and feedback for the practice activities are provided at the end of this document.

Creating a new spreadsheetIn Excel, a workbook is a file containing one or more sheets also called as worksheet. Each worksheet is a page in the workbook where you enter and work with data. You can move between the worksheets using the navigation buttons at the bottom of the Excel screen or by selecting the sheet tab.

Video

Understanding Workbook and worksheet

The video Understanding workbooks and worksheets (LinkedIn Learning, 2:56 min) explains the difference between a workbook and a worksheet in Microsoft Excel.

(Long URL: https://www.linkedin.com/learninglogin/share?account=57684225&forceAccount=false&redirect=https%3A%2F%2Fwww.linkedin.com%2Flearning%2Fexcel-essential-training-office-365-microsoft-365%2Funderstanding-workbooks-and-worksheets%3Ftrk%3Dshare_video_url%26shareId%3Do0GsYa%252BJQ2GMXZB2FCvvCQ%253D%253D)

This video will open in a new tab within your browser. You can access the full video by logging in with your TAFE NSW username and password. You can access a transcript of each video on the transcript tab within LinkedIn Learning. When youre finished, simply close the tab, and return to your course.

Tips for creating spreadsheet

To create a workbook in Excel for the web:

Log into Microsoft Office using your TAFE NSW email address and password.

Under Apps (access by selecting the cube of dots), select Excel.

Select New blank workbook.

To add a sheet within the same workbook, select the New sheet button.

To move a sheet within the same workbook:

Select sheet tab you wish to move.

Drag it to its new location (the black triangle indicates the position).

Release the mouse button when the triangle is in the desired location.

To delete a sheet using right mouse button shortcut:

Position the mouse over sheet tab to be deleted.

Select the right mouse button and select Delete from the dialogue box.

To name or rename a sheet using mouse:

Position the mouse over sheet tab to be renamed.

Double click the left mouse button to highlight the existing name.

Type the new name and press Enter.

Video

The video presented by industry experts, Tips for creating spreadsheets (Vimeo, 1:08 min), offers tips on creating spreadsheets from industry leaders.

(Long URL: https://player.vimeo.com/video/594498075?h=fa4907401c)

To save a spreadsheet:

Select the File menu, Save As.

Save your document in a location, for example, OneDrive.

Entering data into spreadsheet and checking for consistencyAdding data

The information you enter into a spreadsheet is called data. An entry made into a cell is either a label, number, or formula. Text is also called a label. Headings are a common form of label entry and can be comprised of letters or numbers. The text is automatically left-aligned in Excel, and numbers (also called values) are automatically right-aligned.

To enter data into a workbook:

Select the cell where data is to appear.

Type in the data.

Confirm the data entry by pressing Enter or any of the cursor movement keys.

If you make a mistake while you are entering data, you can do one of the following:

press the Backspace key, then retype the data

stop the entry by selecting the X button in the Formula Bar, or

press the Esc key.

If you have already accepted the cell entry do one of the following:

select the cell and retype the whole entry or

press F2 and edit within the cell.

Video

The video Get started with data (LinkedIn Learning, 2:42 min) explains how to enter data in Excel for web.

(Long URL: https://www.linkedin.com/learning-login/share?account=57684225&forceAccount=false&redirect=https%3A%2F%2Fwww.linkedin.com%2Flearning%2Fexcel-for-the-web-tips-and-tricks-office-365-microsoft-365%2Fget-started-with-data%3Ftrk%3Dshare_video_url%26shareId%3Dvet8L1XJQ%252B%252BYTMQWU8KVHw%253D%253D)

Video

Cut, copy and paste data

The video Moving, copying, and inserting data (LinkedIn Learning, 2.52 min) explains the process to copy data by cutting or dragging.

(Long URL: https://www.linkedin.com/learning-login/share?account=57684225&forceAccount=false&redirect=https%3A%2F%2Fwww.linkedin.com%2Flearning%2Fexcel-essential-training-office-365-microsoft-365%2Fmoving-copying-and-inserting-data%3Ftrk%3Dshare_video_url%26shareId%3DefhAniZWQW6wuLz7decpcA%253D%253D)

Design and layout

You can adapt the design and layout in Excel, including:

Change column width

You might need to expand column width to make space for longer descriptions, web links, or large amounts of text. You might need to shorten columns to fit everything in one screen without scrolling. To change the column width with your mouse, position your cursor on the vertical line between column letters (pointer changes to a black cross with arrows on horizontal bar). Using your mouse, click and drag to the required width.

Increase row height

You may need to increase the height of a cell or row to show all the content in a cell, particularly if you use the Wrap Text button.

To increase the row height:

Highlight rows or rows in the worksheet by selecting the first numbered column.

Drag down to select multiple rowsthe cursor will be a black arrow when it hovers over a row number.

Position pointer on the horizontal line between any two numbers.

Drag down until the reference area displays the required height and release.

You can also right-mouse-button select on row number, choose Row Height, type in the desired number for row height.

Insert a row

You may need to increase the height of a cell or row to show all the content in a cell, particularly if you use the Wrap Text button. A new row will always be inserted above the active cell so ensure you have selected the correct cell first.

Start by selecting a cell or a row. Go to the Cells block in the Home ribbon, select the Insert button, and then Insert Sheet Rows. You can also right-mouse-button select in a cell and select Insert. Enter new data.

Centre the heading over the worksheet

Highlight the cells containing the heading and drag the mouse along the row to the last column containing data. In the Home ribbon, select the Merge & Centre button on the Home ribbon.

To create a header or footer, you will need to use the Excel desktop application. Headers and footers cannot be created in Excel for the web. In the Excel desktop application, the header and footer will appear on any documents that you print. You can also use 'Page Layout' to see how your document will look when it is printed.

Select the View tab in the Ribbon and select Page Layout. Activate anywhere in the header or footer to bring up the Header & Footer ribbon.

The Header area will be split into three sections: left, middle and right.

You can type into each section or add content using the Header and Footer Elements section.

To add a footer, select 'Go to footer' in the Ribbon.

To return to your spreadsheet, activate anywhere in the worksheet area. You will still be in Page Layout View. To go back to the normal view, go to View Ribbon, choose Normal View, or select the grid button on the bottom right of the screen.

Video

The video Rows and columns: Insert, delete, hide, and unhide (LinkedIn Learning, 4.30 min) provides an overview of the key steps described above.

(Long URL: https://www.linkedin.com/learning-login/share?account=57684225&forceAccount=false&redirect=https%3A%2F%2Fwww.linkedin.com%2Flearning%2Fexcel-essential-training-office-365-microsoft-365%2Frows-and-columns-insert-delete-hide-and-unhide%3Ftrk%3Dshare_video_url%26shareId%3DPUskyNHWRlGHinLsZgrvsw%253D%253D)

Consulting stakeholder to confirm formulasIf you are unsure which formulas to use or how these apply to the spreadsheet you are creating, you should always consult the content author (the person giving you the information) or your supervisor.

Formulas

Formulas are powerful ways to crunch data automatically. You can type the formula straight into a cell or into the Formula bar once a cell is selected. For example, =B2+B10. This formula adds the contents of cell B2 and cell B10. Formulas always start with the Equal = sign. In this example, B2 and B10 are cell references. If you change the values in these cells, the cell with the formula will automatically update. A formula can either be typed in or by pointing to the cell where the reference is absolute and pressing F4 (which automatically inserts the $ signs for both the column and row).

Video

The video Excel: Intro to Formulas (YouTube, 3:38 min) gives an overview of using formulas in Excel.

(Long URL: https://share.tafensw.edu.au/share/items/a7f6f6cf-30ce-4ed3-a848-5970523358c6/0/?attachment.uuid=3abec416-193d-4e0b-8b62-29cdd83df41a)

Calculations and formula: mathematical operators

Excel is regularly used to do mathematical calculations. The computer symbol for each of the operators is in the table number 4.

Table SEQ Table * ARABIC 7 Formula table

Symbol Mathematical operators

+ Addition

- Subtraction

* Multiplication

/ Division

^ Exponential, for example, 5^2 means 5 * 5, which is 25

() Operations enclosed in parentheses or brackets are done first

Entering a formula

To enter a formula in an active cell, first press = (equal sign) to signify that what follows is a formula. Using the keys on the numeric keypad will make formula entries easier.

Video

The video Using simple formulas (LinkedIn Learning, 3:57 min) outlines how to enter and write a formula.

(Long URL: https://www.linkedin.com/learning-login/share?account=57684225&forceAccount=false&redirect=https%3A%2F%2Fwww.linkedin.com%2Flearning%2Fexcel-essential-training-office-365-microsoft-365%2Fusing-simple-formulas%3Ftrk%3Dshare_video_url%26shareId%3DefhAniZWQW6wuLz7decpcA%253D%253D)

Autosum

Autosum allows you to add up numbers automatically. When you use Autosum, Excel inserts a formula to add up the values. The (Sigma) symbol on the Home ribbon will add either vertically or horizontally. Use Autosum for adding figures. Position the cursor in the cell where you will insert the total. Select the area to be added is surrounded by a moving line (check the area is correct). Select again to insert the total figure.

Copying formula using autofill

To copy the formula into adjacent cells, you need to keep in mind the following points:

Position your cursor in the cell that contains the formula to be copied.

Move the mouse pointer to the bottom right-hand corner of the box (called the fill handle).

The pointer changes to a thinner black cross.

Drag fill handles down or across the cells where the formula is to be copied to and released.

Video

The video Copying a formula into adjacent cells (LinkedIn Learning, 3:02 min) explains the above process in more detail.)

(Long URL: https://www.linkedin.com/learning-login/share?account=57684225&forceAccount=false&redirect=https%3A%2F%2Fwww.linkedin.com%2Flearning%2Fexcel-essential-training-office-365-microsoft-365%2Fcopying-a-formula-into-adjacent-cells%3Ftrk%3Dshare_video_url%26shareId%3DefhAniZWQW6wuLz7decpcA%253D%253D)

Help functionThe Help menu is like a library where you can find information about topics. Search for a pop-up panel on the right of your screen when you press the F1 key. To access the Help menu:

select Help from the top menu and select the Help button or press the F1 key

a pop-up Help panel appears on the right of the screen

type your questions in the box provided, for example, type in formulas and press Enterinformation on Overview of formulas in Excel will appear.

Video

The video Using Excel Help (LinkedIn Learning, 2:47 min) demonstrates how to access Help in Excel.

(Long URL: https://www.linkedin.com/learning-login/share?account=57684225&forceAccount=false&redirect=https%3A%2F%2Fwww.linkedin.com%2Flearning%2Fexcel-essential-training-office-365-microsoft-365%2Fusing-excel-help%3Ftrk%3Dshare_video_url%26shareId%3DD6SSpNBuS16613S5zmFJuA%253D%253D)

Practice activity

Activity SEQ Activity * ARABIC 5: Reflective Exercise

Read the question carefully. Your answer should be a minimum of 50 words but no longer than 120 words.

Explain the steps involved in entering data into the spreadsheet.

Why is it important to consult stakeholders and confirm formula to be used?

Check your knowledge

Activity SEQ Activity * ARABIC 6: Matching

Match the correct number in Column A to the correct letter from Column B in the Answer column.

Table SEQ Table * ARABIC 8 - Matching

Column A Answer Column B

Exponential +

Addition *

Multiplication ^

Self-check

How did you go?

You have completed the topic on creating spreadsheet. Check the boxes for the tasks you feel confident you can complete.

I can enter data, check and amend to maintain consistency of design and layout, in accordance with organisational and task requirements.

I can format spreadsheet using application functions, according to organisational policies and procedures and presentation requirements.

I can consult with relevant stakeholders and confirm formulae are tested and output meets task requirements.

I can use required help functions and action issues as required.

Topic SEQ Topic * ARABIC4: Produce charts

OverviewIn this unit, you will learn about chart types and design for organisational requirements. Able to modify chart type and layout using formatting features, adhering to organisational and task requirements.

In this topic you will learn how about:

chart type and design that offers the analysis of numerical data, and meets organisational and task requirements

creating charts using the required data range in the spreadsheet

chart types and layout using formatting features while adhering to organisational and task requirements.

Chart design for numerical data to meet organisational requirements

What do you know already?

You now know the importance of planning spreadsheets to meet organisational policies. Creating spreadsheets and using functions to perform calculations of the data is a great way to track and present the data. Lets look at another way to present the data contained in spreadsheets through the use of charts.

Figure SEQ Figure * ARABIC 4 - Survey sheet by Fauxels Pexels Images copied Pexels licenceWhat is a chart?Spreadsheets are great for entering, storing, and manipulating large sets of data. However, they can be hard to read at a glance and understand the data quickly. Charts provide a visual shortcut that can quickly summarise trends and patterns in your data.

Video

Watch this Excel charts (YouTube, 4:30 min) video to learn more about the basics of working with charts in Excel 2019, Excel 2016, and Office 365.

(Long URL: https://www.youtube.com/watch?v=_Wu7jYTr1Pk)

Chart types

When creating a chart, Excel gives you the option to select the chart type. Common chart types include:

Table SEQ Table * ARABIC 9 Chart type description

Chart Type Description

Column Uses vertical bars to represent and compare data.

Line Uses data points connected with lines to show trends.

Pie Uses data to create slices of a circle (or pie) to show proportions.

Bar Uses horizontal bars to represent and compare data.

Scatter Uses data points or dots to compare values and show intervals or clusters of data.

All charts are available in 2D (or two dimensions). These charts are flat.

Some chart types are also available in 3D. The third dimension adds depth as the chart can rotate and be shown from multiple perspectives.

Figure SEQ Figure * ARABIC 5 - Chart Image by Lukas Images copied under Pexels licenceYou will find the complete list of chart types on the Microsoft Office website.

(Long URL: https://support.microsoft.com/en-us/office/available-chart-types-in-office-a6187218-807e-4103-9e0a-27cdb19afb90)

Practice activity

Activity 7: Reflective exercise

Write the charts types and their uses.

Video

Watch this video to learn about Exploring chart types in excel (LinkedIn Learning, 3:58 min).

(Long URL: https://www.linkedin.com/learning/excel-essential-training-office-365-microsoft-365/exploring-chart-types)

Create chartsTo create a chart:

Select the cells with the data you want to include in the chart.

Go to the Insert Ribbon, Charts group.

Here the Recommended Charts options show the charts which will best display the data you have selected, or you can select the All-Charts option.

Choose the chart type, for example, Column, Pie, Bar, Line.

The chart will appear on the worksheet with your data. It can be moved or resized on the worksheet.

You should check your organisation's style guide for guidelines on standard spreadsheet formats, including the chart style, type, and layout.

To make charts easier to read, you should always include:

a meaningful chart title that tells the audience what the chart is about

data labels that describe the categories and values included on each axis

legend that tells the audience which colour represents each data series.

Video

Watch this Creating chart (LinkedIn Learning,5:39 min). video to learn about how to enter and organise data, perform calculations with simple functions, and format the appearance of rows, columns, cells, and data in.

(Long URL: https://www.linkedin.com/learning/excel-essential-training-office-365-microsoft-365/creating-charts?autoAdvance=true&autoSkip=false&autoplay=true&resume=true)

Resources

Additional resources

The following tutorials are short, practical and give a good overview of each of the above features:

add a chart element (LinkedIn Learning, 2:31 min) (Long URL: https://www.linkedin.com/learning-login/share?account=57684225&forceAccount=false&redirect=https%3A%2F%2Fwww.linkedin.com%2Flearning%2Fexcel-charts-in-depth%2Fadd-a-chart-element%3Ftrk%3Dshare_video_url%26shareId%3DB%252B62ogNUQRSDxTphRtsbPw%253D%253D)

add a chart title (Microsoft Office Support, 1:13 min) (Long URL: https://www.microsoft.com/en-us/videoplayer/embed/RE269IM?pid=ocpVideo0-innerdiv-oneplayer&postJsllMsg=true&maskLevel=20&market=en-us)

add and edit data labels (LinkedIn Learning, 3:28 min) (Long URL: https://www.linkedin.com/learning-login/share?account=57684225&forceAccount=false&redirect=https%3A%2F%2Fwww.linkedin.com%2Flearning%2Fexcel-charts-in-depth%2Fadd-and-edit-data-labels%3Ftrk%3Dshare_video_url%26shareId%3DB%252B62ogNUQRSDxTphRtsbPw%253D%253D)

use legends (LinkedIn Learning, 3:22 min) (Long URL: https://www.linkedin.com/learning-login/share?account=57684225&forceAccount=false&redirect=https%3A%2F%2Fwww.linkedin.com%2Flearning%2Fexcel-charts-in-depth%2Fuse-legends%3Ftrk%3Dshare_video_url%26shareId%3DB%252B62ogNUQRSDxTphRtsbPw%253D%253D)

show or hide a chart legend (Microsoft Office Support, 0:39 min) (Long URL: https://www.microsoft.com/en-us/videoplayer/embed/RE264wI?pid=ocpVideo0-innerdiv-oneplayer&postJsllMsg=true&maskLevel=20&market=en-us)

Data range

When you first create a chart, you select the data range by selecting the cells which include the relevant data.

You may later add more data to your spreadsheet. To update your chart to include this data, you will need to adjust the data range. To do this:

Activate the chart to highlight the data range in your spreadsheet.

Hover your mouse over the lower-right corner.

Drag the cross that appears to highlight the new data range.

Video

In this video, you will learn about how to make charts auto update (YouTube, 1:30 min).

(Long URL: https://www.youtube.com/watch?v=a1osBWySSvk)

Watch the following video, learn more about how to modify chart (Vimeo,0.39 min) type and discusses the importance use of charts within workplace settings.

(Long URL: https://player.vimeo.com/video/640978686?h=283be34408)

Modify chart style and layoutYou can edit the chart by activating the chart to enable the Chart Tools Ribbon:

To change the chart type, select the design tab in the chart tools ribbon; select change chart type, for example, column, pie, bar, line.

To change the chart style, select the design tab in the chart tools ribbon, select the down arrow in the chart styles group and select a style.

To move the chart, select the design tab in the chart tools ribbon, select move chart to choose between making your chart a separate sheet or making it appear as an object on your worksheet.

To add chart parts, including chart titles, legends, and data labels, select the design tab, select add chart element, and choose the chart part from the drop-down menu.

To edit any chart parts, select it and start typing.

Formatting features

In addition to choosing the chart type and parts, you can format charts using various styles and layouts.

Video

This video explains how to Choose styles and layouts (LinkedIn Learning, 3:42 min) to change the appearance of charts in

(Long URL: https://www.linkedin.com/learning/excel-charts-in-depth)

Resources

Additional resources

The following tutorials give an overview of the formatting of standard chart types:

column and bar charts (LinkedIn Learning, 3:16 min) (Long URL: https://www.linkedin.com/learning-login/share?account=57684225&forceAccount=false&redirect=https%3A%2F%2Fwww.linkedin.com%2Flearning%2Fexcel-charts-in-depth%2Fcolumn-and-bar-charts%3Ftrk%3Dshare_video_url%26shareId%3DB%252B62ogNUQRSDxTphRtsbPw%253D%253D)

line and area charts (LinkedIn Learning, 3:25 min) (Long URL: https://www.linkedin.com/learning-login/share?account=57684225&forceAccount=false&redirect=https%3A%2F%2Fwww.linkedin.com%2Flearning%2Fexcel-charts-in-depth%2Fline-and-area-charts%3Ftrk%3Dshare_video_url%26shareId%3DB%252B62ogNUQRSDxTphRtsbPw%253D%253D)

pie charts and doughnut charts (LinkedIn Learning, 3:21 min) (Long URL: https://www.linkedin.com/learning-login/share?account=57684225&forceAccount=false&redirect=https%3A%2F%2Fwww.linkedin.com%2Flearning%2Fexcel-charts-in-depth%2Fpie-charts-and-doughnut-charts%3Ftrk%3Dshare_video_url%26shareId%3DB%252B62ogNUQRSDxTphRtsbPw%253D%253D)

Practice activity

Activity 8: Reflective exercise

Write the charts styles and their purpose.

Self-check

How did you go?

You have completed the topic on produce charts. Check the boxes for the tasks you feel confident you can complete.

I can select the chart type and design that offers analysis of numerical data and meets organisational and task requirements.

I can create charts using the required data range in the spreadsheet.

I can modify chart type and layout using formatting features, adhering to organisational and task requirements.

Topic SEQ Topic * ARABIC5: Finalise and present spreadsheets

OverviewIn this unit, you will learn about how to prepare a spreadsheet and deliver it according to the organisational requirements. Able to name and store according to the organisational and task requirements.

In this topic you will learn about how to:

review and edit the final spreadsheet to prepare for delivery according to task requirements

deliver document to required stakeholders according to organisational requirements, policies and procedures

name and store spreadsheet according to organisational requirements and exit application.

Prepare and review spreadsheet according to task requirementsThe final step is to review and make final edits to your spreadsheet. It's important to do this before you print or send it to users.

Excel does not automatically check spelling and grammar. To check spelling and grammar, go to the Review tab in the Ribbon and select 'Spelling'.

Practice activity

Activity 9: Virtual organisation Gelos Enterprises

You can access the virtual organisation Gelos enterprises to gain their organisational plans, policies and procedures and workplace documentation.

Figure SEQ Figure * ARABIC 6 - Gelos home page image TAFENSW

Remember to review the organisation's style guide to ensure that your spreadsheet meets all organisational requirements, including fonts and colours, for example, HYPERLINK "https://share.tafensw.edu.au/share/items/5f1cec7b-1d03-446a-85b7-edb42692c34e/0/?attachment.uuid=7f4fb7d3-3c6e-4e89-ad1b-2443d7e7b2fe" Gelos Enterprises' Style Guide.

(Long URL: https://share.tafensw.edu.au/share/items/5f1cec7b-1d03-446a-85b7-edb42692c34e/0/?attachment.uuid=7f4fb7d3-3c6e-4e89-ad1b-2443d7e7b2fe)

Video

In this video, virtual mentor Bailey Robert explains the importance of Knowing your audience (Vimeo, 0:56 min). when creating spreadsheets.

(Long URL: https://player.vimeo.com/video/640979054?h=aa089bfbc6)

Deliver document to required stakeholders

Once you have completed and reviewed your spreadsheet, you may be presenting it to an audience or at least printing and distributing it. An essential step in sharing your spreadsheet is knowing your audience and how they will view your work. In a workplace, you will usually be told how to present your Excel files. You should consider some of the presentation options listed below:

On-screen presentation

For this purpose, you might just show the Excel file. Make sure you use charts and make data large enough to be understood by an audience. You can also copy and paste the Excel data or table into a PowerPoint presentation.

Share via email

Converting your Excel file to a PDF file is a great way to share your document via email. PDF files can also be viewed on mobile devices and don't require the Excel application. Simply select 'Save as Adobe PDF' under the 'File' menu in Excel.

Printed copies

When printing, pay attention to the page layout and give your document a header and footer. In Excel, go to 'View' and then 'Page Layout' to see how your document will look in print. Remember to review your organisation's style guide to ensure that your printed document meets all organisational requirements, including alignment and page orientation.

Other formats

Excel lets you export the data in very simple formats like CSV (Comma Separated Values) for use in accounting programs, or you can even make a web page to display your work.

Print worksheet to fit on one page

From the Page Layout Ribbon, select Page Set-up, Page. Then on the Scale to fit tab, select 1-page for width and height.

Print selected range

Highlight the cells to be printed.

From the File menu, select print.

Under Settings, select Print Selection.

Previewing your printout and print to PDF

If you do not have access to a printer, you can preview how your spreadsheet will look by creating a PDF document.

From the File menu, select print.

Under the Printer, select Microsoft Print to PDF and select print.

You will be prompted to save a copy of your spreadsheet as a PDF document.

This document will show you how your spreadsheet will look if you choose to print it.

Sustainability requirementsSustainability means using resources in a way that reduces waste and negative impact on our natural environment. Many organisations have sustainability policies that help them to implement environmental and socially sustainable work practices.

Practice activity

Activity 10: Virtual organisation Gelos Enterprises

Gelos Enterprises' Sustainability Policy and Procedure (pdf) outlines our commitment to the environment, provides procedures for minimising resource wastage, and sets out your responsibilities.

(Long URL: https://share.tafensw.edu.au/share/items/5f1cec7b-1d03-446a-85b7-edb42692c34e/0/?attachment.uuid=74b18d25-f423-4cb5-80d4-c3d594127cdb)

While we are working on our spreadsheets, we must be thoughtful in our use of resources to reduce our impact on the environment. You can contribute to sustainability by reducing the amount of paper and energy you use

Reducing paper

Here are a few ideas that you can easily implement to save paper:

photocopy and print double-sided and in grayscale

use wastepaper as note pads

use 100% recycled paper for office printing and photocopying

keep files and communications electronic as much as possible

replace paper record keeping with secure, reliable digital backups

avoid printing emails unless necessary

take notes and attend meetings with a laptop instead of paper

proofread your work on the screen instead of marking up hard copies

have colleagues review electronic documents

implement pause4print program for all office computers.

Check your knowledge

Activity 11: Finalise and present your spreadsheet

Name three ways to deliver your spreadsheet to relevant people.

Reducing energy

Here are some useful tips to reduce energy use in the workplace:

reduce power usage as much as possible by turning off computers and lights when not in use

all appliances must be switched off at the wall at the end of the day

power down equipment like printers over the weekend

install energy-efficient equipment

install timed switches with motion detectors in public areas so that lights come on only as required

use air-conditioners sparingly

use natural ventilation where possible

install shades over windows to prevent heat build-up

use of solar panels and green power.

Store spreadsheetsYour organisation's style guide and policies will usually provide information on:

where to save files

the file naming convention

backing up of documents.

Standard naming conventions

You should use your organisational file naming convention to describe the document's contents and make your work easy to find.

Cloud and non-cloud-based

You can save your workbook to:

The cloud using an internet cloud storage service such as Microsoft OneDrive ('OneDrive') or a personal Google Drive space.

Non- Cloud-based by saving to your device or removable storage, such as a USB drive.

You can access Microsoft OneDrive as a TAFE NSW student using your TAFE NSW login details.

You can access OneDrive can be accessed through your web browser, or you can install the OneDrive application on your computer. Once you have installed OneDrive, it will appear on your computer as one of your network locations.

Cloud-based storage has several key advantages, including:

able to work on the spreadsheet from any location provided you have internet access

able to access the spreadsheet from any device provided you have internet access

facilitates online collaboration as you can share the spreadsheet via links

does not require memory space in a desktop computer or removable storage

new software versions are automatically updated.

The main disadvantage of cloud-based storage is that it requires internet access to view, open, and edit files, including spreadsheets.

Video

This video explains how-to explains how to save Excel workbooks. Choose styles and layouts (LinkedIn Learning, 3:42 min) change the appearance of charts.

(Long URL: https://www.linkedin.com/learning/excel-charts-in-depth)

This video explains how Using Save or Save As (LinkedIn Learning, 3.34 min) excel workbooks in creating spreadsheets.

(Long URL: https://www.linkedin.com/learning/excel-essential-training-office-365-microsoft-365/using-save-or-save-as)

Organizational requirements

Video

This video explains the importance of Sustainability in the workplace (Vimeo, 0.42 min)

(Long URL: https://player.vimeo.com/video/640979237?h=7737f5b5f3)

Self-check

How did you go?

You have completed the topic Finalise and present spreadsheets. Check the boxes for the tasks you feel confident you can complete.

I can review and edit final spreadsheet and accompanying charts and prepare for delivery according to task requirements.

I can deliver the document to required stakeholders according to organisational requirements, policies and procedures.

I can name and store spreadsheet according to organisational requirements and exit application.

Answers and feedback for practice activitiesPractice activity:Page:

Activity 117

Question:

Outline the points to consider before creating a spreadsheet on the basis of audience

Answer:

Some points to consider on the basis of audience:

Prior knowledge level are they already familiar with the content

Needs and goals how is the document going to meet their need for information or results

Level of education consider language, literacy, numeracy and digital literary ability

Feedback:

To know more about you can refer topic 1

Practice activity:Page:

Activity 117

Question:

What are the points to be considered before handing out the information in the spreadsheet?

Answer:

Some points to be considered before handing out the information in the spreadsheet:

it is essential to make sure it will look good when printed

if you print it in colour, ensure the colours are consistent with the colour theme of the organisation's branding

if you print it in black and white (monochrome), use shading appropriately so that the spreadsheet is easy to read.

Feedback:

[Add feedback]

Practice activity:Page:

Activity 117

Question:

Mention some of the popular applications to produce a spreadsheet

Answer:

Some popular applications are: Microsoft Excel, Google Sheets (part of Google Docs), Numbers (for Apple products only), Open Office, Zoho Sheets

Feedback:

Practice activity:Page:

Activity 218

Question:

1. An organisation's style guide will generally include

2. To reduce injuries caused by intense periods of repetitive movement, it is recommended that Gelos Enterprises staff members:

Answer:

1. Option A

2. Option A

Feedback:

Practice activity:Page:

Activity 326

Question:

Describe the features to consider while designing a spreadsheet

Answer:

Features to consider while designing spreadsheet are:

Page Set-up- Adjust the print borders.

Headers and footers- This allow the spreadsheet to be identified by including dates, page numbers, file name, and file path.

Column width and height- Adjust the row width and column heights to show all data while minimising the need to scroll.

Feedback:

Practice activity:Page:

Activity 326

Question:

How to be consistent in the appearance of spreadsheets?

Answer:

It is important to be consistent in the appearance of all spreadsheets:

the style and point size should be consistent throughout the spreadsheet

ensure you follow organisational policy and procedures for content and format

documents are easier to read without underline and all capital letters. Use font style and point size with care - they should enhance the appearance of your spreadsheet for ease of reading and not confuse your reader.

Feedback:

Practice activity:Page:

Activity 326

Question:

What is a function in a spreadsheet? Give few examples.

Answer:

A function is a pre-written or in-built formula. All functions will begin with an equal sign (=), have a function name, for example- SUM, and include an argument which is what you want to calculate. Some of the functions are:- SUM, Average, Count.

Practice activity:Page:

Activity 4:26

Question:

1. Purpose, audience, and presentation are three main aspects to consider while planning your spreadsheet.

2. Page setup enhances the spreadsheet's appearance and makes the data look more presentable on screen and a printout.

3. Count function allows you to get the number of entries that is in a range of numbers.

Answer:

1.True

2. False

3. True

Practice activity:Page:

Activity 538

Question:

Explain the steps involved in entering data into a spreadsheet.

Why is it important to consult stakeholders and confirm formula to be used?

Answer:

The information you enter into a spreadsheet is called data. An entry made into a cell is either a label, number, or formula.

Steps involved in entering data into a workbook are:

Select the cell where data is to appear.

Type in the data.

Confirm the data entry by pressing Enter or any of the cursor movement keys.

Practice activity:Page:

Activity 538

Question:

Why is it important to consult stakeholders and confirm formula to be used?

Answer:

If you are unsure which formulas to use or how these apply to the spreadsheet you are creating, you should always consult the content author (the person giving you the information) or your supervisor.

Practice activity:Page:

Activity 638

Question:

Match the following

Answer:

Table SEQ Table * ARABIC 10 - Matching answer

Column A Answer Column B

Exponential C +

Addition A *

Multiplication B ^

Feedback:

Practice activity:Page:

Activity 745

Question:

Write the chart types and their use

Answer:

Refer chart types in Topic 4

Feedback:

Practice activity:Page:

Activity 850

Question:

Write the chart styles and their purpose

Answer:

Refer chart styles and layout in Topic 4

Practice activity:Page:

Activity 1157

Question:

Name three ways to deliver your spreadsheet to relevant people.

Answer:

On-screen presentation , Share via email, Printed Copies.

Feedback:

References

Add a chart element :(Long URL: https://www.linkedin.com/learning/excel-charts-in-depth)

Add a chart title :(Long URL: https://www.microsoft.com/en-us/videoplayer/embed/RE269IM?pid=ocpVideo0-innerdiv-oneplayer&postJsllMsg=true&maskLevel=20&market=en-us)

Add and edit data labels :(Long URL: https://www.linkedin.com/learning/excel-charts-in-depth)

Use legends :(Long URL: https://www.linkedin.com/learning/excel-charts-in-depth)

Show or hide a chart legend :(Long URL: https://www.microsoft.com/en-us/videoplayer/embed/RE264wI?pid=ocpVideo0-innerdiv-oneplayer&postJsllMsg=true&maskLevel=20&market=en-us)

column and bar charts :(Long URL: https://www.linkedin.com/learning/excel-charts-in-depth)

line and area charts :(Long URL: https://www.linkedin.com/learning/excel-charts-in-depth)

pie charts and doughnut charts :(Long URL: https://www.linkedin.com/learning/excel-charts-in-depth).

Google Sheets : (Long URL: https://www.google.com/sheets/about/)

Numbers : (Long URL: https://www.apple.com/au/numbers/)

Open Office : (Long URL: https://www.openoffice.org/)

Zoho Sheets : (Long URL: https://www.zoho.com/sheet/).

Image attributionsTable SEQ Table * ARABIC 11 - Image attribution table

Title Page Creator Licence Modified/By

Cover image 1 Photo by Firmbee Unsplash Images copied under licenceN/A

Getting Started 4 Photo by Lukas Blazek Unsplash images copied under licenceN/A

Figure 1- Person using a laptop8 Photo by Fauxels Pexels Images copied under licenceN/A

Topic 1 Select and prepare resources 10 Photo by Pixabay Pexels Images copied under licenceN/A

Figure 2 Developer working in the office

16 Photo by Tim Van der Kuip Unsplash images copied under licenceN/A

Topic 2 Planning spreadsheet 20 Photo by Yan Krukov Pexels Images copied under licenceN/A

REF _Ref91012895 h Figure 3 - Planning Presentation by Ono Kosuki under Pexels licence 22 Photo by Ono Kosuki Pexels Images copied under licenceN/A

Topic 3 Creating spreadsheet 29 Photo by Mikael Blomkvist Pexels Images copied under licenceN/A

Topic 4 Produce charts 40 Photo by Rodnae Production Pexels Images copied under licenceN/A

Figure 4 Survey sheet 42 Photo by Fauxels Pexels Images copied under licenceN/A

Figure 5 Chart Image 44 Photo by Lukas Pexels Images copied under licenceN/A

Topic 5 Finalise and give a presentation 52 Photo by Kampus Pexels Images copied under licenceN/A

Figure 6- Gelos Enterprise logo 54 Gelos Enterprise TAFE NSW N/A

Assessment event 3 of 3: Skills

Criteria

Unit code and name

BSBTEC302 | Design and produce spreadsheetsQualification/Course code and name

Select your Qualification/Course code and name from the dropdown.

Code | Course nameStudent details

Student name

Student number

Version:20230310

Date created:20 December 2022

TAFE NSW 2023RTO Provider Number 90003 | CRICOS Provider Code: 00591E

This assessment can be found in the TAFE NSW Learning Bank.

The content in this document is copyright TAFE NSW 2023 and should not be reproduced without the permission of TAFE NSW. Information contained in this document is correct at time of printing: DATE @ "dd MMMM yyyy" * MERGEFORMAT 21 March 2023. For current information please refer to our website or your teacher or assessor as appropriate.

Assessment instructions

Table 1 Assessment instructions

Assessment details Instructions

Assessment event overview The aim of this assessment is to assess your knowledge and performance in developing spreadsheets through the use of spreadsheet applications.

This assessment is in 2 parts:

Part 1: Store spreadsheets

Part 2: Plan and produce spreadsheets

And is supported by:

Checklist

Assessment feedback

Simulated organisation Gelos EnterprisesBSBTEC302_AE__Appx_Data.xlsxAppendix: Gelos Style Guide extractNote: This assessment may contain links to external resources. Access to the long URL is provided via the External resources Links and URLs section located at the end of this document.

Unit assessment guide Refer to the unit assessment guide (UAG) before attempting this assessment event. The UAG contains information including assessment requirements and how to achieve a satisfactory result.

Submission instructions When you complete this assessment:

read the checklist at the end of the assessment to make sure you have completed everything

keep a copy of all the electronic and hardcopy assessments you submit to TAFE NSW

make sure you have completed the assessment declaration before you submit.

Microsoft Office applications

As a TAFE student, you are entitled to a free copy of the full version of Word, Excel, PowerPoint, and Publisher for your studies.

To access this software go to www.office.com then:

Select Install Apps (top right hand side).

Select Microsoft Office 365.

Save the set up file.

Step through the installation process (Mac or PC version will automatically be detected).

Assessment instructions

For this assessment you will design and produce the following:

Spreadsheet for Mobile phone stock on hand

Spreadsheet for June property sales

Task instructions

The assessor will use the criteria outlined in the following parts to determine if you have satisfactorily completed this assessment event. Follow these instructions to ensure you demonstrate the required knowledge and skills.

Part 1: Store spreadsheets according to organisational requirements

Read the instructions carefully and refer to Gelos Style Guide and Policies as required.

While completing these tasks, it is important that you adhere to the Gelos WHS Procedure Ergonomics policy and any other relevant legislation and codes of practice for work health and safety. Ensure your workspace and computer are set up correctly and that you take regular breaks.

Task 1.1 Create folder and store files

Before you begin the assessment tasks, you will need to create a folder to store downloaded and saved files.

Instructions

Create a new folder: BSBTEC302 Assessment 3.

Figure SEQ Figure * ARABIC 1 Example screenshot of the new folderYou will be using data from an existing spreadsheet. Download the following Excel file: BSBTEC302_AE_Appx_Data.xlsx.

(Note: You may have already downloaded the BSBTEC302_AE_Appx_Data.xlsx spreadsheet containing the data when completing Skills Assessment 2).

Part 2: Plan and produce spreadsheets

For this part of the assessment, you will plan and then produce spreadsheets according to requirements and following the Gelos Style Guide extract.

While completing this task, it is important that you adhere to the Gelos WHS Procedure Ergonomics policy and any other relevant legislation and codes of practice for work health and safety. Ensure your workspace and computer are set up correctly and that you take regular breaks.

Task 2.1 Plan the phone stock on hand spreadsheet

Refer to the below scenario and mobile phone data located in the worksheet tab A3 Sheet 1.

Figure 2 Use data from A3 Sheet 1

Scenario

As well as administering the day-to-day activities of the office and producing business documents, you also provide support to other Gelos managers.

Purchasing Manager, Rob Miller requires a spreadsheet to display all phone stock on hand for the current financial year. He needs this spreadsheet by Thursday morning and has reminded you to follow the Gelos Style Guide extract for formatting and presentation.

Rob has also asked you to create a column chart that displays the total stock on hand for each phone.

Complete the following Spreadsheet Plan:

Table 2 Spreadsheet plan 1

Spreadsheet Plan What is the purpose of the spreadsheet?

Click or tap here to enter text.

Who will be the audience of this spreadsheet?

Click or tap here to enter text.

List two organisational requirements for these tasks (Refer to Gelos Style Guide) Click or tap here to enter text.

Click or tap here to enter text.

What type of chart is to be inserted?

Click or tap here to enter text.

What would be the title of this chart?

Click or tap here to enter text.

When is this task due?

Click or tap here to enter text.

Task 2.2 Produce the phone stock on hand spreadsheet

Using your plan and the following instructions, design and produce the phone stock on hand spreadsheets.

Instructions Phone stock on hand spreadsheet

Table 3 Spreadsheet instructions

Spreadsheet instructions

Select the most appropriate software for this task.

Type (or copy) the A3 Sheet 1 mobile phone data to a new spreadsheet.

Rename this worksheet, ensuring that it reflects the data in the worksheet.

You will be creating formulas to calculate the profit (markup), GST, cost price and selling price for each phone, as well as the total value of each phone model. Markup and GST: First create labels and values for markup and GST. Markup is 45% GST is 10%. Either name these values or use absolute referencing, in your formulas.

Add columns, headings and formulas into your worksheet to calculate the following: Stock Value: This is the value of the stock on hand at cost price. For example, multiply the Cost Price x Quantity.

Markup Value: The markup value will be the profit you make on the item (in this case, 45% of the cost price). For example, multiply the Cost Price x Markup. (Remember to use either an absolute or named cell reference in this formula).

Cost Price (including Markup): This is the value of the Cost Price after the 45% Markup has been included. For example, add together the Cost Price + Markup Value.

GST Amount: Calculate the GST amount for each item. For example, multiply the Cost Price (including Markup) x GST. (Remember to use either an absolute or named cell reference in this formula).

Selling Price: The price that each item will sell for (including the Markup Value and GST Amount). For example, add together the original Cost Price + Markup Value + GST Amount.

Total Sell Value: Calculate the total selling value of each product. For example, multiply the Selling Price x Quantity.

Beneath the spreadsheet data, add labels and formulas to calculate:

Total Value of Stock

Total Sell Value

Number of Products (use the Count or CountA function)

Average Selling Price (use the Average function)

Insert the heading Gelos Enterprises above your data (you may need to insert a row/s): Format as Heading 1. Merge and centre heading across columns.

Format Phone Stock on hand at 30 June as Heading 2 and merge and centre across columns.

Refer to the Gelos Style Guide extract for spreadsheet formatting requirements and incorporate in design.

Adjust column widths so all data is appropriately displayed and ensure wrap text has been applied as necessary.

Apply middle alignment to all headings (so that text is centred between the top and bottom of the cell).

Apply cell borders to outline or separate your data and identify totals.

As a separate sheet, insert a column chart to show the Stock Value for each Model. Add a suitable chart title (you do not need to add a legend).

Rename both sheets with appropriate tab names.

Orientation of both sheets to be landscape.

Footer: Insert a footer for both sheets (following the Gelos Style Guide footer requirements).

Header: Insert a header for both sheets (following the Gelos Style Guide header requirements).

Review the spreadsheet and task. Edit or make corrections if required.

Print preview your worksheets. Ensure the contents of each sheet are balanced on the page (centred evenly between top, bottom, left and right margins) and that each sheet would print on 1 page only.

Save and name your file as per Gelos records and management information procedure and store it in accordance with organisational requirements. For example GE_Phone_stock_june.xlsx.

Close application.

Task 2.3 Plan the property sales data spreadsheetRefer to the below scenario and the property sales data located in the worksheet tab A3 Sheet 2.

Figure 3 Use data from A3 Sheet 2

Scenario

Property Manager, Myer Kingsley requires a spreadsheet to display the June property sales for the following three suburbs: Kappa, Gimmel and Lambda. Data for each suburb will be placed in a separate worksheet.

All worksheets will follow organisational requirements and as per the Gelos Style Guide.

Myer will be presenting this spreadsheet to the Real Estate Institute and needs this spreadsheet by next Tuesday.

Complete the following Spreadsheet Plan:

Table 4 Spreadsheet plan 2

Spreadsheet Plan Who will be the audience of this spreadsheet?

Click or tap here to enter text.

What will you name this spreadsheet file?

Click or tap here to enter text.

List two organisational requirements for this task? (Refer to Gelos Style Guide) Click or tap here to enter text.

Click or tap here to enter text.

How many worksheets are required to store and display your data?

Click or tap here to enter text.

What is the deadline for this spreadsheet task?

Click or tap here to enter text.

Task 2.4 Produce the property sales data spreadsheet

Using your plan and the following instructions, design and produce the property sales data spreadsheet.

Instructions Property sales data spreadsheet

Table 5 Spreadsheet instructions

Spreadsheet instructions

Select the most appropriate software for this task.

Create a new blank workbook and copy the A3 Sheet 2 property sales data (or sheet) and paste to your new workbook.

Sort the data by Suburb in Ascending (A-Z) order.

Insert a new sheet for each of the 3 suburbs.

Rename the three new sheets (each worksheet tab name to match the suburb name. Change the tab colour of each worksheet (Gimmel = blue, Kappa = red and Lambda = Green).

Move each suburbs sales data (also copy the headings) to the appropriate worksheet.

Delete the original A3 Sheet 2 sheet.

For each worksheet: (Tip: Group sheets first) Insert the heading Gelos Enterprises above your data (you may need to insert a row/s): Format as Heading 1. Merge and centre across columns.

Insert the heading June Property Sales as Heading 2 and merge and centre across columns.

Below the data, add headings and formulas that calculate the following:

The Total Sales Value for each suburb

Count the Number of Sales for each suburb.

The Highest Sale Price for each suburb.

The Lowest Sale Price for each suburb.

The Difference in sale prices for each suburb.

Adjust column widths so all data is appropriately displayed.

Apply cell borders to outline or separate your data.

Orientation of all sheets to be landscape.

Footer: Insert a footer for all sheets (following the Gelos Style Guide extract footer requirements).

Header: Insert a header for all sheets (following the Gelos Style Guide extract header requirements).

Tip: Remember to ungroup sheets Review the spreadsheet and task. Edit or make corrections if required.

Print preview your worksheets. Ensure the contents of each sheet are balanced on the page (centred evenly between top, bottom, left and right margins) and the data and chart would print to 1 page.

Save and name your file as per Gelos records and management information procedure and store it in accordance with organisational requirements.

Exit application

Part 3: Finalise spreadsheets

Task 3.1 Finalise spreadsheets

You had earlier advised Property Manager, Rob Miller that you had designed and produced the phone stock on hand spreadsheet, and asked for his feedback (Note: you did not need to create an email to Rob Miller).

Rob has responded, and provided you with the following feedback:

From: Rob Miller

Sent: Monday, 01 September 202X 10:44 AM

To: Administrative Assistant

Subject: Finalising Stock on Hand Spreadsheet

Dear Admin Assistant

Thank you for help with the phone stock on hand spreadsheet.

The spreadsheet looks great however I would like you to make a few changes to the chart:

The Stock on Hand Chart needs to include Data Labels to show the value of the stock on hand for each phone.

Please change the chart title font colour to Gelos blue-grey

Id like you to highlight our highest stock value phone. Please change the fill colour of this column only, to Gelos orange.

We need to include the Gelos Logo in this chart sheet.

Again, thank you for your assistance. If you have further questions, please ask.

Kind regards

Rob MillerProduct Manager

m: 0491 000 000

Instructions Finalise spreadsheets

Open your previously created June phone stock spreadsheet and create a copy of the chart sheet.

Rename this sheet Chart 2.

Make the following changes to the Chart 2 sheet:

Add data labels (to the outside end of columns) showing the value of stock for each phone.

Change the chart title font colour to blue-grey (refer to Gelos colour theme).

Change the column fill colour, of the highest stock value phone only, to orange (refer to Gelos colour theme).

Insert the Gelos Enterprises Logo into the top right corner area of the chart sheet (copy and paste the below logo).

Save and close the file.

Using the Gelos email template on the following page, write to Rob Miller confirming:

that you have made the changes as requested.

the amended chart still follows the Gelos Style guide,

a request to confirm formulae are tested and output meets task requirements,

the final spreadsheet file is attached.

Email From: To: Cc: Date: Subject: Attachment(s): Kind regards

Your Name

Submission checklist

Submit the following for marking:

This completed Assessment event 3 of 3: Skills

Phone stock on hand spreadsheet

Property sales spreadsheet

Checklist

The assessor will use this checklist while observing you completing your assessment. Read the checklist to understand what skills you need to demonstrate/ items you need to complete or submit. You must meet all the criteria.

Note that S = Satisfactory and U/S = Unsatisfactory.

Table 6 Checklist

Task number Did the student do the following? S U/S S U/S Assessor commentsDescribe the students ability in demonstrating the required skills and knowledge. Record your observations in enough detail to demonstrate your judgement of the students performance against the criteria required.

Part 1 Task 2.1 Create a new folder: BSBTEC302 Assessment 3

Date:

Part 2 Task 2.2

1 Plan the spreadsheet by providing all relevant details in the template provided (Table 2 Spreadsheet plan 1).

Task 2.2

1 Most appropriate software selected.

2 Data from the A3 Sheet 1 worksheet copied/typed onto new spreadsheet

3 Worksheet tab renamed to reflect data in the worksheet

4 Labels for Markup and GST added to spreadsheet, values added and cells named or referred to in formulae using absolute referencing

5 Stock value (at cost price) formula added using cell names or absolute referencing

6 Markup Value formula added using cell names or absolute referencing

7 Cost Price (incl Markup) formula added using cell names or absolute referencing

8 GST Amount formula added using cell names or absolute referencing

9 Selling Price formula added using cell names or absolute referencing

10 Total Sell Value formula added using cell names or absolute referencing

11 Beneath the spreadsheet data, add labels and formulas to calculate:

Total Value of Stock

Total Sell Value

Number of Products (using the Count or CountA function)

Average Selling Price (using the Average function)

12 Heading Gelos Enterprises added above data and formatted as Heading 1. Merged and centred across columns.

13 Heading Phone Stock on hand at 30 June formatted as Heading 2. Merged and centred across columns

14 Gelos Style Guide for spreadsheet formatting incorporated in design

15 Column widths adjusted so all data is appropriately displayed, wrap text applied as necessary

16 Apply middle alignment to all headings

17 Apply cell borders to outline or separate data

18 Column chart added as separate sheet to show the Stock Value for each Model. Suitable chart title added and no legend displayed

19 Both sheets renamed with appropriate tab names

20 Orientation of both sheets is landscape

21 Insert a footer for both sheets following the Gelos Style Guide

22 Insert a header for both sheets following the Gelos Style Guide

23 Reviewed the spreadsheet and task. Errors or corrections edited if required

24 Print preview your worksheets. Ensure the contents of each sheet are balanced on the page and each sheet would print to 1 page

25 Save and name file as per Gelos records and management information procedure and store it in accordance with organisational requirements

Task 2.3 Plan the spreadsheet by providing all relevant details in the template provided (Table 4 Spreadsheet plan 2).

Task 2.4

1 Most appropriate software selected.

2 New workbook created and copied the A3 Sheet 2 property sales data to the new workbook

3 Sorted the data by Suburb in Ascending (A-Z) order

4 Inserted a new sheet for each of the 3 suburbs

5 Renamed the 3 new sheets (each worksheet tab name to match the suburb name).

Changed the tab colour of each worksheet (Gimmel = blue, Kappa = red and Lambda = Green).

6 Moved each suburbs sales data (also copy the headings) to the appropriate worksheet.

7 Deleted the original A3 Sheet 2 sheet

8 Insert the heading Gelos Enterprises above the data and format as Heading 1. Merge and centre across columns

9 Insert the heading June Property Sales as Heading 2 and merge and centre across columns.

10 Below the data, add headings and formulas that calculate:

The Total Sales Value

Count the Number of Sales

The Highest Sale Price

The Lowest Sale Price

The Difference in sale prices

11 Adjust column widths so that all data is appropriately displayed

12 Apply cell borders to outline or separate data

13 Change the orientation of the spreadsheet to landscape

14 Insert a footer following the Gelos Style Guide

15 Insert a header following the Gelos Style Guide

16 Reviewed the spreadsheet and task. Errors or corrections edited if required

17 Print preview the worksheet. Ensure contents are balanced on the page and it will print to 1 page

18 Name your file as per Gelos records and management information procedure and store it in accordance with organisational requirements

Part 3 Task 3.1

1 Open your previously created June phone stock spreadsheet and create a copy of the chart sheet

2 Rename copied sheet Chart 2

3 Add data labels (to the outside end of columns) showing the value of stock for each phone

4 Change the chart title font colour to blue-grey as per Gelos colour theme

5 Change the column fill colour, of the highest stock value phone only, to orange as per Gelos colour theme

6 Insert the Gelos Enterprises Logo into the top right corner area of the chart sheet

7 Save and close the file

8 Using the Gelos email template, write to Rob Miller confirming:

that you have made the changes as requested

the amended chart still follows the Gelos Style guide

a request to confirm formulae are tested and output meets task requirements

the final spreadsheet file is attached.

Additional evidence for verification of assessment

Assessors may ask questions to clarify your understanding during the assessment event.

You may be asked questions:

to clarify your understanding (for example, Why did you select that particular piece of equipment?)

to capture contingencies that may form part of the demonstration (for example, dealing with faults or unexpected events)

to prevent a work, health and safety incident.

If questions are required during the assessment event, these questions and your responses will be recorded in this table.

Table 7 Additional evidence

Questions asked by assessor Student responses

External resources Links and URLs

Long URLs and permalinks are provided for access to content when the assessment is not used digitally, for example, not clickable.

Table SEQ Table * ARABIC 8 Long URLs

Resource Name Long URL

The Learning Bank https://share.tafensw.edu.au/share/home.do

Simulated organisation Gelos Enterprises https://share.tafensw.edu.au/share/items/d0b458dc-3922-409d-b1fe-9a2f785f4a38/0/?attachment.uuid=5f1677bf-8296-4137-ae33-8b9e30bad1ab

BSBTEC302_AE_Appx_Data.xlsx https://share.tafensw.edu.au/share/items/d17aa037-a71b-4a21-8761-9fc3d7d174fe/0/?attachment.uuid=cebf4c54-f031-4903-9b25-db0b09dc72ca

Gelos WHS Procedure Ergonomics https://share.tafensw.edu.au/share/file/b7f3345c-19b3-422a-8b3c-5eb1d2950cee/1/GE_Ergonomics-procedure_admin.pdf

Gelos records and management information procedure https://share.tafensw.edu.au/share/file/5f1cec7b-1d03-446a-85b7-edb42692c34e/1/GE_Records-information-management-ICT-procedure.pdf

Appendix

Gelos Style Guide Extract

Below is an extract from the Gelos Style Guide. For consistency in design and layout ensure all spreadsheets meet the below style guide requirements.

Spreadsheet elements will reflect the Gelos colour theme:

Spreadsheets (Extract)

Style Requirements

Normal/Default Font: Calibri

Font Size: 11 pt

Font Colour: Black

Heading 1 Font: Georgia

Font Size: 18 pt, bold

Font Colour: Orange (R221, G171, B94)

Heading 2 Font: Georgia

Font Size: 14 pt, bold

Font Colour: Turquoise (R7, G122, B153)

Column, Row and Label Headings Font: Calibri

Font Size: 12 pt

Font Colour: White

Cell Alignment: Centre

Cell Fill Colour: Blue-Grey (R66, G71, B84)

Margins All margins - left, right, top and bottom: 2 cm

Layout All data to be centred horizontally and vertically on the page. All spreadsheets are to be reviewed via Print Preview prior to delivery.

Sustainability Where possible spreadsheets to fit one page

Cell alignment Text: left-aligned (unless column heading)

Numbers/Dates: right-aligned

Borders Add suitable borders to identify cells containing data

Total rows only must be formatted with top and double bottom borders

Number formatting Dollar values with cents: Currency style with two decimal places eg. $21.23All other numerical data: Zero decimal places

Header Font: Calibri

Font Size: 9 pt

Font Colour: Black

Left: Sheet tab name

Margin: 0.8

Footer Font: Calibri

Font Size: 9 pt

Font Colour: Black

Left: (Automatic) File name

Centre: Current date eg. 14/11/2023

Right: Authors name

Margin: 0.8

Chart Styles 2D column chart or pie chart

Must have an appropriate chart title at the top

If a legend is required, it should sit to the right of the chart

Axis titles and percentage (%) labels where appropriate

Chart title Font: Calibri

Font Size: 12 pt

Font Colour: Black

This page is not required for online assessment submissions.

Student assessment declaration

This assessment is my original work and has not been:

copied from any source without proper referencing

written for me by any other person except where such collaboration has been approved by a teacher or assessor.

Student signature and date

Reasonable adjustment

Reasonable adjustment was in place for this assessment event.

If so, please provide details of any reasonable adjustment strategies that were implemented:

[Insert reasonable adjustment strategies]

Assessment outcome

Satisfactory Unsatisfactory

Comments

[Insert comments]

Assessor name, signature and date

Student acknowledgement of assessment outcome

[Would you like to make any comments about this assessment?]

Student name, signature and date

Assessment event 2 of 3: Project

Criteria

Unit code and name

BSBTEC302 | Design and produce spreadsheetsQualification/Course code and name

Select your Qualification/Course code and name from the dropdown.

Code | Course nameStudent details

Student name

Student number

Version:20230310

Date created:20 December 2022

TAFE NSW 2023RTO Provider Number 90003 | CRICOS Provider Code: 00591E

This assessment can be found in the TAFE NSW Learning Bank.

Assessment instructions

Table 1 Assessment instructions

Assessment details Instructions

Assessment event overview The objective of this assessment is to assess your knowledge and performance in developing spreadsheets through the use of spreadsheet applications.

This assessment is in 3 parts:

Part 1: Store spreadsheets

Part 2: Use Help functions

Part 3: Plan and produce spreadsheets

And is supported by:

An assessment checklist

Assessment feedback

Unit assessment guide Refer to the unit assessment guide (UAG) before attempting this assessment event. The UAG contains information, including assessment requirements and how to achieve a satisfactory result.

Submission instructions When you complete this assessment:

read the checklist at the end of the assessment to make sure you have completed everything

keep a copy of all the electronic and hardcopy assessments you submit to TAFE NSW

make sure you have completed the assessment declaration before you submit.

Microsoft Office applications

As a TAFE student, you are entitled to a free copy of the full version of Word, Excel, PowerPoint, and Publisher for your studies.

To access this software go to www.office.com then:

Select Install Apps (top right hand side).

Select Microsoft Office 365.

Save the set up file.

Step through the installation process (Mac or PC version will automatically be detected).

Taking screenshots

During this assessment, you are required to take screenshots.

Capturing a screenshot

PC users:

There are 2 methods by that PC users can capture screenshots:

Press Shift + Windows Logo Key + S (screen will go into a dark screen mode)

Draw a box around the part of the screen you wish to capture

Go to your document and Paste (Ctrl V). The screenshot will be pasted as an image

Alternatively, you may use an application such as Snipping Tool to capture screenshots.

Mac users:

Press Shift + Command Key + 4 (Capture screenshot on your Mac)

Task instructions

The assessor will use the criteria outlined in the following tasks to determine if you have satisfactorily completed this assessment event. Follow these instructions to ensure you demonstrate the required knowledge and skills.

For this part of the assessment, you will plan, design and produce the following:

1.Spreadsheet for Sales figures for June and July.

2.Spreadsheet for Sales representatives payroll figures.

Part 1: Store spreadsheets according to organisational requirements

Read the instructions carefully and refer to Gelos Style Guide and policies as required.

While completing this task, you must adhere to the Gelos WHS Procedure Ergonomics policy and any other relevant legislation and codes of practice for work health and safety. Ensure your workspace and computer are set up correctly and that you take regular breaks.

Task 1.1 Create folder and store files

Before you begin the assessment tasks, you must create a folder to store your downloaded and saved files.

Instructions

Create a new folder (on your device or as instructed by your teacher).

Name this folder: BSBTEC302 Assessment 2. Your new folder structure should look similar to the following:

Figure 1 Example of the new folder structure

You will be using data from an existing spreadsheet. Download the following Excel file: BSBTEC302_AE_Appx_Data.xlsx.

Store this file in your BSBTEC302 Assessment 2 folder.

Capture a screenshot of your BSBTEC302 Assessment 2 folder showing the downloaded data file and paste it in the box below:

Part 2: Use Help functions

Task 2.1 Use required Help functions

Throughout this assessment, you will be using various Excel features. At times, you may need help troubleshooting these features.

For this task, you will use Excel's Help Feature to find out how to:

Wrap text in a cell, and

Group worksheets.

You will capture screenshots of the help topics you found.

Instructions

Use the Help function (Tip: Windows = F1, Mac = Help key or use Google) to find out how to wrap text in a cell.

Capture a screenshot of this Help topic and paste it in the box below.

In your own words, summarise the steps you would take to wrap text in a cell:

Table SEQ Table * ARABIC 2 Step summary

Step Action

1 2 3 Use the Help function to find out how to group selected worksheets. Capture a screenshot of this Help topic (Tip: you do not need to screenshot the entire help article) and paste it in the box below.

Part 3: Plan and produce spreadsheets

Scenario

You work for Gelos Enterprises as an Administrative Assistant and report to the Gelos Project Officer, Kay McCormack. Your role involves administering the day-to-day activities of the office as well as producing a range of business documents such as spreadsheets.

Kay McCormack has emailed you, asking for your help preparing spreadsheets for the upcoming Senior Management Meeting.

From: Kay McCormack

Sent: Monday, 01 August 202X 8:44 am

To: Administrative Assistant

Subject: Spreadsheets for Senior Management Meeting

Hi Admin Assistant

As we have our Senior Management Meeting next week, I will need your help to create spreadsheets displaying our June and July sales figures. You have been given the raw data in a separate spreadsheet file.

Please organise the June and July data and place it into separate sheets (one sheet for June and one for July). Both sheets should remain in one workbook (file).

To visually represent the June and July sales, I will need you to insert a chart into each sheet. I will also require you to insert formulas.

Please follow the Gelos Style Guide for all spreadsheet formatting and layout requirements and store the completed file in the BSBTEC302 Assessments 2 folder.

I will need these spreadsheets by 5.00 pm Wednesday.

Don't hesitate to ask if you have further questions.

Kind regards

Kay McCormackProject Officer

Task 3.1 Plan the monthly sales data spreadsheet

Refer to Kay McCormack's email as well as the Sales Data located in the worksheet tab A2 Sheet 1:

Figure 2 Use data from A2 Sheet 1

The Plan

Before you begin this first task, you will need to plan the spreadsheet (refer to Kay McCormack's email).

Table SEQ Table * ARABIC 3 Spreadsheet plan 1

Spreadsheet Plan 1

Gelos' policy is for staff to use Microsoft 365 to produce business documents. What software application will you use for this task?

Click or tap here to enter text.

What is the purpose of this task?

Click or tap here to enter text.

When does this task need to be completed?

Click or tap here to enter text.

Who will be the audience of these spreadsheets?

Click or tap here to enter text.

List 4 presentation format requirements (refer to Appendix - Gelos Style Guide extract) Click or tap here to enter text.

Click or tap here to enter text.

How many worksheets are required to store and display your data?

Click or tap here to enter text.

What needs to be inserted into each sheet?

Click or tap here to enter text.

Task 3.2 Design and produce the monthly sales data spreadsheets

Design and create the monthly sales data spreadsheets using your plan and the following instructions.

Instructions monthly sales data spreadsheets

Table SEQ Table * ARABIC 4 Spreadsheet instructions 1

Spreadsheet instructions

Select the most appropriate software for this task.

Type (or copy) the data from the A2 Sheet 1 worksheet (the data for June and July must be displayed as a separate sheet).

Ensure the worksheet tab names reflect the month (for example, June Sales).

For both the June and July sheets: Tip: group sheets Include a new column, and add a label: Total Sales

Calculate the total sales for each Sales Representative

Below the data, add a new label: Weekly Total

Calculate the totals for each week

Format the following column headings: Region, Sales Representative, Week 1, Week 2, Week 3, Week 4 and Total Sales (following the Gelos Style Guide extract).

Adjust column widths so all data is appropriately displayed.

Apply middle alignment to all headings (so they are centred between the top and bottom of the cell).

Insert the main heading: Gelos Enterprises above Monthly Sales Figures. You may need to insert a row(s): Format as Heading 1. Merge and centre across columns.

Below the data, include labels and formula functions to calculate the following:

Maximum Total Sales

Minimum Total Sales

Average Total Sales

Format all labels and data as per the Gelos Style Guide.

Apply cell borders to outline or separate your data

Ensure the top and bottom border for the Weekly Total row follow the Gelos Style Guide.

Change the orientation of both sheets to landscape.

Footer: Insert a footer for both sheets (following the Gelos Style Guide footer requirements).

Header: Insert a header for both sheets (following the Gelos Style Guide header requirements).

Tip: Remember to ungroup sheets. On the June Sales worksheet, select the most appropriate chart that displays the Sales Representatives and their total sales.

Include chart labels with the percentages displayed.

Add an appropriate chart title.

Place the chart below the worksheet data.

Placement of the legend is to follow Gelos Style Guide.

On the July Sales worksheet, create another suitable chart that displays the Sales Representatives and their Weekly Sales. (Do not include the totals).

Placement of the legend is to follow Gelos Style Guide

Add an appropriate chart title.

Review the spreadsheet and task. Edit or make corrections as required.

Print preview your worksheets. Ensure the contents of each sheet are balanced on the page (centred evenly between top, bottom, left and right margins), and each sheet prints to 1 page.

Save and name your file as per Gelos records and management information procedure and store it in accordance with organisational requirements. For example, GE_June_July_Sales.xlsx.

Exit application

Scenario

Your supervisor, Kay McCormack, has assigned another task to you.

The Accounts Department has asked for assistance calculating the Annual payroll figures for Gelos Sales Representatives.

This spreadsheet is to be completed by Thursday morning.

You have been provided with the data (A2 Sheet 2). You will need to insert formulas and format the worksheet following the Gelos Style Guide extract.

Figure 3 Use data from A2 Sheet 2

The Plan

Before you begin this task, you will need to plan the worksheet.

Table SEQ Table * ARABIC 5 Spreadsheet plan 2

Spreadsheet Plan 2

What is the purpose of the spreadsheet?

Click or tap here to enter text.

When does the spreadsheet need to be completed?

Click or tap here to enter text.

Who will be the audience of this spreadsheet?

Click or tap here to enter text.

List 2 format requirements (refer to the Gelos Style Guide extract list 2 different format requirements to Spreadsheet Plan 1): Click or tap here to enter text.Click or tap here to enter text.

What would be a suitable name for this file?

Click or tap here to enter text.

Task 3.4 Produce the annual payroll figures spreadsheetDesign and produce the monthly sales data spreadsheets using your plan and the following instructions.

Instructions annual payroll figures spreadsheet

Table SEQ Table * ARABIC 6 Spreadsheet instructions 2

Spreadsheet instructions Type (or copy and paste) the A2 Sheet 2 Payroll data to a new Excel file (workbook).

Insert the heading Gelos Enterprises above your data (cell A1). Format as Heading 1.

Format Payroll Calculations as Heading 2.

Merge and centre the main headings across columns.

Format the column headings: Sales Representative, Annual Gross Salary, Monthly Gross Salary and Total Salary Package.

Insert formulas that calculate: Monthly Gross Salary for each Sales Representative (=Annual Gross Salary divided () by 12).

Calculate the Total Salary Package for each Sales Representative. For example, =Annual Gross Salary + (Annual Gross Salary x Superannuation*).Note: brackets must be used in this formula.

* Superannuation % must be either named or use absolute referencing in your formula.

Adjust column widths so that all data is appropriately displayed.

Use wrap text for column headings if required.

Apply middle alignment to all headings (so they are centred between the top and bottom of the cell).

Format all figures and labels as per the Gelos Style Guide.

Ensure the worksheet tab name reflects the data in the worksheet.

Change the orientation of the spreadsheet to landscape.

Footer: Insert a footer (following the Gelos Style Guide footer requirements).

Header: Insert a header (following the Gelos Style Guide header requirements).

Format the spreadsheet according to Gelos Style Guide.

Review the spreadsheet and task. Edit or make corrections if required.

Print preview your worksheet. Ensure contents are balanced on the page (centred evenly between the top, bottom, left and right margins), and the sheet prints to 1 page.

Name your file as per Gelos records and management information procedure and store it in accordance with organisational requirements.

Exit application

Submission checklist

Submit the following for marking:

This completed Assessment event 2 of 3: Project

The completed spreadsheet files from Part 3

Appendix

Gelos Style Guide extract

Below is an extract from the Gelos Style Guide. For consistency in design and layout, ensure all spreadsheets meet the below style guide requirements.

Spreadsheet elements will reflect the Gelos colour theme:

Spreadsheets (Extract)

Style Requirements

Normal/Default Font: Calibri

Font Size: 11 pt

Font Colour: Black

Heading 1 Font: Georgia

Font Size: 18 pt, bold

Font Colour: Orange (R221, G171, B94)

Heading 2 Font: Georgia

Font Size: 14 pt, bold

Font Colour: Turquoise (R7, G122, B153)

Column, Row and Label Headings Font: Calibri

Font Size: 12 pt

Font Colour: White

Cell Alignment: Centre

Cell Fill Colour: Blue-Grey (R66, G71, B84)

Margins All margins - left, right, top and bottom: 2 cm

Layout All data to be centred horizontally and vertically on the page. All spreadsheets are to be reviewed via Print Preview prior to delivery.

Sustainability Where possible spreadsheets to fit one page

Cell alignment Text: left-aligned (unless column heading)

Numbers/Dates: right-aligned

Borders Add suitable borders to identify cells containing data

Total rows only must be formatted with top and double bottom borders

Number formatting Dollar values with cents: Currency style with two decimal places eg. $21.23All other numerical data: Zero decimal places

Header Font: Calibri

Font Size: 9 pt

Font Colour: Black

Left: Sheet tab name

Margin: 0.8

Footer Font: Calibri

Font Size: 9 pt

Font Colour: Black

Left: (Automatic) File name

Centre: Current date eg. 14/11/2023

Right: Author's name

Margin: 0.8

Chart Styles 2D column chart or pie chart

Must have an appropriate chart title at the top

If a legend is required, it should sit to the right of the chart

Axis titles and percentage (%) labels where appropriate

Chart title Font: Calibri

Font Size: 12 pt

Font Colour: Black

Checklist

The assessment checklist lists the requirements for each task in this assessment as outlined in the student's assessment instructions. The assessor will use this checklist to ensure all required tasks have been completed and submitted and provide feedback for each task.

Note that S = Satisfactory and U/S = Unsatisfactory.

Table 7 Checklist

Task number Did the student do the following? S U/S S U/S Assessor comments

Record your comments in enough detail to demonstrate your judgement of the student's performance against the criteria required.

Part 1 Date:

Task 1 Create a folder for the project named BSBTEC302 Assessment 2

Task 3 Download the required data file BSBTEC302_AE_Data.xlsx

Task 4 Save the downloaded file to designated folder.

Task 5 Provide a screenshot of the assessment folder showing the downloaded data file.

Part 2 1 Use Help to find out how to wrap text in a cell

2 Provide a screenshot of the Help topic.

3 In your own words, summarise the steps to wrap text in a cell.

4 Use the Help function to find out how to group selected worksheets. Provide a screenshot of the Help topic.

Part 3 Task 3.1 1 Plan the spreadsheet by providing all relevant details in the template (Table 2 Spreadsheet plan 1).

Task 3.2

1 The most appropriate software selected.

2 Data from the A2 Sheet 1 worksheet copied/typed onto separate sheets for June and July.

3 Worksheet tab names reflect the month (for example, June Sales).

4 Add a new column and add a label: Total Sales. Add a formula to calculate the total sales for each Sales Representative.

5 Below the data, add a new label: Weekly Total. Add a formula to calculate the totals for each week.

6 Format column headings following the Gelos Style Guide

7 Adjust column widths so all data is appropriately displayed

8 Apply middle alignment to all headings

9 Insert the main heading: Gelos Enterprises above Monthly Sales Figures.

Format as Heading 1. Merge and centre across columns.

10 Below the data, include labels and formula functions to calculate Maximum Total Sales, Minimum Total Sales and Average Total Sales

11 Format all labels and data as per the Gelos Style Guide

12 Apply cell borders to outline or separate data

13 Top and bottom border for the Weekly Total row follow the Gelos Style Guide

14 Change the orientation of both sheets to landscape

15 Insert a footer for both sheets following the Gelos Style Guide

16 Insert a header for both sheets following the Gelos Style Guide

17 On the June Sales worksheet, select the most appropriate chart that displays the Sales Representatives and their total sales

18 Include chart labels with the percentages displayed

19 Add an appropriate chart title

20 Display chart on sheet below data

21 Place the legend following Gelos Style Guide

22 On the July Sales worksheet, create another suitable chart that displays the Sales Representatives and their Weekly Sales but does not include the totals

23 Place the legend following Gelos Style Guide

24 Add an appropriate chart title

26 Print preview your worksheets. Ensure the contents of each sheet are balanced on the page and each sheet prints to 1 page

27 Save and name file as per Gelos records and management information procedure and store it in accordance with organisational requirements.

Task 3.3

1 Plan the spreadsheet by providing all relevant details in the template (Table 5 Spreadsheet plan 2).

Task 3.4

1 Data from the A2 Sheet 2 worksheet copied/typed into new spreadsheet file.

2 Insert the heading Gelos Enterprises above data and format as Heading 1

3 Format Payroll Calculations as Heading 2.

4 Merge and centre the main headings across columns

5 Format the column headings: Sales Representative, Annual Gross Salary, Monthly Gross Salary and Total Salary Package

6 Insert formulas to calculate Monthly Gross Salary for each Sales Representative

7 Insert formulas to calculate the Total Salary Package for each Sales Representative. Superannuation cell named or absolute reference used in the formula.

8 Adjust column widths so that all data is appropriately displayed

9 Use wrap text for column headings if required

10 Apply middle alignment to all headings

11 Format all figures and labels as per the Gelos Style Guide

12 Rename the worksheet tab name to reflect the data in the worksheet

13 Change the orientation of the spreadsheet to landscape

14 Insert a footer following the Gelos Style Guide

15 Insert a header following the Gelos Style Guide

16 Format the spreadsheet according to Gelos Style Guide

18 Print preview the worksheet. Ensure contents are balanced on the page, and it will print to 1 page

19 Name your file as per Gelos records and management information procedure and store it in accordance with organisational requirements

Additional evidence for verification of assessment

Assessors may ask questions to clarify your understanding during the assessment event.

You may be asked questions:

to clarify your understanding (for example, Why did you select that particular piece of equipment?)

to capture contingencies that may form part of the demonstration (for example, dealing with faults or unexpected events)

to prevent a work, health and safety incident.

If questions are required during the assessment event, these questions and your responses will be recorded in this table.

Table 7 Additional evidence

Questions asked by assessor Student responses

External resources Links and URLs

Long URLs and permalinks are provided for access to content when the assessment is not used digitally, for example, not clickable.

Table 9 Long URLs

Resource Name Long URL

The Learning Bank https://share.tafensw.edu.au/share/home.do

Simulated organisation Gelos Enterprises https://share.tafensw.edu.au/share/items/d0b458dc-3922-409d-b1fe-9a2f785f4a38/0/?attachment.uuid=5f1677bf-8296-4137-ae33-8b9e30bad1ab

BSBTEC302_AE_Appx_Data.xlsx https://share.tafensw.edu.au/share/items/d17aa037-a71b-4a21-8761-9fc3d7d174fe/0/?attachment.uuid=cebf4c54-f031-4903-9b25-db0b09dc72ca

Snipping tool https://support.microsoft.com/en-gb/windows/use-snipping-tool-to-capture-screenshots-00246869-1843-655f-f220-97299b865f6b

Capture screenshot on your Mac https://support.apple.com/en-au/HT201361

Gelos WHS Procedure Ergonomics https://share.tafensw.edu.au/share/file/b7f3345c-19b3-422a-8b3c-5eb1d2950cee/1/GE_Ergonomics-procedure_admin.pdf

Gelos records and management information procedure https://share.tafensw.edu.au/share/file/5f1cec7b-1d03-446a-85b7-edb42692c34e/1/GE_Records-information-management-ICT-procedure.pdf

This page is not required for online assessment submissions.

Student assessment declaration

This assessment is my original work and has not been:

copied from any source without proper referencing

written for me by any other person except where such collaboration has been approved by a teacher or assessor.

Student signature and date

Reasonable adjustment

Reasonable adjustment was in place for this assessment event.

If so, please provide details of any reasonable adjustment strategies that were implemented:

[Insert reasonable adjustment strategies]

Assessment outcome

Satisfactory Unsatisfactory

Comments

[Insert comments]

Assessor name, signature and date

Student acknowledgement of assessment outcome

[Would you like to make any comments about this assessment?]

Student name, signature and date

Download Solution Now

Uploaded By : Pooja Dhaka
Posted on : February 28th, 2025
Downloads : 0
Views : 116

BSBTEC302

Download Solution Now

Download Solution Now

Choose a Plan

Premium

Gold

Silver

BSBTEC302

Download Solution Now

Download Solution Now

Choose a Plan

Premium

Gold

Silver

Request a Call Back