Audit lab

Preparation work:

1.Download the file and save in laptop,then upload the data

sub<- readRDS(file.choose())
View(sub)

2.Translate the column names from Polish to English and open all tables

Ag<-sub$Aging#abstract the table of Aging
View(Ag)
colnames(Ag)<-c("LP","Nr","Account Name","Typ","Wn_Dr","Ma_Cr","Turnover Dr","Turnover Cr","Saldo_Balance","Current","<30","31_60","61_90","90_180","180_360",">1Y","PD","LGD","EAD")
BA<-sub$BankAcc
View(BA)
colnames(BA)<-c("Date","Konto_DR.1.35700.","Konto_CR.1.35700.","Wartosc.1.35700.","Opis.1.35700.")
CA<-sub$ChOA
View(CA)
colnames(CA)<-c("Section","Number","Account name","Class","Typ")
Jo<-sub$Journal
View(Jo)
colnames(Jo)<-c("Date","Konto_DR.1.35700.","Konto_CR.1.35700.","Wartosc.1.35700.","Opis.1.35700.")
Co<-sub$Kontr
View(Co)
colnames(Co)<-c("LP","Nr","Name","Typ","Wn_Dr","Ma_Cr","OB._WN_DR","OB._MA_CR","Saldo_Balance")
TB<-sub$TB
View(TB)
colnames(TB)<-c("Nr","Class","NR.1","Account name","Class","Subclass","Opening Balance","Debit turnover","Credit turnover","Closing balance")
ZPK<-sub$ZPK
View(ZPK)
colnames(ZPK)<-c("Section","Number","Nazwa konta","Account name","Account type")
FAR<-sub$FAR_RST_Final
View(FAR)
colnames(FAR)<-c("LP","Date","Name","City","Depreciation rate","Gross value","Acc.depreciation","Depreciation","Sold","Type","NAME")
PL<-sub$CC_PL
View(PL)
BS<-sub$CC_BS
View(BS)

Question 1: Reconcile the turnover to the T&E

Bil<-BS [c(1,3),]#Abstract total assets and intangible assets from BS
Bil
TBScb<-Bil[2,2]/Bil[1,2]#Calculate the turnover to intangible assets from BS
TBScb
TBSob<-Bil[2,3]/Bil[1,3]
TBSob
Tbs<-c(TBScb,TBSob)#Sort the data
Tbs
Inta<-TB[c(2,6),c(4,7,10)]#Abstract intangible assets and its amortization from TB
Inta
NetintaOB<-Inta[1,2]-Inta[2,2]#Calculate the net intangible assets at the end of previous financial year from TB
NetintaOB
NetintaCB<-Inta[1,3]-Inta[2,3]#Calculate the net intangible assets at the end of current financial year from TB
NetintaCB
Ttbob<-NetintaOB/Bil[1,3]#Calculate the turnover to intangible assets from TB
Ttbob
Ttbcb<-NetintaCB/Bil[1,2]
Ttbcb
Ttb<-c(Ttbcb,Ttbob)#Sort the data
Ttb
IntanDiff<- Ttb-Tbs#Calculate the difference of turnover to intangible assets from TB and BS
IntanDiff

The results of turnover to intangible assets from TB and BS at the end of current financial and at the end of previous financial year can be seen as follows:

#[1] -0.04354692 -0.02782982

Question 2: Make an analytical review of fixed assets B/O + Increases - Decreases Reclassification BZ for gross and net worth. Analytically check the accrual for the reasonableness of the depreciation charge.

FixBs<-BS[7,]#Abstract fixed assets from BS
View(FixBs)
FixTb<-TB[c(1,5),c(4,7,8,9,10)]#Abstract fixed assets and accumulated depreciation from TB
View(FixTb)
FixTbOB<-FixTb[1,2]-FixTb[2,2]#Calculate the net value of fixed assets at previous financial year
FixTbOB
FixTbCB<-FixTb[1,5]-FixTb[2,5]#Calculate the net value of fixed assets at current financial year
FixTbCB
FixDiff<-c(FixTbOB-FixBs[1,3],FixTbCB-FixBs[1,2])#Compare the results from TB and BS
FixDiff

The value of fixed assets from TB and BS at the end of previous financial and at the end of current financial year can be seen as follows:

#[1] -640895.4 -673582.5

It is clear from the TB that the Gross Value of fixed assets has increased(15930.52->22894.136) during the year and it is reasonable to assume that accumulated depreciation should also increase, but the year-end amount of accumulated depreciation(7943.673) in the TB is lower than the beginning of the year(12100.92).Therefore the current year depreciation charge is not reasonable

Question 3:Check the fixed assets register for arithmetic accuracy.

Fix03<-FAR[,c(11,2,5,6,7,8)]#Extract "NAME""Date""Depreciation rate""Gross value""Acc.Deprecition"and "Depreciation" from FAR
View(Fix03)
Fix03$Dep<-Fix03$`Depreciation rate`*Fix03$`Gross value`#Recalculate the depreciation of the fixed assets
Fix03$DepDiff<-Fix03$Dep-Fix03$Depreciation#Compare the depreciation after recalculating with the original depreciation in the table
sum_depDiff<-sum(Fix03$DepDiff)
sum_depDiff
Fix03$ClosingDate<-as.Date("2023-04-30")#Create a new column and insert the current time from Jouranl
Fix03$Date<-as.Date(Fix03$Date)#Change the format of the date
library(lubridate)
Fix03$MonthDiff<-interval(Fix03$Date,Fix03$ClosingDate)%/% months(1)#Calculate the number of month between purchase time and current time
Fix03$Acc.dep<-(Fix03$Dep/12)*Fix03$MonthDiff#Recalculate the accumulated dep.
Fix03$Acc.dep<-ifelse(Fix03$Acc.dep>Fix03$`Gross value`,Fix03$`Gross value`,Fix03$Acc.dep)#If the result is higher than gross value,then the true answer is gross value,otherwise Acc.dep
Fix03$Acc.depDiff<-(Fix03$Acc.dep-Fix03$Acc.depreciation)#Calculate the difference between original Acc.dep. and recalculated Acc.dep
sum_Acc.depDiff<-sum(Fix03$Acc.depDiff)
sum_Acc.depDiff

The difference is:

#[1] -1309377

Question 4: lnvalidate the provision for receivables. Prospective and retrospective.

Ag_subset<-Ag[Ag$Typ=="Odbiorca",]#Make an extraction based on the keyword “Odbiorca”of Typ column in Ag
View(Ag_subset)
Ag_subset$Provision<-rowSums(Ag_subset[,11:16])#calculate the sum of bad debt for each customer in the whole payment period
sumPro<-sum(Ag_subset$Provision)#calculate the sum of bad debts
sumPro
Ag_subset$EL<-Ag_subset$PD*Ag_subset$LGD*Ag_subset$EAD#calculate EL for each customer 
sumEL<-sum(Ag_subset$EL)
sumEL
ProTB<-TB[36,]#Extract provision of bad debts for trade receivable
View(ProTB)
Diff_1<-sumPro-ProTB[1,10]#Compare the provision from TB with the sum of bad debt based on aging analysis
Diff_1
Diff_2<-sumEL-ProTB[1,10]#Compare the provision from TB with the sum of bad debt based on prospective approach
Diff_2

The differences based on aging analysis and prospective approach are:

#[1] 12439568

#[1] 153575.9

The provision which stated in TB can not cover the bad debt based on aging analysis and prospective approach,so that there is a need for adjustment

Question 5：For items above the PM, make a search for unaccounted liabilities.

Question 6:Reconcile the equity position to the KRS extract.

EquTB<-TB[c(99,100,101,102,109,110,111),c(4,7,10)]#Extract rows that related to equity from TB
View(EquTB)
EquTBOB<-EquTB[1,2]+EquTB[2,2]+EquTB[3,2]+EquTB[4,2]+EquTB[5,2]-EquTB[6,2]-EquTB[7,2]#Calculated the equity ant the end of previous financial year
EquTBOB
EquTBCB<-EquTB[1,3]+EquTB[2,3]+EquTB[3,3]+EquTB[4,3]+EquTB[5,3]-EquTB[6,3]-EquTB[7,3]#Calculated the equity ant the end of current financial year
EquTBCB
Equ_01<-c(EquTBOB,EquTBCB)
View(Equ_01)
EquBS<-BS[50,]#Extract equity from BS
View(EquBS)
EquDiff<-c(EquTBOB-EquBS[1,3],EquTBCB-EquBS[1,2])#Compare the results from TB with the data in BS
EquDiff

The differences at the end of previous financial year and at the end of current financial year are:

[1] -538014.9 -553514.9

Question 7: Take a sample of fixed assets and check the calculation of depreciation.

sample(c(1:113),10,replace = FALSE)#Pick 10 number from 1 to 113 at random
Fix07<-FAR[c(77,109,111,80,99,49,75,42,89,59),c(11,2,5,6,7,8,9)]#Extract corresponding rows according to the number
View(Fix07)
Fix07$Closingdate<-as.Date("2023-04-30")#Create a new column and insert the current time from Jouranl
Fix07$Date<-as.Date(Fix07$Date)#Change the format of the date
library(lubridate)
Fix07$MonthDiff <- interval( Fix07$Date,Fix07$Closingdate)%/% months(1)#Calculate the number of month between purchase time and current time
print(Fix07)
Fix07$Dep<-Fix07$`Depreciation rate`*Fix07$`Gross value`#Recalculate the dep.
Fix07$DepDiff<-Fix07$Dep-Fix07$Depreciation#Calculate the difference between recalcuted dep. and original data
sum_DepDiff<-sum(Fix07$DepDiff)
sum_DepDiff

The difference of the the sum of dep. is 0:

[1] 0

Check the calculation of accumulated dep.

Fix07$Acc.dep<-(Fix07$Dep/12)*Fix07$MonthDiff
Fix07$Acc.dep<-ifelse(Fix07$Acc.dep>Fix07$`Gross value`,Fix07$`Gross value`,Fix07$Acc.dep)
Fix07$Acc.depDiff<-(Fix07$Acc.dep-Fix07$Acc.depreciation)
sum_Acc.depDiff<-sum(Fix07$Acc.depDiff)
sum_Acc.depDiff

The difference is:

[1] 42057.35

Question 8: Calculate the correctness of the calculation of interest on liabilities. Assume a rate of 8% pa.

Extract short-term loan and long-term loan from Jo and BA respectively

Jo_subset<-Jo[Jo$Opis.1.35700.=="Kredyty bankowe d??ugoterminowe",]
View(Jo_subset)
Jo_subset_2<-Jo[Jo$Opis.1.35700.=="Kredyty bankowe kr??tkoterminowe",]
View(Jo_subset_2)
Jo_subset<-Jo_subset[c(1:239),]
View(Jo_subset)
BA_subset<-BA[BA$Opis.1.35700.=="Kredyty bankowe d??ugoterminowe",]
View(BA_subset)
BA_subset_2<-BA[BA$Opis.1.35700.=="Kredyty bankowe kr??tkoterminowe",]
View(BA_subset_2)

Question9: Write the audit program for provisions.

Test on provisions
A provision is a liability of uncertain timing or amount that should only be recognized:
(1).An entity has a present obligation (legal or constructive) as a result of past event;
(2).It is probable(50%) that an outflow of economic resources will be required to settle the obligation;
(3).A reliable estimate can be made of the amount of the obligation
Therefore the test on provisions could based on its definition
Substantive  process
(1)Present obligation
==>Constructive obligation
a: Have they crested a valid expectation in other parties that they will discharge the obligation?
b: Discuss with the directors as to whether they have formally announced their intention,to confirm that a present obligation exists at the year end. If announced before the year end, review supporting documentation to verify that the decision has been formally announced.
c: Review evidence of the past practices, published policies and statements made
==>Legal obligation
a: Discuss with the management the nature of the issue, to ensure that a full understanding of the issue it is obtained and to  assess  whether the obligation exists.
b: Review of correspondence and other documentation to assess whether the company has a present obligation as a result of past event
(2)Probable outflow
a: Review the post year-end period to identity whether any payments have been made
b: Discuss the position with similar past provisions with the directors. Were these provisions eventually settled?
c: Review the board minutes and any company correspondence to assertion whether it is probable  the that payments will be paid by reviewing the supporting evidence
d:Write and send and enquiry letter to the company’s lawyers to obtain their views as to the probability of the claim being successful
e: Obtain a written representation from the directors to confirm the completeness of the of the provision,by confirming their view that the chances of a successful claim are remote and no provision or contingent liability is required.
(3)Reliable estimate
a: Obtain a breakdown of the calculation and cast it to ensure completeness, agree components of the calculation to supporting documentation.
b: Review any correspondence to assess whether the company has s reliable estimate of any potential payments can be made.

Question10: Write a general audit program

A general audit program mainly includes two parts:
1.Overall audit strategy: It is used to determine the scope of the audit; reporting objectives, timing; audit direction and audit resources. At this stage it is necessary to confirm, for example: 
(1) The appropriateness of the audit scope; 
(2) The reasonableness of the time budget; 
(3) The appropriateness of the selection and  
(4) The determination of the appropriate level of materiality
2.Specific audit plan: This phase consists of three main steps: risk assessment, further audit procedures and other audit procedures
(1)Risk assessment (IR): Understanding the entity and its environment. Assess the risk of material misstatement at the financial statement and assertion level
(2)Further audit procedures:=>There are two types of procedures:
a: Tests of control (CR): To test the effectiveness of the entity’s internal controls in preventing or detecting material misstatements
b: Substantive procedures (DR): To detect material misstatements. This can be done in two ways: First of all,tests of detail.For example, vouching amounts back to invoices, physical inspection of assets; Moreover, we can use analytical procedures, for example variance analysis, ratio analysis & proof in total.
(3)However, we also need to judging the needs for the test of fraud, going concern and relative-party transaction etc. by our professional.