CS110 -
Introduction to
Computers and Applications
SPRING 2008 - Spreadsheet Assignment
Preparation
- Read Part 3, chapters 9-16 of Office
for
MacOS-X 2004 (Schwartz) or the Excel chapter of the
applications text that corresponds to your software before you begin
this assignment.
- Hint: Reading the Schwartz text and practicing sample
worksheet documents before you begin the assignment will help you
complete your work in a more productive, efficient manner.
- Remember to save your work often and know
how to retrieve the most recent version of the file saved to Briefcase
or your USB. Do not save junk files -- if you are dissatisfied
with the
document currently in RAM, do not save this file to your diskette;
instead, close the document currently in RAM and reload the older
version of the file.
- Review the Worksheet
Features and Functions
reference page. While you do not have to demonstrate
knowledge of all the features listed on the page, use it as a guide for
basic worksheet construction, editing, and formatting information and
to expand your knowledge of advanced features of Excel.
Excel
Worksheet / Workbook Assignment
Excel is comprised of Worksheets inside a Workbook. For this assignment,
you must save all worksheets and
charts you
create inside a Workbook and submit
the Workbook to Sakai. Do not submit individual worksheets
or charts to Sakai. If you submit individual
worksheets or charts to Sakai, your
assignment will be incomplete, and you will lose points.
For this assignment, create an Excel workbook containing five
worksheets. The worksheets will include a list of sources for
purchasing the type of music you enjoy, a
sorted version of the worksheet,
two graphs, and a version of
the worksheet that displays formulas. To
gather current data for the worksheet, browse the websites you listed
in the webpage assignment, visit new www sites, or use fictitious
data. Remember to follow the instructions on the type and
range of values to enter into the worksheet.
The
worksheets and charts included in the Excel Workbook will be submitted
as
one document to Sakai. The main worksheet will include
the
names of eight sources, real or fictitious, where you can purchase
music and for each display cost, tax,
shipping, discount, and payment options.
- Construct an Excel worksheet that
includes the following:
- Descriptive Title Section: At the
top of the worksheet enter the name of the worksheet, current date,
your name, and
any other information needed to describe the purpose of the
worksheet.
Leave five blank rows after the title section before entering column
headings.
- Column Titles - Beginning in Column
A, enter the following titles: Source, Number Ordered, Unit
Price, Purchase Amount, Shipping, Discount Rate, Discount Amount, Tax,
Frequent Buyer Points, and Total Cost
Use the Wrap Text
option in the Alignment folder of
cell formatting to improve the appearance of long titles.
- Row Titles - In Column A enter the
names of eight sources for purchasing the music (real or fictitious).
- Data
- Enter values, as indicated:
Column
B (Number
Ordered): Enter numbers between 1 and 8, representing the
number of items you purchased from each source.
Column C (Unit
Price): Enter the price of the items purchased at each source
(assume one price for each source).
Column E (Shipping):
Enter amounts for shipping or zero if there is no shipping cost.
- Enter and copy formulas to display
the following:
- Purchase
Amount (Column D): Equal to Unit Price times
Number
Ordered
- Discount
Rate (Column F): Equal to ten percent if Purchase Amount
is greater than $150; otherwise, five percent.
- Discount Amount (Column G): Equal to
Purchase Amount times Discount Rate
- Tax (Column H):
Before entering
data in this column, move the cursor several rows above the column
title (Tax). Enter the label Tax Rate in the
new cell and
the value .07 in the cell below it (see illustration below at
#5). In Column H calculate and
display the tax due to each source. Tax is equal to
Purchase Amount minus Discount Amount times Tax Rate,
with Tax Rate held
absolute in
the formula.
-
- Frequent Buyer Points
(Column I):
Equal to 10 points for every $50 purchase (Purchase Amount divided by
50 times 10).
- Total Cost (Column J): Equal to Purchase
Amount minus Discount Amount plus Shipping and Tax.
- Skip two rows after the last source
named
in Column A and enter three row titles: Column Items,
Highest, Lowest, and Average. Calculate
and copy the number of values, lowest, highest, and average values
across appropriate rows. If there are cells where no data will
appear, enter N/A
in that cell (N/A means "not
applicable").
- Insert two new rows after the last row of
source items data and enter additional values where needed in Columns
A, B, C, and E. The formulas already entered should
recalculate once the new data is entered; check to see this has occured.
Note: There should
still be two blank rows below the last row of data and the comparative
data rows. Insert blank rows if necessary.
- Two rows below the last item in Column E
(Shipping), enter the label Shipping > $25. In the
cell
below
the new label, write a formula with function to display the number of
items in
Column E that contain a value greater than 25 (do not include the
comparative data cells when constructing
this formula).
- Select options from the Alignment,
Style, Number, and Border menus to enhance the overall
appearance of the worksheet. Remember to adjust column width as needed
to incorporate the formatting options you've chosen.
- Insert a Footer in the worksheet
with your name at the left side, section number in the center, and page
number at the right side.
- Save the completed, modified worksheet in Landscape
mode to the Workbook as Sheet #1 and label the Sheet Tab Main Worksheet.
Note: Include grid lines and row and
column headings.
- Sort the worksheet in descending order by Number
Ordered as the first order key and Purchase Amount as the
second
order key.
Save the sorted worksheet in Portrait mode to the Workbook as
Sheet #2 and label the Sheet Tab Sorted.
Note: Include grid lines and row and
column headings.
- Create
Charts
- Create and Save
a Pie Chart to display the Unit Price
for each source to the Workbook as Sheet #3 and label the
Sheet Tab Pie Chart.
- Create and Save a Bar Chart that
compares Total Cost from
each source with Numbered Ordered from each source as Sheet #4
and label the Sheet Tab Column Chart.
Hint: You may have to copy and paste the columns
containing the data next to each other in another part of the worksheet
before setting up the Bar Chart.
- Save a duplicate
copy of the Main Worksheet
(Sheet Tab #1) to Sheet Tab #5 and label the Sheet Tab Formulas.
Using the duplicate copy of the worksheet (Sheet Tab #5),
select the Formulas display option, adjust the width
of the columns as needed, and add a Footer
with your name, section number, and date to the worksheet (Excel does
not duplicate headers and footers).
Note: Most formatting options are
not displayed when the
worksheet is in formula mode.
Include grid lines and row and
column headings.
Reminders
- Print the Worksheet Checklist and
evaluate your printouts before submitting the Workbook to Sakai.
- Review the Worksheet
Features and Functions
page to evaluate your understanding of worksheet construction, editing,
and formatting.
- You must
save all worksheets and charts inside a Workbook and submit the
Workbook
to Sakai. If you submit individual worksheets or charts to
Sakai, your
assignment will be incomplete, and you will lose points.
Return
to: