MkT150 is the marquee brand and facilitator of social and competitive gaming acr

Posted: September 20th, 2023

MkT150 is the marquee brand and facilitator of social and competitive gaming across Ontario. They have recently been approved to host an event in Ontario for March 2023. With the eSports industry on the verge of rapid growth, research has shown that there is an untapped Amateur eSports market. With over 7.6 million gamers in Ontario alone. MkT150 possess the leading platform that attracts the masses and centralizes each eSport’s community.
As a Services Marketing consultant, you want to determine where in Ontario would be the most profitable place to promote and host your event.
A research company has provided you with a list of data from a survey conducted in cities across Ontario, that gathered information on the ages, genders, and annual funds spent on gaming in households, with a variety of combined income levels, game genera and their ratings.
Use the Excel template to complete the following data queries, placing your answers in the respective cells of the data sheet. Be sure to use the appropriate filtering & analysis tools as well as the formulas/functions you have been taught throughout excel, to determine the location to host the event.
1. Using Excel Total row and Sort & filter options, display the following statistics. Hint: You will need to change your data to a table format. Enter your answers/formulas in cells M2:M4 on the Customer Survey sheet. (Show your results with zero decimal places)
Average age of Undisclosed Gender in Owen Sound who spent money on Gaming.
Create a header to include your First and Last Name on the left, the answer to question (a) on the right and the current date filed in the center, on the Customer Survey worksheet.
Average age of males likely to purchase games in the city found to have the highest population.
Average age of all genders in all locations.
2. On the Customer Survey sheet, convert your table back to a data range and then create a pivot table to be placed on the Pivot Table & Chart worksheet starting at cell A1, which will display each of the following criteria. Save the information a 3D column pivot chart on the same worksheet placing the top left corner of the chart in cell G3. Set all monetary values to Accounting format.
a. City with the maximum household income total
b. Average population of all cities
c. The city where most money was spent on gaming.
3. On the Customer Survey sheet, change your date to a table and sort your data by Customer ID smallest to largest, Using and excel functions/formula determine the household income and location for the following customer ID’s. Place your answers/formulas in cells M6:M10. Be sure to use absolute cell reference when necessary. Hint: You will need to use a lookup function.
a. 60096
b. 60262
c. 60230
d. 60166
e. 80276
4. Filter the Pivot Chart you created in step 2, to display only the above 5 cities to market your new game in.
5. On the Customer Survey worksheet, use the lookup table found in K29:U32 to locate the average game rating related to the locations listed below. Place your answers/formulas in cells M12:M16 Hint: You will need to use a Lookup function.
a. Thunder Bay
b. Timiskaming Shores
c. Kitchener
d. Brantford
e. Brant
6. On the Customer Survey worksheet, use the lookup table found in K29:U32 to locate the average household income related to the locations listed below. Place your answers/formulas in cells M18:M22 Hint: You will need to use a Lookup function.
a. Pickering
b. Kitchener
c. Haldimand County
d. Quinte West
e. Niagara Falls
7. Apply the subtotal feature to your data range (A1:I301), to show each city by game rating. Hint: you must convert your data back from Table to Range. Collapse the cities to display only the subtotals and not the summary of each.
8. Remove all subtotals, and then create a formula to display the Most, Average, and Median game ratings placing your answers in cell M24:M26
9. Using the data collected, explain which city you will choose to market your game and why.
10. Insert 2 rows above your Data and create a logo for your game placing it in Cell A1:B4.
11. When you have completed all steps above, move to the End of Assignment Quiz Worksheet and fill in the answers in Column C. Be sure to read the questions carefully and manually enter your answers. (Do Not Copy and paste)