library(readxl)
library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(ggplot2)
library(lubridate)
## 
## Attaching package: 'lubridate'
## The following objects are masked from 'package:base':
## 
##     date, intersect, setdiff, union
library(knitr)

1. Carregamento e Preparação dos Dados

vendas <- read_excel("Vendas2024(1).xlsx", sheet = 1)

vendas <- vendas %>%
  mutate(
    vendedor = as.factor(vendedor),
    dias = as.Date(dias),
    venda_diaria = as.numeric(venda_diaria)
  )

vendas <- vendas %>% filter(!is.na(venda_diaria))

str(vendas)
## tibble [113,202 × 5] (S3: tbl_df/tbl/data.frame)
##  $ vendedor    : Factor w/ 12 levels "101101","101102",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ dias        : Date[1:113202], format: "2018-01-01" "2018-01-01" ...
##  $ venda_diaria: num [1:113202] 372 139 354 241 123 ...
##  $ ano         : num [1:113202] 2018 2018 2018 2018 2018 ...
##  $ mes         : num [1:113202] 1 1 1 1 1 1 1 1 1 1 ...
head(vendas)
## # A tibble: 6 × 5
##   vendedor dias       venda_diaria   ano   mes
##   <fct>    <date>            <dbl> <dbl> <dbl>
## 1 101101   2018-01-01         372.  2018     1
## 2 101101   2018-01-01         139.  2018     1
## 3 101101   2018-01-01         354.  2018     1
## 4 101101   2018-01-01         241.  2018     1
## 5 101101   2018-01-01         123.  2018     1
## 6 101101   2018-01-01         165.  2018     1

2. Análise Estatística

2.1 Estatísticas por vendedor

estat_vendedor <- vendas %>%
  group_by(vendedor) %>%
  summarise(
    total_vendas = sum(venda_diaria),
    media_diaria = mean(venda_diaria),
    mediana = median(venda_diaria),
    desvio_padrao = sd(venda_diaria),
    n_registros = n()
  ) %>%
  arrange(desc(total_vendas))

kable(estat_vendedor, caption = "Estatísticas por Vendedor")
Estatísticas por Vendedor
vendedor total_vendas media_diaria mediana desvio_padrao n_registros
101102 4031176 276.0323 268.370 160.7581 14604
101101 3489519 253.9309 249.280 146.2669 13742
101103 3433923 268.7161 264.700 155.3434 12779
101105 2694622 258.6258 250.790 150.1411 10419
101104 2491491 237.4432 232.130 137.8471 10493
101108 2473665 271.4734 265.235 157.0374 9112
101106 2458056 245.8794 240.990 143.1502 9997
102111 2441308 244.6691 240.990 140.9007 9978
101107 2201441 250.2775 243.280 146.5876 8796
102112 1338667 289.5042 282.730 166.8494 4624
101109 1296194 304.0569 297.620 173.8729 4263
101110 1125154 256.0078 252.310 145.7166 4395

2. Análise Estatística

2.1 Estatísticas por vendedor

estat_vendedor <- vendas %>%
  group_by(vendedor) %>%
  summarise(
    total_vendas = sum(venda_diaria),
    media_diaria = mean(venda_diaria),
    mediana = median(venda_diaria),
    desvio_padrao = sd(venda_diaria),
    n_registros = n()
  ) %>%
  arrange(desc(total_vendas))

kable(estat_vendedor, caption = "Estatísticas por Vendedor")
Estatísticas por Vendedor
vendedor total_vendas media_diaria mediana desvio_padrao n_registros
101102 4031176 276.0323 268.370 160.7581 14604
101101 3489519 253.9309 249.280 146.2669 13742
101103 3433923 268.7161 264.700 155.3434 12779
101105 2694622 258.6258 250.790 150.1411 10419
101104 2491491 237.4432 232.130 137.8471 10493
101108 2473665 271.4734 265.235 157.0374 9112
101106 2458056 245.8794 240.990 143.1502 9997
102111 2441308 244.6691 240.990 140.9007 9978
101107 2201441 250.2775 243.280 146.5876 8796
102112 1338667 289.5042 282.730 166.8494 4624
101109 1296194 304.0569 297.620 173.8729 4263
101110 1125154 256.0078 252.310 145.7166 4395

2.2 Venda mensal total

venda_mensal_total <- vendas %>%
  group_by(mes) %>%
  summarise(total_mensal = sum(venda_diaria))

kable(venda_mensal_total, caption = "Venda Mensal Total da Empresa")
Venda Mensal Total da Empresa
mes total_mensal
1 2386425
2 1997638
3 2465693
4 2164830
5 2661291
6 2439277
7 2057659
8 2091707
9 2109516
10 2511229
11 3013742
12 3576212

2.3 Venda mensal por vendedor

venda_mensal_vendedor <- vendas %>%
  group_by(vendedor, mes) %>%
  summarise(total_mensal = sum(venda_diaria))
## `summarise()` has grouped output by 'vendedor'. You can override using the
## `.groups` argument.
kable(venda_mensal_vendedor, caption = "Venda Mensal por Vendedor")
Venda Mensal por Vendedor
vendedor mes total_mensal
101101 1 304996.55
101101 2 280841.29
101101 3 271108.23
101101 4 312580.18
101101 5 332379.50
101101 6 273624.83
101101 7 244295.36
101101 8 241060.96
101101 9 235423.53
101101 10 272452.76
101101 11 353683.41
101101 12 367072.11
101102 1 358325.87
101102 2 258428.16
101102 3 350061.41
101102 4 316714.02
101102 5 303263.64
101102 6 351696.00
101102 7 290731.31
101102 8 293787.95
101102 9 313033.75
101102 10 328450.46
101102 11 354579.10
101102 12 512104.04
101103 1 256950.60
101103 2 227085.20
101103 3 261548.28
101103 4 264556.80
101103 5 290407.44
101103 6 253482.21
101103 7 219043.61
101103 8 289967.85
101103 9 281298.37
101103 10 296370.12
101103 11 376967.19
101103 12 416245.38
101104 1 222662.16
101104 2 155017.30
101104 3 217127.72
101104 4 154917.52
101104 5 229778.76
101104 6 210482.34
101104 7 153517.12
101104 8 144273.45
101104 9 154356.88
101104 10 230839.02
101104 11 292080.27
101104 12 326438.93
101105 1 243601.59
101105 2 181129.39
101105 3 230925.68
101105 4 217487.04
101105 5 210134.02
101105 6 211313.24
101105 7 198280.47
101105 8 178660.18
101105 9 182837.38
101105 10 219010.56
101105 11 281821.85
101105 12 339420.87
101106 1 190747.81
101106 2 186249.53
101106 3 211870.26
101106 4 187609.33
101106 5 248299.20
101106 6 178133.89
101106 7 138348.33
101106 8 163860.42
101106 9 178171.05
101106 10 203824.08
101106 11 270247.30
101106 12 300695.05
101107 1 131991.95
101107 2 148701.20
101107 3 205178.07
101107 4 186903.56
101107 5 216176.32
101107 6 193094.22
101107 7 159875.93
101107 8 174730.10
101107 9 154164.84
101107 10 190023.95
101107 11 190755.82
101107 12 249844.79
101108 1 144378.98
101108 2 147176.34
101108 3 225444.00
101108 4 203459.68
101108 5 256836.88
101108 6 212237.53
101108 7 191760.85
101108 8 169119.74
101108 9 194768.33
101108 10 193896.73
101108 11 240693.66
101108 12 293892.62
101109 1 130292.65
101109 2 89180.06
101109 3 86114.55
101109 4 56328.50
101109 5 96427.52
101109 6 133369.88
101109 7 94089.19
101109 8 101625.88
101109 9 80056.90
101109 10 131671.38
101109 11 145915.46
101109 12 151122.44
101110 1 102221.04
101110 2 71676.60
101110 3 78463.27
101110 4 49210.58
101110 5 103094.09
101110 6 111804.99
101110 7 97608.66
101110 8 95514.34
101110 9 81701.94
101110 10 103513.47
101110 11 120435.98
101110 12 109909.32
102111 1 214266.61
102111 2 160480.82
102111 3 205464.53
102111 4 154996.70
102111 5 237483.46
102111 6 195677.70
102111 7 166520.44
102111 8 128257.65
102111 9 163233.04
102111 10 224139.70
102111 11 262223.83
102111 12 328563.49
102112 1 85988.78
102112 2 91672.44
102112 3 122386.60
102112 4 60065.69
102112 5 137010.19
102112 6 114360.31
102112 7 103587.34
102112 8 110848.82
102112 9 90469.73
102112 10 117036.86
102112 11 124337.65
102112 12 180902.99

3. Visualização com ggplot2

3.1 Barras ΓÇõ total por vendedor

ggplot(estat_vendedor, aes(x = vendedor, y = total_vendas)) +
  geom_bar(stat = "identity") +
  labs(title = "Total de Vendas por Vendedor",
       x = "Vendedor", y = "Total de Vendas")

## 3.2 Linhas — tendência mensal geral

ggplot(venda_mensal_total, aes(x = mes, y = total_mensal)) +
  geom_line(size = 1) +
  geom_point() +
  labs(title = "Tendência Mensal de Vendas da Empresa",
       x = "Mês", y = "Total Mensal")
## Warning: Using `size` aesthetic for lines was deprecated in ggplot2 3.4.0.
## ℹ Please use `linewidth` instead.
## This warning is displayed once every 8 hours.
## Call `lifecycle::last_lifecycle_warnings()` to see where this warning was
## generated.

3.3 Facet — vendas mensais por vendedor

ggplot(venda_mensal_vendedor, aes(x = mes, y = total_mensal)) +
  geom_line(size = 1) +
  geom_point() +
  facet_wrap(~ vendedor, scales = "free_y") +
  labs(title = "Tendência Mensal por Vendedor",
       x = "Mês", y = "Total Mensal")

3.4 Histograma vendas diárias

ggplot(vendas, aes(venda_diaria)) +
  geom_histogram(bins = 20) +
  labs(title = "Distribuição das Vendas Diárias",
       x = "Venda Diária", y = "Frequência")

3.5 Boxplot por mês

ggplot(vendas, aes(x = factor(mes), y = venda_diaria)) +
  geom_boxplot() +
  labs(title = "Boxplot das Vendas Diárias por Mês",
       x = "Mês", y = "Venda Diária")

## 3.6 Boxplot por vendedor

ggplot(vendas, aes(x = vendedor, y = venda_diaria)) +
  geom_boxplot() +
  labs(title = "Distribuição das Vendas por Vendedor",
       x = "Vendedor", y = "Venda Diária")

# 4. Previsão de Vendas (ARIMA)

vendas_ts <- ts(venda_mensal_total$total_mensal, frequency = 12)
previsao <- predict(arima(vendas_ts, order = c(1,1,1)), n.ahead = 6)
previsao
## $pred
##       Jan     Feb     Mar     Apr     May     Jun
## 2 3520988 3560881 3532063 3552881 3537842 3548706
## 
## $se
##        Jan      Feb      Mar      Apr      May      Jun
## 2 379391.7 501023.9 619828.5 705853.0 791160.0 862375.3