Thomas
H. Brennan
Working Group Number: 9
Subject:
Computer Science
Level: Undergraduate
Course: CIS200  Software Topics
Lesson: Music Store Analysis Commencement Content Standards
Standard
3: Mathematics
Students will understand mathematics and become mathematically confident
by communicating and reasoning mathematically, by applying mathematics
in realworld settings, and by solving problems through the integrated
study of number systems, geometry, algebra, data analysis,
probability and trigonometry.
Standard
5: Technology
Students will apply technological knowledge and skills to design,
construct, use, and evaluate products and systems to
satisfy human and environmental needs.
Benchmark
Standards Content
Mathematics: Mathematical Reasoning
 Students
use mathematical reasoning to analyze mathematical situations, make
conjectures, gather evidence, and construct an argument.
Technology:
Engineering Design
 Engineering
design is an iterative process involving modeling and optimization
used to develop technological solutions to problems with given constraints.
Performance
Mathematics:
Mathematical Reasoning
 Students
will use dimensional analysis techniques.
Technology:
Engineering Design
 Students
will, in a group setting, test their solution against design specifications,
present and evaluate results, describe how the solution might have
been modified for different or better results, and discuss tradeoffs
that might have to be made.
Content
Standards
Mathematics
 Students
will be able to understand the multidimensional interfaces of an
electronic spreadsheet.
 The Cartesian
coordinate system will be represented by a grid of columns and rows,
and a three dimensional system will be represented by a Cartesian
grid in combination with different sheets within a spreadsheet file.
 Students
will understand the differences between data and formulas in a spreadsheet.
 Students
will differentiate among different types of numerical data.
 Students
will be able to construct appropriate arithmetic formulas.
 Students
will be able to select appropriate mathematical and statistical formulas.
 Students
will be able to answer correctly specific questions based on calculations.
Technology
 Students
will design the appropriate solution to a problem which contains detailed
specifications. The solution will incorporate the use of an electronic
spreadsheet and will utilize the multidimensional interfaces of the
software.
Performance
Measures
Mathematics
 Students
will create a new spreadsheet file, name it, and save it on appropriate
secondary storage. The spreadsheet design will be modeled according
to a Cartesian coordinate systems and will contain appropriate text
(labels), data, and formulas for a given set of specifications.
 Students
will assign appropriate names to different sheets in the spreadsheet
file and will be modeled according to a three dimensional coordinate
system.
 Students
will insert several different types of data, including dummy data,
test data and real data.
 Students
will select mathematical and statistical formulas to calculate sums,
averages, minimums, maximums, and tallies of different sets of data.
 Students
will construct arithmetic formulas to calculate percentages.
Technology
 Students
will submit a written report of approximately 250 words describing
how the spreadsheet was used to solve a particular problem. This report
will list the results of the spreadsheet calculations for the problem
specifications and describe changes to the spreadsheet model for new
or modified specifications.
Rubrics
 The completed
spreadsheet will be evaluated using an appropriate set of test data.
All spreadsheets calculations will be checked for correct results.
Grades will not be determined for each individual item but rather
by a portfolio of assignments given in the course.
The
course portfolio will be judged as follows:
Assignments
will have due dates. Assignments will be evaluated in one of three ways:
accepted without reservations, accepted with reservations, and not accepted.
Students may resubmit assignments within a specific period of time to
have them accepted without reservations.
Course
letter grades will be assigned on a schedule of the percentage of completed
assignments submitted by the due date and a percentage of assignments
completed with accepted evaluations. Attendance at lecture classes and
labs will also be included in student evaluations.
Enabling
Activities Class sessions are 75 minutes in length. Class
#1: Classroom Lecture
 Review
and discuss spreadsheet terminology.
 Explain
the mathematical nature of the spreadsheet interface including grid
and sheet concepts.
 Make associations
between spreadsheet interface design and the mathematical concepts
of multidimensional coordinate systems.
 Define
the three different types of mathematical data: dummy, test, and real.
 Explain
how spreadsheets can incorporate dummy data from edit and fill menus.
 Differentiate
between the meanings of percentage: percentages as data values (7%
tax) and percentages as the results of calculations (calculate the
percent of 2 parts out of 7).
Class
#2 and #3: Computer Lab
Design
and implement a spreadsheet solution for the following set of specifications.
The Music Company Spreadsheet.
A
music company has three outlet stores. Each store sells music in three
different formats: classical, pop, and country. Each of the formats can
be purchased in three different media: record, compact disk and cassette.
The price for a record is $8.95, a compact disk is $14.95 and the price
for a cassette is $11.95.
1.
Construct a twodimension spreadsheet design which includes the following:
 the number
of items of each format in each media,
 the total
number of items of each format in each media,
 the average
number of items of each format in each media,
 the minimum
number of items of each format in each media,
 the maximum
number of items of each format in each media,
 the percentage
of items of each format in each media,
 the dollar
amount of sales of each format in each media,
 the percentage
of the dollar amount of sales of each format in each media
2.
Construct a threedimensional spreadsheet design which inserts part 1
in a different sheet for each store. Each store will have three sheets.
One sheet will be for dummy data, one sheet for test data, and one sheet
for real data.
3.
Using a word processor write a report about the design and how it was
implemented. In the report address the issues of the following modified
specifications.
 How would
the design be modified if there were another type of media to be added
for each type of music?
 How would
the design be modified if there were another type of music to be added
for each type of media?
 How would
the design be modified if both items immediately above were implemented.
 At the
end of the report answer the following questions from the real data
supplied by your instructor:
For each store:
 What type
of music has the most sales?
 What media
of music has the least sales?
 What is
the total number of classical items sold?
 What is
the average number of cassettes sold?
 What percent
of all sales is pop on cassette?
 What type
of music has the greatest dollar amount of sales?
 What media
of music has the least percentage dollar amount of sales?
 If the
price of a compact disk was lowered to $10.95 what effect would this
have on the percentages of compact disks sold?
 If the
price of a cassette was raised to $12.95 what effect would this have
on the percentage of the dollar amount of sales of classical music?
 Based
on the lowest percentage of dollar amount of sales what type of music
in which type of media should be eliminated
4.
How would the design be modified if there were different prices of records,
compact disks, and cassettes for each type of music?
Class #4: Classroom Presentations
Students
will present their respective reports.
Class
#5: Computer Lab
Students
will implement the four modifications in the specification as noted above.
Students
will submit a disk with the spreadsheet files and a printout of the original
and modified spreadsheets by a due date. Note: A sample of a portion of
the spreadsheet is attached.
