“Assignment #2
Submit the assignment
in three files, a Word file, a PowerPoint file and an Excel file. The assignment is designed to demonstrate the
Web 2 interactive capabilities of the Internet and the power of databases, to
enhance your knowledge of business enterprise systems and to add to your
knowledge of Excels capabilities from a business marketing perspective. Submit the assignment with the following file
names: last name_first name_A2 web
(lupica_carmela_A2 web), last name_first name_A2 PP (lupica_carmela_A2 PP and
last name_first name_A2_Excel (lupica_carmela_A2 Excel). Save the files in 2010 or 2007 versions so
that I can open them. This will help me
find your assignment quickly should you have any questions on your grade. Submit the assignment by Thursday, October

3 at midnight at the latest. Note, the
day is Thursday and not Sunday to give me time to grade the assignments by
Thursday of the final exam week so that you have feedback prior to submitting
your final exam.
Exercise 1 15 points
1. Several web sites provide information on many products. Review one of the web sites below and then
answer the following questions.

Use.cars.com/””>www.cars.com or.mysimon.com/””>www.mysimon.comto review/assess a product of interest.

a. Identify the variables
used to choose and analyze products on the site and which were most useful to
you. 4 pts

b. How thorough, valid, and valuable
were the product and retailer reviews to you? Explain your response. 4 pts
c. How is the web site functionally similar to a
decision support system (DSS)? Justify your response. 4 pts
2.
Shopping websites seen in #1 above are databases. Identify whether the database you assessed is
a relational, object-oriented or multi-dimensional database. Explain your reasons for your choice. 3 points
Exercise 2: Gaining Business Intelligence from Strategic
Initiatives 15 points
The exercise is adapted from Project IV,
page 337 of the textbook, Business Driven Information Systems by Paige
Baltzan.
You are a new employee in the customer
service department at Premier One, a large office supply distributor. The company has been in business for three
years and focuses on providing top of the line office supplies at a low
cost. The company currently has 90
employees and operates in seven states.
Sales over the past three years have
tripled. The manual systems currently in
place are no longer sufficient to run the business. Your first task is to meet with your new team
and create a PowerPoint presentation for the president and chief executive
officer describing supply chain management, customer relationship management
and enterprise resource planning. In 6-8
slides, the presentation should highlight the main benefits Premier One can
receive from these enterprise systems along with any additional added business
value that can be gained from the systems.
Also, offer any cautions that management should consider. Research the
Internet and find examples of enterprise systems for small businesses. Make a recommendation to management of a SCM,
CRM and ERP enterprise system that might be used but that needs further
research by your team.
Please provide strong contrast between your content and the
background of the presentation. Add notes to the presentation in the notes section of
the software to make sure the instructor understands the contents of the
presentation. Also, add some graphics to
make the presentation more interesting.
Cite your sources in the last slide.

Exercise
3: Sales Support 15 points
You
may use Excel to provide essential insurance sales information. The Excel Insurance Exercise (attached to
this message) shows how information systems can provide practical and important
information in the workplace. This is a
particularly useful exercise for anyone who has a sales job, is an account manager
or wants to start a small business.

Exercise
taken from Management Information Systems, 10th edition by James
A. OBrien and George M Marakas, 2011, page 344.

Christina
Lovan works as an independent agent for Farmers Insurance (.farmers.com/””>www.farmers.com) in OFallon, Illinois.
As an independent agent, she manages her own office and earns her income
entirely from policy sales. Farmers
Insurance supports Christinas efforts with training and access to a web-based
suite of applications that help her manage her business. This extranet includes contact, customer and
policy management systems, as well as corporate communications. The system also includes a feedback feature
agents may use to report software problems or make suggestions regarding future
software enhancements.

Christina
enjoys working with people to help them solve their problems. However, to meet these people, Christina
needs leads. She purchases leads from
Farmers Insurance. Net Quote (.netquote.com/””>www.netquote.com), InsureMe (.insureme.com/””>www.insureme.com), AllQuotes (.allquotesinsurance.com/””>www.allquotesinsurance.com), and Crisscross (.criscross.org/””>www.criscross.org), a database of local residents and businesses. She asks each client for three referrals at
each meeting. She is a member of
Business Networking International (.bni.com/””>www.bni.com), and she is an active member of the local chamber of
commerce.

Although
the software tools provided by Farmers Insurance allow Christina to track
prospective customers, they do not measure information quality. Led information is expensive to purchase or
solicit. Christina needs to know the
best way to spend her time and money.
Until Farmers provides a system update to accommodate this need,
Christina has developed her own data quality evaluation tools using Microsoft
Excel.

With
a few basic Excel skills, we can duplicate Christinas efforts. Download and save Insurance.xls from this
web assignment and use it to complete the following exercises. Note that the data providers used in this
exercise are real, but the data in this spreadsheet are randomly generated. There are three spreadsheet tabs for this
exercise: Cover Sheet, Sale Leads and Pivot Table Extra Credit. If you do not see the tabs at the lower left
hand corner of Excel, click on View on the ribbon and then click on Full
Screen.

a.
Pending
sales need Christinas immediate attention.
Help her spot these opportunities by automatically highlighting
them. Select the Sale Leads
spreadsheet and use Excels Conditional Formatting feature to set the cell
shading color of each Status cell
(Life, Auto, Home, Health) to green if the status is Sale (S) and read if the
status is Pending (P). 5 pts

b.
Select the
Cover Sheet spreadsheet. In cell C2,
use the COUNTA function to count the total number of sales leads in the Sales
Leads sheet. (Note: this calculation
needs to be correct for the final answer to be correct.) In cells C5 through C8, use the COUNTIF
function to count the number of sales made for each type of insurance. In cells D5 through D8, divide the sales made
by the number of leads to calculate the percentage of sales. Write these formulas so they calculate the
correct answers even when Christina later adds more records to the list. Which product has the highest sales rate? 5 pts

c.
When rating
the quality of her information, Christina counts whether or not a lead resulted
in at least one sale. Using the IF and
the OR functions write a formula that will display 1 when at least one sale
was made to a lead and 0 if the lead generated no sales. 5 pts

d.
Extra
Credit (4 points): Ultimately, Christina needs to know which
lead source results in the most sales.
Create a pivot table to average the data
in the Sales for each Source. Format
this result as a percentage. Because a 1 indicates at least one sale and a
0 indicates no sales, the average per source indicates each sources closing rate. Which source had the highest closing
rate? Which source had the lowest
closing rate?
The responses need to be thorough. Be sure to use formulas rather than counting
the information manually since the usefulness of a spreadsheet is to increase
productivity by letting the software do the work for you. I will grade based on your formulas.
Excel is a Microsoft Office application that you should have learned to use in
IFSM 201. Even if you did not learn how
to use some of the functions/formulas in this exercise, you may use Excel
tutorials on the Internet or the help function in Excel to assist you once you
know the basics of how to use Excel. If
you do not know how to use the software formulas, click on formulas in the
Excel software and look for the formula requested in the exercise or type a key
word next to the question mark in the upper right hand corner of the Excel
spreadsheet and read the instructions for performing the task. Youtube.com also has some visuals for using
Excel. If you need assistance, you may send a private message to the
instructor. You may also do some
crowdsourcing by asking a question here to get help from another student or
the instructor. However, no answers can
be exchanged offer only the steps used to perform the task. If any answers are exchanged, I will need to
give the students involved a 0 on the
exercise. You may also share other
tutorial sites that helped you with this exercise.
Sample
tutorials:
.youtube.com/watch?v=Pi38NELdfwA””>www.youtube.com/watch
for conditional formatting tutorial
.youtube.com/watch?v=nsUTFVNu9HU””>www.youtube.com/watch
and.youtube.com/watch?v=ec9D0DLliIM””>www.youtube.com/watch
for CountA function tutorial
.youtube.com/watch?v=wKg5TWGofRg””>www.youtube.com/watch for the
IF then OR function tutorial
Pivot Tables:.timeatlas.com/5_minute_tips/chunkers/learn_to_use_pivot_tables_in_excel_2007_to_organize_data#.UQ7xweiPCQ4″”>http://www.timeatlas.com/5_minute_tips/chunkers/learn_to_use_pivot_tables_in_excel_2007_to_organize_data#.UQ7xweiPCQ4
Grading
Rubric for Assignment #2
Your grade on this exercise is based on the accuracy of your
formulas and calculations. There is more than one answer to this issue
but you must meet the requirements set forth in the exercise.
Grading Rubric for Assignment #2, Exercise 1
and 2

Criteria

Excellent
A
90-100%

Satisfactory
B
80-89%

Needs
Improvement Less than 80%

Exercise
1

Variable
identification and most valuable one. 4
pts

Excellent set of variables and explanation of the most useful one.

Good explanation of variables and explanation of the most useful one.

Inadequate description of the variables and explanation of the most
useful one.

Value
of retailer reviews
4
pts

Excellent explanation of howthorough, valid and
valuable the product and retailer reviews were.

Good explanation of howthorough, valid and
valuable the product and retailer reviews were.

Inadequate explanation of howthorough, valid and
valuable the product and retailer reviews were.

Similarity of web site to a decision
support system (DSS).
4 pts

Excellent
description of the websites similarity or lack of similarity to a decision
support system (DSS).

Gooddescription of the websites
similarity or lack of similarity to a decision support system (DSS).

Inadequatedescription of the
websites similarity or lack of similarity to a decision support system (DSS).

Choice
of database, clarity of the definition and common features
3
pts

Excellent choice of database, clarity of
definition and common features.

Good choice of database, clarity of definition and
common features.

Inadequate choice of database, clarity of the
definition and common features.

Exercise
2

Covers Supply Chain Management benefits, cautions and SCM
recommendation. 5 pts

Thorough presentation of benefits, cautions and plausible
recommendation for the office supply company.

Presentation is missing some benefits, cautions but has a
plausible recommendation for the office supply company.

Presentation is missing most benefits, cautions and a
plausible recommendation for the office supply company.

Covers Customer Relationship Management benefits, cautions
and CRM recommendation. 5 pts

Thorough presentation of benefits, cautions and plausible
recommendation for the office supply company.

Presentation is missing some benefits, cautions but has a
plausible recommendation for the office supply company.

Presentation is missing most benefits, cautions and a
plausible recommendation for the office supply company.

Covers Enterprise Resource Planning benefits, cautions and
ERP recommendation. 5 pts.

Thorough presentation of benefits, cautions and plausible
recommendation for the office supply company.

Presentation is missing some benefits, cautions but has a
plausible recommendation for the office supply company.

Presentation is missing most benefits, cautions and a
plausible recommendation for the office supply company.

Criteria

Excellent
A (90+)

Satisfactory
B (80-89)

Needs
Improvement (below 80)

Exercise
3

Uses
Excels Conditioning Formatting feature.
5 pts

Properly
uses the conditioning formatting feature to obtain the correct answer.

Uses
a portion of the conditioning formatting feature to obtain the correct
answer.

Does
not use the conditioning format feature properly.

Completes
Excel Cover Sheet information. 5 pts

Correctly
completes the Cover Sheet information.

Completes
the Cover Sheet information with one error.

Completes
the Cover Sheet information with two or more errors.

Uses
the IF and OR functions. 5 pts

Correctly
uses the IF and OR functions.

Makes
one error in performing the IF and OR function.

Makes
two or more errors in performing the IF and OR function.

Completes
the Pivot exercise for extra credit. 4 pts

Correctly completes the pivot table with the averages in
the format of percentages.

Makes an error in completing the pivot table for the
average in the format of percentages for two of the sources.

Correctly completes a pivot table for one of the sources.

Your
Grade:

Additional
Comments:

Assignment #2Submit the assignment
in three files, a Word file, a PowerPoint file and an Excel file. The assignment is designed to demonstrate the
Web 2 interactive capabilities of the Internet and the power of databases, to
enhance your knowledge of business enterprise systems and to add to your
knowledge of Excels capabilities from a business marketing perspective. Submit the assignment with the following file
names: last name_first name_A2 web
(lupica_carmela_A2 web), last name_first name_A2 PP (lupica_carmela_A2 PP and
last name_first name_A2_Excel (lupica_carmela_A2 Excel). Save the files in 2010 or 2007 versions so
that I can open them. This will help me
find your assignment quickly should you have any questions on your grade. Submit the assignment by Thursday, October
3 at midnight at the latest. Note, the
day is Thursday and not Sunday to give me time to grade the assignments by
Thursday of the final exam week so that you have feedback prior to submitting
your final exam. Exercise 1 15 points1. Several web sites provide information on many products. Review one of the web sites below and then
answer the following questions.
Use.cars.com/””>www.cars.com or.mysimon.com/””>www.mysimon.comto review/assess a product of interest.

a. Identify the variables
used to choose and analyze products on the site and which were most useful to
you. 4 pts
b. How thorough, valid, and valuable
were the product and retailer reviews to you? Explain your response. 4 pts c. How is the web site functionally similar to a
decision support system (DSS)? Justify your response. 4 pts 2.
Shopping websites seen in #1 above are databases. Identify whether the database you assessed is
a relational, object-oriented or multi-dimensional database. Explain your reasons for your choice. 3 pointsExercise 2: Gaining Business Intelligence from Strategic
Initiatives 15 pointsThe exercise is adapted from Project IV,
page 337 of the textbook, Business Driven Information Systems by Paige
Baltzan.You are a new employee in the customer
service department at Premier One, a large office supply distributor. The company has been in business for three
years and focuses on providing top of the line office supplies at a low
cost. The company currently has 90
employees and operates in seven states.Sales over the past three years have
tripled. The manual systems currently in
place are no longer sufficient to run the business. Your first task is to meet with your new team
and create a PowerPoint presentation for the president and chief executive
officer describing supply chain management, customer relationship management
and enterprise resource planning. In 6-8
slides, the presentation should highlight the main benefits Premier One can
receive from these enterprise systems along with any additional added business
value that can be gained from the systems.
Also, offer any cautions that management should consider. Research the
Internet and find examples of enterprise systems for small businesses. Make a recommendation to management of a SCM,
CRM and ERP enterprise system that might be used but that needs further
research by your team.Please provide strong contrast between your content and the
background of the presentation. Add notes to the presentation in the notes section of
the software to make sure the instructor understands the contents of the
presentation. Also, add some graphics to
make the presentation more interesting.
Cite your sources in the last slide. Exercise
3: Sales Support 15 pointsYou
may use Excel to provide essential insurance sales information. The Excel Insurance Exercise (attached to
this message) shows how information systems can provide practical and important
information in the workplace. This is a
particularly useful exercise for anyone who has a sales job, is an account manager
or wants to start a small business. Exercise
taken from Management Information Systems, 10th edition by James
A. OBrien and George M Marakas, 2011, page 344.Christina
Lovan works as an independent agent for Farmers Insurance (.farmers.com/””>www.farmers.com) in OFallon, Illinois.
As an independent agent, she manages her own office and earns her income
entirely from policy sales. Farmers
Insurance supports Christinas efforts with training and access to a web-based
suite of applications that help her manage her business. This extranet includes contact, customer and
policy management systems, as well as corporate communications. The system also includes a feedback feature
agents may use to report software problems or make suggestions regarding future
software enhancements. Christina
enjoys working with people to help them solve their problems. However, to meet these people, Christina
needs leads. She purchases leads from
Farmers Insurance. Net Quote (.netquote.com/””>www.netquote.com), InsureMe (.insureme.com/””>www.insureme.com), AllQuotes (.allquotesinsurance.com/””>www.allquotesinsurance.com), and Crisscross (.criscross.org/””>www.criscross.org), a database of local residents and businesses. She asks each client for three referrals at
each meeting. She is a member of
Business Networking International (.bni.com/””>www.bni.com), and she is an active member of the local chamber of
commerce.Although
the software tools provided by Farmers Insurance allow Christina to track
prospective customers, they do not measure information quality. Led information is expensive to purchase or
solicit. Christina needs to know the
best way to spend her time and money.
Until Farmers provides a system update to accommodate this need,
Christina has developed her own data quality evaluation tools using Microsoft
Excel.With
a few basic Excel skills, we can duplicate Christinas efforts. Download and save Insurance.xls from this
web assignment and use it to complete the following exercises. Note that the data providers used in this
exercise are real, but the data in this spreadsheet are randomly generated. There are three spreadsheet tabs for this
exercise: Cover Sheet, Sale Leads and Pivot Table Extra Credit. If you do not see the tabs at the lower left
hand corner of Excel, click on View on the ribbon and then click on Full
Screen.a.
Pending
sales need Christinas immediate attention.
Help her spot these opportunities by automatically highlighting
them. Select the Sale Leads
spreadsheet and use Excels Conditional Formatting feature to set the cell
shading color of each Status cell
(Life, Auto, Home, Health) to green if the status is Sale (S) and read if the
status is Pending (P). 5 ptsb.
Select the
Cover Sheet spreadsheet. In cell C2,
use the COUNTA function to count the total number of sales leads in the Sales
Leads sheet. (Note: this calculation
needs to be correct for the final answer to be correct.) In cells C5 through C8, use the COUNTIF
function to count the number of sales made for each type of insurance. In cells D5 through D8, divide the sales made
by the number of leads to calculate the percentage of sales. Write these formulas so they calculate the
correct answers even when Christina later adds more records to the list. Which product has the highest sales rate? 5 ptsc.
When rating
the quality of her information, Christina counts whether or not a lead resulted
in at least one sale. Using the IF and
the OR functions write a formula that will display 1 when at least one sale
was made to a lead and 0 if the lead generated no sales. 5 ptsd.
Extra
Credit (4 points): Ultimately, Christina needs to know which
lead source results in the most sales.
Create a pivot table to average the data
in the Sales for each Source. Format
this result as a percentage. Because a 1 indicates at least one sale and a
0 indicates no sales, the average per source indicates each sources closing rate. Which source had the highest closing
rate? Which source had the lowest
closing rate?The responses need to be thorough. Be sure to use formulas rather than counting
the information manually since the usefulness of a spreadsheet is to increase
productivity by letting the software do the work for you. I will grade based on your formulas.
Excel is a Microsoft Office application that you should have learned to use in
IFSM 201. Even if you did not learn how
to use some of the functions/formulas in this exercise, you may use Excel
tutorials on the Internet or the help function in Excel to assist you once you
know the basics of how to use Excel. If
you do not know how to use the software formulas, click on formulas in the
Excel software and look for the formula requested in the exercise or type a key
word next to the question mark in the upper right hand corner of the Excel
spreadsheet and read the instructions for performing the task. Youtube.com also has some visuals for using
Excel. If you need assistance, you may send a private message to the
instructor. You may also do some
crowdsourcing by asking a question here to get help from another student or
the instructor. However, no answers can
be exchanged offer only the steps used to perform the task. If any answers are exchanged, I will need to
give the students involved a 0 on the
exercise. You may also share other
tutorial sites that helped you with this exercise.Sample
tutorials:.youtube.com/watch?v=Pi38NELdfwA””>www.youtube.com/watch
for conditional formatting tutorial.youtube.com/watch?v=nsUTFVNu9HU””>www.youtube.com/watch
and.youtube.com/watch?v=ec9D0DLliIM””>www.youtube.com/watch
for CountA function tutorial.youtube.com/watch?v=wKg5TWGofRg””>www.youtube.com/watch for the
IF then OR function tutorial Pivot Tables:.timeatlas.com/5_minute_tips/chunkers/learn_to_use_pivot_tables_in_excel_2007_to_organize_data#.UQ7xweiPCQ4″”>http://www.timeatlas.com/5_minute_tips/chunkers/learn_to_use_pivot_tables_in_excel_2007_to_organize_data#.UQ7xweiPCQ4Grading
Rubric for Assignment #2Your grade on this exercise is based on the accuracy of your
formulas and calculations. There is more than one answer to this issue
but you must meet the requirements set forth in the exercise.Grading Rubric for Assignment #2, Exercise 1
and 2CriteriaExcellent
A90-100%Satisfactory
B80-89% Needs
Improvement Less than 80%Exercise
1Variable
identification and most valuable one. 4
ptsExcellent set of variables and explanation of the most useful one.Good explanation of variables and explanation of the most useful one.Inadequate description of the variables and explanation of the most
useful one.Value
of retailer reviews4
ptsExcellent explanation of howthorough, valid and
valuable the product and retailer reviews were.Good explanation of howthorough, valid and
valuable the product and retailer reviews were.Inadequate explanation of howthorough, valid and
valuable the product and retailer reviews were.Similarity of web site to a decision
support system (DSS).
4 ptsExcellent
description of the websites similarity or lack of similarity to a decision
support system (DSS). Gooddescription of the websites
similarity or lack of similarity to a decision support system (DSS). Inadequatedescription of the
websites similarity or lack of similarity to a decision support system (DSS). Choice
of database, clarity of the definition and common features3
ptsExcellent choice of database, clarity of
definition and common features.Good choice of database, clarity of definition and
common features.Inadequate choice of database, clarity of the
definition and common features. Exercise
2Covers Supply Chain Management benefits, cautions and SCM
recommendation. 5 ptsThorough presentation of benefits, cautions and plausible
recommendation for the office supply company.Presentation is missing some benefits, cautions but has a
plausible recommendation for the office supply company.Presentation is missing most benefits, cautions and a
plausible recommendation for the office supply company.Covers Customer Relationship Management benefits, cautions
and CRM recommendation. 5 ptsThorough presentation of benefits, cautions and plausible
recommendation for the office supply company.Presentation is missing some benefits, cautions but has a
plausible recommendation for the office supply company.Presentation is missing most benefits, cautions and a
plausible recommendation for the office supply company.Covers Enterprise Resource Planning benefits, cautions and
ERP recommendation. 5 pts.Thorough presentation of benefits, cautions and plausible
recommendation for the office supply company.Presentation is missing some benefits, cautions but has a
plausible recommendation for the office supply company.Presentation is missing most benefits, cautions and a
plausible recommendation for the office supply company.Criteria Excellent
A (90+) Satisfactory
B (80-89) Needs
Improvement (below 80) Exercise
3 Uses
Excels Conditioning Formatting feature.
5 ptsProperly
uses the conditioning formatting feature to obtain the correct answer.Uses
a portion of the conditioning formatting feature to obtain the correct
answer.Does
not use the conditioning format feature properly.Completes
Excel Cover Sheet information. 5 ptsCorrectly
completes the Cover Sheet information.Completes
the Cover Sheet information with one error.Completes
the Cover Sheet information with two or more errors.Uses
the IF and OR functions. 5 ptsCorrectly
uses the IF and OR functions.Makes
one error in performing the IF and OR function.Makes
two or more errors in performing the IF and OR function.Completes
the Pivot exercise for extra credit. 4 pts Correctly completes the pivot table with the averages in
the format of percentages.Makes an error in completing the pivot table for the
average in the format of percentages for two of the sources.Correctly completes a pivot table for one of the sources.Your
Grade:
Additional
Comments: “