Shari Brown, Gemma Cannings, Kareen Ndema, Adam Tapia, Jack
Watters
# Loading FinCal package for NPV and IRR functions:
library(FinCal)
# Loading tibble, knitr, and kable for table functions:
library(tibble)
library(knitr)
library(kableExtra)
# Define applicable rows from Exhibit 6 for EBIT:
net_sales <- c(2112, 2304, 2496, 2688, 2880, 2880, 3072, 3072, 3264, 3264)
COGS <- c(-1100, -1200, -1300, -1400, -1500, -1500, -1600, -1600, -1700, -1700)
adv_expense <- c(-1100, -1050, -1000, -900, -700, -700, -730, -730, -750, -750)
start_up_costs <- c(-15, 0, 0, 0, 0, 0, 0, 0, 0, 0)
jello_sales_erosion <- c(-180, -200, -210, -220, -230, -230, -240, -240, -250, -250)
increased_overhead <- c(0, 0, 0, 0, -90, -90, -90, -90, -90, -90)
# Creating a function to calculate EBIT after tax and add tax credit for the first 8 periods
calculate_NOPAT <- function(net_sales, COGS, adv_expense, start_up_costs, jello_sales_erosion, increased_overhead, tax_rate) {
# Calculate EBIT for each period
EBIT <- net_sales + COGS + adv_expense + start_up_costs + jello_sales_erosion + increased_overhead
# Calculate EBIT after tax by applying the tax rate
NOPAT <- EBIT * (1 - tax_rate)
# Add tax credit of 1 back into EBIT after tax for the first 8 periods
tax_credit <- rep(0, length(EBIT)) # Create a vector of zeros
tax_credit[1:8] <- 1 # Set tax credit to 1 for the first 8 periods
NOPAT <- NOPAT + tax_credit
return(NOPAT)
}
# Define the tax rate
tax_rate <- 0.52
# Use the function with the provided data
NOPAT <- calculate_NOPAT(net_sales, COGS, adv_expense, start_up_costs, jello_sales_erosion, increased_overhead, tax_rate)
# Create a vector for depreciation from the cumulative depreciation values provided:
cumulative_depreciation <- c(19, 37, 54, 70, 85, 98, 110, 121, 131, 140)
# Calculate the depreciation add-back for each period:
depreciation_add_back <- c(cumulative_depreciation[1], diff(cumulative_depreciation[-length(cumulative_depreciation)]))
# Now you have the depreciation add-back that you can add to the EBIT after tax:
depreciation_add_back <- diff(c(0, cumulative_depreciation))
# Calculate the Cash Flow from Operations (CFO) by adding EBIT after tax credit and depreciation add-back
CFO <- NOPAT + depreciation_add_back
# Create a vector from total working funds to determine net working capital:
total_working_funds <- c(329, 274, 271, 264, 241, 242, 255, 255, 267, 267)
#Calculate the difference in working funds by period:
changes_in_NWC <- diff(total_working_funds)
# In the cash flow calculation, an increase in NWC (positive change) is a cash outflow and should be subtracted,
# while a decrease in NWC (negative change) is a cash inflow and should be added.
# Since diff gives the change from one period to the next, to align with the cash flow periods,
# we need to add a 0 at the beginning of the changes_in_NWC vector to reflect no change in the first period.
# Adjust changes_in_NWC to reflect the correct cash flow effect:
changes_in_NWC <- c(0, -changes_in_NWC)
# Now create a vector for the Capital Expenditures (only expressed in period 1):
CapEx <- c(200,0,0,0,0,0,0,0,0,0)
# Calculate the Free Cash Flow (FCF) for each period:
# This is done by taking the CFO and adjusting for changes in NWC and subtracting CapEx
FCF <- CFO - changes_in_NWC - CapEx
# Calculate NPV at 10% discount rate using the cash flow vector:
npv <- npv(0.10, FCF)
# Calculate IRR using the IRR function against the cash flow vector:
irr <- irr(FCF)*100
# Combine all the relevant vectors into a data frame to create a table for cross-check with excel:
financial_table <- data.frame(
Net_Sales = net_sales,
COGS = COGS,
Adv_Expense = adv_expense,
Start_Up_Costs = start_up_costs,
Jello_Sales_Erosion = jello_sales_erosion,
Increased_Overhead = increased_overhead,
NOPAT = NOPAT,
Depreciation_Add_Back = depreciation_add_back,
CFO = CFO,
Changes_in_NWC = changes_in_NWC,
CapEx = CapEx,
FCF = FCF
)
# Transpose the data frame elements:
transposed_matrix <- t(financial_table)
transposed_df <- as.data.frame(transposed_matrix)
transposed_df <- cbind("Period" = rownames(transposed_df), transposed_df)
rownames(transposed_df) <- NULL
# Using kable and kableExtra to create a table with a footnote for NPV and IRR
kable(transposed_df, "html", escape = FALSE) %>%
kable_styling(full_width = F, font_size = 11) %>%
footnote(general = paste("NPV at a 10% discount rate:", sprintf("%.2f", npv), " Calculated IRR:", sprintf("%.2f%%", irr)),
general_title = "*",
footnote_as_chunk = T,
threeparttable = T)
|
Period
|
V1
|
V2
|
V3
|
V4
|
V5
|
V6
|
V7
|
V8
|
V9
|
V10
|
|
Net_Sales
|
2112.00
|
2304.00
|
2496.00
|
2688.00
|
2880.0
|
2880.0
|
3072.00
|
3072.00
|
3264.00
|
3264.00
|
|
COGS
|
-1100.00
|
-1200.00
|
-1300.00
|
-1400.00
|
-1500.0
|
-1500.0
|
-1600.00
|
-1600.00
|
-1700.00
|
-1700.00
|
|
Adv_Expense
|
-1100.00
|
-1050.00
|
-1000.00
|
-900.00
|
-700.0
|
-700.0
|
-730.00
|
-730.00
|
-750.00
|
-750.00
|
|
Start_Up_Costs
|
-15.00
|
0.00
|
0.00
|
0.00
|
0.0
|
0.0
|
0.00
|
0.00
|
0.00
|
0.00
|
|
Jello_Sales_Erosion
|
-180.00
|
-200.00
|
-210.00
|
-220.00
|
-230.0
|
-230.0
|
-240.00
|
-240.00
|
-250.00
|
-250.00
|
|
Increased_Overhead
|
0.00
|
0.00
|
0.00
|
0.00
|
-90.0
|
-90.0
|
-90.00
|
-90.00
|
-90.00
|
-90.00
|
|
NOPAT
|
-134.84
|
-69.08
|
-5.72
|
81.64
|
173.8
|
173.8
|
198.76
|
198.76
|
227.52
|
227.52
|
|
Depreciation_Add_Back
|
19.00
|
18.00
|
17.00
|
16.00
|
15.0
|
13.0
|
12.00
|
11.00
|
10.00
|
9.00
|
|
CFO
|
-115.84
|
-51.08
|
11.28
|
97.64
|
188.8
|
186.8
|
210.76
|
209.76
|
237.52
|
236.52
|
|
Changes_in_NWC
|
0.00
|
55.00
|
3.00
|
7.00
|
23.0
|
-1.0
|
-13.00
|
0.00
|
-12.00
|
0.00
|
|
CapEx
|
200.00
|
0.00
|
0.00
|
0.00
|
0.0
|
0.0
|
0.00
|
0.00
|
0.00
|
0.00
|
|
FCF
|
-315.84
|
-106.08
|
8.28
|
90.64
|
165.8
|
187.8
|
223.76
|
209.76
|
249.52
|
236.52
|
|
* NPV at a 10%
discount rate: 343.18 Calculated IRR: 22.40%
|