Sheets Business Challenge

Company challenge: Details in the data

In this activity you’ll apply what you have learned about Google Sheets to help Sherrie West and the team at On the Rise Bakery solve a business
challenge.

Task 1

Sherrie informs you that she has recently collected numbers from On the Rise stores across the globe and added them to a spreadsheet. Sherrie asks for your help. She wants to analyze the data in order to get a clearer understanding of the current and future employment situation at On the Rise. She also hopes to share her findings and work collaboratively with the On the Rise leadership team. In order to achieve these goals, Sherrie needs your help to leverage the power of Google Sheets.

Complete the following steps:

1. Let’s start by making a copy of the course spreadsheet. Click the following link On the Rise employee openings – North America – East region. Make a copy to your My Drive when prompted.

2. Rename your copy with your first and last name to read: “On the Rise employee openings_YOURNAME.”

3. Star the Google Sheet to make it easy to find.

Task 2

Sherrie thanks you and tells you that she recently received an update from On the Rise Fort Lauderdale store owner Yousef Amadi. Yousef has just uploaded a new Google Sheet with employment data from his location. He has named the Sheet “On the Rise employee openings – Ft. Lauderdale.” Sherrie requests that you add Yousef’s data to her master spreadsheet. Later, Sherrie informs you that unfortunately On the Rise store Charleston has closed permanently. She asks you to remove this store’s data from the spreadsheet.

Complete the following steps:

1. Open your Google Sheet “On the Rise employee openings_YOURNAME.”

2. Then, open On the Rise employee openings - Ft Lauderdale and copy the values found in Row 4.

3. Paste the data from Row 4 in the Ft Lauderdale Sheet into a new row, Row 21 in “On the Rise employment openings_YOURNAME.” Select Paste special and choose Values only in order to paste only the numeric values and none of the formatting from Yousef’s spreadsheet.

4. Select the row containing the Charleston data and delete it.

5. Close the “On the Rise employee openings - Ft Lauderdale” tab.

Task 3

In order to help Sherrie make “On the Rise employee openings_YOURNAME” easier to navigate, you offer to make some formatting changes. You freeze the header, merge ranges to eliminate duplication. You also wrap text, align data, add colors to differentiate each quarter, and color the borders.

Complete the following steps:

1. Freeze the header of the spreadsheet.

a. Select Row 3.

b. From the View menu, select Freeze and choose Up to Row 3.

2. Merge the ranges below. Select the cells and from the Format menu select Merge cells and choose Merge all.

a. Employee Openings C1:N1

b. Actual C2:H2

c. Forecast I2:N2

3. Select Column A. From the Format menu, select Text wrapping and choose Wrap.

4. Resize columns K, M, and N. Select the column, right-click, select Resize column, and choose Fit to data.

5. Select B1:N20. From the Format menu, select Align and choose Center and Middle.

6. Make each quarter a different color. Select the range, Fill color, and choose the following colors:

a. Q1 (C4:E20) choose light yellow 3.

b. Q2 (F4:H20) choose light yellow 2.

c. Q3 (I4:K20) choose light blue 3.

d. Q4 (L4:N20) choose light blue 2.

7. Make the cell borders dark grey.

a. Select cells A1:N20.

b. Select Borders.

c. Choose All borders.

d. Choose Dark grey 3.

Task 4

Sherrie is still having a difficult time with the data, so you offer to make some more updates to make the Sheet easier to navigate. You start with sorting the data by city A > Z. Next, you filter the entries by state. Then, you name each range after the corresponding month.

Complete the following steps:

1. Sort the data by city.

a. Select Column A.

b. Select the down arrow and choose Sort sheet A → Z.

2. Set up a filter by state.

a. Select Column B.

b. From the top menu, select Create a filter.

c. Try filtering by state. How many stores are in New York? Pennsylvania?

d. Reset your filter so that it shows all states.

3. Name a range for each month. Select the range, right-click, and select Define the named range.

a. C4:C20 “Jan”

b. D4:D20 “Feb”

c. E4:E20 “Mar”

d. F4:F20 “Apr”

e. G4:G20 “May”

f. H4:H20 “Jun”

g. I4:I20 “Jul”

h. J4:J20 “Aug”

i. K4:K20 “Sep”

j. L4:L20 “Oct”

k. M4:M20 “Nov”

l. N4:N20 “Dec”

Task 5

Next, in order to help Sherrie better understand what is happening each month and over the year as a whole, you apply two formulas: SUM and AVERAGE. You tell Sherrie that naming the ranges earlier will help simplify the formulas. You use a SUM formula to calculate the total job openings for each month, then combine all the
monthly totals to calculate the total job vacancies for the year. In order to identify which stores are experiencing the most turnover, you calculate the yearly average of job openings per store. You apply the AVERAGE formula to each On the Rise location.

Complete the following steps:

1. In cell A21, enter “Total.”

2. Calculate the total job openings per month using a SUM formula and your named ranges.

a. For January, apply the formula C21=sum(Jan)

b. For February, apply the formula D21=sum(Feb)

c. For March, apply the formula E21=sum(Mar)

d. Complete similarly for the remaining months.

3. In cell O3, enter “Average.”

4. Calculate the yearly average of job openings per store by applying the AVERAGE formula to column O.

a. For Albany, apply the formula O4 =average(C4:N4)

b. Double click on the bottom right corner of cell O4 or drag the bottom right corner of cell O4 to O20 to reveal averages for all cities.

c. Select column O, and from the toolbar, choose Decrease decimal places to round values in this column to one decimal place.

5. Bold Row 21, and Column O.

6. Select C21:O21. From the Format menu, select Align and choose Center and Middle.

7. Repeat step 6 for Column 0.

Task 6

Sherrie tells you she is soon planning to share the spreadsheet with the On the Rise leadership team. She is concerned about accidental changes to the data and would like to make sure that the current store data is protected. She’d like Thomas to be able to make changes if necessary, so you assign him as an editor for the protected range.

Complete the following steps:

1. Click the down arrow next to the name of your current sheet at the bottom of the page, choose Protect the sheet from the menu.

2. Enter a description, and select Range and choose C4:N20.

3. Select Set permissions. Choose Restrict who can edit this range and Custom.

4. Add Thomas Omar as an Editor. For the course, add a colleague’s email address but please remember to tell them that you are completing a training exercise.

5. Click Done.

6. Click Share to grant your colleague editor access to the spreadsheet.

Task 7

A few days later, Sherrie informs you that she is ready to share the spreadsheet with the On the Rise leadership team for review. She provides you the team emails and you invite each member, assigning them commenter permission only. The leadership team members access the spreadsheet and leave numerous comments. They
also assign action items for Sherrie West and Thomas Omar.

Complete the following steps:

1. Share the spreadsheet with the leadership team (choose another colleague, and let them know as before that you are undertaking a training exercise) as a Commenter. To share silently, you can uncheck the Notify people box when sharing.

Next, you will play the part of someone on the leadership team.

2. Leave at least two general comments.

3. Assign an action item to yourself or one of your collaborators.

Task 8

The next day, Sherrie reviews the feedback she has received. She notes that she will need to make a few updates to the spreadsheet and asks for your help responding to the leadership team.

Complete the following steps:

1. Select Open comment history in the top right to easily view comments.

2. Reply to one of the comments with a “thank you.”

3. Delete one of the comments.

4. Resolve a comment.

5. Mark an action item as done.

Case wrap up

Sitting at her desk, Sherrie opens her laptop and reviews the final version of her spreadsheet “On the Rise employee openings - North America - East region”. She reflects that by using Google Sheets and working collaboratively with her team, she was able to transform the numbers coming in from the stores across the North America - East region into practical insights that she and the On the Rise leadership can use to help guide their recruitment efforts. Sherrie notes that this approach has been so successful that it should be applied across the other regions so that the team can proactively support On the Rise’s human resources needs.

SUBMIT YOUR FILE

In order to submit, please click the link below. This will open up the assignment in Google Assignments.

Attach your copy of On the Rise employee openings_YOURNAME and click SUBMIT.