Loading the Libraries
library(tidyverse)
library(magrittr)
library(tidyr)
library(dplyr)
library(reshape2)
library(readxl)
library(openxlsx)
library("stringr")
Loading Data
df.book <- read_xlsx("C:\\FCOE_Capital Opti\\CDR_raw.xlsx", sheet = "3)Cap Opt Data - Book Life", trim_ws = TRUE, skip=3 )
## New names:
## * `` -> ...41
## * `` -> ...46
## * `` -> ...47
## * `` -> ...48
df.book.truncate <-df.book[,c(1:4,7,10,19,29)] %>% filter(Company == "1500: FLORIDA POWER & LIGHT CO")#column 29 is 2021
df.tax <- read_xlsx("C:\\FCOE_Capital Opti\\CDR_raw.xlsx", sheet = "4)Cap Opt Data - Tax Life", trim_ws = TRUE, skip = 3)
df.tax.truncate <-df.tax[,c(1:3,5)] %>% filter(Company == "1500: FLORIDA POWER & LIGHT CO")
df.depr <- read_xlsx("C:\\FCOE_Capital Opti\\CDR_raw.xlsx", sheet = "5) Distribution Allocation %",
range = cell_rows(4:28))
## New names:
## * `Jan - 2020` -> `Jan - 2020...3`
## * `Jan - 2020` -> `Jan - 2020...13`
#removing NAs
df.depr1<-df.depr %>% na.omit() %>% select("vlookup Key","Distribution Allocation %","Jan - 2021")#select column of the month
head(df.book.truncate )
## # A tibble: 6 x 8
## Company `Depr Group` `WBS L4` `WBS L4: Busine~ `WBS L4: Depr F~
## <chr> <chr> <chr> <chr> <chr>
## 1 1500: ~ 001FCST: Mi~ MOD003:~ A01: Base Y: Depreciable
## 2 1500: ~ 001FCST: Mi~ MOD010:~ A01: Base Y: Depreciable
## 3 1500: ~ 001FCST: Mi~ UCOR.00~ A01: Base Y: Depreciable
## 4 1500: ~ 001FCST: Mi~ UCOR.00~ A01: Base Y: Depreciable
## 5 1500: ~ 001FCST: Mi~ UENC.00~ A01: Base Y: Depreciable
## 6 1500: ~ 001FCST: Mi~ UPGD.00~ A01: Base Y: Depreciable
## # ... with 3 more variables: `WBS L4: FERC Function` <chr>, `WBS L4: Plant
## # Site/Plant Unit` <chr>, `2021` <dbl>
Data Wrangling
## Ferc Plant Plant.Site Depreciation Currency Depr.Group
## 1 6 # 994 Y USD 186FCST: Distribution 362
## 2 6 # 994 Y USD 186FCST: Distribution 362
## 3 6 # 994 Y USD 186FCST: Distribution 362
## 4 6 # 994 Y USD 186FCST: Distribution 362
## 5 6 # 994 Y USD 186FCST: Distribution 362
## 6 6 # 994 Y USD 186FCST: Distribution 362
## Book.life MACRs Allocation
## 1 52.63158 20 0.926106
## 2 52.63158 20 0.926106
## 3 52.63158 20 0.926106
## 4 52.63158 20 0.926106
## 5 52.63158 20 0.926106
## 6 52.63158 20 0.926106
Pivot Table
pivot1<- df.final1 %>% group_by(Ferc,Plant,Plant.Site,Depreciation,Currency,Depr.Group) %>% summarise(Allocation.Percent=min(Allocation),Book_Life=min(Book.life),Tax_Life=min(MACRs))
head(pivot1)
## # A tibble: 6 x 9
## # Groups: Ferc, Plant, Plant.Site, Depreciation, Currency [2]
## Ferc Plant Plant.Site Depreciation Currency Depr.Group Allocation.Perc~
## <dbl> <chr> <dbl> <chr> <chr> <chr> <dbl>
## 1 0 # 0 N USD 216FCST: ~ 1
## 2 0 # 0 Y USD 040FCST: ~ 1
## 3 0 # 0 Y USD 076FCST: ~ 1
## 4 0 # 0 Y USD 290FCST: ~ 1
## 5 0 # 0 Y USD 586FCST: ~ 1
## 6 0 # 0 Y USD 800FCST: ~ 1
## # ... with 2 more variables: Book_Life <dbl>, Tax_Life <dbl>
Output as Excel
write.xlsx(pivot1, "BW-Dec-2020-Upload.xlsx")
## Warning in file.create(to[okay]): cannot create file 'BW-Dec-2020-Upload.xlsx',
## reason 'Permission denied'
getwd()
## [1] "C:/FCOE_Capital Opti"
Checking
#Check Pivot Table
check2<- pivot1%>% filter(Depr.Group=="449FCST: 197: BABCOCK RANCH SOLAR PV.A01: Base Transmission" & Plant.Site==197)
check2
## # A tibble: 1 x 9
## # Groups: Ferc, Plant, Plant.Site, Depreciation, Currency [1]
## Ferc Plant Plant.Site Depreciation Currency Depr.Group Allocation.Perc~
## <dbl> <chr> <dbl> <chr> <chr> <chr> <dbl>
## 1 4 # 197 Y USD 449FCST: ~ 1
## # ... with 2 more variables: Book_Life <dbl>, Tax_Life <dbl>