Case Problem $1$

Data File needed for this Case Problem: NP$\_$EX$\_5-4.$xlsx

Medicina Medical Software Imani Emeka is the Social Media Manager for Medicina Medical

Software, a tech company specializing in medical software for managing patient enrollments and

hospital staffing. Imani is working on improving the company's visibility on social media and wants

your help in maintaining a workbook tracking the company's monthly social media posts. Imani

wants metrics on the number of posts made to social media, including the number of retweets, likes,

mentions, clicks, and followers. Imani wants to determine whether the efforts to expand and improve

the company's media presence are showing results. Complete the following.

Open the NP$\_$EX$\_5-4.$xlsx workbook located in the Excel$5>$ Case$1$ folder included with your

Data Files. Save the workbook as NP$\_$EX$\_5\_$Medicina in the location specified by your instructor.

In the Documentation sheet, in the range B$3$:B$4,$ enter your name and the date.

The January through June worksheets contain the monthly social media logs$.$ In each worksheet,

change the entries in column C to hyperlinks by double$-$clicking each cell, and then pressing

ENTER. $($Note that you cannot make this change in a worksheet group.$)$ Excel will automatically

convert the cells to hyperlinks using the addresses stored into the cells. $($Note that these fictional

web addresses will not open real pages if clicked.$)$

One important social media metric is the number of engagements for each post where an indi$-$

vidual is actively engaged in the post by retweeting the post, liking the post, mentioning the

post in other forums, or clicking links within the post. In column I of the January through June

worksheets, use the SUM function to calculate the total number of retweets, likes, mentions, and

clicks. Do not include the number of followers in the total.

Another important social media metric is the Engagement Rate, which is the percentage of fol$-$

lowers that are actively engaged with the post. In column J of the January through June work$-$

sheets, calculate the Engagement Rate by dividing the number of engagements by the number of

followers for each post.

In the Metrics worksheet, calculate the following summary statistics:

a$.$ In cell C$5,$ use the COUNT function to count the number of data values in column B of the

January worksheet.

b$.$ In cell D$5,$ use the SUM function to sum the total retweets in column D of the January

worksheet.

c$.$ In cell D$6,$ use the AVERAGE function to calculate the average number of retweets in column

D of the January worksheet.

d$.$ Use AutoFill to copy the formulas from the range D$5$:D$6$ over the range D$5$:H$6.$

e$.$ In cell $15,$ use the AVERAGE function to calculate the average Engagement Rate from column I

of the January worksheet.

Repeat Step $6$ for February through June rows in the table to calculate summary statistics of the

media metrics for each month.

Calculate summary statistics of the media metrics across all months by doing the following:

a$.$ In cell C$19,$ use the COUNT function applied to column B of the January:June worksheet

group to calculate the total number of posts made over the six month period.

b$.$ In cell D$19,$ use the SUM function to sum up the total retweets in column D of January

through June worksheets.

c$.$ In cell D$20,$ use the AVERAGE function to average the number of retweets from column D of

the January through June worksheets.