Science RWLO Template Title Placeholder



Use the Internet and Excel to shop effectively

Project Overview

Shopping for technical products like computers, used cars, cameras, and cell phone plans can be a daunting task. There are numerous seemingly similar competing products with different features and technical specifications sold by a vast array of vendors at varying price levels.

This RWLO project will show students how to become informed consumers by using the Internet and Excel to organize and analyze the tsunami of product information so they can make effective purchasing decisions.

In a brick and mortar shopping environment, it is often difficult and inconvenient to compare technical products because the consumer is forced to travel from store to store to gather the product and pricing information. More than likely after spending the time to make store to store comparisons, the consumer is confused by the differences in product features and prices and unable to determine the best purchase deal.

The Internet is here to help our beleaguered consumer. The Internet provides easy 24/7 access to comprehensive product specifications, pricing, technical reviews, and consumer ratings and reviews. However, you can easily be overwhelmed and swept away by the vast amount of data you find on the Internet. The wealth of data stored on the Internet is one of the biggest advantages of the Internet. However, this vast knowledgebase of seemingly useful data can also be confusing and disheartening to Internet users if they do not know how to organize and use the information. In many cases, too much information confuses rather than informs consumers.

The purpose of this project is to show students how to use Internet shopping bots, auction sites, and information portals to collect product information and then organize and analyze the collected shopping data with Microsoft Excel. Through the use of Excel worksheet formulas, functions, and charts students are able to effectively compare product data to determine the best overall product deal.

By the end of the project, students will know how to become informed consumers by blending together the power of the Internet and Excel. The techniques used in this project can also be applied to many other major purchasing decisions the students will face in the future that involve both quantitative and qualitative issues. For example, students could use these techniques to develop a college comparison value index to determine which college to attend or an apartment comparison value index to determine the best apartment to rent.

Student Learning Objectives

The project will focus on the unique data collection power of the Internet (focusing primarily on shopping bots, auction sites, and information portals) and the way this large amount of data can be harnessed and used effectively by creating personalized product value indexes in Excel. Hopefully, this type of everyday use of the Internet and Microsoft Excel will open student eyes to other opportunities to merge the power of these tools.

For this RWLO, the student will be able to:

Internet Module Objectives

• Use Internet shopping bots, auction sites, and information portal sites to collect product and vendor information.

• Utilize Internet to provide an explanation of the significance of product technical features and specifications.

• Use the Internet to obtain “before you buy” product feedback from consumer and expert ratings.

Excel Module Objectives

• Employ Microsoft Excel as a DSS (Decision Support System) to organize and analyze data collected from the Internet.

• Apply Microsoft Excel formulas, functions, charts, and other tools to automate and improve data analysis.

• Use Microsoft Excel to quantify subjective product features, such as appearance or condition, so an objective product comparison can be made.

Procedure

Time:

[pic]

This project involves two standalone modules, one covering a unique Internet tool (shopping bots), and the other covering the creation of a spreadsheet tool (Product Value Index) that will help a consumer determine the best deal. An instructor can choose to use either individual module or may choose to use both modules together.

The Internet Module primarily involves observation and discovery and will take approximately 60 minutes or one class period.

The Excel Module is more complicated and time-consuming because it involves more advanced spreadsheet modifications (functions, cell linking, data validation, range names, etc.) and will take approximately 240 minutes or four class periods.

If an instructor chooses to use both modules, it is suggested that the Internet Module be made a classroom assignment with the Excel Module assigned as an outside the classroom special project.

For more information on this project and the relationship between the modules, view the Internet Shopping RWLO Summary PowerPoint presentation.

Materials: Computers with Internet access, Internet Explorer or equivalent browser software, and Microsoft Excel or similar spreadsheet program.

Prerequisites: Since the underlying premise of this project is working with information gathered from the Internet, students must have at least a basic understanding of how to access the Internet. If the project focus is on Microsoft Excel, students should have a good understanding of Microsoft Excel basic skills (data entry, copying, moving, selecting, navigating, and formatting) since this project uses more advanced features of the program.

Implementation: This RWLO project is flexible enough so an instructor could chose the appropriate emphasis (Internet usage, Excel, or both) and the degree of difficulty. For example, completed Excel templates with completed formulas, charts, and other features are provided for instructors that want to concentrate on Internet usage. If an instructor wants to emphasize Excel, a partially completed starter workbook is provided along with student workbook completion instructions and related PowerPoint demonstration presentations. This allows this RWLO to be used by more disciplines based on the instructor’s chosen emphasis.

Products to Compare: The project will focus on purchasing technical products (i.e. computers, cell phone plans, used cars, and digital cameras) that are inherently harder to compare because of their large number of technical features and the wide disparity in prices. For example purposes, all of the sample files, instructions, and presentations assume that the product being purchased is a laptop computer.

Features Used in Project: The more important features used in this project are listed below.

Internet Features

• Discover, through Internet research, technical product features to understand the significance of specific product features in relationship to overall product value.

• Comparison shop and gather product information by using Internet shopping bots, auction sites, and information portals.

• Review and analyze product feedback from consumer and professional ratings found on the Internet to determine product quality.

Microsoft Excel Features

• Accelerate data entry and maintain the integrity of the data collection table data by using Data Validation.

• Use Range Names to simplify and improve accuracy of formulas and functions.

• Use IF and VLOOKUP functions to match personal weighted values with product features.

• Summarize separate worksheet totals with 3-D formulas.

• Create an Excel List (database).

• Use the DSUM function to extract data totals from an Excel List.

• Create charts from worksheet data.

• Create a personalized product value index from the summation of weighted values assigned to specific product features.

Steps:

Internet Shopping Bot Module

1. Ask students, individually or as a group, to list the strengths and weaknesses of shopping on the Internet. Also have students compare some of their own online and offline shopping experiences.

a. Compare their list of strengths and weaknesses to the Internet Shopping Strengths and Weaknesses table in the Supplemental Resources section of this RWLO.

b. Poll the students to determine if they believe that online shopping is better, the same, or worse than offline shopping.

2. Have students review the training files that have been created for this part of the project.

|File Name |File Type |Description |

|Shopping Bot Introduction |PowerPoint Show with Link to Video |Narrated presentation and video about Shopping Bots |

|Shopping Bot Instructions |PowerPoint Show |Presentation showing how to access and use shopping bot |

| | |features |

|How to Use Completed Workbook |Breeze Link |Narrated presentation and video explaining the use of |

| | |the Laptop Project – Completed Workbook.xls file |

|Laptop Project – completed workbook.xls |MS Excel |Predefined Product Value Index worksheets that students |

| | |can be use to compare laptop computers |

3. Determine the technical product (i.e. computer, cell phone plan, used car, digital camera, etc.) that will be the subject of the comparison shopping exercise.

a. Some possible product comparisons are:

i. Laptop Computers (sample comparison is provided)

ii. Desktop Computers

iii. Digital Cameras

iv. Cell Phones and Calling Plans

v. MP3 and iPod Type Music/Video Players

vi. Used Cars

vii. New Cars

viii. Apartment Lease

ix. House Purchase

b. Assigned product should be inherently hard to compare because of existence of many competitive brands, numerous technical features, and a big variance in prices.

4. If you are using the enclosed sample Laptop Computer comparison for this project, you can skip steps 4, 5, and 6 listed below.

5. Have students (individually or in groups) make a preliminary list of the significant product technical features that will be used in the product comparison table.

6. Have students do Internet research to improve their understanding of the product and to find additional significant product features.

7. Have students discuss their research findings, disclose their sources (i.e. domain names and URLs), analyze the validity of their findings, and finalize the list of significant product technical features for the project product comparison table.

a. Compare to features listed in the Data Collection worksheet in Laptop Project example.

b. See the Desired Features – Laptop Computer table in the Supplemental Resources section of this RWLO for additional guidance.

8. Have students answer all the Internet Module questions in the Content Material section of this RWLO.

Excel Product Value Index Computation Module

9. Have students discuss the importance of the each of the product features and determine a weight value percentage for each significant technical feature. The total of all the weights must equal 100%. All significant product features, including subjective features like color or appearance, must be given a weight in order for the comparison to be complete.

10. Have students prepare a list of expected values for each product feature. For example, for a car purchase the type of transmission can be manual or automatic. For a subjective feature like used car appearance, the item list could be excellent, good, fair, or poor.

11. Have students assign numeric rating values for each expected feature value. The rating scale should be zero to ten with ten being the most desirable rating.

12. Have students create VLOOKUP tables that match the expected values with their related ratings.

13. Have students create formulas that automatically compute a weighted feature value based on data entered in the Data Collection worksheet.

14. Have students create a formula that totals all of the weighted feature values for a product. This will be called the product’s Product Value Index.

15. Have students create a formula (using the IF function with nested MAX or MIN functions) that automatically compares the Product Value Indexes and determines the product with the highest index value.

Content Material

Student Directions:

Module 1 - Internet Exercises

Goal: To learn how to use Internet tools to become a more informed consumer.

Use the provided sample Laptop Computer product comparison as a guide. (See Laptop Project – Completed Workbook.xls)

Step 1

Open and review the following files.

|File Name |File Type |Description |

|Shopping Bot Introduction |PowerPoint Show with Link to Video |Narrated presentation and video about Shopping Bots |

|Shopping Bot Instructions |PowerPoint Show |Presentation showing how to access and use shopping bot |

| | |features |

|How to Use Completed Workbook |Breeze Link |Narrated presentation and video explaining the use of |

| | |the Laptop Project – Completed Workbook.xls file |

|Laptop Project – completed workbook.xls |MS Excel |Predefined Product Value Index worksheets that students |

| | |can be use to compare laptop computers |

Step 2.

Search the Internet for a definition of a “shopping bot.”

• Review the definitions from at least two Internet web sites in order to determine the accuracy and completeness of the definitions.

• Use some of the following suggested search engines and web sites for your search:

o

o

o

o

o

o

Step 3

Review two of the following Internet shopping bot web sites.

•

•

•

•

Step 4

Use one or more shopping bots to determine the important product features for the product comparison assigned by your instructor.

Step 5

Answer the following questions, based on the knowledge you acquired from the previous activities.

|1. What is the purpose of a shopping bot? |

|Your Answer | |

| | |

| | |

| | |

|2. How does a shopping bot gather its information? |

|Your Answer | |

| | |

| | |

|3. How does a shopping bot differ from an auction site? |

|Your Answer | |

| | |

| | |

|4. Is the information displayed by shopping bots always accurate and unbiased? Explain. |

|Your Answer | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

| | |

|5. List at least three (3) strengths and weaknesses of online shopping bots. Your listed items should be explained with examples |

|and/or other details. |

|Your Answer |Strengths |

| | |

| | |

| | |

| | |

| |Weaknesses (Hint: consider trust, reliability, and accuracy) |

| | |

| | |

| | |

| | |

| | |

| | |

|Extra Credit Question |

|EC1. Review the computed Product Value Indexes and explain why the highest index value is the best deal. Was the product with the |

|lowest price the best deal? Why not? Your explanation should take into consideration how a product’s features are recognized by |

|the index computation. |

|Your Answer | |

| | |

| | |

| | |

Step 6

Summarize the important product features for the assigned product in the table below. List at least five (5) features but no more than ten (10) features.

|Important Product Features |

|Assigned Product: | |

| 1. | |

| 2. | |

| 3. | |

| 4. | |

| 5. | |

| 6. | |

| 7. | |

| 8. | |

| 9. | |

|10. | |

End of Internet Module exercises

Student Directions:

Excel Module Exercises

Goal: To use Excel to create a DSS (a Decision Support System, in this case a Product Value Index) that objectively compares all major product features and determines the best deal.

Step 1

Open and review the following files.

|File Name |File Type |Description |

|Laptop Project – completed workbook.xls |MS Excel |Predefined Product Value Index worksheets that students |

| | |can be use to compare laptop computers |

|Laptop Project – starter workbook.xls |MS Excel |Partially completed workbook that needs to updated with |

| | |formulas, functions, weights, products info data entry, |

| | |and range names |

|Excel as a DSS |PowerPoint Show |Presentation that gives an overview of using Excel as a |

| | |Decision Support System (DSS). |

|Data Collection sheet update |PowerPoint Show |Presentation with instructions for updating the Data |

| | |Collection worksheet |

|Assigned Weight sheet update |PowerPoint Show |Presentation with instructions for updating the Assigned|

| | |Weight worksheet |

|Calculated Values sheet update |PowerPoint Show |Presentation with instructions for updating the |

| | |Calculated Weighted Values worksheet |

|Lookup values sheet update |MS PowerPoint |Presentation with instructions for updating the Lookup |

| | |Values worksheet |

|VLookup Video |Macromedia SWF |Narrated video with detailed instructions for using the |

| | |VLOOKUP function and range names |

Step 2.

Create a preliminary Product Feature Data Collection worksheet for the product assigned by your instructor.

• Determine the most important product features (limit the number of features to 12 or less).

• Use the provided Laptop Project – starter workbook.xls as a guide for listing product features.

Step 3

Compare your Product Feature Data Collection worksheet with other students and revise, if necessary.

Step 4

Create an Assigned Weights worksheet.

• List features in a table matrix.

• Assign relative weights to each feature based on the importance of the feature.

• Use Assigned Weight sheet update as a guide.

Step 5

Create a Lookup Tables worksheet.

• Create VLOOKUP tables for the expected values for each product feature.

• Using a scale of 0 (zero) to 10, assign a rating to each lookup value.

( a zero rating is the least desirable, a ten rating is the most desirable).

• Assign range names to all the appropriate cells in the VLOOKUP tables.

• Assign range names to the expected values in each VLOOKUP table

(Range names make it easier to reference a range in other formulas and make it easier to understand the formula calculations)

• Use Lookup values sheet update as a guide.

Step 6

Create a Calculated Weighted Values worksheet.

• For each feature, create a formula find the feature value and multiply it by its assigned weight.

• For each product compared, create a formula to total the weighted feature values. (This will be called the Product Value Index).

• Create a formula that compares the Product Value Indexes and indicates the index with the highest value.

• Create a formula that compares the product prices and indicates the lowest price.

• Use Calculated Values sheet update as a guide.

Step 7

Update the Data Collection worksheet.

• For each important feature, use Data Validation to improve the accuracy and speed of data entry.

o Restrict data entry to values listed in the VLOOKUP tables.

o Show helpful data entry instructions when cells are selected.

• Enter product feature information in the worksheet and debug problems, if necessary.

• Use Data Collection sheet update as a guide

End of Excel Module exercises

Assessment

Internet Module Assessment

Students are required to answer these five questions.

1. What is the purpose of a shopping bot?

2. How does a shopping bot gather its information?

3. How does a shopping bot differ from an auction site?

4. Is the information displayed on a shopping bot always accurate and unbiased?

5. List at least three strengths and weaknesses of an online shopping bot.

In addition, there is an extra credit question that students may elect to answer.

EC 1. Explain why the highest Product Value Index is the best deal.

To assess the student’s understanding of the Internet Module material, the following grading rubric will be applied to the student’s answers of the above questions.

(Double-Click to open this embedded worksheet in Excel)

[pic]

Excel Module Assessment

To assess the student’s understanding of the Excel Module material, the following grading rubric will be applied to the student’s efforts to update the product value index starter workbook provided with this RWLO.

(Double-Click to open this embedded worksheet in Excel)

[pic]

Links to Course Competencies

This RWLO could be applied in the following courses:

• Internet Fundamentals

• Spreadsheet Use and Design

• Information Technology Concepts

• Introduction to Business

• E-Commerce Concepts

• Advanced Spreadsheet Projects

• Any other course that deals with gathering and using information from the Internet

• Any other course that uses Excel or other spreadsheet software to create a DSS (Decision Support System)

Specifically, this RWLO meets the following Educational Technology course competencies:

• Use content-specific tools, i.e. the Internet and spreadsheet software, to support learning and research.

• Use technology resources to facilitate higher order and complex thinking skills, including problem solving, critical thinking, informed decision making, knowledge construction, and creativity.

• Use technology to efficiently locate, evaluate, and collect information from a variety of sources.

• Use technology in the development of strategies for solving real world problems.

• Use technology as a tool to objectively analyze data and provide a basis for making reasonable conclusions and decisions.

Supplementary Resources

Internet Shopping Module

Module Goal: To show students how to become informed consumers by taking advantage of the Internet’s strengths while minimizing the risks of online shopping.

The Internet has revolutionized shopping for many consumers. Because of the Internet’s global reach and 24/7 accessibility, shoppers are no longer limited to dealing with local suppliers or forced to shop within limited store business hours. In addition, vast amounts of product and supplier information are readily available at the click of a mouse.

However, as with most advancements in technology, there are risks related to Internet shopping. The impersonal nature of the Internet and the anonymity of buyers and sellers make it difficult to establish a trusting shopping environment. The overwhelming amount of data found on the Internet often contains inaccurate, misleading, outdated, and biased information. The privacy and security of personal information is now subject to global rather than local violations as online “phishing”, “spoofing”, and “hacking” techniques are a constant threat.

Some useful web sites that can provide additional information for this RWLO are listed below.

Internet Module

Wikipedia – a free online encyclopedia that anyone can edit

“Online Shop.” Wikepedia, The Free Enclyclopedia. < >. March 22, 2006.

“Electronic Commerce.” Wikepedia, The Free Enclyclopedia. < >. March 22, 2006.

How Stuff Works – a useful web site with layman explanations of how things work

“How Internet Infrastructure Works.” How Stuff Works. < >. March 22, 2006.

“How E-Commerce Works.” How Stuff Works. .>. March 22, 2006.

PC World Magazine – a magazine devoted to personal computer issues

McCracken, Harry. “Web Savvy: Smart Shopping on Today’s Web.” PC World. March, 2001.< > . March 22, 2006.

Keizer, Greg. “Web Shopping: Bots and Beyond.” PC World. Jan 2001. < >. March 22, 2006.

PC Magazine – a magazine devoted to personal computer issues

“Shopping Around.” PC Magazine. 12 Feb 2002. < >. March 22, 2006.

“Online Shopping Tips.” PC Magazine. 17 Dec 2003. < >. March 22, 2006.

VeriSign web site – a company that specializes in verifying web site security

“Tips for Successful Online Shopping.” < >. March 22, 2006.

Miscellaneous

Mulrean, Jennifer. “How Shopping Bots Really Work.” web site. . March 22, 2006.

Rudl, Corey. “Using Shopping Bot Sites to Attract New Visitors.” Entrepreneur Magazine. 26 Feb 2004. . March 22, 2006.

Excel Module

web site – tutorials, FAQs, and other useful Office Suite information

“Data Validation 1: Control User Choices With Lists in Excel.” < >. March 22, 2006.

“About Nesting Functions Within Functions.” . March 22, 2006.

Internet Shopping – Strengths and Weaknesses

The following table lists some of the major strengths and weaknesses of online shopping.

|Internet Shopping Strengths and Weaknesses |

| |Strengths | |

|Streng|Convenience and Speed |Always “open for business” 24/7 online storefronts. |

|ths | |Easily reached by the click of a mouse from any location that has |

| | |Internet access. |

| | |Self-service format provides fast order processing. |

| | |Virtual nature of store offers broader product selection and “one |

| | |stop” shopping convenience. |

| |Wealth of Data |Easy to search and find products and vendors. |

| | |Easy to find substitutes and less expensive versions of products. |

| |Third-Party Ratings and Comparisons |Consumer and professional product and seller ratings reduce the risk |

| | |of buying the wrong product. |

| | |Shopping bots, information portals, and auction sites make it easy to|

| | |compare specific products. |

| |Global Access |Internet expands access to product suppliers from local geographic |

| | |area to entire world providing more competitive marketplace. |

| |Blended Online/Offline Operations |Integration of online and brick & mortar stores allows consumer to |

| | |order product online and then immediately pickup product at local |

| | |store. |

| |Weaknesses | |

|Weakne|Anonymity and Impersonal Nature of Web |Use of screen names and other forms of anonymity makes it difficult |

|sses | |to establish a trusting shopping environment because it is hard to |

| | |determine if the other party can be trusted. |

| |No “Touch and Feel” Product Testing |Consumers are unable to touch or test a product before buying. If |

| | |online consumer has not done proper product research, it is likely |

| | |that they will be disappointed by missing features, functionality, or|

| | |appearance. |

|Weakne|Inaccurate, Outdated, or Misleading Data |Prices listed on shopping bots and other third-party sites may not |

|sses, | |reflect a seller’s current asking price. Product information used on|

|Contin| |web pages may be purposefully misleading in order to attract online |

|ued | |volume. |

| |Biased Search Rankings |Most shopping bots and search engines accept fees for placing vendors|

| | |higher in search rankings. Pay for ranking placement is intended to |

| | |quickly capture consumer attention before they investigate other |

| | |deals and may mislead many online shoppers into buying the wrong |

| | |products. |

| |Privacy and Security Issues |Online consumers inherently furnish more personal information during |

| | |a purchase and are more subject to identity theft, and privacy |

| | |violations. The lack of face to face contact makes it easier to |

| | |mislead and fail to perform. |

| |Overwhelming Volume of Data |The sheer volume of data retrieved from a product search can |

| | |overwhelm and confuse online consumers unless they know how to |

| | |organize and analyze the data. |

Desired Features – Laptop Computer

Before beginning this project, students should discuss and attempt to define the primary attributes that they would like for the product being purchased. This defined list of important attributes will allow students to focus their research efforts on collecting data for just these attributes in order to avoid the distraction of all the other unnecessary product information. If students do not know enough about a product to create this list, they should do Internet research to become more knowledgeable about the important product features.

For example, the table below lists the primary attributes desired for a laptop computer purchase.

|Primary Attributes Desired – Laptop Computer Purchase |

|Attribute |Description |

|Portability |Light-weight and easy to carry. |

| |Self-contained computer with all devices built-into the laptop case allowing ease of |

| |use in confined work environments. |

|Storage Capacity |Significant amount of permanent built-in hard drive storage capacity. |

| |Removable drive connection capability, i.e. USB and Firewire ports. |

| |Significant amount of temporary storage capacity, i.e. RAM, memory cache, and video |

| |card memory. |

| |Expandable storage capacity to accommodate future increased storage capacity needs. |

|Processing Power |Powerful Central Processing Unit (CPU) capable of running several applications |

| |concurrently, i.e. multi-tasking. |

| |Fast processor clock speed to increase data processing efficiency. |

|Connectivity |Built-in networking (wired and/or wireless) capability to allow easy connection to |

| |most computer networks. |

|Transferability |Built-in drives (floppy, CD/DVD) and ports (USB, serial, infrared, and parallel) to |

| |allow easy transfer of data to/from other computers and digital devices like cameras |

| |and cell phones. |

Recommendations

Recommendations for Integration:

[pic]

This project involves two standalone modules, one covering a unique Internet tool (shopping bots), and the other covering the creation of a spreadsheet tool (Product Value Index) that will help a consumer determine the best deal. An instructor can choose to use either individual module or may choose to use both modules together.

The Internet Module primarily involves observation and discovery and will take approximately 60 minutes or one class period.

The Excel Module is more complicated and time-consuming because it involves more advanced spreadsheet modifications (functions, cell linking, data validation, range names, etc.) and will take approximately 240 minutes or four class periods.

If an instructor chooses to use both modules, it is suggested that the Internet Module be made a classroom assignment with the Excel Module assigned as an outside the classroom special project.

For more information on this project and the relationship between the modules, view the Internet Shopping RWLO Summary PowerPoint presentation.

Classroom Preparation:

In order for students to be able to complete the exercises in this RWLO, the instructor needs to verify that the classroom has Internet access, browser software, and Excel spreadsheet software.

Back-up:

If the classroom environment does not have all of the required resources, an instructor can use the following work-arounds to complete the exercises in this RWLO.

o Limited or No Internet Access

In lieu of Internet access, have the students collect product information from newspapers, and magazines. If some important product feature values are not available in these publications, the instructor should declare values for those features.

o No Excel Spreadsheet Software

If Lotus 1-2-3 or other spreadsheet software is available, the Excel spreadsheets should be converted to the available spreadsheet format. If no spreadsheet software is available, the instructor should have students manually write-in product information on a printed Data Collection worksheet.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download