Skip to content
0
suppressPackageStartupMessages(library(tidyverse))
df <- readr::read_csv('data/sales_data.csv', show_col_types = FALSE)
head(df)
Hidden output

Reporting on sales data

Now let's now move on to the competition and challenge.

📖 Background

You work in the accounting department of a company that sells motorcycle parts. The company operates three warehouses in a large metropolitan area.

You’ve recently learned data manipulation and plotting, and suggest helping your colleague analyze past sales data. Your colleague wants to capture sales by payment method. She also needs to know the average unit price for each product line.

💾 The data

The team assembled the following file:

The sales data has the following fields:
  • "date" - The date, from June to August 2021.
  • "warehouse" - The company operates three warehouses: North, Central, and West.
  • "client_type" - There are two types of customers: Retail and Wholesale.
  • "product_line" - Type of products purchased.
  • "quantity" - How many items were purchased.
  • "unit_price" - Price per item sold.
  • "total" - Total sale = quantity * unit_price.
  • "payment" - How the client paid: Cash, Credit card, Transfer.
head(df)

💪 Challenge

Create a report to answer your colleague's questions. Include:

  1. What are the total sales for each payment method?
  2. What is the average unit price for each product line?
  3. Create plots to visualize findings for questions 1 and 2.
  4. [Optional] Investigate further (e.g., average purchase value by client type, total purchase value by product line, etc.)
  5. Summarize your findings.

✅ Checklist before publishing into the competition

  • Rename your workspace to make it descriptive of your work. N.B. you should leave the notebook name as notebook.Rmd.
  • Remove redundant cells like the introduction to data science notebooks, so the workbook is focused on your story
  • Check that all the cells run without error.

⌛️ Time is ticking. Good luck!

What are the total sales for each payment method?

df %>%
	group_by(payment) %>%
	summarize(total_sale = round(sum(total),2)) %>%
	arrange(total_sale)

What is the average unit price for each product line?

df %>%
	group_by(product_line) %>%
	summarise(avg_unit_price = round(mean(unit_price), 2)) %>%
	arrange(avg_unit_price)

Optional Investigate further (e.g., average purchase value by client type, total purchase value by product line, etc.)

#Average purchase value by client type

df %>%
	group_by(client_type) %>%
	summarise(avg_purchase_value = mean(total))
#Total purchase value by product line

df %>%
	group_by(product_line) %>%
	summarise(total_purchase_value = sum(total)) %>%
	arrange(total_purchase_value)

Summary of Findings

When it came to our past performance with our motorcycle part sales, here are the findings that you requested along with additional findings to help better understand our clients, what products from our product line performed the best, and our total sales.

Total Sales for each payment method

Our three payment methods our clients use are cash, credit card, and transfer. We have received transfer payments the most compared to credit card and cash payments combined.

  • Cash : $19,199
  • Credit Card: $110,271
  • Transfer: $159,642
df %>%
  group_by(payment) %>%
  summarize(total_sale = sum(total)) %>%
  ggplot(aes(payment, total_sale, fill = payment)) +
  geom_col() +
  xlab("Payment Method") +
  ylab("Total Sales") +
  guides(fill = guide_legend(title = NULL))
‌
‌
‌