Develop and Use Complex Spreadsheets - PDF

INFOLINK UNIVERSITY COLLEGE C o u r s e Ti t l e : - D e v e l o p a n d u s e c om pl ex s p r e a d s h e e t s...

INFOLINK UNIVERSITY COLLEGE C o u r s e Ti t l e : - D e v e l o p a n d u s e c om pl ex s p r e a d s h e e t s Department:-Accounting P r o g ra m : - T V E T Session:-Regular P r e p a r e d b y : - Wo n d i m a g e g n. D Copyright – Precision Learning  P R E S E N TAT I O N OBJEC TIVES At the end of this presentation you will know about: Organise personal work environment in accordance with ergonomic requirements Analyse task and determine specifications for spreadsheets Identify organisational and task requirements of data entry, storage, output, reporting and presentation requirements Apply work organisation strategies and energy and resource conservation techniques to plan work activities  P R E PA R E TO D E V E L O P S P R E A D S H E E T S Spreadsheets help u s arrange data, sort data, with graphing or charting data and also calculating numerical data. The rows (horizontal) are identified by numbers and columns (vertical are identified by letters (e.g. A, B, C, AA, A B etc.) Microsoft Excel is a widely used spreadsheet program. There are others: Google Doc s - an online and collaborative spreadsheet. iWork Numbers – Apple Office Suite. Lotus Sy mphony – Spreadsheets. Copyright – Precision Learning  ORGANISE PERSONAL WORK ENVIRONMENT WITH ERGONOMIC REQUIREMENTS Ergonomic requirements Avoid radiation Chair -height, Keyboard, screen from computer seat, back Document holder Footrest and mouse screens adjustment position Lighting - glare Noise Posture Workstation and reflection m inim is ation height and layout Your own work area should be monitored by you a s part of your daily or periodic self-check of equipment, workstation and area before work and during work. Organisations should encourage safe working practices. Copyright – Precision Learning  ORGANISE PERSONAL WORK ENVIRONMENT W I T H E R G O N O M I C R E Q U I R E M E N T S (CONT.) R e s p o n s i b i l it i e s include: hazard reporting procedures job procedures safe work instructions emergency procedures accident and near miss reporting and recording procedures consultation on W H S issues correct selection control of risks under direct supervision. E x a m p l e s of w o r kp l a c e policy re q u i re me n t s : Maintain work areas in a safe, uncluttered and organised manner according to policy and procedures. Carry out all procedures safely, effectively and efficiently with minimum inconvenience to staff, according to policy. Apply policy and procedures for tidying work areas and placing items in designated areas. Copyright – Precision Learning  ORGANISE PERSONAL WOR K ENVIRONMENT WITH E R G O N O M I C R E Q U I R E M E N T S (CONT.) W o r k surf a ce C h a ir Equipment Place all controls and task materials Adjust your chair so the work Set the eye to screen distance at a close to avoid twisting surface allows your elbows to be distance that permits you to most Use a document holder to minimise bent at 9 0 , forearm s parallel with the easily focus on the screen. Usually head movement floor, wrist straight, shoulders relaxed within arm's length Place the keyboard in a position that Adjust the backrest to support your Set monitor height with top of the allows the forearms to be close to the lower back when sitting upright screen below eye level and the horizontal and your wrists straight - Adjust the seat tilt so that you are bottom easily read without a marked your hand in line with your forearm comfortable when working on the inclination of the head If elbows are too far out from the side keyboard Usually the centre of the screen is of the body re-check the work surface Usually this will be close to horizontal, near shoulder height or chair height or tilted slightly forwards People with glasses need to avoid too S o me people prefer to have their Your knees should be bent at a much neck flex wrists supported on a wrist desk or the comfortable angle greater than 9 0 A mo use should not cause undue desk. Be careful not to have the wrist degrees. pressure on the wrist and forearm extended or bent in an up position If is not comfortable or if your feet do muscles not reach the floor use a footrest Pressure can be reduced by The footrest should be adjustable releasing the mouse at frequently and by selecting a slim-line, low- profile mouse. Keep the mo use as close as possible to the keyboard, elbow bent and close to the body Avoid cradling the phone between your head and shoulder Use a headset -hands-free/speaker Copyright – Precision Learning phone is another option if environment is suitable  ORGANISE PERSONAL WO R K ENVIRONMENT WITH E R G O N O M I C R E Q U I R E M E N T S (CONT.) Posture and environment Li g ht i n g K e y b o a r d op e r a t i on s Change posture often to minimise Place monitors to the side of the light Typing, a physical activity that fatigue source(s), not underneath requires skill Good posture is essential- natural make sure the correcxt lighting is Staff need to learn correct techniques relaxed position, with opportunity for being used in your office. Unskilled typists are at risk of an operators to a s s u me alternate positions Glare and reflection - to determine glare overuse injury due to: Avoid awkward postures at extremes of from overhead lights whilst seated, hold Using one or two fingers which may the joint range, especially wrists a book above your eyes at eyebrow level. overload finger tendons Frequent short rest breaks rather than Is the screen image clearer withour Constantly looking from keyboard to infrequent longer ones overhead glare screen and back, ma y strain neck Avoid sharp increases in work rate If there are reflections from the desk muscles Changes should be gradual to ensure surface, hold the book above the Can adopt a tense posture, with wrists surface and a s s e s s changes in reflected bent back and fingers tensed work does not cause fatigue glare Efficiency and speed of computers Fixed posture for long periods is tiring and increases likelihood of muscular aches To reduce glare and reflection: makes it possible for skilled operators to and pains Tilt the screen (top forward) so type extremely quickly.This and Long periods of repetitive movement and reflections are directed below eye workload pressures means potential sustained visual attention can give rise to level exists for operators to use speeds which fatigue-related complaints Provide LCD screens ma y cause or contribute to an overuse Avoid spending more than 5 hours a Cover screen with anti-glare screen injury day on keyboard duties and no longer Negative contrast reduces reflections The role of repetitive movement in injury than 50mins/hr without a postural/ Change text and background colours is not fully understood, but is believed to stretching break BEST black characters on white/ interfere with the lubrication capacity of yellow background, or yellow on tendons, and ability of muscles to receive black, white on black, white on blue and sufficient oxygen. 10,000 - 12,000 green on white keystrokes per hour is considered acceptable Avoid red and green and yellow on white Turn screen brightness down Look into the distance to rest your eyes regularly Copyright – Precision Learning  WOR K SAFE AND SMART! S u p e r v i s o r s s h o u l d e n s u re w o r k l o a d c ontrol s are e xe rc ised u s i n g the fol l ow i ng s t r a t e g i e s : Plan ahead to avoid peaks and rushed jobs. Discourage ‘endless’ drafts. Delegate fairly to all staff. Discourage the use of typed internal minutes. Consider the total workload of the Smaller breaks are valuable, individual, schedule work fairly to avoid sometimes more so than one long one. undue pressure on an individual. Clearly define each operator’s workload Support staff and key board operators by ensure there are realistic expectations. providing skills training and strategies in how to be assertive and to prioritise. Use relief staff if required. Supporting operators when there are unrealistic expectations imposed. Apply strict tests to the use of ‘urgent’ Exercise breaks need to be regular and allow people to move from static labels. postures. Copyright – Precision Learning  A N A LY S E TA S K A N D D E T E R M I N E S P E C I F I C AT I O N S W h e n analysing your task what do yo u need to calculate, what information needs to be extracted? Is a spreadsheet the most efficient way to continue? Is a word processor or database better? If a spreadsheet is the most suitable software it needs to be designed and constructed to make sure outcomes are fully realised. A plan is essential from the start. A s k - what do I need this spreadsheet to d o ? D o I need it to: Add a set of Sort data or create Perform complex numbers? a graph? calculations? K n o w i n g t h e o u t c o m e p r o v i d e s t h e b e s t p l a c e t o start. If you understand your task our outcome - begin by thinking of structure. If you are creating a spreadsheet for someone else make sure you know what information they have to give you and what information they need to be able to extract at the end. Ask questions of the eventual user what is needed from the data? W h a t i s req u ired o f t h e t a s k ? F i n d o u t in detail. Copyright – Precision Learning  ANALYSE TASK A N D DETERM I N E SPECIFICATIONS A n a l y s e the t a s k – w h a t d o y o u n e e d y our s p r e a d s h e e t t o d o ? Spreadsheets have many uses: You can automatically Most general purpose create charts from Listing and maintaining information projects can spreadsheet data, to groups of numerically be implemented with a print or import into based data. presentation or other spreadsheet or database. applications. A spreadsheet is excellent for simple data Excel has presentation entry, use the Tab key to facilities and connect advance from cell to cell. easily to Word or Data is laid out in rows PowerPoint. and columns. Copyright – Precision Learning  DATA ENTRY, S TO R A G E , OUTPUT, R E P O R T I N G A N D P R E S E N TAT I O N There are laws and regulations governing the collection and storage of information, especially personal information of customers and individuals. Organisations must not gather personal information about their customers, clients or employees, unless necessary and relevant to their purpose. Organisations must make individuals aware of the information they possess. Individuals must be made aware of: who is storing or collecting information and contact details including third parties the information ma y be passed to what the information will be used for any laws governing the collection and storage of this information, especially medical details will the information will be communicated to overseas countries? This information is usually contained within the organisation’s privacy policy, which should be available for customers to reference. This must be in line with Privacy Laws - Privacy Act 1 9 8 8 (Cth) Anti-Discrimination legislation also applies: It is against the law to discriminate against anyone on the basis of gender, sexual preference, political opinion, trade union activities, colour, race and ethnicity, social origin, religion, nationality, family responsibility, irrelevant medical record, irrelevant criminal history, age, marital status, carer status, parental status, breastfeeding, disability, pregnancy. Copyright – Precision Learning  DATA ENTRY, S TO R A G E , OUTPUT, R E P O RT I N G A N D P R E S E N TAT I O N S p r e a d s h e e t specifications The structure of tables is determined by different kinds of cells and their relationships. The specifications m a y include decimal points, headings and format of cells. D a t a entry – navigate through spreadsheets using different options. Press TAB to move your active cell to the right by one cell, and ENTER to move your active cell down by one cell, Shift + TAB will move you back. Fill down from above using CTRL + D or click and drag on the + in the bottom right hand corner of a cell or range of cells - values are copied down. Or use Autofill option for this purpose. Copyright – Precision Learning  DATA ENTRY, S T O R A G E , OUTPUT, R E P O RT I N G A N D P R E S E N TAT I O N Output Spreadsheets allow users to perform computations and checks on data input. Determining required outputs is important. Data output is a process to study, manipulate and work with data a s required, for u se by other parties. Simple statistical analysis and parameters like mean, median, mo d e and range are available a s spreadsheet formulas. Spreadsheets are designed to record, sort, calculate and store data. M a k e s u re y o ur o r g a n i s a t i o n s style g u i d e i s b e i n g followed carefully. Style guides provide information about expected presentation standards for all reporting and documents. Policies and procedures exist within in the workplace to guide users in correct methods of presentation, saving and storing. There are times when adding graphics ma y help to communicate information more effectively, these can include charts or graphs to illustrate results. Copyright – Precision Learning  W O R K O R G A N I S AT I O N , ENERGY, R E S O U R C E C O N S E R VAT I O N W o r k o r g a n i s a t i o n s t ra t e g i e s i n v o l v e f a c t o r i n g in t h e followin g: Exercise breaks and rest periods maintain correct ergonomic requirements. Mix of repetitive and other activities – to avoid continuous postures and activities which could lead to overuse injury or inefficiencies in the workplace. Stress in the workplace and recognising when a working environment is affecting performance. Observing individuals and respecting their needs. W h o will schedule the work? Are they aware of the importance of allowing time for safe and fair work practices in this w a y ? W h o is monitoring physical resource u s e ? Are they aware of the importance of minimising the businesses environmental impact? Workplace solutions must help meet current and future needs of the Copyright – Precision Learning business.  ENERGY, R ES O U RC E CONSERVATION TECHNIQUES Energy and resource conservation: It is our responsibility. Reduce all resource use in the first place. Be part of a culture of change. E.g. paper management can include the following: Edit using print Re-use paper for rough Electronic filing rather preview prevents Double-sided paper Recycling used and drafts (observe than paper based printing inaccurate use shredded paper confidentiality documents requirements) A s part of reviewing organisation strategies work activities need to keep pace with current and future trends on environmental management practices. Consider these issues and high energy consumers, what can be done at your workplace? Air conditioning, Office Using power-save equipm ent, Hot water Copyright – Precision Learning options for Managing ventilation and cooking com puters , Lifts equipment lighting levels heating equipm ent printers  ENERGY, R E S O U R C E C O N S E R VAT I O N T E C H N I Q U E S Track re sourc e u s e Reduce resource use a s part of a culture change – learn the usage and handling practices in place. Set targets to change levels for improved outcomes. Conduct audits to understand purchase, use and disposal of resources, this will give you an indication of priorities. Can you use less? Take advantage of sustainable opportunities. E.g. recycled paper or carry ba gs made from corn starch? Gather information on resource purchases, costs and amount of recycled and non-recycled alternatives available or purchased. Communicate findings via email, internal newsletter or staff meetings. Copyright – Precision Learning  D ES I G N SOFTWARE FUNCTIONS A N D FORMULAE A n a l y s i s of data and application -does your spreadsheet work for you in terms of the output you need? A p p ro p r i a t e n e s s for the audience and the task? Doe s it represent the data accurately and in a clear manner for ease of interpretation? Avoi d b l a n k ro w a n d c o l u m n s it is better practice to widen the row or column to arrange spacing. Dra g on the space between rows or columns to widen to suit your layout and format. E m b e d d i n g cell references in for mulae a cell reference refers to the Letter then the Number, the uppermost left cell is A1. Create formula using cell references rather than numerical values for calculations is much more reliable. E x a m p l e Use cell reference ‘C1’ instead of the number. If C 1 changes the formula still works. Copyright – Precision Learning  D E S I G N S O F T WA R E F U N C T I O N S A N D F O R M U L A E H e a d e r s /footers allow us to identify elements of spreadsheets and can contain:. Your organisation will specify contents for headers or footers. Eg. page numbers to keep documents manageable, and file path to locate it again! L a b e l s are the ‘names’ you give your columns or rows. They must make sense and accurately describe your spreadsheet to others. I m p o r t a n d e x p o r t o f d a t a – Use spreadsheets in ma n y forms, insert a s a table, a s a chart or a s a linked file into a Word documents M u l t i - p a g e d o c u m e n t s - create several sheets within one workbook. Copyright – Precision Learning  SPREADSHEET DESIGN Linked fo rm u la e use cells in another worksheet/book to link to your current spreadsheet. Eg. This spreadsheet uses a cell from the ‘Expenses wages’ sheet. S o cell D 4 in ‘Expenses wages’ = B 2 6 in the ‘Sales’ Copyright – Precision Learning  LINKED SPREADSHEETS Link s p r e a d s h e e t s in a c c o r d a n c e wi t h s o f t w a r e p r o c e d u r e s Linked worksheets can be in the s a m e workbook or in different workbooks. Source worksheets provide the data. Destination worksheets receive the data. Wh e n cell values change in the source worksheets, Excel automatically updates values in the destination worksheet the next time it is opened. W h y link w o r k s h e e t d a t a ? The ability to link data between Excel worksheets and workbooks can often eliminate the need to have identical data entered and updated in multiple worksheets. This s a v e s time, reduces the chance for error and improves overall data integrity. Copyright – Precision Learning  SYMBOLS AND MEANINGS Symbol Operation Meaning + Plus Addition - Minus Subtraction or negative value * Asterisk Multiplication / Forward slash Division % Percent Percent ^ Caret Exponentiation = Equal sign Equals A formula in Excel always > Greater than Greater than < Less than less than >= Greater than or equal to Greater than or equal to