Case Study Description
Susan is a Manager of a store that sells multiple juice brands. She wants to develop a performance dashboard. Attached is that sample dataset she’s taken a from the past. The sample data contains weekly volume of sales for orange juice, and information about how much was spent on advertisements, television, temperature, petrol prices etc.
She has an annual conference coming up in less than a week to prepare for, but wanted to get this started without delay. She jotted down a few bullets to convey what she has in mind for the dashboard :
The dashboard should have 3 tabs.
I want the first tab to show how the Orange Juice Sales (Both in Gallons as well as Revenue) across time. There has to be two graphs in this tab. o One should show the sales trend for the entire time period, and I should be able to collapse or expand to see yearly, quarterly, monthly or weekly. o The other chart should show the trends of 52 weeks, and have one line per year. On this graph, I should have a filter to select just one year, or multiple years.
In the second tab, I would like to see how Orange Juice Sales (Gallons) change in relation to factors which we can control (like prices, advertisement spend, promotions, TV etc.). Each graph in this tab can either be X-Y type charts, or show how both variables vary across time. I should have filters to select one or multiple years.
In the final tab, I would like to see how the Orange Juice Sales (Gallons) change in relation to the factors outside control (temperature, petrol prices, holidays etc.). Each graph in this tab can either be X-Y type charts, or show how both variables vary across time. I should have filters to select one or multiple years.
Dashboard Results
💡
Summary of findings
- Sales of orange juice are primarily seasonal, peaking at the beginning and end of the year. This pattern may be due to the seasonal cultivation patterns of orange (roughly October - May). However this reasoning only holds under the assumption that the sales data we are analyzing pertains to juice from freshly obtained harvest (sometimes orange juice is stored and preserved for future packaging).
- Due to the rising price of orange juice, overall revenue has essentially remained steady over time even while sales volume has decreased.
- Orange juice sales volume and the price of gasoline appear to be strongly inversely related to each other. Although there is a significant association between them, it is not immediately clear whether temperature changes have an impact on the sales of orange juice. We can hypothesize that gasoline prices have an indirect causal effect on sales in terms of their effect on prices. Increased gas prices increase the cost of transportation, which raises orange juice pricing and decreases sales.
- Periods of low sales are characterized by higher discounts and increased ad spending, compared to the peak sales periods.
