Audit lab / Substantive workshop

Valtteri Ahola - 135131

I am uploading the data to my workspace

sub<-readRDS("Substatnive.RData")

I end up with the sub object in my “sub” object.

Translate column names into English:

Aging_en<-sub$Aging
colnames(Aging_en)<-c("LP","Nr","Account Name","Type","Wn_Dr","Ma_Cr","Turnover Dr","Turnover Cr","Saldo_Balance","Current","<30","31_60","61_90","90_180","180_360",">1Y","PD","LGD","EAD")
BankAcc_en<-sub$BankAcc
colnames(BankAcc_en)<-c("Date","Account_DR.1.35700.","Account_CR.1.35700.","Value.1.35700.","Details.1.35700.")
ChOA_en<-sub$ChOA
colnames(ChOA_en)<-c("Section","Number","Account name","Class","Type")
Journal_en<-sub$Journal
colnames(Journal_en)<-c("Date","Account_DR.1.35700.","Account_CR.1.35700.","Value.1.35700.","Explanation.1.35700.")
Counter<-sub$Kontr
colnames(Counter)<-c("LP","Nr","Name","Type","Wn_Dr","Ma_Cr","OB._WN_DR","OB._MA_CR","Saldo_Balance")
TB_en<-sub$TB
colnames(TB_en)<-c("Nr","Class","NR.1","Account name","State","Subclass","Opening Balance","Debit bookings","Credit bookings","Closing balance")
ZPK_en<-sub$ZPK
colnames(ZPK_en)<-c("Section","Number","Account name","State","Account type")
CC_PL_en<-sub$CC_PL
CC_BS_en<-sub$CC_BS
FAR_RST_Final_en<-sub$FAR_RST_Final
colnames(FAR_RST_Final_en)<-c("LP","Date","Name","City","Depreciation rate","Gross value","Acc.depreciation","Depreciation","Sold","Type","NAME")

1. Reconcile the turnover to the T&E: -let’s start by checking which accounts are related to turnover and which T&E:

View(ChOA_en %>% filter(Class == 'Revenue', Type == 'Resulting'))
View(ChOA_en %>% filter(Class == 'Cost', Type == 'Resulting'))

According to chart of accounts, revenue related accounts are accounts 701, 702, 730, 740, 750 and 760. T&E account is account number 406 “business travel”

Check the bookings from journal in whose account 406 has been either in the credit or debit side to analyze the bookings related to that account:

View(Journal_en %>% filter(Account_DR.1.35700. == '406'))
View(Journal_en %>% filter(Account_CR.1.35700. == '406'))

Do the same with the accounts 701, 702, 730, 740, 750 and 760:

View(Journal_en %>% filter(Account_CR.1.35700. %in% c('701', '702', '730', '740', '750', '760')))
View(Journal_en %>% filter(Account_DR.1.35700. %in% c('701', '702', '730', '740', '750', '760')))

After this the T&E bookings should be compared to turnover to make sure that all of the expenses are related to business travelling and entertainment(like hospitality for clients). Check that there is actual invoice for every expense and that they have been approved. Look that the expenses are related to clients and thus, are related to revenue-generating events. Compare the sum of account for the previous years to find any unexpected anomalies. The T&E expenses should be related to exisiting client’s of company:

View(Counter)

however, based on the data obtained we couldn’t make the actual deep analysis since the explanations regarding each booking in the journal spreadsheet were not detailed enough to obtain full review of the bookings in the T&E and revenue accounts

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:

Retrieve the movement of fixed assets in the balance sheet:

fixed_assets_movement <- CC_BS_en[2,2] - CC_BS_en[2,3]

fixed_assets_movement

It seems that the fixed assets have increased amount 88152 compared to previous year

Next, let’s go to trial balance to calculate the opening balance, movement and closing balance of all fixed assets related accounts to find if they reconcile with the balance sheet figures:

I couldn’t identify all the accounts related to fixed assets so let’s assume that the accounts 10, 20, 30, 40 and 80 in the TB are related to fixed assets:

rows_to_sum <- c(1, 2, 3, 4, 10)

ob_fixed_assets <- sum(TB_en[rows_to_sum,7])

cb_fixed_assets <- sum(TB_en[rows_to_sum,10])

movement_of_fixed_assets <- cb_fixed_assets - ob_fixed_assets

increases_to_fx_assets <- sum(TB_en[rows_to_sum,8])
decreases_to_fx_assets <- sum(TB_en[rows_to_sum,9])

by analyzing, whether the closing balance of fixed asset accounts in trial balance equals to fixed assets amount at the end of current financial year and whether movement in fixed asset tb accounts equals to difference of fixed assets between current and previous financial year, we can analytically review if balance sheet figures are in line with tb. The difference should be traced if it exceeds the materiality limits. Increases (debit bookings to fixed asset accounts) and decreases (credit bookings to fixed asset accounts) can also be retrieved from trial balance.

From the fixed assets register, we can check the depreciation charge and compare it to the depreciation charge on the P&L statement:

depreciation_charge <- sum(FAR_RST_Final_en[,8])

Also, we can calculate the average depreciation rate from fixed asset register and then multiplying it with the accrued fixed asset from previous financial year and compare this to the depreciation charge on the financial statement and if material differences are detected, this should be noted:

average_depreciation_rate <- sum(FAR_RST_Final_en[1:113,5]) / 113

analytical_depreciation <- ob_fixed_assets * average_depreciation_rate

3. check the fixed assets register for arithmetic accuracy:

first, let’s extract the most important columns from fixed asset register to recalculate depreciation:

fixed_asset_analysis <- FAR_RST_Final_en[,c(11,2,5,6,7,8)]

then, let’s add new column which contains manually calculated depreciation (gross value * depreciation rate)

fixed_asset_analysis$check = fixed_asset_analysis$`Gross value` * fixed_asset_analysis$`Depreciation rate`

let’s compare the values of this new column to the depreciation column:

fixed_asset_analysis$difference = fixed_asset_analysis$Depreciation - fixed_asset_analysis$check

sum(fixed_asset_analysis$difference)

it seems, that the sum of the difference column is 0 which means that the depreciation in the fixed asset register is correctly calculated

next, let’s calculate the current value of fixed assets based on fixed asset register:

sum(fixed_asset_analysis$`Gross value`) - sum(fixed_asset_analysis$Depreciation)

According to this calculation, the value of fixed assets should be 14096382. The value should be the same in the balance sheet.

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

Let’s extract aging accounts of customers from aging account:

Aging_customer <- Aging_en[Aging_en$Type=="Odbiorca",]
View(Aging_customer)

let’s calculate current receivables

Aging_customer$Current_receivables <- rowSums(Aging_customer[,10])
sum(Aging_customer$Current_receivables)

current receivables are -24180153 which means that there are no receivables, instead payables in total and no provision needed to be constructed.

let’s calculate prospective receivables:

Aging_customer$Prosepctive_receivables <- rowSums(Aging_customer[,11:16])
sum(Aging_customer$Prosepctive_receivables)

they are 12474179

let’s calculate expected losses for prospective receivables:

Aging_customer$Expected_losses <- Aging_customer$PD * Aging_customer$LGD * Aging_customer$EAD

sum(Aging_customer$Expected_losses)

expected losses for prospective receivables are 188186.3

let’s compare expected losses for provisions in the balance sheet

Provisions <- CC_BS_en[59,]
sum(Provisions[,2])

sum of provisions on the balance sheet is 102714

let’s calculate the difference between expected losses and provisions on the balance sheet:

Diff <- sum(Aging_customer$Expected_losses) - sum(Provisions[,2])

the difference is 85472,34 which means that expected losses in the future are expected to be greater than provision in the balance sheet so the provisions should be increased.

6. reconcile the equity position to the KRS extract:

let’s extract equity related accounts from TB:

Equity_in_TB <- TB_en[c(99, 100, 101, 102, 103,109), c(4, 7, 10)]

According to those accounts, let’s calculate equity at the end of current financial year:

Equity_current_fy <- sum(Equity_in_TB[,3])

result is 210460.1

Also, let’s calculate equity at the end of previous financial year:

Equity_previous_fy <- sum(Equity_in_TB[,2])

Equity_previous_fy

result is 217083.6

let’s compare the results to the equity in the balance sheet:

Equity_in_BS <- CC_BS_en[c(50), c(1, 2, 3)]

BS_Equity_current_BS <- sum(Equity_in_BS[,2])

BS_Equity_current_BS #current financial year

BS_Equity_previous_BS <- sum(Equity_in_BS[,3])

BS_Equity_previous_BS #previous financial year

In the balance sheet equity at the end of current financial year is 656190 and at the end of previous financial year 644387.

let compare the BS and TB figures:

Current_year_difference <- BS_Equity_current_BS - Equity_current_fy

Current_year_difference

Previous_year_difference <- BS_Equity_previous_BS - Equity_previous_fy

Previous_year_difference

The difference between equity figures in BS and TB at the end of current financial year is: 445729.9 and at the end of previous financial year: 427303.3

So after the reconciliation of equity position between BS and TB it has been recognized a major difference which is mandatory to be investigated further.

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

let’s assume that the assurance control of the company is on relatively low level which means that to achieve low detection risk the sample size must be large. There is 113 items in fixed asset register so let’s pick at randomly 30 items to check the calculation of deprectiation:

random_numbers <- sample(1:113, 40)

random numbers picked are: 39 93 57 98 43 14 63 107 15 94 48 2 9 71 41 8 76 1 82 49 10 21 17 18 19 73 25 74 100 105 16 90 56 3 24 112 27 84 99 50

let’s calculate the depreciation of the chose items:

first, let’s extarct sample from fixed asset register

Fixed_assets_sample <- FAR_RST_Final_en %>% filter(LP %in% c(39, 93, 57, 98, 43, 14, 63, 107, 15, 94, 48, 2, 9, 71, 41, 8, 76, 1, 82, 49, 10, 21, 17, 18, 19, 73, 25, 74, 100, 105, 16, 90, 56, 3, 24, 112, 27, 84, 99, 50))

then, calculate the depreciation for each item in the sample:

Fixed_assets_sample$Calulated_sample_depreciation <- Fixed_assets_sample$`Gross value` * Fixed_assets_sample$`Depreciation rate`

calculated_depreciation <- sum(Fixed_assets_sample$Calulated_sample_depreciation)

then, calculate the original depreciation for each item:

original_depreciation <- sum(Fixed_assets_sample$Depreciation)

then, let’s check if they differ:

difference <- calculated_depreciation - original_depreciation

difference

the difference is zero which indicates that the current year depreciation for the sample is calculated correctly.

Next, let’s calculate the accumulated depreciation

first, let’s adjust new column to fixed asset sample where the last year of fy is included:

Fixed_assets_sample$current_date <- as.Date('2022-12-31', format = '%Y-%m-%d')

then, calculate the years from the purchase date of item to the end of current fy:

Fixed_assets_sample$years_since_purchase <- as.numeric(difftime(Fixed_assets_sample$current_date, Fixed_assets_sample$Date, units = "weeks")) / 52.25

then calulate the fixed asset sample yearly depreciation

Fixed_assets_sample$yearly_depreciation <- (Fixed_assets_sample$`Gross value`+ Fixed_assets_sample$Depreciation) * Fixed_assets_sample$`Depreciation rate`

then, let’s finish the acc.depreciation calculation:

Fixed_assets_sample$calculated_accum.depreciation <- Fixed_assets_sample$years_since_purchase * Fixed_assets_sample$yearly_depreciation

then, let’s sum the rows:

total_calulated_acc.depreciation <- sum(Fixed_assets_sample$calculated_accum.depreciation)

total_original_acc.depreciation <- sum(Fixed_assets_sample$Acc.depreciation)

let’s calculate the difference between calculation and original acc.deprectiation:

acc.depreciation_difference <- total_calulated_acc.depreciation - total_original_acc.depreciation

acc.depreciation_difference

according to the calculations the difference is 7908402 which indicates that there might be a significant error regarding accumulated depreciation in fixed asset register of which is important to check the calculation procedure and compare it the executed calculation done for the sample.

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

First, let’s check the development of interest-bearing financial liabilities according to TB:

let’s extract them from the rest of the TB accounts

TB_interest_bearing_liabilities <- TB_en %>% filter(Nr %in% c(17, 18))

View(TB_interest_bearing_liabilities)

let’s recalculate the closing balance based on the op balance, credit and debit turnover:

TB_interest_bearing_liabilities$recalculated_closing_balance <- TB_interest_bearing_liabilities$`Opening Balance`- TB_interest_bearing_liabilities$`Debit bookings`+ TB_interest_bearing_liabilities$`Credit bookings`

TB_interest_bearing_liabilities$difference <- TB_interest_bearing_liabilities$`Closing balance`- TB_interest_bearing_liabilities$recalculated_closing_balance

sum(TB_interest_bearing_liabilities$difference)

according to recalculation, the difference is -15168.8 which means that the closing balance in the TB is 15168.8 smaller than the recalculated one. This is due to the fact that on TB it looks like that debit bookings are increasing the liabilities account balance and credit decreasing when it should be other way around. Let’s assume that the recalculated on is correct.

let’s assume that the increases in interest bearing liabilities has happened on average, in the middle of the year and use that information to calculate the interest for liabilities of the financial year:

interest_for_fy_according_TB <- sum(TB_interest_bearing_liabilities$`Opening Balance`) * 0.08 + (sum(TB_interest_bearing_liabilities$recalculated_closing_balance) - sum(TB_interest_bearing_liabilities$`Opening Balance`)) * (0.08/2)

interest_for_fy_according_TB

according to TB, the interest for liabilities should be 8509.094

let’s compare the result to interest expenses on the P&L:

interest_for_fy_in_PL <- CC_PL_en$`Amount at the end of current financial year`[27]

interest_difference <- interest_for_fy_in_PL - interest_for_fy_according_TB

interest_difference

Difference between interest in P&L and calculated interest is -1374.094 indicating that interest for liabilities in P&L might be understated.

9. Write the audit program for provisions.

1 Understanding and documenting the process

• Interview management and key personnel involved in the provision estimation process.

-Obtain and document the entity’s policies and procedures for identifying, calculating, and recording provisions.

-Assess the design and implementation of key controls over the provision process.

2 Review provision estimates -Take the TB for the period being audited

-From the TB, choose the accounts to which provisions are booked.

-Analyze the opening balance of those accounts and closing balance to see the possible increase or decrease of provisions for the FY.

-Compare that the opening balance of provisions match with the provisions on the balance sheet of previous financial year and the the closing balance match with BS of current FY and that the difference with the changes for provisions on P&L.

-Compare provisions with industry benchmarks or similar entities, if available.

3 Testing the accuracy and completeness

-Calculate expected losses based on the counterpart aging for a sample of accounts: expected losses = PD * LGD * EAD

-Examine methods used to calculate the parameters

-Compare expected losses and provisions for sample accounts to reconcile that the provisions are in line with the expected losses.

-Inspect supporting documentation, such as contracts, agreements, or correspondence related to the provisions.

-Confirm completeness by reviewing minutes of board meetings, legal correspondence, and other relevant documents to identify potential provisions not recorded.

4 Review of Subsequent Events

-Review subsequent events up to the date of the auditor’s report to identify any events that might affect the provisions.

-Verify whether any adjustments or disclosures are necessary based on subsequent events.

5 Disclosures in Financial Statements

-Ensure that disclosures include a description of the nature of the provision, the uncertainties related to the amount or timing, and the expected timing of any resulting outflows.

-Compare disclosures with the requirements of the applicable financial reporting framework (e.g., IFRS, GAAP).

6 Finish the process

-Prepare audit working papers detailing the audit procedures performed, evidence obtained, and conclusions reached.

-Inform all the relevant parties including audit team and client for the results.

-Discuss with the management about the results and whether any changes are necessary and how the management is planning to do them

10. Write a general audit program.

1 Determine the objective for the audit

2 Audit planning

2.1 Understand the Entity and Its Environment

-Obtain background information about the entity, including its industry, regulatory environment, and key stakeholders.

-Review prior year audit files and reports.

2.2 Assess Risks and Determine Materiality

-Identify and assess the risks of material misstatement at the financial statement and assertion levels.

-Determine materiality for the financial statements as a whole and performance materiality.

-Document risk assessment and materiality thresholds.

2.3 Develop Audit Strategy and Plan

-Develop an overall audit strategy, including the scope, timing, and direction of the audit.

-Prepare a detailed audit plan outlining specific audit procedures to be performed.

-Allocate audit resources and assign responsibilities to the audit team.

3 Internal Controls

3.1 Evaluate Design and Implementation of Internal Controls

-Obtain an understanding of the internal control system, including control activities, risk assessment, information and communication, monitoring, and control environment.

-Evaluate the design and implementation of key controls relevant to the audit.

-Perform walkthroughs to verify the implementation of controls.

3.2 Test Operating Effectiveness of Controls

-Select a sample of transactions and test the operating effectiveness of key controls.

-Document the results of control testing and assess whether controls are operating effectively.

-Modify the nature, timing, and extent of substantive procedures based on control testing results.

4 Substantive Procedures

4.1 Cash and Bank

-Perform bank reconciliations.

-Confirm bank balances and loans with financial institutions.

-Test cash transactions for accuracy and completeness.

4.2. Receivables

-Confirm accounts receivable with customers.

-Review subsequent receipts for collectability.

-Analyze aging of receivables and evaluate allowance for doubtful accounts.

4.3.Inventory

-Observe physical inventory count.

-Test inventory valuation and costing methods.

-Evaluate inventory obsolescence and write-downs.

4.4. Property, Plant, and Equipment

-Verify existence through physical inspection.

-Test additions and disposals for proper authorization and recording.

-Review depreciation calculations and assess useful lives.

4.5 Liabilities

-Confirm accounts payable with suppliers.

-Review subsequent payments and disbursements.

-Test accruals and provisions for completeness and accuracy.

4.6. Revenue and Expenses

-Test revenue recognition policies for compliance with accounting standards.

-Perform analytical procedures on revenue and expenses.

-Substantiate significant expenses and review supporting documentation.

5 Analytical Procedures

5.1 Perform Analytical Procedures

-Conduct ratio analysis and trend analysis.

-Compare current year financial data with prior periods and industry benchmarks.

-Investigate significant variances and unusual transactions.

6 Review and Reporting

6.1. Review Financial Statements

-Review the draft financial statements for compliance with the applicable financial reporting framework.

-Ensure that disclosures are complete and accurate.

6.2. Obtain Management Representations

-Obtain a management representation letter covering significant assertions made during the audit.

-Document representations obtained and evaluate their impact on the audit.

6.3. Finalize Audit Documentation

-Complete and review all audit working papers.

-Ensure that all audit evidence is appropriately documented and supports the audit opinion.

6.4. Issue Audit Report

-Prepare the draft audit report.

-Discuss audit findings and conclusions with management and those charged with governance.

-Issue the final audit report expressing the audit opinion.