Data Specialist Groupon Exercise
Please be sure to read the instructions carefully. The “Background on the Dataset” section will be especially useful in performing your analyses.
At YipitData, we collect and analyze unique data sets that allow us to track various metrics on many publicly traded companies. This helps our clients, who are investors in the stock market, better understand the companies they invest in.
One of the first companies we covered was Groupon. For Groupon, the main metric we tracked is called gross billings. Every quarter, Groupon reports a metric called gross billings in their financial statements. We used our proprietary data to estimate gross billings before Groupon reported it in their financial statements, giving an edge to investors who purchased our reports.
Copyright By PowCoder代写 加微信 powcoder
Your goal is to use the attached data file to estimate Groupon’s North America gross billings by segment (Local, Goods, and Travel) for 4Q13 (the fourth quarter of 2013, or the time period from 10/1/13 – 12/31/13, inclusive). The data is based on real data we collected for Groupon in 2013, and you will need to overcome real challenges we faced back then in order to arrive at your estimate.
Groupon released their 4Q13 earnings results in February 2014. For the assignment, pretend it is January 2014 and you are creating the gross billings estimates that our clients will see before Groupon reports the results in February 2014. You may only use the materials that were available to you as of January 2014 – you cannot use any materials created afterward.
This assignment will give you a sense of the type of work that this role will include. As a Data Specialist, you will be the primary owner running or building new data products end-to-end from data ingestion or web scraping through publishing reports and sending granular data feeds to clients. You’ll also be solving data challenges similar to this one to ensure our data accuracy!
Please reach out to Michael with any questions!
yipitdata.com
Your response should include:
1. Your North America gross billings estimate by segment (Local, Goods, and Travel) for 4Q13.
2. A summary and step by step explanation of how you adjusted for the Local segment outage (described in more detail below), the rationale for those adjustments, and supporting calculations.
3. A description of the 5 most important Quality Assurance (QA) checks you performed, and any decisions or adjustments made based on those checks. For guidance on QA checks, please refer to the Key Terms below.
4. A step-by-step explanation, including the calculations you performed, of how you arrived at your final billings estimate.
5. If you plot Local Segment daily billings by start date from September 1st to the end of Q4 2013, you’ll see a drop off towards the end of December 2013, an uptrend at the end of September, and a regular pattern of variation. Please explain why these three trends are occurring based on what you know about the data and Groupon’s business model.
6. Based on the data we provided, do you think investors will interpret Groupon’s 4Q13 North America total gross billings and gross billings by segment results positively or negatively? Why might one segment be more important than the others for investors from a financial perspective?
7. An explanation of how you and investors can be confident in YipitData gross billings estimates based on the materials provided.
Please put your work in this template, which is based on the criteria above.
• https://docs.google.com/document/d/1s7t15e6qOBXusmDIrRQyIT2BC_HFd037gR0Y3h
krc18/edit?usp=sharingDirect link to the template
• Please limit your response to no more than 6 pages. The best assignments include
everything in the template, including all backup calculations, in a clear, concise fashion.
You will be graded on:
• Accuracy of your gross billings estimates by segment (Local, Goods, and Travel) for 4Q13
• Logic, explanation, and correct calculations of your Local segment outage methodology including the ability to be easily understood and easily replicated by someone who doesn’t have a strong background in data, programming, or statistics. Please do not use an overly complex methodology.
• Comprehensiveness and logic of Quality Assurance (QA) checks performed and any adjustments or decisions you made based on those checks
• Clear explanation of how you arrived at your final billings estimate
• Understanding of each of the 3 key trends in the data when plotting Local segment gross
billings by start date
• Understanding of how investors will interpret Groupon’s 4Q13 gross billings results
• Understanding of how you and investors can be confident in YipitData gross billings
• Attention to detail including correct calculations and description of data trends. This is
very important. We are looking for candidates who can produce data outputs with zero errors and who are paranoid about finding every data inaccuracy. We will double check every number in your assignment.
yipitdata.com
• Clearness of your communication. We will check that your writing is not only clear but also free of errors.
• Your assignment submission uses the template provided, and you include all backup calculation files so we can double check your work.
o https://docs.google.com/document/d/1s7t15e6qOBXusmDIrRQyIT2BC_HFd037g R0Y3hkrc18/edit?usp=sharingDirect link to the template
yipitdata.com
Methods & Deliverable:
• Please include a written description of your response using this template. Your response should be in a PDF file.
o https://docs.google.com/document/d/1s7t15e6qOBXusmDIrRQyIT2BC_HFd037g R0Y3hkrc18/edit?usp=sharingDirect link to the template
• Please include all additional files that include all of your calculations. Please perform your calculations entirely in Excel, Python, or R. You do not need to learn any new programming languages or packages to complete this assignment. You should be able to complete the entire analysis in Excel if that is your preference.
• We are not looking for an overly complicated solution in this assignment. Ultimately, this assignment’s purpose is to test your problem solving and logic abilities, not your technical or coding skills, as those will be taught to Data Specialists in our training program.
To keep the process fair, please do not share your work online. If you use an online code hosting platform, be sure to make it private.
After you submit your PDF and backup calculation files, we will review them as soon as possible. We may reach out with follow-up questions. This is a good sign as it means we want to clarify some points and better understand your logic.
yipitdata.com
Finance Overview
YipitData is the go-to data team for hundreds of the largest hedge funds, mutual funds, pension funds, private equity funds, family offices, sovereign wealth funds, and venture capital funds in the world.
Many of our clients use our data to understand how key company metrics are trending and invest in companies based on the financial performance of the company compared to investor expectations. For many of our products, we track KPIs (see below for definition) that companies disclose quarterly when they report their financial earnings. Investment banks also forecast these KPIs, and the average of the investment bank forecasts is called “consensus”. A stock price after a quarterly earnings report will generally move based on how the company performed compared to what investors were expecting heading into the earnings release.
• Key Performance Indicator (KPI): A metric that investors follow closely to understand
how a business is performing. These vary business by business. For Groupon, one KPI
is gross billings.
• Quarter: A quarter is a three-month period on a company’s financial calendar that acts
as a basis for financial reports. For this assignment, we will be focusing on the fourth
quarter of 2013 (4Q13, Q4 2013, or the time period from 10/1/13 – 12/31/13, inclusive)
• Consensus: Consensus estimates refer to the average forecasts for a company’s
reported KPIs from investment banks. When investment banks forecast a company’s KPIs, they base their estimates on management guidance, industry trends,
and historical KPIs reported by the company. For this assignment, we provide forecasts from JP Morgan, Deutsche Bank, and . You can assume they are the only banks that provide estimates for Groupon.
• Gross Profit: Gross Profit is calculated by subtracting certain costs from Gross Billings.
• Gross Profit Margin: Calculated as (Gross Profit / Gross Billings) and represents the
percentage ratio of gross billings that Groupon keeps from each sale after certain costs
are deducted.
• Quality Assurance (QA): QA is the process of ensuring data is accurate, complete, and
yipitdata.com
Groupon Business Overview
(information as of 2013, in line with the time period of the data provided)
Groupon is a global leader in local commerce, making it easy for people around the world to search and discover great businesses and merchandise. Its local commerce marketplace connects merchants to consumers by offering goods and services at a discount. Traditionally, local merchants have reached consumers through a variety of methods, including online advertising, the yellow pages, direct mail, newspaper, radio, television, and promotions. By bringing the brick and mortar world of local commerce onto the Internet and mobile devices, Groupon is creating a new way for local merchant partners to attract customers and sell goods and services. Groupon offers consumers deals on the best things to eat, see, do, and buy in 48 countries.
Groupon has started to establish a local commerce marketplace where customers can purchase discount vouchers (“Groupons”) for a variety of products and services from local, national, and online merchants that can be redeemed immediately upon purchase. The company has also expanded beyond local commerce to provide deals on consumer goods, travel, and entertainment events. This expansion has allowed Groupon to serve more merchant partners by separating its current and potential customer base, offering more relevant, targeted deals, and increasing the rate at which deals are purchased.
Groupon has global operations and breaks down its financial metrics into North America and International. We provide data only for North America.
Groupon also breaks down its financial metrics into three segments: Local, Goods, and Travel. In the Local segment, Groupon offers deals for local merchant partners across multiple categories, including food and drink, events and activities, beauty and spa, fitness, health, home and auto, shopping, and education. In the Goods segment, Groupon offers customers deals from well-known brands across multiple product lines, including electronics, sports, outdoors & fitness, toys, home, and clothing. In the Travel segment, Groupon offers offers from travel partners, including hotels, airfare, and package deals. We provide data that includes the segment for each deal.
Groupon reports several financial metrics. One of the most important and the metric you will be estimating is called gross billings. Gross billings represent the total dollar value of customer purchases of goods and services, excluding taxes and net of estimated refunds. For example, if Groupon generated $100 of total customer purchases of goods and services but $10 was refunded, Groupon would report gross billings of $90. The data we provide you also excludes taxes and is net of refunds.
yipitdata.com
Background on the Dataset
We provide you with an Excel file that contains four tabs with relevant information for this exercise.
• “Q4 2013 Raw Data”: Contains one row for each Groupon deal that was active in Groupon’s North America segment for some or all of Q4 2013 (10/1/13 – 12/31/13). The columns in this tab include:
o Deal ID: a unique identifier for each deal
o Units Sold: the number of units sold for each deal during Q4 2013
o Gross Billings: the gross billings generated for each deal during Q4 2013 o Start Date: the date that each deal started (in MM/DD/YY format)
o Deal URL: the URL of each deal from the webpage
o Segment: the segment of each deal (Local, Goods, or Travel)
• “YipitData Historical Data”: Contains YipitData estimates from previous quarters.
o This tab may be helpful in understanding to what extent YipitData estimates can
accurately estimate Groupon reported gross billings.
o Although we broke out this historical data on a monthly basis, the raw data we
provide for Q4 2013 in the “Q4 2013 Raw Data” tab does not break out gross billings by month. Additionally, we do not provide the raw data that was used to generate the historical estimates, only the Q4 2013 raw data.
• “Groupon Historical Data”: Contains Groupon reported metrics from previous quarters
• “Consensus”: Contains investment bank consensus estimates for 4Q13
YipitData collects this data by finding all deals on Groupon’s platform and then tracking the quantity sold and price information that is available on each deal webpage. Since we have the price and quantity for each deal, we can generate gross billings estimates.
Groupon offers thousands of deals on its platform, with new deals starting each day. Some deals stay active for weeks, months, or even years, while other deals are only active for a day or a few days. In the “Q4 2013 Raw Data” tab, we include the start date of each deal that was active in Q4 2013. Remember, these dates are not necessarily the date the gross billings were generated but the date that the deal launched. Since deals can be active for many days, weeks, months, or even years, they can generate gross billings on many days, not just the date that the deal started.
Our system that finds the deals on the Groupon North America website and adds them to our database broke from October 20 to October 30 2013 (inclusive) for the Local segment, so we did not add any Local segment deals that started between those dates to our database (you’ll notice this in the dataset). This means we missed all gross billings from deals that started during this period. In other words, our dataset includes zero Local deals that started from October 20 to October 30, 2013 inclusive, and 100% of all other Local deals that were active in Q4 2013. You’ll need to adjust the data to deal with this outage as you make an estimate for North America Q4 2013 gross billings by segment. You can assume that the Local segment is the only segment with an outage.
Sometimes units sold and gross billings estimates in the dataset may be negative. This is not an error as it represents refunds. As a reminder, all the data we provide in the dataset is net of
yipitdata.com
refunds. We don’t break out the data by purchases and returns. We provide only the net number, which is called gross billings and is in line with how Groupon reports the gross billings metric in their financial statements.
You may notice that units sold are often in decimals. We are employing estimation techniques behind the scenes. You can ignore the methodology behind these estimations and assume the data is correct in these instances.
yipitdata.com
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com