CERT II/III IN BUSINESS

1. CERT II/III IN BUSINESS CREATE AND USE SPREADSHEETS

2. SECTION I: (Activity 1) This section addresses the following – Ergonomics: Adjust your workspace, furniture and equipment to suit you. This means having everything in easy access for the user. You should be comfortable in able to reach your telephone, computer, keypad, files. Good ergonomics is not having to turn or stretch or contort your body to reach anything. If you continually have to reach or twist and turn whilst you are working you will strain muscles causing discomfort and pain. Working Safely: Over 300 people will injure themselves in the workplace on any average day in Australia. This is why it is important to have a good knowledge of the WHS Policies and Procedures that are in place in your organisation. You are usually informed of these policies and procedures when your induction is carried out on your first day of employment. If you have not been advised of the policies and procedures you should ask your supervisor or manager about this.

3. Due to the large emphasis on the use of technology in the workplace, one of the most common hazards is the use of the computer. Sitting in front of the computer, looking at the screen and using the keyboard for extended periods of time can result in back pain, sore wrists and hands, stiff neck and shoulders and eyestrain. Ensure that your workspace is set up to suit you. There is also a range of equipment that can also assist you to ensure your health and safety whilst at the computer.

4. YOUR WORKSPACE (Activity 1) Display Monitor: Should be positioned so the distance from the eye to the screen can be adjusted. The centre of the screen should be angled at 15 to 25 degrees below eye level. This will alleviate neck and shoulder pain and strain on your eyes. It should also be tiltable. Chair:Should be easily adjusted, have back support and be fitted with castors (preferable five) Posture: Should be as shown with right angles at the elbow, hip and knee. Your head should be held in a neutral position facing straight ahead with the eyes gazing forward or slightly down. Footrest: This may be needed if your feet do not comfortably reach the floor. Your feet should reach and touch the floor in a flat, relaxed manner Support Items: Support items that can be used to assist you with following the WHS objectives are: Keyboard support tableDocument HoldersAnti Glare Screens Office lighting and room temperature should be comfortable to all.

5. ENERGY AND RESOURCE SAVING TECHNIQUES • The overall objective of organisational policies and statutory requirements is to reduce the harmful health and environmental impacts of waste. In order to meet this objective, it is particularly important to: • Reduce the amount of office waste • Encourage staff, suppliers & contractors to minimise the volume of packaging used & recover and recycle packaging where practicable. • Process all waste in accordance with legal requirements and best practice • Provide facilities to recycle waste paper, cardboard, plastic, glass, toner cartridges and cans • Manage heating and air conditioning across the organisation to ensure the most efficient use of energy • Monitor data on energy and resource consumption in all areas • Encourage staff to save energy through activities to raise awareness in the workplace and art home • Promote a “Lights Out Policy” to ensure lights are switched off overnight and when not needed • Monitor, record and audit water consumption in buildings wherever possible • Use water-efficient appliances and equipment • Install and maintain flow restricting devices and more efficient technologies where it is practicable to do so • Ensure all leaks and faults are dealt with in a reasonable time frame

6. CONSERVING RESOURCES (Activity 1 & 2) This is to use resources in such ways to provide the most efficient, economic and environmentally sustainable use. Conserving resources in a green office by using the three “R’s” REDUCE RECYCLE REUSE • OTHER WAYS TO HELP CONSERVE RESOURCES IN THE WORKPLACE ARE: • Switch off power and lights when not in use • Set equipment to power saving mode • Reuse paper for rough drafts, notepaper • Preview documents before printing • Double side print wherever necessary • Use a paper recycling bin for all waste paper • Install power saving light bulbs • Bring your own cup to work • Turn computers, monitors, printers and copiers off at night when not in use. • Keep air conditioning vents clear of blockages and free of dust • Close the door when air conditioning is on

7. IDENTIFYING SPREADSHEET TASK REQUIREMENTS • Microsoft Excel is a spreadsheet package that allows you to organise data, make simple and complex calculations and analyse information. Charts can be easily generated for use in reports. Excel replaces pen and paper using formulae with accurate results. A spreadsheet looks like a large grid and is divided in columns and rows. Each column is represented by a letter and a row by a number. At the meeting point of each column and row is a cell, each cell has a cell reference number. An excel file is like a workbook and so it may contain several sheets just like pages in a book. These sheets are initially named Sheet 1/ Sheet 2/ Sheet 3 ect. You may change this by double clicking on the name and retyping your title. • When you are designing your worksheet you may need to ask several questions to ensure that your design meets organisational standards and the needs of the task at hand. • Some points that you may consider when planning your design : • The purpose of the spreadsheet • Information that needs to be included • Headings needed to explain the information in the spreadsheet • The best layout for the information – rows or columns. • Some specifications might include: • Common features such as shading all input cells • Adding a print date and spreadsheet version number in the body and footer of the spreadsheet • Removing all worksheets not in use • Renaming the default worksheet name (instead of Sheet 1, Sheet 2 ect) • Guidelines or Procedures might include: • Spreadsheet development according to user requirement specifications • Documentation of spreadsheet formulas and additional functionality • User testing of additional spreadsheet functions • Spreadsheet copies printed with final layout and formulas displayed • A meeting to consult with relevant personnel will clarify these tasks and determine the type of input, design and layout required

8. SPREADSHEET TERMINOLOGY

10. COLUMN ROW HEADING HEADER

11. Simple Formula

12. Cert III Activity 2 What are spreadsheets used for? A. Name at least three things that you can think of that spreadsheets might be used for. How many worksheets are contained in an new excel file, by default (before it is changed). What are the worksheets called? A. When you first open an excel sheet, how many sheets appear on the bottom. Cert II Activity 3 • Create a simple plan in sketch form for a spreadsheet using the following information: • Sales report for the month of January • Staff names should be input to Column A with Weeks 1 – 4 also in columns • Number of sales should be totalled for each staff member and each week • You are to draw this on a piece of paper and hand it to your assessor.

13. RIBBON & QUICK ACCESS TOOLBAR: This is a panel that houses command buttons and icons. It organises functions in sets of tabs and groups together relevant commands. Not all command are displayed, you may have to click on the arrow in the bottom right corner to expand the selection. • HOME TAB: This includes: Clipboard – cut, copy, paste & format painter functions, Font – includes font, font sizes, colour ect, Alignment –Buttons to align text to left, right, centre, justified, Merge & Centre cells. Number - formatting features such as percent style, comma style, decimal places. Styles – here you can apply either a predefined style or create your own to the cells. Cells - provides buttons to insert, delete and format cells. Editing – This function includes Auto Sum, Fill, Clear, Sort and filter, find and select functions. • FILE TAB: Displays options such as: New (Predefined Spreadsheets) Options, General (Allows you to personalise your work environment with the mini toolbar, colour schemes, default options for new workbooks, customise sort and fill sequences user name and allow you to access the Live Preview feature. The Live Preview features allows you to preview the results of applying design and formatting changes as you move the mouse) Formulas (allows you to modify calculation options, working with formulas, error checking. Proofing (Allows you to personalise how excel corrects and formats your text. Your can customise auto correction settings and have excel ignore certain words or errors in a document through the customer dictionaries, Sort(Allows you to sort your information into an order of your choice.) Advanced (allows you to specifiy options for editing, copying, pasting, printing, displaying, formulas, calculations & other general settings) Customize (allows you to add features to the Ribbon or Quick Access Toolbar. If there are tools that you are utilising frequently you can add these to the Quick Access Toolbar) • Open, Save, Print ect.

16. To Alter the column width: This can be down in two ways. You can either double click on the right hand side of the top cell, this will automatically change the width to the longest word. Or you can place your mouse on the column heading between the two cells, your mouse will change to a + sign and then you drag to the required width. To Insert a column: Select the Column on the right hand side of where you wish to insert your extra column, Right click, Insert To Alter the height of a row: As above with the exception of placing your mouse between the row numbers. To insert a row: Select the row below where you wish to insert your extra row, Right Click, Insert

17. Saving your worksheet As soon as you enter information to your worksheet your should save this to a folder and continually save your information as you are working. Doing this can save you a lot of heartache, if the computer crashes, power runs out or any other unforseen events. You would also save this before you print or close the file. To Save A Workbook for the first time: From the File tab – select File Save As- The select the location that you want to save it to, normally Computer, Drive, Folder, To Save A Workbook that has already been created and you have either edited or added information to it - you may either click the Save button or Control S. It is a good idea to set your computer to Auto Save – which will automatically save your work every 5 – 10 minutes. But to be on the safe side To Close your Workbook – Always save first (better safe than sorry) and then X top right hand corner. To Name your Worksheet – Double click on the Sheet number at the bottom of the sheet, this will then be highlighted and then type the name specific to the worksheet.

18. FORMATTING YOUR WORKSHEET • There are many formatting options to make your worksheet appealing to your audience and to assist you with your set up. • To format numbers – click on the numbers tab • To format alignment – click on the alignment tab • To format font – click on the font tab • To format a title heading across a worksheet – Highlight the cells where you wish to place your heading on the worksheet, then select Merge & Centre in the Alignment Tab. This will merge all of your cells and you can from here alter the font, size and colour if you wish. • Note: you are only able to merge one row at a time. Trying to merge more will result in loss of dat

19. TO SELECT DATA • Select a Cell – Click on the cell • Select a Range of Cells - Click and drag over the cells OR Click in the first cell, hold down the shift key and click in the last cell • Select Non-Adjacent Cells - Select the first cell or range of cells, hold down the Ctrl key and select the next cell or range of cells • Select the Whole Row - Click on the row number • Select the Whole Column – Click on the column letter TO MOVE OR COPY DATA

20. INSERTING DATE AND TIME • INSERT AUTOMATIC DATE OR TIME: • The current date or time can be inserted automatically into a cell or spreadsheet. The date and time used are taken from the computer’s system clock. • To insert current date – Select a cell and press CTRL+; • To insert current time – Select a cell and press CTRL+SHIFT+; • To insert current date and time – Select a cell and press CTRL+;then SPACE then CTRL+SHIFT+; • To insert the date or time that is automatically updated – Select a cell and enter =today( ) for the date and =now( ) for the time. A space must be inserted between the brackets. The today and now functions only when the worksheet is calculated. For example (F9 or the function key is pressed) or saved and re-opened. They are not updated continuously. TO SORT DATA Why would you sort data? Since spreadsheets are used for many tasks such as expenditures, grades, data information, stock records. Being able to sort the data enables the user to define their search/information. There are two ways to sort in Excel, using the Sort buttons on the standard toolbar or Using Data menu, Sort It is always a good idea to ensure you have headings for your columns and to save the spreadsheet before you sort your data. The method that you use will depend on the layout of your data and how many columns that you would like to sort by.

24. TYPING IN YOUR FORMULA Formulae can be created using the type in method or automatically using function tools. When the type in method is used = (equal sign) must always be entered into the cell first where the calculation will appear. This indicates that a formula will be created rather than regular text or any other numerical entry. ( ) Brackets Exponentiation indicated by ^ Addition indicated by + Subtraction indicated by – Multiplication indicated by * Division indicated by / Your cell may look like this =Sum(C2+C3) TO TYPE IN A FORMULA: Select the cell you require for the result Enter the = (equal) sign Type in your instruction. For example Sum, Minimum, Maximum, Average Enter left bracket ( Type in the first number (cell reference or click on the cell) Type in the arithmetic operator (+ - * /) Type in the next number (cell reference) or click on the cell. Press the enter key.

25. AUTO SUM CTRL ~ TO VIEW FORMULAS

28. TESTING FORMULAE

37. UTILISING SHORTCUTS • CTRL + A TO SELECT ALL • CTRL + B TO MAKE SELECTED CELLS BOLD • CTRL + C TO COPY SELECTED CELLS • CTRL + F TO ACTIVATE THE FIND FUNCTION • CTRL + H TO REPLACE TEXT • CTRL + I TO MAKE SELECTED CELLS ITALIC • CTRL + N TO CREATE A NEW DOCUMENT • CTRL + O TO OPEN A DOCUMENT • CTRL + P TO PRINT A DOCUMENT • CTRL + S TO SAVE A DOCUMENT • CTRL + U TOGGLES THE UNDERLINE FEATURE ON/OFF • CTRL + V TO PASTE SELECTED CELLS • CTRL + W CLOSES A DOCUMENT • CTRL + X TO CUT SELECTED CELLS • CTRL + Y REDO THE UNDO AND/OR REPEAT ANY PREVIOUS STEP • CTRL + Z UNDO THE LAST STEP • WHEN YOU PRESS THE ALT KEY IT WILL TURN ON A SPECIAL SHORTCUT FOR THE TAB LEVEL (HOME, INSERT, PAGE LAYOUT ECT) • CTRL + ~ TO VIEW YOUR FORMULAE

39. SELECTING YOUR CHART Selecting the right type of chart and design can make your presentation visually appealing and easy to view the data and recognise patterns and trends. You are able to see at a glance whether sales are falling or rising over quarterly periods or how the actual sales compare to the projected sales. Excel offers a wide variety of chart types and simple methods for selecting and previewing them. Different charts can deliver different messages about the same data. Selecting the most effective chart type will make your data clearer, stronger, and more informative.. Each type of chart can emphasise different aspects of data. Choose a chart that is appropriate for your audience and purpose. As you think about what information to present also think about who your audience is and what is the purpose of the spreadsheet/chart. A chart should help the audience understand or remember information. As the audience wants to understand your chart quickly, you should include only those data and design elements that help convey and emphasise your primary point.. A graphic should be simple and uncluttered. Your audience will understand your data more easily if it is presented in small chunks rather than all at once.

40. CHART TYPES COLUMN - A column chart has vertical columns and is useful for comparing separate types of data. PIE - A pie chart shows data in segments. It is useful for showing data as a percentage of a whole. Pie charts are commonly used to show which products are selling or how much market share an organisation enjoys. AREA - Area charts are used to display changes over time, for example they may be used to show and compare changes to sales over a given time. They highlight total values across a time line. BAR - A bar chart displays information as a series of horizontal bars. They are useful for comparing data arranged in columns or rows. Bar charts show the differences between individual items. LINE - A line chart shows the movement of data values using single or multiple lines. It is useful for showing how the values of a data series can change over a period of time. SCATTER - A scatter chart is used to compare and arrange data to analyse the relationship of information. A scatter chart displays clusters, and is commonly used for statistical comparisons. DOUGHNUT – Like a pie chart, a doughnut chart shos the relationship of parts to a whole. However, it can contain more than one data series. Displays data in rings. CYLINDER/CONE/ PYRAMID - These chart types use cylinder, cone, or pyramid data markers to lend a dramatic effect to column, bar and 3D column charts SURFACE - A surface chart is useful when you want to find optimum combinations between two sets of data. As in a topographic map, colours and patterns indicate areas that are in the same range of values. STOCK - This chart type is most often used for stock price data, but can also be used for scientific data, (i.e. to indicate temperature change). You must organize your data in the correct order to create stock charts. BUBBLE - A bubble chart is a type of xy (scatter) chart. It compares sets of three values and can be displayed with a 3D visual effect. The size of the bubble or data marker indicates the value of a third variable. RADAR - A radar chart compares the aggregate values of a number of data series. Lines connect all data

41. COLUMN BAR PIE DOUGHNUT SCATTER LINE PYRAMID/CONE/CYLINDER AREA BUBBLE RADAR SURFACE STOCK

42. Using the following information decide what type of chart would be best and why

44. CREATE A SIMPLE CHART

45. MODIFY A SIMPLE CHART

46. MODIFY A SIMPLE CHART

47. MODIFY A SIMPLE CHART DESIGN TOOLS: Design tools allow you to change the type of chart selected, adjust the style and layout, alternate the view of data between row or column, check or adjust the data select. LAYOUT TOOLS; Layout tools allow you to add to and adjust the appearance of the chart by adding titles, labels, gridlines and so forth FORMAT TOOLS; Format tools allow you to alter the general appearance of the chart such as colours, font type and font size

48. FINALISING SPREADSHEETS