EXCEL tutorial for business analyst
EXCEL tutorial for business analyst
...
How Can You Use Excel to Make Decisions in the Workplace?
For many businesses, Excel is used to analyze and interpret data. Based on this analysis, important decisions can be made. For example, the banking industry requires employees at every level to analyze data and to make decisions accordingly. Employees may use Excel to decide whether to take on a new customer, accept a loan application, or invest in a particular stock.
Using Excel At Work
Every bank employee will probably use Excel at some point in his or her career. A teller may use Excel to track customer transactions. Supervisors can use Excel to create internal budgets and to track payroll. Loan offi cers and fi nancial analysts will use Excel’s advanced tools to calculate loan rates and to evaluate potential fi nancial investments. The more familiar an employee is with Excel’s advanced data analysis tools, the more complicated his or her responsibilities and decisions are likely to be.
Evaluate Choose one of the banking careers in the chart above. List three ways that you think Excel skills might help you prepare for that occupation.
Calculate Determine the annual full-time salary for a new account clerk. Assume that the pay is based on a 40-hour work week for 52 weeks per year.
Explain Why do you think a fi nancial analyst makes more than a teller?
LESSON 1 Advanced Data Organization
An Excel worksheet is laid out like a huge grid with 16,384 columns and 1,048,576 rows. This makes it an ideal place to store the huge amount of data that businesses require today. Knowing how to organize this data is just as important as gathering the information. In this lesson, you will learn how to use Excel’s advanced tools and features to help manage data. You will also learn to group and outline data, name a cell range, and use a name in a formula.
1st century skills
Learn by Doing If you want to learn to dance, you can read or listen to instructions, but unless you try doing it yourself, you will never really know how. The same is true about learning Excel. Watching your teacher demonstrate new concepts or reading about them will give you a general idea of how to use Excel. However, the best way to understand the program thoroughly is to use the features to check your work. You may make mistakes, but with practice you can perfect your skills! Name a skill that you recently learned well by practicing.
Before You Read
Prior Knowledge The more you know about a subject, or can put it in context, the more you understand. Look over the Key Concepts at the beginning of the lesson. Then, write down what you already know about each objective and what you want to fi nd out by reading the lesson. As you read, fi nd examples for both categories.
Read To Learn
- Use list ranges to manage and organize related data.
- Explore how Excel fi lters allow you to show or hide specifi c records.
- Consider how to group your data and create subtotals per group.
- Learn how to use data validation to control how the user keys in records to help prevent errors.
Main Idea
Excel has many advanced tools and features to improve the way you manage, access, and organize data.
Vocabulary
Key Terms advanced filter criteria data validation duplicate value
Evaluate Formula name
Name Manager subset subtotal
Academic Vocabulary
These words appear in your reading and on your tests. Make sure you know their meanings. Convert error interpret sum
Quick Write Activity
Describe On a separate sheet of paper, describe why Excel is an ideal place to store a huge amount of data. Create a list of the different types of information that a business may need to save.
Study Skills
Review Your Notes You can improve your recall at exam time if you look over your notes the same day you take them. Make sure they are clear and add any information that you forgot to add in class.
Academic Standards
Language Arts
NCTE 5 Employ a wide range of strategies while writing to communicate effectively with different audiences.
Math
NCTM (Number and Operations) Understand numbers, ways of representing numbers, relationships among numbers, and number systems.
NCTM (Number and Operations) Understand meanings of operations and how they relate to one another.
EXERCISE 1-1
Create and Modify List Ranges
In Microsoft Excel 2010, you can use a table to manage and organize related data. You can select a range, or sequence, of cells that you want to make into a table. Since many tables have a huge amount of data in them, you can use the AutoFilter tool to find and work with a smaller amount, or subset, of data. In this exercise, you will create a table and use the AutoFilter to sort and filter information in the table. You will then convert, or change, the table back to a normal range of data.
Step-By-Step
Choose Start>All
Programs>Microsoft
Office®>Microsoft Excel 2010.
Open the data fi le
Blues.xlsx. Click Enable
Editing, if necessary. Save as: Blues-[your first initial and last name]. (For example, Blues-erusso.)
Select the Merchandise sales tab. Click cell B6.
Choose Insert>Tables> Table . In the Create
Table dialog box, select cells A1:D52. Click OK.
Your screen should look like Figure 1.1.
With cells A1:D52 still selected, click the
Concert Date drop-down arrow. Choose Sort
Newest to Oldest .
Scroll to the top of the worksheet.
Your screen should look like Figure 1.2.
Click the Location dropdown arrow. Click the
Select All check box.
Click the New York check box. Click OK.
Click the Sales drop-down arrow. Choose Number
Filters>Custom Filter.
… … …
EXERCISE 1-2
Use Advanced Filters
AutoFilter allows you to fi lter data based on simple, preset criteria, or conditions. When you use an advanced fi lter, however, you can specify more criteria, and only those items in a range of cells or table that meet the criteria will be displayed. The criteria range contains the conditions that the data must meet in order to be displayed. The range of cells contains the list of data items. There must be a blank row between the criteria range and the list range.
…
EXERCISE 1-3
Create Subtotals
A subtotal is the total, or sum, of a group of items within a larger set of items. To create subtotals in a worksheet, make sure the list is sorted so that similar items are grouped together. Excel will then automatically create the subtotals for you. In this exercise, you will fi nd the subtotal for the sales for each concert in the Blues fi le.
…
EXERCISE 1-5
Apply Data Validation Criteria
An Excel worksheet is an ideal place to store the huge amount of data that businesses require today. If the data has typos and mistakes, the fi lters and reports will not function properly. To control the type of data entered into cells, apply data validation criteria. Data validation is the process of ensuring that data is correct based on specifi c criteria. In this exercise, you will apply data validation to require the amount for each advertising method to be $2,000 or less. You will then add a drop-down list from a range of cells to limit the data that can be entered into a column.
…
EXERCISE 1-13
Use Paste Special
The Paste Special command allows you to copy and paste specifi c cell contents or attributes, such as formulas, formats, or comments, from the Clipboard in a worksheet. You can also choose to perform simple mathematical calculations based on the values of copied cell(s) and the values of target cell(s). A few examples of Paste Special options for cell contents and attributes are shown in Table 2.1. Microsoft Excel 2010’s Paste Preview feature also allows you to select from a variety of options when pasting formats, formulas, and values within the same worksheet, between workbooks, or from other programs, including keeping the source column widths, having no borders, and keeping the source formatting.
…
Making Complex Choices
At home, at school, and at work, making tough choices is a part of everyday life. For example, Sarah’s teacher is encouraging her to take an advanced math course next fall. Taking the course will mean a lot of extra homework. Sarah already has an after-school job, and she plans to try out for the school play next fall. When Sarah asks for advice, her parents say taking the math course might help her get into college. Her friends are worried that Sarah will not try out for the play. Sarah weighs the pros and cons and follows some simple problem-solving and decision-making steps she learned at school. Finally, Sarah decides to take the course and cut back her hours at work.
Meet the manager
Making complex choices is an important part of running a business. My employees often have to make difficult decisions about books, says Leo Landry, manager of Children’s Book Shop in Brookline, Massachusetts. They sometimes decide not to carry a popular book they think is low quality, even though they risk losing sales. Other times they try something else. For example, the staff once decided to stock just two copies of a best-selling book that they did not think was very well-written. “We knew people were going to ask for it. Instead of sending customers elsewhere, we gave them a chance to decide about the book here. Most of them looked at it and just handed it back.”