# Excel Spreadsheet for Sales Activity Report - DOC

Document Sample

```					C:\Docstoc\Working\pdf\863e1986-67f8-47f3-a28a-4b12f517e664.doc                   Page 1/6

Excel Chapter 2: Gaining Proficiency
Overview
Build upon your experience writing basic Excel formulas.

Besides typing formulas, you will learn how to build a formula by typing math
operating symbols and pointing and clicking on cells to be included in the formula. This
procedure is more accurate than typing the entire formula since you “touch” the involved
cells.

Enter a formula in one cell and use the fill handle as a shortcut to copy a formula across
or down to adjacent rows or columns. This speeds up data entry, cuts down on risk of
typing errors, and improves accuracy. The formulas in the destination cells will readjust
depending on their location in the worksheet but will maintain the same mathematical
relationships.

Relative cell references in formulas permit formulas to readjust when copied or moved
to new locations. Absolute cell references in formulas permit formulas to refer to a
constant when copied or moved to new locations. For example, you might use constants
such as sales tax, discount percent, markup percent, or a base salary in financial

Understanding Percent-of-Change Formulas is important because
1. they require the use of the math order of operations to obtain proper results.
2. news stories appear almost weekly showing percent of change in corporate sales,
housing starts, population, births, deaths, etc.

When you save an Excel file, the standard format ends with the extension .xls. You can
also save an Excel file in other formats if you need to use another spreadsheet program
such as Lotus or Quattro Pro, or you can save it as a web page.

Pretest for Excel
The Excel pretest is described in the first Excel module, Excel Chapter 1: Introduction to
Excel.

Objectives
By the end of this Excel module students will be able to
 write a formula or function using cell address and no raw numbers.
 create an assumptions area for raw numbers so no raw number appear in formulas.
 use relative and absolute references effectively.
 use the fill handle to copy cells to adjacent area.
 use the math order of operations to write percent-of-change formulas.
 save Excel files in a different file format.

2/3/2011 3:19 PM
C:\Docstoc\Working\pdf\863e1986-67f8-47f3-a28a-4b12f517e664.doc                 Page 2/6

In your textbook, Exploring Microsoft Office, Volume I, Grauer and Barber, you will
work in the chapter entitled Gaining Proficiency, identified as MXL.

MXL 65-68.
This reading reinforces writing formulas and focuses on using relative and absolute cell
references, when to use them and how to write them.

Read definitions for this list of three terms in the glossary of your text:
 Cell References
o Example: E1 (cell located at the intersection of Column E and Row 1)
 Relative Cell References
o Example: =E1-E2
 Absolute Cell Reference
o Example: =\$E\$1-\$E\$2

Use no raw numbers in formulas. See Assumptions.xls for an example of a spreadsheet
written with raw numbers in an assumptions area and both relative and absolute cell
references in formulas.
The Assumptions.xls scenario has you shopping at XYZ outlet store.
Today the store has a ten-percent discount on all items purchased. Of
course, you are required to pay six percent (6%) Indiana sales tax. If
tomorrow’s sale offers a twenty-percent discount ,you only need to
change the discount percentage in the assumption cell. The results
update automatically in other cells. Perform “what-if” analysis by
changing raw numbers in assumption cells.

View
After you work through the demonstrations listed here, you will create or edit a
Topic and Description                                       Location of Tutorial
Use Cell References in Formulas          Use the Train IT Office CD. Click on the links in the
Formula results update automatically if  order listed to get to the demonstration.
you use cell references in formulas          My Modules
instead of raw numbers.                      Exploring Series Office XP
 Exploring Excel 2002
 Exploring Excel 2002—Chapter 2
 Use Cell References in Formulas
Copy and Edit Formulas                   Use the Train IT Office CD. Click on the links in the
Save yourself time and increase          order listed to get to the demonstration.
spreadsheet accuracy by copying              My Modules
formulas to new locations.                   Exploring Series Office XP
 Exploring Excel 2002
 Exploring Excel 2002—Chapter 2
 Copy and Edit Formulas
Relative Versus Absolute Cell           Click on the link to AbsoluteCellRefs (RLO video) for

2/3/2011 3:19 PM
C:\Docstoc\Working\pdf\863e1986-67f8-47f3-a28a-4b12f517e664.doc                   Page 3/6

References                                 using and creating absolute cell references and the
Use absolute cell references when a        impact if you leave all cell references relative.
formula must refer to a constant such as
sales tax, discount percent, or markup
percent.

Use the F4 function key on the keyboard
or type dollar signs (\$) left of the
column letter and row number in a cell.

2/3/2011 3:19 PM
C:\Docstoc\Working\pdf\863e1986-67f8-47f3-a28a-4b12f517e664.doc                    Page 4/6

Do
MXL Pages 69-75. My only objection to the formula in these pages is the use of the raw
number 1.5 as the overtime factor. It would have been better to put the overtime factor in
the assumptions area. If and when the company decided to increase the overtime factor to
2, the spreadsheet results would update automatically by simply typing a 2 in an
assumptions cell.

When you work in this spreadsheet, add Overtime Factor to Cell A13 and the value 1.5 to
C13.

MXL Page 79.

Your book gives brief reference to writing percent-of-change formulas. Such formulas
can show percent of increase, decrease, loss, difference, or change.

Understanding how to write these formulas in Excel is important for two reasons:
 Almost weekly, the newspaper has a story with a percent-of-change spreadsheet
or chart based on population, government expenditures, births, deaths, new home
starts or other statistical information.
 To write an accurate percent-of-change formula, you must use the math order of
operations.

Some find it confusing to build percent-of-change formulas. Try this structure to design
your formulas =(new-old)/old. New in the formula represents the most recent data. Old
in the formula represents data furthest away in time. Go to PercentofChange.xls. The
column labeled Mar 2003 presents the new data. The column labeled Dec 2002
represents the old data.

Do
MXL Page 79.

MXL Pages 76-77.

The Internet has become an integral part of the computer world. You can save your
spreadsheets as web pages to display on a web server.

Because this is an introductory course, we will limit web-related activity to saving a file
in web or html format and inserting a hyperlink to a web site in an Excel spreadsheet.
Cell B21 in Assumptions.xls has a hyperlink to a web tutorial about absolute cell
references.

2/3/2011 3:19 PM
C:\Docstoc\Working\pdf\863e1986-67f8-47f3-a28a-4b12f517e664.doc                Page 5/6

View
After you work through the demonstrations listed here, you will create or edit a
Topic and Description                                               Location of Tutorial
Use Save As                                             Use the Train IT Office CD. Click on the
To be able to change the file name and choose the       links in the order listed to get to the
drive and folder where a file is to be stored, use the  demonstration.
File Save As command. There is not a toolbar button         My Modules
for File Save As.                                           Exploring Series Office XP
 Exploring Excel 2002
 Exploring Excel 2002—Chapter 2
Understand Hyperlinks                                   Use the Train IT Office CD. Click on the
Hyperlinks can connect to another data file or to an    links in the order listed to get to the
Internet site. This tutorial gives an overview of both  demonstration.
options.                                                    My Modules
 Exploring Series Office XP
 Exploring Excel 2002
 Exploring Excel 2002—Chapter 2
Create and Edit Hyperlinks                              Use the Train IT Office CD. Click on the
This tutorial shows you how to create a hyperlink to    links in the order listed to get to the
another file or an Internet site. You will also see how demonstration.
to edit a file.                                             My Modules
 Exploring Series Office XP
 Exploring Excel 2002
 Exploring Excel 2002—Chapter 2
 Create and Edit Hyperlinks
Save Worksheet as a Web Page                            Use the Train IT Office CD. Click on the
Save Workbook as a Web Page                             links in the order listed to get to the
Use the File Save As Web Page command to save an        demonstration.
Excel spreadsheet in HTML format, the protocol              My Modules
required for web pages. You can save either the             Exploring Series Office XP
entire workbook or one worksheet as a web page.             Exploring Excel 2002
 Exploring Excel 2002—Chapter 2
 Save Worksheet as a Web Page
 Save Workbook as a Web Page

Do
MXL Pages 28-83; Omit Page 81.

2/3/2011 3:19 PM
C:\Docstoc\Working\pdf\863e1986-67f8-47f3-a28a-4b12f517e664.doc                 Page 6/6

Summary
See if you have mastered the objectives for this Excel lesson. Once you are convinced
you have mastered the objectives, go on to the Assignments section.
 write a formula or function using cell address and no raw numbers.
 create an assumptions area for raw numbers so no raw numbers appear in
formulas.
 use relative and absolute references effectively.
 use the fill handle to copy cells to adjacent area.
 use the math order of operations to write percent-of-change formulas.
 Save Excel files in a different file format.

Assignments
1. Go to Oncourse to the Test and Survey Tool. Do the Excel Chapter 2 Homework.
Your answers will be automatically submitted and scored.
2. Work through the following files, save them and submit them individually to the
Oncourse drop box named Excel Chapter 2 Assignments.
a. MXL P. 98, #2, The Sports Statistician
b. MXL P. 100, #3, Web Pages and Hyperlinks
c. MXL P. 100, #4, Wishful Thinking CD
d. http://portfolio.iu.edu/janstev/Relabs.xls. Do Relative vs. Absolute and
Midterm1 sheets. There are answer sheets to check your work.
e. http://portfolio.iu.edu/janstev/Passengers.xls.
f. http://portfolio.iu.edu/janstev/Pepsico97.xls . Do only the Annual Report
sheet. There are answer sheets to check your work.

Excel Project
The Excel project is posted under the Project link on the Course Home Page.

Excel Post Test
The Excel post test will be given after Excel Lesson 4.

2/3/2011 3:19 PM

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 301 posted: 2/3/2011 language: English pages: 6
Description: Excel Spreadsheet for Sales Activity Report document sample