Analysis of Financial Time Seriesby Formula Base Interacting with Year-to-Date Calculation Model
Olivier Kumar (奧利弗~庫馬爾) is leadership in general business management. His achievements include the areas of outsourcing management, corporate finance, and investment diversification.
As a key user of financial statements, he had identified some weaknesses of ERP and accounting software during his past employments. It mainly attributed to the implementation of software which cannot support formula base query function to facilitate end-users designing different kinds of time series financial statements with the capability to handle year-to-date balances of assets and liabilities.
- Outsourcing Management: completed a restructuring exercise of a logistics business from mainly on asset-based carrier to supply chain solution provider
- Corporate Finance: successfully to recruit strategic investors for a retail group which contributed to a pre-IPO preparation work
- Investment Diversification: re-balancing property asset portfolio of a global hotel group, to build up a diversified portfolio of investment properties by disposal some under-performing hotels
Business Case in Logistics Sector
The logistics group manages a supply chain consulting services helping customers to reduce total cost of logistics e.g. optimising routes, carrier rates, and tariffs. Consulting services are charging on time cost basis with ceiling per each project. Billing operations are responsible by Finance Department using a custom-made accounting software. The accounting software is customised to support interface with time sheet data (time sheet data exported from a custom-made staff attendance software) for generation of all relevant vouchers.
Finance staff is frequently using Excel Pivot Table and Excel Formula Vlookup to prepare rolling 36-month financial statements for management based on a mass volume of vouchers exported from their accounting software. This support management to review the logistics business trend and identify any abnormal account balance e.g. staff attendance and related cost allocation.
Using the Excel functions to prepare Financial Statements in fact which can provide relevant analysis information for monthly Profit and Loss Account. However, it is misleading to the management in which all Assets, Liabilities, and Equities are reported by each monthly total movement for all accounts, rather than monthly year-to-date closing balance.
Business Case in Retail Sector
The retail group has over 200 branches located in different locations within an economic region. Each branch has operated a POS software which covers sales, purchase, inventory and general ledger. Multi-dimensional double entry vouchers are generated on a real-time basis in respect of sales, purchase, and inventory. For any non-trade business activity, finance staff is required to prepare vouchers manually using an accounting software. Both POS software and accounting software has built-in a report writer to support generating financial statements.
Key concerns of using the POS and Accounting software is mainly due to the long lead time to effect any amendment of financial statement layout. Also, two software are separated from each other, this mean accounting for trade activities are separated from accounting for non-trade activities.
Top management from time to time requests to implement an end-user reporting tool without affect the existing software. The project delay due to either cost concerns or complexity of software involving automation of consolidated financial statements for different grouping of branch, company and product line. Most common consolidation systems are mainly support using trial balance as a data import template, but most of the finance users of the group cannot accept this because voucher entries which contain important audit trial to support meaningful drill down enquiry function.
Business Case in Property Investment Sector
The property investment group invests and manages a portfolio of properties for operating a leasing business. Before software selection of an ERP which specialises in property investment sector, the top management had a preference of selection criteria including the top 3 priorities as follows: –
- Customer Relationship Management
- Rental Billing and Collection
- Property & Facility Management
The selection and implementation of the ERP are proven successful to meet the top 3 priorities. However, there is a long lead time for the preparation of full set of time series financial statements with notes to accounts when the presentation of financial reports is changed very frequently. The key issue of the ERP is designed for operating business rather than operating accounting. The software does not offer formula base reporting tool to support ever changing of financial reports.
To adapt the change of financial reports, the CFO decided to implement a very popular Business Intelligence software – which offers very attractive drag and drop function with graphical presentation. It is proven the new reporting tool can build a lot of fancy dashboards, but remain very weakness to deal with YTD Balances and numerical base reports – full set of time-series financial statements with notes to accounts.
Conso 123 Apps: 001 Multi-Month M-AccountReal Apps with Sample Data and Reports
Step 1: Connect
Connect your data files which contain multi-dimensional double entry vouchers recorded with different companies, voucher dates, business contracts, contractual parties, documents, etc.
Step 2: Calculate
Calculate monthly year-to-date balance for all accounts and analysis accounts. For any account configured as “P” profit and loss, accumulated balance for each year will be transferred to a retained account.
Step 3: Conclude
Offer user interface within Excel and LB Spreadsheet to assist configuring query formula e.g. =lq(“leg(B01..B10)a/c(28008888)pri(01/2016)val(CY)”) where B01 to B10 are selected Ledgers, 28008888 is a selected account, 01/2016 is a selected period and CY is to specify retrieving current year-to-date balance. Most of the finance users are familiar with the setting up of formula in Excel, so using the query formula can allow end-users to configure ad hoc reports very quickly.
In order to support the automation of step 1 and step 2 of consolidation practices, the apps have implemented recording functions such as Import_Transaction and Calculate_All_Balance. The former function supports to import mass volume of vouchers and the last function is to solve the calculation of monthly year-to-date balances for each account and analysis account.
Consolidation practices are usually attributed to the high performance of logical robot while accounting practices are often involving human judgement. For the purpose of setting up of a chart of account of this apps, there are two types of account namely balance sheet account “B” and profit and loss account “P”. Typically, assets, liabilities and equity (except profit and loss account) are classified as balance sheet type account while incomes, costs expenses are classified as profit and loss type account.
Within the equity (capital and reserves) section of the chart of account, there are a special account – retained account. By the end of each financial year, all closing balance of profit and loss account shall be transferred the retained account. Thus, opening balance for all profit and loss accounts are starting from zero at the beginning of each financial year.
There are no universal rules to govern assigning individual account code and account name. If the place of incorporation of your responsible legal entities has no regulation on a chart of account, you can consider the following example as a starting point to think about how to design better chart of account which can simplify the setting of financial statements.
An Example of Chart of Account Framework: –
- 1..1ZZZZZZZ Non-Current Assets
- 2..2ZZZZZZZ Current Assets
- 3..3ZZZZZZZ Current Liabilities
- 4..4ZZZZZZZ Non-Current Liabilities
- 5..5ZZZZZZZ Capital and Reserves
- 6..6ZZZZZZZ Revenue
- 7..7ZZZZZZZ Direct Cost / Expenses
- 8..8ZZZZZZZ Other Income
- 9..9ZZZZZZZ Indirect Cost / Expenses
Following examples is how assigning account code to affect your setting of financial statements: –
- a/c(1..2ZZZZZZZ) Total Assets
- a/c(2..3ZZZZZZZ) Net Current Assets
- a/c(3..4ZZZZZZZ) Total Liabilities
- a/c(6..7ZZZZZZZ) Gross Profit
- a/c(6..9ZZZZZZZ) Profit & Loss
- a/c(7..7ZZZZZZZ,9..9ZZZZZZZ) Total Cost
Assigning any long account code is not recommended if your accounting software can support multi-dimensional double entry voucher.
Follow the above accounting practices, for each period closing the sum of accounts reported as debit balance, shall be equal to the sum of accounts reported as a credit balance. Certain accounting software can also support a simplified presentation of individual account balance or group of account balances when you are preparing different types of Multi-Month M-Account. In this case, debit balance can be represented as positive amount without “Debit” sign, credit balance can be represented as negative amount without “Credit” sign.
Learning accountancy you may need more time to adapt at the beginning because you may be confused by the notation of “debit/credit” and the presentation of “left-hand side amount/right-hand side amount”. You may wonder why not record positive instead of debit and negative as credit for voucher entry. In the future of business case publishing, we can cover a case how the debit/credit recording mechanism can be benefits to internal control measures for monitoring of abnormal account balances.