Midterm Marketing Project Instructions_V1 (2)

Marketing Midterm Project (15%) Instructions Please Note:  You will have two (2) hours to complete the following Midterm Project. This is an individual assignment, and no collaboration is permitted.  All required exam files will be found in eConestoga as specified by your professor.  All files created by you MUST be uploaded to eConestoga as specified by your professor.  Save your work frequently! Instructions: 1. Open the Start File and save as: YourName_YourCondorID_MarketingMidterm . 2. Be sure to Enable Macros , enter your name and the date, then click the Start button. ( For help with allowing macros see the Midterm instructions on eConestoga! ) 3. Navigate to the Contest Entries worksheet and apply All Borders to the range A11: N959 4. Format the header row A10:N10, using colours to match the theme of your organization like Figure 1. Figure 1 5. In cell K11 use a SUM function to calculate the total engagements in the ENGAGEMENTS column by adding up the Views, Likes, and Shares. Fill the formula down in the column. 6. In cell L11 use a VLOOKUP function to determine the ENGAGEMENT LEVEL. Use K11 as your lookup_value , the Engagement Level data table in K2:L4 as your table_array , 2 as your column_index_num , and TRUE as your range_lookup . Fill the formula down in the column. 7. In cell M11 create a logical function for the ELIGIBLE FOR DRAW? column to determine whether a contest entry is eligible for the draw. If the engagement level is Poor, then the result should display “No” otherwise the result should say “Yes”. Fill the formula down in the column. 8. Apply conditional formatting to the ELIGIBLE FOR DRAW? column to highlight any instances of No with light red fill and dark red text. 9. In cell N11 ( TOTAL ELIGIBLE column) create a logical function that inserts the number one (1) if the contestant is eligible for the draw, otherwise insert a zero (0) if they are not eligible. Fill the formula down in the column. 10. Complete the following functions in the range H1:H5 a. In cell H1 use a COUNTA function to determine the number of entries in the range B11:B959. b. In cell H2 use a SUM function to total the number of views in H11:H959. c. In cell H3 use a SUM function to total the number of Likes in I11:I959. d. In cell H4 use a SUM function to total the number of Shares in J11:J959. COMP1141 Marketing Midterm P a g e | 1

e. In cell H5 use a SUM function to total the number of eligible contestants in N11:N959. COMP1141 Marketing Midterm P a g e | 2

Figure 3 Figure 4 COMP1141 Marketing Midterm P a g e | 4