Value Estimate
DR
October 1, 2020
1 Baseline Projections
Assumptions Sales price = 100
Efficiency = 0.8
Cost=30
Kitcost=10
Discount Rate=0.2/12
Income tax rate = 0.3
The business will be sold for 2 million at the end of 2022
Jan 2021 - 200k
Apr - 300k
Oct - 500k
sp = 100
priceeff = 0.8
cost=30
kitcost=10
billinglist = 0.0575
disc=0.20/12
df <- data.table(month=1:26,
gr=c(0,seq(1,0,by=-0.2),rep(0,7),rep(NA,12)),
ctgr = rep(1,26))
df[,sd:=1/(gr+5)]
qty0 = 1250
df[,units:=0]
for(i in 1:14) {
if(i>1) {
grate = rnorm(1,df[i,]$gr,sd = 0)
if(grate<df[i,]$gr) {
grate = grate*0.5
}
} else {
grate = df[i,]$gr
}
qty0 = qty0 * (1+grate)
df[i,]$units=qty0
}
df[,revenue:=units*sp*priceeff]
df[,samplingcost:= units*cost]
df[,kitcost:=units*kitcost]
df[,billinglist:=billinglist*revenue]
df[,payroll:=10000]
df[,consultant:=units*sp*0.05]
df[,billinglisteqp:=ifelse(month<=4,3125,0)]
df[,equipdepr:=2500]
df[,postage:=units*7/2]
df[,othercosts:=18000]
df[,ibt:=revenue-(samplingcost+kitcost+billinglist+payroll+consultant+billinglisteqp+equipdepr+postage+othercosts)]
df[,tax:=ifelse(ibt>0,ibt*0.3,0)]
df[,pat:=ibt-tax]
df[15:26,pat:=df[14,]$pat*0.5]
df[,pat:=ifelse(month==3,pat-200000,ifelse(month==6,pat-300000,ifelse(month==12,pat-500000,pat)))]
df[,pv:=pat/(1+disc)^df$month]
tv = 2000000
df[,monthd:=as.Date("2020-10-15") %m+% months(month)]1.1 Number of Units
ggplot(df[month<=14],aes(x=monthd,y=units))+geom_line()
1.2 Revenue and Profit after Tax
t <- melt(df[,c("monthd","pat","pv")],id.vars = "monthd")
ggplot(t,aes(x=monthd,y=value,color=variable))+geom_line()
1.3 Value
print(sum(df$pv)+tv)## [1] 39290712 Simulated Projections
Month-on-month growth is allowed to vary each month. Growth rate is randomly selected from a normal distribution.
sp = 100
priceeff = 0.8
cost=30
kitcost=10
billinglist = 0.0575
disc=0.20/12
valuevec <- NA
unitpaths <- NULL
pvpaths <- NULL
for(s in 1:10) {
df <- data.table(month=1:26,
gr=c(0,seq(1,0,by=-0.2),rep(0,7),rep(NA,12)),
ctgr = rep(1,26))
df[,sd:=1/(gr+5)]
qty0 = 1250
df[,units:=0]
for(i in 1:14) {
if(i>1) {
grate = rnorm(1,df[i,]$gr,sd = df[i,]$sd)
if(grate<df[i,]$gr) {
grate = grate*0.5
}
} else {
grate = df[i,]$gr
}
qty0 = qty0 * (1+grate)
df[i,]$units=qty0
}
df[,revenue:=units*sp*priceeff]
df[,samplingcost:= units*cost]
df[,kitcost:=units*kitcost]
df[,billinglist:=billinglist*revenue]
df[,payroll:=10000]
df[,consultant:=units*sp*0.05]
df[,billinglisteqp:=ifelse(month<=4,3125,0)]
df[,equipdepr:=2500]
df[,postage:=units*7/2]
df[,othercosts:=18000]
df[,ibt:=revenue-(samplingcost+kitcost+billinglist+payroll+consultant+billinglisteqp+equipdepr+postage+othercosts)]
df[,tax:=ifelse(ibt>0,ibt*0.3,0)]
df[,pat:=ibt-tax]
df[15:26,pat:=df[14,]$pat*0.5]
df[,pat:=ifelse(month==3,pat-200000,ifelse(month==6,pat-300000,ifelse(month==12,pat-500000,pat)))]
df[,pv:=pat/(1+disc)^df$month]
tv = 2000000#min(df[nrow(df)]$pv/disc,1000000)
unitpaths <- rbind(unitpaths,t(df$units))
pvpaths <- rbind(pvpaths,t(df$pv))
value <- sum(df$pv)+tv
valuevec <- c(valuevec,value)
# write.table(t(df$pat),file="covidproj.csv",append = T,col.names = F,row.names = F)
}2.1 Number of Units
unitpaths <- t(unitpaths)
unitpaths <- data.table(unitpaths)
unitpaths <- unitpaths[1:14]
unitpaths[,month:=.I]
unitpaths[,month:=as.Date("2020-10-15") %m+% months(month)]
unitpaths <- melt(unitpaths,id.vars = "month")
ggplot(unitpaths,aes(x=month,y=value,color=variable))+geom_line()
2.2 Present Value
pvpaths <- t(pvpaths)
pvpaths <- data.table(pvpaths)
pvpaths[,month:=.I]
pvpaths[,month:=as.Date("2020-10-15") %m+% months(month)]
pvpaths <- melt(pvpaths,id.vars = "month")
ggplot(pvpaths,aes(x=month,y=value,color=variable))+geom_line()
2.3 Valuation Distribution
hist(valuevec)
3 Full Simulation 10000 scenarios
sp = 100
priceeff = 0.8
cost=30
kitcost=10
billinglist = 0.0575
disc=0.20/12
valuevec <- NA
for(s in 1:10000) {
df <- data.table(month=1:26,
gr=c(0,seq(1,0,by=-0.2),rep(0,7),rep(NA,12)),
ctgr = rep(1,26))
df[,sd:=1/(gr+5)]
qty0 = 1250
df[,units:=0]
for(i in 1:14) {
if(i>1) {
grate = rnorm(1,df[i,]$gr,sd = df[i,]$sd)
if(grate<df[i,]$gr) {
grate = grate*0.5
}
} else {
grate = df[i,]$gr
}
qty0 = qty0 * (1+grate)
df[i,]$units=qty0
}
df[,revenue:=units*sp*priceeff]
df[,samplingcost:= units*cost]
df[,kitcost:=units*kitcost]
df[,billinglist:=billinglist*revenue]
df[,payroll:=10000]
df[,consultant:=units*sp*0.05]
df[,billinglisteqp:=ifelse(month<=4,3125,0)]
df[,equipdepr:=2500]
df[,postage:=units*7/2]
df[,othercosts:=18000]
df[,ibt:=revenue-(samplingcost+kitcost+billinglist+payroll+consultant+billinglisteqp+equipdepr+postage+othercosts)]
df[,tax:=ifelse(ibt>0,ibt*0.3,0)]
df[,pat:=ibt-tax]
df[15:26,pat:=df[14,]$pat*0.5]
df[,pat:=ifelse(month==3,pat-200000,ifelse(month==6,pat-300000,ifelse(month==12,pat-500000,pat)))]
df[,pv:=pat/(1+disc)^df$month]
tv = 2000000#min(df[nrow(df)]$pv/disc,1000000)
# unitpaths <- rbind(unitpaths,t(df$units))
# pvpaths <- rbind(pvpaths,t(df$pv))
value <- sum(df$pv)+tv
valuevec <- c(valuevec,value)
# write.table(t(df$pat),file="covidproj.csv",append = T,col.names = F,row.names = F)
}
hist(valuevec)
summary(valuevec)## Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
## 1355072 2546801 3188742 3485972 4108188 15537805 1