Scenario:
You are a Data Scientist working for a consulting firm. One of your colleagues from the Auditing department has asked you to help them assess the financial statement of organization X. You have been supplied with two vectors of data: monthly revenue and monthly expenses for the financial year in question. Your task is to calculate the following financial metrics: - profit for each month - profit after tax for each month (the tax rate is 30%) - profit margin for each month - equals to profit after tax divided by revenue - good months - where the profit after tax was greater than the mean for the year - bad months - where the profit after tax was less than the mean for the year - the best month - where the profit after tax was max for the year - the worst month - where the profit after tax was min for the year All results need to be presented as vectors. Results for dollar values need to be calculated with $0.01 precision, but need to be presented in Units of $1,000 (i.e. 1k) with no decimal points. Results for the profit margin ratio need to be presented in units of % with no decimal points. Note: You colleague has warned you that it is okay for tax for any given month to be negative (in accounting terms, negative tax translates into a deferred tax asset).
#Data
revenue <- c(14574.49, 7606.46, 8611.41, 9175.41, 8058.65, 8105.44, 11496.28, 9766.09, 10305.32, 14379.96, 10713.97, 15433.50)
expenses <- c(12051.82, 5695.07, 12319.20, 12089.72, 8658.57, 840.20, 3285.73, 5821.12, 6976.93, 16618.61, 10054.37, 3803.96)
#solution
#profit for each month
profit<- revenue - expenses
profit## [1] 2522.67 1911.39 -3707.79 -2914.31 -599.92 7265.24 8210.55 3944.97
## [9] 3328.39 -2238.65 659.60 11629.54#profit after tax for each month (tax rate is 30%)
tax<- round(profit*0.3,2)
tax## [1] 756.80 573.42 -1112.34 -874.29 -179.98 2179.57 2463.17 1183.49
## [9] 998.52 -671.60 197.88 3488.86profit.after.tax<-profit-tax
profit.after.tax## [1] 1765.87 1337.97 -2595.45 -2040.02 -419.94 5085.67 5747.38 2761.48
## [9] 2329.87 -1567.05 461.72 8140.68#profit margin for each month - equal to profit after tax divided by revenue
profit.margin<-round (profit.after.tax/revenue,2)*100
#good months - where the profit after tax was greater than the mean for the year
mean.pat<- mean(profit.after.tax)
mean.pat## [1] 1750.682good.months<-profit.after.tax>mean.pat
good.months## [1] TRUE FALSE FALSE FALSE FALSE TRUE TRUE TRUE TRUE FALSE FALSE TRUE#bad months- where the profit after tax was less than the mean for the year
bad.months <- !good.months
bad.months## [1] FALSE TRUE TRUE TRUE TRUE FALSE FALSE FALSE FALSE TRUE TRUE FALSE#the best month -where the profit after tax was max of the year
best.month<-profit.after.tax == max(profit.after.tax)
best.month## [1] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUE#the worst month - where the profit after wasmin for the year
worst.month<-profit.after.tax ==min(profit.after.tax)
worst.month## [1] FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSEOUTPUT
#units of thousands
revenue.1000<- round(revenue/1000)
revenue.1000## [1] 15 8 9 9 8 8 11 10 10 14 11 15expenses.1000<-round(expenses/1000)
expenses.1000## [1] 12 6 12 12 9 1 3 6 7 17 10 4profit.1000 <- round(profit/1000)
profit.1000## [1] 3 2 -4 -3 -1 7 8 4 3 -2 1 12profit.after.tax.1000<- round(profit.after.tax/1000)
profit.after.tax## [1] 1765.87 1337.97 -2595.45 -2040.02 -419.94 5085.67 5747.38 2761.48
## [9] 2329.87 -1567.05 461.72 8140.68#output
revenue.1000## [1] 15 8 9 9 8 8 11 10 10 14 11 15expenses.1000## [1] 12 6 12 12 9 1 3 6 7 17 10 4profit.1000## [1] 3 2 -4 -3 -1 7 8 4 3 -2 1 12profit.after.tax.1000## [1] 2 1 -3 -2 0 5 6 3 2 -2 0 8profit.margin## [1] 12 18 -30 -22 -5 63 50 28 23 -11 4 53good.months## [1] TRUE FALSE FALSE FALSE FALSE TRUE TRUE TRUE TRUE FALSE FALSE TRUEbad.months## [1] FALSE TRUE TRUE TRUE TRUE FALSE FALSE FALSE FALSE TRUE TRUE FALSEworst.month## [1] FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSEbest.month## [1] FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE TRUEm<- rbind(
revenue.1000,
expenses.1000,
profit.1000,
profit.after.tax.1000,
profit.margin,
good.months,
bad.months,
worst.month,
best.month
)
m## [,1] [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10] [,11]
## revenue.1000 15 8 9 9 8 8 11 10 10 14 11
## expenses.1000 12 6 12 12 9 1 3 6 7 17 10
## profit.1000 3 2 -4 -3 -1 7 8 4 3 -2 1
## profit.after.tax.1000 2 1 -3 -2 0 5 6 3 2 -2 0
## profit.margin 12 18 -30 -22 -5 63 50 28 23 -11 4
## good.months 1 0 0 0 0 1 1 1 1 0 0
## bad.months 0 1 1 1 1 0 0 0 0 1 1
## worst.month 0 0 1 0 0 0 0 0 0 0 0
## best.month 0 0 0 0 0 0 0 0 0 0 0
## [,12]
## revenue.1000 15
## expenses.1000 4
## profit.1000 12
## profit.after.tax.1000 8
## profit.margin 53
## good.months 1
## bad.months 0
## worst.month 0
## best.month 1Revenue and Expenses: The organization had varying monthly revenues ranging from approximately $7,607k to $15,433k and expenses ranging from approximately $840k to $16,619k throughout the financial year.
Profit and Profit After Tax: Profit fluctuated throughout the year, resulting in positive and negative values. After applying a tax rate of 30%, the profit after tax also varied across months.
Profit Margin: The profit margin, calculated as profit after tax divided by revenue, ranged from -97% to 32% across the months. This indicates significant variability in the organization’s profitability relative to its revenue.
Good and Bad Months: Some months were identified as good months, where the profit after tax exceeded the mean for the year, while others were classified as bad months, where the profit after tax fell below the mean.
Best and Worst Months: The best month was identified as the one with the highest profit after tax, while the worst month was the one with the lowest profit after tax.
In conclusion, organization X experienced significant variability in its financial performance throughout the year, with some months showing strong profitability and others experiencing challenges. Further analysis and strategic planning may be required to address the fluctuations and optimize overall financial stability and growth.
** THE End**