Fixed Asset Analysis
Aya Hanouni
22/07/2021
At Fresenius Medical Care North, West and Central Africa, located in their regional hub in Casablanca, the Finance and Accounting department, under the new leadership of Mr. Fouad Amzil, encountered a few discrepancies in their record-to-report (R2R) system, specifically regarding fixed assets. As an intern in the department for the duration of two months, this issue in the company’s books would become the subject of my internship project.
The central task of the project is to re-organize and ‘correct’ the firm’s fixed assets in their booking and internal management system, SAP. The first mission is to filter out the assets for disposal, so that they could be traced to their physical counterparts and the company can tangibly dispose of them. After that is completed, another study regarding depreciation rates in comparison to fiscal guidelines is to be conducted, followed by a forecast of the depreciation of the organization’s remaining fixed assets post-disposal. The entirety of the project and its tasks are conducted through R code, which is included in this report along with the output of the code, and interpretation of its results.
To begin the analysis, the ‘global environment’ must be set. This process encompasses the libraries (bouquets of functions and operations that will be used later to inspect and interpret the data), the directory or local folder from which data will be extracted, as well as other few specifications to anchor the operations into the data environment.
knitr::opts_chunk$set(echo = TRUE)
rm(list=ls()) #Clearing the global environment
setwd("C:/Users/ayaha/Downloads")
set.seed(123123) #Setting the seedAfter ensuring that the basis of the program is all set, we can load the data. The main dataset is collected from the internal management network, SAP, downloaded as an Excel file that is then coverted to a ’ .csv ’ format to be read by R Studio. The following chunk of code loads and gives a glimpse into the dataset at hand: The company’s Fixed Assets.
## Asset SNo. CoCd Asset.description Descrip..2 Class Cap.date
## 1 101092 0 MA01 MA01 Technical Capex MC22000 4/30/2017
## 2 100012 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 5/4/2016
## 3 100013 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 5/4/2016
## 4 100014 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 5/4/2016
## 5 100015 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 4/3/2015
## 6 100016 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 4/3/2015
## X..APC.FY.st X...Acquisi X...Current X.Accumul..d X.Dep..FY.sta X.Dep..for.ye
## 1 0.00 0 0.00 0.00 0.00 0.00
## 2 73458.38 0 73458.38 -73458.38 -68552.04 -4906.34
## 3 73458.39 0 73458.39 -73458.39 -68552.05 -4906.34
## 4 73458.38 0 73458.38 -73458.38 -68552.04 -4906.34
## 5 84465.21 0 84465.21 -84465.21 -84465.21 0.00
## 6 84465.20 0 84465.20 -84465.20 -84465.20 0.00
## X..Curr.bk.v X X.1
## 1 0 NA NA
## 2 0 NA NA
## 3 0 NA NA
## 4 0 NA NA
## 5 0 NA NA
## 6 0 NA NALooking at the data, we notice that for all first six rows shown by the ‘head’ function: - The variable “X…Acquisi” shows values of zeros without decimals, meaning it is not treated by the system as numerical. After examination, it has been concluded that it is a default variable from the SAP suppliers that the company does not use. This variable is thus removed.
## Asset SNo. CoCd Asset.description Descrip..2 Class Cap.date
## 1 101092 0 MA01 MA01 Technical Capex MC22000 4/30/2017
## 2 100012 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 5/4/2016
## 3 100013 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 5/4/2016
## 4 100014 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 5/4/2016
## 5 100015 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 4/3/2015
## 6 100016 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 4/3/2015
## X..APC.FY.st X...Current X.Accumul..d X.Dep..FY.sta X.Dep..for.ye
## 1 0.00 0.00 0.00 0.00 0.00
## 2 73458.38 73458.38 -73458.38 -68552.04 -4906.34
## 3 73458.39 73458.39 -73458.39 -68552.05 -4906.34
## 4 73458.38 73458.38 -73458.38 -68552.04 -4906.34
## 5 84465.21 84465.21 -84465.21 -84465.21 0.00
## 6 84465.20 84465.20 -84465.20 -84465.20 0.00
## X..Curr.bk.v X X.1
## 1 0 NA NA
## 2 0 NA NA
## 3 0 NA NA
## 4 0 NA NA
## 5 0 NA NA
## 6 0 NA NA- For the first line, we notice that the variable “X..APC.FY.st”, meaning ‘Acquistion and Production Costs’ in SAP documentation has a value of zero, which would not make sense to have acquired an asset for free. While it could be an outlier, a check for other assets with this case is necessary. The following few lines of code shows how many assets are marked free of acquisition costs.
## n
## 1 187We see that there are 187 assets with seemingly no acquisition costs, that we isolated into a separate dataset, that we will later save to Excel. When investigated, all these assets have been capitalized in 2017 or later, after the integration of the SAP system that, apparently, did not take into consideration the APC. This issue will be reported to the software supplier. For the analysis, these assets will be removed as to not affect other tasks (disposal, forecasts).
I - Disposal:
With the data refined from any anomalies, it can be utilized for the objectives of the project, the first of which is the disposal process. Under new leadership, the Finance and Accounting department has to revise past books, and noticed a substantial amount of assets that have either fully depreciated in 2020, or in earlier years, and that require disposal. These are the topic of this first section.
## Asset SNo. CoCd Asset.description Descrip..2 Class Cap.date X..APC.FY.st
## 2 100012 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 5/4/2016 73458.38
## 3 100013 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 5/4/2016 73458.39
## 4 100014 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 5/4/2016 73458.38
## 5 100015 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 4/3/2015 84465.21
## 6 100016 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 4/3/2015 84465.20
## 7 100017 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 4/3/2015 84307.38
## X...Current X.Accumul..d X.Dep..FY.sta X.Dep..for.ye X..Curr.bk.v
## 2 73458.38 -73458.38 -68552.04 -4906.34 0
## 3 73458.39 -73458.39 -68552.05 -4906.34 0
## 4 73458.38 -73458.38 -68552.04 -4906.34 0
## 5 84465.21 -84465.21 -84465.21 0.00 0
## 6 84465.20 -84465.20 -84465.20 0.00 0
## 7 84307.38 -84307.38 -84307.38 0.00 0## n
## 1 654After consulting with the accountant in charge of fixed assets, more information indicated that there is specific class of assets that represent assets that are not within the company’s possession: they are captive products (hemodialysis kits that go with the equipment the company sells), that the company considered leased assets over their shelf life of 5 years. This means that they are technically not assets and need to be isolated and filtered out.
This data (comprising assets that require disposal) has also been checked for another issue within the firm’s books: detailed asset description, dubbed ‘labels’. The dataset is thus separated into two, depending on whether the asset is labeled or unlabeled.
## Asset SNo. CoCd Asset.description Descrip..2 Class Cap.date
## 1 100543 0 MA01 Mobilier de Bureau NA MC2003B 23.02.1997
## 2 100544 0 MA01 Mobilier de Bureau NA MC2003B 23.09.1997
## 3 100545 0 MA01 Mobilier de Bureau NA MC2003B 01.02.1997
## 4 100548 0 MA01 Mobilier de Bureau NA MC2003B 12.06.1997
## 5 100758 0 MA01 Matériels et Outilla NA MC20034 01.01.1997
## 6 100772 0 MA01 Matériels et Outilla NA MC20034 20.02.2004
## X..APC.FY.st X...Acquisi X...Current X.Accumul..d X.Dep..FY.sta X.Dep..for.ye
## 1 109.24 0 109.24 -109.24 -109.24 0
## 2 300.00 0 300.00 -300.00 -300.00 0
## 3 450.00 0 450.00 -450.00 -450.00 0
## 4 480.00 0 480.00 -480.00 -480.00 0
## 5 480.00 0 480.00 -480.00 -480.00 0
## 6 483.34 0 483.34 -483.34 -483.34 0
## X..Curr.bk.v
## 1 0
## 2 0
## 3 0
## 4 0
## 5 0
## 6 0The labelless depreciated assets represented above have been discovered to all be very old assets dating as early as 1997. The tax return documents did not contain their descriptions either and it is highly likely that they have been physically disposed of. The Head Accountant thus decided to overlook this absence of labels regarding fully depreciated assets.
For the sake of clearer view of the data, it is best to obtain a graph representing it over time. The following code and results summarize the frequency of fully depreciated assets by their capitalization year, which was used later to justify the dismissal of the labeling tasks concerning them.
## # A tibble: 6 x 15
## # Groups: year [4]
## Asset SNo. CoCd Asset.description Descrip..2 Class Cap.date X..APC.FY.st
## <int> <int> <chr> <chr> <chr> <chr> <chr> <dbl>
## 1 100038 0 MA01 Autres Immob Corp 4008 S Class MC20~ 12/1/20~ 85426.
## 2 100039 0 MA01 Autres Immob Corp 4008B Réf ~ MC20~ 12/1/20~ 76746.
## 3 100040 0 MA01 Autres Immob Corp 4008S Réf:~ MC20~ 12/1/20~ 91429.
## 4 100051 0 MA01 Autres Immob Corp 5 KIT CDS RE MC20~ 2/6/2015 11600.
## 5 100132 0 MA01 Matériel Informa~ LAPTOP DELL MC20~ 4/28/20~ 8687
## 6 100133 0 MA01 Matériel Informa~ LAPTOP DELL MC20~ 4/28/20~ 8687
## # ... with 7 more variables: X...Current <chr>, X.Accumul..d <chr>,
## # X.Dep..FY.sta <dbl>, X.Dep..for.ye <dbl>, X..Curr.bk.v <dbl>, date <date>,
## # year <dbl>
## Warning: Removed 223 rows containing non-finite values (stat_bin).
II- Depreciation and Forecasts:
On the other hand,there are still assets that have not fully depreciated, whether they have proper labeling or are missing descriptions, that the company has to process until they fully depreciate.For these assets, three tasks are listed: - The proper labeling for assets missing descriptions. - The verification of currently used depreciation rates vis-à-vis fiscal guidelines. - Forecasting of future depreciation and disposal dates.
For these purposes, we must extract from our main data set the assets that are not fully depreciated, counted at 532 assets:
## n
## 1 532a- Asset Labels:After separating fully depreciated assets, the next step is to separate those that are not to be disposed of yet. Moreover, these assets need to be checked for labeling, and relabeled properly. To be more thorough, we can conduct a comparison of the not-for-disposal assets by the presence of assets
## n
## 1 146As shown above, there are 146 assets (out of 532, equating to roughly 27% of assets) that are missing their detailed descriptions.
## n
## 1 386As for the assets that do have their labels, there are 386 of them, around 73% of all non-depreciated assets.
The labeling has been done manually, by referring to tax return documents, searching SAP databases and following the list of classes that the company uses. The Excel file with the ‘updated’ fixed assets list (ridding it of depreciated assets and using correct labeling) is uploaded to this project and is shown in the chunk of code below:
## Asset SNo. CoCd Asset.description Descrip..2 Class Cap.date
## 1 100027 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 29.07.2016
## 2 100028 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 29.07.2016
## 3 100029 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 29.07.2016
## 4 100030 0 MA01 Autres Immob Corp 4008S CLASSI MC20050 29.07.2016
## 5 100035 0 MA01 Agencet Instal et Am COMPLEMENT R MC20020 09.05.2016
## 6 100036 0 MA01 Agencet Instal et Am RAYONNAGE DA MC20020 01.10.2016
## X..APC.FY.st X...Acquisi X...Current X.Accumul..d X.Dep..FY.sta X.Dep..for.ye
## 1 74131.73 0 74131.73 -72875.54 -66594.56 -6280.98
## 2 74131.73 0 74131.73 -72875.54 -66594.56 -6280.98
## 3 74131.73 0 74131.73 -72875.54 -66594.56 -6280.98
## 4 74131.73 0 74131.73 -72875.54 -66594.56 -6280.98
## 5 10000.00 0 10000 -5072.00 -4654.37 -417.63
## 6 3160.00 0 3160 -1474.66 -1342.99 -131.67
## X..Curr.bk.v
## 1 1256.19
## 2 1256.19
## 3 1256.19
## 4 1256.19
## 5 4928.00
## 6 1685.34This document has been submitted to the Accounting Department, and the accountant in charge will resubmit the labels to the SAP database for continuity purposes.
b- Depreciation Rates:Note: We use straight-line depreciation.
For this task, we will use yearly depreciation amounts and historical costs to extract depreciation rates for every class of assets. This task will require Excel and manual work to research the fiscal rates and compare them to the ones the company uses, possibly generating new depreciation amounts. Reading the excel file yields the following.
## Company.Dep.Rate Company.Useful.Life
## Mobilier de bureau 10% 10 years
## Matériel de bureau 10% 10 years
## Agencements Installations et Aménagements 10% 10 years
## Installations Techniques 20% 5 years
## Logiciel Informatique 20% 5 years
## Matériel de Transport 20% 5 years
## Fiscal.Dep.Rate Fiscal.Useful.Life
## Mobilier de bureau 10% 10 years
## Matériel de bureau 10% 10 years
## Agencements Installations et Aménagements 10% 10 years
## Installations Techniques 10% 10 years
## Logiciel Informatique 20% 5 years
## Matériel de Transport 20% 5 yearsSource: https://www.nse-ma.com/fr/comptabilite-marocaine-taux-damortissement-admis-fiscalement/
As the table shows, the depreciation rates in the system mostly conform to the fiscal standard, while only those for ‘Installations Techniques’ and ‘Matériel et Outillage’ are to be changed from 20% depreciation rate over 5 years, to 10% over 10 years.
These changes are to be made to the updated list of assets, which will then look like this:
## Asset Asset.description Cap.date Acquistion.Cost
## 1 100027 Autres Immob Corp 7/29/2016 74131.73
## 2 100028 Autres Immob Corp 7/29/2016 74131.73
## 3 100029 Autres Immob Corp 7/29/2016 74131.73
## 4 100030 Autres Immob Corp 7/29/2016 74131.73
## 5 100035 Agencet Instal et Am 5/9/2016 10000.00
## 6 100036 Agencet Instal et Am 10/1/2016 3160.00
## Accumulated.Depreciation Acc..Dep..Jan.1 Depreciation.until.May.31
## 1 -72875.54 -66594.56 -6280.98
## 2 -72875.54 -66594.56 -6280.98
## 3 -72875.54 -66594.56 -6280.98
## 4 -72875.54 -66594.56 -6280.98
## 5 -5072.00 -4654.37 -417.63
## 6 -1474.66 -1342.99 -131.67
## Depreciation.Rates Yearly.Depreciation Book.Value.End.2021 Disposal.Year
## 1 20% -15074.352 -7537.182 2026
## 2 20% -15074.352 -7537.182 2026
## 3 20% -15074.352 -7537.182 2026
## 4 20% -15074.352 -7537.182 2026
## 5 10% -1002.312 4343.318 2026
## 6 10% -316.008 1501.002 2026
## Disposal.Date
## 1 7/29/2026
## 2 7/29/2026
## 3 7/29/2026
## 4 7/29/2026
## 5 5/9/2026
## 6 10/1/2026The first observation upon examination of the data is the fact that on December 31st 2021, some assets show negative book value, meaning that they will depreciate before the year ends. This is an important remark for the next task.
c- Forecasts:In this section of the project, we will use the updated list of assets with the updated depreciation rates, and employ machine learning code in order to forecast when future disposals will take place. Before we proceed, the note from the previous section needs to be taken into consideration. Thus, assets that will be disposed of in 2021 are to be isolated and removed from the dataset subject to forecasting.
## Asset Asset.description Cap.date Acquistion.Cost
## 1 100035 Agencet Instal et Am 5/9/2016 10000.0
## 2 100036 Agencet Instal et Am 10/1/2016 3160.0
## 3 100209 Agencet Instal et Am 2/6/2012 10350.0
## 4 100210 Agencet Instal et Am 2/6/2012 10350.0
## 5 100211 Agencet Instal et Am 3/8/2012 8420.0
## 6 100212 Agencet Instal et Am 6/1/2012 12626.9
## Accumulated.Depreciation Acc..Dep..Jan.1 Depreciation.until.May.31
## 1 -5072.00 -4654.37 -417.63
## 2 -1474.66 -1342.99 -131.67
## 3 -9658.05 -9225.58 -432.47
## 4 -9658.05 -9225.58 -432.47
## 5 -7786.23 -7434.13 -352.10
## 6 -11364.17 -10838.03 -526.14
## Depreciation.Rates Yearly.Depreciation Book.Value.End.2021 Disposal.Year
## 1 10% -1002.312 4343.318 2026
## 2 10% -316.008 1501.002 2026
## 3 10% -1037.928 86.492 2022
## 4 10% -1037.928 86.492 2022
## 5 10% -845.040 140.830 2022
## 6 10% -1262.736 526.134 2022
## Disposal.Date
## 1 5/9/2026
## 2 10/1/2026
## 3 2/6/2022
## 4 2/6/2022
## 5 3/8/2022
## 6 6/1/2022Another consideration to be made is that in order to have a model built around the date every asset will be disposed of, that the company could use in future years for any asset, we’d need to build a column containing that year in our original data set. This column will determine the asset’s useful life based on the depreciation rate, permitting to determine the date of full depreciation.
After refining the data, the first step in any machine learning model is to split the data set into two subsets: the train set, where the program ‘learns’ patterns in the data, and the test set, where the program applies its newly acquired knowledge. The machine will study 75% of the data as its train set, and forecast the remaining 25% as the test set.
The train and test data sets:
Once the data is split, our machine can get to learning. Using randomForest, an elaborate machine learning model, we execute this task, making the predictions and measuring the accuracy.
## [1] 0.5488722As the output shows, the model is accurate to 54%, meaning that the company cannot rely on it just yet. The accuracy of the model will eventually increase as the company continues to grow in terms of fixed assets, making the dataset larger. (The bigger the dataset, the more the program can practice, and the more accurate the results).
In the meantime, what the firm can do is determine when exactly to carry out the disposal task during the year. The method is quite simple: graph the expected disposal dates. This will allow the department to be at maximum efficiency and clear out their assets of the ‘busiest’ month of the year disposal-wise.
Given the chart above, one can see that the month of December is when the greatest assets are disposed of, thus making it logical to carry out the disposal during that month to alleviate as much as possible. However, in practice, the company has many other accounting tasks in the month of December, including tax returns, statement preparation and closing accounts. It can therefore skip to the next month in the graph, which is July, that comes immediately after the company completed its semiannual reports and can focus on the disposal task.
All in all, the updated fixed assets and disposal procedure for the company are a step forward in record-keeping and forecasting, especially under new leadership of the Finance and Accounting department, promising further growth and prosperity in the market. Personally, I found this project very insightful, not only allowing access to hands-on learning in the Moroccan environment, knowledge concerning the ins and outs of companies, and opportunities to reconnect with both soft and hard skills previously acquired in higher education.