Understanding the Problem and Working with Historical Sales Data and Budgets
Welcome to lesson 2 of Wink Reports for Data Analysts. In this lesson, we will start working on a real-world problem that involves creating a forecasting report so that the stock operations team knows when to order new stock.
Before jumping in and building things, we need to understand what we are after. We need to use historical sales data to work out how much of each product that each customer ordered in the previous year, and use that information to portion the total monthly budget for each customer per product.
To get started, we will create a budget report that shows the total dollar amount each customer is likely to spend. In this example, the sales team has created a budget for January to December for each customer. We can create a simple budget report by inputting the data into an Excel sheet.
Next, we need to look at historical sales data to work out how much of each product did each customer contribute to their sales over the last year. We can use the sales items report we worked on in the previous lesson and add the product skew and customer name to the report to see for each customer, each product in each month, how much did they sell.
Once we have this information, we can use the product pricing to convert the budget per product into a quantity so we can let the stock operations team know how many products we expect to sell. To do this, we can calculate the quantity we need by dividing the budget by the price of each product. We can then use this quantity to create a forecast and let the stock operations team know how many products they need to have in stock.
In the next few lessons, we will explore different ways to import the budget and join it with the sales data to perform the necessary calculations. Once we have that, we can work on adding in the exceptions and creating the daily report and dashboard.
Topics with time stamps:
- 00:00 - Introduction
- 00:30 - Importance of understanding report requirements
- 01:15 - Different types of reports
- 02:14 - Key questions to ask when understanding report requirements
- 03:10 - Gathering and analyzing data
- 04:17 - Designing the report
- 06:03 - Introduction to Wink Reports
- 07:00 - Creating a mockup in Excel
- 24:40 - Conclusion and summary of key takeaways
The Report Request:
Please create a forecasting report so that the stock operations team knows when to order new stock.
The sales team has created a budget for January to December for each customer. The budget shows only the total $ amount each customer is likely to spend.
We want to use historical sales data to work out how much of each sku each customer ordered in the previous year, and use that information to apportion the total monthly budget for each customer per sku.
Once we have that information, please use the product pricing to convert that budget per sku into a quantity, so we can let the stock operations team know how many we expect to sell.
Please exclude the worst performing sku in terms of $ sold for each customer, as it might be an outlier.
Also, any products from the “Printers” product category have a 2 month lead time, so please adjust their forecast to be 2 months earlier.
Can you also create a report which runs daily and will clearly indicate if any forecasts have changed.
Then we also want a dashboard with a chart showing projected category sales, by using actuals for historical data, and forecasts for future data.