Instructions You are a digital marketing intern at a small footwear company located in Portland, OR. You have been asked to evaluate the financial performance of your social media advertising campaigns and make recommendations about how to allocate your digital advertising budget. Your manager gave you the following information: Online sales earn a 12% profit margin. . Your monthly social media advertising budget is $20,000 You currently allocate 30% to Facebook, 30% to Instagram, 20% to Tiktok, and 20% to Twitter Part 1: Data Analysis Download the Campaign Data file attached to the Assignment Dropbox. At a minimum, calculate the following values: Impressions Clicks Click-Thrus Transactions Conversions Revenues Profits Total Impressions Total Clicks Click-Thru Rate Total Transactions Conversion Rate Total Revenues Total Profits Cost Per 1,000 Impressions Cost Per Click Cost Per Transaction Perhaps most importantly, you should also calculate Return On Ad Spend (ROAs) expressed in both revenues and profits. You will need to add several new columns, formulas, and tabs to the spreadsheet to calculate many of these values, and you will need to consider when it's appropriate to calculate totals, averages, and rates (%). For example, average revenue per click (total revenues/total clicks) will tell you how much revenue you earn for each click (on average), but it won't tell you which social media platform generates more clicks, more revenue, or more average revenue per click. You should also use pivot tables to evaluate this data. For example, this screenshot is calculating total clicks, grouped by social media channel, and by day of the week. (Pro Tip: Notice how | used conditional formatting to highlight the highest and lowest values in each data range) Sum of Clicks Column Labels - Row Labels - Sunday Monday Tuesday Wednesday Thursday Friday Saturday (blank) Grand Total Facebook 2768 2877 3107 3058 1730 1401 2661 17602 Instagram 1623 3053 2025 2335 2518 1853 882 14289 TikTok 3094 2118 1957 2323 2021 1788 2526 15827 Twitter 2366 4168 3780 2271 1758 1387 1971 17701 (blank) Grand Total 9851 12216 10869 9987 8027 6429 8040 65419 Include all of your calculations, pivot tables, and graphs in your excel document (in other words, "show all of your work). Analyze the data and evaluate the performance of your advertising campaigns. Part 2: Executive Summary Consolidate your analysis into a 1-page executive summary. I have provided a couple examples attached to the Assignment Dropbox. You should at least answer the following questions in your executive summary: How is our social media advertising performing? Should we increase our social media advertising budget? Why/why not? Should we allocate more of our budget to a specific platform? Which one(s)? Why? . Should we allocate less of our budget to a specific platform? Which one(s)? Why? . Should we allocate more of our budget to specific days of the week? Which one(s)? Why? Should we allocate less of our budget to a specific day of the week? Which one(s)? Why? . Bonus Question: If we didn't increase our total budget, but we allocated the funds from the worst performing platform to our best performing platform (to the best performing day of the week) how would it impact our total ROAS? (Pro Tip: You will need to make a copy of the original data, modify the data based on your findings, and recalculate ROAS). Include an Appendix A: Key Performance Indicators that includes the following values: . Which platform had the highest number of clicks? What was it? . Which platform had the lowest number of clicks? What was it? . Which day of the week had the highest number of clicks? What was it? . Which day of the week had the lowest number of clicks? What was it? Which platform had the highest Click-Thru rate? What was it? Which platform had the lowest Click-Thru rate? What was it? Which day of the week had the highest Click-Thru rate? What was it? Which day of the week had the lowest Click-Thru rate? What was it? . Which platform had the highest conversion rate? What was it? . Which platform had the lowest conversion rate? What was it? Which day of the week had the highest conversion rate? What was it? Which day of the week had the lowest conversion rate? What was it? . Which platform had the highest total revenue? What was it? . Which platform had the lowest total revenue? What was it? Which day of the week had the highest total revenue? What was it? . Which day of the week had the lowest total revenue? What was it? . Which Platform had the highest Average Revenue per Transaction? What was it? Which Platform had the lowest Average Revenue per Transaction? What was it? . Which day of the week had the highest Average Revenue per Transaction? What was it? . Which day of the week had the lowest Average Revenue per Transaction? What was it? Which platform had the highest total profit? What was it? Which platform had the lowest total profit? What was it? Which day of the week had the highest total profit? What was it? Which day of the week had the lowest total profit? What was it? Which Platform had the highest Average Profit per Transaction? What was it? . Which Platform had the lowest Average Profit per Transaction? What was it? . Which day of the week had the highest Average Profit per Transaction? What was it? Which day of the week had the lowest Average Profit per Transaction? What was it? . Which platform had the highest ROAS (Revenue)? What was it? Which platform had the lowest ROAS (Revenue)? What was it? Which platform had the highest ROAS (Profit)? What was it? Which platform had the lowest ROAS (Profit)? What was it? Include as many additional appendices as you need to provide your manager with all the information she needs to validate your findings. Final Deliverables: 1. Executive Summary (1 page + appendices) 2. Excel Spreadsheet with all tabs showing all of your work. If I can't see how you got your answers, I will have to assume you did not produce them yourself. Grading: Your grade will be based on four criteria: 1. Your executive summary (based on the Learning Goals 3: Business Communication Rubric) 2. Your answers to the questions in your Executive Summary 3. Your answers to the question in Appendix A 4. Proper use of Microsoft Excel analysis tools and techniques (formulas, pivot tables, conditional formatting, and charts/graphs). Pro Tip: Don't always just paste screenshots of excel cells into your executive summary. Use graphs when they are appropriate. *Review the Learning Goal objectives and rubrics below for guidelines and expectations of this assignment Learning Goal 1: Problem Solving & Decision Making Graduates of the Business Administration Baccalaureate program will have the capacity to recognize and understand a wide range of common and novel business problems and to follow a systematic approach to solve them effectively. Learning Objectives: 1. Problem definition: Students will construct clear and specific problem statements using contextually relevant evidence. 2. Research techniques and data analysis: Students will employ in-depth information and data from relevant sources throughout the problem-solving process. 3. Solution evaluation: Students will thoroughly evaluate solutions from multiple dimensions using appropriate analytical tools. 4. Decision making: Students will make decisions based on a synthesis of research, data analysis, and evaluation of solutions. Exceeds Expectations Meets Expectations Does Not Meet Expectations Problem definition Does not construct a clear problem statement. Research techniques & data analysis Does not use relevant data or analytical tools appropriately. Clearly and thoroughly defines Sufficiently defines the the problem using most problem using most relevant contextual factors. relevant contextual factors. Organizes and synthesizes in- Uses a range of sources depth data and information and a range of analytical from a wide range of research tools. Evidence of resources. Thorough synthesis synthesis of data and of data and information. information from these sources. Thoroughly evaluates solutions Sufficiently evaluates in an elegant manner with solutions in an adequate creative consideration of manner with appropriate relevant factors. consideration of relevant factors. Solution evaluation Insufficiently evaluates solutions. Decision making Cogently presents decisions, using a rich synthesis of research, data analysis, and evaluation of solutions. Sufficiently presents decisions based on a basic synthesis of research, data analysis, and evaluation of solutions. Does not present a reasonable or appropriate decision. Learning Goal 3: Business Communication Graduates of the Business Administration Baccalaureate program will effectively communicate complex information with a variety of stakeholders to increase knowledge and achieve objectives. Learning Objectives: 1. Context, purpose and audience: Students will demonstrate a thorough consideration of context, purpose, and audience in the communication. 2. Manner of presentation: Students will present a central message in a clear, concise, and convincing manner. 3. Delivery of complex information: Students will skillfully articulate complex information in a manner that allows the message to be understood by multiple stakeholders. 4. Use of sources: Students will demonstrate a consistent use of credible, relevant sources that supports the message or credibility of the communicator. Exceeds Expectations Meets Expectations Does Not Meet Expectations Context, purpose & audience Demonstrates a thorough and creative understanding of context, audience, and purpose. Demonstrates adequate consideration of context, audience, and purpose. Does not demonstrate appropriate attention to context, audience, and purpose. Does not present the message in a clear or appropriate manner. Manner of presentation Message is skillfully delivered with a clear, concise and persuasive authority. Very professional. Message is delivered in a clear and concise manner. Demonstrates consistent use of professional communication conventions. Articulates complex information in an appropriate way that it can be understood by the targeted audience. Delivery of complex information Skillfully conveys complex information that is easily understandable by the targeted audience. Delivery engages and teaches. Does not synthesize complex information or deliver it in a manner that can be understood by the targeted audience. Does not appropriately or effectively use credible or relevant sources that support the communication. Use of sources Demonstrates a skillful use of high-quality, credible, relevant sources that enhance the communication. Demonstrates a consistent use of credible and relevant sources that support the communication. Instructions You are a digital marketing intern at a small footwear company located in Portland, OR. You have been asked to evaluate the financial performance of your social media advertising campaigns and make recommendations about how to allocate your digital advertising budget. Your manager gave you the following information: Online sales earn a 12% profit margin. . Your monthly social media advertising budget is $20,000 You currently allocate 30% to Facebook, 30% to Instagram, 20% to Tiktok, and 20% to Twitter Part 1: Data Analysis Download the Campaign Data file attached to the Assignment Dropbox. At a minimum, calculate the following values: Impressions Clicks Click-Thrus Transactions Conversions Revenues Profits Total Impressions Total Clicks Click-Thru Rate Total Transactions Conversion Rate Total Revenues Total Profits Cost Per 1,000 Impressions Cost Per Click Cost Per Transaction Perhaps most importantly, you should also calculate Return On Ad Spend (ROAs) expressed in both revenues and profits. You will need to add several new columns, formulas, and tabs to the spreadsheet to calculate many of these values, and you will need to consider when it's appropriate to calculate totals, averages, and rates (%). For example, average revenue per click (total revenues/total clicks) will tell you how much revenue you earn for each click (on average), but it won't tell you which social media platform generates more clicks, more revenue, or more average revenue per click. You should also use pivot tables to evaluate this data. For example, this screenshot is calculating total clicks, grouped by social media channel, and by day of the week. (Pro Tip: Notice how | used conditional formatting to highlight the highest and lowest values in each data range) Sum of Clicks Column Labels - Row Labels - Sunday Monday Tuesday Wednesday Thursday Friday Saturday (blank) Grand Total Facebook 2768 2877 3107 3058 1730 1401 2661 17602 Instagram 1623 3053 2025 2335 2518 1853 882 14289 TikTok 3094 2118 1957 2323 2021 1788 2526 15827 Twitter 2366 4168 3780 2271 1758 1387 1971 17701 (blank) Grand Total 9851 12216 10869 9987 8027 6429 8040 65419 Include all of your calculations, pivot tables, and graphs in your excel document (in other words, "show all of your work). Analyze the data and evaluate the performance of your advertising campaigns. Part 2: Executive Summary Consolidate your analysis into a 1-page executive summary. I have provided a couple examples attached to the Assignment Dropbox. You should at least answer the following questions in your executive summary: How is our social media advertising performing? Should we increase our social media advertising budget? Why/why not? Should we allocate more of our budget to a specific platform? Which one(s)? Why? . Should we allocate less of our budget to a specific platform? Which one(s)? Why? . Should we allocate more of our budget to specific days of the week? Which one(s)? Why? Should we allocate less of our budget to a specific day of the week? Which one(s)? Why? . Bonus Question: If we didn't increase our total budget, but we allocated the funds from the worst performing platform to our best performing platform (to the best performing day of the week) how would it impact our total ROAS? (Pro Tip: You will need to make a copy of the original data, modify the data based on your findings, and recalculate ROAS). Include an Appendix A: Key Performance Indicators that includes the following values: . Which platform had the highest number of clicks? What was it? . Which platform had the lowest number of clicks? What was it? . Which day of the week had the highest number of clicks? What was it? . Which day of the week had the lowest number of clicks? What was it? Which platform had the highest Click-Thru rate? What was it? Which platform had the lowest Click-Thru rate? What was it? Which day of the week had the highest Click-Thru rate? What was it? Which day of the week had the lowest Click-Thru rate? What was it? . Which platform had the highest conversion rate? What was it? . Which platform had the lowest conversion rate? What was it? Which day of the week had the highest conversion rate? What was it? Which day of the week had the lowest conversion rate? What was it? . Which platform had the highest total revenue? What was it? . Which platform had the lowest total revenue? What was it? Which day of the week had the highest total revenue? What was it? . Which day of the week had the lowest total revenue? What was it? . Which Platform had the highest Average Revenue per Transaction? What was it? Which Platform had the lowest Average Revenue per Transaction? What was it? . Which day of the week had the highest Average Revenue per Transaction? What was it? . Which day of the week had the lowest Average Revenue per Transaction? What was it? Which platform had the highest total profit? What was it? Which platform had the lowest total profit? What was it? Which day of the week had the highest total profit? What was it? Which day of the week had the lowest total profit? What was it? Which Platform had the highest Average Profit per Transaction? What was it? . Which Platform had the lowest Average Profit per Transaction? What was it? . Which day of the week had the highest Average Profit per Transaction? What was it? Which day of the week had the lowest Average Profit per Transaction? What was it? . Which platform had the highest ROAS (Revenue)? What was it? Which platform had the lowest ROAS (Revenue)? What was it? Which platform had the highest ROAS (Profit)? What was it? Which platform had the lowest ROAS (Profit)? What was it? Include as many additional appendices as you need to provide your manager with all the information she needs to validate your findings. Final Deliverables: 1. Executive Summary (1 page + appendices) 2. Excel Spreadsheet with all tabs showing all of your work. If I can't see how you got your answers, I will have to assume you did not produce them yourself. Grading: Your grade will be based on four criteria: 1. Your executive summary (based on the Learning Goals 3: Business Communication Rubric) 2. Your answers to the questions in your Executive Summary 3. Your answers to the question in Appendix A 4. Proper use of Microsoft Excel analysis tools and techniques (formulas, pivot tables, conditional formatting, and charts/graphs). Pro Tip: Don't always just paste screenshots of excel cells into your executive summary. Use graphs when they are appropriate. *Review the Learning Goal objectives and rubrics below for guidelines and expectations of this assignment Learning Goal 1: Problem Solving & Decision Making Graduates of the Business Administration Baccalaureate program will have the capacity to recognize and understand a wide range of common and novel business problems and to follow a systematic approach to solve them effectively. Learning Objectives: 1. Problem definition: Students will construct clear and specific problem statements using contextually relevant evidence. 2. Research techniques and data analysis: Students will employ in-depth information and data from relevant sources throughout the problem-solving process. 3. Solution evaluation: Students will thoroughly evaluate solutions from multiple dimensions using appropriate analytical tools. 4. Decision making: Students will make decisions based on a synthesis of research, data analysis, and evaluation of solutions. Exceeds Expectations Meets Expectations Does Not Meet Expectations Problem definition Does not construct a clear problem statement. Research techniques & data analysis Does not use relevant data or analytical tools appropriately. Clearly and thoroughly defines Sufficiently defines the the problem using most problem using most relevant contextual factors. relevant contextual factors. Organizes and synthesizes in- Uses a range of sources depth data and information and a range of analytical from a wide range of research tools. Evidence of resources. Thorough synthesis synthesis of data and of data and information. information from these sources. Thoroughly evaluates solutions Sufficiently evaluates in an elegant manner with solutions in an adequate creative consideration of manner with appropriate relevant factors. consideration of relevant factors. Solution evaluation Insufficiently evaluates solutions. Decision making Cogently presents decisions, using a rich synthesis of research, data analysis, and evaluation of solutions. Sufficiently presents decisions based on a basic synthesis of research, data analysis, and evaluation of solutions. Does not present a reasonable or appropriate decision. Learning Goal 3: Business Communication Graduates of the Business Administration Baccalaureate program will effectively communicate complex information with a variety of stakeholders to increase knowledge and achieve objectives. Learning Objectives: 1. Context, purpose and audience: Students will demonstrate a thorough consideration of context, purpose, and audience in the communication. 2. Manner of presentation: Students will present a central message in a clear, concise, and convincing manner. 3. Delivery of complex information: Students will skillfully articulate complex information in a manner that allows the message to be understood by multiple stakeholders. 4. Use of sources: Students will demonstrate a consistent use of credible, relevant sources that supports the message or credibility of the communicator. Exceeds Expectations Meets Expectations Does Not Meet Expectations Context, purpose & audience Demonstrates a thorough and creative understanding of context, audience, and purpose. Demonstrates adequate consideration of context, audience, and purpose. Does not demonstrate appropriate attention to context, audience, and purpose. Does not present the message in a clear or appropriate manner. Manner of presentation Message is skillfully delivered with a clear, concise and persuasive authority. Very professional. Message is delivered in a clear and concise manner. Demonstrates consistent use of professional communication conventions. Articulates complex information in an appropriate way that it can be understood by the targeted audience. Delivery of complex information Skillfully conveys complex information that is easily understandable by the targeted audience. Delivery engages and teaches. Does not synthesize complex information or deliver it in a manner that can be understood by the targeted audience. Does not appropriately or effectively use credible or relevant sources that support the communication. Use of sources Demonstrates a skillful use of high-quality, credible, relevant sources that enhance the communication. Demonstrates a consistent use of credible and relevant sources that support the communication