PowerBI Expert for Sales Analysis

Data Insights Technical Assessment For the technical assessment, we will be using a mock scenario similar to a recent project. All data is anonymized. Scenario: I was hired by a lender to evaluate one of their portfolio companies and their ability to hit their 2021 financial forecast. I was also hired to introduce better reporting and visualization practices for increased visibility into the finances of the business. Client: Small CPG company (~$70M annual revenue) that produces snacks and baked goods. They sell private labels (think store branded, i.e., Harris Teeter) and branded products across two channels, Foodservice (i.e., restaurants) and Retail (i.e., grocery stores). Context: This is a mix of an analytical exercise to identify and quantify the risks and opportunities to the business as well as suggesting an architecture and/or toolset to automate this type of reporting moving forward. Data: The client provided three different extracts to analyze. - Historical Sales – o Description: Self-explanatory. o Data Source: Custom-built application that tracks shipped orders, built on an on- prem SQL Server database. - Budget – o Description: The sales forecast as provided by the client. o Data Source: Third-party SaaS budgeting application. API documentation/access is available for this app. - Case Weights – o Description: Mapping of item weights. o Data Source: Currently, there is no system to map and keep track of item weight data. Data is hosted on an Excel file and maintained by an operations manager on their desktop. Analytical Questions: Some relevant questions to answer that will help me give their recommendation: 1. How is the volume (lbs.) and pricing trending across customer, private/branded, channel, and item? 2. What is the distribution of revenue across all items/customers? 3. How different is the pricing per pound by item group and private/brand? 4. What are the customers that overlap with one another between Business A and Business B, and how much revenue does that make up? 5. What other insights or trends do you see? The preferred tools to analyze, clean, and visualize the data are Power BI and Python, but any other tools are welcome so long as there is a reason behind using them. Architecture Questions: Some relevant questions to answer to help propose to the client an automated way to report and analyze their sales and budget metrics: 1. Given that the board wants to continue analyzing historical sales and budgets, how would you go about automating this type of reporting? 2. Which tools / what data architecture would you recommend for this client, given that the data volume will not significantly increase over time? Why? 3. Which tools / what data architecture would you recommend for this client, given that the data volume would dramatically increase in the future (billions of rows per month for shipped orders)? Why? 4. Any other recommendations for the client in terms of automating their reporting? Timeline: You will have a week to analyze, clean, and prep the data and prepare relevant dashboards or visualizations to explain your findings. At the end of the week, we will schedule a session for you to walk us through your analysis and present your findings as well as your product (dashboards). The expectation is to present the insights you’ve uncovered from the data as well as walk through your architecture recommendations.