Integrate the core values of Saint Leo University that you believe should apply to this issue.

Case Studies guidelines: Each case study will include issues/questions to address. The cases are intended for students to take a comprehensive look at the current event topics as related to business and society. Students should explore the issue using the text as well as personal, intellectual, academic references, and/or experiences, thus applying their knowledge to these cases. Where sources are used, proper notation must be included. Responses to each assigned question in all assigned case studies should be in paragraph form, be double-spaced, and be between 250-300 words in.

St Leo Core Values: Core Values: The SLU core values of responsible stewardship, excellence, and integrity will be emphasized in this course. Responsible Stewardship: Our Creator blesses us with an abundance of resources. We foster a spirit of service to employ our resources to university and community development. We must be resourceful. We must optimize and apply all of the resources of our community to fulfill Saint Leo University’s mission and goals. Excellence: Saint Leo University is an educational enterprise. All of us, individually and collectively, work hard to ensure that our students develop the character, learn the skills, and assimilate the knowledge essential to become morally responsible leaders. The success of our University depends upon a conscientious commitment to our mission, vision, and goals. Integrity: The commitment of Saint Leo University to excellence demands that its members live its mission and deliver on its promise. The faculty, staff, and students pledge to be honest, just, and consistent in word and deed.

CASE- Google and the Right to Be Forgotten

In 2009, Mario Costeja Gonzalez, a self-employed attorney living in a small town outside Madrid, Spain, casually “googled” himself and was startled by what came up on his computer screen. Prominently displayed in the search results was a brief legal notice that had appeared more than a decade earlier in a local newspaper, La Vanguardia, which listed property seized and being auctioned by a government agency for nonpayments of debts. Among the properties was a home jointly owned by Costeja and his wife.

Costeja immediately realized that this information could damage his reputation as an attorney. Equally troubling, the information was no longer factual. He had paid his debt nearly a decade earlier. Abanlex, Costeja’s small law firm, depended on the Internet to gain much of its new business, which was often generated by a Google search. Potential clients might choose not to hire him, based on the old auction notice, he reflected. His mind then turned to the possible effects of this kind of information on other people’s livelihoods. “There are people who cannot get a job because of content that is irrelevant,” he thought.1 “I support freedom of expression and I do not defend censorship. [However, I decided] to fight for the right to request the deletion of data that violates the honor, dignity and reputation of individuals.”2

The next week, Costeja wrote to La Vanguardia and requested that it remove the article about his debt notice, because it had had been fully resolved a number of years earlier and reference to it now was therefore entirely irrelevant.3 In doing so, he was making use of his rights under Spain’s strong data protection policies, which recognized the protection and integrity of personal data as a constitutional right under Section 18 of the nation’s Data Protection Act.4 In response, the newspaper informed him that it had recently uploaded to the Internet all its past archives, dating back to 1881, to allow them to be searched by the public. It also noted that the auction notice had originally been publicly posted in order to secure as many bidders as possible. The newspaper refused Costeja’s request, stating that the information was obtained from public records and had thus been published lawfully.5

To be sure, the real problem for Costeja was not that the notice had appeared in La Vanguardia’s digital library, but that it had shown up in the results of the most widely used search engine in the world, Google, where potential clients might use it to judge his character.6 Following this reasoning, Costeja then wrote to Google Spain, the firm’s Spanish affiliate, only to be told that the parent company, Google Inc., was the entity responsible for the development of search results.7 Costeja was taken aback by this development. “The resources Google has at their disposal aren’t like those of any other citizens,” he reflected.8 Costeja felt he would be at a disadvantage in a lawsuit against an industry giant like Google.

In March 2010, after his unsuccessful attempts with the newspaper and Google Spain, Costeja turned to Spain’s Data Protection Agency (SDPA), the government agency responsible for enforcing the Data Protection Act. “Google in Spain asked me to address myself to its headquarters in the U.S., but I found it too far and difficult to launch a complaint in the U.S., so I went to the agency in Spain to ask for their assistance. They said I was right, and the case went to court,” he explained.9 In a legal filing, Costeja requested, first, that the agency issue an administrative order requiring La Vanguardia either to remove or alter the pages in question (so that his personal data no longer appeared) or to use certain tools made available by search engines in order to shield the data from view. Second, he requested that the agency require that Google Spain or Google Inc. remove or conceal his personal data so that it no longer appeared in the search results and in the links to La Vanguardia. Costeja stated that his debt had been fully resolved.10

With these steps, a small-town Spanish lawyer had drawn one of the world’s richest and best-known companies, Google, into a debate over the right to be forgotten.

Google, Inc.

Google Inc. was a technology company that built products and provides services to organize information. Founded in 1998 and headquartered in Mountain View, CA, Google’s mission was to organize the world’s information and make it universally accessible and useful. It employed more than 55,000 people and had revenues of $45 billion. The company also had 70 offices in more than 40 countries.

The company’s main product, Google Search, provided information online in response to a user’s search. Google’s other well-known products provided additional services. For example, Google Now provided information to users when they needed it, and its Product Listing Ads offered product image, price, and merchant information. The company also provided AdWords, an auction-based advertising program and AdSense, which enabled websites that were part of the Google network to deliver ads. Google Display was a display advertising network; DoubleClick Ad Exchange was a marketplace for the trading display ad space; and YouTube offered video, interactive, and other ad formats.

Search Technology

In its core business, Google conducted searches in three stages: crawling and indexing, applying algorithms, and fighting spam.

Crawlers, programs that browsed the web to create an index of data, looked at web pages and followed links on those pages. They then moved from link to link and brought data about those web pages back to Google’s servers. Google would then use this information to create an index to know exactly how to retrieve information for its users. Algorithms were the computer processes and formulas that took users’ questions and turned them into answers. At the most basic level, Google’s algorithms looked up the user’s search terms in the index to find the most appropriate pages. For a typical query, thousands, if not millions, of web pages might have helpful information. Google’s algorithms relied on more than 200 unique signals or “clues” that made it possible to guess what an individual was really looking for. These signals included the terms on websites, the freshness of content, the region, and the page rank of the web page.11 Lastly, the company fought spam through a combination of computer algorithms and manual review. Spam sites attempted to game their way to the top of search results by repeating keywords, buying links that passed Google’s Page Rank process, or putting invisible text on the screen. Google scouted out and removed spam because it could make legitimate websites harder to find. While much of this process was automated, Google did maintain teams whose job was to review sites manually.

Policy on Information Removal

Google’s policy on the general removal of information was the following:

Upon request, we’ll remove personal information from search results if we believe it could make you susceptible to specific harm, such as identity theft or financial fraud. This includes sensitive government ID numbers like U.S. Social Security numbers, bank account numbers, credit card numbers and images of signatures. We generally don’t process removals of national ID numbers from official government websites because in those cases we consider the information to be public. We sometimes refuse requests if we believe someone is attempting to abuse these policies to remove other information from our results.1

Apart from this general policy, Google Inc. also removed content or features from its search results for legal reasons. For example, in the United States, the company would remove content with valid notification from the copyright holder under the Digital Millennium Copyright Act (DMCA), which was administered by the U.S. Copyright Office. The DCMA provided recourse for owners of copyrighted materials who believed that their rights under copyright law had been infringed upon on the Internet.14 Under the notice and takedown procedure of the law, a copyright owner could notify the service provider, such as Google, requesting that a website or portion of a website be removed or blocked. If, upon receiving proper notification, the service provider promptly did so, it would be exempt from monetary liability.

Google regularly received such requests from copyright holders and those that represented them, such as the Walt Disney Company and the Recording Industry Association page 483of America. Google produced and made public a list of the domain portions of URLs that had been the subject of a request for removal, and noted which ones had been removed. As of July 2015, it had removed more than 600,000 URLs out of more than 2.4 million requests.

Likewise, content on local versions of Google was also removed when required by national laws. For example, content that glorified the Nazi party was illegal in Germany, and content that insulted religion was illegal in India.16 The respective governments, via a court order or a routine request as described above, typically made these requests. Google reviewed these requests to determine if any content should be removed because it violated a specific country’s law.

When Google removed content from search results for legal reasons, it first displayed a notification that the content had been removed and then reported the removal to www.chillingeffects.org, a website established by the Electronic Frontier Foundation and several law schools. This website, which later changed its name to lumendatabase.org, collected and analyzed legal complaints and requests for removal of a broad set of online materials. It was designed to help Internet users know their rights and understand the law. Researchers could use the data to study the prevalence of legal threats and the source of content removals. This database also allowed the public to search for specific takedown notifications.17

Google removed content quickly. Its average processing time across all copyright infringement removal requests submitted via its website was approximately 6 hours. Different factors influenced the processing time, including the method of delivery, language, and completeness of the information submitted.

The Right to Be Forgotten

The right to be forgotten can be understood as peoples’ right to request that information be removed from the Internet or other repositories because it violated their privacy or was no longer relevant. This right assumed greater prominence in the digital era, when people began finding it increasingly difficult to escape information that had accumulated over many years, resulting in expressions such as “the net never forgets,” “everything is in the cloud,” “reputation bankruptcy,” and “online reputation.”18 According to Jeffrey Rosen, professor of law at George Washington University, the intellectual roots of the right to be forgotten could be found in French law, “which recognizes le droit à l’oubli—or the ‘right of oblivion’—a right that allows a convicted criminal who has served his time and been rehabilitated to object to the publication of the facts of his conviction and incarceration.”19

Although the right to be forgotten was rooted in expunging criminal records, the rise of the Internet had given the concept a new, more complex meaning. Search engines enabled users to access information on just about any topic with considerable ease. The ease with which information could be shared, stored, and retrieved through online search raised issues of both privacy and freedom of expression. On the one hand, when opening a bank account, joining a social networking website, or booking a flight online, a consumer would voluntarily disclose vital personal information such as name, address, and credit card numbers. Consumers were often unsure of what happened to their data and were concerned that it might fall into the wrong hands—that is, that their privacy would be violated.

On the other hand, by facilitating the retrieval of information, search engines enhanced individuals’ freedom to receive and impart information. Any interference with search engine activities could therefore pose a threat to the effective enjoyment of these rights.20 As Van Alsenoy, a researcher at the Interdisciplinary Center for Law and Information Communication Technology, argued, “In a world where search engines are used as the main tool to find relevant content online, any governmental interference in the provisioning of these services presents a substantial risk that requires close scrutiny.

Europe

Since the 1990s, both the European Union and its member states (such as Spain) had enacted laws that addressed the right to privacy and, by extension, the right to be forgotten.

A fundamental right of individuals to protect their data was introduced in the EU’s original data protection law, passed in 1995. Specifically, the European Data Protection Directive 95/46 defined the appropriate scope of national laws relating to personal data and the processing of those data. According to Article 3(1), Directive 95/46 applied “to the processing of personal data wholly or partly by automatic means, and to the processing otherwise than by automatic means of personal data which form part of a filing system or are intended to form part of a filing system.”22 Article 2(b) of the EU Data Protection Directive 95/46 defined the processing of personal data as

any operation or set of operations which is performed upon personal data, whether or not by automatic means, such as collection, recording, organization, storage, adaptation or alteration, retrieval, consultation, use, disclosure by transmission, dissemination or otherwise making available, alignment or combination, blocking, erasure or destruction.

Individual countries within the European Union also enacted their own laws, which were sometimes stronger than those of the EU. For example, in Spain, the protection of data was a constitutional right. The Spanish Constitution recognized the right to personal privacy, secrecy of communications, and the protection of personal data. These rights were protected through the Data Protection Act (the “Act”), passed in 1999, which incorporated the 1995 European Directive on data protection, and was enforced by the Spanish Data Protection Agency (SDPA). Created in 1993, this agency was relatively inactive until the passing of the Act, which gave it more powers and a mandate to enforce privacy rules in a wide range of situations.23

The Spanish agency exercised its powers broadly. For example, in 2013, it fined telecom firm Telefonica SA €20,000 for twice listing an individual’s phone number in local phone books without the individual’s prior consent. In 2008, the agency fined a marketing company €600 for using “recommend this to a friend” icons on websites, saying that senders of recommendation e-mails had to first request the recipient’s permission. The agency had also successfully required anyone using security cameras to clearly mark their presence with a recognizable icon. Supporters of this move have highlighted the importance of transparency in protecting one’s privacy.24

Over time, however, differences in the way that each EU country interpreted privacy rights led to an uneven level of protection for personal data, depending on where an page 485individual lived or bought goods and services. This led the European high court to take a second look, in 2013, at the original law.25 A European Commission memo at that time noted that the right “is about empowering individuals, not about erasing past events or restricting freedom of the press.”26 The changes were intended to give citizens more control over their personal data, making it easier to access and improve the quality of information they received about what happened to their data once they decided to share it. An unanswered question, however, was the latitude given to national courts and regulators across Europe to set the parameters by which these requests could be made.

The United States

U.S. courts had taken a very different approach to privacy and to the right to be forgotten. A few U.S. laws recognized the right to be forgotten; the Fair Credit Reporting Act of 1970, for example, gave individuals the right to delete certain negative information about their credit—such as late payments, tax liens, or judgments—seven years from the date of the delinquency. But, for the most part, fundamental differences in legal philosophy made this right less likely to become widely supported in the United States. In an article published in the Atlantic in May 2014, Matt Ford suggested that in the U.S. context, one person’s right to be forgotten logically imposed a responsibility to forget upon someone else, a notion that was alien to American law. The First Amendment to the Constitution barred the government from interfering with free speech. Law professor Rosen argued that the First Amendment would make a right to be forgotten virtually impossible, not only to create but to enforce. For example, the U.S. Supreme Court ruled in 1989 that penalizing a newspaper for publishing truthful, lawfully obtained information from the public record was unconstitutional

The Lawsuit and Court Decision

The main focus of Costeja’s complaint before Spanish Data Protection Agency (SDPA) was his request that La Vanguardia remove the debt notice from its archives. In doing so, he was claiming his constitutional right to protect the integrity of his personal data. Costeja’s request had two parts: that (1) La Vanguardia be required either to remove or alter the pages in question or to use certain tools made available by search engines in order to protect the data and (2) that Google Spain or Google Inc. be required to remove or conceal the personal data relating to him so that the data no longer appeared in search results.

In July 2010, two months after Costeja’s original request, the SDPA ordered Google Spain and Google Inc. to take “all reasonable steps to remove the disputed personal data from its index and preclude further access,” upholding that part of the complaint.29 However, the SDPA rejected Costeja’s complaint as it related to La Vanguardia, because it considered that the publication by it of the information in question was legally justified.

A year later, Google filed an appeal against the decision by the SDPA before the Audiencia Nacional in Madrid, Spain’s highest national court. In March 2012, this court referred the case to the European Court of Justice, the EU’s high court, for a preliminary ruling.31

In their briefs, Google Spain and Google Inc.’s argument hinged on the meaning of “personal data” and “crawling.” Crawling, as noted above, was the use of software programs to find multiple websites that responded to requests for information online.32 These programs were configured to look for information on the Internet, according to a set of criteria that told them where to go and when.33 Once the relevant web pages had been copied and collected, their content was analyzed and indexed.34 Google compared its search engine index to an index at the back of a textbook, in that it included information about words and their locations.35

Specifically, Google argued before the European Court of Justice that because it crawled and indexed websites “indiscriminately” (that is, without a deliberate intent to process personal data as such), no processing of personal data within the meaning of Article 2 (b) of the EU Data Protection Directive 95/46 actually took place. This absence of intent, the company argued, clearly distinguished Google’s activities as a search engine provider from the processing of personal data as interpreted by the Court.

Google’s other main argument was that the publisher of the information should be the sole controller of data, not the search engine. After all, its attorneys argued, Google’s intervention was purely accessory in nature; it was merely making information published by others more readily accessible. If a publisher, for whatever reason, decided to remove certain information from its website, this information would (eventually) be removed from Google’s index and would no longer appear in its search results. As a result, Google’s counsel argued, the role of a search engine should be thought of as an “intermediary.”

In May 2014, the European Court of Justice ruled against Google. The court found the Internet search provider was responsible for the processing of personal data that appeared on web pages published by third parties. It further required Google to remove links returned in search results based on an individual’s name when those results were deemed to be “inadequate, irrelevant or no longer relevant, or excessive.” At the heart of the court’s logic was the process that Google used to produce its search results. The official ruling explained the court’s rationale:

The Court points out in this context that processing of personal data carried out by such an operator enables any Internet user, when he makes a search on the basis of an individual’s name, to obtain, through the list of results, a structured overview of the information relating to that individual on the internet. The Court observes, furthermore, that this information potentially concerns a vast number of aspects of his private life and that, without the search engine, the information could not have been interconnected or could have been only with great difficulty.

In essence, the Court ruled that an activity, “whether or not by automatic means” could be considered to be the “processing of personal data” within the meaning of Article 2(b), even page 487if no intention to process such data existed.37 The court’s ruling applied to any search engine operators that had a branch or a subsidiary in any of the 28 member states of the EU.

Costeja’s lawyer, Joaquín Muñoz, was pleased with the ruling. “When you search for something in Google, they don’t scour the entire Internet for you and then give you a result. They’ve stored links, organized them, and they show them based on a criteria they’ve decided upon.”39 As for Costeja, he expressed satisfaction with the result of his four-year legal crusade. Speaking of the court’s decision, he said, “I think this is the correct move. You have to provide a path for communication between the user and the search engine. Now that communication can take place.

Google’s Application of the Ruling

For its part, Google—although disappointed with the ruling—set about complying with it. Soon after the court decision, it removed Costeja’s disputed information from its search results. But, the company also took mre general action.

The Court’s decision recognized Google as a data controller, or the operator of the search engine and the party responsible for its data. As such, the court said, Google was required to police its links and put into place a mechanism to address individual concerns. Accordingly, shortly after the ruling was announced, Google set up an online form for users (from the European Union only) to request the right to be forgotten. The company website stated that each request would be evaluated individually and that Google would attempt to “balance the privacy rights of the individual with the public’s interest to know and the right to distribute information.”41 Once an individual had filled out the form, he or she received a confirmation. Each request was assessed on a case-by-case basis. Occasionally, Google would ask for more information from the individual. Once Google had made its decision, it notified the individual by e-mail, providing a brief explanation if the decision was against removal. If so, the individual could request that a local data protection authority review Google’s decision.

In evaluating a request, Google looked at whether the results included outdated or inaccurate information about the individual. It also weighed whether or not the information was of public interest. For example, Google generally retained the information if it related to financial scams, professional malpractice, criminal convictions, or a government official’s public conduct.42

At the same time, Google invited eight independent experts to form an advisory council expressly to “advise it on performing the balancing act between an individual’s right to privacy and the public’s interest in access to information.”43 The committee included three professors (two of law and one of ethics), a newspaper editorial director, a former government page 488official, and three privacy and freedom of speech experts (including one from the United Nations). Google’s CEO and chief legal officer served as conveners. The committee’s job was to provide recommendations to Google on how to best implement the EU court’s ruling.

The majority recommendation of the advisory council, published on February 6, 2015, was that the right to be forgotten ruling should apply only within the 28 countries in the European Union.44 As a practical matter, this meant that Google was only required to apply removals to European domains, such as Google.fr or Google.co.uk, but not Google.com, even when accessed in Europe. Although over 95 percent of all queries originating in Europe used European domains, users could still access information that had been removed via the Google.com site.

The report also explained that once the information was removed, it was still available at the source site (e.g., the newspaper article about Costeja in La Vanguardia). Removal meant merely that its accessibility to the general public was reduced because searches for that information would not return a link to the source site. A person could still find the information, since only the link to the information had been removed, not the information itself.

The advisory council also recommended a set of criteria Google should use in assessing requests by individuals to “delist” their information (that is, to remove certain links in search results based on queries for that individual’s name). How should the operator of the search engine best balance the privacy and data protection rights of the subject with the interest of the general public in having access to the information? The authors of the report felt that whether the data subject experienced harm from such accessibility to the information was relevant to this balancing test. Following this reasoning, they identified four primary criteria for evaluating delisting requests:

• First, what was the data subject’s role in public life? Did the individuals have a clear role in public life (CEOs, politicians, sports stars)? If so, this would weigh against delisting.
• Second, what type of information was involved? Information that would normally be considered private (such as financial information, details of a person’s sex life, or identification numbers) would weigh toward delisting. Information that would normally be considered to be in the public interest (such as data relevant to political discourse, citizen engagement, or governance) would normally weigh against delisting.
• Third, what was the source of the information? Here, the report suggested that journalistic writing or government publications would normally not be delisted.
• Finally, the report considered the effect of time, given that as circumstances change, the relevance of information might fade. Thus, the passage of time might favor delisting.

The advisory council also considered procedures and recommended that Google adopt an easily accessible and easy-to-understand form for data subjects to use in submitting their requests.

The recommendations of the advisory council were not unanimous. Jimmy Wales, the cofounder of Wikipedia and one of the eight group members, appended a dissenting comment to the report. “I completely oppose the legal situation in which a commercial company is forced to become the judge of our most fundamental rights of expression and privacy, without allowing any appropriate procedure for appeal by publishers whose work in being suppressed,” Mr. Wales wrote. “The recommendations to Google contained in this report are deeply flawed due to the law itself being deeply flawed.

In this project, you will work with sales data from Top’t Corn, a popcorn company with an online store, multiple food trucks, and two retail stores. You will begin by copying the sales data for one of the retail stores from another workbook. Next, you will insert a new worksheet and enter sales data for the four food truck locations, formatting the data, and calculating totals. You will create a pie chart ton this project, you will work with sales data from Top’t Corn, a popcorn company with an online store, multiple food trucks, and two retail stores. You will begin by copying the sales data for one of the retail stores from another workbook. Next, you will insert a new worksheet and enter sales data for the four food truck locations, formatting the data, and calculating totals. You will create a pie chart to represent the total units sold by location and a column chart to represent sales by popcorn type. You will format the charts, and then set up the worksheet for printing. Next, you help Top’t Corn calculate payments for a loan and decide whether or not the purchase is a good idea. Working with the daily sales data for one of the brick-and-mortar stores, you will apply conditional formatting to find the top 10 sales dates. Y ou will also calculate the sales for each date, and the average, minimum, and maximum sales. You will use Goal Seek to find the appropriate price to reach a higher daily average sales goal. You will use VLOOKUP to look up sales data for a specific date. Finally, you will work with their online sales data to format it as an Excel table and apply sorting and filtering. You will create a PivotTable and a PivotChart from a copy of the online sales data to summarize the sales.

Skills needed to complete this project: 

Open a workbook

Copy a worksheet to another workbook

• Close a workbook
• Insert a worksheet
• Name a worksheet
• Move a worksheet
• Enter text
• Enter numbers
• Edit text
• Autofit a column
• Apply a cell style
• Add cell shading
• Change font color
• Merge and center text across cells
• Apply bold formatting
• Apply number formatting
• Enter a SUM function
• Copy formula using AutoFill
• Insert a pie chart
• Apply a chart Quick Layout
• Move a chart
• Insert a column chart
• Switch the row/column in a column chart
• Change the chart title
• Apply a chart Quick Style
• Show chart data labels
• Preview how a worksheet will look when printed
• Change worksheet orientation
• Change the print margins
• Scale a worksheet for printing
• Change the color of a worksheet tab
• Apply a column width
• Calculate a loan payment with PMT
• Enter a simple formula using multiplication
• Enter a simple formula using subtraction
• Add cell borders
• Create a formula referencing cells in another worksheet
• Enter an AVERAGE function
• Use the IF function
• Hide a worksheet
• Apply date formats
• Apply Top Ten conditional formatting
• Use an absolute reference in a formula
• Name a range of cells
• Use a named range in a formula
• Use the MIN function in a formula
• Use the MAX function in a formula
• Wrap text
• Analyze data with Goal Seek
• Use the VLOOKUP function in a formula
• Convert data into a table
• Apply a table Quick Style
• Use the table Total row
• Sort data in a table
• Filter data in a table
• Create a PivotTable
• Create a PivotChart
• Unhide a worksheet
• Important: Download the resource files needed for this project from the Resources link. If the files download in a zipped folder, make sure to extract the files after downloading the resources zipped folder. Visit the SIMnet instant help for step-by-step instruction.
• Open the start file EX2019-Capstone-Level3.
  Note: If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the worksheet so you can modify it

The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it 

Copy the OldTownStore worksheet from the OldTownSales workbook (downloaded from the Resources link) to the capstone project. 

Open the Excel file OldTownSales. 

Copy the worksheet OldTownStore. In the Move or Copy dialog, be sure to check the Create a copy check box and select your capstone project Excel file from the To book drop-down list. Make the correct selection to ensure the copied worksheet will appear at the end after the TysonsStore2018 worksheet in your capstone workbook.

1. Close the OldTownSales workbook when you have successfully copied the OldTownStore worksheet to the capstone workbook.

Before continuing, verify that you are working in the capstone project that you downloaded and not the OldTownSalesworkbook that you downloaded from the Resources link. 

Insert a new worksheet and rename it: MobileSales

If necessary, move the MobileSales worksheet so it appears first in the workbook.

1. In the MobileSales worksheet, enter the text and sales data as shown in the table below. Check your work carefully. 

A

B

1. C

D

1. E

1

Top’t Corn Mobile Sales (July)

2

1. Truck Location

3

Farragut Square

GW

1. Georgetown

K Street

4

Old Bay

1. 2500

800

600

900

1. 5

Truffle

3200

600

1. 1200

1500

6

1. Sea Salt and Caramel

4200

1500

1400

1200

Still working with the MobileSales worksheet, format the data as follows: 

Apply the Title cell style to cell A1.

Apply the Purple fill color to cell A1. Use the first color at the right in the row of Standard colors.

Apply the White, Background 1 font color to cell A1. Use the first color at the left in the first row of Theme colors.

Merge and center the worksheet title across cells A1:E1. 

Apply the Heading 2 cell style to cell B2.

Merge and center cells B2:E2. 

Bold cells B3:E3.

Apply the Accounting Number Format with 0 digits after the decimal to cells B4:E6.

AutoFit columns A:E.

In the MobileSales worksheet, calculate total sales for each of the truck locations. 

Enter the word Total in cell A7. 

Enter a SUM function in cell B7 to calculate the total of cells B4:B6. 

Use AutoFill to copy the formula to cells C7:E7.

Apply the Total cell style to cells A7:E7.

In the MobileSales worksheet, insert a pie chart (2-D Pie) to show the Old Bay sales for the month by location. Each piece of the pie should represent the Old Bay sales for a single location.
Note: You must complete this step correctly in order to receive points for completing the next step. Check your work carefully.

Working with the pie chart you just created, modify the pie chart as follows: 

Apply the Layout 6 Quick Layout. 

Move the chart so it appears below the sales data.

In the MobileSales worksheet insert a clustered column chart (2-D Column) to show the sales for each type of popcorn for each location. Do not include the totals.
Note: You must complete this step correctly in order to receive points for completing the next step. Check your work carefully.

Working with the column chart you just created, modify the column chart as follows:

If necessary, modify the chart so each location is represented by a data series and the popcorn types are listed along the x axis. 

Change the chart title to: July Sales by Popcorn Type

Apply the Style 5 chart Quick Style.

Display the chart data labels using the Outside End option. 

If necessary, move the chart so it is next to the pie chart and the top of the charts are aligned. 

Preview how the MobileSales worksheet will look when printed, and then apply print settings to print the worksheet on a single page. Hint: If you have one of the charts selected, deselect it before previewing the worksheet. Preview the worksheet again when you are finished to check your work. 

Change the orientation so the page is wider than it is tall. 

Change the margins to the preset narrow option.

Change the printing scale so all columns will print on a single page. 

Top’t Corn is considering a new truck purchase. Calculate the monthly loan payments and total cost
of the loan. 

Insert a new worksheet between the MobileSales sheet and the OnlineSales sheets.

Name the new worksheet: TruckLoan

Change the color of the TruckLoan worksheet tab to Orange. Use the third color from the left in the row of Standard colors. 

Enter the loan terms in the TruckLoan worksheet as shown below. 

A

B

1

Price

55000

2

Interest (annual)

3%

3

Loan term (in months)

24

4

Monthly payment

AutoFit column A.

Set the width of column B to 16.

Apply the Currency number format to cell B1. Display two digits after the decimal.

Enter a formula using the PMT function in cell B4. Be sure to use a negative value for the
Pv argument.

In cell A6, type: Total payments

In cell B6, enter a formula to calculate the total paid over the life of the loan (the monthly payment amount * the number of payments). Use cell references. 

In cell A7, type: Interest paid

In cell B7, enter a formula to calculate the total interest paid over the life of the loan (the total payments – the original price of the truck). Use cell references. 

Apply borders using the Thick Outside Borders option around cells A6:B7.

In cell A9, type: Average sales

In cell B9, enter a formula to calculate the average sales per month for the truck locations. Hint: Use cells B7:E7 from the MobileSales worksheet as the function argument.

Apply the Currency number format to cell B9. Display two digits after the decimal.

In cell A10, type: Buy new truck?

In cell B10, enter a formula using the IF function to display Yes if the monthly payment for the truck loan is less than the average sales per month for the current trucks. Display
No if it is not. 

This workbook includes two worksheets for data from the Tysons store. You should only be working with the latest data from 2019. 

Hide the TysonsStore2018 worksheet.

Complete the following steps in the TysonsStore2019 worksheet:

Select cells A2:A32, and apply the Short Date date format.

Find the top ten sales items for the month. Select cells B2:D32 and use conditional formatting to apply a green fill with dark green text to the top 10 values.

In cell F2, enter a formula to calculate the daily total in dollars. Multiply the value in the Daily Total (# Sold) column by the current price per box in cell K1. Use an absolute reference where appropriate and copy the formula to cells F3:F32.

In cell G2, enter a formula using the IF function to determine whether the daily sales goal in cell K2 was met. Display yes if the value in the Daily Total ($) column is greater than or equal to the daily sales goal. Display no if it is not. Use an absolute reference where appropriate and copy the formula to cells G3:G32.

Create a named range DailyTotals for cells F2:F32. 

In cell K3, enter a formula using the named range DailyTotals to calculate the average daily
sales in dollars. 

In cell K4, enter a formula using the named range DailyTotals to find the lowest daily sales
in dollars.

In cell K5, enter a formula using the named range DailyTotals to find the highest daily sales
in dollars.

Wrap the text in cell J7. 

Use Goal Seek to find the new price per box (cell K8) to reach a new daily average sales goal of $3,000 in cell K7. Accept the solution found by Goal Seek. 

Modify cell K8 to show two places after the decimal. 

Create a named range SalesData for cells A2:G32. 

In cell K10, enter 8/19/2019 as the lookup date.

In cell K11, enter a formula using VLOOKUP to display whether or not the sales goal was met for the date listed in cell K10. Use the named range SalesData for the Table_array argument. The formula should return the value in the Sales Goal Met?column (column 7 in the data array) only when there is an exact match.

Make a copy of the OnlineSales worksheet and name it PivotData. The PivotData worksheet should be the last worksheet in the workbook.

Go to the OnlineSales worksheet and format the sales data as a table using the table style
Aqua, Table Style Light 9.

Continue working with the table on the OnlineSales worksheet and display the table Total row. 

Display the total for the Quantity column.

Remove the count from the State column.

Continue working with the table on the OnlineSales worksheet and sort the data alphabetically by values in the Item column.

Continue working with the table on the OnlineSales worksheet and filter the table to show only rows where the value in the Statecolumn is MD.

Create a PivotTable using the data in cells A3:D120 from the data in the PivotData worksheet. The PivotTable should appear on its own worksheet. Use values from the Item column as the rows and the sum of values in the Quantity column as the values.

Name the PivotTable worksheet: PivotTable It should be located to the left of the PivotData worksheet.

Insert a PivotChart on the PivotTable worksheet. Use a pie chart to represent the total quantity
for each item. If necessary, move the PivotChart to the right of the PivotTable so it does not cover the data.

This workbook includes a hidden worksheet with online sales data from the 2018 buy one get one free sale. 

Unhide the BOGOSale2018 worksheet.

Save and close the workbook.

Upload and save your project file.

Submit project for grading.

represent the total units sold by location and a column chart to represent sales by popcorn type. You will format the charts, and then set up the worksheet for printing. Next, you help Top’t Corn calculate payments for a loan and decide whether or not the purchase is a good idea. Working with the daily sales data for one of the brick-and-mortar stores, you will apply conditional formatting to find the top 10 sales dates. Y ou will also calculate the sales for each date, and the average, minimum, and maximum sales. You will use Goal Seek to find the appropriate price to reach a higher daily average sales goal. You will use VLOOKUP to look up sales data for a specific date. Finally, you will work with their online sales data to format it as an Excel table and apply sorting and filtering. You will create a PivotTable and a PivotChart from a copy of the online sales data to summarize the sales.

Skills needed to complete this project: 

Open a workbook

Copy a worksheet to another workbook

Close a workbook

Insert a worksheet

Name a worksheet

Move a worksheet

Enter text

Enter numbers

1. Edit text

Autofit a column

Apply a cell style

1. Add cell shading

Change font color

Merge and center text across cells

1. Apply bold formatting

Apply number formatting

Enter a SUM function

1. Copy formula using AutoFill

Insert a pie chart

Apply a chart Quick Layout

1. Move a chart

Insert a column chart

Switch the row/column in a column chart

1. Change the chart title

Apply a chart Quick Style

Show chart data labels

1. Preview how a worksheet will look when printed

Change worksheet orientation

Change the print margins

1. Scale a worksheet for printing

Change the color of a worksheet tab

Apply a column width

1. Calculate a loan payment with PMT

Enter a simple formula using multiplication

Enter a simple formula using subtraction

1. Add cell borders

Create a formula referencing cells in another worksheet

Enter an AVERAGE function

1. Use the IF function

Hide a worksheet

Apply date formats

1. Apply Top Ten conditional formatting

Use an absolute reference in a formula

Name a range of cells

Use a named range in a formula

1. Use the MIN function in a formula

Use the MAX function in a formula

Wrap text

1. Analyze data with Goal Seek

Use the VLOOKUP function in a formula

Convert data into a table

1. Apply a table Quick Style

Use the table Total row

Sort data in a table

1. Filter data in a table

Create a PivotTable

Create a PivotChart

Unhide a worksheet

Important: Download the resource files needed for this project from the Resources link. If the files download in a zipped folder, make sure to extract the files after downloading the resources zipped folder. Visit the SIMnet instant help for step-by-step instruction.

Open the start file EX2019-Capstone-Level3.
Note: If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the worksheet so you can modify it

1. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it 

Copy the OldTownStore worksheet from the OldTownSales workbook (downloaded from the Resources link) to the capstone project. 

1. Open the Excel file OldTownSales. 

Copy the worksheet OldTownStore. In the Move or Copy dialog, be sure to check the Create a copy check box and select your capstone project Excel file from the To book drop-down list. Make the correct selection to ensure the copied worksheet will appear at the end after the TysonsStore2018 worksheet in your capstone workbook.

Close the OldTownSales workbook when you have successfully copied the OldTownStore worksheet to the capstone workbook.

1. Before continuing, verify that you are working in the capstone project that you downloaded and not the OldTownSalesworkbook that you downloaded from the Resources link. 

Insert a new worksheet and rename it: MobileSales

If necessary, move the MobileSales worksheet so it appears first in the workbook.

1. In the MobileSales worksheet, enter the text and sales data as shown in the table below. Check your work carefully. 

A

B

C

D

1. E

1

1. Top’t Corn Mobile Sales (July)

2

Truck Location

1. 3

Farragut Square

GW

Georgetown

1. K Street

4

Old Bay

1. 2500

800

600

1. 900

5

Truffle

1. 3200

600

1200

1500

1. 6

Sea Salt and Caramel

4200

1. 1500

1400

1200

1. Still working with the MobileSales worksheet, format the data as follows: 

Apply the Title cell style to cell A1.

Apply the Purple fill color to cell A1. Use the first color at the right in the row of Standard colors.

1. Apply the White, Background 1 font color to cell A1. Use the first color at the left in the first row of Theme colors.

Merge and center the worksheet title across cells A1:E1. 

Apply the Heading 2 cell style to cell B2.

1. Merge and center cells B2:E2. 

Bold cells B3:E3.

Apply the Accounting Number Format with 0 digits after the decimal to cells B4:E6.

AutoFit columns A:E.

In the MobileSales worksheet, calculate total sales for each of the truck locations. 

1. Enter the word Total in cell A7. 

Enter a SUM function in cell B7 to calculate the total of cells B4:B6. 

Use AutoFill to copy the formula to cells C7:E7.

Apply the Total cell style to cells A7:E7.

1. In the MobileSales worksheet, insert a pie chart (2-D Pie) to show the Old Bay sales for the month by location. Each piece of the pie should represent the Old Bay sales for a single location.
   Note: You must complete this step correctly in order to receive points for completing the next step. Check your work carefully.

Working with the pie chart you just created, modify the pie chart as follows: 

Apply the Layout 6 Quick Layout. 

Move the chart so it appears below the sales data.

1. In the MobileSales worksheet insert a clustered column chart (2-D Column) to show the sales for each type of popcorn for each location. Do not include the totals.
   Note: You must complete this step correctly in order to receive points for completing the next step. Check your work carefully.

Working with the column chart you just created, modify the column chart as follows:

If necessary, modify the chart so each location is represented by a data series and the popcorn types are listed along the x axis. 

Change the chart title to: July Sales by Popcorn Type

Apply the Style 5 chart Quick Style.

Display the chart data labels using the Outside End option. 

If necessary, move the chart so it is next to the pie chart and the top of the charts are aligned. 

Preview how the MobileSales worksheet will look when printed, and then apply print settings to print the worksheet on a single page. Hint: If you have one of the charts selected, deselect it before previewing the worksheet. Preview the worksheet again when you are finished to check your work. 

Change the orientation so the page is wider than it is tall. 

Change the margins to the preset narrow option.

Change the printing scale so all columns will print on a single page. 

Top’t Corn is considering a new truck purchase. Calculate the monthly loan payments and total cost
of the loan. 

Insert a new worksheet between the MobileSales sheet and the OnlineSales sheets.

Name the new worksheet: TruckLoan

Change the color of the TruckLoan worksheet tab to Orange. Use the third color from the left in the row of Standard colors. 

Enter the loan terms in the TruckLoan worksheet as shown below. 

A

B

1

Price

55000

2

Interest (annual)

3%

3

Loan term (in months)

24

4

Monthly payment

AutoFit column A.

Set the width of column B to 16.

Apply the Currency number format to cell B1. Display two digits after the decimal.

Enter a formula using the PMT function in cell B4. Be sure to use a negative value for the
Pv argument.

In cell A6, type: Total payments

In cell B6, enter a formula to calculate the total paid over the life of the loan (the monthly payment amount * the number of payments). Use cell references. 

In cell A7, type: Interest paid

In cell B7, enter a formula to calculate the total interest paid over the life of the loan (the total payments – the original price of the truck). Use cell references. 

Apply borders using the Thick Outside Borders option around cells A6:B7.

In cell A9, type: Average sales

In cell B9, enter a formula to calculate the average sales per month for the truck locations. Hint: Use cells B7:E7 from the MobileSales worksheet as the function argument.

Apply the Currency number format to cell B9. Display two digits after the decimal.

In cell A10, type: Buy new truck?

In cell B10, enter a formula using the IF function to display Yes if the monthly payment for the truck loan is less than the average sales per month for the current trucks. Display
No if it is not. 

This workbook includes two worksheets for data from the Tysons store. You should only be working with the latest data from 2019. 

Hide the TysonsStore2018 worksheet.

Complete the following steps in the TysonsStore2019 worksheet:

Select cells A2:A32, and apply the Short Date date format.

1. Find the top ten sales items for the month. Select cells B2:D32 and use conditional formatting to apply a green fill with dark green text to the top 10 values.

In cell F2, enter a formula to calculate the daily total in dollars. Multiply the value in the Daily Total (# Sold) column by the current price per box in cell K1. Use an absolute reference where appropriate and copy the formula to cells F3:F32.

In cell G2, enter a formula using the IF function to determine whether the daily sales goal in cell K2 was met. Display yes if the value in the Daily Total ($) column is greater than or equal to the daily sales goal. Display no if it is not. Use an absolute reference where appropriate and copy the formula to cells G3:G32.

1. Create a named range DailyTotals for cells F2:F32. 

In cell K3, enter a formula using the named range DailyTotals to calculate the average daily
sales in dollars. 

In cell K4, enter a formula using the named range DailyTotals to find the lowest daily sales
in dollars.

1. In cell K5, enter a formula using the named range DailyTotals to find the highest daily sales
   in dollars.

Wrap the text in cell J7. 

Use Goal Seek to find the new price per box (cell K8) to reach a new daily average sales goal of $3,000 in cell K7. Accept the solution found by Goal Seek. 

1. Modify cell K8 to show two places after the decimal. 

Create a named range SalesData for cells A2:G32. 

In cell K10, enter 8/19/2019 as the lookup date.

In cell K11, enter a formula using VLOOKUP to display whether or not the sales goal was met for the date listed in cell K10. Use the named range SalesData for the Table_array argument. The formula should return the value in the Sales Goal Met?column (column 7 in the data array) only when there is an exact match.

Make a copy of the OnlineSales worksheet and name it PivotData. The PivotData worksheet should be the last worksheet in the workbook.

1. Go to the OnlineSales worksheet and format the sales data as a table using the table style
   Aqua, Table Style Light 9.

Continue working with the table on the OnlineSales worksheet and display the table Total row. 

Display the total for the Quantity column.

Remove the count from the State column.

1. Continue working with the table on the OnlineSales worksheet and sort the data alphabetically by values in the Item column.

Continue working with the table on the OnlineSales worksheet and filter the table to show only rows where the value in the Statecolumn is MD.

Create a PivotTable using the data in cells A3:D120 from the data in the PivotData worksheet. The PivotTable should appear on its own worksheet. Use values from the Item column as the rows and the sum of values in the Quantity column as the values.

1. Name the PivotTable worksheet: PivotTable It should be located to the left of the PivotData worksheet.

Insert a PivotChart on the PivotTable worksheet. Use a pie chart to represent the total quantity
for each item. If necessary, move the PivotChart to the right of the PivotTable so it does not cover the data.

This workbook includes a hidden worksheet with online sales data from the 2018 buy one get one free sale. 

Unhide the BOGOSale2018 worksheet.

1. Save and close the workbook.