Overview

You are a business analyst at E Corp, an e-commerce company headquartered in the United States with offices and operations in both the U.S. and Canada. To meet strategic growth objectives the company wishes to expand globally beyond North America. The CEO, John Anderson, has tasked your boss, Chief Technology Officer Bill Schmitt, with overseeing the research into which country the firm should expand into next. He has assigned you to lead the project and be responsible for reporting the analysis and findings to the company executives and board. As part of last years annual strategic analysis, youve explored this topic before. Therefore, you can add the most current years data (2016) to your previous work and conduct an analysis on which country is the best candidate for expansion. Any country to consider expanding into must meet the following criteria:

Number of current internet users must be greater than 10 million Penetration (Internet Users as a percent of Population) for the current year must be greater than 50% The selected country should be ranked within the top 10 tech ready countries You cannot choose a country where you already do business

After conducting your research and analysis, you will prepare several reports for your company to summarize your findings.

You will conduct analysis in Excel; Write a memo (Word document) addressed to your CEO detailing your recommendation for which country should be expanded into and why; Prepare a PowerPoint to present your findings to the board of directors.

Your documents should have a cohesive and professional look and feel, but the information contained within should be tailored to the audience you have created them for.

Grading

Points per step are outlined in the following pages. To receive the highest grade, you are encouraged to use the skills you have learned over the course of the semester to create cohesive and professional documents use your good judgement to determine where doing this will help communicate to your audience (your boss, your company CEO and the board of directors). Not everything needs to be enhanced to be meaningful.

Excel: graded on following directions and using formulas to extract the data to determine correct options.

Word: graded on following directions, providing a clear outline of your conclusion, and using correct spelling and grammar.

PowerPoint: graded on clarity of communication including the use of graphics and text appropriately.

Excel

Step #

Question

Points

1

Download the Excel template and open it. Do a Save As and name the file using the following convention: LastnameFirstname-internet-usage-analysis.xlsx (use your name!).

2

2

In the Excel document, create a new worksheet and name it 2016. Place the worksheet to the right of the worksheet named 2015.

Update the newly created 2016 worksheet with the 2016 data available at the following link: http://www.internetlivestats.com/internet-users-by-country/

Hint: there are several ways to do this, use the one you are most comfortable with!

Format the new data to match the data formatting on the 2014 and 2015 worksheets.

8

3

Switch to the worksheet Internet user and pop. Table. Use a VLOOKUP function in cell F2 so that it searches for the country in A2 (of current worksheet) that also appears within the 2016 worksheet and display its number of internet users. Expand the formula down to find the number of internet users for each country. (Be sure to use relative and absolute cell references as needed.)

10

4

In the cell G2, use a similar VLOOKUP function to display the population for that country. Expand the formula down to find the population for each country.

7

5

In cell J2, use a formula to calculate the % of the population that uses the internet. Use the same calculation for each country.

7

6

Select all the data and convert it to a table. Choose any of the table format styles. Filter the data using the following information:

Number of internet users in 2016 must be greater than 10 million Penetration (Internet Users as a percent of Population) for 2016 must be greater than 50%

10

7

At this point, a smaller list of countries should be visible in the worksheet Internet user and pop. Table. Cross reference these countries with the list of tech ready countries in the Tech Readiness 2016 worksheet. Highlight all of the countries that are in both lists on the Internet user and pop. Table in any shade of orange.

BONUS: You can use conditional formatting to complete Step 7. The cross-referencing can be done automatically so that only countries that appear in both tables are highlighted in the Internet user and pop. table worksheet. If you correctly complete this step using conditional formatting an additional 10 points will be awarded to your exam score.

6

8

The resulting highlighted countries should meet the statistical criteria needed for expansion consideration.

Create a new Worksheet titled Chart and Insert a 2D Clustered Column bar chart that graphs Internet Users as a % of Population for 2014, 2015 and 2016 for only the countries to consider. The vertical axis should list the percentages, and the horizontal axis should show each country for each year. Ensure that your horizontal axis is formatted to match the data headers.

Change the Chart Title to Internet Users as % of Population 2014 2016; change the style of the chart to any style and color layout you choose. Be sure that the formatting of the data on the Vertical Axis matches the type of data it represents. Add a legend to the chart that denotes the color for each years data.

Based on the results of this exercise, review the full list of criteria required for expansion and choose the country you think would be the best choice. Use that country as your recommendation for the remainder of the project.

10

Total Points - EXCEL

60

Word

Youve done the research and now have your recommendation. Its now time to prepare a memo outlining your conclusion to your CEO. Follow the instructions below and using proper memo structure state your findings and list your recommendation.

Step #

Question

Points

1

Open Word and search for a template using the term memo. Select the template Memo (elegant).

4

2

Save the document using the convention LastnameFirstname-memo.docx (using your name!).

2

3

Insert the companys logo (accessible at the link below) in the Header; align the image to the center. Resize the image so that the height is 1 https://ih0.redbubble.net/image.116583655.3559/sticker,220x200-pad,220x200,ffffff.jpg

5

4

Insert a new row above the recipients name. Merge the columns. Add the company name, format as a Header and center the text. Replace the recipients name with that of the CEOs name, the senders name with your own name, and CC section with the CTOs name. Change the date to the date you are preparing the memo, and include the subject, Expansion Country Recommendation - Country inserting the name of the country you are recommending in place of Country.

8

5

Using normal text styling, provide an explanation of your country recommendation with at least 200 words between all body paragraphs of the memo (NOT the entire memo). Include an initial paragraph providing an overview of what the memo is about, a second paragraph containing the problem (what you are trying to solve, how you are going about solving it, and which criteria you used to pick a country). A third paragraph should state your recommendation country (which country you picked) and any supporting details on how and why you came to your decision. Wrap up with one to two sentences for closing remarks.

**REMEMBER: This is a memo, not a letter. If youre not sure how these differ consider researching.

15

6

Insert an object/linked icon into the memo for the Excel Worksheet Document created previously. Have it Display as icon.

7

Total Points - WORD

41

PowerPoint

The CEO read your memo and likes your work. He has asked you to present your findings to the board of directors, in person. Given that the board of directors is not familiar with this project, you will need to prepare your presentation under the assumption that they dont have any background information on your project. Therefore, you will need to briefly discuss the task assigned to you by the CEO, the criteria you used to determine your recommendation, and why you made your recommendation.

**Remember that PowerPoint slides should be a supplement to your verbal presentation, not something for the audience to read. Include only brief, pertinent information using bullet points when appropriate. **

Question #

Question

Points Available

1

Open PowerPoint and create a new blank presentation. Apply the Ion Boardroom theme and select any color variant you like.

2

2

Save the document using the convention of LastnameFirstname-pres.pptx (replace with your name!).

2

3

The first slide should use a title slide layout, put the title as E Corp. Expansion Analysis and place your name in a subtitle text box. For the following steps, select the slide layout that you feel presents your information best; you can add graphics or formatting of your liking, unless otherwise stated in the instructions. Be sure to delete any slides not related to your presentation.

7

4

Slide 2 Overview: create an overview of what will be covered in the upcoming slides in bullet point format.

** Be consistent with the naming of these bullets. They should be very similar, if not identical to the titles in the subsequent steps. The following topics will be covered in the following slides: the purpose of the presentation/problem the company is facing, the criteria used to make your decision, analysis, and your recommendation for the company. **

6

5

Slide 3 Objective: identify the objective you were tasked with its importance to the company.

8

6

Slide 4 Criteria: Using bullets, list the criteria given/used for selecting which country to expand into.

8

7

Slide 5 Analysis: Include the filtered table on the Internet user and pop. Table worksheet from your Excel workbook using the method you are most comfortable with and results in the most professional look If your table is not easy to read on the PowerPoint slide, adjust formatting so that it is (unreadable tables will be docked points.)

10

8

Slide 6 Recommendation: using the Two Content Slide layout, list your recommended country and why you chose it on the left. Insert the chart you created in your Excel document on the right side.

8

Total Points - POWERPOINT

51 Excel File Link

https://docs.google.com/spreadsheets/d/1QMq7RxDfl26Sj9uF1lI4PhSDJyOVQbIQQKUkrM8PV4M/edit?usp=sharing

Please list detailed steps on how to complete this, not just give the solution.